تابع Match :آموزش جامع و رایگان تابع match+فيلم آموزش|آکادمي استاد آموز

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

تابع Match ،برعکس تابع Index  در ماکروسافت اکسل کار می کند .تابع index، مقدار موجود در شماره ستون و سطر مشخص را بر می گرداند .

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

تابع Match به تنهایی و با ترکیب توابع مختلف در اکسل استفاده می شود و بسیار پرکاربرد می باشد .و اغلب برای جستجو و یافتن مقادیر در  نرم افزار Excel  کاربرد دارد .

براي آشنايي با کاربردهاي پيشرفته تابع match مي توانيد از  آموزش کاربرد اکسل در حسابداري استفاده نماييد . 

ساختار تابع Match

Lookup_value:

در این آرگومان مقدار مورد جستجو را می نویسیم.

مقدار مورد جستجو می تواند عدد ،متن،مقدار منطقی ،خروجی تابع و یا آدرس خانه  باشد.

lookup_array:

محدوده مورد جستجو می باشد که می تواند شامل فقط یک ستون و یا فقط یک ردیف باشد.

Match_type:

این آرگومان اختیاری است  و می تواند مقداری را نگیرد .

این آرگومان سه مقدار ورودی می تواند داشته باشد.

• مقدار 1 یا Less than

1. مقدار 1 ، مقدار پیش فرض این آرگومان هم هست.
2. یعنی اگر این آرگومان خالی باشد و مقداری برای آن تعیین نشده باشد ، بصورت پیش  فرض مقدار یک را می گیرد.
3. اگر تابع مقدار مورد جستجو را نیابد بزرگترین مقدار،کوچکتر از مقدار مورد جستجو را نمایش می دهد .
4. پس باید محدوده جستجو بصورت صعودی حتما مرتب شده باشد(A..Z,1..n)
5. به عنوان مثال از چپ به راست : 5-4-3-2-1 و یا a-b-c-d-e-f و  ت-پ-ب-ا
   

• مقدار صفر یا Exact match

1. دقیقا مقدار مورد جستجو را پیدا می کند و نمایش می دهد.
2. اگر مقدار مورد جستجو را نیابد خطا N/A# می دهد .

• مقدار 1- یا Greater than

1. اگر مقدار مورد جستجو پیدا نشد کوچکترین مقدار در محدوده ، که بزرگتر از مقدار مورد جستجو را می یابد و نشان می دهد .
2. در این حالت باید داده ها بصورت نزولی (آخر به اول )مرتب شوند .
3. برای مثال از چپ به راست :(Z-A)(الف …..-ن-و-ه-ی)

برای مثال لیستی از افراد و نمرات دانش آموزان داریم می خواهیم با وارد کردن نام افراد ، مشخص شود فرد در چه شماره سطری قرار دارد

نام فرد مورد جستجو را در خانه B9 می نویسیم .

در خانه B5 کلیک کرده و علامت مساوی و تابع Match را تایپ می کنیم و آرگومان های تابع را به ترتیب زیر وارد می کنیم .

Lookup_value:

• مقدار مورد جستجو را در این قسمت وارد می کنیم.
• می توانیم مقدار “علی” یا آدرس خانه مربوط به علی ،B9 را وارد کنیم .

Lookup_Array:

محدوده مورد جستجو که شامل ستون نام  است از خانه A3 تا A7 را انتخاب می کنیم .

Match_type:

• مقدار صفر را وارد می کنیم تا دقیقا نام علی را بیابد و  نشان دهد .

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

MATCH(B9;A3:A7;0)=

(;A3:A7;0;”علی”)MATCH=

مثال: در لیست نمرات می خواهیم بدانیم نمره 14.5 در کدام ردیف قرار دارد

مقدار آرگومان اول( Lookup_value ):

1. نمره 14.5 می باشد پس آدرس خانه مربوط به 14.5 ، که B9  است را انتخاب می کنیم .
   

آرگومان دوم (Lookup_array):

1. شامل ستون نمره از خانه B3 تا B7 است .

 آرگومان آخر (Match_type) :

1. اگر مقدار آن را صفر قرار دهیم (یعنی دقیقا مقدار 14.5 پیدا شود )
2. اما چون نمره 14.5 در لیست نمرات وجود ندارد اگر فرمول زیر را بنویسیم ، خطای N/ A#  می دهد .

MATCH(B9;B3:B7;0)=

نکته :اگر در صورت نبودن نمره در لیست مورد جستجو نمی خواستیم خطای N/ A#  نمایش داده شود می توانیم از تابع IFNA  که تمام خطاهای N/A # را شناسایی می کند استفاده کنیم .البته از تابع IFERROR  که امکان شناسایی تمام خطاها را دارد هم می توانیم استفاده کنیم .

