تابع Vlookup به نحوی طراحی شده است که صرفا قادر است یک مقدار یا عبارت را جستجو کرده و مقدار مربوط به آن را در ردیف دلخواه برگرداند. اما بسیار اتفاق می افتد که نیاز است تابع Vlookup به جای جستجوی یک مقدار ( یک شرط) می بایست چندین مقدار را جستجو کند و از تابع Vlookup اکسل با چند شرط استفاده کنید. استفاده از تابع Vlookup اکسل با چند شرط و چسباندن چندن مقدار مسئله بسیار رایجی است. مثلا مقدارها زیر را در نظر بگیرید:

نام ——– سال —– مقدار

امیر —–    97  —-  1500

پیمان —– 97   —– 2500

آرمیتا —–   98   ——   1900

امیر ——   98 ——-  4800

پیمان —–    98 ——-   4500

اگر بخواهیم مقدار مربوط به «امیر» در سال 98 رو پیدا کنیم چه فرمولی باید بنویسیم؟ اگر از تابع vlookup ساده استفاده کنیم فقط میتونیم شرط «امیر» اعمال کنیم و اولین مقدار «امیر» برای ما پیدا میکنه. در این حالت نیاز داریم از تابع Vlookup اکسل با چند شرط استفاده کنیم.

پیشنهاد مطالعه: 

تابع xlookup در اکسل قابلیتی را ایجاد کرده است که می توانید به راحتی چندین شرط را برای برگرداندن مقدار تعیین کنید:

تابع xlookup در اکسل (تایع جدید در اکسل 2021)

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

پیشنهاد مطالعه: 

اگر با تابع vlookup د ر اکسل و نحوه کارکرد این تابع آشنا نیستید، پیشنهاد میکنیم قبل از مطالعه این آموزش، مطلب زیر را مطالعه کنید:

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

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

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

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

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

• استفاده از یک ستون کمکی
• استفاده از تابع Choose

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

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

1. این شیوه باعث میشود کاربران و به خصوص کاربران تازه کار، فهم بسیار خوبی نسبت به اتفاقاتی که در ورک شیت رخ میدهد داشته باشند.
2. سرعت انجام پردازش در ستون های کمکی نسبت به فرمول های آرایه ای بسیار بالاتر است( این موضوع در زمان کار با داده های بزرگ خود را نشان خواهد داد)

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

پیشنهاد مطالعه:

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

• مفهوم آرایه ها در اکسل
• نگاه آرایه ای به تابع vlookup

به موضوع اصلی برمیگردیم. دلیل استفاده از ستون های کمکی در اینجا این است که شرط های چندگانه تابع Vlookup را در این ستون تبدیل به یک شرط کنیم و سپس درون تابع Vlookup قرار دهیم. در این حالت به راحتی می تونیم از تابع Vlookup اکسل با چند شرط استفاده کنیم. برای مثال، در داده های نمایش داده شده سه نوع امتحان ریاضی و نمره ریاضی وجود دارد که ما در هر بار فقط به یکی از این نمرات( یک ترکیب از math و unit test) احتیاج داریم.

برای انجام این کار مراحل زیر را طی کنید:

1. در بین ستون های B و C یک ستون جدید ایجاد کنید.
2. در ستون کمکی ایجاد شده از فرمول زیر استفاده کنید.

=A2&”|”&B2

 

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

3. در سلول G3 از فرمول زیر استفاده کنید.

=VLOOKUP($F3&”|”&G$2,$C$2:$D$19,2,0)

 

4. این فرمول را برای تمامی سلول ها کپی کنید.

این روش چگونه کار میکند؟

همانطور که قبلا گفتیم تابع vlookup صرفا یک مقدار را جستجو میکند و مقدار مربوط به آن را در ردیف دلخواه برمیگرداند. اما در اینجا که مقادیر مورد جستجو بیش از یک مورد بود، پارامتر lookup_value عبارت $F3&”|”&G$2 قرار داده شد تا هر دو مقدار مورد جستجو را به هم بچسباند و تبدیل به یک مقدار کند. برای مثال در سلول G2 مقدار مورد جستجو Matt|Unit Test قرار داده شد و تابع vlookup نمره مربوط به این ترکیب را از C2:D19 برمیگرداند.

توضیحات:

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

• چرا در این روش از علامت | برای ترکیب دو شرط استفاده کردیم؟

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

همانطور که مشاهده میکنید اگرچه A2 و A3 متفاوت هستند و B2 و B3 نیز متفاوت هستند ولی ترکیب اینها مشابه خواهد بود. ولی زمانی که از جداکننده استفاده کنیم، ترکیب های نیز متفاوت خواهند بود( سلول D2 و D3)

• چرا ستون کمکی را بین B و C اضافه کردیم و. در سمت چپ داده های اضافه نکردیم؟

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

Vlookup با شرط های چندگانه – استفاده از تابع Choose

از آنجایی که همه افراد به یک شکل فکر نمیکنند، بعضی از آنها ممکن است ترجیح دهند که به جای ستون کمکی استفاده نکنند و روش های جایگزین را استفاده نمایند. یکی از این روش های جایگزین استفاده از تابع Choose  اکسل در ترکیب با تابع Vlookup است.

یکی از مزایای استفاده از توابع آرایه ای مثل choose این است که فضای کمتری از ورک شیت را استفاده میکنند و در صورتی که به تعداد دفعات زیاد از این فرمول ها در یک ورک شیت استفاده نشود، عملکرد قابل قبولی را نیز خواهد داشت.

 

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

=VLOOKUP($E3&”|”&F$2,CHOOSE({1,2},$A$2:$A$19&”|”&$B$2:$B$19,$C$2:$C$19),2,0)

 

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

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

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

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

{“Matt|Unit Test”,91;”Bob|Unit Test”, 52;……}

 

در این آرایه علامت (,) نشان دهنده سلول بعدی در همان ردیف و علامت (;) نشان دهنده این است که داده های بعدی در ستون بعدی قرار دارند. در نتیجه این فرمول، دو ستون داده را ایجاد خواهد کرد. یک ستون شامل شناسنده های منحصر به فرد و ستون دیگر شامل نمرات میشود.

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

همیشه در اکسل راه حل های مختلفی برای انجام یک کار وجود دارد. برای مثال شما میتوانید روش جایگزین ترکیب index/match و sumproduct را برای تمرین بیشتر امتحان کنید.

آیا شما روش دیگری برای انجام این کار سراغ دارید؟ ما را در تکمیل این مطلب کمک کنید.