تابع MATCH در اکسل — آموزش با مثال‌های کاربردی

جستجو و پیدا کردن یک مورد خاص، هنگامی که با یک کاربرگ اطلاعاتی مانند اکسل، با تعداد سطرها یا ستون‌های زیاد مواجه هستید، کار مشکلی است. به این منظور اغلب از روش‌های مرتب‌سازی یا دسته‌بندی برای تفکیک گروه‌ها استفاده کرده و به این ترتیب سرعت جستجو را افزایش می‌دهند. توجه داشته باشید که در اینجا منظورمان جستجو با هدف دسترسی به یک مورد از اطلاعات (سطر یا ستون) کاربرگ است و هرگز از جستجو با ابزار FIND یا SEARCH اکسل، صحبت نمی‌کنیم. تابع MATCH در اکسل با این کارکرد در اختیارمان قرار گرفته و به خوبی از عهده پیدا کردن موضوع مورد جستجو و مطابقت دادن یک سلول با آن بر می‌آید. به همین دلیل این نوشتار از مجله فرادرس را به تابع MATCH در اکسل اختصاص داده‌ایم.

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

تابع MATCH در اکسل

همانطور که گفته شد، MATCH یک تابع در اکسل است که در نسخه ۲۰۰۳ معرفی شده و برای تعیین موقعیت مقدار جستجو شده، در یک ردیف یا ستون از جدول یا کاربرگ، استفاده می‌شود.

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

تابع MATCH از تطبیق تقریبی و دقیق و همچنین علامت‌های جایگزین یا فرانویسه‌ها (Wildcards) مانند * و ? پشتیبانی می‌کند. اغلب، تابع MATCH با تابع INDEX ترکیب می‌شود تا یک مقدار را جستجو کرده و در یک موقعیت مشخص شده، محتویات سلول مورد نظر را بازیابی کند.

از آنجایی که کار این تابع مانند تابع Lookup، جستجو در کاربرگ است، قاعدتا باید در گروه Lookup and Reference قرار گرفته باشد. بنابراین طبق مسیر زیر می‌توانید به آن دسترسی داشته باشید.

از برگه Formula، در بخش Function Library، لیست توابع Lookup and Reference را باز کرده و تابع MATCH را انتخاب کنید. با این کار، پنجره درج پارامترهای این تابع ظاهر خواهد شد. شکل دستوری و پارامترهای این تابع در ادامه قابل مشاهده است.

=MATCH (lookup_value, lookup_array, [match_type])

واضح است که دو پارامتر اول، اجباری بوده و پارامتر سوم (Match_type) اختیاری است. معرفی این پارامترها در جدول زیر صورت گرفته است.

جدول ۱: معرفی پارامترهای تابع MATCH

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

منظور از موقعیت نسبی مقدار جستجو شده در این بردار این است که تابع MATCH مشخص می‌کند که عنصر یا مقدار lookup_value، چندمین مقدار در لیست یا بردار lookup_array است. برای مثال نتیجه این تابع، ممکن است برابر با ۵ باشد. این موقعیت ممکن است در سطر دهم کاربرگ باشد ولی مهم آن  است که پنجمین مقدار از بردار مورد نظر، پاسخ تابع خواهد بود.

پارامتر سوم، که البته اختیاری است، برای نوع جستجو، مقداری را در نظر گرفته است. اگر به دنبال مقدار دقیق lookup_value هستید، این پارامتر را صفر در نظر بگیرید. به این ترتیب تابع MATCH همگی مقادیر موجود در بردار lookup_array را مورد بررسی قرار داده و اولین موقعیتی که مطابقت در آن صورت گرفته را گزارش می‌کند.

مقدار ۱ و ۱- برای این پارامتر، به منظور سرعت بخشیدن به جستجو و پیدا کردن نزدیک‌ترین پاسخ برای موضوع مورد جستجو است. در ادامه به مثال‌هایی خواهیم پرداخت که کاربرد تابع MATCH در اکسل را با توجه به پارامترهای آن بهتر درک کنید. برای این کار از یک فایل داده استفاده خواهیم کرد که در تصویر ۱ مشخص شده است.

جستجو و بازیابی موقعیت با تابع MATCH در اکسل

از تابع MATCH برای تعیین موقعیت یک مقدار در یک محدوده یا آرایه استفاده می‌شود. در تصویر ۲، ناحیه یا بردار B6 تا B14 با اسامی میوه‌ها دیده می‌شود. تابعی که در سلول E6 ثبت شده، باید به دنبال مقدار Peach گشته و موقعیت آن را طبق لیست، مشخص کند.

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

کلیک کنید

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

تابع و فرمول به کار رفته را در زیر مشاهده می‌کنید.

1=MATCH(D6,B6:B14,0)

نکته: این لیست مرتب شده نیست. در نتیجه برای پیدا کردن مقدار مورد جستجو، پارامتر اختیاری (match_type) را با مقدار ۰ مشخص کرده‌ایم تا تابع MATCH در اکسل همه لیست را جستجو کند.

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

استفاده از علامت‌های جایگزین در تابع MATCH

وقتی نوع انطباق یا پارامتر سوم تابع MATCH، مقدار صفر تنظیم شود، این تابع می‌تواند یک مطابقت را با استفاده از علامت‌های جایگزین یا فرانویسه (Wildcards) انجام دهد. توجه داشته باشید که علامت * نشانگر جایگزین هر گونه علامت یا حروف در محل تعیین شده است. برای مثال اگر عبارتی به صورت *p را به عنوان پارامتر اول تابع MATCH به کار برید، منظور پیدا کردن اولین موردی از اسامی میوه‌ها است که با حرف p شروع می‌شود. واضح است که بر طبق تصویر ۱، نتیجه برابر با ۲ خواهد بود، زیرا اولین میوه با حرف p همان Pear است که در لیست موجود، دومین میوه محسوب می‌شود.

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

1=MATCH("p*",B3:B11,0)

البته می‌توانستیم که مقدار *p را در یک سلول وارد کرده و از آن به عنوان پارامتر استفاده کنیم. فرض کنید که عبارت *p در سلول E2 نوشته شده. بنابراین فرمول زیر همان نتیجه بالا را خواهد داشت.

1=MATCH(E2,B3:B11,0)

پارامتر نوع مطابقت

همانطور که در جدول ۲ مشاهده خواهید کرد، 3 حالت مختلف برای نوع مطابقت یا پارامتر سوم تابع MATCH وجود دارد.

فیلم آموزش اکسل – ابزارهای کاربردی Excel در فرادرس

کلیک کنید

نوع مطابقت یعنی match_type در تابع MATCH اختیاری است. در صورت عدم تعیین آن، پیش فرض مقدار 1 خواهد بود.

جدول 2: ویژگی‌های پارامتر نوع مطابقت

فرض کنید باز هم از لیستی استفاده کرده‌ایم که مطابق با تصویر ۳ است. برای مثال فرض کنید که قرار است مقدار 575 در ستون B3 تا B12 مورد جستجو قرار گرفته و موقعیت آن مشخص شود.

همانطور که می‌بینید، در این لیست مقداری برابر با ۵۷۵ وجود ندارد. اگر تابع MATCH را به صورت زیر می‌نوشتیم، مقدار !N/A# ظاهر می‌شد، زیرا مورد جستجو در لیست وجود ندارد و پارامتر سوم نیز مقدار صفر دارد.

1=MATCH(E2,B3:B11,0)

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

1=MATCH(E2,B3:B11,1)

نکته: نتیجه اجرای این محاسبه، با توجه به تصویر ۳، مقدار ۵ خواهد بود. واضح است که ۵۰۰ نزدیک‌ترین مقدار به ۵۷۵ است که از آن نیز کوچکتر است. البته شاید تصور شود که ۶۰۰ به ۵۷۵ نزدیک‌تر است ولی مقدار آن بزرگتر از ۵۷۵ است.

این بار فرض کنید که لیست را به صورت نزولی (از زیاد به کم) مرتب کرده‌ باشیم و بخواهیم نزدیک‌ترین مقدار به ۵۷۵ را پیدا کنیم. مشخص است که طبق جدول ۲، باید پارامتر سوم را با ۱- مقدار دهی کنیم. این امر به تابع MATCH می‌گوید، از بین لیستی که به صورت زیاد به کم مرتب شده (Descending)، نزدیک‌ترین مقدار به ۵۷۵ را از بین مقادیر بزرگتر پیدا کن. به این ترتیب کوچکترین مقداری که از ۵۷۵ بزرگتر است، پاسخ نهایی خواهد بود.