آرگومان اول تابع Ifna ، تابع Match  را بررسی می کند اگر خروجی این تابع N/A # باشد پیام “نمره در لیست موجود نمی باشد” را نمایش می دهد.

در غیر این صورت مقدار سطر پیدا شده نشان داده می شود .

(“نمره در لیست موجود نیست “;MATCH(B9;B3:B7;0))IFNA=

(“نمره در لیست موجود نیست “;MATCH(B9;B3:B7;0))IFERROR=

اگر آرگومان آخر(Match_type) را یک قرار دهیم.

1. بزرگترین مقدار کوچکتر از 14.5 یافت می شود و نشان داده می شود .
2. البته ابتدا باید ستون نمره باید بطور صعودی مرتب شده باشد(از عدد کوچک به عدد بزرگ )
3. برای مرتب سازی کل مقادیر ستون نمره از B3 تا B7  را انتخاب می کنیم از تب Home گروه Editing گزینه Sort & filter را انتخاب و از زیرمنوی باز شده Sort smallest to largest را کلیک می کنیم
   
4. اگر ستون نمره صعودی مرتب نشود تابع Match ، مقدار صحیح نشان  نمی دهد .
   
5. MATCH(B9;B3:B7;1)=
6. فرمول فوق ، مقدار 3 را بر می گرداند .
7. در ردیف  3 نمره مینا قرار دارد که  عدد 14 است و بزرگترین عدد کوچکتر از 14.5 در لیست است .

اگر برای آرگومان آخر (match_type)، مقدار 1- قرار دهیم .

1. قبل از نوشتن فرمول ، مقادیر ستون نمره را از B3 تا B7 انتخاب و بصورت نزولی (1-2-3-4-5و C-B-A) مرتب می کنیم .
2. از تب Home گروه Editing گزینه Sort & filter را انتخاب و از زیرمنوی باز شده Sort  largest to smallestرا کلیک می کنیم .
3. سپس فرمول زیر را می نویسیم .
4. MATCH(B9;B3:B7;-1)=
5. تابعMatch ، مقدار 2 را باز می گرداند که معرف سطر دو و نمره رضا ،15.5 است .
6. نمره 15.5 ، کو چکترین مقدار در بازه B3 تا B9  است که از نمره 14.5 بزرگتر است.

مثال : می خواهیم افرادی که نام آنها با م شروع و 4 حرفی هستند را جستجو کنیم

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

(A3:A7;0;”م؟؟؟”)MATCH=

در آرگومان اول مقدار مورد جستجو ،افرادی که نام آنها با “م” شروع می شود و نام آنها 4 حرفی است پس در این قسمت “م؟؟؟” را می نویسیم .

حرف اول “م” را می نویسیم به ازای 3 حرف باقی مانده از ؟ استفاده می کنیم .

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

نکته: اگر تعداد کاراکتر ها مشخص نبود از * استفاده می کنیم یک * جایگزین هر تعداد کاراکتر می تواند باشد .

مثال :افرادی که نام آنها با حرف “م”  شروع می شوند در کدام ردیف قرار دارند

در قسمت آرگومان اول ابتدا حرف “م” را می نویسیم و چون تعداد کاراکتر های بعد از حرف “م” مشخص نمی باشد از * استفاده می کنیم.

تابع Match ، مقدار 2 را باز می گرداند  افرادی که نام آنها با حرف “م” شروع می شوند و تعداد کاراکتر بعد از حرف “م” مشخص نیست مینا و مهدی است.

اما تابع اولین مقدار مورد جستجو را برمی گرداند یعنی مینا که در ردیف دوم لیست جستجو قرار دارد .

(A3:A7;0; “*م”)MATCH=

[ps2id id=’2′ target=”/]

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

ترکیب تابع Match  با تابع Vlookup

تابع Vlookup ، مقداری را در بازه مشخص جستجو می کند و در صورت یافتن ،مقدار متناظر در ستون دیگر را نمایش می دهد

برای مثال می خواهیم با ورود نام افراد در سلول C10 ، معدل دانش آموز با استفاده از تابع Vlookup در خانه دیگر محاسبه شود .

در خانه C10 ، نام طاها(مقدار مورد جستجو که در آرگومان اول تابع این مقدار قرار می گیرد ) را وارد می کنیم .

آرگومان دوم تابع (Table_array)، از A2 تا C8  را انتخاب می کنیم .مقداری که قرار است تابع برگرداند معدل طاها است که در ستون سوم قرار دارد .پس در آرگومان سوم عدد 3 را وارد می کنیم .

VLOOKUP(C10;A2:C8;3)=

تابع Vlookup ، مقدار 17.35 را برمی گرداند .

دلیل استفاده از تابع Vlookup و تابع match با هم

