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

جستجوی چند شرطی در اکسل، یکی از مباحث بسیار مهم و کاربردی در کار با داده‌ها در اکسل است. در جستجوی «تک شرطی»، که با استفاده از توابعی مانند «VLOOKUP» و ترکیب توابع «INDEX» و «MATCH» انجام می‌شود، یک مقدار دلخواه بر اساس یک معیار مشخص در یک جدول جستجو شده و مقدار متناظر با آن در ردیف دلخواه برمی‌گردد. در‌حالیکه در جستجوی چند شرطی، همان‌گونه که از اسم آن بر‌می‌آید، جستجو به ازای چند شرط انجام می‌شود. در ادامه این مقاله از مجله فرادرس، با نحوه جستجوی چند شرطی در اکسل با استفاده از توابع INDEX و MATCH، تابع «DGET» و همچنین فرمول‌نویسی آرایه‌ای در اکسل آشنا می‌شویم.

جستجوی چند شرطی با تابع INDEX و MATCH در اکسل

در مجموعه مقالات آموزش اکسل مجله فرادرس، به «توابع INDEX و MATCH در اکسل» پرداختیم و با نحوه استفاده از ترکیب دو تابع INDEX و MATCH در اکسل، برای جستجوی تک شرطی آشنا شدیم. در اینجا با طرح یک مثال، نحوه استفاده از این دو تابع برای جستجوی چند شرطی را می‌آموزیم.

آرگومان‌های تابع MATCH عبارتند از:

1. lookup_value : مقداری که می‌خواهیم شماره موقعیت آن دریک ستون برگردانده شود.
2. lookup_array : محدوده ستون مورد نظر.
3. [match_type] : این آرگومان میتواند ۰، ۱ و یا -۱ انتخاب شود. (برای انطباق کامل جستجو این آرگومان باید ۰ وارد شود)

آرگومان های تابع INDEX عبارتند از:

1. array : محدوده مورد نظر برای برگرداندن مقدار
2. row_num : شماره ردیف داده مورد نظر
3. col_num : شماره ستون داده مورد نظر

منطق استفاده از این دو تابع در جستجوی چند شرطی به این صورت است که ابتدا با استفاده از تابع MATCH، شماره موقعیت یک داده خاص را در یک محدوده ستونی به دست می‌آوریم. سپس خروجی این فرمول که یک عدد است را به عنوان ورودی آرگومان دوم تابع INDEX آن استفاده می‌کنیم. به طور مثال در فرمول INDEX(A1:A10,5) در محدوده ستونی A1 تا A10، داده موجود در پنجمین سلول برمی‌گردد. حال فرض کنید جدولی مطابق شکل زیر از یک سری کالا، در اینجا لباس، که شامل کد کالا، نام لباس، سایز و قیمت آن است را در اختیار داریم.

همان‌طور که مشاهده می‌شود، در این جدول، چند مدل لباس با سایزهای مختلف وجود دارد. به عنوان مثال «ژاکت» دارای سه سایز (کوچک) «S»، (متوسط) «M» و (بزرگ) «L» است. می‌خواهیم با استفاده از توابع INDEX و MATCH، قیمت «ژاکت» با سایز «L» را جستجو کرده و مشخص کنیم. برای این کار باید با جستجوی چند شرطی در اکسل آشنا باشیم.

همان‌طور که مشخص است، برای این جستجو دو شرط «ژاکت» و سایز آن یعنی «L» در نظر گرفته شده است. به همین خاطر با جستجوی «تک شرطی»، مثلا برای یافتن قیمت ژاکت به تنهایی، که به سادگی با VLOOKUP یا ترکیب دو تابع INDEX و MATCH قابل انجام بود، متفاوت است. برای حل این مسئله، ابتدا با ایجاد دو ستون جدید، مسئله را تبیین می‌کنیم و سپس با مفهوم فرمول‌نویسی آرایه‌ای آن را توضیح می‌دهیم.

ابتدا در یک محدوده دلخواه، جدول معیارهای جستجو را ایجاد می‌کنیم. ستون‌های این جدول شامل شرط‌های مورد نظر ما و ستونی است که می‌خواهیم مقدار آن به ازای آن شرط‌ها برگردادنده شود. نام ستون‌های ایجاد شده باید عینا مطابق نام ستون‌های جدول اصلی باشد. در این مثال این جدول را در محدوده «H4» تا «J5» ایجاد کرده‌ایم.

حال ستون جدیدی «E»، با یک شرط منطقی، مطابق شرط اول مورد نظر ما یعنی «ژاکت» بودن کالا ایجاد می‌کنیم. «ژاکت» در جدول معیارهای جستجو در سلول «H5» قرار دارد. همان‌طور که در تصویر مشخص است، به ازای فرمول =$H$5=B2 هر جا که نام کالا برابر سلول «H5» یعنی «ژاکت» باشد، شرط (درست) «TRUE» و هر جا که این شرط اعمال نشود (غلط) یا «FALSE» نمایش داده شده است.

همین کار را در ستون جدید «F» برای شرط دوم یعنی سایز «L» نیز انجام می‌دهیم که نتیجه مطابق شکل زیر است.

از آنجاییکه می‌خواهیم هر دوی این شرط‌ها برقرار باشد، ستون جدیدی از حاصل‌ضرب این دو ستون ایجاد می‌کنیم. همان‌طور که مشخص است، هر جا که هر دو شرط‌ درست باشد، حاصل ضرب دو ستون برابر «۱» و در غیر این صورت برابر «۰» شده است.

حال کافی است با استفاده از تابع INDEX و MATCH، قیمت ژاکت با سایز L را اینگونه مشخص کنیم:

=INDEX(D2:D11,MATCH(1,G2:G11,0))

در فرمول بالا، ستون حاوی مقادیر قیمت (ستون D)، ستونی است که مقدار سلول مقابل آن در ستون G، برابر با یک می‌شود. به عبارت دیگر، در این ستون، هر دو شرط مورد نظر ما برقرار است. نتیجه این فرمول در تصویر زیر مشخص شده و قیمت ژاکت با سایز L را به درستی «۵۰» نمایش می‌دهد.

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

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

مطابق شکل بالا، به جای ایجاد دو ستون جدید که حاوی مقادیر TRUE و FALSE به ازای شرط‌های مورد نظر ما بود، فرمول‌های منطقی متناظر، در تابع MATCH و INDEX به صورت یک‌جا نوشته شده‌اند. همچنین دقیقا مثل روش حل اول، این دو مقدار در هم ضرب شده و آرگومان اول تابع MATCH نیز «۱» وارد شده است. بنابراین تا اینجا منطق فرمول‌نویسی تغییری نداشته و فرمول‌ها صرفا به جای نوشتن در ستون‌های جدا، یک‌جا نوشته شده‌اند.

نکته بسیار مهم در اجرای فرمول‌نویسی آرایه‌ای این است که به جای فشردن کلید «Enter» به منظور اجرای فرمول، باید از کلید ترکیبی «Ctrl+Shift+Enter» برای اجرا استفاده کرد. همچنین در نوار فرمول، کل فرمول نوشته شده باید درون آکلاد «{ }» قرار گیرد.

جستجوی چند شرطی با استفاده از تابع DGET در اکسل

تابع «DGET» یکی دیگر از توابعی است که کار جستجوی چند شرطی را انجام می‌دهد. در ادامه با ساختار این تابع و نحوه استفاده از آن آشنا می‌شویم. ساختار تابع DGET به صورت زیر است:

=DGET(database,field,criteria)

همان‌طور که مشخص است این تابع سه آرگومان دارد:

1. database : محدوده داده‌هایی که قرار است در داخل آن عملیات جستجو انجام شود.
2. field : نام ستونی که مقدار آن به عنوان خروجی مد نظر ماست.
3. criteria : محدوده شرط، یا شرط‌هایی که می‌خواهیم اعمال شود.

در مثال ارائه شده در بخش قبل، می‌توان با استفاده از فرمول زیر به نتیجه دلخواه یعنی قیمت ژاکت با سایز L دست یافت.

=DGET(A1:D11,D1,F4:G5)

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

در فرمول بالا، آرگومان اول یا همان دیتابیس، محدوده‌ای است که داده‌های ما در آن وارد شده‌اند. آرگومان دوم، یا همان field، نام ستون مورد نظر ما در آن دیتابیس (قیمت) است. آرگومان سوم نیز محدوده شرط مورد نظر ما را دربرمی‌گیرد.

همان‌طور که در تصویر مشخص است، محدوده شرط در این تابع این امکان را به ما می‌دهد که چند شرط را به تابع DGET معرفی کنیم. فقط باید نام ستونهای موجود در دیتابیس (آرگومان اول) را عینا در این محدوده تکرار کنیم و همچنین مقادیر شرط را نیز عینا مطابق رشته متنی موجود در دیتابیس بنویسیم. مثلا در محدوده شرط در فرمول بالا، که همان «F4» تا «G5» است، عبارت‌های «نام کالا»، «سایز» و «قیمت» که نام ستو‌های جدول اصلی نیز هستند، عینا در جدول کوچک دوم تکرار شده‌اند. همچنین عبارت‌های «ژاکت» و «L» نیز زیر آنها آورده شده‌اند و فرمول، در خانه خالی زیر «قیمت» وارد شده است.

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

در این بخش به چند سوال متداول در مورد جستجوی چند شرطی در اکسل پاسخ می‌دهیم.

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

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

تابع INDEX در اکسل چیست؟

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

تابع MATCH در اکسل چیست؟

تابع MATCH شماره موقعیت یک داده مشخص در یک ستون را مشخص می‌کند.

کاربرد ترکیب دو تابع INDEX و MATCH در اکسل چیست؟

ترکیب دو تابع INDEX و MATCH برای جستجوی شرطی کاربرد دارد. در واقع با استفاده از تابع MATCH شماره موقعیت شرط مورد نظر به عنوان ورودی تابع INDEX، برای برگرداندن داده مورد نظر استفاده می‌شود.

تابع DGET در اکسل چیست؟

تابع DGET برای برگرداندن یک مقدار دلخواه از یک ستون مشخص، به ازای شرط یا شرط‌های دلخواه کاربرد دارد.

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

جستجوی چند شرطی با استفاده از توابع INDEX، MATCH و همچنین تابع DGET در اکسل انجام می‌شود.