جستجوی چند شرطی در اکسل

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

به عنوان مثال داده های مثال زیر را در نظر بگیرید.

چگونه باید مقداری را جستجو کنیم که دارای سه شرط “آرایشی بهداشتی”، “عطر،ادکلن” و “جوان” باشد؟

با ما همراه باشید تا به جواب این دسته از سوال ها یعنی “چگونگی جستجوی چند شرطی در اکسل” برسیم.

روش 1- استفاده از تابع Vlookup و ستون کمکی

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

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

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

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

1- بین ستون های C و E یک ستون جدید ایجاد می کنیم.

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

2- در ستون کمکی ایجاد شده از فرمول زیر استفاده می کنیم.

این فرمول، مقادیر سه ستون B، A و C را کنار هم در ستون کمکی قرار می دهد و با استفاده از کاراکتر “|” آن ها را از هم متمایز می کند.

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

3- در سلول J7 فرمول زیر را اجرا می کنیم.

همانطور که قبلا گفتیم تابع Vlookup صرفا یک مقدار را جستجو می کند و مقدار مربوط به آن را در ردیف دلخواه برمی گرداند. اما در اینجا که مقادیر مورد جستجو بیش از یک مورد بود، برای پارامتر lookup_value عبارت G7&”|”&H7&”|”&I7 قرار داده شد تا هر سه مقدار مورد جستجو را به هم بچسباند و تبدیل به یک مقدار کند.

نتیجه را به صورت زیر مشاهده می کنیم.

مقدار مجهول سلول J8 را با استفاده از خاصیت Fill Sequence به دست آورده ایم.

روش 2- استفاده از تابع Vlookup بدون ستون کمکی

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

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

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

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

پارامتر های نامگذاری شده در تصویر به شرح زیر است:

1- آرگومان اول تابع SEARCH VALUE است که ترکیبی از سه شرط است. در این حالت مقادیر جستجو به صورت زیر است:

2- در آرگومان دوم تابع Vlookup یعنی Search Area، تابع Choose را وارد می کنیم.

ایده این است که فرمول Choose یک آرایه دو بعدی ایجاد می کند. می توانید جدول را به صورت دو ستون تصور کنید. ستون اول شامل عبارت جستجو است، در این حالت ترکیبی از هر سه شرط جستجو است (در این مثال “مد پوشاک، عینک افتابی، میانسال”) و ستون دوم “فروش” است.

2A- آرگومان INDEX number در تابع Choose، همیشه {1,2} است.

2B و 2C و 2D- اولین ستون جستجو با کارکتر & به دومین و سومین ستون جستجو متصل می شود تا باهم تلفیق شونند و یک ستون در نظر گرفته شوند.

2E- آخرین قسمت فرمول Choose ستون برگشتِ یا در این مثال ستون “فروش” است.

3- در آرگومان Column Vumber، عدد 2 را وارد می کنیم. شماره ستون مورد نظر ما که در اینجا ستون D یعنی ستون دوم است (A و B و C یک ستون در نظر گرفته می شوند).

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

ترکیب فرمولی استفاده شده در اینجا به شکل زیر در می آید:

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

با فشردن کلید های CTRL+SHIFT+ENTER به صورت همزمان پس از کامل کردن فرمول درون سلول، فرمول را به صورت آرایه ای استفاده می کنیم.

روش3- تابع INDEX/MATCH و ستون کمکی

روش سوم برای جستجوی چند شرطی از فرمول INDEX / MATCH و یک ستون کمکی اضافی استفاده می کند.

ایده این روش ایجاد یک ستون کمکی است که در آن تمام مقادیر جستجو را بهم پیوند می دهد. برخلاف ستون کمکی روش 1 (VLOOKUP و ستون کمکی)، ستون کمکی را می توان در هرجای صفحه کار خود قرار داد.

فرمول در ستون کمکی (در اینجا: سلول E2) به صورت زیر است:

در این روش تابع معمول INDEX / Match را اعمال می کنیم با این تفاوت که ترکیبی از شرط ها را در بخش تابع MATCH، جستجو می کنیم. تابع مورد نظر در این روش به صورت زیر است.

پس از Enter کردن می نوانید خروجی تابع را مشاهده کنید.

روش 4- تابع INDEX/MATCH بدون ستون کمکی

گاهی ستون کمکی به معنای کار اضافی تلقی می شود و در بعضی موارد، می خواهیم داده های خام را دست نخورده نگه داریم. از ترکیب فرمول INDEX / MATCH می توان بدون درج ستون کمکی نیز استفاده کرد. مانند روش 2 ، فرمول INDEX / MATCH به صورت فرمول آرایه ای استفاده می شود.

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

ساختار فرمول چند شرطی INDEX / MATCH در تصویر زیر نشان داده شده است.

پارامتر های نامگذاری شده در تصویر به شرح زیر است:

1- “Cell Array” به محدوده بازگشتی اشاره دارد. در این مثال، مقدار برگشتی ما یعنی “فروش” در ستون D قرار دارد. به منظور صرفه جویی در زمان محاسبه، توصیه می شود به جای کل ستون از محدوده شامل داده استفاده کنید.

2- اولین آرگومان فرمول MATCH مقدار مورد جستجو (lookup value) است. مقادیر جستجوی متعدد را با استفاده ار کاراکتر & در یک آرگومان خلاصه می کنیم.

3- lookup array چندین محدوده جستجو را با علامت & ترکیب می کند. ممکن است بیش از سه محدوده جستجو وجود داشته باشد.

4- آخرین آرگومان فرمول MATCH نوع مطابقت را مشخص می کند. برای جستجوی چند شرطی، برای دستیابی به یک مطابقت دقیق، همیشه 0 است.

با استفاده از این ساختار به تابع زیر می رسیم.

پس از وازد کردن تابع بالا در سلول، با فشردن سه دکمه CTRL+SHIFT+ENTER به صورت همزمان می توانیم خروجی را بدست آوریم.