1. مشکل تابع Vlookup این است که اگر ستونی از شکل فوق حذف و یا اضافه شود تابع Vlookup خطای ! REF# بر می گرداند .
2. فرض کنید  ستون نام خانوادگی را حذف کنیم ، تابع خطا می دهد و یا ممکنه  است مقدار درست نشان ندهد .
3. اما اگر تابع Vlookup  را با تابع Match ترکیب کنیم با حذف و اضافه ستون تابع Vlookup  مقدار صحیح را نشان می دهد .
4. زیرا در حالت اول برای آرگومان سوم (col-index-num)  تابع Vlookup مقدار 3 را وارد کردیم تا مقادیر ستون نمره برگردانده شود و وقتی ستون نام خانوادگی را حذف می کنیم فقط دو ستون داریم و ستون 3 وجود ندارد تا از آن مقداری برگشت داده شود بنابراین تابع خطا می دهد .
5. اما وقتی از تابع Match ، استفاده کنیم نام ستونی که می خواهیم ،مقادیر متناظر توسط Vlookup از آن برگشت داده شود توسط تابع match ،محاسبه می شود .
6. و اگر ستون اضافه یا کم شود شماره ستون توسط تابع Match بدست می آید و با خطا مواجه نمی شویم
   

روش استفاده ار تابع Match و تابع Vlookup

ابتدا در سلول دلخواه مثل E6 ، تابع match را برای تعیین شماره ستون معدل می نویسیم تا با کم و اضافه شدن ستون بتوانیم شماره ستون معدل را بدست آوریم .

آرگومان اول تابع Match شامل معدل است که در خانه  D1 قرار دارد و آرگومان سوم تابع را صفر قرار می دهیم تا مقدار دقیق نشان داده شود .

مقدار آرگومان دوم (lookup-array):

شامل  ستون های نام ، نام خانوادگی و معدل  از خانه A1 تا C1 است.

MATCH(D1;A1:C1;0)=

در  شکل فوق ،تابع Match  ، مقدار 3 را برمی گرداند یعنی معدل در ستون سوم جدول فوق قرار دارد و اگر ستون نام خانوادگی را حذف کنیم تابع مقدار 2 را برمی گرداند.

در سلول D2 کلیک می کنیم و تابع Vlookup را بصورت زیر می نویسیم.

VLOOKUP(E2;A2:C8;3)=

حال بجای آرگومان سوم (Col-index-num) که مقدار ثابت 3 را وارد کردیم تابع Match بالا را می نویسیم تا با کم و زیاد شدن ستون ها تابع Match مقدار آرگومان سوم را محاسبه کند.

VLOOKUP(E2;A2:C8;MATCH(D1;A1:C1;0))=

حال برای هر نامی که در خانه E2 وارد می کنیم معدل متناظر با نام نمایش داده می شود و با حذف ستون نام هم باز مقدار درست نشان داده می شود .

[ps2id id=’3′ target=”/]

ترکیب تابع Match  با تابع Hlookup

تابع Hlookup ، مقداری را در ردیف های اکسل جستجو می کند و در صورت یافتن ،مقدار متناظر در ردیف دیگر را نمایش می دهد.

برای مثال با ورود کد دانش آموزی در سلول ، نام خانوادگی دانش آموز با استفاده از تابع Hlookup در خانه دیگر نشان داده می شود  .

در خانه C6  ، کد دانش آموزی 1003 را وارد می کنیم .

HLOOKUP(C6;A1:F3;3;TRUE)=

تابع Hlookup ، مقدار قاسمی را برمی گرداند .

دلیل استفاده از تابع Hlookup و تابع match با هم

مشکل تابع Hlookup :

 اگر  ردیفی از شکل فوق بر حسب نیاز حذف و یا اضافه شود تابع Hlookup خطای ! REF# و یا مقدار ناصحیح بر می گرداند .

فرض کنید  ردیف نام را حذف کنیم ، تابع در محدوده A1 تا F2 خطا ! REF#می دهد  .چون در این محدوده ردیف سوم وجود ندارد. 

اما ردیف سوم در محدوده A1 تا F3 وجو دارد اما مقدار متناظر معدل را برمی گرداند نه نام خانوادگی را بنابراین تابع مقدار نادرست نمایش داده است .

