توابع IF ، IFERROR، IFS، CHOOSE و SWITCH – بخش 3

آخرین به‌روزرسانی: 27 دی 1401

در فصل گذشته به بررسی ارجاع‌های چرخشی (Circular-refrence) پرداختیم, در این فصل به بررسی توابع IF ، IFERROR ،IFS ،CHOOSE و SWITCH – بخش 3 می پردازیم.

توابع IF ، IFERROR، IFS، CHOOSE و SWITCH

کاربرگ من حاوی درآمدهای فصلی فروشگاه والمارت می‌باشد. آیا به‌راحتی می‌توانم درآمدهای هرسال را محاسبه کنم و آنها را در ردیفی که حاوی فروش اولین فصل آن سال است قرار دهم؟

فایلی بنام Walmartrev.xlsx حاوی درآمدهای فصلی (به میلیون دلار) فروشگاه والمارت می‌باشد (تصویر 10-12 را ببینید). ردیف‌های 6، 10 ، 14 و غیره حاوی درآمدهای اولین فصل از هرسال می‌باشند. می‌خواهم در هریک از این ردیف‌ها، کل درآمد سال در ستون E را محاسبه کنم. در سایر ردیف‌ها ستون E می‌بایست خالی باشد. برای انجام این کار می‌توان در سلول E6 فرمول =SUM(D6:D9) را وارد کرد و این فرمول را در سلول‌های E10 سپس E14 سپسE18 و غیره کپی کرد. اما می‌بایست راه بهتری هم وجود داشته باشد. استفاده از عبارت شرطیIF با دو تابع کارآمد اکسل (ROW وMOD) راه آسان‌تری برای تنها یک‌بار واردکردن فرمول مورد نظر و کپی‌کردن آن در اختیار قرار می‌دهد. تابع Row(Cell refrence) مقدار ردیف را برمی‌گرداند. تابع =ROW(A6) عدد 6 را برمی‌گرداند، اگر شما در ردیف 6 باشید تابع =ROW() نیز یک مقدار 6 را برمی‌گرداند. تابعMOD(number.divisor) وقتی‌که عدد بر مقسوم علیه تقسیم شده باشد باقیمانده را به ما برمی‌گرداند. مثلاً MOD(9.4) به ما عدد یک را برمی‌گرداند درحالی‌که MOD(6,3) عدد صفر را برمی‌گرداند. توجه کنید که می‌خواهیم فرمول تنها در ردیف‌هایی کار کند که وقتی مقدار آنها بر 4 تقسیم شود عدد 2 را برگرداند؛ بنابراین کپی‌کردن فرمول  =IF(MOD(ROW() ,4)= 2,SUM(D6:D9),” “) از سلول E6  به سلول‌های E7:E57 به ما اطمینان می‌دهد که درآمدهای سال جاری تنها از ردیف‌هایی که وقتی بر چهار تقسیم شوند باقیمانده 2 خواهند داشت جمع زده شده‌اند. این بدان معناست که درآمدهای سالیانه را تنها از اولین فصل هرسال محاسبه کرده ایم که هدف ما هم همین بوده است .

تصویر 10-12 خلاصه کردن درآمدهای سالیانه فروشگاه والمارت

  ممکن است عبارت‌های شرطی IF در اکسل بسیار طولانی شوند. چند عبارت شرطی IF‌را می‌توان در یک سلول قرار داد؟ حداکثر تعداد کاراکترهای مجاز در یک فرمول اکسل چند عدد می‌باشد.

حداکثر چند کاراکتر را می توان در یک فرمول اکسل گنجاند؟ در اکسل 2016 و 2019 دریک سلول می توانید از 64 دستور IF تو در تو استفاده کنید. در نسخه های قبلی اکسل تنها می توانستید از 7 دستور  IF تودرتو در یک سلول استفاده کنید. در اکسل 2010 ( و اکسل 2007) یک سلول می تواند 32000 کاراکتر را در خود بگنجاند.

عبارت‌های شرطی تو در توی IF بسیار طولانی و پیچیده هستند. چگونه تابع جدید IFS به ما راهی ساده‌تر برای نوشتن عبارت‌های شرطیIF ارائه می‌کند؟

نوشتن عبارت‌های تودرتویIF کار پردردسری است چراکه اغلب باید عبارتIF را چندین بار تایپ کرد و یا نگران تعداد دفعات استفاده از پرانتز های بسته بود. خوشبختانه اگر دارای  برنامه Office365 و یا Excell2019 باشید، می‌توانید از تابع فوق العاده IFS استفاده کنید که کار نوشتن عبارت‌های شرطی IF را بسیار ساده کرده است. فایلی به نامIFSfinal.xlsx( تصویر 11-12  و 12-12 را مشاهده کنید) که نحوه استفاده از تابعIFS را به نمایش گذاشته است. ُتابع IFS اساساً به شما اجازه می‌دهد که از تکرار نوشتنIF درون فرمول پرهیز کرده و فرمول را تنها با یک جفت پرانتز( باز و بسته) کامل کنید.

تصویر 11-12   استفاده از تابعIFS برای مشخص کردن نتیجه بازی کراپز پس از اولین پرتاب تاس ها

 از تابع IFS  برای مشخص‌کردن خروجی بازی کراپز بعد از اولین پرتاب تاس استفاده می‌شود. توجه کنید که پیش از آنکه تمام احتمالات را داشته باشیم به عبارتTrue نیازمندیم. در سلول E15 نتیجه بازی را پس از اولین پرتاب با واردکردن فرمول زیر مشخص کرده ایم.

IFS(OR(D15=2,D15=3,D15=12),”LOSE”,OR(D15=7,D15=11),”WIN”,TRUE,”keep going”)= چنانچه رقم پرتاب 3،2 و یا 12 باشد نتیجه باخت (Lose) می‌شود؛ اگر تاس رقم 7 یا 11 بیاورد نتیجه بازی برد (Win) است، در غیر این صورت عبارت True آخرین خروجی فهرست شده را احضار خواهد: ادامه بدهید (Keep going). توجه کنید که فرمول فوق‌الذکر تنها نیازمند یک‌بار استفاده از عبارتIF و یک پرانتز بسته بود.

تصویر 12-12 نشان می‌دهد چگونه استفاده از تابعIFS اولین مثال عبارت شرطی IF بررسی شده در این فصل را چقدر ساده کرده است. در اینجا فرمول IFS  جهت مشخص کردن قیمت واحد هریک از کالاها از سلول D9 به محدوده D10:D12 کپی شده است.   IFS(A9<=_cut1,price1,A9<=_cut2,price2,A9<=_cut3,price3,TRUE,price4)=.

اگر مقدار درخواست کمتر یا برابر 500 باشد آن‌وقت قیمت واحد 3 دلار به ما داده می‌شود، اگر حجم درخواست بین 501 و 1200 باشد آن‌وقت قیمت واحد 2.70 دلار به ما داده می‌شود. اگر حجم در خواست بین 1201 و 2000 باشد آن‌وقت قیمت واحد 2.30 دلار به ما داده می‌شود. در غیر این صورت (به‌خاطر کلمه True) قیمت واحد 2 دلار به ما داده می‌شود. بازهم توجه کنید که ما تنها یک‌بار کلمه IF را تایپ کردیم و تنها به یک پرانتز بسته نیاز داشتیم. همچنین توجه کنید که فرمول قدیمی IF چنانچه هیچ‌کدام از شرایط موجود True نباشند به طور خودکار نتیجه پایانی را برمی‌گرداند، در مورد فرمولIFS می‌بایست عبارت TRUE را تایپ کنید تا کاری کنید که عبارت شرطیIFS آخرین نتیجه موجود در لیست را باز گرداند.

تصویر 12-12  استفاده از فرمول IFS برای مشخص‌کردن قیمت واحد

 تابعCHOOSE چگونه عمل می‌کند؟

تابعCHOOSE مقداری را از لیست مقادیر بر اساس یک موقعیت مکانی خاص بر می گرداند. دستور زبان تابع CHOOSE عبارت است از: CHOOSE(index_num, value1, [value2] , …) در اینجا مقادیرمی توانند محدوده‌ها هم باشند. اگر آرگومان index برابر با n باشد آن‌وقت مقدار n انتخاب می‌شود. فایلی بنام   Choosefinal.xlsx (تصویر 13-12 تا 15-12 را ببینید) حاوی سه مثال از تابع CHOOSE می‌باشد.

تصویر 13-12 استفاده از تابعCHOOSE برای شمارش کارتهای ورق بازی بلک جک

