مدل سازی ساده با اکسل “تابع Match”
23 مرداد 1400
دقیقه
تابع Match فهرست محتوا پنهان تابع Match دستور زبان تابع Match سؤالاتی که در این فصل پاسخ داده میشوند: با درنظرگرفتن فروش ماهیانه چند محصول، چگونه میتوانم فرمولی بنویسم که فروش یک محصول را در ماهی خاص به من نشان بدهد؟ مثلاً اینکه در ماه ژوئن چه مقدار از محصول شماره 2 فروختهام؟ لیست دستمزد...
آخرین بهروزرسانی: 18 بهمن 1401
تابع Match
سؤالاتی که در این فصل پاسخ داده میشوند:
- با درنظرگرفتن فروش ماهیانه چند محصول، چگونه میتوانم فرمولی بنویسم که فروش یک محصول را در ماهی خاص به من نشان بدهد؟ مثلاً اینکه در ماه ژوئن چه مقدار از محصول شماره 2 فروختهام؟
- لیست دستمزد پرداختی بازیکنان تیم بیسبال داده شده ، چگونه میتوانم فرمولی بنویسم که نام بازیکن گیرنده بالاترین دستمزد و بازیکنی که در رده پنجم بیشترین دستمزد گیرندگان قرار گرفته است را مشخص کند؟
- با درنظرگرفتن جریان نقدی یک پروژه سرمایهگذاری، چگونه میتوانم فرمولی بنویسم که تعداد سالهای موردنیاز برای بازپرداخت هزینه سرمایهگذاری اولیه پروژه را نشان دهد؟
دستور زبان تابع Match
فرض کنید کتاب کاری (Worksheet) با 5000 ردیف دارید که شامل 5000 نام است. قصد دارید نام شخصی بنام جان دو که میدانید جایی درون آن لیست قرار دارد (و فقط یکبار درج شده) را پیدا کنید. آیا دوست ندارید فرمولی یاد بگیرید تا شماره ردیفی که نام جان دو در آن درج شده را به شما نشان دهد؟ تابع Match شما را قادر میسازد تا در آرایهای ارائه شده، اولین نمایش کلمه مورد نظر را به شکل رشتهای از متن یا عدد پیدا نماید. شما میبایست در مواقعی که به موقعیت مکانی یک رقم و نه مقدار آن در سلولی خاص نیازمندید، از تابع Match بهجای تابع Lookup استفاده کنید. این دستور زبان استفاده از تابعMatch میباشد:
Match(lookup value ,lookup range,[match type])
در مثال بعدی فرض را برآن گرفتهایم که تمام سلولها در محدوده جستجو در یک ستون قرار گرفتهاند. قوانین زیر در دستور زبان این تابع صادق است:
Lookup Value یا مقدار جستجو، مقداری است که در محدوده جستجوی خود به دنبال آن هستید.
Lookup Range یا محدوده جستجو، محدودهای است که آن را برای پیداکردن مقدار برابر با مقدار مورد جستجو، بررسی می کنید. محدوده جستجو میبایست یک ردیف و یا یک ستون باشد.
Match type =1 یا نوع تطابق، نیازمند آن است که محدوده جستجو شامل اعدادی باشد که به ترتیب صعودی ردیف شده باشند. تابع MATCH در آن صورت، مکان ردیف موردنظر را در محدوده جستجو نشان میدهد (نسبت به بالای محدوده جستجو) که شامل بزرگترین رقم در محدوده است که کمتر و یا برابر با مقدار جستجو میباشد.
Match type = -1 نیازمند آن است که محدوده جستجو شامل اعدادی باشد که به ترتیب نزولی ردیف شده باشند. تابع MATCH مکان ردیف موردنظر را در محدوده جستجو نشان میدهد (نسبت به بالای محدوده جستجو) که شامل آخرین مقدار در محدوده جستجو است که بزرگتر یا مساوی مقدار جستجو میباشد.
Match type = 0 موقعیت ردیفی را در محدوده جستجو نشان میدهد که حاوی مقدار برابر با مقدار مورد جستجو میباشد. (موضوع پیداکردن دومین و یا سومین مورد تطبیق شده را در فصل 20 : توابع COUNTIF،COUNTIFS، COUNT،COUNTA وCOUNTBLANK توضیح داده ام) وقتیکه هیچ مقدار مطابقی وجود نداشته باشد و match type برابر با صفر باشد، اکسل پیام خطای #N/A را نشان میدهد. بیشتر کاربردهای تابع Match از match type=0 استفاده میکنند اما اگر آرگومان match type وجود نداشته باشد مقدار آن برابر با 1 فرض میشود؛ بنابراین ما از match type=0 وقتی استفاده میکنیم که محتویات سلولهای محدوده جستجو مرتب نشده باشد. معمولا با این وضعیت روبرو می شوید.
فایل نامگذاری شده به نام Matchex.xlsx که در تصویر 1-5 نشان داده میشود شامل سه مثال از دستور زبان تابع MATCH میباشد.
تصویر 1-5 استفاده از تابع Match برای پیداکردن مکان یک مقدار در محدوده جستجو
در سلول B13، فرمولMatch(“Boston,B4:B11,0) عدد یک را به ما میدهد چرا که اولین ردیف در محدوده B4:B11 شامل مقدار Boston است. نوشته ها حتماً میبایست در میان گیومه (“”) قرار گیرند.
در سلول B14، فرمول Match(“Phoenix”,B4:B11,0) مقدار هفت را به ما نشان میدهد چرا که سلولB10 (هفتمین سلول در محدوده B4:B11) اولین سلول در آن محدوده است که مقدار درون آن با Phoenix برابر است.
در سلولE12 ، فرمولMatch(0,E4:E11,1) عدد چهار را به ما میدهد چرا که آخرین عددی که کمتر یا مساوی مقدار صفر در محدوده E4: E11 است در سلولE7 (چهارمین سلول در محدوده جستجو) قرار گرفته است.
در سلول G12 ، فرمول MATCH(-4,G4:G11,-1) به ما عدد هفت را میدهد چرا که آخرین عددی که در محدوده G4:G11 بزرگتر یا مساوی با 4- میباشد عدد 4- است که در سلول G10 قرار گرفته (هفتمین سلول از محدوده جستجو).
تابع Match را میتوان در مورد مقادیری که کاملاً با مورد جستجو مطابقت ندارند نیز بکار برد. مثلاً فرمول سلول B15 ، MATCH(“Pho8”,B4:B11,0) عدد هفت را به ما میدهد. کاراکتر ستاره بهعنوان فرانویسه یاwildcard)) عمل میکند بدان معنی که با اضافهکردن آن، اکسل به دنبال اولین رشته متنی در محدوده B4:B11 میگردد که با حروف Phoشروع شده باشد. برحسب اتفاق این تکنیک را میتوان با تابع lookup مورداستفاده قرارداد. مثلاً در تمرین جستجوی قیمت در فصل سوم، تابع lookup ، فرمول VLOOKUP(“X*,lookup2,2) قیمت محصول X212 را نشان میدهد( 4.80 دلار)
اگر محدوده جستجو شامل یک ردیف باشد، اکسل با جستجوی مقادیر از چپ به راست، موقعیت اولین مورد مطابق در محدوده جستجو را نشان میدهد. همانطور که در مثالهای بعدی نشانداده شده، تابعMatch اغلب وقتی با سایر توابع اکسل مثل VLOOKUP، INDEX و Max ترکیب شود کارایی بسیار خوبی خواهد داشت.
پاسخ به سؤالات این فصل
با درنظرگرفتن فروش ماهیانه چند محصول، چگونه میتوانم فرمولی بنویسم که فروش یک محصول را در ماهی خاص به من بدهد؟ مثلاً اینکه در ماه ژوئن چقدر از محصول شماره 2 فروختهام؟
فایل Productlookup.xlsx(تصویر 2-5) فهرست فروش چهار عروسک بازیکنان لیگ بسکتبال ان بی ای از ماه ژانویه تا ژوئن را نشان میدهد. چگونه میتوانید فرمولی بنویسید که فروش محصولی خاص را در ماهی خاص مشخص محاسبه نماید؟ راهحل آن است که از تابع MATCH برای یافتن ردیفی که محصول مورد نظر در آن قرار دارد و از تابع MATCH دیگری برای یافتن ستونی که ماه موردنظر در آن درج شده استفاده نمایید. شما میتوانید از تابع INDEX برای بهدستآوردن فروش آن محصول برای ماه مورد نظر استفاده کنید.
تصویر 2-5 تابع Match میتواند در ترکیب با سایر توابع مثلINDEX و یا VLOOKUP استفاده شود.
من به محدوده B4:G7 که حاوی دادههای فروش عروسکها است نام Sales یا فروشها را اختصاص داده ام. نام محصولی را که میخواستم دربارهاش بدانم در سلول A10 و نام ماه را در سلول B10 وارد کردم. در سلول C10 از فرمول MATCH(A10,A4:A7,0) استفاده کردم تا مشخص کنم کدام شماره ردیف از محدوده فروشها شامل ارقام فروش عروسکهای بازیکنی بنام کوبه است. بعد در سلول D10 از فرمول Match(B10,B3:G3,0) استفاده کردم تا مشخص کنم کدام ستون در محدوده فروشها شامل فروش ماه ژوئن است. حالا که اعداد ردیف و ستونهایی را که شامل فروش عروسکهای دلخواهم بود را به دست آورده بودم میتوانستم در سلول E10 از فرمول
INDEX (Sales, C10,D10) استفاده کنم تا داده مربوط به فروش مورد احتیاج را به دست آورم. برای اطلاعات بیشتر درباره تابع INDEX میتوانید فصل چهارم، تابع INDEX را مطالعه کنید.
اگر بخواهیم نتایج مورد نظر را با تنها یک فرمول به دست آوریم، میتوانستیم از این فرمول استفاده نماییم:
INDEX(Sales,MATCH(A10,A4:A7,0),MATCH(B10,B3:G3,0))
- لیست دستمزد پرداختی بازیکنان تیم بیسبال داده شده ، چگونه میتوانم فرمولی بنویسم که نام بازیکن گیرنده بالاترین دستمزد و بازیکنی که در رده پنجم بیشترین دستمزد گیرندگان قرار گرفته است را مشخص کند؟
فایل Baseball.xlsx (تصویر 3-5) فهرستی از دستمزدهای پرداخت شده به 401 بازیکن تیمهای اصلی بسکتبال در فصل ورزشی سال 2001 را نشان میدهد. دادهها بر اساس میزان دستمزد مرتب نشدهاند و ما میخواهیم فرمولی بنویسیم که نام یک بازیکن با بیشترین دستمزد را به همراه نام بازیکنی که از نظر دستمزد در رده پنجم قرار گرفته ، به ما نشان دهد.
تصویر 3-5 این مثال استفاده از توابع MAX، Match و VLOOKUPرا برای یافتن و نمایش بالاترین مقدار در یک فهرست نشان میدهد.
برای یافتن نام بازیکنی با بالاترین دستمزد به شکل زیر عمل کنید:
- از تابعMax برای مشخص کردن بیشترین دستمزد استفاده کنید.
- از تابعMatch برای مشخص کردن ردیفی که شامل بازیکن هایی با بیشترین دستمزد میباشد استفاده کنید.
- از تابع VLOOKUP (استفاده از ردیف دادههای دستمزد بازیکنان) استفاده کنید تا نام بازیکن را جستجو نمایید.
محدوده C12:C412 را salaries (دستمزد) نام گذاشتم چرا که حاوی مقادیر دستمزد بازیکنان است و محدوده مورداستفاده در تابع VLOOKUP (محدوده A12:C412) را lookup (جستجو) نامگذاری کردم.
در سلول C9 بالاترین دستمزد بازیکنان (22 میلیون دلار) را با فرمول MAX(salaries) پیدا کردم. سپس در سلول C8 از فرمول MATCH(C9.salaries,0) استفاده کردم تا “شماره بازیکن” مربوط به بازیکنی که بیشترین دستمزد دریافت میکند را مشخص کنم. من از match type=0 استفاده کردم چرا که دستمزدها بهصورت صعودی و نزولی مرتب نشده بودند. بازیکن شماره 345 بیشترین دستمزد را دریافت میکند. بالاخره در سلول C6 از تابع
VLOOKUP (C8,lookup,2) استفاده کردم تا نام بازیگر را در دومین ستون محدوده جستجو پیدا کنم. بدون هیچ تعجبی میبینیم که به الکس رودریگز در سال 2001 بیشترین دستمزد پرداخت شده.
برای پیداکردن نام بازیکنی که در رده پنجم بالاترین دریافتکنندگان دستمزد قرار دارد، نیازمند تابعی هستید که پنجمین عدد بزرگ در آرایه دستمزدها را به شما نشان دهد. تابع LARGE این کار را برای شما انجام میدهد. دستور زبان تابع LARGE عبارت است از
LARGE(cell range,k) وقتی تابعLARGE به این شکل درج میشود، k امین عدد بزرگ در محدوده سلول را نشان میدهد؛ بنابراین فرمولLARGE(salaries,5) در سلول D9 پنجمین دستمزد بالا را نشان میدهد (12.6 میلیون دلار) وقتی به روش قبلی عمل کنیم ، درمی یابیم که درک جتر بازیکنی است که پنجمین دستمزد بالا را می گیرد. تابع SMALL(salaries.5) پنجمین دستمزد پایین را به ما نشان خواهد داد.
با درنظرگرفتن جریان نقدی یک پروژه سرمایهگذاری، چگونه میتوانم فرمولی بنویسم که تعداد سالهای موردنیاز برای بازپرداخت هزینه سرمایهگذاری اولیه پروژه را نشان دهد؟
فایل موردنظر به نام Payback.xlsx که در تصویر 4-5 نشاندادهشده، جریان نقدی تولید شده برای پروژه سرمایهگذاری را در طی 15 سال به تصویر کشیده است. فرض میکنیم که پروژه در سال اول نیازمند جریان خروج وجه نقد برابر با 100 میلیون دلار باشد. پروژه در سال اول جریان ورود وجه نقد برابر با 14 میلیون دلار تولید کرده است. انتظار داریم که جریان ورود وجه نقد هرسال ده درصد افزایش داشته باشد. چند سال خواهد گذشت تا پروژه بتواند سرمایهگذاری خود را بازیافت نماید؟
عدد سالهای موردنیاز برای بازپرداخت سرمایهگذاری پروژه، دوره بازگشت سرمایه (Payback Period) نامیده میشود. در صنایع با فناوری پیشرفته، دوره بازپرداخت اغلب برای رتبهبندی سرمایه گذاری استفاده میشود. شما در فصل هشتم” ارزشیابی سرمایه با استفاده از معیار ارزش خالص فعلی” یاد میگیرید که بازپرداخت بهعنوان یک معیار اندازهگیری کیفیت سرمایه گذاری دارای نقص میباشد چرا که ارزش پول در طول زمان را نادیده میگیرد. در حال حاضر اجازه دهید که بروی مشخصکردن دوره بازگشت سرمایه در مدل ساده سرمایهگذاریمان تمرکز کنیم.
تصویر 4-5. استفاده از تابعMATCH برای محاسبه دوره بازگشت سرمایه
برای مشخصکردن دوره بازگشت سرمایه پروژه اعمال زیر را انجام دهید:
- در ستونB جریان نقدی هر سال را محاسبه کنید.
- در ستون C جریان نقدی انباشته (تجمعی) هرسال را محاسبه کنید.
حالا میتوانید از تابعMATCH (با match type=1) برای مشخص کردن عدد ستون سال اولی که جریان نقدی تجمعی مثبت بوده استفاده کنید. این محاسبه به شما دوره بازپرداخت را میدهد.
من به سلولهای B1:B3 نامهای فهرست شده درA1:A3 را نسبت داده ام. سال صفر جریان نقدی (Initial_investment_) درسلول B5 وارد شده است. سال اول جریان نقدی (year_1_cf) درسلول B6 وارد شده است. فرمول کپی شده از سلولB7 به سلولهای B8:B20 یعنی B6*(1+Growth) جریان نقدی را از سال دوم تا سال پانزدهم محاسبه می نماید.
برای محاسبه جریان نقدی تجمعی سال صفر، از فرمول سلول B5 در سلول C5 استفاده کردم. برای سالهای بعدی شما میتوانید جریان نقدی تجمعی را با استفاده از فرمولی مثل:
Year t cumulative cash flow= Year t-1 cumulative cash flow+Year t cash flow
برای اجرای این رابطه کافی است فرمول=C5+B6 را از سلول C6 به سلولهای C7:C20کپی نمائید.
برای محاسبه دوره بازگشت سرمایه ، از تابعMATCH استفاده کنید (با match type=1) تا مکان آخرین ردیف از محدوده C5:C20 که دارای مقداری کمتر از صفر باشد را محاسبه کنید. این محاسبه همیشه به شما دوره بازپرداخت را ارائه خواهد کرد. مثلا اگر آخرین ردیف در محدودهC5:C20 که دارای ارزشی کمتر از صفر است ششمین ردیف باشد، این بدان معنا است که هفتمین مقدار، جریان نقدی تجمعی برای اولین سال پروژه که پرداخت شده را نشان میدهد. از آنجا که اولین سال ما سال صفر است، بازپرداخت در طول سال 6 انجام میشود. بنابراین فرمول موجود در سلول E2 یعنی:MATCH(0,C5:C20,1) دوره بازپرداخت (شش ساله) را به ما میدهد. اگر هریک از جریان نقدی بعد از سال صفر منفی باشد این روش اشتباه بوده و پیغام خطا صادر میشود چرا که محدوده جریان نقدی تجمعی نمی تواند به شکل نزولی فهرست شود. استفاده از تابعIFERROR (در فصل دوازده : تابع های If، IFERROR، IFS ،CHOOSE و SWITCH مورد بحث قرار گرفته) می توانید اطمینان حاصل کنید که وقتی هیچ بازپرداختی وجود نداشته باشد، سلول E2 مقداری نوشتاری (مثلا “بازپرداختی موجود نیست”) را نشان میدهد.
مسائل
با استفاده از فاصله بین شهرهای ایالات متحده امریکا که در فایلی بنام Index.xlsx ارائه شدهاند با بهکارگیری تابع MATCH فرمولی بنویسید که فاصله میان هریک از دو شهر را (بر اساس نام شهرها)مشخص کند.
در فایلی بنام Matchtype1.xlsx فهرستی مرتب شده بر اساس زمان از مقادیر نقدی 30 مبادله مالی ارائه شده است. فرمولی بنویسید که اولین مبادله مالی را که مقدار کل آن تا به حال بیش از 10000 دلار باشد را به دست آورد.
فایلی بنام Matchthemax.xlsx کدهای شناسایی محصولات و میزان فروش 265 محصول را نشان داده است. از تابع MATCH در فرمولی استفاده کنید تا بتوانید کد شناسایی محصولی را که بیشترین فروش را داشته است پیدا کنید.
فایلی بنامBuslist.xlsx زمان بین رسیدن اتوبوس (به دقیقه) در خیابان چهل و پنجم و خیابان پارک در شهر نیویورک را نشان داده است. فرمولی بنویسید که در هر بار زمان رسیدن شما بعد از اتوبوس اول، میزان زمانی را که میبایست منتظر اتوبوس بعدی شوید را محاسبه کند. مثلاً اگر شما از 12.4 دقیقه بعد از الان به آنجا میرسید و اتوبوس پنج دقیقه و 21 دقیقه از الان به آنجا میرسد، شما برای اتوبوس 21-12.4=8.6 دقیقه صبر میکنید.
فایلی بنامSalesdata.xlsx شامل تعداد کامپیوترهای یک مغازه است که توسط هریک از فروشندهها به فروش رسیدهاند. فرمولی خلق کنید که تعداد واحدهای فروخته شده توسط یک فروشنده خاص را نشان دهد.
فرض کنید که تابع VLOOKUP از نرمافزار اکسل حذف شده است. توضیح دهید چگونه هنوز قادر خواهید بود که با استفاده از توابع MATCH و INDEX محاسبات کاربردی خود را انجام دهید.
در فایلی بنام Baseballproblem7.xlsx به شما آماری درباره تیمهایی از لیگ اصلی ارائه شده است. شما نام تیم را در سلول I2 و آمار را سلول J2 وارد می نمائید. فرمولی در سلول K2 بنویسید که مقدار هریک از داده های آماری برای تیم منتخب را بدست آورد.
در فایلی بنامFootballProblem8.xlsx آماری درباره بازیکنان خط حمله لیگ ملی فوتبال نشان داده شده است. فرض کنید ما نام بازیکن خط حمله ای را در سلولG3 وارده کرده و آماری را در سلول H3 وارد می نمائیم. فرمولی بنویسید که در سلولI3 آمار بازیکن خط حمله را بدست آورده و نمایش دهد.