اگرچه فهرست طولانی توابع اکسل یکی از جذاب‌ترین ویژگی‌های برنامه صفحه‌گسترده مایکروسافت است، اما تعدادی از جواهرات کم استفاده وجود دارند که این توابع را تقویت می‌کنند. یکی از ابزارهایی که اغلب نادیده گرفته می شود، تحلیل What-If است.

ابزار تجزیه و تحلیل What-If^{(What-If Analysis)} Excel به سه جزء اصلی تقسیم می شود. بخشی که در اینجا مورد بحث قرار می‌گیرد، ویژگی قدرتمند Goal Seek است که به شما امکان می‌دهد از یک تابع به عقب کار کنید و ورودی‌های لازم برای دریافت خروجی مورد نظر از یک فرمول در یک سلول را تعیین کنید. برای یادگیری نحوه استفاده از ابزار What-If Analysis Goal Seek در اکسل به ادامه مطلب مراجعه کنید.

مثال ابزار جستجوی هدف اکسل

فرض کنید می خواهید برای خرید خانه وام رهنی بگیرید و نگران این هستید که نرخ سود وام چه تاثیری بر پرداخت های سالانه دارد. مبلغ وام مسکن 100000 دلار است و شما در طی 30 سال وام را پس خواهید داد.

با استفاده از تابع PMT^(PMT) اکسل ، می توانید به راحتی بفهمید که اگر نرخ بهره 0 درصد باشد، پرداخت های سالانه چقدر خواهد بود. صفحه گسترده احتمالاً چیزی شبیه به این خواهد بود:

سلول در A2 نشان دهنده نرخ بهره سالانه، سلول در B2 طول وام بر حسب سال و سلول در C2 مقدار وام وام مسکن است. فرمول در D2 این است:

=PMT(A2,B2,C2)

و نشان دهنده پرداخت سالانه یک وام مسکن 30 ساله 100000 دلاری با سود 0 درصد است. توجه داشته باشید^(Notice) که رقم D2 منفی است زیرا اکسل^(Excel) فرض می کند که پرداخت ها یک جریان نقدی منفی از وضعیت مالی شما هستند.

متأسفانه، هیچ وام دهنده ای به شما 100000 دلار با سود 0 درصد وام نمی دهد. فرض کنید^(Suppose) مقداری محاسبه کرده اید و متوجه می شوید که می توانید سالانه 6000 دلار به عنوان وام مسکن بازپرداخت کنید. اکنون به این فکر می کنید که بالاترین نرخ بهره ای که می توانید برای وام بگیرید تا مطمئن شوید که در نهایت بیش از 6000 دلار در سال پرداخت نمی کنید، چیست.

بسیاری از افراد در این شرایط به سادگی شروع به تایپ اعداد در سلول A2 می کنند تا زمانی که رقم D2 به حدود 6000 دلار برسد. با این حال، می توانید با استفاده از ابزار What-If Analysis Goal Seek کاری کنید که ^{(Analysis Goal Seek)}اکسل^(Excel) کار را برای شما انجام دهد . در اصل، شما کاری خواهید کرد که اکسل^(Excel) نسبت به نتیجه در D4 به عقب عمل کند تا زمانی که به نرخ بهره ای برسد که حداکثر پرداخت 6000 دلاری شما را برآورده کند.

با کلیک بر روی تب Data در ^(Data)نوار^(Ribbon) و قرار دادن دکمه What-If Analysis در بخش Data Tools شروع کنید. روی دکمه What-If Analysis کلیک کنید و Goal Seek را از منو انتخاب کنید.

اکسل یک پنجره کوچک باز می کند و از شما می خواهد که فقط سه متغیر را وارد کنید. متغیر Set Cell باید سلولی باشد که حاوی فرمول باشد. در مثال ما در اینجا، D2 است . متغیر To Value مقداری است که می‌خواهید سلول در D2 در پایان تجزیه و تحلیل باشد.

برای ما 6000- است . به یاد داشته باشید که اکسل^(Excel) پرداخت ها را به عنوان یک جریان نقدی منفی می بیند. متغیر By Changing Cell نرخ بهره ای است که می خواهید اکسل برای شما پیدا کند به طوری که وام 100000 دلاری تنها 6000 دلار در سال برای شما هزینه دارد. بنابراین، از سلول A2 استفاده کنید .

روی دکمه OK کلیک کنید و ممکن است متوجه شوید که اکسل^(Excel) دسته ای از اعداد را در سلول های مربوطه چشمک می زند تا زمانی که تکرارها در نهایت به یک عدد نهایی همگرا شوند. در مورد ما، سلول در A2 اکنون باید حدود 4.31٪ را بخواند.

این تجزیه و تحلیل به ما می گوید که برای اینکه بیش از 6000 دلار در سال برای وام مسکن 30 ساله و 100000 دلاری خرج نکنید، باید وام را با حداکثر 4.31 درصد تضمین کنید. اگر می‌خواهید به انجام تحلیل‌های چه می‌خواهید ادامه دهید، می‌توانید ترکیب‌های مختلفی از اعداد و متغیرها را امتحان کنید تا گزینه‌هایی را که هنگام تلاش برای تضمین نرخ بهره خوب در وام مسکن دارید، بررسی کنید.

ابزار اکسل What-If Analysis Goal Seek مکمل قدرتمندی برای توابع و فرمول های مختلف موجود در صفحه گسترده معمولی است. با عملکرد معکوس از نتایج یک فرمول در یک سلول، می توانید متغیرهای مختلف در محاسبات خود را با وضوح بیشتری بررسی کنید.

Using Excel’s What-If Analysis Goal Seek Tool

Although Excеl’s long list of functions is one of the most enticing featurеs of Microsoft’s spreadsheet application, there a few underutilіzed gems that enhanсe these functions. One often-overlooked tool is the What-If Analysis.

Excel’s What-If Analysis tool is broken down into three main components. The part discussed here is the powerful Goal Seek feature that lets you work backwards from a function and determine the inputs necessary to get the desired output from a formula in a cell. Read on to learn how to use Excel’s What-If Analysis Goal Seek tool.

Excel’s Goal Seek Tool Example

Suppose that you want to take out a mortgage loan to buy a house and you are concerned about how the interest rate on the loan will affect the yearly payments. The amount of the mortgage is $100,000 and you will pay back the loan over the course of 30 years.

Using Excel’s PMT function, you can easily figure out what the yearly payments would be if the interest rate were 0%. The spreadsheet would likely look something like this:

The cell at A2 represents the yearly interest rate, the cell at B2 is the length of the loan in years, and the cell at C2 is the amount of the mortgage loan. The formula in D2 is:

=PMT(A2,B2,C2)

and represents the yearly payments of a 30-year, $100,000 mortgage at 0% interest. Notice that the figure in D2 is negative since Excel assumes that the payments are a negative cash flow from your financial position.

Unfortunately, no mortgage lender is going to lend you $100,000 at 0% interest. Suppose you do some figuring and find out that you can afford to pay back $6,000 per year in mortgage payments. You are now wondering what is the highest interest rate you can take on for the loan to make sure you don’t end up paying more than $6,000 per year.

Many people in this situation would simply start typing numbers in cell A2 until the figure in D2 reached approximately $6,000. However, you can make Excel do the work for you by using the What-If Analysis Goal Seek tool. Essentially, you will make Excel work backwards from the result in D4 until it arrives at an interest rate that satisfies your maximum payout of $6,000.

Begin by clicking on the Data tab on the Ribbon and locating the What-If Analysis button in the Data Tools section. Click on the What-If Analysis button and choose Goal Seek from the menu.

Excel opens up a small window and asks you to input only three variables. The Set Cell variable must be a cell that contains a formula. In our example here, it is D2. The To Value variable is the amount you want the cell at D2 to be at the end of the analysis.

For us, it is -6,000. Remember that Excel sees payments as a negative cash flow. The By Changing Cell variable is the interest rate you want Excel to find for you so that the $100,000 mortgage will cost you only $6,000 per year. So, use cell A2.

Click the OK button and you may notice that Excel flashes a bunch of numbers in the respective cells until the iterations finally converge on a final number. In our case, the cell at A2 should now read about 4.31%.

This analysis tells us that in order not to spend more than $6,000 per year on a 30-year, $100,000 mortgage, you need to secure the loan at no more than 4.31%. If you want to continue doing what-if analyses, you can try different combinations of numbers and variables to explore the options you have when trying to secure a good interest rate on a mortgage.

Excel’s What-If Analysis Goal Seek tool is a powerful complement to the various functions and formulas found in the typical spreadsheet. By working backwards from the results of a formula in a cell, you can explore the different variables in your calculations more clearly.

Related posts

• نحوه استفاده از تجزیه و تحلیل What-If اکسل

• نحوه استفاده از ویژگی Speak Cells اکسل

• 2 روش برای استفاده از تابع Transpose اکسل

• چگونه می توان تجزیه و تحلیل What-If را در مایکروسافت اکسل درک کرد

• نحوه حذف خطوط خالی در اکسل

• نحوه جستجو در اکسل

• نحوه ایجاد یک ماکرو یا اسکریپت VBA در اکسل

• چگونه یک فرمول/گزاره IF در اکسل بنویسیم

• نحوه درج CSV یا TSV در کاربرگ اکسل

• چرا باید از محدوده های نامگذاری شده در اکسل استفاده کنید؟

• نحوه حذف ردیف های تکراری در اکسل

• نحوه استفاده از Flash Fill In Excel

• داده های کاربرگ خود را برای چاپ در اکسل مرکز کنید

• نحوه ادغام داده ها در چندین فایل اکسل

• نحوه باز کردن Word و Excel در حالت ایمن

• نحوه تعمیر یک ردیف در اکسل

• نحوه سفارشی کردن تابع چسباندن Word

• نحوه مرتب سازی بر اساس تاریخ در اکسل

• از نام های محدوده پویا در اکسل برای کشویی های انعطاف پذیر استفاده کنید

• 5 روش برای تبدیل متن به اعداد در اکسل