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

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

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

در بسیاری از پیش بینی های صورت‌های مالی، از وجه نقد به‌عنوان حلقه اتصالی برای ایجاد تراز میان بدهی‌ها و دارایی‌ها استفاده می‌شود. به نظر من استفاده از بدهی به‌عنوان حلقه اتصال مورد واقع‌گرایانه‌تری است.

چگونه می‌توانم پیش بینی صورت‌های مالی ایجاد کنم که در آن از بدهی‌ها به‌عنوان حلقه اتصال استفاده شده باشد؟

پیش بینی صورت مالی

یک پیش بینی صورت‌های مالی (پیش صورت وضعیت یا پیش صورت‌حساب) اساساً پیش‌بینی وضعیت مالی شرکت در آینده است. برای ایجاد یک پیش بینی  نیاز داریم که ترازنامه آتی و صورت سود و زیان را تهیه کنیم. ترازنامه شرکت درواقع نگاهی اجمالی به دارایی‌ها و بدهی‌های شرکت در هر بازه زمانی می‌باشد. صورت سود و زیان به شما در باره تغییرات مالی شرکت در هر بازه زمانی اطلاعاتی ارائه می‌دهد. پیش بینی صورت‌های مالی می‌تواند به شرکت کمک کند تا نیازهای آینده برای پرداخت بدهی‌ها و همچنین نقاط کلیدی شبیه‌سازی‌هایی که تحلیل گران سهام از آن برای روشن شدن ارزشگذاری سهام استفاده می‌کنند ، را مشخص کند. در فایلی بنام performa.xlsx جریان آزاد نقدی (FCFs) یک شرکت را برای چهار سال آینده ایجاد کرده‌ام. تصویر 6-12 به شما ترازنامه و تصویر شماره 7-12 به شما صورت سود و زیان را نشان می‌دهد.

تصویر 6-12 پیش‌فرض‌های پیش بینی  و ترازنامه

تصویر 7-12 پیش بینی صورت سود و زیان

ستونD حاوی اطلاعاتی درباره وضعیت جاری شرکت( در طول سال صفر ) می‌باشد. پیش‌فرض‌های اساسی عبارتند از:

رشد فروش در سال 2 درصد است.

فروش اولیه 1000 دلار است.

نرخ بهره دیون 10 درصد است.

پرداخت سود سهام 5 درصد از درآمد کل است.

نرخ مالیات 53 درصد است.

هزینه کالاهای فروخته شده (COGS)75 درصد فروش است.

استهلاک 10 درصد از جمع دارایی‌های ثابت است.

دارایی نقد و شبه نقد 9 درصد درآمد کسب می‌کند.

بدهی‌های جاری هفت درصد فروش می‌باشند.

در ابتدا نام‌های سلول‌های محدوده C3:C10 را به سلول‌های محدوده D3:D10 تخصیص می‌دهیم. سپس در هرسال t مفاهیم مالی و حسابداری روابط زیر را ایجاد می‌کنند که در یک سری از فرمول‌ها قرار داده شده‌اند:

فرمول 12.1: Year t+1 sales=(Year t sales)*(1+SG). فروش هرسال را با کپی‌کردن فرمول =D28*(1+SG) از سلول E28  به محدوده F28:H28 به دست می‌آوریم.

فرمول 12.2:  Year t COGS=COGS*(Year t sales). هزینه کالاهای فروخته شده هرسال را با کپی‌کردن فرمول =COGS*E28 از سلول E29 به محدوده F29:H29 محاسبه می‌کنیم.

