تابع Vlookup برای برگرداندن مقدارهای تکراری، چندین مقدار یکسان و مطابق

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

قبلا در مطالب متعددی به موضوع تابع Vlookup در اکسل پرداخته ایم و اگر تاکنون با ما همراه بوده باشید، به خوبی با نحوه کارکرد معمول تابع Vlookup اکسل آشنا هستید. اما در این قسمت به حالتی خاص از این تابع یعنی برگرداندن چندین نتیجه به صورت همزمان و بر اساس شروط مختلف صحبت خواهیم کرد. در صورتی که با تابع Vlookup  در اکسل آشنا نیستید یا نیاز به مطالعه بیشتر دارید پیشنهاد میکنیم مطالب زیر را مطالعه کنید:

تابع VLOOKUP در اکسل | مثال ها، کاربردها و فیلم آموزشی

6 مشکل رایج در تابع vlookup

در بسیاری از اوقات اتفاق می افتد که شما می بایست تمام مقادیر مربوط به یک نام، آی دی، کالا، … را به صورت همزمان برگردانید. در این گونه موارد، اولین تابعی که به ذهن میرسد تابع Vlookup است؛ ولی مشکل اینجاست که تابع Vlookup صرفا یک مقدار (اولین مقدار مطالبق) را برمیگرداند.

برای حل این مشکل یکی از بهترین راه حل ها این است که از ترکیب چند تابع استفاده کنیم. حتی اگر اطلاعات چندانی راجع به چگونگی ترکیب توابع در اکسل ندارید هم، با ما همراه باشید. تلاش میکنیم در این مطلب منطق و چگونگی عملکرد توابع ترکیب شده در اکسل را به صورت ساده توضیح دهیم.

همانطور که در ابتدای این مطلب گفته شد،Vlookup فقط یک مقدار (اولین مقدار مطابق) را در هر زمان برمیگرداند و برای برگرداندن چندین مقدار با استفاده از تابع Vlookup میتوانید از توابع زیر به صورت آرایه ای استفاده کنید.

تابع IF: این تابع شرط را بررسی میکند و یک مقدار را به ازای صحیح یا اشتباه بودن شرط  برمیگرداند.

تابع K: smallامین عدد کوچک در بین اعداد را بعنوان نتیجه برمیگرداند.

Index: بر اساس شماره ستون و ردیفی که شما مشخص می کنید، یک عنصر را در آرایه برمیگرداند.

Row: شماره ردیف را برمیگرداند.

Column: شماره ستون را برمیگرداند.

Iferror: خطا ها را شناسایی میکند و مقدار دلخواه شما را به جای پیغام خطا برمیگرداند.

در صورتی که با آرایه ها و فرمول های آرایه ای در اکسل آشنا نیستید، پیشنهاد میکنیم مطالب زیر را مطالعه فرمایید.

مفهوم آرایه ها در اکسل

معرفی آرایه ها و فرمول های آرایه ای در اکسل

فرمول ها و فرمول نویسی آرایه ای اکسل | مثال های ساده و پیشرفته فرمول نویسی آرایه ای

تابع IF آرایه ای در اکسل

فرمول شماره 1: برگرداندن چندین مقدار با استفاده از Vlookup و قرار دادن نتایج در یک ستون

فرض کنید جدولی در اختیار دارید که در ستون A آن نام فروشنده و در ستون B نام محصول فروخته شده توسط او نشان داده میشود. ممکن است نام هر فروشنده چند بار در ستون A تکرار شود. حال میخواهیم لیست محصولات فروخته شده توسط هر فروشنده را برگردانیم. برای انجام این کار، به شیوه زیر عمل میکنیم:

1. لیستی از نام های منحصر به فرد را در چند ردیف خالی وارد کنید. در این مثال، ما این لیست را در سلول های D2:G2 وارد میکنیم.
2. در زیر نام اول، تعداد بزرگتر یا مساوی با حداکثر مقادیر مربوط به آن نام را انتخاب کنید، یکی از فرمول های آرایه ای زیر را در قسمت Formula bar وارد کنید و کلیدهای ترکیبی Ctrl + Shift + Enter را همزمان فشار دهید (در این صورت دیگر قادر به اصلاح فرمول در هرکدام از سلول ها به صورت جداگانه نیستید). شما همچنین میتوانید به جای این روش، فرمول مورد نظر را در اولین سلول وارد کنید، کلیدهای Ctrl + Shift + Enter را فشار دهید و سپس آن را در چند سلول پایین ستون درگ کنید (در صورتی که از این روش استفاده کنید امکان تغییر فرمول مربوط به هر کدام از سلول ها به صورت جداگانه وجود دارد).

=IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,""), ROW()-2)),"")

یا

=IFERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""), ROW()-2)),"")

3. فرمول را به یک ستون دیگر کپی کنید. برای انجام این کار، محدوده سلولی که فرمول را در آنها وارد کردید، انتخاب کنید و دستگیره fill ( مربع کوچک در پایین و سمت راست محدوده انتخاب شده) را به سمت دلخواه درگ کنید.

نتایج به شکل زیر خواهد بود.

اما این فرمول چگونه کار میکند:

در واقع این فرمول نمونه ای از فرمول های متوسط و پیشرفته اکسل است که نیاز به اطلاعاتی در زمینه فرمول های آرایه ای و توابع اکسل دارد. در این قسمت تلاش میکنیم از داخل به خارج به این فرمول نگاه کنیم و به نوعی آن را تجزیه نماییم. نحوه کار فرمول به شکل زیر است:

1. تابع IF:

در قلب این فرمول از یک تابع IF استفاده میکنیم تا محل قرارگیری تمام تکرارهای Lookup value (مقدار مورد جستجو) را مشخص کند.

تابع IF مقدار مورد جستجو(D2) را با تمام مقادیر lookup range (سلول های A3:A13) مقایسه میکند و در صورتی که مقداری مطابق با آن یافت شد، محل قرارگیری نسبی ردیف را برمیگراند، در غیر اینصورت “” (مقدار خالی) برگردانده میشود.

