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

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

فرمول آرایه شامل تابع MATCH در داخل تابع INDEX است.

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

در زیر مراحل در موضوعات آموزشی زیر به شما در ایجاد و استفاده از فرمول دیده می شود در تصویر بالا.

01 از 09

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

اولین گام در آموزش این است که داده ها را به یک صفحه اکسل وارد کنید.

به منظور پیروی از مراحل آموزش، داده های نشان داده شده در تصویر بالا را به سلول های زیر وارد کنید.

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

ردیف 3 و 4 به جای فرمول آرایه ایجاد شده در این آموزش خالی خالی می باشند.

آموزش قالببندی دیده شده در تصویر را شامل نمی شود، اما این کار بر نحوه کارکرد فرم جستجو تاثیر نمی گذارد.

اطلاعات در مورد گزینه های قالب بندی مشابه آنچه که در بالا دیده می شود در این Basic Excel Formatting Tutorial موجود است.

02 از 09

شروع تابع INDEX

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

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

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

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

مراحل آموزش

1. روی سلول F3 کلیک کنید تا آن سلول فعال باشد . این جایی است که ما به تابع توزیع وارد میشویم.
2. روی برگه فرمول نوار منو کلیک کنید.
3. گرین کارت و مرجع را از روبان انتخاب کنید تا لیست کشویی تابع باز شود.
4. روی INDEX در لیست کلیک کنید تا کادر محاوره ای Arguments را انتخاب کنید .
5. آرایه، row_num، col_num را در کادر محاوره ای انتخاب کنید.
6. برای باز کردن کادر محاوره ای INDEX روی OK کلیک کنید.

03 از 09

وارد کردن Argument Array تابع INDEX

اولین استدلال مورد نیاز استدلال آرایه است. این استدلال طیف وسیعی از سلول هایی را که برای داده های مورد نظر مورد جستجو هستند تعیین می کند.

برای این آموزش این استدلال پایگاه داده نمونه است.

مراحل آموزش

1. در کادر محاوره ای INDEX روی خط آرایه کلیک کنید.
2. سلولهای D6 تا F11 را در برگه کارتی وارد کنید تا محدوده را به کادر محاوره وارد کنید.

04 از 09

شروع تابع MATCH Nested

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

تابع توزیع شده باید به عنوان یکی از استدلال های تابع اول تایپ شود.

در این آموزش، تابع MATTES تودرتو و استدلال های آن به خط دوم کادر محاوره ای INDEX وارد می شوند - خط Row_num .

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

وارد کردن Argument Lookup_value تابع MATCH

اولین گام در ورود به عملکرد MATCH توشتون این است که وارد بحث Argument Lookup_value شوید .

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

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

این کار با ترکیب کردن یا پیوستن به دو یا چندین سلول با هم با استفاده از نماد ampersand و & amp ؛ انجام می شود.

مراحل آموزش

1. در کادر محاوره ای INDEX روی خط Row_num کلیک کنید.
2. تابع نام نام تابع را دنبال کنید که به ترتیب یک دور باز است " ( "
3. بر روی سلول D3 کلیک کنید تا مرجع سلولی را در کادر محاوره وارد کنید.
4. بعد از مرجع سلول D3 یک ampersand و && را وارد کنید تا مرجع سلولی دوم اضافه شود.
5. بر روی سلول E3 کلیک کنید تا مرجع سلولی دوم را در جعبه محاوره وارد کنید.
6. علامت "،" بعد از سلول E3 مرجع برای تکمیل ورود به argumentApp Lookup_value تابع MATCH وارد کنید .
7. کادر محاوره ای INDEX را برای مرحله بعدی آموزش باز کنید.

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

05 از 09

اضافه کردن Lookup_array برای تابع MATCH

این مرحله شامل اضافه کردن استدلال Lookup_array برای عملکرد MATCH توشتی است .

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

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

برای وارد کردن آرایه های چندگانه، ما دوباره از & ampersand & ampersand استفاده می کنیم تا آرایه ها را با هم ترکیب کنیم.

مراحل آموزش

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

1. پس از قرار دادن نقطه در انتهای ورود فعلی روی خط Row_num کلیک کنید.
2. سلولهای D6 تا D11 را در صفحه کارتی وارد کنید تا محدوده را وارد کنید. این اولین آرایه است که تابع برای جستجو است.
3. بعد از مراجعه به سلول D6: D11 یک ampersand و & after را وارد کنید، زیرا ما می خواهیم تابع را برای دو آرایه جستجو کنیم.
4. برجسته کردن سلولهای E6 تا E11 در برگه برای ورود به محدوده. این آرایه دوم تابع برای جستجو است.
5. یک کاما "،" بعد از مرجع سلول E3، برای تکمیل ورودی آرگومان Lookup_array تابع MATCH وارد کنید .
6. کادر محاوره ای INDEX را برای مرحله بعدی آموزش باز کنید.

06 از 09

اضافه کردن نوع بازی و تکمیل تابع MATCH

استدلال سوم و نهایی تابع MATCH، استدلال Match_type است.

این استدلال به اکسل می گوید که چگونه با Lookup_array با مقادیر 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. به دنبال کاما در خط Row_num ، صفر صفر را تایپ کنید، زیرا ما می خواهیم تابع توزیع شده برای بازگشت دقیق به شرایطی که در سلول های D3 و E3 وارد می کنیم، بازگردیم .
2. براى تکميل تابع MATCH يک براکت دور بسته را وارد کنيد ) ""
3. کادر محاوره ای INDEX را برای مرحله بعدی آموزش باز کنید.

07 از 09

بازگشت به عملکرد INDEX

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

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

مراحل آموزش

1. روی خط Column_num در کادر محاوره ای کلیک کنید.
2. از آنجا که ما به دنبال اطلاعات در ستون سوم دامنه D6 تا F11 می رویم شماره 3 " 3 " (بدون نقل قول) را در این خط وارد کنید.
3. دکمه OK را فشار دهید یا CLOCK INDEX را خاموش نکنید. این باید برای مرحله بعدی در آموزش باز شود - ایجاد فرمول آرایه .

08 از 09

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

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

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

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

اثر فشردن این کلیدها با هم این است که تابع را با تسمه های مجزای احاطه کنیم: {} نشان می دهد که در حال حاضر یک فرمول آرایه است.

مراحل آموزش

1. با کادر محاوره ای کامل که از مرحله قبل این آموزش باز است، کلید CTRL و SHIFT را در صفحه کلید فشار داده و نگه دارید و سپس کلید ENTER را فشار دهید و کلید را فشار دهید .
2. اگر درست انجام شود، کادر محاوره ای بسته خواهد شد و خطای # N / A در سلول F3 ظاهر می شود - سلولی که در آن تابع وارد شده ایم.
3. خطای # N / A در سلول F3 ظاهر می شود زیرا سلول های D3 و E3 خالی هستند. D3 و E3 سلول هایی هستند که ما در تابع برای پیدا کردن Lookup_values ​​در مرحله 5 آموزش توضیح دادیم. هنگامی که داده ها به این دو سلول اضافه می شوند، خطا با اطلاعات از پایگاه داده جایگزین خواهد شد.

09 از 09

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

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

همانطور که در مرحله قبل ذکر شد، ما به دنبال اصطلاحات ویدجت از ستون 1 و تیتانیوم از ستون 2 هستیم.

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

مراحل آموزش

1. بر روی سلول D3 کلیک کنید.
2. نوع ویجت ها را فشار داده و کلید Enter را در صفحه کلید فشار دهید.
3. بر روی سلول E3 کلیک کنید.
4. تایپ تیتانیوم را تایپ کنید و کلید Enter را در صفحه کلید فشار دهید.
5. نام تامین کننده Widgets Inc. باید در F3 سلول ظاهر شود - محل تابع از آنجایی که تنها فروشنده موجود است که ویجت های تیتانیوم را به فروش می رساند.
6. هنگامی که روی F3 سلول تابع کامل کلیک کنید
   {= INDEX (D6: F11، MATCH (D3 و E3، D6: D11 و E6: E11، 0)، 3)}
   در نوار فرمول بالای برگه ظاهر می شود .

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