یکی از ویژگیهای جذاب گوشیهای هوشمند این است که بهوسیله آنها دقیقا میتوانیم موقعیت و مکان موردنظر را پیدا کنیم. تکنولوژی GPS در گوشیهای هوشمند، این کار را برای ما انجام میدهد. تابع MATCH در اکسل هم برای ما مثل GPS عمل میکند! در واقع این تابع مقدار مشخصی را در طیف وسیعی از سلولها جستجو میکند و موقعیت آن مقدار را برمیگرداند. این تابع در دسته توابع جستجو و مرجع (Lookup & Reference) قرار دارد. چندین تابع در اکسل شما را برای یافتن مقداری مشخص در طیف وسیعی از سلولها کمک میکنند که تابع MATCH یکی از آنهاست.
نکته: تابع MATCH اغلب با تابع INDEX استفاده میشود. به این صورت که تابع MATCH موقعیت یک مقدار را برای تابع INDEX پیدا میکند و سپس تابع INDEX مقدار موردنظر را برمیگرداند.
ساختار تابع MATCH در اکسل
ساختار تابع MATCH در اکسل به شکل زیر است:
MATCH(lookup_value, lookup_array, [match_type])
- Lookup_value: در این قسمت مقداری را که میخواهید موقعیت آن را پیدا کنید، قرار دهید. این مقدار میتواند یک عدد، متن و یا آدرس سلول باشد.
- Lookup_array: محدوده سلولهایی که میخواهید موقعیت مقدار موردنظر را در بین آنها پیدا کنید، در این قسمت قرار دهید.
- Match_type: در این قسمت نوع مطابقت را تعیین کنید. میتوانید یکی از مقادیر ۰، ۱، -۱ را انتخاب کنید. اگر آرگومان Match_type را روی ۰ تنظیم کنید مطابقت دقیق را انجام میدهد، در حالی که دو عدد دیگر مطابقت تقریبی را انجام میدهند. در حالت پیش فرض مقدار این آرگومان ۱ است. در ادامه این موضوع را به طور مفصل توضیح میدهیم.
نکته: دو آرگومان اول ضروری هستند و آرگومان سوم اختیاری است.
چند نکته درباره آرگومان Match_type
در جدول زیر ۳ حالتی که ممکن است برای آرگومان Match_type رخ دهد و نحوه مرتبسازی دادهها و همچنین نتیجه تابع MATCH را میبینید.
این حالتها در تصویر زیر هم گویای مطلب است:
مثال زیر هم عملکرد تابع MATCHرا وقتی آرگومان Match_type مقادیر ۱ و -۱ را دریافت میکند به خوبی نشان میدهد. فرض کنید در لیستتان، عددی که به دنبال آن هستید، نباشد:
- اگر در قسمت Match_type عدد ۱ را تایپ کنید، تابع MATCH موقعیت نزدیکترین عدد کوچکتر از عدد تایپ شده در قسمت Lookup_value را نشان میدهد.
- اگر در قسمت Match_type عدد -۱ را تایپ کنید، تابع MATCH موقعیت نزدیکترین عدد بزرگتر از عدد تایپ شده در قسمت Lookup_value را نشان میدهد.
من سعی کردم با استفاده از رسم نمودارهای میلهای برای دادههای خود، این موضوع را بهتر نشان دهم.
مثال ۱: عملکرد تابع MATCH در اکسل
برای درک بهتر عملکرد تابع MATCH، بیایید یک فرمول ساده بر اساس دادهها تهیه کنیم. من در یک فایل اکسل، نام و نام خانوادگی دانش آموزان را در ستون A و نمرات امتحانات آنها را در ستون B تایپ کردهام.
سپس نمرات را از بزرگترین تا کوچکترین مرتب کردهام. این کار را با استفاده از ابزار Sort & Filter انجام دادهام. برای این کار ابتدا ستون B را انتخاب کرده و سپس به سربرگ Home میروم با کلیک روی Sort & Filter گزینه Sort Z to A را انتخاب میکنم.
برای اینکه بفهمم یک دانش آموز مثلا “سهیل فرامرزی” در لیستم در چه موقعیتی قرار دارد. یک سلول را به عنوان سلول نتیجه (D2) در نظر میگیرم. سپس در نوار فرمول نرمافزار روی Fx کلیک میکنم (یا اینکه کلیدهای Shift+F3 را فشار میدهم.)
پنجره Inert Function باز میشود. از دسته توابع جستجو و مرجع (Lookup & Reference) تابع MATCH را انتخاب میکنم و روی OK کلیک میکنم.
پنجره Function Argumentes باز میشود. در کادر Lookup_value کلیک کرده و “سهیل فرامرزی” را تایپ میکنم. سپس در کادر Lookup_array کلیک کرده و محدوده موردنظر (A2:A8) را انتخاب میکنم. حالا در کادر Match_type کلیک کرده و عدد (۰) را برای مطابقت کامل انتخاب میکنم. در نهایت روی OK کلیک میکنم.
مثال ۲: استفاده از کاراکترهای Wildcard در تابع MATCH
مشابه بسیاری از توابع میتوانید در فرمول تابع MATCH هم از کاراکترهای Wildcard استفاده کنید. Wildcard کاراکتری است که در جستجوها، جانشین یک یا چند کاراکتر دیگر میشود. شما میتوانید از دو کاراکتر زیر در این تابع SUMIF استفاده کنید.
- علامت سؤال (؟): جایگزین یک کاراکتر میشود.
- علامت ستاره (*): جایگزین دنبالهای از کاراکترها میشود.
توجه داشته باشید کاراکترهای Wildcard فقط در فرمولهای MATCH با Match_type برابر با ۰ قابل استفاده هستند.
فرض کنید شما لیستی از نام و نام خانوادگی مسئولین نمایندگیهای فروش و آمار فروش آنها را در یک ماه گذشته دارید. میخواهید موقعیت یک نماینده فروش خاص، که فقط نام او را به خاطر دارید در لیست پیدا کنید. (لیست شما طبقه بندی شده بر اساس مبلغ فروش به ترتیب نزولی است.) نام و نام خانوادگی مسئولین نمایندگیهای فروش، در محدوده A2:A9 قرار دارند و شما میخواهید موقعیت فروشندهای با نام “سعید” را بدانید.
برای این کار در سلول نتیجه (D2) کلیک کنید سپس در پنجره Function Arguments در کادر Lookup_value کلیک کرده و “سعید* را تایپ کنید. سپس در کادر Lookup_array کلیک کرده و محدوده سلولهای موردنظر (A2:A9) را تایپ کنید. در نهایت در کادر Match_type کلیک کرده و عدد ۰ را تایپ کنید و روی OK کلیک کنید.
چند نکته درباره تابع MATCH
- تابع MATCH نسبت به کوچکی و بزرگی حروف حساس نیست.
- اگر در محدوده جستجو بیش از یک مقدار یافت شد، موقعیت اولین مقدار برگردانده میشود.
- اگر آرگومان Match_type (0) باشد و مقدار موردنظر در محدوده یافت نشود، تابع MATCH خطای #N/A را برمیگرداند.
- اگر آرگومان Match_type (-1) باشد و محدوده موردنظر شما به صورت صعودی مرتب شده باشد، تابع MATCH خطای #N/A را برمیگرداند.
- اگر آرگومان Match_type (1) باشد و محدوده موردنظر شما به صورت نزولی مرتب شده باشد، تابع MATCH خطای #N/A را برمیگرداند.
پیشنهاد مطالعه: آموزش تابع OFFSET در اکسل
جمعبندی
در این آموزش تابع MATCH و برخی نکات درباره آن را توضیح دادیم. فرمول ترکیبی دو تابع MATCH و INDEX هم ترکیب جالبی است! در آموزشهای بعدی ترکیب این دو تابع را توضیح خواهم داد.
مثل همیشه عالی بود سپاس
ممنونم. نظر لطف شماست. موفق و پیروز باشید.
سلام
چطوری موقعیت آخرین سلول تکراری در یک ستون رو پیدا کنیم؟
مثلاٌ ستونی حاوی چند سلول با عدد صفر هست. ما آخری رو چطور اتومات پیدا کنیم که در فرمول نویسی از اون استفاده کنیم؟
ممنونم
سلام. یا باید از ترکیب ۳ تایع max و index و sum product استفاده کنین و یا از ماکرو نویسی
مثلا فرض کنین توی سلول های a1 تا a11 نام مدیران جلسات رو نوشتین و در سلولهای b1 تا b11 تاریخ جلسات رو. توی لیست، اسامی تکراری دارین حالا میخواین ببینین آخرین تاریخی که هر مدیر جلسه داشته چه زمانی بوده. توی سلول f1 هم لیست کشویی اسامی مدیران جلسه هست.
((INDEX($B$1:$B$11,SUMPRODUCT(MAX(ROW($A$1:$A$11)*($F$1=$A$1:$A$11))-0
حالا این فرمول رو تعمیم بدین به مثال خودتون و تغییرات لازم رو بدین.
واقعا عالیه
چه خوبه آدم عاشق کارش باشه
برات بهترینها رو آرزو دارم
ممنونم. بله برای انجام یک کار به درستی باید عاشق آن بود. موفق باشید. سپاس از همراهی تون.
سلام
من وقتی که میخوام از تابع MATCH استفاده کنم به مشکل میخورم (عجیبه برام چون توی چند سلول کار میکنه توی بقیه مشکل داره )
تنظیمات خاصی انجام نداده ام
اکسل ۲۰۰۷
من میخوام نام یک شهر را از داخل یک لیست پیدا کنم و سپس کد دسته بندی آن شهر را پیدا کنم ( مثل کد شهر هست با این تفاوت که ممکنه چند شهر کد یکسان داشته باشند) راه حلی برای من دارید؟ استفاده از تابع دیگری ممکن هست ؟ چون با تابع MATCH نمی توانم کار کنم
سلام من یه ردیف ۲۰۰۰۰ تایی دارم میخوام از ۱ تا ۲۰۰۰ به من ۱ بده
از ۲۰۰۱ تا ۴۰۰۰ به من ۲ بده و الی آخر
چطور میتونم انجام بدم
سلام. متوجه منظورتون نشدم.
سلام و وقت بخیر
میخواستم بپرسم
آیا در اکسل فرمولی هست که بشود اعدادی که دهگان یا صدگان … مشخص (مثلا ۲ )دارند را مشخص کرد؟
ممنون میشم اگر راهنمایی بفرمایید
با سلام
در یک شیت اکسل می خواهم روی هر سلول که کلیک می کنم , شماره ردیف و شماره ستون سلول کلیک شده نمایش داده شود . راهنمایی فرمایید .
استاد نیاز به تابعی دارم در اکسل که بتواند در یک ستون از اعداد که شامل اعداد مثبت و منفی هستند شمارش بیشترین اعداد منفی پشت سر هم را انجام بدهد.
سلام من میخواهم در اکسل کد ملی شخصی را بر اساس نام آن فرد که در سلول مجاور قراردارد از یک شیت دیگر فراخوانی کنم ممنون میشم راهنمایی بفرمائید
سلام. با استفاده از تابع vlookup میتونید این کار رو انجام بدین. برای آشنایی با نحوه عملکرد این تابع پیشنهاد میکنم مقالهآموزش تابع VLOOKUP در اکسل – جستجوی یک مقدار در ستون رو مطالعه بفرمایید.
درود ، دو ستون دارم(A & B) با داده های مختلف که داده های ستون B همگی در ستون A وجود دارن میخوام سطر اون داده رو تو ستون B که متناظر اون در ستون A هست رو بهم بده .با استفاده از فرمول countif & row امتحان کردم ولی موفق نشدم ،ممنون میشم راهنمایی کنید.