آموزش تابع XMATCH در اکسل

تابع XMATCH از توابع جستجو (Lookup & Reference) بوده و برای تعیین موقعیت یک مقدار خاص در یک سطر، ستون و یا آرایه به کار می‌رود. این تابع جایگزینی مناسب و منعطف برای تابع MATCH است و از تطابق دقیق، تقریبی، جزئی و جستجوی معکوس پشتیبانی می‌کند.

هدف تابع

تعیین موقعیت داده در یک ستون، ردیف و یا آرایه

خروجی

موقعیت داده در محدوده مدنظر

ساختار

=XMATCH(lookup_value,lookup_array,[match_mode],[search_mode])

آرگومان‌ها

lookup_value: مقدار مورد جستجو
lookup_array: آرایه یا محدوده‌ای که داده مدنظر در آن قرار دارد
match_mode [اختیاری]: نوع مطابقت (دقیق، دقیق یا بزرگتر، دقیق یا کوچکتر و یا جزئی)
search_mode [اختیاری]: حالت جستجو (از بالا، از پایین، از بزرگترین و یا از کوچکترین)

کاربرد

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

=XMATCH(D5,B5:B10)

ترجمه: مقدار سلول D5 را در محدوده B5 الی B10 پیدا کن و نشان بده چندمین داده در این محدوده است.

مطابقت دقیق

تابع XMATCH دارای چهار حالت تطبیق «تطابق دقیق، تطابق دقیق یا کوچک‌ترین بعدی، تطابق دقیق یا بزرگ‌تر بعدی و تطابق جزئی» است که توسط آرگومان سوم (match_mode) تعیین می‌شود. این آرگومان اختیاری بوده و در صورت نادیده گرفته شدن، حالت پیشفرض (تطابق دقیق) را اعمال خواهد کرد.

معمولا زمانیکه از XMATCH استفاده می‌کنیم، انتظار تطبیق دقیق داریم. به همین دلیل اکسل این نوع جستجو را حالت پیشفرض این تابع قرار داده و در مواقعی که به دنبال تطابق دقیق باشیم، می‌توان این آرگومان را نادیده گرفت و یا مقدار ۰ وارد کرد. برای مثال قصد داریم جایگاه عدد ۵۰۰۰ را در آرایه {۱۵۶۹,۲۳۷۵,۳۳۵۱,۴۲۷۰,۵۰۰۰} بازگردانیم. بدین منظور می‌توانیم هر یک از فرمول‌های زیر را بنویسیم:

=XMATCH(5000,{1569,2375,3351,4270,5000})--->5
=XMATCH(5000,{1569,2375,3351,4270,5000},0)--->5

تطابق تقریبی

هنگامیکه بخواهیم در صورت عدم تطابق، بهترین تطبیق بازگردانده شود، در آرگومان match_mode عدد ۱ را برای مقدار بزرگتر و -۱ را برای مقدار کوچکتر وارد می‌کنیم. برای مثال می‌خواهیم موقعیت عدد ۲۵۰۰ را در آرایه مثال قبل در دو حالت کوچکترمساوی و بزرگترمساوی جستجو کنیم:

=XMATCH(2500,{1569,2375,3351,4270,5000},-1)--->2

ترجمه: عدد ۲۵۰۰ را در آرایه وارد شده جستجو کن و در صورت شکست، نزدیک‌ترین داده کوچکتر از ۲۵۰۰ را پیدا کن و موقعیت آن را نشان بده.

=XMATCH(2500,{1569,2375,3351,4270,5000},1)--->3

*XMATCH مانند تابع MATCH نیاز به داده‌های سورت شده ندارد.

مطابقت جزئی

XMATCH نیز همانند تابع MATCH از کارکترهای wildcard پشتیبانی می‌کند؛ با این تفاوت که باید به آن بفهمانیم به دنبال تطابق جزئی هستیم. بدین منظور می‌بایست در آرگومان match_mode مقدار ۲ وارد کنیم. این ویژگی زمانی کاربرد دارد که بخواهیم با وارد کردن قسمتی یا کل شرح، مقدار متناظر آن را بیابیم. برای مثال قصد داریم موقعیت عددی “شلیل” را در بازه B5 تا B10 بازگردانیم. برای اینکار فرمول را به این شکل می‌نویسیم (به دلیل متنی بودن آرگومان اول، قبل و بعد آن از ” استفاده شده است):

=XMATCH("شلیل",B5:B10,2)

شیوه جستجو

به طور پیش‌فرض، XMATCH از اولین مقدار داده شروع به تطبیق می‌کند اما با استفاده از آرگومان چهارم این تابع با عنوان search_mode می‌توان شیوه جستجو را کنترل کرد. این آرگومان اختیاری بوده و زمانی که نادیده گرفته شود از حالت پیشفرض خود استفاده می‌کند. عدد ۱ از اولین داده، عدد -۱ از آخرین داده، عدد ۲ از کوچکترین داده و عدد -۲ از بزرگترین داده جستجو را آغاز می‌کند.

=XMATCH(3,{4,3,1,3,2},,1)--->2
=XMATCH(3,{4,3,1,3,2},,-1)--->4
=XMATCH(3,{4,3,1,3,2},,2)--->4
=XMATCH(3,{4,3,1,3,2},,-2)--->2

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

=XMATCH(3,{2,3,1,5,4},,2)

تابع XMATCH با توجه به آرگومان چهارم، جستجو را از کوچکترین داده شروع می‌کند که در این صورت اعداد ۲ و ۳ نادیده گرفته و آرایه مورد جستجو تبدیل به {۱,۵,۴} شده که فاقد عدد ۳ است؛ به همین دلیل خطای #N/A بازمی‌گرداند.

معیارهای چندگانه

تابع XMATCH راه داخلی برای جستجو با معیار چندگانه ارائه نمی‌دهد اما از آن پشتیبانی می‌کند. برای درک بهتر این موضوع فرض کنید چند بازاریاب داریم که محصولات متفاوت می‌فروشند. حال میخواهیم ردیف فروش محصول “شیر” توسط بازاریاب “۱۰۰۴” را بازگردانیم. بدین منظور فرمول XMATCH را به شکل زیر می‌نویسیم:

=XMATCH(1,(B3:B8=G2)*(C3:C8=G3))

ترکیب توابع XMATCH و INDEX

XMATCH را می‌توان همانند MATCH با تابع INDEX ادغام نمود. برای روشن شدن موضوع، XMATCH را در مثال ادغام توابع MATCH و INDEX جایگزین کرده و فرمول را بصورت زیر می‌نویسیم:

=INDEX(C3:C7,XMATCH(E3,B3:B7))

مقایسه XMATCH و MATCH

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

1. XMATCH به طور پیش‌فرض مطابقت دقیق انجام می‌دهد؛ اما مطابقت پیشفرض تابع MATCH، تطابق تقریبی است.
2. XMATCH نیازی به مرتب‌سازی مقادیر در هنگام انجام یک تطابق تقریبی ندارد.
3. در تطابق تقریبی، تابع XMATCH می‌تواند مورد بزرگتر بعدی یا کوچکتر بعدی را پیدا کند؛ درحالیکه MATCH موقعیت یک داده قبل را بازمی‌گرداند.
4. تابع XMATCH برای تطابق جزئی نیاز به دستور دارد.
5. در تابع XMATCH می‌توان جستجوی معکوس انجام داد.
6. XMATCH می‌تواند یک جستجوی باینری انجام دهد که به طور خاص سرعت پردازش را بهینه می‌کند (مقدار ۲ و -۲ در آرگومان جستجو).
7. تابع XMATCH از معیارهای چندگانه پشتیبانی می‌کند.

ورژن مورد نیاز

اکسل ۲۰۲۱ و بالاتر

پی‌نوشت

*XMATCH به حروف بزرگ و کوچک حساس نیست.
*اگر مطابقت پیدا نشود، XMATCH خطای #N/A برمی‌گرداند.
*XMATCH فقط از آرایه‌ها یا محدوده‌های یک بعدی (عمودی و یا افقی) پشتیبانی می‌کند. با این حال، می‌توان با ترکیب نمودن این تابع و INDEX، مکان مقادیر را در یک محدوده یا جدول دو بعدی تعیین نمود. همچنین می‌توانید دو بار از XMATCH در یک فرمول استفاده کرده تا یک ردیف و ستون منطبق را به طور همزمان پیدا کنید.
*اگر می‌خواهید همه موارد منطبق را برگردانید، از تابع FILTER استفاده نمایید.