ترکیب تابع index و match در اکسل

ترکیب تابع index و match در اکسل

ترکیب دو تابع Index و match می تواند خیلی از کم وکاستی های تابع vlookup را برای ما پر نماید. همانگونه که در تشریح تابع vlookup گفتم این تابع بر اساس مقدار ورودی و ستون مدنظر در یک ناحیه خروجی خاصی را برای ما نشان خواهد داد.

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


نقشه راه این آموزش:

در این آموزش این موارد را بررسی خواهیم نمود:

1- اجزای تابع Index به همراه مثال

2- اجزای تابع match به همراه مثال

3- ترکیب دو تابع Index و match در اکسل


1- تابع Index

تابع index در یک ناحیه براساس شماره سطر و ستونی که دریافت می کند مقدار درون آن سلول را نمایش می دهد. به طور مثال در یک کوچه ای که چندین ساختمان چند طبقه وجود دارد تابع index با دریافت اینکه چندمین ساختمان درون کوچه و چندمین طبقه آن ساختمان به ما صاحب خانه را معرفی می نماید.

تابع indexدر اکسل
تابع index در اکسل

اما اجزای این تابع کاربردی

این تابع در دو حالت مورد استفاده قرار می گیرد:

  1. اگر قرار باشد مقدار را از یک ناحیه استخراج نماید
  2. اگر قرار باشد مقدار را از چند ناحیه انتخاب نماید.

حالت اول:

INDEX(array, row_num, [column_num])

INDEX(شماره ستون ، شماره ردیف ، ناحیه )

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

بیشتر بخوانید:   ساخت لیست کشویی در اکسل

حالت دوم

INDEX(reference, row_num, [column_num], [area_num])

INDEX(شماره ناحیه ، شماره ستون ، شماره ردیف ، ناحیه ها )

در این حالت به جای معرفی یک ناحیه، چندین ناحیه را می توان مشخص نماییم طریقه معرفی هم به شکل زیر خواهد بود:

(A2:C6, A7:C10)

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

ABC
1
محصولقیمتتعداد
2محصول 1200,000 تومان40
3 محصول 2 130,000 تومان38
4محصول 3 312,000 تومان15
5محصول 4 100,000 تومان25
6محصول 5 4100,000 تومان40
7محصول 6 500,000 تومان10
8محصول 7 270,000 تومان16
9محصول 8 180,000 تومان20
10محصول 9 150,000 تومان12

در حالت اول برای نمایش تعداد محصول 5 به این صورت فرمول نویسی می کنم:

index(A2:C10,5,3)

درفرمول بالا به تابع گفتیم که در ناحیه C2:C10 مقدار پنجمین ردیف و سومین ستون را به عنوان خروجی نمایش بده.

حالا فرض کنید همین ناحیه را به دو قسمت تقسیم کنیم:

index((A2:C5,A6:C10),2,2,2)

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

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


2- اجزای تابع Match

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

تابع match در اکسل
تابع match در اکسل

MATCH(lookup_value, lookup_array, [match_type])

بیشتر بخوانید:   تابع Sumifs

MATCH(نوع تطابق ، ناحیه جستجو ، مقدار مدنظر )

ABC
1
محصولقیمتتعداد
2محصول 1200,000 تومان40
3 محصول 2 130,000 تومان38
4محصول 3 312,000 تومان15
5محصول 4 100,000 تومان25
6محصول 5 4100,000 تومان40
7محصول 6 500,000 تومان10
8محصول 7 270,000 تومان16
9محصول 8 180,000 تومان20
10محصول 9 150,000 تومان12

دوباره همان مثالی که برای تابع index زدیم را در نظر بگیرید. این بار می خواهیم مکان محصول 5 را پیدا کنیم لذا می نویسیم:

=MATCH(“محصول 5”,A2:A10,0)

در فرمولی که قید نمودیم در ناحیه مشخص شده دنبال “محصول 5 ” گشته و مکانش را به ما به عنوان خروجی نمایش می دهد.

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

  • مقدار 1 یا خالی باشد که در این صورت مقادیر باید به صورت صعودی مرتب شده باشد. در این حالت بزرگترین مقدار که کمتر یا مساوی با مقدار جستجو باشد انتخاب می شود.
  • مقدار صفر باشد که در این صورت مقدار دقیق را نمایش می دهد.
  • مقدار -1 باشد که در این صورت مقادیر باید به صورت نزولی مرتب شده باشد. کمترین مقداری که بزرگتر یا مساوی با مقدار مورد نظر برای جستجو را انتخاب می کند.

3- ترکیب دو تابع index و match

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

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

مثالی که در بالاتر بررسی کردیم را دوباره در نظر بگیرید.

ABC
1
محصولقیمتتعداد
2محصول 1200,000 تومان40
3 محصول 2 130,000 تومان38
4محصول 3 312,000 تومان15
5محصول 4 100,000 تومان25
6محصول 5 4100,000 تومان40
7محصول 6 500,000 تومان10
8محصول 7 270,000 تومان16
9محصول 8 180,000 تومان20
10محصول 9 150,000 تومان12

ما می خواهیم تعداد موجود “محصول 5” را داشته باشیم. می توانیم فرمول مان را به این شکل نگارش نماییم:

=INDEX(A2:C10,MATCH(“محصول 5”,A2:A10,0),3)

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

ترکیب تابع index و match

در مثال ویدئویی زیر محاسبات مربوط به بارگذاری سازه را در اکسل با استفاده از ترکیب این دو تابع بررسی نموده ایم:

بیشتر بدانید:

1- دوره 50 تابع اکسل

2- ترفندهای حرفه ای با فرمت سل

3- راهنمای جامع Pivot table

4- دوره 30 تابع اکسل در ویرگول

پاسخی بگذارید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *