سبد خرید
0

سبد خرید شما خالی است.

جستجو
Generic filters

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

جستجو
نظر شما در مورد این آموزش

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

خیلی وقت ها پیش میاد که نیاز باشه جستجو رو بر اساس چند شرط انجام بدیم. مثلا اگر سه سلول از یک مجموعه داده با شروط مورد نظر ما برابر بود، داده مرتبط با اونو برگردونه. با وجود اینکه در اکسل برای جستجو در سطر ها و ستون ها توابع اختصاصی (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 با شرط های چندگانه جستجوی چند شرطی به روش آرایه ای

شکل ۱- ترکیب توابع 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

شکل ۲- 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 با شرط های چندگانه، جستجوی چند شرطی به روش غیر آرایه ای

شکل ۳- ترکیب توابع 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 با شرط های چندگانه در سطرها و ستون ها

شکل ۴- جستجوی چند شرطی – ترکیب توابع 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) رو با هم ترکیب کردیم که این قسمت از فرمول حالت آرایه ای داره. نحوه عملکرد این قسمت از فرمول به شکل زیر هست:

فرمول Lookup

با توجه به فرمول بالا، خروجی فرمول عدد ۴ خواهد بود.

با توجه به خروجی دو تابع MATCH تابع INDEX به شکل زیر در میاد:

=INDEX (B3:E5, 2,4)

برای رسیدن به نتیجه درست چون فرمول حالت آرایه ای داره باید بعد از نوشتن فرمول حتما دکمه های Ctrl+Shift+Enter رو بزنیم، که با توجه به شکل ۴ خروجی فرمول عدد ۴۱۰۰۰۰ هست.

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

تابع Index کاربردهای جالب دیگه ای هم داره که تو مقاله ۶ کاربرد فوق العاده از تابع INDEX در موردشون صحبت کردیم. همچنین  مطلب جستجو پیشرفته در اکسل با استفاده از Wildcard رو هم از دست ندید.

فارغ التحصیل لیسانس مهندسی صنایع، ارشد مدیریت صنعتی از دانشگاه تربیت مدرس و عاشق اکسل هستم. از سال 1388 که ترم 2 لیسانس بودم، به توصیه استاد مشاورم شروع به خوندن اکسل بصورت حرفه ای کردم و همچنان در حال مطالعه و یادگیری و البته آموزش به بقیه هستم.

دیدگاه کاربران
  • علی حامد ۲ شهریور ۱۴۰۰ / ۵:۲۱ ق٫ظ

    با سلام و تشکر از زحماتتون. بنده یک جدول ۱۳ در ۲۴ دارم و بر اساس یکی از ستون ها سورت رو انجام میدم. حالا اگر بخوام یه ستون دیگه که جزو جدولم نیست و دقیقا تعدادش برابر با ستون های جدولمه، همراه با این سورت جدول سورت بشه، باید چیکار کنم؟!

    • حسنا خاکزاد ۲ شهریور ۱۴۰۰ / ۱۲:۰۵ ب٫ظ

      درود بر شما
      بچسبونید به داده ها!
      در غیر اینصورت باید با فرمول سورت کنید

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان