چگونه یک فرمول جستجوی اکسل با چندین معیار ایجاد کنیم

2024 نویسنده: Abigail Brown | [email protected]. آخرین اصلاح شده: 2024-01-07 19:02

چه باید بدانیم

• ابتدا یک تابع INDEX ایجاد کنید، سپس تابع MATCH تودرتو را با وارد کردن آرگومان Lookup_value شروع کنید.
• بعد، آرگومان Lookup_array به دنبال آرگومان Match_type را اضافه کنید، سپس محدوده ستون را مشخص کنید.
• سپس، با فشار دادن Ctrl+ Shift+ Enter، تابع تودرتو را به فرمول آرایه تبدیل کنید.. در نهایت، عبارات جستجو را به کاربرگ اضافه کنید.

این مقاله نحوه ایجاد فرمول جستجو را توضیح می دهد که از چندین معیار در اکسل برای یافتن اطلاعات در پایگاه داده یا جدول داده ها با استفاده از فرمول آرایه استفاده می کند.فرمول آرایه شامل تودرتو کردن تابع MATCH در داخل تابع INDEX است. اطلاعات اکسل برای مایکروسافت 365، اکسل 2019، اکسل 2016، اکسل 2013، اکسل 2010 و اکسل برای مک را پوشش می‌دهد.

همراه با آموزش را دنبال کنید

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

• بازه بالایی داده ها را در سلول های D1 تا F2 وارد کنید.
• بازه دوم را در سلول های D5 تا F11 وارد کنید.

ایجاد یک تابع INDEX در اکسل

عملکرد INDEX یکی از معدود توابع در اکسل است که چندین فرم دارد. تابع دارای یک فرم آرایه و یک فرم مرجع است. فرم آرایه داده ها را از یک پایگاه داده یا جدول داده ها برمی گرداند.فرم مرجع مرجع سلول یا مکان داده ها در جدول را نشان می دهد.

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

برای ایجاد تابع INDEX مراحل زیر را دنبال کنید:

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

2. برو به Formulas.

   

3. Lookup & Reference را برای باز کردن لیست کشویی تابع انتخاب کنید.
4. INDEX را انتخاب کنید تا کادر گفتگوی Select Arguments را باز کنید.
5. آرایه، ردیف_شماره، ستون_تعداد را انتخاب کنید.
6. OK را انتخاب کنید تا کادر محاوره ای Function Arguments باز شود. در اکسل برای مک، فرمول ساز باز می شود.
7. مکان نما را در کادر متنی Array قرار دهید.

8. سلول های D6 تا F11 را در کاربرگ برجسته کنید تا محدوده را در کادر محاوره ای وارد کنید.

   کادر گفتگوی Function Arguments را باز بگذارید. فرمول تمام نشده است فرمول را در دستورالعمل‌های زیر تکمیل می‌کنید.

   

شروع عملکرد Nested MATCH

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

هنگام وارد کردن دستی توابع، آرگومان های تابع با کاما از یکدیگر جدا می شوند.

اولین مرحله برای وارد کردن تابع MATCH تودرتو، وارد کردن آرگومان Lookup_value است. Lookup_value محل یا مرجع سلولی است برای عبارت جستجو که باید در پایگاه داده مطابقت داده شود.

Lookup_value فقط یک معیار یا عبارت جستجو را می پذیرد. برای جستجوی چند معیار، Lookup_value را با الحاق یا پیوستن دو یا چند مرجع سلول با استفاده از علامت علامت (&) گسترش دهید.

