Хотя длинный список функций Excel является одной из самых заманчивых особенностей приложения для работы с электронными таблицами от Microsoft, есть несколько малоиспользуемых жемчужин, которые улучшают эти функции. Одним из часто упускаемых из виду инструментов является анализ «что, если».

Инструмент анализа^{(What-If Analysis)} «что, если» в Excel разбит на три основных компонента. Обсуждаемая здесь часть — это мощная функция поиска цели^{(Goal Seek)} , которая позволяет работать в обратном направлении от функции и определять входные данные, необходимые для получения желаемого вывода из формулы в ячейке. Читайте дальше, чтобы узнать, как использовать инструмент поиска цели анализа «что, если^{(What-If Analysis Goal Seek)} » в Excel .

Пример инструмента поиска цели в Excel

Предположим, вы хотите взять ипотечный кредит на покупку дома и вас беспокоит, как процентная ставка по кредиту повлияет на ежегодные платежи. Сумма ипотечного кредита составляет 100 000 долларов США, и вы будете выплачивать кредит в течение 30 лет.

Используя функцию Excel PMT , вы можете легко вычислить, какими были бы ежегодные платежи, если бы процентная ставка была равна 0%. Электронная таблица, вероятно, будет выглядеть примерно так:

В ячейке A2 представлена ​​годовая процентная ставка, в ячейке B2 — срок кредита в годах, а в ячейке C2 — сумма ипотечного кредита. Формула в D2:

=PMT(A2,B2,C2)

и представляет собой ежегодные платежи по 30-летней ипотеке на сумму 100 000 долларов США под 0% годовых. Обратите внимание^(Notice) , что число в D2 отрицательное, поскольку Excel предполагает, что платежи представляют собой отрицательный денежный поток от вашего финансового положения.

К сожалению, ни один ипотечный кредитор не одолжит вам 100 000 долларов под 0% годовых. Предположим^(Suppose) , вы подсчитали и выяснили, что можете позволить себе возвращать 6000 долларов в год в виде платежей по ипотеке. Теперь вам интересно, какова самая высокая процентная ставка, которую вы можете взять по кредиту, чтобы убедиться, что вы не платите больше 6000 долларов в год.

Многие люди в такой ситуации просто начали бы вводить числа в ячейку A2, пока цифра в D2 не достигла бы примерно 6000 долларов. Однако вы можете заставить Excel делать всю работу за вас, используя инструмент поиска цели анализа^{(Analysis Goal Seek)} «что, если» . По сути, вы заставите Excel работать в обратном направлении от результата в D4, пока он не достигнет процентной ставки, которая удовлетворяет вашей максимальной выплате в 6000 долларов.

Начните с нажатия на вкладку « Данные^(Data) » на ленте^(Ribbon) и найдите кнопку « Анализ «что, если»» в разделе « ^{(What-If Analysis)}Инструменты данных^{(Data Tools)} ». Нажмите кнопку « Анализ «что, если»»^{(What-If Analysis)} и выберите «Поиск цели^{(Goal Seek)} » в меню.

Excel открывает маленькое окно и просит вас ввести только три переменные. Переменная Set Cell должна быть ячейкой, содержащей формулу. В нашем примере это D2 . Переменная To Value — это сумма, которую вы хотите, чтобы ячейка в D2 была в конце анализа.

Для нас это -6000 . Помните, что Excel рассматривает платежи как отрицательный денежный поток. Переменная By Changeing Cell^{(By Changing Cell)} — это процентная ставка, которую Excel должен найти для вас, чтобы ипотека в размере 100 000 долларов стоила вам всего 6 000 долларов в год. Итак, используйте ячейку A2 .

Нажмите кнопку « ОК^(OK) » , и вы можете заметить, что Excel мигает набором чисел в соответствующих ячейках, пока итерации, наконец, не сойдутся на окончательном числе. В нашем случае ячейка A2 теперь должна показывать около 4,31%.

Этот анализ говорит нам, что для того, чтобы не тратить более 6000 долларов в год на 30-летнюю ипотеку на 100 000 долларов, вам необходимо обеспечить кредит не более чем на 4,31%. Если вы хотите продолжить анализ «что, если», вы можете попробовать различные комбинации чисел и переменных, чтобы изучить варианты, которые у вас есть, когда вы пытаетесь получить хорошую процентную ставку по ипотеке.

Инструмент поиска цели анализа^{(What-If Analysis Goal Seek)} «что, если» в Excel является мощным дополнением к различным функциям и формулам, которые можно найти в типичной электронной таблице. Работая в обратном направлении от результатов формулы в ячейке, вы можете более четко изучить различные переменные в своих вычислениях.

Using Excel’s What-If Analysis Goal Seek Tool

Althоugh Excel’s long list of functions is one of the most enticing features of Microsoft’s spreadsheet application, there a few underutilized gems that enhance these functions. One often-overloоked 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

• Как использовать анализ «что, если» в Excel

• Как использовать функцию «Говорить ячейки» в Excel

• 2 способа использования функции транспонирования Excel

• Как понять анализ «что, если» в Microsoft Excel

• Как удалить пустые строки в Excel

• Быстрое написание чисел в Word и Excel

• Как настроить функцию вставки Word

• Как написать формулу/оператор ЕСЛИ в Excel

• Как создать простую сводную таблицу в Excel

• Как объединить ячейки, столбцы и строки в Excel

• Как вставить CSV или TSV в рабочий лист Excel

• Как быстро вставить несколько строк в Excel

• Группировать строки и столбцы на листе Excel

• 4 способа конвертировать Excel в Google Таблицы

• Как исправить ошибки #N/A в формулах Excel, таких как ВПР

• Как отследить зависимых в Excel

• Базовая сортировка данных в один столбец и несколько столбцов в электронных таблицах Excel

• Как создать несколько связанных раскрывающихся списков в Excel

• Как удалить линии сетки в Excel

• 4 способа использования галочки в Excel