1. اما اگر تابع Hlookup  را با تابع Match ترکیب کنیم با حذف و اضافه ردیف تابع Hlookup  مقدار صحیح برمی گرداند .
2. زیرا در حالت اول برای آرگومان سوم (col-index-num)  تابع Hlookup مقدار 3 را وارد کردیم تا مقادیر ردیف نام خانوادگی متناظر با مقدار مورد جستجو برگردانده شود
3.   وقتی ردیف نام را حذف می کنیم ردیف 3 در بازه مورد جستجو معدل می شود  و در نتیجه تابع مقدار نام خانوادگی را بر نمی گرداند و مقدار صحیح نشان داده نمی شود .
4. اما وقتی از تابع Match ، استفاده کنیم شماره ردیف که می خواهیم ،مقادیر متناظر توسط Hlookup از آن برگشت داده شود توسط تابع match ،محاسبه می شود .
5. و اگر ردیف اضافه یا کم شود شماره ردیف توسط تابع Match بدست می آید و با خطا و یا مقدار نادرست مواجه نمی شویم.
   

روش استفاده ار تابع Match و تابع Hlookup

ابتدا در سلول دلخواه مثل C9، تابع match را برای تعیین شماره ردیف نام خانوادگی می نویسیم تا با کم و اضافه شدن ردیف ها بتوانیم شماره ردیف نام خانوادگی را بدست آوریم .

آرگومان اول و سوم تابع Match را طبق فرمول زیر به ترتیب مقدار آدرس خانه ی D6 (نام خانوادگی)و صفر وارد شده است.

مقدار آرگومان دوم (lookup-array):

شامل  ردیف های  کد دانش آموزی ، نام ، نام خانوادگی و معدل  از خانه A1 تا A4 است.

MATCH(D6;A1:A4;0)=

در  شکل فوق ،تابع Match  ، مقدار 3 را برمی گرداند یعنی نام خانوادگی در ردیف سوم جدول فوق قرار دارد و اگر ردیف نام را حذف کنیم تابع مقدار 2 را برمی گرداند.

در سلول C7 کلیک می کنیم و تابع Hlookup را بصورت زیر می نویسیم.

HLOOKUP(C6;A1:F3;3;TRUE)=

حال بجای آرگومان سوم (Col-index-num) که مقدار ثابت 3 را وارد کردیم تابع Match بالا را می نویسیم تا با کم و زیاد شدن ستون ها تابع Match مقدار آرگومان سوم را محاسبه کند.

HLOOKUP(C6;A1:F3;MATCH(D1;A1:C1;0);true)=

[ps2id id=’4′ target=”/]

حالا برای هر کد دانش آموزی که در خانه C6 وارد می کنیم نام خانوادگی متناظر با کد دانش آموزی نمایش داده می شود و با حذف ردیف نام هم باز مقدار درست نشان داده می شود .

ترکیب تابع match با تابع Index

همانطور که در مطالب بالا توضیح داده شد تابع Index ، مقدار موجود در سطر و ستون خاصی را بر می گرداند .

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

برای مثال: می خواهیم با ورود نام و نام نمره هر فرد ، عدد نمره در خانه C11 نمایش یابد 

ابتدا نام علی و نمره شیمی را به ترتیب در خانه های B9 و B10 می نویسیم . در خانه D9 کلیک می کنیم و با کمک تابع Match ، شماره ردیف مربوط به علی را بدست می آوریم.

محدوده داده (lookup_array) ، شامل  ستون نام افراد از خانه A3 تا A7 است .

و آرگومان سوم را هم مقدار صفر وارد می کنیم تا مقدار دقیق برگردانده شود .

MATCH(B9;A3:A7;0)=

سپس در خانه D10 کلیک می کنیم و شماره ستون مربوط به نمره شیمی را با کمک تابع Match  بدست می آوریم .

محدوده داده (lookup_array) ، شامل نام ، نمره فیزیک ، نمره شیمی و نمره ریاضی از خانه  A2 تا  D2 است .

MATCH(B10;A2:D2;0)=

حال در خانه C11 کلیک می کنیم و از تابع Index  استفاده می کنیم .

محدوده داده (array) ، کل اسامی افراد و نمرات می باشد و از خانه A3 تا D7 انتخاب می کنیم .

و برای تعیین شماره ستون و شماره ردیف تابع Index ، از خروجی های تابع Match که در خانه های D2 و D9  است استفاده می کنیم .

  فرمول به شکل زیر می شود

INDEX(A3:D7;D9;D10)=

حال بجای خانه های D9 و D10 خود فرمول تابع Match ، که در قسمت بالا وارد کرده بودیم را می نویسیم .

INDEX(A3:D7;MATCH(B9;A3:A7;0);MATCH(B10;A2:D2;0))=

حالا می توانیم محتوای خانه های D9 و D10 را حذف کنیم.

تابع مقدار 15.5 را نمایش می دهد .

اگر ازقسمت Data validation لیستی از  نام افراد در خانه B9 و لیستی از نمرات در خانه  B10 ایجاد کنیم می توان با انتخاب نام و نوع نمره از لیست باز شو  ، مقدار مربوط به نمره هر فرد را در خانه C11 به راحتی مشاهده کنیم.