آموزش تابع Match در اکسل به زبان ساده

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

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

match در اکسل چیست؟

تابع MATCH در اکسل می‌تواند مطابقت‌های دقیق و تقریبی انجام دهد و از حروف عام (‎*?‎) برای مطابقت‌های جزئی پشتیبانی می‌کند.

 

3 حالت تطبیق جداگانه (set by the matchtype argument) وجود دارد که در ادامه شرح match excel آن را توضیح می‌دهیم.

• نکته ١: تابع MATCH همیشه اولین match را برمی‌گرداند. اگر نیاز به بازگشت آخرین مطابقت (جستجوی معکوس) دارید، تابع XMATCH را ببینید. اگر می‌خواهید همه موارد منطبق را برگردانید، تابع FILTER را ببینید.
• نکته ٢: زمانی که به موقعیت یک مورد در یک محدوده به جای خود مورد نیاز دارید، به جای یکی از توابع LOOKUP در اکسل از MATCH استفاده کنید.

MATCH فقط از آرایه‌ها یا محدوده‌های یک‌بعدی، عمودی یا افقی پشتیبانی می‌کند. با این حال، می‌توانید از MATCH برای مکان‌یابی مقادیر در یک محدوده یا جدول دوبعدی با دادن ستون (ردیف) تکی MATCH که حاوی مقدار جستجو است، استفاده کنید. حتی می‌توانید از MATCH دو بار در یک فرمول استفاده کرده تا یک ردیف و ستون منطبق را به طور هم‌زمان پیدا کنید.

اغلب، تابع MATCH با تابع INDEX ترکیب می‌شود تا یک مقدار را در یک موقعیت خاص (منطبق) بازیابی کند؛ به‌عبارت‌دیگر، MATCH موقعیت را مشخص می‌کند و INDEX مقدار آن موقعیت را برمی‌گرداند.

توابع match و index در اکسل

function MATCH معمولاً همراه با تابع INDEX استفاده می‌شود. فرمول به دست آمده INDEX and MATCH نام دارد. به عنوان مثال، در صفحه زیر از INDEX و MATCH برای برگرداندن هزینه کد وارد شده در سلول F4 استفاده می‌شود. فرمول در F5 به صورت زیر است:

‎=INDEX(C5:C12,MATCH(F4,B5:B12,0)) // returns 150

فرمول تابع match در اکسل – Match Formula

فرمول‌های match در اکسل به صورت زیر مورد استفاده قرار می‌گیرند:

‎=MATCH(lookup_value, lookup_array, [match_type])

• Lookup_value : آرگومانی لازم و مقداری است که ما به دنبال آن هستیم.
• Lookup_array : آرگومانی لازم و آرایه داده‌ای که قرار است جستجو شود.
• Match_type : آرگومانی اختیاری است که می‌تواند سه مقدار زیر را بازگراند.

1 یا حذف شده: هنگامی که تابع نتواند مطابقت دقیق را پیدا کند، موقعیت نزدیکترین تطابق را در زیر lookup_value برمی‌گرداند. (در صورت استفاده از این گزینه، lookup_array باید به ترتیب صعودی باشد).

0: وقتی تابع نتواند مطابقت دقیقی را پیدا کند، یک خطا برمی‌گرداند. (در صورت استفاده از این گزینه، نیازی به مرتب سازی lookup_array نیست).

-1: وقتی تابع نتواند مطابقت دقیقی را پیدا کند، موقعیت نزدیکترین تطابق را بالاتر از lookup_value برمی گرداند. (در صورت استفاده از این گزینه، lookup_array باید به ترتیب نزولی باشد).

مثالی از فرمول‌ های match در اکسل

تصور کنید ما اطلاعات زیر را در اختیار شما قرار داده‌ایم:

بر طبق اطلاعات بالا می‌خواهیم ارزش cucumber را بدست بیاوریم:

 