محل نسبی ردیف به این صورت محاسبه میشود که عدد 2 از ROW($B$3:$B$13) کسر میشود تا به این صورت اولین ردیف برابر با 1 خواهد بود. در صورتی که محدوده برگردان شما (سلول های  (B3:B13)  از ردیف 2 شروع شود، می بایست مقدار 1 را کسر کنید و … . نتیجه این عملیات آرایه ی  {1;2;3;4;5;6;7;8;9;10;11} خواهد بود که در پارامتر value_if_true تابع  IF قرار میگیرد.

به جای عملیات بالا میتوانید از عبارت ROW(lookup_column)- MIN(ROW(lookup_column))+1 استفاده کنید که همان نتیجه را برمیگرداند که نیاز به هیچ تغییری ندارد و محل ستون برگردانده شده در آن هیچ تاثیری ندارد. در این مثال این عبارت برابر خواهد بود از : ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1

تا به اینجای کار شما یک آرایه متشکل از اعداد(محل های قرارگیری مقادیر مطابق) و رشته های خالی(غیر مطابق ها) در اختیار دارید. در این مثال، در سلول D3 عبارت زیر را خواهیم داشت:

حال اگر این عبارت را با داده های منبع چک کنید متوجه خواهید شد که “Adam” ( مقدار مورد جستجو در سلول D2) در محل های سوم، هشتم و دهم lookup range ظاهر شده است.

2. تابع Small

در مرحله دوم، تابع SMALL(array, k) مشخص میکند که کدام یک از مقادیر مطابق می بایست در یک سلول خاص قرار گیرد. در واقع این تابع محل قرارگیری موارد یافت شده را مشخص میکند.

پارامتر Array این تابع که قبلا به دست آمده است و در این قسمت به توضیح در مورد پارامتر K میپزدازیم. این پارامتر در واقع kامین مقدار کوچک آرایه را برمیگرداند. برای انجام این کار، بر اساس فرمول  ROW()-n عمل دسته بندی را انجام میدهیم که در این فرمول n برابر است با شماره ردیف اولین فرمول منهای 1 . در این مثال، ما فرمول را در سلول های  D3:D7 وارد کرده ایم. در نتیجه ROW()-2 مقدار 1 را برای D3 (ردیف 3 منهای 2)، مقدار 2 را برای D4 (ردیف 4 منهای 2) و … برمیگرداند.

در نتیجه تابع small، کوچترین مقدار ارایه را در سلول D3، دومین مقدار کوچک را در سلول D4 و … قرار میدهد.

و این کار فرمول پیچیده و طولانی ابتدایی را به فرمول بسیار ساده زیر بدل میکند:

نکته:

برای مشاهده مقدار محاسبه شده موجود در پشت هرکدام از قسمت های فرمول، کافیست آن قسمت را انتخاب کنید و کلید F9 را فشار دهید.

3. تابع Index

این قسمت آسانتر از سایر بخش های فرمول است. از تابع index برای برگرداندن مقدار یکی از عناصر آرایه بر اساس شماره ردیف آن استفاده میکنیم.

4. تابع Iferror

و در نهایت، می بایست فرمول را در درون تابع iferror قرار دهیم تا در صورتی که خطایی رخ دهد، مقدار یا عبارت مورد نظر شما نمایش داده شود. وقوع خطا در این فرمول اجتناب ناپذیر است زیرا شما نمیدانید دقیقا چند مقدار مطابق با مقدار مورد جستجو وجود دارد و احتمالا در انتخاب سلول ها، که می بایست تعداد آنها بزرگتر یا مساوی تعداد مطابق ها باشد، دچار اشتباه خواهید شد. برای اینکه کاربران شما با حجم زیادی از خطا روبرو نشوند، بهتر است از فرمول Iferror استفاده کنید.

با در نظر گرفتن تمام توضیحات بالا، فرمول های توضیح داده شده به شکل زیر خواهند بود:

فرمول 1:

IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")

فرمول 2:

IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")

توضیحات:

• m برابر با شماره ردیف اولین سلول محدوده برگردان (سلول های B3:B13) منهای 1
• n برابر با شماره ردیف سلول حاوی اولین فرمول منهای 1

نکته:

در مثال بالا، هم n و هم m برابر با 2 هستند. زیرا محدوده برگردان و محدوده فرمول ما هر دو برابر با 3 هستند. در ورک شیت شما ممکن است این مقادیر با هم متفاوت باشند.

فرمول شماره 2 . برگرداندن چند نتیجه با استفاده از تابع Vlookup و قرار دادن آنها در یک ردیف

در صورتی که میخواهید مقادیر برگردانده شده به جای ستون در ردیف های متعدد قرار گیرد، تغییرات زیر را بر روی فرمول توضیح داده شده در قسمت قبل  اعمال کنید.

=IFERROR(INDEX($B$3:$B$13, SMALL(IF($D3=$A$3:$A$13, ROW($B$3:$B$13)-2,""), COLUMN()-4)),"")

یا

=IFERROR(INDEX($B$3:$B$13,SMALL(IF($D3=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""),COLUMN()-4)), "")

شبیه به قسمت قبل، هر دو فرمول، آرایه ای هستند. بنابراین پس از وارد کردن فرمول، کلیدهای ترکیبی Ctrl + Shift + Enter را فشار دهید.

این فرمول نیز با همان منطق توضیح داده شده در قسمت قبل کار میکند با این تفاوت که در اینجا از تابع Column (فرمول COLUMN()-n) به جای تابع Row و به منظور مشخص کردن اینکه کدام یک از مقادیر مطابق می بایست در یک سلول خاص قرار گیرند، استفاده میکنیم. در این فرمول n شماره ستون اولین سلولی که فرمول در آن وارد شده است منهای 1 است. در این مثال، فرمول در سلول های E2:H2 وارد شده است. از آنجایی که E پنجمین ستون است، n برابر با 4 است (5-1=4)

نکته:

برای اینکه فرمول به شکل صحیحی کپی شود، حتما آدرس مقادیر مورد جستجو(lookup value) را به این صورت وارد کنید که ستون ها به صورت مطلق و ردیف ها به صورت نسبی باشند($D3)

بر اساس توضیحات بالا، فرمول های گفته شده به شکل زیر خواهند بود:

فرمول 1:

IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")

فرمول 2:

IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(lookup_range) - MIN(ROW( lookup_range))+1,""),COLUMN() - n)), "")

توضیحات:

m برابر با شماره ردیف اولین سلول در محدوده برگردان (سلول های B3:B13) منهای 1

