در این مطلب نحوه استفاده از تابع match را به همراه مثال های فرمولی ارائه خواهیم کرد. همچنین نحوه بهبود فرمول های جستجوگر خود را با ترکیب تابع match و vlookup نشان خواهیم داد.

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

• تابع match در اکسل – ترکیب و کاربرد های اولیه
• نحوه استفاده از match در اکسل – مثال های فرمولی
  • فرمول های match در اکسل همراه با کاراکترهای wild card
  • فرمول های match حساس به حروف بزرگ و کوچک
  • مقایسه دو ستون به لحاظ تفاوت ها و اشتراکات (isna match)
  • توابع match و vlookup در اکسل
  • توابع match و hlookup در اکسل

تابع match در اکسل – ترکیب و کاربرد

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

ترکیب تابع match به شکل زیر است:

([MATCH(lookup_value, lookup_array, [match_type=

Lookup_value(الزامی): مقداری که میخواهید پیدا کنید. این مقدار میتواند عدد، متن، آدرس سلول یا مقدار منطقی باشد.

Lookup_array(الزامی): محدوده ای از سلول ها که عمل جستجو در این محدوده انجام میشوند.

Match_type(اختیاری): نوع مطابقت را مشخص میکند. این مقدار میتواند 0,1,-1 باشد. وقتی این پارامتر را 0 قرار دهید مطابقت دقیق، و دو مقدار دیگر مطابقت جزئی را جستجو میکنند.

• 1 یا خالی(پیش فرض) بزرگترین مقدار موجود در آرایه را که کوچکتر با مساوی مقدار مورد جستجو باشد را یافت میکند. در صورت انتخاب این مقدار می بایست Lookup_array را به صورت صعودی مرتب کرد یعنی از کوچکتر به بزرگتر و از a تا z
• 0: اولین مقداری را که در آرایه با مقدار مورد جستجو برابر باشد را برمیگرداند. در این مورد مرتب کردن آرایه لازم نیست.
• 1-: کوچکترین مقدار در آرایه را که بزرگتر یا مساوی مقدار مورد جستجو باشد را برمیگرداند. در این حالت می بایست داده های موجود در Lookup_array را به صورت نزولی و برعکس مورد اول مرتب کرد.

برای درک بهتر تابع MATCH بر اساس داده های زیر یک فرمول ساده را تشکیل میدهیم:

نام دانش آموزان در ستون A و نمرات آنان در ستون B قرار گرفته است. و اطلاعات از بزرگتر به کوچکتر مرتب شده است. برای پیدا کردن جایگاه یک دانش آموز ( برای مثال علی) میتوان از فرمول زیر استفاده کرد:

(0,A2:A8,"علی")match=

همچنین میتوانید مقدار مورد جستجو را در یک سلول مثلاً e1 قرار دهید. و آدرس سلول را وارد فرمول کنید:

(MATCH(E1, A2:A8, 0=

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

اگر در هنگام کار با تابع match با خطای #N/A مواجه می شوید، در اینجا سه دلیل عمده ای که این خطا ایجاد می شود و روش های حل آن را بررسی کرده ایم:

خطای #N/A در توابع اکسل و روش ها حل آن

4 چیزی که می بایست در مورد تابع MATCH بدانید:

همانطور که مشاهده کردید، استفاده از تابع MATCH در اکسل کار ساده ای است. ولی مثل دیگر توابع این تابع نیز دارای توضیحاتی است که می بایست از این توضیحات آگاهی داشته باشید:

1. تابع MATCH جایگاه یک مقدار را در یک آرایه برمیگرداند نه خود مقدار را.
2. تابع MATCH نسبت به حروف بزرگ و کوچک حساس نیست. به این معنی که استفاده از حروف بزرگ و کوچک هیچ فرقی ندارد.
3. در صورت که در آرایه مورد جستجو چند مقدار look up value به صورت تکراری وجود داشته باشد، جایگاه اولین مقدار برگردانده میشود.
4. در صورتی که مقدار مورد جستجو یافت نشود تابع MATCH خطای #N/A را برمیگرداند.

نحوه استفاده از MATCH در اکسل –  مثال های فرمولی

حال که با کاربردهای اولیه تابع MATCH آشنا شدید، به بررسی چند مثال فرمولی پیشرفته تر میپردازیم.

تابع MATCH همراه با کاراکتر های wildcard

شبیه به بسیاری از دیگر توابع اکسل، از کاراکترهای زیر در ترکیب با تابع MATCH نیز میتوان استفاده کرد:

• علامت سوال(؟)
• علامت ستاره(*)

نکته: این کاراکترها تنها در صورتی در تابع MATCH قابل استفاده هستند که نوع مطابقت بر روی 0 قرار داده شده باشد.

از کاراکترهای wildcard زمانی استفاده میشود که میخواهید جایگاه چند کاراکتر یا قسمتی از یک رشته را پیدا کنید. برای درک بهتر، به مثال زیر توجه کنید:

فرض کنید لیستی از فروشندگان منطقه ای و مقدار فروش آنها برای ماه قبل را در اختیار دارید. و میخواهید جایگاه نسبی یک فروشنده را در میان لیست فروشندگان (بر اساس مقدار فروش و به صورت نزولی مرتب شده است) پیدا کنید. ولی شما نام این فروشنده را به صورت دقیق به یاد نمی آورید ولی چند کاراکتر ابتدای آن را به خاطر دارید.

فرض کنید لیست فروشندگان در سلول های A2:A11 قرار دارد و در جستجوی نامی هستید که با «امی» شروع میشود. فرمول به شکل زیر خواهد شد:

(0,A2:A11,"*امی")MATCH=

برای فراگیر تر کردن فرمول خود میتوانید lookup value را در یک سلول تایپ کنید و آن سلول را به کاراکترهای wild card متصل کنید:

(MATCH(E1&"*", A2:A11,0=

همانطور که در تصویر زیر نشان داده است، فرمول مقدار 3 را برمیگرداند که محل« امیر» را نشان میدهد.

برای جایگزین کردن یک کاراکتر در lookup value میبایست از علامت سوال استفاده کرد. برای مثال:

(MATCH("ba?er", A2:A11,0=

فرمول بالا نام baker را پیدا میکند و جایگاه نسبی آن را برمیگرداند که 5 است.

فرمول match حساس به حروف بزرگ و کوچک

همانطور که در ابتدای این مطلب ذکر شد، تابع MATCH بین حروف بزرگ و کوچک تمایزی قائل نمیشود. برای حساس کردن این تابع در مورد حروف بزرگ و کوچک، ار ترکیب تابع MATCH و exact استفاده کنید. این تابع کلمات را دقیقاً با هم مقایسه میکند و بزرگ و کوچک بودن حروف را نیر مورد نوجه قرار میدهد:

(MATCH(TRUE, EXACT(lookup array, lookup value), 0=

این فرمول با منطق زیر کار میکند:

• تابع exact مقدار lookup value را با تک تک عناصر موجود در آرایه مورد مقایسه قرار میدهد. در صورتی که یکی از سلول ها دقیقاً مشابه مقدار مورد جستجو باشد، تابع true را برمیگرداند در غیر اینصورت false برگردانده میشود.
• آنگاه تابع MATCH مقدار true(که همان lookup value است) را با هرکدام از مقادیر آرایه ای که توسط تابع exact برگردانده شده است مقایسه میکند و محل اولین مقدار مورد تطابق را برمیگرداند.

لطفاً به یاد داشته باشید که این فرمول یک فرمول آرایه است و می بایست حتماً از کلید های ترکیبی Ctrl + Shft + Enter استفاده کنید.

با فرض اینکه مقدار مورد جستجوی شما در سلول E1 قرار گرفته و آرایه جستجوی شما A2:A9 است، فرمول به شکل زیر خواهد بود:

(MATCH(TRUE, EXACT(A2:A9,E1),0=

تصویر زیر نتیجه فرمول match حساس به حروف بزرگ و کوچک را نشان میدهد:

مقایسه دو ستون به لحاظ مطابقت ها و تفاوت ها(isna match)

مقایسه دو ستون یکی از رایج ترین کارهایی است که در اکسل انجام میشود و میتوان این کار را به شیوه های مختلفی انجام داد. یکی از این شیوه ها استفاده از isna/match است:

("","در لیست 1 نیست",((IF(ISNA(MATCH(1st value in List1, List2, 0=

برای هر مقداری از لیست 2 که در لیست 1 موجود نیست، فرمول مقدار “درلیست 1 نیست” را برمیگرداند. شیوه کار این فرمول در زیر توضیح داده شده است:

• تابع MATCH یک مقدار از لیست 1 را در لیست دو جستجو میکند. در صورتی که یافت شود، جایگاه آن را برمیگرداند و اگر یافت نشود مقدار #n/a برگردانده میشود.
• تابع isna فقط یک کار را در اکسل انجام میدهد.خطای #n/a را جستجو میکند اگر یک مقدار خطای n/a باشد true را برمیگرداند در غیر این صورت false را برمیگرداند. در مثال ما true به این معنی است که یک مقدار که در لیست 1 وجود دارد در لیست 2 وجود ندارد.
• از آنجایی که مقدار true برای مقادیری که در لیست 2 یافت نشده است ممکن است کمی برای کاربران شما گیج کننده باشد، از تابع if استفاده میکنیم تا به جای آن مقدار “در لیست 1 نیست” یا هر متنی که شما میخواهید را برگرداند .

با فرض اینکه مقادیر ستون A را با مقادیر ستون B مقایسه میکنید، فرمول به شکل زیر میشود (سلول B2 بالاترین سلول است):

("","در لیست 1 نیست",((IF(ISNA(MATCH(B2,A:A=

همانطور که گفتیم تابع MATCH به خودی خود نسبت به حروف بزرگ و کوچک حساس نیست. برای حساس کردن این تابع، از تابع excat در آرایه جستجو خود استفاده کنید. و به یاد داشته باشید که کلیدهای ترکیبی Ctrl + Shift + Enter را فشار دهید.

("","در لیست 1 نیست",((IF(ISNA(MATCH(TRUE, EXACT(A:A, B2),0=

تصویر زیر هر دو فرمول را به صورت عملی نشان میدهد:

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

در این مثال فرض شده است که شما اطلاعات پایه ای در مورد تابع vlookup دارید. اگر اینطور باشد، حتماً در مورد محدودیت های این تابع نیز اطلاع دارید و به دنبال یک جایگزین قویتر برای این تابع هستید.

تابع vlookup در اکسل | مثال ها و کاربردها

معرفی تابع match در اکسل، ترکیب vlookup و match در محاسبات بانکی-توابع اساسی اکسل

یکی از بزرگترین محدودیت های این تابع این است که اگر ستون جدید را اضافه کنید یا یکی از ستون ها را حذف کنید تابع از کار می افتد. این اتفاق به این دلیل می افتد که تابع Vlookup مقدار مورد نظر را بر اساس شماره ستونی که در index num مشخص میکنید برمیگرداند. از آنجایی که index num در درون فرمول قرار گرفته و امکان تغییر در آن وجود ندارد، زمانی که یک ستون اضافه میشود یا یکی از ستون ها حذف میشود اکسل قادر به به تغییر این پارامتر نیست.

تابع match در اکسل با جایگاه نسبی lookup value کار میکند و این تابع برای تعیین col_index_num تابع vlookup بسیار مناسب است. به عبارت دیگر، به جای قرار دادن یک عدد برای شماره ستون، میتوانید تابع match را برای بدست آوردن محل ستون مورد نظر استفاده کنید.

برای درک بهتر این موضوع یکبار دیگر جدول دانش آموزان و نمرات آنها را مورد توجه قرار دهید، ولی اینبار نمرات واقعی را و نه جایگاه نمرات را در آرایه به دست میآوریم.

با فرض اینکه lookup value در سلول F1 قرار دارد، table array عبارت است از $A$1:$C$2 ( بهتر است اگر میخواهید فرمول خود را به سلول دیگر ی کپی کنید از آدرس مطلق استفاده کنید)، و فرمول به شکل زیر میشود:

(VLOOKUP(F1, $A$1:$C$8, 3, FALSE=

پارامتر سوم(col_index_num)، 3 قرار داده شده است زیرا «نمره ریاضی» که مورد نظر ماست در سومین ستون جدول قرار دارد. همانطور که در تصویر زیر مشاهده میکنید، یک فرمول vlookup معمولی خوب کار میکند:

ولی زمانی که یک ستون را حذف یا اضافه کنید اتفاق دیگری می افتد:

چرا خطای #ref اتفاق می افتد؟ زیرا(col_index_num) که بر روی 3 قرار داده شده است، به اکسل می گوید که ستون سوم را برگرداند، در حالی که فقط 2 ستون در جدول وجود دارد.

برای جلوگیری از اتفاق افتادن چنین مسئله ای، می توانید با قرار دادن فرمولی  شبیه به فرمول زیر تابع vlookup خود را پویا تر کنید:

(MATCH(E2,A1:C1,0=

• E2 مقدار lookup value است که دقیقاً مشابه نام return col است (ستونی که میخواهید مقدار مورد نظر از آن برگردانده شود. در این مثال نمره ریاضی).
• A1:C1 آرایه جستجو است که سرفصل جدول را نیز در بر میگیرد.

حال، این تابع MATCH را در درون پارامتر col_index_num تابع vlookup  خود قرار دهید. مثل:

(VLOOKUP(F1,$A$1:$C$8, MATCH(E2,$A$1:$C$1, 0), FALSE=

و مطمئن باشید که با حذف یا اضافه کردن هر تعداد ستون این فرمول بدون نقص کار خواهد کرد.

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

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

به همین شیوه، میتوانید برای بهبود تابع hlookup خود نیز از تابع match استفاده کنید. اصول اساسی همانی است که در مورد Vlookup استفاده کردیم. از تابع match برای بدست آوردن محل نسبی return column استفاده می کنید. و مقدار بدست آمده را در row index num تابع hlookup خود قرار میدهید.

فرض کنید مقدار lookup value در سلول B5 قرار دارد و آرایه جستجو B1:H3 است. نام return row در سلول A6 قرار گرفته و header های ردیف در A1:A3 قرار دارند. فرمول کامل به شکل زیر است:

(HLOOKUP(B5, B1:H3, MATCH(A6, A1:A3, 0), FALSE=

همانطور که مشاهده کردید، تابع match میتواند تا حد زیادی به بهبود توابع hlookup و vlookup منجر شود. با اینحال، تابع match نمیتواند تمام محدودیت های این توابع را رفع کند. برای مثال فرمول vlookup هنوزه قادر نیست سمت چپ خود را جستجو کند. و تابع hlookup هیچ ردیفی به جز بالاترین ردیف را قادر به جستجو کردن نیست.

برای رفع این نوع محدودیت ها، میتوانید از ترکیب توابع index و match استفاده کنید. که در بسیاری از موارد و زمینه ها از توابع hlookup و vlookup قوی تر هستند. برای دسترسی به جزئیات و مثال های متعدد فرمولی میتوانید به «چرا ترکیب توابع index و match بهتر از تابع vlookup است؟» مراجعه کنید.

شیوه استفاده از فرمول های match در اکسل توضیح داده شد. امیدواریم مثال های توضیح داده شده در این مطلب، در زمینه شغلی شما نیز کارایی داشته باشد.