1=MATCH(E2,B3:B11,-1)

اکسل شروع به جستجو می‌کند، اولین مقدار یعنی ۹۰۰ از ۵۷۵ بزرگتر است، پس به گزینه بعدی رفته و ۸۰۰، ۷۰۰ و ۶۰۰ را مورد بررسی قرار می‌دهد. همه این مقادیر از ۵۷۵ بزرگتر هستند. مقدار بعدی برای مقایسه، ۵۰۰ است که از ۵۷۵ بزرگتر است. در این هنگام اکسل یا دقیق‌تر، تابع MATCH در اکسل متوجه می‌شود که پاسخ را رد کرده و موقعیت نسبی مقدار قبلی در لیست را به عنوان نتیجه تقریبی نمایش می‌دهد. بنابراین پاسخ تابعی که در بالا نوشته شده، مقدار ۴ خواهد بود.

اطمینان حاصل کنید که نوع انطباق را به درستی انتخاب کرده‌اید. استفاده از مقدار پیش فرض برای پارامتر match_type (یعنی مقدار 1) می‌تواند باعث شود تابع MATCH نتایج به ظاهر درستی ارائه کند. ولی با کمی کندوکاو، متوجه می‌شویم که حاصل محاسبه تابع MATCH نادرست بوده و با واقعیت مطابقت ندارد. بنابراین تعیین مقدار مناسب برای match_type، در محاسبات صورت گرفته توسط این تابع، نقش مهمی ایفا می‌کند.

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

نکاتی در مورد تابع MATCH

در ادامه به بعضی نکات اشاره می‌کنیم که هنگام استفاده از MATCH باید در نظر گرفته شود. رعایت این نکته‌ها، کارکرد بهتری از تابع MATCH در اکسل برایتان به همراه می‌آورد.

فیلم مجموعه آموزش اکسل – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

• MATCH به حروف کوچک و بزرگ لاتین  (Case Sensitive) حساس نیست. بنابراین با خیال راحت به جستجو عبارت و کلمه‌های انگلیسی بپردازید.
• اگر موردی برای مطابقت پیدا نشود، تابع MATCH خطای !N/A# را نشان می‌دهد.
• تابع MATCH فقط با متن تا حداکثر 255 نویسه (Character) کار می‌کند. بنابراین اگر به دنبال عبارت‌های طولانی می‌گردید، استفاده از تابع MATCH مناسب نخواهد بود.
• در صورتی که مقدار مورد جستجو در چندین بخش از بردار lookup_array تکرار شده باشد، تابع MATCH اولین مطابقت را نشان می‌دهد و از نمایش موقعیت‌های بعدی عاجز است.
• اگر مقدار پارامتر match_type برابر با ۱- یا 1 باشد، آرایه یا بردار مربوط به پارامتر lookup مرتب شده در نظر گرفته می‌شوند. بنابراین اگر این لیست مرتب نیست، ممکن است پاسخ‌های نامناسب از تابع MATCH دریافت کنید.
• اگر مقدار پارامتر match_type برابر با 0 باشد، مقدار پارامتر lookup_value می‌تواند شامل علامت‌های جایگزین (Wildcard) باشد.
• تابع MATCH اغلب همراه با تابع INDEX استفاده می‌شود تا بتوان مقدار موقعیت‌های دیگر که نسبت به محل مورد جستجو وجود دارند را نشان داد.
• تابع دیگری که در نسخه‌های جدید اکسل 365 جایگزین MATCH شده است، XMATCH نام‌ دارد ولی تقریبا کارکرد آن‌ها یکسان است.

خلاصه و جمع‌بندی

همانطور که خواندید، تابع MATCH در اکسل قادر به جستجو در یک بردار بوده و موقعیت نسبی مقدار تطبیق داده شده را نشان می‌دهد. توجه داشته باشید که موقعیت توسط تابع MATCH به صورت مطلق و مطابق با آدرس کاربرگ اکسل نیست. منظور از موقعیت نسبی، وابستگی نتیجه تابع به آدرس یا وضعیت آغاز جستجو است. این موضوع، حساسیت تعیین پارامتر lookup_array را نشان می‌دهد.

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