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

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

آموزش تابع match اکسل

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

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

این جدول چند ردیف دارد؟ جواب

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

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

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

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

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

برای اینکه بتوانید شماره ستون و یا شماره ردیف را در یک جدول دلخواه بدست آورید از تابع MATCH استفاده می کنید.قبل از اینکه به این دو سوال پاسخ دهیم، ساختار تابع 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امین ردیف می باشد.

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

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

تابع match برای ایجاد برنامه های پویا

[cf-restrict]

 قبل از اینکه پاسخ این سوال رو ارائه دهیم بهتر است با کاربرد تابع 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 وارد کنید.

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

دانلود فایل نمونه

[/cf-restrict]

 

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *