ترکیب دو تابع 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 |
ما می خواهیم تعداد موجود “محصول 5” را داشته باشیم. می توانیم فرمول مان را به این شکل نگارش نماییم:
=INDEX(A2:C10,MATCH(“محصول 5”,A2:A10,0),3)
کمی فرمول بالا را بهتر نماییم. مقدار ورودی تابع match که “محصول 5” می باشد را می توانیم از یک لیست کشویی انتخاب نماییم.
در مثال ویدئویی زیر محاسبات مربوط به بارگذاری سازه را در اکسل با استفاده از ترکیب این دو تابع بررسی نموده ایم:
بیشتر بدانید:
2- ترفندهای حرفه ای با فرمت سل
3- راهنمای جامع Pivot table
4- دوره 30 تابع اکسل در ویرگول