ریاضی‌دان بزرگ ادوارد تورپ شمارش کارت برای بازی ورق بلک جک را اختراع کرد. وقتی‌که کارت‌های دو تا هفت از دسته کارت بیرون کشیده می‌شوند، این به نفع بازی‌کننده است و او این کارت‌ها را به‌عنوان 1+ شمارش می‌کند. وقتی کارت‌های 9، 10 یا اینکه تک خال (که به شماره 1 نام‌گذاری شده) از دسته کارت‌ها بیرون می‌آیند، این به ضرر بازی‌کننده است و به‌عنوان 1- شمرده می‌شود. کارت شماره 8 خنثی است و به‌عنوان رقم صفر شمرده می‌شود. در کاربرگی بنام Blackjack فایلی بنامChoosefinal.xlsx وجود دارد که به کمک آن نشان‌داده‌شده که چگونه می‌توان به هنگام کشیدن کارت از دسته کارت‌ها از تابعCHOOSE برای کمک به محاسبه کردن کل امتیازات استفاده کرد. ستون G فهرست کارت‌های بیرون کشیده شده از دسته کارت را نشان می‌دهد. سپس فرمول =CHOOSE(G3,–1,1,1,1,1,1,1,0,–1,–1)  از سلولH3 کپی می‌شود که فهرستی از مقدار امتیازهای کارت‌های حاضر را به ما نشان می‌دهد. همان‌طور که گفتیم کشیدن کارت‌هایی 2 تا 7 امتیاز 1 را به ما می‌دهد، کشیدن کارت‌های 1، 9 و 10 امتیاز 1- را به ما می‌دهد و کشیدن کارت‌های 8 امتیاز صفر را نصیب می‌کند. ستون I  جمع امتیازات نهایی را به ما نشان می‌دهد و بعد از کشیدن هفت کارت جمع کل آنها 3+ می‌شود که به طور نسبی برای بازی‌کننده امتیاز خوبی است.

کاربرگی بنام Quarters حاوی دو مثال دیگر از عملکرد تابعCHOOSE می‌باشد( تصاویر 14-12 و 15-12  را مشاهده کنید) فرض کنید که ماه اول تا سوم از سال فصل دوم، ماه های چهارم تا ششم فصل سوم ، ماه های هفتم تا نهم فصل چهارم و ماه های دهم تا دوازدهم فصل یکم می‌باشند. فرمول=CHOOSE(H5,2,2,2,3,3,3,4,4,4,1,1,1)  بر اساس ماه های سلول H5 در سلول I5 عدد سه ماهه مربوطه را باز می گرداند. البته ما می‌توانیم همان نتیجه را با فرمول =VLOOKUP(H5,D4:E15,2) نیز به دست آوریم.

تصویر 14-12  استفاده از تابعCHOOSE برای مشخص کردن فصل از سال

 در سلول M2 از کاربرگی بنام Quarters (تصویر 15-12 را ببینید) عدد فصل از سال را وارد کرده ایم و در سلولN2 فروش کل فصل مورد نظر را می‌خواهیم. می‌توان با واردکردن فرمولSUM(CHOOSE(M2,M6:M8,N6:N8,O6:O8,P6:P8))  در سلول N2 به این هدف دستیابی پیدا کرد.

مثلاً وقتی‌که سلول N2 حاوی رقم 3 است، تابعCHOOSE محدودهO6:O8 را نتخاب می‌کند. سپس تابعSUM ارقام موجود در این محدوده را جمع زده عدد 120  را به ما می‌دهد.

تصویر 15-12 استفاده از تابع CHOOSE برای مشخص‌کردن فروش کل فصل موردنظر

تابع جدید SWITCH چگونه عمل می‌کند؟

اگر نرم‌افزار اکسل 2019 و یا Office 365 داشته باشید می‌توانید ازتابعSWITCH استفاده کنید. تابعSwitch  مقداری ( که عبارت نامیده می‌شود)  را در برابر فهرستی از مقادیر ارزشیابی کرده و اولین مقدار برابر را به‌عنوان نتیجه بر می گرداند. اگر مقدار برابری وجود نداشته باشد  مقدار از پیش تعیین شده انتخاب می‌شود. تابعSWITCH می‌تواند تا 126 مقدار و نتیجه مطابق را ارزشیابی کند. فایلی بنامSwitchfinal.xlsx ( تصویر 16-12 را ببینید) نحوه استفاده از تابعSWITCH را نشان می‌دهد. ستون G بر اساس کد های پیراهن در ستون D،حاوی شماره سایز های پیراهن می‌باشد. می خواهیم کدهای اندازه را بر اساس کد هر پیرهن به نام‌های در ستون B تبدیل کنیم. کپی‌کردن فرمول زیر از ستون H4 به محدوده H5:H27 کد پیرهن ها را به نام‌های مرتبط تبدیل می‌کند.

SWITCH(G4,”XXL”,Really Big”,”Extra Large”,”L”,”Large”,”M”,”Medium” ,”S”,”Small”,”XS”,”Extra Small”,”XXS”,”Code Is Wrong”)=