فرمول 12.3: اگر اموال سال t بزرگ‌تر از بدهی های سالt باشند، آن‌وقت بدهی های سال t می‌بایست برابر با :  Year t total assets–Year t current liabilities–Year t equity باشد. در غیر این صورت بدهی سال t برابر صفر است. من بدهی هرسال را با کپی‌کردن فرمول =IF(E18>E20+E24,E18–E20–E24,0) از سلول E21 در سلول‌های F21:H21 محاسبه کردم. اگر کل اموال سال t بیشتر از کل بدهی‌های سالt  باشد ، این فرمول بدهی سال tرا برابر: Year t total assets–Year t current liabilities–Year t equity قرار می‌دهد. این امر اموال و بدهی را باهم برابر یا تراز می‌کند. در غیر این صورت شما بدهی سال t را برابر صفر قرار می دهید. در این مثال پول نقد و اوراق بهادار قابل ارائه در بازار سال t برای تراز کردن دارایی‌ها و دیون مورد استفاده قرار گرفته است.

فرمول 12.4: Year t current liabilities=(CL/Sales ratio)*(Year t sales) . در محدوده E20:H20 مقدار بدهی های جاری را با کپی‌کردن فرمول =$H$4*E28  از سلول E20 در سلول‌های F20:H20 محاسبه کرده‌ام.

فرمول 12.5: Year t equity=Year t stock+Year t retained earnings. در سلول E24:H24 میزان سهام عادی را با کپی‌کردن فرمول =SUM(E22:E23) از سلول E24 به سلول‌های F24:H24 محاسبه کرده‌ام.

فرمول 12.6 :چنانچه بدهی سال t بزرگ‌تر از صفر باشد، پول و اوراق بهادار سال t  برابر با صفر خواهند بود. در غیر این صورت پول و اوراق بهادار قابل‌عرضه در بازار سال t برابراست با:

MAX(0,Year t total liabilities–Year t current assets–Year t net fixed assets)

در سلول‌های E13:H13وجوه نقد و اوراق بهادار قابل عرضه در بازار هرسال را با کپی‌کردن فرمول =IF(E21>0,0,MAX(0,E25–E14–E17)) از سلول E13 در سلول‌های F13:H13 محاسبه کرده ایم. اگر دیون سال t بزرگ‌تر از صفر باشد، نیازی نیست از وجوه نقد و اوراق بهادار سال  t برای تراز کردن دارایی‌ها و دیون استفاده کنید. در این صورت مقدار وجوه نقد و اوراق بهادار قابل عرضه در بازار را برابر با صفر قرار می‌دهم. در غیر این صورت من میزان وجوه نقد و اوراق بهادار  را برابر با:Year t total assets–Year t current liabilities–Year t equity قرار می‌دهم. این مورد چنانچه دارایی سال t(بدون وجه نقد و اوراق بهادار قابل‌عرضه در بازار) کمتر از دیون سال t باشد، دیون و دارایی را تراز می‌کند. اگر قرض‌ها با دارایی‌ها و دیون تراز نباشد این باعث به‌وجودآمدن دارایی‌های شبه نقد می‌شود که به‌عنوان تنظیم‌کننده‌ای عمل کرده و دارایی‌ها و دیون را تراز می‌نماید.

فرمول 12.7 : Year t interest expense=(Year t debt)*IRD. در سلولE33 هزینه را با استفاده از فرمول =IRD*E21 درج کرده‌ام. کپی‌کردن دوباره این فرمول به محدوده F33:H33  هزینه بهره را برای سال دوم تا چهارم محاسبه می‌کند.

فرمول 12.8:Year t interest income=(Year t cash and marketable securities)*LAIR  درمحدوده E32:H32  هزینه بهره با کپی‌کردن فرمول: =LAIR*E13 از سلول E32 به محدوده F32:H32 محاسبه شده است.

فرمول 12.9: Year t operating income=Year t sales–Year t COGS–Year t depreciation درآمد عملیاتی در سلول‌های E31:H31 با کپی‌کردن فرمول =E28–E29–E30  از سلول E31 به محدوده F31:H31 محاسبه شده است.

فرمول 12.10: Year t dividends=(Year t net income)*DIV. در محدوده E39:H39  فرمول =E36*DIV  را از سلول E39 به سلول‌های F39:H39 کپی می‌کنیم تا سود سهام هرسال را بدست آوریم.

