جشنواره تابستانی اکسل پدیا (یک روز تا پایان)
سبد خرید
0

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

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

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

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

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

آواتار
127

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

دیدگاه کاربران
  • سینا ۵ بهمن ۱۴۰۰ / ۱۰:۰۴ ق٫ظ

    ببینید مثلا تو همین مثال ۲ فرض کنید یک ردیف دارین رنج سنی فروشندگان از عدد ۵ داریم تا ۲۵ و برامون این مهم شده که رنج ۲۰ تا ۲۴ سال چقدر فروختن
    تو match های قبلی عینا نوشتیم منطقه شرق و ماه خرداد و کالای ماکروفر و حالا میخایم بگیم و رنج سنی ۲۰ تا ۲۴ سال
    match این مورد چی میشه؟
    ممنون بابت تایمی که میذارین

    • آواتار
      حسنا خاکزاد ۵ بهمن ۱۴۰۰ / ۴:۱۰ ب٫ظ

      این که میفرمایید میشه جمع شرطی
      sumifs باید استفاده کنید
      و بقیه شرط ها رو هم بدید
      جون این مقاله برای پیدا کردن داده یونیک هست. یعنی حاصل اون جستجو ها ۱ مورد میشه که match میکنه

      بازه که باشه و جمع فروش، یعنی sumifs

  • سینا ۵ بهمن ۱۴۰۰ / ۸:۵۳ ق٫ظ

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

    • آواتار
      حسنا خاکزاد ۵ بهمن ۱۴۰۰ / ۹:۱۵ ق٫ظ

      درود
      کوچکتر بودن اینطوری نوشته میشه:
      A1<5

  • مصطفی عندلیب ۴ بهمن ۱۴۰۰ / ۸:۰۲ ق٫ظ

    با سلام
    من در ستون اول عنوان سال از سال ۱۳۹۰ تا ۱۴۰۰ دارم. در ستون دوم به ازای هریک از سالها ۱۲ ماه دارم که از سلول b2 تا b122 . از طرفی در سطر۱ از سلول c1 تا x1 هم عنوانهایی دارم .
    در این جدول ایجاد شده هم اعدادی وجود دارد که محل تقاطع عناوین است.
    حال میخواهم بصورت خودکار هر عنوان را که انتخاب میکنم اعداد مربوط به آن را برای من جستجو نماید .
    توضیحات شما خیلی مفید بود ولی در مثالهای شما عمومی یک سط یه ستون را ثابت نگهداشتید ولی در پرسش من هم سطر و هم ستونها کاملا متغیر می باشند.

    • آواتار
      حسنا خاکزاد ۴ بهمن ۱۴۰۰ / ۱:۱۸ ب٫ظ

      درود بر شما
      فکر کنم خواسته شما فراخوانی داده از یک جدول ماتریسی باشه
      برای این کار یک راه استفاده از ترکیب index, match هست مقاله زیر رو ببینید
      https://excelpedia.net/index-function/

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

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

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

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

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

توسط
تومان