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

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

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

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

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

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

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

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

1- تابع Index

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

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

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

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

حالت اول:

INDEX(array, row_num, [column_num])

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

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

حالت دوم

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

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

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

(A2:C6, A7:C10)

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

	A	B	C
1	محصول	قیمت	تعداد
2	محصول 1	200,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(lookup_value, lookup_array, [match_type])

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

	A	B	C
1	محصول	قیمت	تعداد
2	محصول 1	200,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 استفاده نماییم.

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

	A	B	C
1	محصول	قیمت	تعداد
2	محصول 1	200,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