1. در کادر محاوره ای Function Arguments ، مکان نما را در کادر متنی Row_num قرار دهید.
2. MATCH(. را وارد کنید
3. سلول D3 را انتخاب کنید تا مرجع آن سلول را در کادر محاوره ای وارد کنید.
4. & (امپرسند) را بعد از مرجع سلول D3 برای افزودن مرجع سلول دوم وارد کنید.
5. سلول E3 را برای وارد کردن مرجع سلول دوم انتخاب کنید.

6. را وارد کنید،(یک کاما) بعد از مرجع سلول E3 برای تکمیل ورودی آرگومان Lookup_value تابع MATCH.

   

   در آخرین مرحله آموزش، Lookup_values در سلول‌های D3 و E3 کاربرگ وارد می‌شود.

عملکرد Nested MATCH را کامل کنید

این مرحله اضافه کردن آرگومان Lookup_array برای تابع MATCH تودرتو را پوشش می دهد. Lookup_array محدوده سلول هایی است که تابع MATCH برای یافتن آرگومان Lookup_value اضافه شده در مرحله قبلی آموزش جستجو می کند.

از آنجایی که دو فیلد جستجو در آرگومان Lookup_array شناسایی شد، همین کار باید برای آرایه_Lookup انجام شود. تابع MATCH فقط یک آرایه را برای هر عبارت مشخص شده جستجو می کند. برای وارد کردن چند آرایه، از علامت علامت برای به هم پیوستن آرایه ها به یکدیگر استفاده کنید.

1. مکان نما را در انتهای داده ها در کادر متنی Row_num قرار دهید. مکان نما بعد از کاما در انتهای ورودی فعلی ظاهر می شود.

2. سلول های D6 تا D11 را در کاربرگ برای ورود به محدوده برجسته کنید. این محدوده اولین آرایه ای است که تابع جستجو می کند.

3. & (یک آمپرسند) را بعد از مراجع سلول D6:D11 وارد کنید. این نماد باعث می شود که تابع دو آرایه را جستجو کند.
4. سلول های E6 تا E11 را در کاربرگ برای وارد کردن محدوده برجسته کنید. این محدوده دومین آرایه ای است که تابع جستجو می کند.

5. ، (یک کاما) را بعد از مرجع سلول E3 وارد کنید تا ورودی آرگومان Lookup_array تابع MATCH را تکمیل کنید.

   

6. کادر گفتگو را برای مرحله بعدی آموزش باز بگذارید.

Add the MATCH Type Argument

سومین و آخرین آرگومان تابع MATCH آرگومان Match_type است. این آرگومان به اکسل می گوید که چگونه Lookup_value را با مقادیر موجود در Lookup_array مطابقت دهد. گزینه های موجود 1، 0 یا -1 هستند.

این آرگومان اختیاری است. اگر حذف شود، تابع از مقدار پیش فرض 1 استفاده می کند.

• اگر Match_type=1 باشد یا حذف شود، MATCH بزرگترین مقدار را پیدا می کند که کمتر یا مساوی با Lookup_value است. داده های Lookup_array باید به ترتیب صعودی مرتب شوند.
• اگر Match_type=0 باشد، MATCH اولین مقداری را پیدا می کند که برابر با Lookup_value است. داده های Lookup_array را می توان به هر ترتیبی مرتب کرد.
• اگر Match_type=-1 باشد، MATCH کوچکترین مقداری را پیدا می کند که بزرگتر یا مساوی با Lookup_value است. داده های Lookup_array باید به ترتیب نزولی مرتب شوند.

این مراحل را بعد از کاما وارد شده در مرحله قبل در خط Row_num در تابع INDEX وارد کنید:

1. 0 (صفر) را بعد از کاما در کادر متنی Row_num وارد کنید. این عدد باعث می شود که تابع تو در تو مطابقت های دقیقی را با عبارت های وارد شده در سلول های D3 و E3 برگرداند.

2. ) (یک براکت دور بسته) را برای تکمیل تابع MATCH وارد کنید.

   

3. کادر گفتگو را برای مرحله بعدی آموزش باز بگذارید.

تمام تابع INDEX

عملکرد MATCH انجام شد. وقت آن است که به کادر متنی Column_num در کادر محاوره ای بروید و آخرین آرگومان را برای تابع INDEX وارد کنید. این آرگومان به اکسل می گوید که شماره ستون در محدوده D6 تا F11 است. این محدوده جایی است که اطلاعات برگردانده شده توسط تابع را پیدا می کند.در این مورد، یک تامین کننده برای ویجت های تیتانیوم.

1. مکان نما را در کادر متنی Column_num قرار دهید.

2. 3 (عدد سه) را وارد کنید. این عدد به فرمول می گوید که به دنبال داده در ستون سوم محدوده D6 تا F11 باشد.

   

3. کادر گفتگو را برای مرحله بعدی آموزش باز بگذارید.

ایجاد فرمول آرایه

قبل از بستن کادر محاوره ای، تابع تودرتو را به فرمول آرایه تبدیل کنید. این آرایه به تابع اجازه می دهد تا چندین عبارت را در جدول داده ها جستجو کند. در این آموزش، دو عبارت با هم تطبیق داده شده اند: ویجت ها از ستون 1 و تیتانیوم از ستون 2.

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

1. OK را برای بستن کادر محاوره ای انتخاب کنید. در Excel برای Mac، Done را انتخاب کنید.
2. سلول F3 را برای مشاهده فرمول انتخاب کنید، سپس مکان نما را در انتهای فرمول در نوار فرمول قرار دهید.
3. برای تبدیل فرمول به آرایه، CTRL+ SHIFT+ ENTER را فشار دهید.
4. A N/A خطا در سلول F3 ظاهر می شود. این سلولی است که تابع در آن وارد شده است.

5. خطای N/A در سلول F3 ظاهر می شود زیرا سلول های D3 و E3 خالی هستند. D3 و E3 سلول هایی هستند که تابع برای یافتن Lookup_value در آنها جستجو می کند. پس از افزودن داده به این دو سلول، خطا با اطلاعات پایگاه داده جایگزین می شود.

   

افزودن معیارهای جستجو

آخرین مرحله افزودن عبارات جستجو به کاربرگ است. این مرحله با عبارت ابزارک از ستون 1 و تیتانیوم از ستون 2 مطابقت دارد.

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

1. انتخاب سلول D3.
2. Widgets را وارد کنید.
3. انتخاب سلول E3.
4. Titanium را تایپ کنید و Enter را فشار دهید.
5. نام تامین کننده، Widgets Inc.، در سلول F3 ظاهر می شود. این تنها تامین کننده لیست شده است که ویجت های تیتانیوم را می فروشد.

6. سلول F3 را انتخاب کنید. تابع در نوار فرمول بالای کاربرگ ظاهر می شود.

   {=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
   در این مثال، تنها یک تامین کننده برای ویجت های تیتانیوم وجود دارد. اگر بیش از یک تامین کننده وجود داشته باشد، تامین کننده لیست شده اول در پایگاه داده با تابع بازگردانده می شود.