فرمول 12.11: Year t+1 beginning retained earnings=Year t ending retained earning سود انباشته اولیه هرسال در سلول‌هایF38:H38 با کپی‌ فرمول: =E40  از سلول F38 به محدوده G38:H38 محاسبه می‌شود.

فرمول 12.12: Year t ending of the year retaind earning= year t beginning retaind earnings + year t net income-year t devidends در محدوده E40:H40 سود انباشته پایانی هرسال با کپی‌کردن فرمول =E38+E36–E39 از سلول E40 به  سلول‌های F40:H40 محاسبه می‌شود.

فرمول 12.13: Year t income before taxes=Year t operating income–Year t interest expense+Year t cash income

درآمد پیش از مالیات را با کپی‌کردن فرمول =E31–E33+E32 از سلول E34 به محدوده F34:H34 به دست می‌آوریم.

فرمول 12.14: .Year t taxes=(Year t income before taxes)*TR  مالیات هرسال را در سلول‌های E35:H35 با کپی‌کردن فرمول  =TR*E34  از سلول E35 به سلول‌های F35:H35 محاسبه می‌کنیم.

فرمول 12.15: Year t net income after taxes=(Year t income before taxes)–(Year t taxes) در سلول‌های E36:H36 در آمد خالص هرسال را با کپی‌کردن فرمول=E34–E35 از سلول E36 به سلول‌های F36:H36 محاسبه می‌کنیم.

فرمول 12.16:Year t gross fixed assets=Year t net fixed assets+Year t accumulated depreciation در سلول‌هایE15:H15 جمع دارایی‌های ثابت هرسال را با کپی‌کردن فرمول =E17+E16 از سلول E15 به محدوده G15:H15 محاسبه می‌کنیم.

فرمول 12.17: Year t depreciation=(Year t net fixed assets)*DEP. با کپی‌کردن فرمول =DEP*E15   از سلول  E30به سلولهایF30:H30  برای محاسبه استهلاک هرسال استفاده می‌کنیم.

فرمول 12.18: Year t accumulated depreciation=yeart-1accumulared depreciation+year t Deperciation از فرمول =D16+E30 برای محاسبه جمع استهلاک با کپی‌کردن آن از سلول E16 به محدودهF16:H16 استفاده می‌کنیم.

فرمول 12.19: Year t net fixed assets=year t gross fixed assets-year t accumulated depreciationدر ردیف 17  برای محاسبه دارایی‌های ثابت خالص فرمول=E15-E16 را از سلول E17 به محدوده F17:H17 کپی کرده ایم.

فرمول 12.20: Year t total assets=year t liquid assets+year tfixed assets+year t cash and marketable securities با جمع‌کردن دارایی‌های نقد و شبه نقد، دارایی‌های جاری و دارایی‌های ثابت خالص، کل دارایی‌ها را با کپی‌کردن فرمول =SUM(E13,E14,E17) از سلول E18 به محدوده F18:H18 محاسبه می‌کنیم.

فرمول 12.21: Year t total liabilities=Year t current liabilities+Year t debt+Year t equity با کپی‌کردن فرمول =SUM(E20,E21,E24) از سلولE25به محدوده F25:H25  کل بدهی‌ها را برای هر دوره محاسبه می‌کنیم. به‌خاطر صورت بدهی ها و دارایی‌های شبه نقد ، هرسال دارای تراز خواهد بود.

فرمول‌های 12.3 و 12.6 نیازمند به استفاده از عبارت شرطی IF می‌باشند. این کاربرگ همچنین دارای ارجاع چرخشی می‌باشد. (برای اطلاعات بیشتر درباره حل ارجاعات چرخشی فصل یازده بنام ارجاع چرخشی را مطالعه کنید.) مثلاً رابطه زیر ارجاع چرخشی ایجاد می‌کنند.

وجوه نقد سال t بر دارایی‌های کل سال t اثر می‌گذارند.

دارایی‌های کل سال t  بر بدهی‌های سال t اثر می‌گذارند.

بدهی‌های سال t بر وجوه نقد سال t  اثر می‌گذارند.

ازآنجاکه کاربرگ دارای ارجاع چرخشی است، می‌بایست بر سربرگ File کلیک کرده و بعد روی گزینه Options کلیک نموده و بخش Formulas در سمت چپ کادر محاوره‌ای را انتخاب نموده و سپس گزینه Enable Irattive Calculation را از بخش Calculation Section فعال نمایید. بعد روی OK  کلیک کنید. همان‌طور که در فصل 11 توضیح داده شد، این کار باعث می‌شود اکسل مشکل ارجاع‌های چرخشی را حل نماید. توجه کنید که کل دارایی‌ها در هرسال t در ردیف 18 برابر با کل بدهی‌ها در ردیف 25 خواهند بود. این قدرت فرمول‌هایIF ترکیب شده با ارجاع های چرخشی را نشان می‌دهد.

وقتی‌که فرمول VLOOKUP را برای تعیین دستمزد کارمندها کپی می‌کنم، پیغام‌های خطای #NA زیادی دریافت می‌کنم. سپس هنگامی‌که میانگین دستمزد کارمندان را محاسبه می‌کنم به‌خاطر پیغام‌های خطای#NA نمی‌توانم پاسخی عددی دریافت کنم. آیا می‌توانم به‌سادگی پیغام‌های خطا را با فضایی خالی جایگزین کنم تا بتوانم میانگین دستمزدها را محاسبه کنم؟

فایلی به نام Errortrap.xlsx (تصویر 8-12 را ببینید) حاوی دستمزدها و نام‌های پنج کارمند در محدوده سلول‌هایD3:E7 در کار برگSheet1 می‌باشد. در سلول‌های D11:D15 فهرستی از نام پنج نفر (برخی از آنها از فهرست بالایی تکرار شده‌اند) درج شده و دستمزد آنها با کپی‌کردن فرمول =VLOOKUP(D11,$D3:$E$7,2,False)  از سلول E11 به سلول‌های E12:E14 محاسبه شده است. بدبختانه اکسل در سلول‌های E13 و E14 پیغام خطای #N/A نشان می دهد.( که مخفف کلمه Not available یا دردسترس نمی باشد است). اکسل پیغام خطای #N/A را وقتی‌که فرمولی نتواند مقدار مناسب را به ما بدهد صادر می‌کند. از آنجا که کارمندهایی به نام جی آر و جاش در فهرست فاقد دستمزد لیست شده می‌باشند، تابعVLOOKUP نمی تواند مقدار دستمزدی برای آنها برگرداند. بنابراین وقتی‌که میانگین دستمزد را در سلول E16 با فرمول =AVERAGE(E11:E15) محاسبه می‌کنیم پیغام خطای#N/A دریافت می‌کنیم. بسیاری افراد به صورت دستی مقادیر سلول‌های دارای پیغام خطا را با یک فضای خالی(SPACE) پر می کنند تا فرمول میانگین بتواند محاسبه را به شکل صحیح انجام دهد( با نادیده گرفتن فضا های خالی) هرچند راه بهتری برای انجام این کار وجود دارد! تابع IFERROR  در یک‌چشم به‌هم‌زدن خطاها را با یک کاراکتر (مثل فضای خالی یا عدد صفر) جایگزین می‌کند. دستور زبان این تابع عبارت است از IFERROR(value,value_if_error).

مثالی از این تابع را بعداً به شما نشان خواهیم داد.

