توابع INDEX و MATCH در اکسل برای جست‌وجوی سریعتر (+ دانلود فیلم آموزش گام به گام)

۲۲۲۳۹ بازدید
آخرین به‌روزرسانی: ۲۳ بهمن ۱۴۰۲
زمان مطالعه: ۲۲ دقیقه
توابع INDEX و MATCH در اکسل برای جست‌وجوی سریعتر (+ دانلود فیلم آموزش گام به گام)

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

فیلم آموزشی استفاده از توابع Index و Match در اکسل برای جستجوی سریع

دانلود ویدیو

بیشتر کاربران برای اینگونه کارها از تابع «VLOOKUP» استفاده می‌کنند. این تابع سریع بوده و به سادگی قابل استفاده است، ولی به قدرتمندی دیگر راه‌ها نیست. با استفاده از توابع «INDEX» و «MATCH» می‌توان از اشتباهاتی که در استفاده از VLOOKUP پیش می‌آید جلوگیری کرد. علاوه بر این، اگر با یک صفحه گسترده‌ی خیلی بزرگ طرف باشید، متوجه خواهید شد که با استفاده از این دو تابع، کار جست‌وجوی شما خیلی سریعتر از قبل انجام خواهد شد.

در این مطلب توضیح می‌دهیم که چرا بهتر است از INDEX و MATCH به جای VLOOKUP استفاده کنید؛ پس برای آگاهی از علت این موضوع با ما همراه باشید.

نحوه‌ استفاده از VLOOKUP

ابتدا یک نگاه سریع به نحوه‌ی کار کردن VLOOKUP می‌اندازیم. در جدول زیر نام، شناسه و قیمت تعدادی پوشاک را مشاهده می‌کنید.

تابع VLOOKUP در اکسل

با توجه به فرمولی که در تصویر بالا نوشته شده است، هنگامی که یک شناسه را در سلول C10 وارد کنیم، C11 نسبت به قیمت آن جنس بروز می‌شود. این امر بدین علت رخ می‌دهد که تابع مورد اشاره رشته‌ی موجود در سلول C10 را گرفته و در بین سلول‌های A1 تا C8 به دنبال آن می‌گردد. سپس با توجه به عدد 3 که در فرمول وارد کرده‌ایم، اکسل متوجه می‌شود که ما به دنبال یک سلول در ستون سوم هستیم، چراکه می‌خواهیم قیمت جنس را به دست آوریم. در نهایت نیز با استفاده از مقدار FALSE اطمینان حاصل می‌کنیم که فرمول ما تنها مقادیری را بر می‌گرداند که دقیقا آی‌دی مشابه مقدار وارد شده‌ی ما داشته باشند و از بازگرداندن مقادیر مشابه و حدودی خودداری می‌کند.

این روش به خوبی کار می‌کند، ولی اگر قصد داشته باشید که در آینده تغییری در صفحه گسترده‌ خود ایجاد کنید، خیلی برایتان ایده‌آل نخواهد بود. برای مثال اگر دنبال اضافه کردن یک سلول جدید به صفحه گسترده‌تان باشید، در چنین حالتی امکان دارد فرمول پیش‌تر اشاره شده کاملا از کار بیافتد، چراکه ممکن است قیمت دیگر در ستون سوم نباشد.

با استفاده از یک شیوه متفاوت می‌توانیم نیاز به شمارش دستی ستون‌ها را از بین ببریم و امکان بروز برخی خطاها را کاهش دهیم. همچنین این کار از نظر پردازشی نیز بهینه‌تر بوده و در هنگام سروکار داشتن با داده‌های زیاد، سرعت کار ما را افزایش می‌دهد.

نحوه‌ استفاده از INDEX و MATCH

با استفاده از INDEX و MATCH می‌توانیم جلوی اشتباهات آزاردهنده را بگیریم. این روش کمی پیچیده‌تر است، ولی هنگامی که کار را به بخش‌های مجزا تقسیم کنیم، خیلی هم سخت نخواهد بود.

ابتدا با استفاده از INDEX یک مجموع از سلول‌ها را مشخص می‌کنیم، سپس از متد MATCH برای پیدا کردن سلول درست از بین آن‌ها استفاده می‌نماییم.

نحوه‌ استفاده از INDEX

در تصویر زیر یک نمونه‌ی ساده از نحوه‌ی استفاده از INDEX را مشاهده می‌کنید.

تابع INDEX و اکسل

همانطور که می‌بینید در آن، مجموعه سلول‌های خود را - که C2 تا C8 هستند - انتخاب کرده‌ایم. همچنین مشخص کرده‌ایم که به دنبال هفتمین سطر می‌گردیم. در ادامه می‌توانیم این عدد 7 را با یک تابع MATCH جایگزین کرده تا بتوانیم مبلغ شناسه مشخص شده در سلول C10 را پیدا کنیم.

نحوه‌ استفاده از MATCH

در تصویر زیر چگونگی استفاده از MATCH را مشاهده می‌کنید.

تابع MATCH در اکسل

تابع MATCH به ما اجازه می‌دهد تا یک رشته را برای جست‌وجو مشخص کنیم که در این مثال گفته‌ایم رشته‌ی ما در سلول C10 قرار دارد. سپس یک مجوعه از سلول‌ها را برای جست‌وجوی خود انتخاب می‌نماییم، عدد 0 نیز مشخص می‌کند که دقیقا به دنبال همان رشته می‌گردیم، نه کمتر و نه بیشتر. این تابع محل سلول را بر می‌گرداند که مقدار آن را در سلول C12 مشاهده می‌کنید، یعنی جنسی که آی‌دی آن «BE99» است، در سطر چهارم از محدوده‌ی مشخص شده‌ی ما قرار داد؛ به زبان ساده‌تر منظور سلول A5 است.

نحوه‌ی ترکیب INDEX و MATCH

برای این که INDEX و MATCH با یکدیگر کار کنند، تنها کافی است که مقدار سطر وارد شده در INDEX را با یک تابع MATCH جایگزین کنیم.

ترکیب توابع INDEX و MATCH در اکسل

همانطور که مشاهده می‌کنید، ساختار فرمول ما خیلی هم پیچیده نیست. تنها کاری که کرده‌ایم این است که تابع MATCH را درون تابع INDEX قرار داده‌ایم و مطمئن شده‌ایم که هر دوی این توابع با یک سری سلول و مجموعه‌ی مشخصی سروکار دارند. حالا که صفحه گسترده‌ی ما اینگونه ساخته شده است، به سادگی می‌توانیم بدون ایجاد مشکل برای فرمول خود، آن را تغییر دهیم.

تغییر جدول و عملکرد فرمول

شاید پیاده سازی INDEX و MATCH کمی بیشتر از VLOOKUP زمان ببرد، ولی نتیجه‌ی حاصل از آن‌ها یک سند تغییرپذیر خواهد بود که جای خطای کمتری در آن وجود دارد. اگر صفحه گسترده‌ی شما هم به اینگونه توابع جست‌وجو نیاز دارد، می‌توانید VLOOKUP را کنار گذاشته و از INDEX و MATCH استفاده کنید تا در آینده مشکلات کمتری داشته باشید.

بر اساس رای ۵۶ نفر
آیا این مطلب برای شما مفید بود؟
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.
منابع:
MakeUseOf
۱۷ دیدگاه برای «توابع INDEX و MATCH در اکسل برای جست‌وجوی سریعتر (+ دانلود فیلم آموزش گام به گام)»

سپاسگزار، بسیار مفید بود و خیلی ساده توضیح دادید.

سلام دوتا فایل اکسل دارم توی اکسل یک اسم اشخاص و مبلغ هست توی اکسل 2 اسم شخص و شناسه ملی .میخام توی ستون جدید در اکسل 1 بیاد شناسه ملی مربوط به هر شخص رو از اکسل 2 بخونه و جلوی اسم مربوطه وارد بشه چه فرمول یا توابعی رو باید بزنم؟؟؟

با سلام
در اكسل 2016 زماني كه براي تابع match بصورت زير بجاي يك ستون يك رنج مي دهم #value ميدهد
=MATCH(H3&I3&J3,A3:A8&B3:B8&C3:C8,0)
در اين مثال مي خواهم تركيب ستونهاي h , i , j را در ستونهاي a, b, c پيدا كنم …
با تشكر

سلام
بهترین و کوتاه ترین آموزش Index+Match بود. ممنون

بسیار عالی و متشکرم… انشالله بابت آموزش رایگانی که در اختیار گذاشتید برکت وارد زندگیتون بشه

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

آموزش خوبی بود. ممنونم ازتون. کار من رو راه انداخت.

ممنون

ممنون از توضیحات تون . فقط یک مشکلی که وجود دارد این است که نتایج حاصل sort نمی شوند و باید آنها را در ستون دیگری Paste Value کرد سپس مرتب نمود . می خواستم ببینم راه بهتری که بصورت فرمولی بشود پیاده سازی کرد وجود دارد یا خیر ؟…با سپاس

سلام وقت بخیر برای استفاده از توابع آیا اکسل تنظیماتی لازم دارد ؟ من هرچه در یک سلول توابع vlookup,index,match می نویسم در سلول مورد نظر عینا فرمول نوشته شده ظاهر می گردد و نتیجه بدست نمی آید قبل از توابع مورد نظر = را درج می کنم مثل index(K2:L15,match(P3,K2:K15,0),12)= می خواهم از سلول k2 در لیستk2:p15 مقدار L را در P3 درج شود . ایکاش می توانستم تصویرش را بریتان ارسال کنم

دوست عزیز ، از زبانه home نوع سلول را بر روی general بذارید

عالی بود . ممنون

سلام و خسته نباشید
یه مشکلی برام پیش آمده در بانک اطلاعاتی محل کارم.
همونطور که می دونید ایندکس و مچ فقط اولین اطلاعات را که بهش میرسه نشون میده حتی اگر 30 بار هم فرمول را در هر سطر تکرار کنیم.
چطور میشه فرمولی نوشت که مثلا در ردیف اول اولین حاصل جستجو ، در دومین ردیف دومین و …
ممنون می شم از راهنماییتون

سلام خسته نباشید
کاری که گفتید رو انجام دادم، یک لیست از نفرات و نمره ها رو ایجاد کردم میخوام رتبه بندی نفراتی که بالاترین نمره رو میگیرند همیشه بصورت خودکار انجام بشه
الان با فرمول Large به ترتیب نمرات انجام میشه
با ترکیب فرمول MATCH+INDEX هم نفراتی که نمره متعلق به اونهاست پیدا میشه
فقط مشکل اینجاست که نفراتی که نمره مشابه دارند، نام اولین نفری که پیدامیکنه رو برای بقیه تکرار میکنه.
نام نمره
علی 40
حسن 30
محمد 40
محسن 52
بعد از استفاده از فرمول، نتیجه اینطوری میاد:
محسن 52
علی 40
علی 40
حسن 30
یعنی اسم محمد رو که نمره 40 داره نمیاره. اگر امکان داره یکی از اساتید فرمول رو اصلاح کنند:
=INDEX($A$2:B13,MATCH(F3,$B$2:B15,0),1)
باتشکر

شما باید برای هر نفر یک شماره مخصوص یا کد اختصاص بدید که این کد به صورت یکتا باشه با این کار مشکل حل میشه

این مورد مشکل من هم هست،جوابش چیه؟؟؟

درود بر شما
عالی بود
سپاس

نظر شما چیست؟

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