n برابر است با شماره ستون اولین سلول حاوی فرمول منهای 1

 فرمول شماره 3: استفاده از تابع Vlookup بر اساس چندین شرط

تا اینجای کار نحوه برگرداندن مقادیر متعدد را با استفاده از تابع Vlookup و بر اساس یک شرط مشاهده کردید. ولی اگر بخواهیم چند مقدار بر اساس چند شرط برگردانده شوند، چه باید کرد؟ فرض کنید در مثال قبل یک ستون  ماه نیز داشته باشیم و بخواهیم لیستی از محصولات فروخته شده توسط هر فروشنده در یک ماه مشخص را داشته باشیم.

اگر با فرمول های آرایه ای آشنا باشید احتمالا میدانید که در این فرمول ها میتوان از علامت ستاره (*) به جای عملگر And استفاده کرد. بنابراین میتوان با استفاده از این علامت، به راحتی فرمول های بالا را تبدیل به حالت زیر کرد که شرط های متعدد را بررسی کنند.

برگرداندن مقادیر متعدد و قرار دادن آنها در یک ستون

IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")

توضیحات:

m برابر با شماره ردیف اولین سلول در محدوده برگردان منهای 1 است

n برابر است با شماره ردیف اولین سلول حاوی فرمول منهای 1

با فرض اینکه اولین لیست فروشنده ها (lookup_range1) در محدوده  A3:A30، لیست ماه ها(lookup_range2) در محدوده B3:B30، فروشنده مورد نظر  (lookup_value1) در سلول E3 و ماه مورد نظر  (lookup_value2)  در سلول F3 قرار دارد؛ فرمول به شکل زیر تبدیل خواهد شد:

=IFERROR(INDEX($C$3:$C$30, SMALL(IF(1=((--($E$3=$A$3:$A$30)) * (--($F$3=$B$3:$B$30))), ROW($C$3:$C$30)-2,""), ROW()-2)),"")

این شکل از فرمول نویسی بیشتر برای ساخت داشبورد مناسب است. به این صورت که کاربر میتواند یک نام در سلول E3، ماه را در سلول F3 وارد کند و لیستی از محصولات فروخته شده را در ستون G بدست آورد.

برگرداندن چندین نتیجه با استفاده از تابع Vlookup در یک ردیف

در صورتی که بخواهید نتایح بدست آمده به شکل افقی در کنار هم قرار گیرند، میتوانید از فرمول زیر استفاده کنید:

IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")

توضیحات:

m برابر با شماره ردیف اولین سلول در محدوده برگردان (سلول های B3:B13) منهای 1 است

n برابر است با شماره ردیف اولین سلول حاوی فرمول منهای 1

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

=IFERROR(INDEX($C$3:$C$30, SMALL(IF(1=((--($E3=$A$3:$A$30)) * (--($F3=$B$3:$B$30))), ROW($C$3:$C$30)-2,""), COLUMN()-6)),"")

با استفاده از همین شیوه شما میتوانید از تابع Vlookup همراه با چندین شرط متعدد استفاده کنید.

این فرمول چگونه کار میکند:

اساسا نحوه کارکرد این فرمول نیز با فرمول ابتدایی توضیح داده شده اختلاف چندانی ندارد. تنها تفاوت این فرمول این است که تابع IF در اینجا چندین شرط را بررسی میکند:

1=((--(lookup_value1=lookup_range1))*(--(lookup_value2=lookup_range2))*…)

نتیجه هرکدام از مقایسات lookup_value=lookup_range آرایه ای از مقادیر منطقی true( در صورتی که شرط برقرار باشد) و false( در صورتی که شرط برقرار نباشد) است. علامت (–) نیز مقادیر منطقی true و false را تبدیل به 1 و 0 میکند. از آنجایی که ضرب هر مقداری در 0 همیشه برابر با صفر خواهد بود، در آرایه ای که نتیجه میشود شما فقط برای عناصری 1 خواهید داشت که تمام شرط های خواسته شده در آنها برقرار باشد. حال فقط می بایست ارایه بدست آمده را با عدد 1 مقایسه کنید. در نتیجه تابع ROW اعداد مربوط به ردیف هایی را که تمام شرط ها را دارا باشند را برمیگرداند. در غیر اینصورت یک رشته خالی برگردانده میشود.