تابع MATCH اکسل – موقعیت در محدوده

تابع MATCH اکسل – موقعیت در محدوده

برای اینکه بتوانید شماره ستون و یا شماره ردیف را در یک جدول دلخواه بدست آورید از تابع MATCH اکسل استفاده می کنید.

جدول زیر را در نظر بگیرید:

از شما سوالات زیر را می پرسند:

این جدول چند ستون دارد؟ تابع columns اکسل

این جدول چند ردیف دارد؟ تابع rows اکسل

سلول C5 (منطقه) در ردیف چندم این شیت قرار دارد؟ تابع Row اکسل

سلول C5 (منطقه) در ستون چندم این شیت قرار دارد؟ تابع column اکسل

اما اکنون از شما دو سوال زیر را می پرسند :

ستون مدیر فروش، چندمین ستون در این جدول (نه شیت) است؟

منطقه شرق، چندمین ردیف در این جدول(نه شیت) است؟

.قبل از اینکه به این دو سوال پاسخ دهیم، ساختار تابع MATCH را بیان می کنیم.

ساختار تابع MATCH:

=MATCH(lookup_value, lookup_array, [match_type])

=MATCH(نوع جستجو,منطقه جستجو, مقدار جستجو)

 lookup_value:

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

lookup_array:

محدوده ای که می خواهد مورد جستجو قرار بگیرد.

[match_type]:

در این قسمت نوع جستجو را مشخص می کنیم.اما در اکثر مواقع آن را برابر با 0 در نظر می گیریم.0 یعنی دقیقا عبارت ما را جستجو کن.

مقدار هایی که این آرگومان می گیرد:

1 یا خالی رها کردن: هنگام جستجو در محدوده، بزرگترین مقداری را که کمتر یا برابر با lookup_value است را پیدا می کند.

-1(منهای یک):  هنگام جستجو در محدوده، کوچکترین مقداری را که بزرگتر یا برابر با lookup_value است را پیدا می کند.

از این دو حالت زمانی استفاده می کنیم که احتمال می دهیم مقدار جستجو در محدوده ما وجود ندارد. حالا به کمک 1 و -1 مشخص می کنیم اگر وجود ندارد، شماره ردیف (ستون) عدد بزرگتر را ( در محدوده) پیدا کند یا شماره ردیف (ستون) عدد کوچکتر را (در محدوده) پیدا کنید.

چون این تابع مقادیر منطقی را هم دریافت می کند، بهتر است بدانید معنی کوچکتر به بزرگتر در این تابع به چه معناست:

 …-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE.

0(صفر):دقیقا خود عبارت را جستجو می کند.

نکات تابع MATCH:

تابع match مقدار عبارت را در یک محدوده بر نمی گرداند.بلکه موقعیت آن را در محدوده مورد نظر به شما نشان می دهد.به عنوان مثال خروجی تابع زیر برابر با 2 خواهد بود نه عبارت b. زیرا موقعیت b در محدوده ما برابر با 2 می باشد.

=MATCH(“b”,{“a”,”b”,”c”},0)

تابع match موقعیت یک عبارت (مقدار یا مقدار منطقی) را در یک محدوده به صورت ستونی یا ردیفی، در همان محدوده به شما نشان می دهد.

تابع match برای عبارت حروف بزرگ و کوچک در عبارت منطقی، تفاوتی قائل نمی شود.(فرقی نمی کند با حروف بزرگ بنویسید یا با حروف کوچک)

تابع match  اگر نتواند عبارت مورد نظر شما را در یک محدوده پیدا کند، خطای #N/A را نمایش خواهد داد.

اگر نوع جستجو (match_type) برابر با صفر باشد و عبارت جستجوی شما شامل متن باشد، می توانید از کارکتر های ؟ و *  در آرگومان lookup_value استفاده کنید.

تابع match در ترکیب با سایر توابع به خصوص تابع Index کارایی خود را بیشتر نشان خواهد داد.

برای ایجاد داشبردهای مدیریتی کاربرد فراوانی دارد.

برای ایجاد برنامه های پویا (داینامیک) کاربرد فراوانی دارد.

جواب سوالات:

مجددا به جدول نگاه کنید.جدول ما در محدوده C5:H9 قرار دارد.

ستون مدیر فروش، چندمین ستون در این جدول (نه شیت) است؟

=MATCH(“مدیر فروش”,C5:H5,0)

خروجی این تابع عدد5 خواهد بود.یعنی ستون فروش در این جدول، 5امین ستون می باشد.

منطقه شرق، چندمین ردیف در این جدول(نه شیت) است؟

=MATCH(“شرق”,C5:C9,0)

خروجی این تابع عدد 4 می باشد.یعنی ردیف شرق در این جدول،4امین ردیف می باشد.

کاربرد تابع MATCH اکسل با مثال:

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

جدول زیر را در نظر بگیرید.از شما می خواهند تعداد قطعات معیوب را بین ساعت های مختلف به دست آورید. مثلا بین ساعت های 1 تا 6 و یا ساعت های 6 تا 12  یا ساعت 4 تا8 را به دست آورید.یعنی محدوده شما در این سوال مدام در حال تغییر می باشد. بعد از آموزش فایل آن را دانلود و تمرین بیشتری را انجام دهید.

 قبل از اینکه پاسخ این سوال رو ارائه دهیم بهتر است با کاربرد تابع INDIRECT آشنا شوید.

برای شروع شما باید ردیف محدوده ها را به دست آورید.یعنی اگر در سلول B1 نوشتیم 6:00 ، باید ردیف آن را در محدوده ما (جدول) پیدا کند. برای سلول دوم یعنی B2 هم دقیقا همین کار را انجام میی دهیم.پس دو فرمول زیر را خواهیم داشت:

برای ساعت اول:

=MATCH(B1,$E$1:$E$14,0)

برای ساعت دوم:

=MATCH(B2,$E$1:$E$14,0)

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

=SUM(INDIRECT(“F”&B1&”:F”&B2))

حالا در این فرمول توابع match ای را که تعریف کرده این را جایگزین می کنیم.یعنی فرمول زیر:

=SUM(INDIRECT(“F”&MATCH(B1,$E$1:$E$14,0)&”:F”&MATCH(B2,$E$1:$E$14,0)))

حالا فقط کافیست، ساعت های خود را در سلول های B1 و B2 وارد کنید.