مهندس تبریزی
2019/08/07, 01:59
آموزش تابع Match اکسل و نحوه استفاده از آن
https://worksheet.ir/wp-content/uploads/2014/11/match.jpg
تابع Match اکسل یکی از توابع مهم این نرم افزار هست که خیلی از مواقع میتونه مشکلات رو حل کنه. این تابع یک مقدار رو در یک محدوده جستجو میکنه و در صورت پیدا کردن اون مقدار در محدوده مورد نظر، موقعیت مقدار مورد نظر رو بر میگردونه. در ادامه با کاربرد این تابع بیشتر اشنا میشیم.
قاعده کلی تابع Match اکسل به صورت زیر هست:
(نوع جستجو [اختیاری]، محدوده جستجو، مقدار موردنظر برای جستجو) MATCH
برای درک بهتر نحوه استفاده از تابع اکسل جدول زیر رو در نظر بگیرید:
A
B
C
D
E
F
1
کد
نام
دارای تخفیف
2
1432
ماوس
1230
3
1134
کیبرد
1245
4
5400
کیس
1160
5
3211
مانیتور
234
6
1611
اسپیکر
4500
7
1610
گیم پد
1610
8
1200
هدست
6570
9
1345
گرافیک
1250
10
8190
هارد
5400
همونطور که در جدول بالا میبینید در ستون A ما کد محصولات رو داریم و در ستون F کد محصولاتی رو که شامل تخفیف هستند. حالا میخوایم با استفاده از تابع MATCH فرمولی در ستون C بنویسیم که اگر کد محصول ستون A شامل تخفیف بود این مساله نشون داده بشه. برای اینکار میتونیم از تابع MATCH اکسل استفاده کنیم:
مقدار موردنظر برای جستجو: ما میخوایم کد هر محصول رو در لیست کدهای شامل تخفیف جستجو کنیم و ببینیم آیا این کد شامل تخفیف هست یا خیر. پس پارامتر مقدار مورد نظر برای جستجو برابر A:A خواهد بود.
محدوده جستجو: همونطور که مشخصه ما میخوایم کد محصول رو در بین کدهای شامل تخفیف که در ستون F قرار دارند جستجو کنیم. بنابراین
پارامتر محدوده جستجو در مثال ما برابر F:F هست.
نوع جستجو: این پارامتر میتونه چندین حالت مختلف زیر رو داشته باشه:
1 یا عدم تعیین پارامتر: اگر مقدار این پارامتر رو برابر 1 قرار بدیم یا مقداری برای اون تعیین نکنیم تابع مقدار موردنظرمون رو در محدوده تعریف شده جستجو میکنه و اگر مقدار موردنظر رو پیدا نکرد بزرگترین مقداری که کوچکتر از مقدار موردنظر ما برای جستجو بوده رو لحاظ میکنه. به عنوان مثال اگر کد 8190 رو جستجو کنیم به دلیل اینکه این کد در ستون F وجود نداره بزرگترین کدی که از عدد 8190 کوچکتره یعنی کد 6570 لحاظ میشه.
0: اگر مقدار گارامتر رو برابر 0 قرار بدیم تابع فقط مقدار مورد نظر ما رو جستجو میکنه و در صورتی که مقدار موردنظرمون رو در محدوده تعیین شده پیدا نکنه خطای N/A# رو برمیگردونه. معمولا ما در اکثر موارد این حالت مد نظرمون هست و این حالت بیشترین کاربرد رو داره.
1-: اگر مقدار این پارامتر رو برابر 1- قرار بدیم تابع مقدار موردنظرمون رو در محدوده تعریف شده جستجو میکنه و اگر مقدار موردنظر رو پیدا نکرد کوچکترین مقداری که بزرگتر از مقدار موردنظر ما برای جستجو بوده رو لحاظ میکنه. به عنوان مثال اگر کد 1134 رو جستجو کنیم به دلیل اینکه این کد در ستون F وجود نداره کوچکترین کدی که از عدد 1134 بزرگتره یعنی کد 1160 لحاظ میشه.
در این مثال ما میخوایم اگر کدی شامل تخفیف نمیشد این مساله مشخص بشه. بنابراین ما مقدار این پارامتر رو برابر 0 در نظر میگیریم. پس در نهایت فرمول ما به شکل زیر کامل میشه:
(MATCH(A:A;F:F;0
حالا این فرمول رو در سلول C2 قرار میدیم و تا پایین کپی میکنیم. نتیجه به شکل جدول زیر میشه:
A
B
C
D
E
F
1
کد
نام
دارای تخفیف
2
1432
ماوس
#N/A
1230
3
1134
کیبرد
#N/A
1245
4
5400
کیس
10
1160
5
3211
مانیتور
#N/A
234
6
1611
اسپیکر
#N/A
4500
7
1610
گیم پد
7
1610
8
1200
هدست
#N/A
6570
9
1345
گرافیک
#N/A
1250
10
8190
هارد
#N/A
5400
همونطور که میبینید عدد مربوط به شماره سطر کدهای 5400 و 1610 که در ستون F قرار داشتند( به ترتیب 10 و 7) در مقابل اونها قرار گرفته و برای سایر کدها که در ستون تخفیف قرار نداشتند خطای N/A# نمایش داده شده. میتونیم برای اینکه جدولمون خواناتر بشه به جای اعداد از عبارت های مورد نظرمون استفده کنیم. مثلاً فرمول تابع MATCH خودمون رو به شکل زیر تکمیل کنیم:
(“تخفیف ندارد”;”تخفیف دارد”; ((IF(ISNUMBER(MATCH(A:A;F:F;0
در فرمول بالا تعیین کردیم که اگر نتیجه تابع عدد بود (تابع MATCH اکسل مقدار مورد نظر مارو پیدا کرد) عبارت تخفیف دارد و در غیر اینصورت عبارت تخفیف ندارد نشون داده بشه. حالا جدول ما به شکل زیر تغییر میکنه:
A
B
C
D
E
F
1
کد
نام
دارای تخفیف
2
1432
ماوس
تخفیف ندارد
1230
3
1134
کیبرد
تخفیف ندارد
1245
4
5400
کیس
تخفیف دارد
1160
5
3211
مانیتور
تخفیف ندارد
234
6
1611
اسپیکر
تخفیف ندارد
4500
7
1610
گیم پد
تخفیف دارد
1610
8
1200
هدست
تخفیف ندارد
6570
9
1345
گرافیک
تخفیف ندارد
1250
10
8190
هارد
تخفیف ندارد
5400
تابع MATCH اکسل کاربردهای زیادی داره و به ویژه ترکیبش با تابع INDEX اکسل بسیار مفیده که در مطالب بعدی راجع بهش توضیح میدم. امیدوارم از این مطلب استفاده کرده باشید.
به نقل از سایت worksheet.ir
لینک مطلب (http://worksheet.ir/%D8%AA%D8%A7%D8%A8%D8%B9-match-%D8%A7%DA%A9%D8%B3%D9%84-%D9%88-%D9%86%D8%AD%D9%88%D9%87-%D8%A7%D8%B3%D8%AA%D9%81%D8%A7%D8%AF%D9%87-%D8%A7%D8%B2-%D8%A2%D9%86/formula/)
https://worksheet.ir/wp-content/uploads/2014/11/match.jpg
تابع Match اکسل یکی از توابع مهم این نرم افزار هست که خیلی از مواقع میتونه مشکلات رو حل کنه. این تابع یک مقدار رو در یک محدوده جستجو میکنه و در صورت پیدا کردن اون مقدار در محدوده مورد نظر، موقعیت مقدار مورد نظر رو بر میگردونه. در ادامه با کاربرد این تابع بیشتر اشنا میشیم.
قاعده کلی تابع Match اکسل به صورت زیر هست:
(نوع جستجو [اختیاری]، محدوده جستجو، مقدار موردنظر برای جستجو) MATCH
برای درک بهتر نحوه استفاده از تابع اکسل جدول زیر رو در نظر بگیرید:
A
B
C
D
E
F
1
کد
نام
دارای تخفیف
2
1432
ماوس
1230
3
1134
کیبرد
1245
4
5400
کیس
1160
5
3211
مانیتور
234
6
1611
اسپیکر
4500
7
1610
گیم پد
1610
8
1200
هدست
6570
9
1345
گرافیک
1250
10
8190
هارد
5400
همونطور که در جدول بالا میبینید در ستون A ما کد محصولات رو داریم و در ستون F کد محصولاتی رو که شامل تخفیف هستند. حالا میخوایم با استفاده از تابع MATCH فرمولی در ستون C بنویسیم که اگر کد محصول ستون A شامل تخفیف بود این مساله نشون داده بشه. برای اینکار میتونیم از تابع MATCH اکسل استفاده کنیم:
مقدار موردنظر برای جستجو: ما میخوایم کد هر محصول رو در لیست کدهای شامل تخفیف جستجو کنیم و ببینیم آیا این کد شامل تخفیف هست یا خیر. پس پارامتر مقدار مورد نظر برای جستجو برابر A:A خواهد بود.
محدوده جستجو: همونطور که مشخصه ما میخوایم کد محصول رو در بین کدهای شامل تخفیف که در ستون F قرار دارند جستجو کنیم. بنابراین
پارامتر محدوده جستجو در مثال ما برابر F:F هست.
نوع جستجو: این پارامتر میتونه چندین حالت مختلف زیر رو داشته باشه:
1 یا عدم تعیین پارامتر: اگر مقدار این پارامتر رو برابر 1 قرار بدیم یا مقداری برای اون تعیین نکنیم تابع مقدار موردنظرمون رو در محدوده تعریف شده جستجو میکنه و اگر مقدار موردنظر رو پیدا نکرد بزرگترین مقداری که کوچکتر از مقدار موردنظر ما برای جستجو بوده رو لحاظ میکنه. به عنوان مثال اگر کد 8190 رو جستجو کنیم به دلیل اینکه این کد در ستون F وجود نداره بزرگترین کدی که از عدد 8190 کوچکتره یعنی کد 6570 لحاظ میشه.
0: اگر مقدار گارامتر رو برابر 0 قرار بدیم تابع فقط مقدار مورد نظر ما رو جستجو میکنه و در صورتی که مقدار موردنظرمون رو در محدوده تعیین شده پیدا نکنه خطای N/A# رو برمیگردونه. معمولا ما در اکثر موارد این حالت مد نظرمون هست و این حالت بیشترین کاربرد رو داره.
1-: اگر مقدار این پارامتر رو برابر 1- قرار بدیم تابع مقدار موردنظرمون رو در محدوده تعریف شده جستجو میکنه و اگر مقدار موردنظر رو پیدا نکرد کوچکترین مقداری که بزرگتر از مقدار موردنظر ما برای جستجو بوده رو لحاظ میکنه. به عنوان مثال اگر کد 1134 رو جستجو کنیم به دلیل اینکه این کد در ستون F وجود نداره کوچکترین کدی که از عدد 1134 بزرگتره یعنی کد 1160 لحاظ میشه.
در این مثال ما میخوایم اگر کدی شامل تخفیف نمیشد این مساله مشخص بشه. بنابراین ما مقدار این پارامتر رو برابر 0 در نظر میگیریم. پس در نهایت فرمول ما به شکل زیر کامل میشه:
(MATCH(A:A;F:F;0
حالا این فرمول رو در سلول C2 قرار میدیم و تا پایین کپی میکنیم. نتیجه به شکل جدول زیر میشه:
A
B
C
D
E
F
1
کد
نام
دارای تخفیف
2
1432
ماوس
#N/A
1230
3
1134
کیبرد
#N/A
1245
4
5400
کیس
10
1160
5
3211
مانیتور
#N/A
234
6
1611
اسپیکر
#N/A
4500
7
1610
گیم پد
7
1610
8
1200
هدست
#N/A
6570
9
1345
گرافیک
#N/A
1250
10
8190
هارد
#N/A
5400
همونطور که میبینید عدد مربوط به شماره سطر کدهای 5400 و 1610 که در ستون F قرار داشتند( به ترتیب 10 و 7) در مقابل اونها قرار گرفته و برای سایر کدها که در ستون تخفیف قرار نداشتند خطای N/A# نمایش داده شده. میتونیم برای اینکه جدولمون خواناتر بشه به جای اعداد از عبارت های مورد نظرمون استفده کنیم. مثلاً فرمول تابع MATCH خودمون رو به شکل زیر تکمیل کنیم:
(“تخفیف ندارد”;”تخفیف دارد”; ((IF(ISNUMBER(MATCH(A:A;F:F;0
در فرمول بالا تعیین کردیم که اگر نتیجه تابع عدد بود (تابع MATCH اکسل مقدار مورد نظر مارو پیدا کرد) عبارت تخفیف دارد و در غیر اینصورت عبارت تخفیف ندارد نشون داده بشه. حالا جدول ما به شکل زیر تغییر میکنه:
A
B
C
D
E
F
1
کد
نام
دارای تخفیف
2
1432
ماوس
تخفیف ندارد
1230
3
1134
کیبرد
تخفیف ندارد
1245
4
5400
کیس
تخفیف دارد
1160
5
3211
مانیتور
تخفیف ندارد
234
6
1611
اسپیکر
تخفیف ندارد
4500
7
1610
گیم پد
تخفیف دارد
1610
8
1200
هدست
تخفیف ندارد
6570
9
1345
گرافیک
تخفیف ندارد
1250
10
8190
هارد
تخفیف ندارد
5400
تابع MATCH اکسل کاربردهای زیادی داره و به ویژه ترکیبش با تابع INDEX اکسل بسیار مفیده که در مطالب بعدی راجع بهش توضیح میدم. امیدوارم از این مطلب استفاده کرده باشید.
به نقل از سایت worksheet.ir
لینک مطلب (http://worksheet.ir/%D8%AA%D8%A7%D8%A8%D8%B9-match-%D8%A7%DA%A9%D8%B3%D9%84-%D9%88-%D9%86%D8%AD%D9%88%D9%87-%D8%A7%D8%B3%D8%AA%D9%81%D8%A7%D8%AF%D9%87-%D8%A7%D8%B2-%D8%A2%D9%86/formula/)