استفاده از تابع vlookup با برقراری دو شرط همزمان

**حسام بحرانی** · 2014/07/02, 03:40

با سلام
به جای استفاده از تابع VLOOKUP در جستجوی بین دو ستون، پیشنهاد می کنم از ترکیب توابع INDEX و MATCH کمک بگیرید.
با سپاس
ɦɛʂɑɱ ɓɑɦɾɑɳɨ

**hoosein** · 2014/07/02, 08:22

خیلی ممنون دوستان
تشکر

**mokaram** · 2014/07/02, 08:26

نوشته اصلی توسط hoosein

خیلی ممنون دوستان
تشکر

دوست عزيز در صورتي كه به جواب مورد نظرتون رسيديد گزينه حل شد (تيك سبز رنگ) در پست اولتون رو كليك كنيد
با تشكر

**amirkhan** · 2016/08/29, 17:05

سلام دوستان
بازیابی مقادیر از یک شیت با اعمال شرط در یک شیت دیگر
یک جدول که در ان اطلاعات خرید سبد از فروشندگان مختلف در اون ثبت میشه (input data)
حالا اگه من شرط یک فروشنده رو براش بذارم تمامی خرید هام رو در شیت بعدی ( search) لیست کنه
و حالا یک شرط تاریخ هم براش بذارم و هم شرط یک فروشنده خاص (شیت search date)

فایل های پیوست شده

برنامه سبد.xlsx (773.9 کیلو بایت, مشاهدات 130)

**mmahdih** · 2016/08/29, 18:56

نوشته اصلی توسط amirkhan

سلام دوستان
بازیابی مقادیر از یک شیت با اعمال شرط در یک شیت دیگر
یک جدول که در ان اطلاعات خرید سبد از فروشندگان مختلف در اون ثبت میشه (input data)
حالا اگه من شرط یک فروشنده رو براش بذارم تمامی خرید هام رو در شیت بعدی ( search) لیست کنه
و حالا یک شرط تاریخ هم براش بذارم و هم شرط یک فروشنده خاص (شیت search date)

سلام
وقتتون بخیر
فایل پیوست رو یک نگاه بندازید
البته این توضیح رو بدم که من در شیت اصلی که مخصوص ورود اطلاعات هست دو ستون اضافه کردم که باید دقت کنید این دو ستون حذف نشه

فایل های پیوست شده

test.xlsx (1.31 مگابایت, مشاهدات 132)

**amir_ts** · 2016/08/29, 19:27

با سلام
فایل نمونه رو ملاحظه کنید.
فرمول های آرایه ای زیر مورد استفاده قرار گرفته است.

کد PHP:


=IFERROR(INDEX(data;SMALL(IF(name=$D$4;ROW(name)-6);ROW(A1));COLUMN(B1));"")

کد PHP:


=IFERROR(INDEX(data;SMALL(IF((name=$D$5)*((VALUE(SUBSTITUTE(date;"/";""))>=$D$4)*(VALUE(SUBSTITUTE(date;"/";""))<=$F$4));ROW(name)-6);ROW(A1));COLUMN(B1));"")

فرمت سل تاریخ در شیت جستجو بر اساس، تاریخ باید به این شکل باشه یعنی شما فقط عدد بزنید.

کد PHP:


##"/"##"/"##

از نامگذاری محدوده استفاده شده است.برای بالا بردن سرعت محاسبات محدوده را باید متناسب با نیازتون کنید.

فایل های پیوست شده

برنامه سبد.xlsx (1.06 مگابایت, مشاهدات 73)

**amirkhan** · 2016/08/30, 09:02

سلام
متشــــــــــــــکرم

**amirkhan** · 2016/09/04, 13:11

سلام
عرض خسته نباشی
یک فایل که اطلاعات بارنامه فروش محصولات (مرکبات)به فروشندگان ثبت میشه
یک گزارش شیت1 report در حقیقت شرط نام فروشنده رو بذاریم ریز اطلاعات بارنامه رو لیست کنه
در یک گزارش دیگه report 2 درحقیقت شمارش بارنامه های غیرتکراری(ستون a در شیت ورودی) با اعمال شرط نام فروشنده رو بیاره
قبلا هم یک فایل مشابه گزارشاتش رو طراحی کردید . سپاس فراوان
درمورد فرمول small و لیست کردن از یک شیت به شیت دیگر یک توضیح بدید . ممنون از سایت خوبتون

فایل های پیوست شده

بار ارسالی.xlsx (1.06 مگابایت, مشاهدات 42)

**amir_ts** · 2016/09/04, 14:18

با سلام
بهتر بود سوالتون رو در یک تاپیک مجزا مطرح میکردید.
فرمول های آرایه ای زیر رو ملاحظه کنید.

یک گزارش شیت1 report در حقیقت شرط نام فروشنده رو بذاریم ریز اطلاعات بارنامه رو لیست کنه

کد PHP:


=IFERROR(INDEX(data1;SMALL(IF(name1=$D$4;ROW(name1)-4);ROW(A1));COLUMN(B1));"")

در یک گزارش دیگه report 2 درحقیقت شمارش بارنامه های غیرتکراری(ستون a در شیت ورودی) با اعمال شرط نام فروشنده رو بیاره

کد PHP:


=SUM(IF(FREQUENCY(IF(name1=$C5;IF(name1<>"";MATCH(num;num;0)));ROW(num)-4);1))

درمورد فرمول small و لیست کردن از یک شیت به شیت دیگر یک توضیح بدید .

تابع small به تنهایی نقشی در لیست کردن اطلاعات نداره برای فهمیدن چگونگی لیست کردن باید با فرمول های آرایه ای آشنا بشید که به فرمول لیست کردن اطلاعات پی ببرید.

فایل های پیوست شده

بار ارسالی.xlsx (1.09 مگابایت, مشاهدات 37)

**hamidreza asadi** · 2018/12/16, 09:40

سلام دوستان.
سلولی که در آن تابع رو میخواهیم آرایه ای ذخیره کنیم merge شده و تابع رو نمیشه آرایه ای ذخیره کرد. برای اون چه پیشنهای دارید

**egafar** · 2019/06/21, 23:40

سلام وعرض ادب
من یک فایل اکسل دارم که حاوی 4 ستون است.ستون اول نام شرکت می باشد .ستون دوم سال مالی و ستون سوم تاریخ مصوب و ستون چهارم نوع گزارش است
هر شرکت می تواند دارای 5 نوع گزارش 3و6و9و12و مجمع باشد با تاریخ مصوب مختلف.لذا در ستون اول نام شرکت ممکن است 5 بار پشت سرهم تکرار شود ولی با تاریخ مصوب مختلف.من می خواهم جدید ترین گزارش مصوب (ستون سوم)برای نام شرکت انتخاب شود و بجای اینکه تک تک خودم هر ردیف را چک کرده و جدیدترین تاریخ گزارش مصوب را برای نام شرکت انتخاب کنم و بقیه را حذف کنم از تابعی استفاده کنم که خودش این کاررا برایم انجام دهد .ممنون می شوم راهنمایی بفرمایید.

فایل های پیوست شده

جمع بدهیها.xls (29.0 کیلو بایت, مشاهدات 11)

**M_ExceL** · 2019/06/22, 03:58

نوشته اصلی توسط egafar

سلام وعرض ادب
من یک فایل اکسل دارم که حاوی 4 ستون است.ستون اول نام شرکت می باشد .ستون دوم سال مالی و ستون سوم تاریخ مصوب و ستون چهارم نوع گزارش است
هر شرکت می تواند دارای 5 نوع گزارش 3و6و9و12و مجمع باشد با تاریخ مصوب مختلف.لذا در ستون اول نام شرکت ممکن است 5 بار پشت سرهم تکرار شود ولی با تاریخ مصوب مختلف.من می خواهم جدید ترین گزارش مصوب (ستون سوم)برای نام شرکت انتخاب شود و بجای اینکه تک تک خودم هر ردیف را چک کرده و جدیدترین تاریخ گزارش مصوب را برای نام شرکت انتخاب کنم و بقیه را حذف کنم از تابعی استفاده کنم که خودش این کاررا برایم انجام دهد .ممنون می شوم راهنمایی بفرمایید.

سلام،
کد زیر رو روی فایلی که پیوست کردید اجرا کنید :

کد:

Sub M_E()
With Application
    .ScreenUpdating = False
    .EnableEvents = False
        Columns(6).ClearContents
        Columns(7).ClearContents
        Columns(8).ClearContents
            Range("f1") = "lvalues"
            Range("g1") = "ulist"
                lr = Cells(Rows.Count, 1).End(3).Row
                Range("A2:A" & lr).Copy
                Range("g2").PasteSpecial Paste:=xlPasteValues
                .CutCopyMode = False
                ActiveSheet.Range("$g$1:$g$" & lr).RemoveDuplicates Columns:=1, Header:=xlYes
                lrg = Cells(Rows.Count, 7).End(3).Row
                    For g = 2 To lrg
                        For a = 2 To lr
                            If Range("g" & g) = Range("a" & a) Then
                                Cells(a, 6) = g
                                    Range("h2").FormulaArray = "=TEXT(MAX(VALUE(SUBSTITUTE(INDIRECT(""c""&MATCH(" & Range("g" & g).Row & ",$f$2:$f$29,0)+1&"":""&""c""&MATCH(" & Range("g" & g).Row & ",$f$2:$f$29)+1),""/"",""""))),""####""""/""""##""""/""""##"")"
                                    s = Range("h2").Text
                                    If s <> Range("c" & a) Then Range("c" & a) = s: Range("c" & a).Interior.ColorIndex = 7
                            End If
                        Next a
                    Next g
        Columns(6).ClearContents
        Columns(7).ClearContents
        Columns(8).ClearContents
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub

یا حق.

**M_ExceL** · 2019/06/22, 12:08

کد باگ کوچکی داشت که برطرف گردید :

کد:

Sub M_E()
With Application
    .ScreenUpdating = False
    .EnableEvents = False
        Columns(6).ClearContents
        Columns(7).ClearContents
        Columns(8).ClearContents
            Range("f1") = "lvalues"
            Range("g1") = "ulist"
                lr = Cells(Rows.Count, 1).End(3).Row
                Range("A2:A" & lr).Copy
                Range("g2").PasteSpecial Paste:=xlPasteValues
                .CutCopyMode = False
                ActiveSheet.Range("$g$1:$g$" & lr).RemoveDuplicates Columns:=1, Header:=xlYes
                lrg = Cells(Rows.Count, 7).End(3).Row
                    For g = 2 To lrg
                        For a = 2 To lr
                            If Range("g" & g) = Range("a" & a) Then
                                Cells(a, 6) = g
                            End If
                        Next a
                    Next g
                    For g = 2 To lrg
                        For a = 2 To lr
                            If g = Range("f" & a) Then
                                    Range("h2").FormulaArray = "=TEXT(MAX(VALUE(SUBSTITUTE(INDIRECT(""c""&MATCH(" & g & ",$f$2:$f$" & lr & ",0)+1&"":""&""c""&MATCH(" & g & ",$f$2:$f$" & lr & ")+1),""/"",""""))),""####""""/""""##""""/""""##"")"
                                    s = Range("h2").Text
                                    If s <> Range("c" & a) Then Range("c" & a) = s: Range("c" & a).Interior.ColorIndex = 7
                            End If
                        Next a
                    Next g
        Columns(6).ClearContents
        Columns(7).ClearContents
        Columns(8).ClearContents
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub

ابتدا ماکرو رو فعال کنید، سپس روی باتن اجرای کد کلیک کنید
یا حق.

فایل های پیوست شده