در نتیجه پاسخ آن به صورت زیرخواهد بود:

کاربرد match case در اکسل

همان‌طور که تا اینجا متوجه شده‌اید وظیفه match موقعیت‌یابی است، match برای محدود کردن روش‌های جستجو استفاده می‌شود. شما می‌توانید در برنامه‌ی اکسل به منوی Edit مراجعه و گزینه‌ی Find را انتخاب كنید. بعد از باز شدن صفحه‌ی تنظیمات گزینه‌ی option را انتخاب نمایید حالا می‌توانید در این صفحه match case را مشاهده کنید. در واقع match case، به نوعی روش جستجو را محدود می‌کند که شما که دقیقاً کلمه‌ی مشابه را جستجو می‌کند اگر کلمه با حروف بزرگ باشد و شما با حروف کوچک جستجو کنید کلمه پیدا نمی‌شود برای همین این گزینه یک گزینه برای جستجو با دقت بالا است.

 

در اصل MATCH در اکسل تابعی است که برای تعیین موقعیت یک مقدار جستجو در یک ردیف، ستون یا جدول استفاده می‌شود. MATCH با تابع INDEX ترکیب می‌شود تا یک مقدار را در یک موقعیت منطبق بازیابی کند. تابع MATCH یک آیتم مشخص شده را در محدوده‌ای از سلول‌ها جستجو می‌کند و سپس موقعیت نسبی آن آیتم را در محدوده برمی‌گرداند. برای مثال، اگر محدوده A1:A3 حاوی مقادیر 5، 25 و 38 باشد، فرمول =MATCH(25,A1:A3,0) عدد 2 را برمی‌گرداند، زیرا 25 دومین مورد در محدوده است.

پیشنهاد مطالعه: آموزش ایجاد چک باکس در اکسل

تابع match index در اکسل

تابع match یکی عملکرد داخلی نرم‌افزار اکسل است که در زمینه‌های مختلف کاربرد دارد و می تواند به عنوان یک تابع کاربرگ (WS) در اکسل استفاده شود. اگر بخواهیم به صورت خلاصه کار این تابع را بیان کنیم بهتر است بگوییم که این تابع مقداری را  در یک بازه مشخص یک ردیف، ستون یا جدول جست و جو می‌کند و در صورت پیداکردن آن،مقدار مورد نظر را بازیابی می‌کند.

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

Formulas>lookup & reference>match

قاعده‌ی کلی این نرم‌افزار به صورت زیر است:

Match (دقت جستجو، محدوده جستجو، مقدارجستجو)

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

مقدار جستجو (lookup_value): آرگومان lookup_value می تواند یک عدد ، متن یا مقداری منطقی باشد که ازمواردالزامی این فرمول است.به عنوان مثال، وقتی شماره شخصی را در دفترچه تلفن جستجو می کنید، از نام شخص به عنوان مقدار جستجو استفاده می‌کنید، اما شماره تلفن چیزی است که در اصل به دنبال آن هستید.

محدوده  جستجو (lookup_array): محدوده جستجواز موارد الزامی فرمول match است و محدوده سلول‌هایی که می‌خواهید بررسی شوند را مشخص می‌کند.

دقت جستجو (matchtype): match_type  از موارد اختیاری این فرمول است. دقت جستجو نحوه مطابقت اکسل با lookup_value و مقادیر در lookup_array را مشخص می‌کند. مقدار پیش فرض این آرگومان، در صورتی که مقدار دقت جستجو در تابع وارد نشود این ۱ است. مقدارهایی که این آرگومان می‌گیرد به شرح زیر است:

• ۱ یا هیچی که حالت پیش فرض آن است بزرگترین مقدار موجود در سلول را که کوچکتر مساوی مقدار جستجو است را پیدا می‌کند. (در این حالت آرگومان باید به صورت افزایشی تنظیم شود.)
• ۰ اولین مقداری را که در سلول با مقدار جستجو یکی باشد را برمی‌گرداند. (اگر مقدار جستجو متنی باشد حتما باید مقدار دقت جستجو صفر باشد.)
• ۱- کوچکترین مقدار در سلول را که بزرگتر مساوی مقدار جستجو باشد را برمی‌گرداند. (در این حالتآرگومان باید به صورت کاهشی تنظیم شود.)

