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

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

دلایل و نکات مهمی که بهتر است بجای تابع vlookup از این دو تابع استفاده گردد به شرح زیر می باشد .

تابع vlookup نمی تواند به عقب حرکت کند و ستون قبل از ستون حاوی داده مورد جستجو را استخراج کند.
در صورتی که یک ستون در بین محدوده انتخابی در تابع vlookup حذف یا اضافه شود نتیجه تابع اشتباه خواهد شد.
در حجم داده زیاد ،سرعت تابع vlookup کمتر است.

برای درک بهتر این مطلب از آموش اکسل برای شما مثال کاربردی تهیه نموده ایم ، با ما همراه باشید .
دراینجا جدولی شامل منطقه، درآمد ، سود و نام کارشناس فروش‌ وارد شده را داریم ، می‌خواهیم با استفاده از نام کارشناس فروش، اسم منطقه را جستجو کنیم .

تابع index

در این مرحله لازم است تابع index را بنویسیم ( در مقاله آموزش صفر تا 100 تابع index در اکسل،همراه با مثال کاربردی ، بطور جامع برای شما این تابع را توضیح داده اییم و اگر آشنایی با این تابع ندارید حتما آن را مطالعه فرمایید )

index(array;Row_num;[column_num])

قسمت اول باید Array یا محدوده مون رو انتخاب کنیم، محدوده منطقه‌ها مد نظر ما هست
جداکننده را گذاشته و سپس قسمت بعدی فرمول از ما می پرسد ، سطر چندم رو می‌خواهی؟
ما فرضاً کارشناس فروش آقای هادی نژاد‌حسن را می خواهیم که در سطر هفتم نوشته شده است بعد از آن پرانتز رو بسته و Enter

در اینجا من از روی این فیلد داده‌ای نگرفتم و نتیجه‌ام رو بصورت دستی وارد کردیم که در سطر چندم هست.

تابع MATCH

و اما سوال اینجاست که چطور می ‌توانیم این را به سلول کارشناس فروش لینک دهیم؟
ما یک تابعی داریم به اسم تابع match که این تابع می‌تواند درون یک محدوده به ما بگوید که یک داده‌ی مشخص چندمین سلول هست.

MATCH(lookup_value, lookup_array, [match_type])

به این صورت نوشته می‌شه که مساوی match پرانتز باز.
در وهله اول ا ما می پرسد دنبال چه داده‌ای می‌گردید یا همان Lookup_Value شما چیست ؟
من کارشناس فروشم رو براش انتخاب می‌کنم و جدا کننده رامی‌گذارم.
سپس از ما میپرسد ، این اسم رو در کدوم محدوده انتخاب کنم؟ من اینجا ستون کارشناس فروش رو انتخاب می‌کنم.
و نکته ایی که حتما به آن توجه کنید آن است که همیشه باید برای تابع match در قسمت Lookup_Array یا یک ستون رو انتخاب کنید یا اگر می‌خواهید سطری شماره ستون رو بهتون بده یک سطر رو انتخاب کنید. نمی‌توانید بیشتر از یک ستون یا یک سطر را انتخاب کنید و جدا کننده را گذاشته و قسمت آخر را همیشه صفر می‌گذاریم. پرانتز رو می‌بندم. Enter .

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

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

پس من خیلی راحت می‌توانم این تابع را کپی کنم Ctrl+c و داخل تابع index قسمت دومش که دستی تایپ کرده بودم را با این فرمول جایگزین کنم تا بر اساس نام کارشناس فروش شماره ردیفش را پیدا کند و ستون متناظرش را به من نتیجه بدهد. Enter . که باید نتیجه کلمه غرب باشد .

حالا اگر بخواهیم سود را استخراج کنیم به چه صورت می‌توانیم عمل کنیم؟
می‌نویسیم مساوی index پرانتز باز، محدوده‌ای که سود ما در اون قرار داره کجاست؟
. جداکننده را گذاشته سپس از ما می پرسد سطر چندم رو می‌خواهی؟ که همون جور که قبلاً گفتم با تابع match ما سطرش رو بهش می‌دیم، match پرانتز باز، match کن. چی رو match کنه؟ نام کارشناس فروش ما رو. کاما Lookup_Array کجاست؟
کارشناس فروش‌ها کجان؟ این محدوده. دقت کنید محدوده‌ای که برای index انتخاب می‌کنید و محدوده‌ای که برای match انتخاب می‌کنید باید ارتفاعشون یکی باشه. یعنی اگر این از D6 تا D15 است کارشناس فروش ما باید حتماً از B6 تا B15 باشه نمی‌تونه از B5 باشه تا B15 یا از B6 باشه تا B17 کاما، قسمت آخر ، صفر پرانتز بسته. تابع match رو پرانتزش رو بستم و پرانتز تابع index رو هم می‌بندم اینجا به من سود رو نشون می‌ده.

کار دیگری که ما می‌خواهیم با این تابع انجام دهیم این است که یه کم پیشرفته‌تر باهاش عمل کنیم به این صورت که به ما بگوید که اگر شخصی اینجا سود رو انتخاب کرد سود رو به ما نشون بده و درآمد رو انتخاب کرد درآمد رو به ما نشون بده و اینجا هم بتونه شخص کارشناس فروش‌هاش رو عوض کنه و نتیجه رو اینجا ببینه.
به چه صورت عمل می‌کنیم؟
در ابتدا مساوی index کن پرانتز را باز می کنیم سپس محدوده ما کجاست؟ اینبار چون ممکن است درآمد باشه و هم ممکنه سود باشه من هر دوی این ستون ها را باید انتخاب کنم.
کاما سطر چندم؟ خوب سطرش رو گفتم که با تابع match پیدا می‌کنیم. match پرانتز باز آقای نژادحسن رو بگرد.
کاما در کجا؟ در این کارشناسای فروش، کاما صفر، پرانتز بسته.
خوب کاما رو بگذارم می‌رم قسمت سوم تابع index که قبلاً توضیح دادم در قسمت سوم باید بگم که حالا تو این دو تا ستون رو به من دادی ستون چندم رو می‌خوای؟
خوب من چجوری می‌تونم بفهمم که ستون چندم؟ باید از روی فیلد کنار اون و این مسئله رو متوجه بشم پس دوباره می تونم match کنم .
چی رو match کنم؟ این سلول کنارش که توش از لیست کشویی کاربر من قراره درآمد یا سود رو انتخاب کنه
کاما کجا بیاد match کنه ؟ توی این دو تا فیلد و کاما صفر.

ببینید در این قسمت کاربر من وقتی مثلاً درآمد رو انتخاب کرد تابع match میاد به این دو تا سلول نگاه می‌کنه می بینه که درآمد اولین ستون نتیجه‌اش می‌شه یک. پس در نتیجه کل این قسمت نتیجه‌اش می‌شه یک و این یک رو می‌ذاره توی تابع index و نتیجه ما درست می‌شه. پرانتز رو می‌بندم. Enter.
و اگر من بجای درآمد سود رو انتخاب کنم خیلی راحت سود رو به من می‌ده اینجا هم اگر بجای کارشناس فروشم یه کارشناس فروش دیگه انتخاب کنم خیلی راحت سودش رو به من می‌ده.

نکته طلایی

این فرمول با توجه به اینکه یه کم طولانی‌تر هست، من یه ترفندی که استفاده می‌کنم به این صورت می باشد که چارجوب این فرمول رو نوشته و آن را قرار می‌دم در قسمت AutoCorrect اکسل قرار می دهم تا خیلی سریع اکسل بتونه نتیجه را به من بدهد.
چطور این کار رو انجام بدهیم؟
ببینید من چارچوبش رو اینجا نوشتم که
مساویه با index و SelectReturnColumn یعنی ستونی که می‌خواد به ما بده و match کنه قسمت‌های مختلفش رو حالا با تعریف خودم نوشتم.
این رو کپی می‌کنم میام قسمت File ، Options ،و دراینجا قسمت Proofing ، AutoCorrect Options…
اینجا یک حالت اختصاری رو براش قرار می‌دم که من iii براش قرار دادم و گفتم که هر وقت من iii رو نوشتم این چارچوب رو برای من بیا نشون بده. خوب ok رو می‌زنم.
حالا دیگه اگر بخوام تابع‌ام رو بنویسم خیلی راحت‌ترم. اینجا بدون هیچ مساوی iii می‌نویسم space می‌زنم تابعم نوشته می‌شه بعد توی قسمت‌های مختلف باید جایگزین کنم. مثلاً Array که این قسمت هست برای index می‌شه کجا؟ می‌شه سودم. بعد match رو خودش نوشته. خوب داخل match ، Lookup_Value ام چیه؟ اینه و Lookup_Value ام چیه؟ Lookup_Array ام این کارشناس فروش‌هاست. تمام. این نتیجه رو به ما داد.