Хотя длинный список функций Excel является одной из самых привлекательных особенностей приложения для работы с электронными таблицами Microsoft, есть несколько малоиспользуемых возможностей, которые расширяют эти функции. Одним из инструментов, о котором часто забывают, является анализ «что, если».
Инструмент анализа «что если» Excel разбит на три основных компонента. Здесь обсуждается мощная функция поиска цели, которая позволяет вам работать в обратном направлении от функции и определять входные данные, необходимые для получения желаемого результата из формулы в ячейке. Читайте дальше, чтобы узнать, как использовать инструмент поиска цели анализа «что, если» в Excel.
Пример инструмента поиска целей Excel
Предположим, вы хотите взять ипотечный кредит на покупку дома и обеспокоены тем, как процентная ставка по кредиту повлияет на ежегодные платежи. Сумма ипотеки составляет 100 000 долларов США, и вы будете погашать ее в течение 30 лет.
Используя функцию ПЛТ Excel, вы можете легко определить, какими были бы годовые платежи, если бы процентная ставка составляла 0%. Таблица, скорее всего, будет выглядеть примерно так:
Ячейка A2 представляет годовую процентную ставку, ячейка B2 — продолжительность кредита в годах, а ячейка C2 — сумму ипотечного кредита. Формула в D2:
=ПЛТ(A2,B2,C2)
и представляет собой ежегодные выплаты по 30-летней ипотеке на сумму 100 000 долларов США под 0 %. Обратите внимание, что цифра в D2 отрицательна, поскольку Excel предполагает, что платежи представляют собой отрицательный денежный поток от вашего финансового положения.
К сожалению, ни один ипотечный кредитор не одолжит вам 100 000 долларов под 0%. Предположим, вы подсчитали и обнаружили, что можете себе позволить выплачивать 6000 долларов в год в виде выплат по ипотеке. Теперь вы задаетесь вопросом, какую самую высокую процентную ставку вы можете взять по кредиту, чтобы не платить более 6000 долларов в год.
Многие люди в такой ситуации просто начали бы вводить числа в ячейку A2, пока цифра в D2 не достигнет примерно 6000 долларов США. Однако вы можете заставить Excel сделать всю работу за вас, используя инструмент поиска цели анализа «Что, если». По сути, вы заставляете Excel работать в обратном направлении от результата в D4 до тех пор, пока он не достигнет процентной ставки, соответствующей вашей максимальной выплате в 6000 долларов США.
Для начала нажмите вкладку Данные на ленте и найдите кнопку Анализ «что, если » в разделе Инструменты для работы с данными . Нажмите кнопку Анализ «что, если » и выберите в меню Поиск цели ..
Excel открывает небольшое окно и просит вас ввести только три переменные. Переменная Set Cell должна представлять собой ячейку, содержащую формулу. В нашем примере это D2 . Переменная To Value – это сумма, которую вы хотите, чтобы ячейка в D2 находилась в конце анализа.
Для нас это -6000 . Помните, что Excel рассматривает платежи как отрицательный денежный поток. Переменная Изменяя ячейку — это процентная ставка, которую Excel должен найти для вас, чтобы ипотека в размере 100 000 долларов США обошлась вам всего в 6 000 долларов США в год. Итак, используйте ячейку A2 .
Нажмите кнопку ОК , и вы можете заметить, что Excel мигает множеством чисел в соответствующих ячейках, пока итерации, наконец, не сойдутся на окончательном числе. В нашем случае в ячейке A2 теперь должно быть около 4,31%.
Этот анализ показывает, что для того, чтобы не тратить более 6000 долларов в год на 30-летнюю ипотеку стоимостью 100 000 долларов, вам необходимо обеспечить кредит под ставку не более 4,31%. Если вы хотите продолжить анализ «что, если», вы можете попробовать различные комбинации чисел и переменных, чтобы изучить варианты, которые у вас есть при попытке обеспечить хорошую процентную ставку по ипотеке.
Инструмент поиска цели анализа «что, если» в Excel является мощным дополнением к различным функциям и формулам, присутствующим в обычной электронной таблице. Работая в обратном направлении от результатов формулы в ячейке, вы можете более четко изучить различные переменные в своих расчетах..