تابع Match، تابعی قدرتمند برای ترکیب با سایر توابع
تابع match در اکسل
برای اینکه به یک فرمول نویس حرفه ای در اکسل تبدیل بشید، علاوه بر اینکه باید به اصول و قواعد فرمول نویسی حرفه ای آشنا باشید، باید به یک سری توابع خاص نیز مسلط باشید. یکی از اون توابع خاص، تابع Match در اکسل از دسته توابع Lookup & Reference هست. درک مفهوم این تابع خیلی اهمیت داره. چرا که در حل خیلی از مسائل و در فرمول نویسی ترکیبی، از این تابع استفاده میشه. این تابع مکان یک داده رو در یک محدوده تک بعدی به ما میده. یعنی چی؟
فرض کنید در شکل ۱، میخوایم ببینیم کلمه “شعبه۳” چندمین سل در این محدوده است و انتظار داریم که خروجی فرمول، عدد ۳ باشد، چرا که این کلمه (شعبه۳)، سومین سل در محدوده مورد نظر است.
شکل ۱- پیدا کردن مکان کلمه “شعبه۳”
تشریح آرگومان ها
Lookup_Value: عبارتیست که میخواهیم ببینیم چندمین سل در محدوده مورد نظر است. در مثال بالا، کلمه شعبه ۳ است.
Lookup_Array: آرایه ای است تک بعدی که در آن، دنبال عبارت Lookup_Value می گردیم. در مثال بالا، B2:G2 هست.
[Match_Type]: سه مقدار ۱، ۰ و -۱ میگیرد:
مقدار ۰ ، اولین داده ای که دقیقا معادل Lookup_Value هست رو پیدا میکنه و شماره مکانشو میگه. در این حالت توالی قرارگیری محتوا در Lookup_Array به هر طریقی می تواند باشد.
مقدار ۱ (حالت پیش فرض، یعنی با خالی گذاشتن آرگومان سوم، این حالت در نظر گرفته می شه)، اگر عین عبارت رو پیدا نکنه، بزرگترین مقدار کوچکتر و یا مساوی Lookup_Value را پیدا و جایگاهش رو میده.
در این حالت مقادیر درون Lookup_Array باید به صورت صعودی باشند. به عنوان مثال: -۲، -۱، ۰، ۱، ۲، …
مقدار ۱-، اگر عین عبارت رو پیدا نکنه، بزرگترین مقدار کوچکتر و یا مساوی Lookup_Value را پیدا و جایگاهش رو میده.
در این حالت مقادیر درون Lookup_Array باید به صورت نزولی مرتب شده باشند. به عنوان مثال: ۲، ۱، ۰، -۱، -۲، …
در مثال بالا، تابع به اینصورت نوشته میشه:
=Match(D2,B2:G2,0)
خروجی این تابع ۳ خواهد بود. چون همونطور که توضیح داده شد، کلمه شعبه۳، سومین سل در محدوده مورد نظر است.
دقت داشته باشید که Lookup_Array، حتما یک محدوده تک بعدی، یعنی یک ستون یا یک ردیف باید باشه.
چون خروجی تابع Match، عدد هست، با اکثر توابعی که آرگومان هایی از جنس عدد دارن قابلیت ترکیب داره. یکی از این ترکیب های کاربردی رو در مثال زیر می بینیم:
ترکیب تابع Match و تابع Vlookup
همونطور که میدونید آرگومان سوم در تابع Vlookup ، یا همون Col_Index عدد هست. پس میتونیم از تابع Match توی این آرگومان استفاده کنیم. همون مثال تشریح شده در تابع Vlookup رو در نظر بگیرید. میخواهیم تابع رو طوری بنویسیم که با تغییر سل H2، جواب نیز تغییر کنه. یعنی (مطابق زیر) هر بار محاسبه بشه که عبارتی که در سل H2 نوشته میشه، چندمین سل در محدوده A1:E1 است.
برای این کار در آرگومان سوم Vlookup از تابع Match استفاده می کنیم.
=Vlookup(G3,A1:E16,Match(H2,A1:E1,0),0)
با این کار شماره ستون در تابع Vlookup رو وصل میکنیم به تابع Match که هر بار با تغییر H2، محاسبات بروز شود.
شکل ۲- ترکیب تابع Match و Vlookup
خب، ماهیت تابع Match در اکسل رو با یک ترکیب کاربردی شرح دادیم. این تابع یکی از مهم ترین توابع جستجو به حساب میاد که تسلط به آن، در حل خیلی از مسائل کمک کننده خواهد بود.
سلام و درود
سوالی داشتم ؟
فرض کنیم کسلی از دانش آموزان کلاسی ۳۰ نفره داریم . که نمرات دروس فیزیک، ریاضی، شیمی ، زبان ، هندسه و فارسی آنها در این اکسل کنار اسامی در ستونهای مختلف درج شده است. حال اگر بخواهیم فرد یا افرادی که بیشترین نمره و یا کمترین نمره در درس ریاضی را گرفته اند ه دستوراتی لازم داریم ؟ و به همین ترتیب برای دروس دیگر در شیت های بعدی بتونیم انجام بدهیم . با تشکر از شما
mehdi_bu58@yahoo.com
درود بر شما
اگر unpivot کنید و دیتابیس ماتریسی نباشه خیلی راحت با پیوت میشه
اما در این حالت با ترکیب match, index و یا اگر فرمول نویسی آرایه ای میدونید، با اون میتونید انجام بدید
به این ترتتیب که با تابع max و min بیشترین و کمترین نمره رو پیدا کنید
بعد با match مکانشو پیدا کنید و بعد با index اسم دانش اموز رو مشخص کنید
برای همه این موارد نمونه های زیادی داخل سایت ارائه شده. در ادامه لینک ها رو قرار میدم
https://excelpedia.net/index-function/
https://excelpedia.net/match-function/
https://excelpedia.net/index-function-tricks/
https://excelpedia.net/unpivot-data/
با سلام و احترام
در محیط VBA خط برنامه زیر را نوشته ام (هدفم اختصاص دادن خروجی Match به یک متغیر عددی است)
Col = WorksheetFunction.Match(5045, “H7:L7”, 0)
اما با پیغام زیر مواجه میشم
Unable to get the Match property of the WorksheetFunction class
علت چیست؟
با تشکر
درود
کلمه range رو قبل از محدوده match نذاشتید
WorksheetFunction.Match(943, Range(“A:A”), 0)
سلام وقتتون بخیر
من یه فرمول نوشتم که match index میره قاعدتا از سسر اول میگرده پیدا میکنه
من میخوام این جستجو رو از سطر آخر اون table انجام بده
چجوری میتونم اینکارو انجام بدم؟
ممنونم
درود
یا میتونید از تابع Xmatch در ورژن ۲۰۲۱ استفاده کنید
یا از فرمول نویسی آرایه ای استفاده کنید
برای این موضوع مقاله جستجوی موارد تکراری رو ببینید
با عرض سلام و خسته نباشید من ی جدول فروش دارم که طبق تاریخه و فروش در هر تاریخ بین ۶ تا ۷ مورد یا بیشترم میشه حالا میخوام که گزارش فروش به تفکیک تاریخ بگیرم که مثلا فلان تاریخ فروش چقدر بوده و مبلغ فروش به کجا رفته مثلا از طریق کارتخوان بوده یا واریز به حساب و … از طریق کدوم فرمول اینکارو بکنم لطفا کمکم کنید چون خیلی درگیرشم اگه لطف کنید فرمولشم بفرستید ممنون میشم
سلام
برای ایجاد این گزارش حتما از Pivot Table استفاده کنید.
سلام
اگه شعبه ۱ تا ۶ از سمت راست شروع شده باشه؟
غاطی میکنه!!!
درود بر شما
قاطی نمیکنه! با توجه به منطق تابع عمل میکنه و جهت محدوده ای که میگیره. از راست به چپ باشه، باید جهت صفحه هم راست به چپ باشه
باسلام
فرمان VLOOKUP(D3, G3:J5, MATCH(C3, H2:J2, 0) + 1, FALSE) مشکلی نداره ولی وقتی به جای D3 از فرمول Cells(3,4) استفاده می کنم با پیغام خطا مواجه میشم. مشکل فرمول چیه؟
باسلام
ممنون از سایت خوبتون
یه سوال داشتم از خدمتتون
یه مثال از ترکیب Match در Vlookup زدید
=Vlookup(G3,A1:E16,Match(H2,A1:E1,0),0)
حالا اگر بخواهیم همین ترکیب را در VBA به کار ببریم با این پیغام خطا مواجه می شیم که دستور Match تعریف نشده است. برای حل این مسئله باید چیکار کنم؟
سلام و تشکر از لطف شما،
برای استفاده از تابع Match باید به صورت زیر کد بزنید:
با عرض سلام
در استفاده از تابع match میخواهم دنبال اولین سلول خالی بگردم. وقتی در آرگومان اول مقدار ۰ رو قرار میدم خطا #N/A میده. باید مقدار آرگومان اول رو چی قرار بدم؟
با تشکر از تیم اکسل پدیا.
درود بر شما
سلام
یه مشکلی داشتم که تقریبا ۲-۳ روزه درگیرش هستم ولی نتونستم درستش کنم
من میخوام برای حداکثر ۸ تا محصول در ۱۲ ماه سال گرافی از نرخ ترسیم کنم
در جدول جداگونه برای هر محصول جداگونه یه فرمول IF نوشتم و با گزینه TRUE (که با تیک فعال میشه) میتونم توی نمودار اضافش کنم اما مشکل اصلی اینه که برای ۱۲ ماه سال الزاما قیمت نیست مثلا ۴ ماه اول سال هست بعد توی مرداد قیمت جدید اضافه میشه و به همین ترتیب. با این حال تمام ماه ها رو بهم نشون میده. برای سایر نمودارها هم همین مشکل رو دارم.
حتی فرمول با OFFSET و MATCH هم نوشتم اما برای نمودار وقتی ادیت رو میزنم و میخوام محدوده دیتا رو با فرمول مشخص کنم اصلا کار نمی کنه و ارور میده.
ممنون میشم کمک کنید
درود بر شما
برای ایجاد نمودار با محدوده متغیر از این لینک کمک بگیرید:
https://excelpedia.net/dynamic-chart/
مهندس اون دوتا فرمولی آخری که بهم دادین من وارد میکنم دوباره #REF مینویسه و جوابی نمیده
درود
خطای Ref به این معنی که به یجا خارج از محدوده ارجاع میده
باید منطق فرمول رو خوب درک کنید تا بتونید خطا رو رفع کنید.
https://excelpedia.net/error-management/
منم دقیقا همین فرمولی که مهندس دادن رو وارد میکنم
بازده اطلاعاتیم بین A1 تا L1 و A2 تا L2 هست که فرمولیم که ایشون دادن بصورت پیشفرض رو همین بازده فرمول نویسی شده..ولی باز ref میزنه.من هیچی تغییر نمیدم
میخواین شما خودتون تو اکسل a1 تا L1 ماههای سالو بنویسید و در a2 تا L2 یسری اعداد بنویسید بعد ببینید جوابش چی میاد
بله
به این صورت اصلاح کنید
دقت کنید که اگر تا خرداد داده دارید، نبادی تا خرداد یکی از سلول ها خالی یا سفر باشه. اگر منطق داده ها طوریه که ممکنه برای یک ماه خالی بمونه. این فرمول ح نمیده و باید ارایه ای نوشته بشه.یعنی بصورت زیر:
دقت کنید فرمول آرایه ای بصورت Ctrl+shift+Enter ثبت میشه.
با سلام میخواهم تکراری کالاها حذف شود بالاترین قیمت بماند راهنماییم کنید تشکر
درود بر شما
لیست غیر تکراری رو با ابزار Remove Duplicate ایجاد کنید
https://excelpedia.net/remove-duplicate/
بعد برای بدست اوردن بیشترین مقدار، یا از Dmax استفاده کنید یا حالت آرایه ای برای Max (If….)
https://excelpedia.net/array-formula/
سلام
بلخشید من یه سوال دارم که جوابشو هیچ جایی پیدا نکردم…
من دوتا سطر دارم.(سطر ۱ و ۲)
سطر ۱ شامل ۱۲ماه سال هستش که هر ماهو داخل یه ستون نوشتم
سطر ۲ شامل حقوقی هست که من تو هر ما میگیرم
حالا من چجوری میتونم یه فرمولی بنویسم که توی یه سلول یا توی دوتا سلول بهم بگه که به عنوان مثال خرداد ماه فلان حقوقو گرفتی
ینی تو بازده ۱۲ ماهه خودش بیشترین حقوقو پیدا کنه بعد ماهشم بگه
ینی فقط مبلغ حقوق نباشه، ماهشم باشه
خواهشا کمکم کنید
برام مهمه
درود بر شما
اگر عدد تکراری نداشته باشید(اگر هم بیشترین حقوق دوبار یا بیشتر بود، اولی رو در نظر بگیرید)، به این روش میشه:
با فرض اینکه در ردیف ۱ ماهها نوشته شده و در ردیف ۲ حقوق ها
نکته:
اگر بخواید تکراری ها رو دخیل کنید باید آرایه ای فرمول نویسی کنید
مهندس دستت درد نکنه
همونی بود که میخواستم
حالا یه سوال دیگه
من از این فرمول تو چنتا چیز استفاده کردم
مثل بیشترین حقوق،کمترین حقوق،بیشترین اضافه کار،کمترین اضافه کار،حالا بعدش اومدم رو بیشترین مرخصی هم همین کارو کنم ولی وقتی تایید میکنم N/A میاد
مطمئنم فرمول درست وارد کردم
مشکلم کجاست؟
خواهش میکنم
با این حجم از اطلاعاتی که شما در مورد مشکلتون دادید که هیچ جوابی نمیشه داد.
لطفا فرمولی که مشکل داره رو بفرستید.
مشکلو پیدا کردم مهندس
دستتون درد نکنه
مهندس یه سوالی برام پیش اومده
من الان ۱۲تا سطر دارم برای آمار اضافه کار ۱۲ ماهم.(همون توضیحاتی که بالا دادم)… که همشونم با sum فرمول نویسی کردم که خودش جمع کنه.الان دوماه از سال میگذره و من اطلاعات دو ماهو وارد کردم.پس بقیه ماها بصورت پیشفرض تو قسمت جمعشون صفر هستش.
حالا مشکل من اینجاس که شما این فرمولی که بالا به من دادین برای max که طبیعتا درسته،،ولی در حالت min خودش میره اون ماهایی رو که من اطلاعاتی وارد نکردم و به صورت پیشفرض صفر خورده رو به عنوان مثال کمترین اضافه کار تو فلان ماه میشناسه.که طبیعتا در این حالت پس پیدا کردن کمترین ماه چه از لحاظ اضافه کارو… بی فایدس
حالا سوال من اینه که نمیشه کاری کرد که تو حالت min ، اون ماهایی روکه پیشفرض صفر خورده رو تو نتیجه نیاره؟؟
سلام
از فرمول زیر میتونید استفاده کنید:
مهندس اول از همه ممنونم از پاسخگوییتون
این فرمولی که نوشتین من وارد میکنم ولی جوابش REF# مینویسه
بعد اینکه من دوتا سطر دارم که اولی ماهها و دومی اضافه کارا،، ولی شما برای یه سطر (اولی) فرمول نویسی کردین
من میخوام که جوابش تو دوتا سلول بیاد،یعنی سلول اول ماهو پیداکنه و سلول زیریش کمترین اضافه کارو (بجز صفرهای پیشفرض) پیدا کنه که فکر کنم باید دوتا فرمول بنویسم(تو هر سلول جدا)
بنظرتون مشکلم کجاست؟؟
سلام
خواهش میکنم
بازه های فرمول رو طوری تنظیم کنید که منطبق بر دو سطری که اطلاعات دارید بشه:
بدست آوردن ماه کمترین اضافه کار
بدست آوردن کمترین مقدار:
متشکر از آموزش های عاااالیتون-من توی یکی از شیت هام نوع محصول و وزن محصول فروخته شده به مشتری های مختلف رو دارم -میخواستم جمع هر کدوم از محصول های فروخته شدم رو توی یه شیت دیگه داشته باشم -برای این کار قبلا فیلتر میکردم نوع محصول رو وجمعشونو در میاوردم -راه بهتر و سریع تری هست امکانش هست کمکم کنید
سلام
خوشحالیم که از مطالب استفاده میکنید.
بهترین ابزار برای شما Pivot Table هست.
مطالب زیر رو مطالعه کنید:
پیوت تیبل چیست؟
همه چیز از پیوت تیبل
واقعا” بصورت واضح و جالب توضیح داده اید.