
ترکیب چندین شرط برای جستجوی چند شرطی در اکسل
خیلی وقت ها پیش میاد که نیاز باشه جستجو رو بر اساس چند شرط انجام بدیم. مثلا اگر سه سلول از یک مجموعه داده با شروط مورد نظر ما برابر بود، داده مرتبط با اونو برگردونه. با وجود اینکه در اکسل برای جستجو در سطر ها و ستون ها توابع اختصاصی (VLOOKUP و HLOOKUP) تعریف شده، اما خیلی از کاربران از ترکیب توابع INDEX و MATCH به جای این توابع استفاده می کنند که در بسیاری از جهات نسبت به توابع VLOOKUP و HLOOKUP برتری دارن. به طور مثال با استفاده از ترکیب توابع INDEX و MATCH می تونیم جستجو رو با اعمال دو یا چند شرط در سطر ها و ستون ها انجام بدیم. در این مقاله قصد داریم نحوه ترکیب توابع INDEX و MATCH رو جهت انجام جستجوی چند شرطی با ارائه مثال های کاربردی توضیح بدیم.
توصیه می کنیم قبل از مطالعه این مقاله حتما مقالات مربوط به فرمول نویسی آرایه ای، تابع Match و Index رو مطالعه کنید تا بهتر بتونید از مطالب ارائه شده در این مقاله استفاده کنید.
مثال ۱ :ترکیب توابع INDEX و MATCH با شرط های چندگانه به روش آرایه ای
برای اینکه بخواهیم مقداری رو با اعمال چندین شرط در یک محدوده داده ای جستجو کنیم براساس الگوی زیر توابع MATCH و INDEX رو با هم ترکیب می کنیم:
{=INDEX (return_range, MATCH (1, (criteria1=range1) * (criteria2=range2) * (…), ۰))}
در فرمول بالا:
return_range: محدوده ای هست که مقدار مورد نظرمون در اون قرار گرفته؛
criteria1، criteria2 و………: شرط هایی هستند که باید بررسی بشن؛
range1، range2 و …….: محدوده هایی هستند که شرط ها در اون بررسی می شن.
فرمول بالا یک فرمول آرایه ای هست و برای فعال کردن فرمول باید بعد از نوشتن اون دکمه هایCtrl + Shift + Enter رو به طور همزمان بزنیم که باعث می شه فرمول بین دو تا آکولاد قرار بگیره. دقت داشته باشید که یکی از علامت های اینکه یک فرمول آرایه ای هست وجود آکولاد در ابتدا و انتهای فرمول هست.
برای روش شدن نحوه عملکرد فرمول مثال شکل ۱ رو بررسی می کنیم.
شکل ۱- ترکیب توابع MACH و INDEX با شرط های چندگانه، جستجوی چند شرطی به روش آرایه ای
می خواهیم براساس شرط های نوشته شده در سلول های G1، G2 و G3 ، مقدار فروش رو در ستون D جستجو کنیم. با توجه به شرط های تعریف شده و محدوده داده ها، فرمول به شکل زیر خواهد بود:
{=INDEX (D2:D15, MATCH (1, (G1=A2:A15) * (G2=B2:B15) * (G3=C2:C15), 0))}
نحوه عملکرد فرمول:
مهمترین و بحث برانگیز ترین قسمت در فرمول بالا مربوط به تابع MATCH هست. که اول این قسمت رو توضیح میدیم:
MATCH (1, (G1=A2:A15) * (G2=B2:B15) * (G3=C2:C15), 0)
همانطور که میدونیم آرگومان های تابع MATCH به صورت زیر هست:
MATCH (lookup_value, lookup_array, [match_type])
تابع MATCH محل قرار گرفتن lookup_value رو در یک محدوده (lookup_array) مشخص می کنه. در این مثال آرگومان های تابع MATCH به شکل زیر هست:
lookup_value: 1
Lookup_array: (G1=A2:A15) * (G2=B2:B15) * (G3=C2:C15)
match_type: 0
آرگومان اول که مشخص هست یعنی عدد یک. آرگومان سوم هم صفر هست که یعنی فرمول اولین مقداری که دقیقا با lookup_value برابر باشه رو نمایش میده. اما نکته اصلی فرمول این هست که چرا lookup_value رو عدد یک قرار دادیم؟ برای پیدا کردن جواب این سوال باید اول Lookup_array رو بررسی کنیم. در Lookup_array هر شرط رو در محدوده مربوط به خودش بررسی می کنیم یعنی مقدار نوشته شده در سلول G1 رو که مربوط به “منطقه” هست با محدوده A2:A15 مقایسه می کنیم و به همین ترتیب مقدار نوشته شده در سلول G2 رو در محدوده B2:B15 و مقدار سلول G3 رو در محدوده C2:C15 کنترل می کنیم. نتیجه مقایسه مقادیر سلول های G1،G2 و G3 در محدوده های مشخص شده به شکل زیر خواهد بود:
شکل ۲- Lookup_array در تابع MATCH
برای اینکه عملکرد هر قسمت از فرمول رو بینیم کافیه قسمت مورد نظر در فرمول رو انتخاب کنیم و بعد دکمه F9 رو بزنیم.
علامت ضربی که در فرمول وجود داره، عبارت های TRUE رو به عدد یک و عبارت های FALSE رو به عدد صفر تبدیل می کنه به صورت زیر:
{۱;۱;۱;۱;۱;۱;۰;۰;۰;۰;۰;۰} * {۰;۰;۱;۱;۰;۰;۰;۰;۱;۱;۰;۰} * {۱;۰;۱;۰;۱;۰;۱;۰;۱;۰;۱;۰}
با توجه به اینکه ضرب هر عددی در صفر حاصلش صفر هست پس عدد یک تنها در جایی نمایش داده میشه که تمام شرط ها در اون صادق باشه که براساس آرایه زیر که حاصل ضرب سه ماتریس بالا هست عدد یک در سطر سوم ظاهر شده.
{۰;۰;۱;۰;۰;۰;۰;۰;۰;۰;۰;۰}
آرایه بالا در واقع Lookup_array تابع MATCH هست و عدد یک رو هم برای این در قسمت lookup_value تعریف کردیم که سطری رو می خواهیم پیدا کنیم که همه سه شرط در اون برقرار باشه. با توجه به آرگومان های تابع MATCH خروجی اون برابر عدد ۳ هست که در واقع همون موقعیت عدد یک در آرایه بالا رو نشون میده.
خروجی تابعMATCH (عدد ۳) به عنوان آرگومان row_num در تابع INDEX تعریف شده و تابع INDEX به شکل زیر در میاد:
=INDEX (D2:D15, 3)
که با توجه به جدول شکل ۱ خروجی فرمول عدد ۳۵.۰۰۰.۰۰۰ خواهد بود.
سوالی که پیش میاد اینه که اگر چند ردیف بود که با شروط ما برابر بود و در واقع خروجی Lookup_Array چندتا عدد ۱ میشد. اونموقع دیگه تابع Match نمیتونه پاسخگو باشه . چون همیشه اولین مورد رو بر میگردونه. برای حل این مسئله باید این منطق رو با منطق جستجوی موارد تکراری ترکیب کنیم. مقاله جستجوی موارد تکراری رو حتما مطالعه کنید.
مثال ۲ : ترکیب توابع INDEX و MATCH با شرط های چندگانه با (جستجوی چند شرطی به روش غیر آرایه ای)
استفاده از فرمول نویسی آرایه ای خیلی حرفه ای و خوش آیند هست اما اگر کاربرای دیگه ای که با فرمول نویسی آرایه ای آشنایی ندارن، در فرمول تغییر ایجاد کنن و بعد به جای دکمه Ctrl+Shift+Enter فقط دکمه Enter رو بزنن فرمول نتیجه درستی رو نمایش نخواهد داد. در این مواقع می تونیم از روش غیر آرایه ای هم برای فرمول نویسی استفاده کنیم. الگوی ترکیب توابع MATCH و INDEX در حالت غیر آرایه ای به صورت زیر هست:
INDEX (return_range, MATCH (1, INDEX((criteria1=range1) * (criteria2=range2) * (..), ۰, ۱), ۰))
با توجه به جدول شکل ۱ فرمول به صورت زیر نوشته میشه:
=INDEX (D2:D15, MATCH (1, INDEX((G1=A2:A15) *(G2=B2:B15) *(G3=C2:C15),۰,۱),۰))
نتیجه استفاده از فرمول در شکل زیر نمایش داده شده:
شکل ۳- ترکیب توابع MACH و INDEX با شرط های چندگانه، جستجوی چند شرطی به روش غیر آرایه ای
نحوه عملکرد فرمول:
تابع Index علاوه بر اینکه یک سلول حاصل تقاطع یک شماره ردیف و ستون رو بر میگردونه، میتونه عملیاتی رو روی یک ردیف یا یک ستون مشخص هم انجام بده. در فرمول بالا تابعINDEX اول حاصل تقاطع یک ردیف و ستون رو بر می گردونه و تابع INDEX دوم یک محدوده رو برمی گردونه. در واقع تابع INDEX دوم محدوده حاصل از ضرب سه آرایه ی دیگر رو نمایش میده که بعد از ضرب مقادیر یک و صفر تابع INDEX به صورت زیر میاد:
INDEX ({0;0;0;0;0;1;0;0;0;0;0;0;0;0},0,1)
وقتی آرگومان row_num رو خالی یا ۰ میذاریم، چون ردیفی رو مشخص نمیکنیم، پس همه ردیف ها (یعنی یک ستون) در نظر گرفته میشه و خروجی تابع INDEX در این حالت آرایه ای با یک ستون و ۱۴ ردیف (به تعداد ردیف های جدول شکل ۳) خواهد بود.
{۰;۰;۰;۰;۰;۱;۰;۰;۰;۰;۰;۰;۰;۰}
این آرایه ورودی تابع MATCH هست و به جای آرگومان lookup_array قرار می گیره و تابع MATCH به شکل زیر در میاد:
MATCH (1, {0;0;0;0;0;1;0;0;0;0;0;0;0;0},0)
تابع MATCH موقعیت عدد یک رو در آرایه تعیین می کنه که خروجی اون ۶ هست یعنی در ردیف ۶ تمامی شرط ها برقرار هست. خروجی تابع MATCH ورودی تابع INDEX اولی خواهد بود که به شکل زیرهست:
= INDEX (D2:D15, 6)
و نهایتا مطابق شکل ۳ خروجی فرمول عدد ۳.۰۰۰.۰۰۰ خواهد بود.
مثال ۳ :ترکیب توابع INDEX و MATCH با شرط های چندگانه در سطرها و ستون ها
در این مثال نحوه جستجو با استفاده از کنترل چندین شرط در سطر ها و ستون ها رو توضیح می دیم. این حالت جستجو و بکار گیری توابع MATCH و INDEX نسبت به دو حالت قبل پیچیده تر هست. الگوی ترکیب توابع در این مثال به شکل زیر هست:
{=INDEX (table_array, MATCH (vlookup_value, lookup_column, ۰), MATCH (hlookup_value1 & hlookup_value2, lookup_row1 & lookup_row2, ۰))}
برای روشن شدن نحوه استفاده از فرمول، مثال شکل ۴ رو بررسی می کنیم. مطابق شکل ۴ می خواهیم براساس شرط های مشخص شده یعنی منطقه، فروشنده و نام کالا میزان فروش رو بدست بیاریم. در واقع می خواهیم با استفاده از تابع INDEX تقاطع سطر “نام کالا” رو با ستون “منطقه-فروشنده” به دست بیاریم. فرمول مورد استفاده در این مثال در شکل ۴ نمایش داده شده.
شکل ۴- جستجوی چند شرطی – ترکیب توابع MACH و INDEX با شرط های چندگانه در سطرها و ستون ها
نحوه عملکرد فرمول:
در این مثال از تابع INDEX با آرگومان های زیر استفاده می کنیم:
=INDEX (array, row_num, column_num)
برای تعیین شماره سطر (row_num) با استفاده از تابع MATCH شرط مربوط به “نام کالا” رو با استفاده از فرمول زیر بررسی می کنیم:
=MATCH (B9, A3:A5,0)
که خروجی این فرمول عدد ۲ هست.
برای تعیین شماره ستون (column_num) هم از تابع MATCH استفاده می کنیم که این بخش از فرمول مهم ترین قسمت فرمول هست.
=MATCH (B7&B8, B1:E1 & B2:E2,۰)
در این فرمول دو تا look up value (سلول های B7 و B8) و دو محدوده (B1:E1 و B2:E2) رو با هم ترکیب کردیم که این قسمت از فرمول حالت آرایه ای داره. نحوه عملکرد این قسمت از فرمول به شکل زیر هست:
با توجه به فرمول بالا، خروجی فرمول عدد ۴ خواهد بود.
با توجه به خروجی دو تابع MATCH تابع INDEX به شکل زیر در میاد:
=INDEX (B3:E5, 2,4)
برای رسیدن به نتیجه درست چون فرمول حالت آرایه ای داره باید بعد از نوشتن فرمول حتما دکمه های Ctrl+Shift+Enter رو بزنیم، که با توجه به شکل ۴ خروجی فرمول عدد ۴۱۰۰۰۰ هست.
در این مقاله نحوه ترکیب توابع MATCH و INDEX رو برای جستجو مقادیر مختلف (جستجوی چند شرطی) در قالب چند مثال بررسی کردیم و همچنین با استفاده از فرمول نویسی آرایه ای و ترکیب توابع MATCH و INDEX جستجوی مقادیر در یک محدوده رو توضیح دادیم. در هر مثال هم سعی شد نحوه عملکرد فرمول (به خصوص در حالت های آرایه ای) به طور کامل توضیح داده بشه. با توجه به اینکه تابع INDEX در دو حالت نوشته می شه، در این مقاله برای هر دو حالت تابع INDEX هم مثال ارائه شده.
تابع Index کاربردهای جالب دیگه ای هم داره که تو مقاله ۶ کاربرد فوق العاده از تابع INDEX در موردشون صحبت کردیم. همچنین مطلب جستجو پیشرفته در اکسل با استفاده از Wildcard رو هم از دست ندید.
با سلام و تشکر اگر داده های ما تکراری باشند و بخواهیم آخرین قیمت را برای ما نشان دهد این شرط (آخرین ) را در فرمول های بالا چجوری جایگذاری می کنیم
با سلام و احترام
اگر در لیست بالا دو ردیف داشتیم که عین هم بودن مثلا دوتا شرق دو تا اردیبهشت و دوتا ماکروویو اما با قیمت متفاوت اگر بخواهیم همین شرط برقرار باشه ولی آخرین قیمت ثبت شده را به ما نمایش بده چجوری فرمول می نویسیم ممنون میشم راهنمایی کنید
درود
خروجی رو وقتی که شرط ها میت شد متناظر با شمره ردیف قرار بدید و بعد یک سری عدد دارید که میتونید بزرگترینش رو بیارید بیرون و بذارید داحل تابع index
با سلام
شما امکان آموزش آنلاین هم دارید؟ هفته ای ۲ ساعت؟ من با اکسل زیاد کار میکنم با توجه به شغلم. نیاز ندارم از صفر شروع کنم. من کیس هایی در طول هفته برام پیش میاد که اونا رو میخوام طی این آموزش و مشاوره دو ساعته از شما بپرسم و راه حل ها رو برام راهنمایی بفرمائید. هزینه رو هم پرداخت می کنم. اگر امکان هست لطف کنید برای جزئیات از طریق ایمیل اطلاع بدهید. ممنون
سلام
جهت هماهنگی به شماره واتس اپ ۰۹۳۶۵۸۸۴۳۳۲ پیام بدید.
ببینید مثلا تو همین مثال ۲ فرض کنید یک ردیف دارین رنج سنی فروشندگان از عدد ۵ داریم تا ۲۵ و برامون این مهم شده که رنج ۲۰ تا ۲۴ سال چقدر فروختن
تو match های قبلی عینا نوشتیم منطقه شرق و ماه خرداد و کالای ماکروفر و حالا میخایم بگیم و رنج سنی ۲۰ تا ۲۴ سال
match این مورد چی میشه؟
ممنون بابت تایمی که میذارین
این که میفرمایید میشه جمع شرطی
sumifs باید استفاده کنید
و بقیه شرط ها رو هم بدید
جون این مقاله برای پیدا کردن داده یونیک هست. یعنی حاصل اون جستجو ها ۱ مورد میشه که match میکنه
بازه که باشه و جمع فروش، یعنی sumifs
سلام وقت بخیر
من تقریبا کارم مشابه مثال دوم هست فقط یکی از شرطهایی که دارم اینه که از یک عبارتی کوچک تر باشه
چجوری میتونم بنویسمش؟
درود
کوچکتر بودن اینطوری نوشته میشه:
A1<5
با سلام
من در ستون اول عنوان سال از سال ۱۳۹۰ تا ۱۴۰۰ دارم. در ستون دوم به ازای هریک از سالها ۱۲ ماه دارم که از سلول b2 تا b122 . از طرفی در سطر۱ از سلول c1 تا x1 هم عنوانهایی دارم .
در این جدول ایجاد شده هم اعدادی وجود دارد که محل تقاطع عناوین است.
حال میخواهم بصورت خودکار هر عنوان را که انتخاب میکنم اعداد مربوط به آن را برای من جستجو نماید .
توضیحات شما خیلی مفید بود ولی در مثالهای شما عمومی یک سط یه ستون را ثابت نگهداشتید ولی در پرسش من هم سطر و هم ستونها کاملا متغیر می باشند.
درود بر شما
فکر کنم خواسته شما فراخوانی داده از یک جدول ماتریسی باشه
برای این کار یک راه استفاده از ترکیب index, match هست مقاله زیر رو ببینید
https://excelpedia.net/index-function/
با سلام و تشکر از زحماتتون. بنده یک جدول ۱۳ در ۲۴ دارم و بر اساس یکی از ستون ها سورت رو انجام میدم. حالا اگر بخوام یه ستون دیگه که جزو جدولم نیست و دقیقا تعدادش برابر با ستون های جدولمه، همراه با این سورت جدول سورت بشه، باید چیکار کنم؟!
درود بر شما
بچسبونید به داده ها!
در غیر اینصورت باید با فرمول سورت کنید