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

۲۳۸۲۲ بازدید

آخرین به‌روزرسانی: ۲۳ بهمن ۱۴۰۲

زمان مطالعه: ۲۲ دقیقه

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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