توابع match و index در اکسل
یکی از ضعف های فرمول VLOOKUP این است که در جستجو ، مقدار جستجو شونده (Lookup Value) حتما باید در ستون اول جدول داده (lookup Array) وجود داشته باشد. در صورتی که مقدار جستجو شونده در ستون سوم جدول باشد و شما بخواهید مقدار در ستون دوم را فراخوانی کنید فرمول VLOOKUP این کار را برای شما انجام نمی دهد. این بزرگترین ضعف فرمول VLOOKUP به شمار می آید.
قبل از ورود به آموزش این مثال ، هرکدام از تابع match و index را جداگانه معرفی می کنیم.
ساختار تابع index
تابع Index بر اساس شماره ردیف و یا ستون در یک محدوده به دنبال داده مورد نظر می گردد و داده را فراخوانی می کند. تابع Index را شبیه به یک GPS بدانید که مختصاتی میگیرد و یک مکان را برای شما بر میگرداند.
=INDEX ( Array ; row_num ; column _num )
Array : محدوده ای که داده در آن وجود دارد را در این آرگومان مشخص می کنند . این محدوده در نقش یک نقشه برای GPS ما است
row _ num : این آرگومان عدد مربوط به سطر داده مورد نظر را می گیرد.
column _ num : این آرگومان عدد مربوط به ستون داده مورد نظر را می گیرد.
در نهایت این فرمول در محدوده ی مشخص شده بر اساس شماره سطر و ستون یک داده را بر می گرداند.
ساختار تابع match
تابع Match بر اساس یک مقدار ، در یک محدوده شماره سطر و یا ستون را برمی گرداند.
=MATCH ( Lookup_Value ; Lookup_Array ; [ match_type ] )
Lookup_Value : مقداری که باید به دنبال آن بگردید در این آرگومان نوشته می شود . این آرگومان مشابه آرگومان Lookup value در فرمول Vlookup است.
Lookup_Array : محدوده ای که Lookup Value در آن وجود دارد باید در همین آرگومان مشخص گردد.
[ match_type ] : این آرگومان سه مفدار 1- و 0 و 1 را می گیرد.
1- : در صورت پیدا نکردن مقدار Lookup value در محدوده Lookup Array ، کمترین مقدار نسبت به مقدار جستجو را در نظر بگیر و بر اساس آن فرمول را انجام بده
0 : دقیقا به دنبال مقدار Lookup Value بگرد . در این گزینه اگر فرمول مقدار Lookuo value را در Lookup Array پیدا نکرد ، خطا می دهد.
1 : در صورت پیدا نکردن مقدار Lookup value در محدوده Lookup Array ، بیشترین مقدار نسبت به مقدار جستجو را در نظر بگیر و بر اساس آن فرمول را انجام بده
در نهایت این فرمول بر اساس یک داده ، شماره سطر و یا ستون را برمی گرداند.
مثال از ترکیب تابع match و index
تابع Index بر اساس یک عدد داده مورد نظر را بر می گرداند و تابع Match بر اساس یک داده عدد برای ما بر می گرداند. تمام مفهوم ترکیب این دوتابع در همین جمله خلاصه شده است .
در مثال زیر ما یک جدول گزارشی از فاکتور های فروش داریم. میخواهیم با ترکیب تابع match و index ، مقادیری که در جدول گزارش 1 وجود دارد را فراخوانی کنیم.این مقادیر بر اساس شماره فاکتور باید فراخوانی شوند.
همانطور که مشاهده می کنید فراخوانی این مقادیر با استفاده از تابع vlookup ممکن نیست. علت آن هم این است که آرگومان Table array در این فرمول باید از ستونی آغاز شود که در آن مقدار Lookup value وجود دارد. در اینجا نمی توان از ستون تاریخ شروع کرد و در ادامه نمی توان مقدار تاریخ را برگرداند.
حل این مسئله تنها به واسطه ترکیب تابع match و index در اکسل ممکن است. اما این ترکیب چگونه انجام می شود؟ ما می خواهیم بر اساس یک مقدار ، مقدار دیگری را برگردانیم پس باید از تابع index استفاده کنیم.
تابع index را در سلول M4 می نویسیم، آرگومان اول با نام Array یک محدوده یا مپ از ما می خواهد. مپ ما باید جایی باشد که جواب در آن وجود دارد . در این جا جواب ما تاریخ است پس ستون تاریخ را انتخاب می کنیم.
در آرگومان دوم که Rows نام دارد ، فرمول می خواهد بداند چند سلول به سمت پایین حرکت کند. این موضوع بستکی دارد به عدد شماره فاکتور. در اینجا تابعی نیاز داریم که یک مقدار را بگیرد و بر اساس موقعیت آن، یک عدد برگرداند. تابع match در اکسل این کار را انجام میدهد.
در ادامه باید در آرگومان Rows تابع index ، تابع match را بنویسیم. درتابع Match آرگومان اول را روی سلول شماره فاکتور فروش تنظیم می کنیم و آرگومان دوم را بر روی ستون شماره فاکتور در جدول تنظیم می کنیم و آرگومان آخر را 0 میگذاریم تا جواب دقیق برای ما بازگردانده شود. در نهایت فرمول به صورت زیر ایجاد می گردد.
MATCH(M3,Table1[شماره فاکتور],0)
تا به اینجا تابع اصلی ما به صورت زیر است:
آرگومان بعدی تابع Index ، آرگومان Columns num هست که در این آرگومان باید مشخص کنیم در مپ ما کدام ستون باید برگردد. مپ ما فقط یک ستون دارد پس بنابر این عدد 1 را در این آرگومان می نویسیم. در نهایت تابع نهایی ما که با ترکیب تابع match و index ساخته شده است به شکل زیر می باشد.
=INDEX(Table1[تاریخ],MATCH(M3,Table1[شماره فاکتور],0),1)
اکنون توانستید تابع match و index را با یکدیگر ترکیب کنید و این مسئله را حل کنید. در زمان اجرای این ترکیب ممکن است با خطاهایی مواجه شوید که در ادامه برای شما شرح خواهیم داد.
رفع خطا در ترکیب تابع match و index
احتمال وجود خطا در توابع جستجوگر اکسل امری اجتناب ناپذیر است. برای شما که می خواهید اکسل را به صورت حرفه ای یاد بگیرید دانستن انواع خطاها بسیار مهم است . در ترکیب تابع match و index ممکن است با دو خطا مواجه شوید:
خطای N/A# در ترکیب تابع match و index
خطای n/a در ترکیب تابع match و index به خاطر تابع Match در این ترکیب است. این خطا به این معنی است که مقدار جستجو شده در محدوده جستجو شده پیدا نشده است. برای مثال اگر در مثال بالا با این خطا مواجه شویم باید بدانیم که این خطا مربوط به شماره فاکتور و ستون شماره فاکتور است.
برای رفع این خطا باید مقادیر وارد شده به تابع match را برسی کنیم. برسی را با این سوال شروع می کنیم که آیا مقدار جستجو شده در ستون Lookup Array وجود دارد؟ در عکس بالا اگر دقت کنید مقدار جستجو شده صفر است و هیچ عددی در آن نوشته نشده . عدد صفر در ستون شماره فاکتور ما وجود ندارد و به همین علت تابع خطای n/a را بر می گرداند. برای رفع این خطا کافی است یک شماره فاکتور صحیح وارد کنیم.
خطای ref# در ترکیب تابع match و index
خطای ref# در فرمولهای جستجوگر به معنای این است که محدوده ای در آن فرمول گم شده یا پیدا نمی شود. این خطا ها و رفع آنها دقیقا مشابه با خطای ref در vlookup است . در صورت مشاهده این خطا چند حالت وجود دارد. هم ممکن است خطا مربوط به تابع Index باشد و هم ممکن است خطا از match باشد.
برای رفع این خطا باید چند مود را برسی کنید. ابتدا فرمول index را برسی می کنیم. اگر محدوده مپ ما یا همان Array دارای 5 ستون و 10 سطر باشد، خطای Ref را زمانی مشاهده می کنید که در آرگومان rows num عدد بزرگتر 10 وارد شود و یا در آرگومان columns num عددی بزرگتر از 5 وارد شود. این موارد را حتما چک کنید.
اگر یک ستون در محدوده Array دلیت شود، ممکن است باز هم این خطا را از جانب تابع index مشاهده کنید. اگر محدوده حذف شده در محدوده آرگومان lookup array تابع match هم وجود داشت، احتمال وقوع این خطا در تابع match نیز وجود دارد.
با چک کردن این موارد به احتمال خیلی زیاد می توانید خطاهای موجود را برطرف سازید.
جمع بندی ترکیب تابع match و index در اکسل
اگرچه توابع جستجوگر زیادی در اکسل وجود دارد ، اما برای جستجو در برخی جداول نمی توان از آنها استفاده کرد . ترکیب تابع match و index در اکسل ، یک ترکیب برنده برای جستجو است . این توابع می توانند بر اساس یک مقدار ، هر مقداری را در هر کجای جدول پیدا کنند و فراخوانی نمایند.
شما عزیزان می توانید برای درک بهتر این آموزش ویدئوی آموزشی را مشاهده نمایید و برای کسب اطلاعات بیشتر به سایت آموزش مایکروسافت اکسل مراجعه کنید.