۴ نکته در مورد تابع match در اکسل 

همانطور که قبلاً مشاهده کردید استفاده از MATCH در اکسل کار  راحتی است آسان است و با مقداری تمرین می‌شود مهارت لازم  در استفاده از این تابع به دست بیاورید،در این میان چند ویژگی هم وجود دارد که دانستن آن‌ها می‌تواند در این امر به شما کمک کند:

• تابع MATCH موقعیت نسبی مقدار جستجوی آرایه را برمی‌گرداند، نه خود مقدار را.
• تابع match به حروف کوچک و بزرگ حساس نیست، به این معنی که بین کاراکترهای کوچک و بزرگ تمایزی قائل نیست.
• اگر سلول جستجو شده شامل چندین مورد از مقدار جستجو باشد، موقعیت اولین مقدار بازگردانده می‌شود.
• اگر مقدار جستجو در آرایه جستجو یافت نشد، خطای # N / A بازگردانده می‌شود.

غالباً، عملکرد MATCH به منظور بازیابی مقداری در یک موقعیت خاص (همسان) با تابع INDEX ترکیب می‌شود. به عبارت دیگر، MATCH موقعیت را مشخص می‌کند و INDEX مقدار را در آن موقعیت بازیابی می‌کند.

تابع INDEX در اکسل

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

• ‎INDEX (شماره ستون، شماره ردیف، ناحیه)‎‎
• اگر قرار باشد مقدار را از چند ناحیه انتخاب کنیم فرمول به این صورت است:
• INDEX(شماره ناحیه، شماره ستون، شماره ردیف، ناحیه‌ها )

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

ورودی‌های تابع ایندکس در اکسل به صورت زیر هستند:

• ناحیه (Array): محدوده‌ای از سلول‌ها یا یک ثابت
• شماره ردیف (row_num): موقعیت ردیف در مرجع یا ناحیه
• شماره ستون (col_num): وارد کردن این مورد اختیاری است و نشان دهنده‌ی موقعیت ستون در مرجع وناحیه است.
• شماره ناحیه (area_num): وارد کردن این مورد هم اختیاری است و به شماره‌ی محدوده مرجعی که باید استفاده شود اشاره دارد.

 

عملکرد تابع index

عملکرد INDEX به دو شکل است: ناحیه و مرجع. رفتار هر دو فرم یکسان است – INDEX مرجعی را در یک ناحیه بر اساس یک ردیف و ستون مشخص برمی‌گرداند.

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

• فرم ناحیه‌ای: در فرم ناحیه‌ای INDEX، اولین پارامتر ناحیه است که به صورت طیفی از سلول‌ها یا یک ثابت ناحیه‌ای ارائه می‌شود.

• فرم مرجع: در فرم مرجع INDEX، اولین پارامتر اشاره به یک یا چند دامنه است و چهارمین آرگومان اختیاری، area_num، برای انتخاب دامنه مناسب ارائه می‌شود.

مانند فرم ناحیه‌ای INDEX، فرم مرجع INDEX مرجع سلول را در تقاطع row_num و col_num بازیابی ‌می‌کند. تفاوت در این است که آرگومان مرجع شامل بیش از یک محدوده است و area_num انتخاب می‌کند که کدام دامنه استفاده شود. به طور خلاصه، INDEX بر اساس موقعیت عددی در یک مکان مشخص در یک محدوده سلول مقداری دریافت می‌کند. وقتی دامنه یک بعدی است، فقط باید یک شماره ردیف وارد کنید.

سخن پایانی

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

یادگیری اکسل

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