• مطالب آموزشی
    • تمام مطالب آموزشی
    • Excel
    • Power BI
    • DAX
  • دوره های آموزشی
    • کلاس اکسل پیشرفته
    • کلاس داشبورد با Power BI
    • کلاس DAX و مدلسازی داده‎
    • Power BI Report Server
    • گالری تصاویر
  • پکیج های آموزشی
    • اکسل
    • Power BI
    • SQL
    • تحلیل داده با پایتون
    • سبد خرید
  • معرفی
  • تماس
  • اینستاگرام
  ورود
گروه تحلیلگری
  • مطالب آموزشی
    • تمام مطالب آموزشی
    • Excel
    • Power BI
    • DAX
  • دوره های آموزشی
    • کلاس اکسل پیشرفته
    • کلاس داشبورد با Power BI
    • کلاس DAX و مدلسازی داده‎
    • Power BI Report Server
    • گالری تصاویر
  • پکیج های آموزشی
    • اکسل
    • Power BI
    • SQL
    • تحلیل داده با پایتون
    • سبد خرید
  • معرفی
  • تماس
  • اینستاگرام
0
ثبت نام / ورود

وبلاگ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=A2&”|”&B2&”|”&C2

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

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

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

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

=VLOOKUP(G7&”|”&H7&”|”&I7;$D$2:$E$13;2;0)

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

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

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

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

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

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

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

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

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

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

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

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

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

F9&G9&H9

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

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

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

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

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

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

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

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

=VLOOKUP(F9&G9&H9;CHOOSE({1;2};A2:A13&B2:B13&C2:C13;D2:D13);2;FALSE)

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

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

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

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

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

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

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

=A2&”|”&B2&”|”&C2

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

=INDEX(D:D;MATCH(G10&”|”&H10&”|”&I10;E:E;0))

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

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

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

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

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

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

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

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

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

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

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

F11&G11&H11

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

A2:A13&B2:B13&C2:C13

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

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

=INDEX(D2:D13;MATCH(F11&G11&H11;A2:A13&B2:B13&C2:C13;0))

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

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

درباره آمنه نوروزیان

دانش آموخته علم ژنتیک و بیوتکنولوژی دانشگاه تهران، علاقه مند به زیست محاسباتی و یادگیری مطالب جدید

نوشته های بیشتر از آمنه نوروزیان
در تلگرام
کانال ما را دنبال کنید!
در اینستاگرام
ما را دنبال کنید!
مطالب زیر را حتما بخوانید
  • ایجاد فرمت سفارشی در اکسل

    2.17k بازدید

  • کتاب مرجع اکسل پیشرفته ۲۰۱۹

    2.14k بازدید

  • مقایسه دو ستون در اکسل

    3.98k بازدید

  • کاربرد تابع offset در اکسل

    1.49k بازدید

  • کاربرد علامت دلار در اکسل

    4.04k بازدید

  • کاربرد علامت تعجب در اکسل

    2.52k بازدید

guest
تعداد ماههای سال به عدد
guest
8 نظر
قدیمی ها
جدید ها بیشترین رای
Inline Feedbacks
View all comments
سجاد ذالی
سجاد ذالی

بسیار جالب بود-با تشکر از شما

1
پاسخ
باده عشق
باده عشق

مفید و عالی

0
پاسخ
فاطمه فروتن
فاطمه فروتن

عااااااالی و فوق العاده کاربردی

0
پاسخ
علی
علی

خییییلی عالی توضیح دادین و خیلی بکار من اومد .سپاسگزارم
ولی دلیل Cntrl+Shift+Enter چیه و کجاها استفاده میشه غیر از این مورد ؟

پیشاپیش ممنونم از پاسختون

0
پاسخ
پوریا بغدادی
پوریا بغدادی
مدیر سایت
در پاسخ به  علی

این کلید های در زمانی که توابع بصورت آرایه ای نوشته می شوند کاربرد دارد.

0
پاسخ
صحرائی
صحرائی

بسیارعالی

0
پاسخ
سجاد
سجاد

سلام. به دوره های شما علاقه مندم. یه بدی که داره قیمتا خیلی بالاس. شاید این مبلغ برا شما زیاد نباشه، اما برا چنتا آدم که درآمد کاریشون پائینه و دوس دارن این دوره ها رو شرکت کنن، زیاد باشه. درسته خیلی زحمت کشیدین برا تهیه. اما خب هرچقدر قیمت پائین تر باشه مشتری بیشتری جذب میکنید.
من علاقه زیادی به آقای بغدادی و دوره هاشون دارم، اما واقعا با این حجم از فشار مالی، توان خرید ندارم

0
پاسخ
پوریا بغدادی
پوریا بغدادی
مدیر سایت
در پاسخ به  سجاد

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

0
پاسخ
جستجو
جستجو برای:
دسته بندی ها
  • Charts
  • DAX
  • Excel
  • Power BI
  • Power Query
  • هوش تجاری
بسته های آموزشی
  • Excel
  • Power BI
  • Python
  • SQL
درباره گروه تحلیلگری

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

اطلاعات تماس
  • تهران، خیابان مطهری، ابتدای خیابان سنایی، کوچه بیست و یکم
  • 543 30 910 - 021
پیوندها
  • آموزش Power BI
  • آموزش SQL
  • آموزش اکسل
دسته بندی محصولات
Excel Power BI Python SQL
نماد الکترونیک
wpDiscuz

ورود

رمز عبور را فراموش کرده اید؟

هنوز عضو نشده اید؟ عضویت در سایت