اولین آرگومان فرمولی است که می‌خواهید محاسبه شود و دومین آرگومان ارزشی است که در سلول قرار داده شده چنانچه فرمول مقدار خطایی را برگرداند. (سایر خطاهای معمول عبارت‌اند از

#DIV/0، #NAME، #NUM، #REF و #VALUE. این موارد را در همین بخش بیشتر توضیح خواهیم داد)

بنابراین با کپی‌کردن فرمول IFERROR(VLOOKUP(D11,$D$3:$E3:$E7,2,False),” “)

از سلول F11 به محدوده F12:F15 دستمزد هریک از کارمندان واقعی محاسبه شده و برای هریک از کارمندانی که جزو این شرکت نیستند یک فضای خالی قرار داده می‌شود. (برای همین است که محدودهF13:F14  سلول‌هایی خالی به ما نشان می‌دهد. می‌توانید فرمول‌های این سلول ها را چک کنید)  حالا فرمول =AVERAGE(F11:F15)  در سلولF16 به‌درستی میانگین دستمزد هریک از کارمندان موجود در لیست را محاسبه می‌کند.

تصویر 8-12 خطا زدایی از فرمول‌ها

اکسل در نسخه سال 2010 تابع جدیدی بنام AGGREGATE معرفی کرد که به کاربرها اجازه می‌داد محاسبات را اجرا نموده و ردیف‌هایی که دارای خطا بودند را نادیده بگیرید. دستور زبان تابعAGGREGATE عبارت است از AGGREGATE(function number, option, array)

Function number(عدد تابع) کدی بین 1 تا 19 است که به تابع فعال در محاسبات داده می‌شود مثلاً 1 = AVERAGE and 9 = SUM.قسمت Help مرتبط با این تابع را (که با کلیک کردن روی گزینه Help در کادر محاوره‌ای Function wizard احضار می‌شود) را برای مشاهده فهرست کاملی از کدهای موجود بررسی کنید. آرگومان Option(انتخاب) نوع سلول‌هایی را که می‌بایست در محاسبات نادیده گرفته شوند را به شما می‌دهد. (جدول زیر را مشاهده کنید.) من از انتخاب شماره 6 استفاده کردم تا مقادیر خطا نادیده گرفته شوند. Array(آرایه) محدوده سلول‌هایی است که در محاسبه مورداستفاده قرار می‌گیرد. همان‌طور که در سلول  E18نشان‌داده‌شده، فرمول AGGREGATE(1,6,E11:E15) به‌درستی متوسط دستمزدهای فهرست شده ( 74.6667) را محاسبه می‌کند.

فایلی بنام Errotrypes.Xlsx که در تصویر 9-12 نشان‌داده‌شده حاوی مثال‌هایی از دیگر خطاهای مقدار رایج می‌باشد.

تصویر 9-12 مثال‌هایی از خطاهای مقداری اکسل

مثال‌های زیر مقادیرخطا‌های مختلفی را نشان می دهند:

• در سلول D3 فرمول =B3/C3 خطای#DIV/0 را برمی‌گرداند چراکه نمی‌توان چیزی را بر صفر تقسیم کرد.
• در سلول D6 فرمول=B6+C6 خطای #VALUE! را برمی‌گرداند. چراکه عبارتJack داده مناسبی برای فرمول وارد شده نیست(Jack یک عبارت متنی است).
• در سلولD8 فرمول=SQRT(-1) پیغام خطای #NUM را می‌دهد. پیغام خطای#NUM وقتی رخ می‌دهد که آرگومان غیر قابل قبول را در یک تابع قرار می دهید. اکسل از آنجایی که اعداد منفی  ریشه دوم ندارند پیغام خطای #NUM! را نشان می‌دهد.
• در سلول C9 فرمول SUM(A1:A3) را وارد کرده ایم و بعد ستون را پاک کرده ایم. این کار منجر به ایجاد پیغام خطای#REF! می‌شود، چراکه سلول‌هایی که در فرمول به آنها ارجاع داده شده (سلول‌هایA1:A3) دیگر در کاربرگ وجود ندارند.

از تابع IFERROR می‌توان برای جایگزین‌کردن هریک از این مقادیر خطا با عدد یا رشته متنی استفاده کرد.