توجه کنید که اگر ستونG  دارای کد پیراهن قابل تشخیصی نباشد ، تابع SWITCH پیام ”CODE IS WRONG”( کد اشتباه است) را  در سلول H27 نشان می‌دهد.( توجه کنید که سلول H27  در تصویر 16-12 نشان‌داده‌شده است.)

تصویر 16-12 از تابعSWITCH‌ برای تبدیل کد پیراهن ها به اندازه واقعی آنها استفاده می‌شود.

 مسئله‌های این فصل

فرض کنید قیمت کالایی در آینده در تاریخ‌هایی به شرح زیر تغییر پیدا کرده است:

فرمولی بنویسید که قیمت محصولات را بر اساس تاریخ فروش آن محصول محاسبه نماید.

1. پرواز خط بلو یاندر از سیاتل به نیویورک گنجایش 250 مسافر را دارد. این خط 270 بلیط برای پرواز به قیمت هر بلیط 300 دلار فروخته است. بلیط ها پس گرفتنی نیستند. هزینه متغیر پرواز یک مسافر (که بیشتر هزینه غذا و سوخت است) برای هر نفر 30 دلار می‌باشد. اگر بیش از 250 نفر برای پرواز حاضر شوند، پرواز اضافه مسافر پیدا کرده و خطوط بلو یاندر می‌بایست به هر مسافر اضافه 350 دلار غرامت بپردازند. کاربرگی درست کنید که سود شرکت خطوط هوایی بلو یاندر را بر اساس تعداد مسافرانی که برای پرواز حاضر می‌شوند محاسبه کند.
2. یک شرکت دارویی بزرگ سعی بر مشخص‌کردن حجم محصول مناسب برای ساخت کارخانه ای برای تولیددارویی جدید دارد. یک واحد حجم سالیانه برای تولید دارو با هزینه 10 دلار ساخته  می‌شود. هر واحد از دارو به قیمت 12 دلار فروخته می‌شود و هزینه متغیر 2 دلاری متحمل می‌شود. دارو قرار است به مدت ده سال فروش رود. کاربرگی ایجاد کنید که سود ده‌ساله شرکت باتوجه‌به سطح حجم تولید سالیانه و درخواست سالیانه برای دارو را محاسبه کند. فرض کنید درخواست دارو در هرسال یکسان است. می‌توانید ارزش زمانی پول را در این مسئله نادیده بگیرید.

3. همان شرکت دارویی، دارویی جدید تولید کرده است. این شرکت فر‌ضیه های زیر را در نظر می گیرد:
4. در طول سال اول 100000 واحد از دارو فروخته خواهد شد.
5. فروش در طول سه سال اول افزایش خواهد داشت و در هفت سال بعدی کاهش خواهد داشت
6. در طول دوره افزایش فروش، مقدار فروش 15 درصد در هرسال افزایش خواهد داشت. در طول دوره کاهش فروش، مقدار آن در هرسال 10 درصد کاهش خواهد داشت. کاربرگی تهیه کنید که ارزش فروش سال اول، طول زمان دوره افزایش، طول زمان دوره کاهش، نرخ رشد در طول دوره رشد و نرخ کاهش دوره کاهش را به دست آورده و سپس تعداد واحدهای فروخته شده در سال‌های 1 تا 11 را محاسبه نماید.

5. در مناقصه‌ای مربوط به یک پروژه ساختمانی شرکت کرده‌اید. کمترین پیشنهاد برنده پروژه می‌باشد. حدس زده‌اید که هزینه پروژه 10000 دلار است. چهار شرکت در مقابل شما پیشنهاد مناقصه می‌دهند. آماده‌کردن پیشنهاد مناقصه 400 دلار هزینه دارد. فرمولی بنویسید که (باتوجه‌به پیشنهادهای هر چهار شرکت و پیشنهاد شما) سود شما را (یا ضرر، چنانچه در مناقصه پیروز نشوید) را محاسبه کند.
6. در مزایده نقاشی گران قیمتی شرکت کرده ایم. بیشترین پیشنهاد برنده نقاشی خواهد بود. تخمین زده‌ایم که ارزش نقاشی 10000 دلار می‌باشد. چهار شرکت در مقابل ما پیشنهاد مزایده ارائه می‌دهند. هزینه آماده‌کردن پیشنهاد مزایده 400 دلار است. فرمولی بنویسید که (باتوجه‌به پیشنهادهای هر چهار شرکت و پیشنهاد شما) مشخص کند که آیا برنده نقاشی هستیم یا نه.
7. یک شرکت داروسازی‌ اعتقاد دارد که از دارویی جدید در سال 2004 ، 10000 واحد خواهد فروخت. آنها انتظار دارند دو شرکت رقیب هم وارد بازار فروش این دارو شوند. شرکت انتظار دارد در سالی که اولین رقیب وارد بازار می‌شود 30 درصد از سهم بازار را از دست بدهد. این شرکت همچنین انتظار دارد در سالی که رقیب دوم وارد بازار می‌شود 15 درصد از سهم بازار را از دست بدهد. حجم بازار هرسال 10 درصد افزایش پیدا می‌کند. باتوجه‌به ارقامی که در مورد ورود دو شرکت رقیب داده شد، کاربرگی تهیه کنید که فروش سالیانه در طول سال‌های 2004 تا 2013 این شرکت را محاسبه نماید.
8. یک مغازه لباس‌فروشی 100000 مایوی شنا سفارش داده است. هر مایوی شنا 22 دلار هزینه تولید دارد. این مغازه می‌خواهد آنها را تا 31 آگوست به مبلغ 40 دلار بفروشد و بعد قیمت را تا 30 دلار پایین آورد. باتوجه‌به قیمت درخواستی این مایوها در 31 آگوست و پس از 31 آگوست، کاربرگی ایجاد کنید که سود مغازه از این سفارش را محاسبه نماید.
9. دربازی کراپز پس از تاس ریختن اولیه قوانین به این شکل تغییر می‌کنند: اگر بازی تمام نشده باشد و مقادیر ریختن تاس برابر با مقادیر اولیه باشند شما برنده بازی خواهید بود. اگر بازی تمام نشده باشد و عدد هفت آورده باشید آن‌وقت باخته‌اید. در غیر این صورت بازی ادامه پیدا می‌کند. کاربرگی تهیه کنید که به شما بگوید (باتوجه‌به معلوماتی که از اولین چهار پرتاب اولیه دارید) که وضعیت بازی پس از چهار بار تاس ریختن چه خواهد بود؟
10. در مثال میانگین متغیر اس‌اندپی، فرض کنید که چنانچه ارزش جاری از میانگین متغیر 15 ماهه بیشتر شود هنوز سهام خریداری می‌کنیم اما ما اگر ارزش جاری کمتر از میانگین متغیر 5 ماهه باشد سهام را می‌فروشیم. آیا چنین استراتژی از فروختن به هنگامی‌که ارزش جاری کمتر از 15 ماه میانگین متغیر باشد سودآورتر نیست؟
11. برطبق یک قرارداد اختیار معامله اروپایی حق خرید سهمی از سهام به قیمت توافقی در زمانی خاص در آینده به شما داده شده است. تکنیک انتشار پروانه (Butterfly Spread) شامل خریدن یک قرارداد اختیار با قیمت توافقی پایین، خریدن یک قرارداد اختیار با قیمت توافقی بالا و فروش این دو قرارداد اختیار با قیمت توافقی میانه بین قیمت‌های توافقی بالا و پایین می‌باشد. در اینجا مثالی از تکنیک انتشار پروانه (بال‌های پروانه) را بررسی می‌کنیم: قیمت سهام جاری 60 دلار می‌باشد. شما سهام شش‌ماهه قرارداد اختیار خرید اروپایی 54 دلاری را به 9 دلار می‌خرید، سپس یک سهم شش‌ماهه قرارداد اختیار خرید اروپایی 66 دلاری را به 4 دلار خریداری می‌کنید و هر دو سهام قرارداد اختیار اروپایی 60 دلاری را به 6 دلار می‌فروشید. سود این مبادله را (به دلار و نه به درصد) به‌عنوان تابعی از ارزش سهام شش ماه که مابین 40 تا 80 دلار می‌ارزد محاسبه کنید. وقتی‌که تاجری با استفاده از تکنیک پروانه گسترده خریداری می‌کند، چه نوع تغییری در ارزش سهام در طی شش ماه آینده را پیش‌بینی می‌کند؟
12. فرض کنید سهامی در حال حاضر به قیمت 32 دلار به فروش می‌رسد. یک قرارداد اختیار فروش اروپایی شش‌ماهه‌ 30 دلاری را به قیمت 2.50 دلار و یک قرارداد اختیار فروش اروپایی شش‌ماهه 35 دلاری را به قیمت 1 دلار می‌خرید. سود این استراتژی را (به دلار) به‌عنوان تابعی از ارزش سهام شش‌ماهه‌ای که از 25 دلار تا 45 دلار متغیر است محاسبه کنید. چرا این استراتژی انتشار گاو نر (Bull Spread) نامیده می‌شود؟ این استراتژی را چگونه تغییر می‌دهید تا به استراتژی انتشار خرس (Bear Spread) مبدل شود؟
13. بیایید مثال مربوط به پیش بینی صورتهای مالی را دوباره بررسی کنیم. فرض کنید که نرخ بهره دیون ما بستگی به بهبود وضعیت مالی دارد. اگر بخواهیم دقیق‌تر بگوییم، فرض کنید که چنانچه سود پیش از بهره و مالیات منفی باشد، نرخ بهره دیون 16 درصد می‌شود. اگر هزینه بهره بیش از 10 درصد از سود پیش از بهره و مالیات باشد و آن هم مثبت باشد، نرخ بهره دیون 13 درصد می‌شود. در غیر این صورت نرخ بهره 10 درصد می‌شود. پیش بینی صورتهای مالی مربوط به این نرخ بهره متغیر را تنظیم کنید.
14. این مسئله را جدا از مسئله شماره 13 انجام دهید. فرض کنید شرکتی نسبت بدهی به حقوق صاحبان سهام 50 درصدی برای هرسال در نظر گرفته است. چگونه برای این شرکت پیش بینی صورتهای مالی تنظیم می‌کنید؟ توجه کنید که می‌بایست سهام هرسال را غیر منفی نگه دارید و از سهام و اوراق بهادار قابل‌عرضه در بازار یا وجه نقد برای تراز دارایی‌ها و دیون استفاده کنید.
15. روز مارتین لوترکینگ سومین دوشنبه در ماه ژانویه است. فرمولی بنویسید که (در سالی مشخص) تاریخ روز مارتین لوترکینگ را محاسبه کند. توجه: ابتدا ببینید اول ژانویه آن سال چه روزی از هفته است .
16. روز شکرگزاری چهارمین پنج‌شنبه در ماه نوامبر می‌باشد. فرمولی بنویسید که (در سالی خاص) تاریخ روز شکرگزاری را محاسبه کند. توجه: ابتدا ببینید اول نوامبر آن سال چه روزی از هفته است.
17. اولین فصل سال ژانویه تا مارس، دومین فصل سال آوریل تا ژوئن، سومین فصل سال ژوئیه تا سپتامبر و چهارمین فصل سال اکتبر تا دسامبر است. فرمولی بنویسید که (در هر تاریخی) فصل آن را نشان دهد.
18. فرمولی بنویسید که سن شخصی را باتوجه‌به تاریخ تولدش نشان دهد.
19. روز کارگر اولین دوشنبه ماه سپتامبر است. فرمولی بنویسید که تاریخ روز کارگر یک سال خاص را مشخص کند.
20. فایلی بنام Nancybonds.xlsx به شما رتبه‌بندی چندین اوراق قرضه در ماه گذشته و آینده را نشان می‌دهد. می‌خواهید بدانید که چند اوراق قرضه دارای اعتبار پایینی هستند. متأسفانه هر شرکتی در بیش از یک ردیف فهرست شده است. فرض کنید که داده‌ها را بر اساس نام شرکت مرتب کرده‌اید، چگونه می‌توانید تعداد اوراق قرضه های کم اعتبار را مشخص کنید؟
21. در فایلی به نام Address.xlsx نام‌های اشخاص در یک خط، آدرس خیابان آن‌ها در خط بعدی و شهر، ایالت و کد پستی در خط‌های بعدی درج شده است. چگونه می‌توانید اطلاعات هر شخص را به یک خط منتقل کنید؟
22. فایلی بنام FormattingDDAnum.xlsx حاوی رشته‌های متنی از قبیلDDA:D در سلول C4، DDA1250045 در سلولC17 و غیره می‌باشد. سلولی که اولین کاراکترهای موجود در آن DDA و هفت رقم آخر عدد یک میلیون یا بزرگ‌تر از آن باشد( بدون وجود Space میان آنها) درست فرمت شده است. مشخص کنید کدام سلول ها درست فرمت شده اند.
23. فرض کنید که تعداد اعضای گروه یک در سلول B1 ، تعداد اعضای گروه 2 در سلول B2 و تعداد اعضای گروه 3 در سلول B3 درج شده است. تعداد کل اعضای گروه 100 نفر می‌باشد. فرض کنید که گروه 1 پنجاه عضو ، گروه 2 سی عضو و گروه 3 بیست عضو دارد. یک رقم 1 برای هر 1 از اعضای گروه یک ، یک رقم 2 برای هریک از اعضای گروه 2 و یک رقم 3 برای هریک از اعضای گروه سوم در ستون D  قرار دهید؛ بنابراین ستون D می‌بایست (در مثال ما) در محدوده‌D1:D50 حاوی رقم 1، در محدوده D51:D80 حاوی رقم 2 و در محدوده D81:D100  حاوی رقم 3 باشد.
24. فایلی بنام Dividedbyprice.xlsx حاوی واحدهای فروخته شده از هر محصول و درآمد کل می‌باشد. میانگین قیمت برای هر محصول را به دست آورده و هر میانگین را در ستون H‌نمایش دهید. البته اگر تعداد واحدهای فروخته شده صفر باشد دیگر میانگین قیمتی وجود نخواهد داشت. فایلDividedbyprice را با تله گذاشتن برای خطاهای احتمالی ، خطا زدایی کنید تا مطمئن شوید تمامی محصولات با فروش صفر به جای پیغام خطای #DIV!0 پیام  No sales “فروش نرفته” را نشان دهند.
25. دانشگاه هنرهای زیبا دارای صد کمد جالباسی است که از یک تا صد شماره‌گذاری شده‌اند، درب تمامی کمدها باز است. شروع می‌کنیم و درب کمدهایی که شماره آنها بر سه قابل قسمت است را می‌بندیم. سپس این کار قبلی را برعکس می‌کنیم (یعنی دربهای بسته را باز می‌کنیم و دربهای باز را می‌بندیم) به شکلی که این بار درهای کمدهای را باز می‌کنیم که عدد آنها قابل تقسیم‌بر 4 است، بعد دوباره عمل جابه‌جایی درهای بسته و باز را انجام می‌دهیم و این بار درهای کمدهایی را باز می‌کنیم که عدد آنها بر 5 قابل تقسیم است. این کار را ادامه می‌دهیم و هر بار رقم تقسیم را بالا می‌بریم تا بالاخره درهای کمدهایی را باز کنیم که عدد آنها قابل تقسیم‌بر 100 باشد. اکنون در چند کمد باز است؟
26. فایلی بنام Matchlist.xlsx حاوی فهرستی از اشخاصی است که کالای شما را در فوریه و همچنین اشخاصی که کالای شما را در مارس خریده‌اند. مشخصی کنید چند نفر از مشتریان ماه فوریه محصول را در ماه مارس هم خریداری کرده‌اند.
27. کاربرگ تقویمی تنظیم کنید که ماه و سال خاصی را به‌صورت ورودی بپذیرد و بعد روزهای هفته را که هر روز از ماه در آن رخ می‌دهد ، به شما نشان دهد.
28. برخی ردیف‌های ستون C از فایلی به نام Problem28data.xlsx حاوی کلمهand می‌باشند. فرمولی در ستون D بنویسید که اگر و تنها اگر ستون C در ردیفی حاوی کلمه and‌باشد در همان ردیف در ستون D عبارت X  درج شود.
29. فرض کنید دو تاس 20 وجهی را پرتاب می‌کنید.400 نتیجه احتمالی عبارت خواهند بود ( 1.1)، (2.2)، و … (20.20). از عبارت شرطیIf استفاده کنید تا به شکلی سیستماتیک تمامی نتایج احتمالی را ایجاد کنید.
30. از داده‌های فایلی بنام Catsanddogs.xlsx استفاده کنید تا مشخص نمایید رشته متنی Csts چند بار و رشته متنی dog چند بار ظاهر شده‌اند.
31. فایلی بنام Footballdats.xlsx نتایج بردوباخت لیگ ملی فوتبال در طول فصل بازی در سال 2015 ارائه می‌دهد. فرمولی در سلول H13 بنویسید که بتونید آن را در محدوده H14:H31 کپی کرده تا درصد بردهای هر تیم را بدون هیچ پیغام خطایی به شما ارائه دهد.
32. در ابتدای سال 1 حق بیمه اتومبیل شما برای سال بعد 300 دلار می‌باشد. اگر در طول سال هیچ تصادفی نداشته باشید حق بیمه برای سال بعد ده دلار کمتر می‌شود، اگر یک تصادف کرده باشید حق بیمه سال بعد 10 درصد افزایش پیدا می‌کند و اگر در سال دو تصادف کرده باشید حق بیمه 30 درصد افزایش پیدا می‌کند کاربرگی ایجاد کنید که بتواند حق بیمه سال 1 الی 30 را محاسبه و نشان دهد.
33. فرض کنید فروش محصولات ما به شکلی که خواهید دید به قیمت من و قیمت رقیب بستگی دارد. اگر قیمتی که من ارائه می‌دهم حداقل سه دلار از قیمت رقیب بالاتر باشد، 500 واحد خواهیم فروخت، اگر قیمت من حداقل سه دلار از قیمت رقیب کمتر باشد 1500 واحد خواهیم فروخت، در غیر این صورت 1000 واحد خواهیم فروخت. فرض کنید همه قیمت‌ها مخصوصاً میان 1 و 10 دلار متغیر خواهند بود. فرمولی بنویسد که واحدهای فروخته شده را برای تمامی ترکیب‌های قیمتی محاسبه نماید.
34. قرار است در آمد سالیانه یک شرکت را بر اساس اطلاعات موجود در سلول‌های C1:D5 از فایلی بنام problem34dat.xlsx به دست آورید. قیمت واحد به نرخ مشخصی بالا می‌رود تا وقتی‌که رقیبی وارد شود، در آن صورت قیمت فروش واحدها ثابت باقی می‌ماند. قیمت محصول هرسال بر اساس نرخ تورم بالا می‌رود. فرمولی در سلول‌های زرد بنویسید که به شکلی صحیح قیمت، تعداد واحدهای فروخته شده و در آمد سال 1 تا 10 را محاسبه کند.
35. سلول D2 در فایلی بنام Problem35data.xlsx حجم بازار سال 1 را در اختیار شما قرار می‌دهد. سلول D3 سهم بازار (از همان سال) ، سلول D4 قیمت کالا (در همان سال) و سلول D5 حاشیه سود (همان سال) را در اختیار شما قرار می‌دهند. سلول D6 مقدار افزایش حجم بازار در هرسال را به شما می‌دهد و بالاخره سلول D7 تعداد سال‌هایی که بازار رشد داشته است را در اختیار شما قرار می‌دهد. فرمولی بنویسید که سود هرسال و  سود کل ده سال را محاسبه نماید.
36. روز ریاست‌جمهوری، سومین دوشنبه از ماه فوریه است. کاربرگی ایجاد کنید که کاربر بتواند در آن سالی را وارد کرده و تاریخ روز ریاست‌جمهوری آن سال به او داده شود.
37. می‌بایست فرمولی بنویسید که سود ناشی از فروش کارت‌تبریکی را به شما بدهد. اطلاعات موجود در سلول‌های B1:B7 از فایلی بنام Problem37data.xlsx به شما داده شده است. کارت‌ها را به قیمت 3.50 دلار سفارش داده‌ایم. کارت‌ها به قیمت معمول 6 دلار فروخته می‌شوند. کارت‌های فروخته شده به قیمت معمول در سلول B7 وارد خواهند شد. 40 کارت اولی که در ابتدا فروخته نشدند به قیمت 2 دلار فروخته شد. کارت‌های باقی‌مانده نیز به قیمت 0.50 دلار فروخته شد. فرمولی بنویسید که همواره سود درست هر دسته از ورودی‌های غیر منفی را به ما برگرداند.
38. ستون B از فایلی به نامProblem38data.xlsx حاوی نام فروشنده های در ردیف‌های فرد است که به دنبال آن واحدهای فروخته شده توسط هر فروشنده درست زیر نامشان درج شده است. فرمولی را استفاده کنید که در ردیف‌های فرد ستون C نام فرد را به همراه تعداد واحدهای فروخته شده ( که با یک ویرگول از هم جدا شده باشند) درج کند. توجه کنید تابعISEVEN می تواند مشخص کند که یک شماره زوج است و تابع ROW() تعداد سلول های فعال یک  ردیف را به ما می‌دهد.
39. در فایلی به نام Problem39data. xlsx داده‌های مشتریانی را که توسط شرکاQXZ حسابرسی شده‌اند مشاهده می‌کنید. در این فایل نشان‌داده‌شده که شرکت بزرگ است یا کوچک و اینکه در حسابرسی ماه ژوئن قبول و یا رد شده‌اند. اگر شرکت بزرگ باشد و در حسابرسی ماه ژوئن مردود شده باشد، حسابرسی ماه آینده آنها در ژوئیه خواهد بود. اگر شرکت بزرگ باشد و در حسابرسی ماه ژوئن پذیرفته شده باشد، حسابرسی ماه آینده آنها در سپتامبر خواهد بود. اگر شرکت کوچک باشد، حسابرسی آینده آنها در آگوست خواهد بود. فرمولی بنویسید که ماه حسابرسی آینده را در ستون D درج کند.

مطالعه بخش های قبلی این مقاله

توابع IF ، IFERROR، IFS، CHOOSE و SWITCH – بخش 1

توابع IF ، IFERROR، IFS، CHOOSE و SWITCH – بخش 2