هر آنچه درباره index و match باید بدانید.

به غیر از VLOOKUP ،توابع INDEX و MATCH  کاربردی ترین ابزار  در Excel برای انجام جستجوها می باشد.ترکیب INDEX و MATCH قدرتمند و انعطاف پذیر است و شما می توانید آن را در انواع فرمول ها، از ابتدایی تا بسیار پیشرفته استفاده کنید.

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

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


مثالی از ترکیب توابع در اکسل

 


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

INDEX –  مقدار را براساس مکان تعیین شده نشان می دهد.

عملکرد INDEX در اکسل فوق العاده انعطاف پذیر و قدرتمند است، و آن را در تعداد زیادی از فرمول های اکسل، به ویژه فرمول های پیشرفته می توانید پیدا کنید.

اما INDEX واقعا چه کار می کند؟

به طور خلاصه، INDEX  مقدار یک مکان مشخص در یک لیست یا جدول را برمی گرداند.

INDEX(array, row_num, [column_num])

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

شما می توانید با این فرمول ساده با تابع INDEX انجام دهید:

=INDEX(B3:B11,4)

تابع index و match

INDEX  سلول چهارم را در محدوده B3:B11  پیدا کرده و مقدار سلول B6 را باز می گرداند.

تابع index

INDEX  و جستجوی دوبعدی

اگر می خواهید قطر مریخ را دریافت کنید چه؟

 در این حالت،  به INDEX شماره ردیف و ستون را می دهیم و در یک آرایه بزرگ (محدوده) داده ها به دنبالشان می گردیم.  فرمول INDEX برای این منظور محدوده   B3: D11  با شماره ردیف 4 و شماره ستون 2 می باشد.

=INDEX(B3:D11,4,2)

تابع index

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

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

در این مرحله، شما ممکن است فکر کنید ” چگونه مکان سلول مد نظرمان را در صفحه اکسل همیشه به صورت دستی بدهیم؟ “

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

MATCH –  موقعیت را در یک لیست پیدا میکند.

تابع MATCH برای یک هدف طراحی شده است: موقعیت عددی یک آیتم را در یک لیست پیدا کند.

برای مثال، ما می توانیم از MATCH برای بدست آوردن موقعیت کلمه “هلو” در این لیست میوه ها مانند این استفاده کنیم:

=MATCH(“هلو”,B3:B9,0)

تابع Match

MATCH  عدد 3  را باز می گرداند، از آنجا که “هلو” سومین مورد در محدوده است.  اهمیتی ندارد لیست تان افقی یا عمودی باشد.

اگر ما لیست میوه ها را به صورت افقی مرتب کنیم، نتیجه یکسانی را با این فرمول MATCH دریافت می کنیم:

=MATCH(“هلو”,C4:I4,0)

تابع Match

تابع MATCH با محدوده افقی. همان نتیجه،  3 را باز می گرداند.

Match type  approximate or exact?

نوع  Match دقیق یا تقریبی؟

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

MATCH(lookup_value, lookup_array, [match_type])

نوع (Match (match_type مهم است و کنترل تطبیق دقیق یا تقریبی  را برعهده دارد.

. جدول زیر گزینه های زیر را خلاصه می کند:

Match type رفتار جزییات
1 تقریبی تابع بزرگترین مقدار که کوچکتر یا برابر با مقدار جستجو است را پیدا می کند. محدوده جستجو باید به ترتیب صعودی مرتب شود.
0 دقیق تابع  اولین مقداری که دقیقا برابر با مقدار جستجو هست را پیدا می کند. آرایش محدوده نیازی به مرتب سازی نیست.
-1 تقریبی تابع  کوچکترین مقدار بزرگتر یا برابر مقدار جستجو را پیدا می کند. محدوده باید به ترتیب نزولی طبقه بندی شود.
تقریبی هنگامی که نوع مطابقت حذف شود، پیش فرض  1 (تقریبی) مدنظر قرار می گیرد.

برای جستجوی دقیق Match، شما باید صفر را مشخص کنید.

با این حال، اگر شما نیاز به یک مقایسه تقریبی با مقدار جستجو در  یک مقیاس داشته باشید، می توانید از 1 یا -1 استفاده کنید، و شما باید محدوده مدنظر را با توجه به نیازهای خود مرتب کنید، [AZ] زمانی که نوع مطابقت 1 است ، [ZA] هنگامی که نوع تطبیق -1 است.

احتیاط: مانند VLOOKUP، تابع MATCH در حالت پیشفرض  یک و به صورت تقریبی می باشد. تطبیق تقریبی زمانی مفید است که بتوانید بهترین بررسی  را در یک مقیاس از مقادیر پیدا کنید، اما این می تواند یک فاجعه باشد، زمانی که نیاز به یک بررسی  دقیق دارید.

توصیه می کنم از تنظیمات “دقیق” استفاده کنید تا مشکلات احتمالی روبرو  نشوید.

ترکیب MATCH و INDEX

حالا که ما مبانی INDEX و MATCH را گفتیم ، چگونه می توانیم دو توابع را در یک فرمول  ترکیب کنیم؟

داده های زیر را در نظر بگیرید، یک لیستی از فروشندگان و  فروش ماهانه را برای سه ماه به شما نشان می دهد: تیر ، مرداد، و شهریور.

تابع index و matchمیخواهیم یک فرمول را بنویسیم که مقدار فروش را برای ماه مرداد برای هر فروشنده ارائه دهد از بحث بالا، می توانیم آدرس را به INDEX بدهیم تا مقدار را به ما بدهد. برای مثال، برای بازگرداندن مقدار فروش مرداد مسعود، این فرمول INDEX این کار را انجام می دهد:

=INDEX(C3:E11,5,2)

در اینجا تابع INDEX مقدار سطر 5، ستون 2 از محدوده C3: E11 را به ما نشان خواهد داد. اما ما قطعا نمی خواهیم  مکان مدنظرمان را دستی به تابع بدهیم بلکه ما یک جستجوی پویا را می خواهیم. کارمان را مرحله به مرحله انجام می دهیم. در مرحله اول شماره ستون را ثابت نگه می داریم و مقدار عددی 2 را به آن می دهیم و شماره ردیف را داینامیک انتخاب می کنیم.

چطور این کار را انجام خواهیم داد؟

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

=INDEX(C3:E11,MATCH(“مسعود”,B3:B11,0),2)

هنگامی که MATCH”مسعود” را در B3: B11 پیدا می کند، شماره 5 را به طور مستقیم به INDEX برمی گرداند:

=INDEX(C3:E11,5,2)

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

فرمول زیر نمونه بسیار ساده ای از این ایده را نشان می دهد.

=INDEX(C3:E11,MATCH(H2,B3:B11,0),2)

تابع index و match

 MATCH از نام فروشنده در H2 برای پیدا کردن یک شماره ردیف برای INDEX استفاده می کند.  INDEX موقعیت عددی می خواهد که این مقدار را  MATCH پیدا می کند.

حالا شماره ستون را حل کنیم.

جستجوی دوطرفه با INDEX و MATCH

در بالا، ما از تابع MATCH برای پیدا کردن شماره ردیف برای هر فروشنده داده شده استفاده کردیم.در مثال بالا ما آمدیم و شماره ستون را ثابت نگه داشتیم و مثال مان را بررسی کردیم. چگونه می توانیم فرمول را به طور کامل پویا کنیم و فروش را برای هر فروشنده معین در هر ماه معین بازگردانیم؟ ترفند استفاده از MATCH یک بار برای گرفتن موقعیت ردیف و یک بار برای گرفتن موقعیت ستون است.

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

=MATCH(“شهریور”,C2:E2,0)

البته نمی خواهیم به هیچ وجه از کد های پیچیده استفاده کنیم، پس اجازه دهید که نام ماه را بروزرسانی کنیم  و از MATCH برای پیدا کردن شماره ستونی که نیاز داریم استفاده کنیم.

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

=INDEX(C3:E11,MATCH(H2,B3:B11,0),MATCH(H3,C2:E2,0))

تابع index و match

اولین فرمول MATCH  عدد 5 را به INDEX به عنوان شماره ردیف باز می گرداند، فرمول دوم MATCH  عدد 3 را به INDEX به عنوان شماره ستون باز می گرداند. هنگامی که MATCH اجرا می شود، فرمول ساده تر می شود:

=INDEX(C3:E11,5,3)

توجه: می توانید از Data Validationرا برای ایجاد منو های کشویی ساده برای فروشنده و ماه استفاده کنید.

اما  VLOOKUP

اولین نکته:  INDEX + MATCH می تواند همه کارهای VLOOKUP را انجام دهد و حتی بیشتر. این یک روش انعطاف پذیر تر است تا همه چیز را جستجو نماید. در اینجا چند مزیت نسبت به VLOOKUP وجود دارد:

  •  INDEX و MATCH می توانند به سمت چپ یا راست در یک جدول حرکت نماید، VLOOKUP فقط می تواند در سمت راست حرکت نماید.
  •  INDEX و MATCH می توانند با محدوده افقی یا عمودی کار کنند، VLOOKUP تنها می تواند از محدوده های عمودی استفاده کند
  •  INDEX و MATCH می توانند با اطلاعات طبقه بندی شده در نزولی کار کنند، VLOOKUP  تنها می تواند مطابق اطلاعات مرتب شده به ترتیب صعودی
  •  اگر مجموعه ای از داده های بزرگ داشته باشید، INDEX و MATCH می تواند سریعتر باشد

با این حال، هیچ دلیلی وجود ندارد که از VLOOKUP استفاده نکنید. این تابع عالی با مزایای خود است:

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

دلایلی که ممکن است بخواهید از INDEX + MATCH به جای VLOOKUP استفاده کنید:

  • مقدار جستجو در سمت راست اطلاعاتی است که شما باید به آن مراجعه کنید
  • محدوده داده افقی است و برای HLOOKUP مناسب نیست
  • داده ها به ترتیب نزولی مرتب شده اند
  • مجموعه داده ها بسیار بزرگ است و سرعت عمل یک عامل است
  • شما دوست دارید. INDEX + MATCH 🙂

من معمولا به VLOOKUP متصل میشوم و در صورت نیاز سراغ  INDEX + MATCH می روم.

۱۳۹۶-۱۰-۲۳ ۲۱:۳۸:۱۶ +۰۳:۳۰

درباره نویسنده: