خوش آموز درخت تو گر بار دانش بگیرد، به زیر آوری چرخ نیلوفری را
تابع match در اکسل + مثال های کاربردی و ترکیبی
در آموزش امروز تابع match در اکسل را بررسی می کنیم. به کمک تابع match می توانید مقداری را در یک محدوده از سلول ها جستجو کنید. این محدودۀ سلولی می تواند یک ردیف، یک ستون و یا یک جدول باشد. تابع match دارای سه پارامتر می باشد. پارامتر اول این تابع که lookup_value نام دارد، مقداری است که قرار است جستجو شود. پارامتر دوم این تابع که lookup_array نام دارد، آدرس بازۀ سلولی مورد جستجو می باشد، برای همین هم در نام این پارامتر واژۀ آرایه قرار دارد که اشاره به آرایه ای از سلول ها دارد. پارامتر سوم این تابع که match_type نام دارد، یک پارامتر اختیاری یا آپشنال (optional) می باشد و شما الزامی ندارید که برای آن آرگومانی را ارسال کنید. با این حال در صورت تمایل می توانید یکی از سه عدد \(0\) یا \(1\) و یا \(-1\) را به آن پاس کنید. در طول این آموزش به معنای هر کدام از این پارامترها و کاربرد آنها اشاره خواهیم کرد. هدف این تابع اینست که موقعیت یک آیتم را در یک آرایه بیابد و به شما باز گرداند. از این رو مقدار بازگشتی این تابع یک عدد می باشد که نشان دهندۀ موقعیت مقدار مورد جستجو در آرایۀ سلولی مربوطه می باشد. فراموش نکنید که بهترین روش درک صحیح توابع اکسل اینست که آنها را به صورت عملی مورد استفاده قرار دهید و بر روی داده های واقعی بیازمایید. از این رو ما هم در این آموزش با مثال های متعدد، این پارامتر را به گونه های مختلف مورد بررسی قرار خواهیم داد. اگر مشتاق هستید در مورد اکسل بیشتر بدانید، آموزش اکسل Microsoft Office Excel 2016 می تواند در این مسیر به شما کمک کند.
تابع match در اکسل برای تعیین موقعیت یک مقدار در یک آرایه یا بازه سلولی مورد استفاده قرار می گیرد. به عنوان مثال، در تصویر زیر فرمول نوشته شده در سلول \(\text{E6}\) طوری نوشته شده است که به کمک تابع match، موقعیت مقدار تایپ شده در سلول \(\text{D6}\) را بیابد. در اینجا خروجی تابع match مقدار \(5\) می باشد، چرا که مقدار مورد جستجو، یعنی "peach" در بازۀ سلولی \(\text{B6:B14}\) در موقعیت \(5\)ام قرار دارد.
به کمک تابع match می توانید جستجوی دقیق (exact) و یا جستجوی تقریبی (approximate) را انجام دهید که هر کدامشان در جای خودشان مفید و کاربردی می باشند. همچنین اگر بخواهید جستجو را به صورت بخشی (partial) از متن مورد جستجو صورت دهید، به کمک وایلدکاردها (Wildcard) در اکسل می توانید این عملیات را صورت دهید. اگر کلمۀ وایلدکارد را قبلاً نشنیده اید و برای اولین بار است که با آن برخورد می کنید، نگران نشوید، در اینجا به صورت عملی با آن کار خواهید کرد. همانطور که در مقدمۀ این مقاله اشاره کردیم، تابع match می تواند به سه روش مختلف جستجو را صورت دهد که این روش ها در پارامتر اختیاری سوم تابع، یعنی match_type مشخص می شوند.
تابع match در اکسل در حالت عادی فقط می تواند در آرایه ها یا بازه های سلولی یک بعدی جستجو کند، خواه این بازه افقی، خواه عمودی باشد. البته اگر بخواهید به کمک تابع match در آرایه های دو بعدی جستجو کنید، می توانید آن را با تابع index در اکسل به صورت ترکیبی مورد استفاده قرار دهید. همچنین با این ترکیب، این امکان را نیز خواهید داشت که در یک فرمول، دوبار از تابع match استفاده کنید. معمولاً حرفه ای های اکسل، تابع match را با تابع index ترکیب می کنند تا مقداری را در یک شرایط مطابقت خاص، بازیابی کنند. در این ترکیب، تابع match موقعیت را پیدا می کند و تابع index مقدار موجود در آن موقعیت را بازیابی می کند. ترکیب تابع match با تابع index قدرت مانور فراوان به ما می دهد، گویی به جای اینکه رستم و اسفندیار در دو جبهۀ مخالف یکدیگر بجنگند، هر دو را در یک لشکر سازمان دهیم.
همانطور که در لابلای آموزش ها متوجه شدید، هر چقدر بیشتر بر روی توابع مختلف در اکسل مسلط باشید، می توانید با ترکیب آنها، امکانات بیشتری را به خدمت بگیرید. در همین راستا آموزش استفاده از توابع و فرمول نویسی در اکسل Excel را که در فرادرس منتشر شده است، به شما پیشنهاد می کنم.
پیشتر اشاره کردیم که پارامتر سوم تابع match در اکسل اختیاری می باشد. اگر این مقدار را مشخص نکنید به صورت پیش فرض اکسل آن را \(1\) در نظر می گیرد. هنگامی که مقدار پارامتر match_type بر روی \(1\) یا \(-1\) تنظیم شود، گاهی اوقات به آن مطابقت تقریبی (approximate match) می گویند. با این حال یادتان باشد که این اسمش است و در عمل تابع match همیشه مقدار دقیق را مورد جستجو قرار می دهد، مگر اینکه از وایلدکاردها استفاده کنید. در ادامه مقادیر پارامتر match_type و شرح هر کدام را می بینید:
هنگامی که آرگومان ارسالی به پارامتر match_type برابر با \(0\) باشد، تابع match یک عملیات مطابقت دقیق را صورت می دهد. در مثال زیر، فرمولی که در سلول \(\text{E3}\) نوشته شده است را می بینید:
در مثال بالا، مقدار مورد جستجو از سلول \(\text{E2}\) خوانده می شود. اگر بخواهید مقدار مورد جستجو را از سلول نخوانید، در واقع به جای متغیر بودن مقدار مورد جستجو، آن را به شکل هاردکد (hardcode) در فرمول بنویسید. باید آن را در یک جفت گیومه قرار دهید. در این حالت، فرمول شما این گونه خواهد بود:
همانطور که احتمالاً می دانید، در حال حاضر آخرین نسخۀ ارائه شده از محصول آفیس که اکسل هم جزئی از آن می باشد، آفیس 2019 می باشد. اگر مایلید که به آخرین نسخۀ این نرم افزار سوئیچ کنید و از امکانات موجود در آن بهره ببرید، آموزش اکسل Microsoft Excel 2019 را به شما پیشنهاد می کنم.
هنگامی که مقدار پارامتر match_type در تابع match در اکسل برابر با \(1\) باشد، مطابقت به صورت تقریبی بر روی مقادیری که به صورت \(\text{A-Z}\) مرتب شده اند، صورت می پذیرد. در این حالت بزرگترین مقداری که کوچکتر یا برابر با مقدار مورد جستجو باشد، خروجی تابع خواهد بود. در مثال زیر، فرمول موجود در سلول \(\text{E3}\) به شرح زیر می باشد:
همانطور که در این مثال می بینید، ما مقدار \(575\) را جستجو کرده ایم. اما این مقدار در آرایۀ مورد جستجوی ما وجود نداشته است. در عوض مقادیر \(500\) و \(600\) در آرایه وجود داشته اند که البته به صورت صعودی نیز مرتب سازی شده اند. در این وضعیت تابع match موقعیت سلولی که مقدار \(500\) در آن قرار داشته است را به ما باز می گرداند که در اینجا برابر با \(5\) می باشد.
قبل از هر چیز اجازه دهید، ببینیم وایلدکارد (wildcard) چیست. وایلدکارد چیزی جز یک کاراکتر خاص نیست که به ما امکان انجام جستجوهای خاصی را می دهد. در اکسل سه وایلدکارد مهم وجود دارد که می توانید در جستجوهایتان از آنها استفاده کنید. این سه وایلدکارد عبارتند از:
توجه داشته باشد که وایلدکاردها فقط با متن کار می کنند و احیاناً از آنها بر روی مقادیر عددی استفاده نکنید.
خوب، حالا که با وایلدکارد و کارکرد آن آشنا شدید، می توانید از آن در تابع match در اکسل بهره ببرید. وقتی که مقدار پارامتر match_type برابر با \(0\) باشد، می توانید از وایلدکاردها استفاده کنید. در مثال زیر، فرمول موجود در سلول \(\text{E3}\) را می بینید:
اگر بخواهیم همین فرمول را به صورت هارد کد بنویسیم، خواهیم داشت:
اکسل دانستنی های زیادی دارد که هر کدامشان می توانند در جایی برای شما سودمند باشند. اگر مایلید که از اکسل بهینه تر و بهتر استفاده کنید، آموزش ابزارهای کاربردی اکسل را به شما پیشنهاد می کنیم.
در اینجا مقدار جستجو در تابع match برابر با \(\text{pq*}\) می باشد، یعنی ما از از این تابع خواسته ایم که مقداری را برای ما پیدا کند که دو حرف آخر آن \(\text{pq}\) باشند و در ادامۀ آنها هر کاراکتری به هر تعداد می تواند باشد. خروجی یافت شده در اینجا \(\text{pqr-121}\) است که موقعیت آن در آرایۀ مورد جستجو، یعنی \(6\) به عنوان خروجی تابع بازگردانده شده است. اگر مقدار ما هر کدام از مقادیر \(\text{pqf-dlk}\) یا \(\text{pq150}\) یا \(\text{pq_dance}\) می بود، جستجو همین خروجی را می داشت.
همانطور که در لابلای این مقاله اشاره کردیم، ترکیب دو تابع match و index مثل قراردادن مارادونا و پله در کنار یکدیگر می ماند و قدرت فراوانی به شما می دهد. در واقع معمولاً هیچگاه تابع match را به صورت جداگانه استفاده نمی کنند (مگر در کد نویسی که بحثش جدا است). در مثال زیر، ترکیب این دو تابع را می بینید. فرمولی که در سلول \(\text{F5}\) نوشته شده است، به شرح زیر است:
در این مثال ابتدا تابع match موقعیت متن جستجو شده را پیدا می کند که \(7\) می باشد. سپس تابع index این موقعیت را به عنوان ورودی اش دریافت می کند و مقدار آن را که \(\text{\$150.00}\) می باشد، باز می گرداند. این تکنیک که از توابع به صورت تو در تو استفاده کنید و مقدار خروجی یکی را به عنوان مقدار ورودی تابع دیگر پاس کنید، تکنیکی بسیار قدرتمند است که نه تنها برای ترکیب این دو تابع می توانید بکار ببرید، بلکه فراتر از آن در کلیۀ توابع اکسل می توانید چنین تکنیکی را بکار گیرید. مشروط بر اینکه نوع دادۀ خروجی و ورودی با یکدیگر مطابقت داشته باشند.
در پایان این مقاله مایلم منابع آموزشی زیر را با شما به اشتراک بگذارم:
نکات مهم در ارتباط با تابع match در اکسل
تابع match در اکسل برای تعیین موقعیت یک مقدار در یک آرایه یا بازه سلولی مورد استفاده قرار می گیرد. به عنوان مثال، در تصویر زیر فرمول نوشته شده در سلول \(\text{E6}\) طوری نوشته شده است که به کمک تابع match، موقعیت مقدار تایپ شده در سلول \(\text{D6}\) را بیابد. در اینجا خروجی تابع match مقدار \(5\) می باشد، چرا که مقدار مورد جستجو، یعنی "peach" در بازۀ سلولی \(\text{B6:B14}\) در موقعیت \(5\)ام قرار دارد.
=MATCH(D6,B6:B14,0)
به کمک تابع match می توانید جستجوی دقیق (exact) و یا جستجوی تقریبی (approximate) را انجام دهید که هر کدامشان در جای خودشان مفید و کاربردی می باشند. همچنین اگر بخواهید جستجو را به صورت بخشی (partial) از متن مورد جستجو صورت دهید، به کمک وایلدکاردها (Wildcard) در اکسل می توانید این عملیات را صورت دهید. اگر کلمۀ وایلدکارد را قبلاً نشنیده اید و برای اولین بار است که با آن برخورد می کنید، نگران نشوید، در اینجا به صورت عملی با آن کار خواهید کرد. همانطور که در مقدمۀ این مقاله اشاره کردیم، تابع match می تواند به سه روش مختلف جستجو را صورت دهد که این روش ها در پارامتر اختیاری سوم تابع، یعنی match_type مشخص می شوند.
نکته مهم: توجه داشته باشید که تابع match در اکسل همواره اولین موردی را که با شرایط جستجو مطابقت کند، باز می گرداند. اگر نیاز دارید که آخرین مورد را به عنوان خروجی باز گردانید، یعنی جستجوی شما معکوس و از آخر به اول باشد، باید از تابع xmatch استفاده کنید. اگر هم نیاز دارید که تمامی مواردی که با جستجو مطابقت دارند به عنوان خروجی بازگردانده شوند، می توانید از تابع filter بدین منظور بهره بگیرید.
تابع match در اکسل در حالت عادی فقط می تواند در آرایه ها یا بازه های سلولی یک بعدی جستجو کند، خواه این بازه افقی، خواه عمودی باشد. البته اگر بخواهید به کمک تابع match در آرایه های دو بعدی جستجو کنید، می توانید آن را با تابع index در اکسل به صورت ترکیبی مورد استفاده قرار دهید. همچنین با این ترکیب، این امکان را نیز خواهید داشت که در یک فرمول، دوبار از تابع match استفاده کنید. معمولاً حرفه ای های اکسل، تابع match را با تابع index ترکیب می کنند تا مقداری را در یک شرایط مطابقت خاص، بازیابی کنند. در این ترکیب، تابع match موقعیت را پیدا می کند و تابع index مقدار موجود در آن موقعیت را بازیابی می کند. ترکیب تابع match با تابع index قدرت مانور فراوان به ما می دهد، گویی به جای اینکه رستم و اسفندیار در دو جبهۀ مخالف یکدیگر بجنگند، هر دو را در یک لشکر سازمان دهیم.
همانطور که در لابلای آموزش ها متوجه شدید، هر چقدر بیشتر بر روی توابع مختلف در اکسل مسلط باشید، می توانید با ترکیب آنها، امکانات بیشتری را به خدمت بگیرید. در همین راستا آموزش استفاده از توابع و فرمول نویسی در اکسل Excel را که در فرادرس منتشر شده است، به شما پیشنهاد می کنم.
پارامتر اختیاریِ match_type
پیشتر اشاره کردیم که پارامتر سوم تابع match در اکسل اختیاری می باشد. اگر این مقدار را مشخص نکنید به صورت پیش فرض اکسل آن را \(1\) در نظر می گیرد. هنگامی که مقدار پارامتر match_type بر روی \(1\) یا \(-1\) تنظیم شود، گاهی اوقات به آن مطابقت تقریبی (approximate match) می گویند. با این حال یادتان باشد که این اسمش است و در عمل تابع match همیشه مقدار دقیق را مورد جستجو قرار می دهد، مگر اینکه از وایلدکاردها استفاده کنید. در ادامه مقادیر پارامتر match_type و شرح هر کدام را می بینید:
-
\(1\) تقریبی (Approximate): در این حالت، تابع match بزرگترین مقداری که کوچکتر یا برابر با مقدار جستجو (lookup value) باشد را پیدا می کند. فراموش نکنید که آرایۀ مورد جستجو (Lookup array) باید در شکل صعودی (ascending order) مرتب شده باشد.
-
\(0\) دقیق (Exact): در این حالت، تابع match اولین مقداری را که برابر با مقدار جستجو باشد، می یابد. وقتی که پارامتر match_type برابر با \(0\) باشد، دیگر ضرورتی ندارد که آرایۀ مورد جستجوی شما مرتب شده باشد.
-
\(-1\) تقریبی (Approximate): در این حالت، تابع match کوچکترین مقداری را که بزرگتر یا برابر با مقدار جستجو باشد، پیدا می کند. در این وضعیت آرایۀ مورد جستجو باید به شکل نزولی (descending order) مرتب شده باشد.
هشدار مهم: همانطور که اشاره کردیم، پارامتر match_type به صورت پیش فرض دارای مقدار \(1\) می باشد که یک جستجوی تقریبی است و باید حتماً مرتب سازی صعودی بر روی آرایۀ جستجو را داشته باشد. بنابراین فراموش نکنید که همیشه به این پارامتر مقدار \(0\) را پاس کنید که مطلوبترین حالت مطابقت می باشد. البته بهتر بود که خود اکسل هم، این مقدار را پیش فرض می کرد که متأسفانه اینگونه نیست.
مطابقت دقیق (Exact match)
هنگامی که آرگومان ارسالی به پارامتر match_type برابر با \(0\) باشد، تابع match یک عملیات مطابقت دقیق را صورت می دهد. در مثال زیر، فرمولی که در سلول \(\text{E3}\) نوشته شده است را می بینید:
=MATCH(E2,B3:B11,0)
در مثال بالا، مقدار مورد جستجو از سلول \(\text{E2}\) خوانده می شود. اگر بخواهید مقدار مورد جستجو را از سلول نخوانید، در واقع به جای متغیر بودن مقدار مورد جستجو، آن را به شکل هاردکد (hardcode) در فرمول بنویسید. باید آن را در یک جفت گیومه قرار دهید. در این حالت، فرمول شما این گونه خواهد بود:
=MATCH("Mars",B3:B11,0)
نکته: تابع match در اکسل حساس به حروف بزرگ و کوچک نیست. بنابراین از دید تابع match مقدار Mars با مقدار mars با مقدار MarS هر سه یکسان هستند و به چیز واحدی اشاره می کنند. اگر هر کدام از این سه مقدار را به تابع نوشته شده در مثال بالا پاس کنید، خروجی همان \(4\) خواهد بود.
همانطور که احتمالاً می دانید، در حال حاضر آخرین نسخۀ ارائه شده از محصول آفیس که اکسل هم جزئی از آن می باشد، آفیس 2019 می باشد. اگر مایلید که به آخرین نسخۀ این نرم افزار سوئیچ کنید و از امکانات موجود در آن بهره ببرید، آموزش اکسل Microsoft Excel 2019 را به شما پیشنهاد می کنم.
مطابقت تقریبی (Approximate match)
هنگامی که مقدار پارامتر match_type در تابع match در اکسل برابر با \(1\) باشد، مطابقت به صورت تقریبی بر روی مقادیری که به صورت \(\text{A-Z}\) مرتب شده اند، صورت می پذیرد. در این حالت بزرگترین مقداری که کوچکتر یا برابر با مقدار مورد جستجو باشد، خروجی تابع خواهد بود. در مثال زیر، فرمول موجود در سلول \(\text{E3}\) به شرح زیر می باشد:
=MATCH(E2,B3:B11,1)
همانطور که در این مثال می بینید، ما مقدار \(575\) را جستجو کرده ایم. اما این مقدار در آرایۀ مورد جستجوی ما وجود نداشته است. در عوض مقادیر \(500\) و \(600\) در آرایه وجود داشته اند که البته به صورت صعودی نیز مرتب سازی شده اند. در این وضعیت تابع match موقعیت سلولی که مقدار \(500\) در آن قرار داشته است را به ما باز می گرداند که در اینجا برابر با \(5\) می باشد.
مطابقت با وایلدکارد (Wildcard match)
قبل از هر چیز اجازه دهید، ببینیم وایلدکارد (wildcard) چیست. وایلدکارد چیزی جز یک کاراکتر خاص نیست که به ما امکان انجام جستجوهای خاصی را می دهد. در اکسل سه وایلدکارد مهم وجود دارد که می توانید در جستجوهایتان از آنها استفاده کنید. این سه وایلدکارد عبارتند از:
-
* ستاره (Asterisk): وایلدکارد * به معنای وجود صفر یا هر تعداد کاراکتر در موقعیت جستجو می باشد.
-
? علامت سوال (Question mark): وایلدکارد ? به معنای وجود یک کاراکتر می باشد که می تواند هر کاراکتری باشد.
-
~ تیلدِ (Tilde): وایلدکارد ~ را به عنوان کارکتر فرار می شناسند. از آنجا که کاراکترهای * و ? در اکسل معنای خاصی می دهند، اگر بخواهیم خود آنها را جستجو کنیم، طبیعتاً به دلیل آن معنای خاص، اکسل منظور ما را طور دیگری برداشت می کند. بنابراین اگر بخواهیم در جایی به اکسل بگوییم که مثلاً منظور ما از * این نیست که به صورت وایلدکارد از آن استفاده کنی و خود آن * را برایم بگرد و پیدا کن، در آن صورت از کاراکتر فرار استفاده می شود. کاراکتر فرار را اینگونه می توان نوشت: \(\text{~*}\) یا \(\text{~?}\) و یا \(\text{~~}\). از آنجا که خود تیلدِ نیز یک وایلدکارد است، برای اینکه خودش را هم به عنوان وایلدکارد نادیده بگیرد، از شکل سوم استفاده می شود.
توجه داشته باشد که وایلدکاردها فقط با متن کار می کنند و احیاناً از آنها بر روی مقادیر عددی استفاده نکنید.
خوب، حالا که با وایلدکارد و کارکرد آن آشنا شدید، می توانید از آن در تابع match در اکسل بهره ببرید. وقتی که مقدار پارامتر match_type برابر با \(0\) باشد، می توانید از وایلدکاردها استفاده کنید. در مثال زیر، فرمول موجود در سلول \(\text{E3}\) را می بینید:
=MATCH(E2,B3:B11,0)
اگر بخواهیم همین فرمول را به صورت هارد کد بنویسیم، خواهیم داشت:
=MATCH("pq*",B3:B11,0)
اکسل دانستنی های زیادی دارد که هر کدامشان می توانند در جایی برای شما سودمند باشند. اگر مایلید که از اکسل بهینه تر و بهتر استفاده کنید، آموزش ابزارهای کاربردی اکسل را به شما پیشنهاد می کنیم.
در اینجا مقدار جستجو در تابع match برابر با \(\text{pq*}\) می باشد، یعنی ما از از این تابع خواسته ایم که مقداری را برای ما پیدا کند که دو حرف آخر آن \(\text{pq}\) باشند و در ادامۀ آنها هر کاراکتری به هر تعداد می تواند باشد. خروجی یافت شده در اینجا \(\text{pqr-121}\) است که موقعیت آن در آرایۀ مورد جستجو، یعنی \(6\) به عنوان خروجی تابع بازگردانده شده است. اگر مقدار ما هر کدام از مقادیر \(\text{pqf-dlk}\) یا \(\text{pq150}\) یا \(\text{pq_dance}\) می بود، جستجو همین خروجی را می داشت.
استفادۀ ترکیبی از تابع match و تابع index
همانطور که در لابلای این مقاله اشاره کردیم، ترکیب دو تابع match و index مثل قراردادن مارادونا و پله در کنار یکدیگر می ماند و قدرت فراوانی به شما می دهد. در واقع معمولاً هیچگاه تابع match را به صورت جداگانه استفاده نمی کنند (مگر در کد نویسی که بحثش جدا است). در مثال زیر، ترکیب این دو تابع را می بینید. فرمولی که در سلول \(\text{F5}\) نوشته شده است، به شرح زیر است:
=INDEX(C5:C12,MATCH(F4,B5:B12,0))
در این مثال ابتدا تابع match موقعیت متن جستجو شده را پیدا می کند که \(7\) می باشد. سپس تابع index این موقعیت را به عنوان ورودی اش دریافت می کند و مقدار آن را که \(\text{\$150.00}\) می باشد، باز می گرداند. این تکنیک که از توابع به صورت تو در تو استفاده کنید و مقدار خروجی یکی را به عنوان مقدار ورودی تابع دیگر پاس کنید، تکنیکی بسیار قدرتمند است که نه تنها برای ترکیب این دو تابع می توانید بکار ببرید، بلکه فراتر از آن در کلیۀ توابع اکسل می توانید چنین تکنیکی را بکار گیرید. مشروط بر اینکه نوع دادۀ خروجی و ورودی با یکدیگر مطابقت داشته باشند.
در پایان این مقاله مایلم منابع آموزشی زیر را با شما به اشتراک بگذارم:
دسته بندی مطالب خوش آموز
نمایش دیدگاه ها (0 دیدگاه)
دیدگاه خود را ثبت کنید: