Хотя длинный список функций Excel является одной из наиболее заманчивых функций приложения Microsoft для работы с электронными таблицами, существует несколько недостаточно используемых драгоценных камней, которые улучшают эти функции. Одним из часто пропускаемых инструментов является анализ «что, если».
Инструмент «Что, если» в Excel разбит на три основных компонента. Обсуждаемая здесь часть - это мощная функция поиска цели, которая позволяет работать в обратном направлении от функции и определять входные данные, необходимые для получения желаемого результата из формулы в ячейке. Читайте дальше, чтобы узнать, как использовать инструмент поиска целей анализа «что, если» в Excel.
Пример инструмента поиска цели в Excel
Предположим, что вы хотите взять ипотечный кредит на покупку дома, и вы обеспокоены тем, как процентная ставка по кредиту повлияет на ежегодные платежи. Сумма ипотеки составляет 100 000 долларов США, и вы будете погашать кредит в течение 30 лет.
Используя функцию Excel PMT, вы можете легко определить, какими будут годовые выплаты, если процентная ставка будет 0%. Таблица, скорее всего, будет выглядеть примерно так:
Ячейка в A2 представляет годовую процентную ставку, ячейка в B2 - это продолжительность займа в годах, а ячейка в C2 - это сумма ипотечного кредита. Формула в D2:
= ПМТ (А2, В2, С2)
и представляет собой ежегодные выплаты по 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 рассматривает платежи как отрицательный денежный поток. Переменная By Changing Cell - это процентная ставка, которую вы хотите, чтобы Excel нашел для вас, поэтому ипотека в 100 000 долларов будет стоить вам всего 6000 долларов в год. Итак, используйте ячейку A2 .
Нажмите кнопку ОК, и вы можете заметить, что Excel мигает набор чисел в соответствующих ячейках, пока итерации окончательно не сойдутся в окончательное число. В нашем случае ячейка в A2 теперь должна показывать около 4, 31%.
Этот анализ говорит нам о том, что для того, чтобы не тратить более 6000 долларов в год на 30-летнюю ипотеку в 100000 долларов, вам необходимо получить кредит под не более 4, 31%. Если вы хотите продолжить анализ «что, если», вы можете попробовать различные комбинации чисел и переменных, чтобы изучить варианты, которые есть у вас при попытке обеспечить хорошую процентную ставку по ипотеке.
Инструмент Excel для поиска целей анализа «что, если» является мощным дополнением к различным функциям и формулам, содержащимся в типовой электронной таблице. Работая в обратном направлении с результатами формулы в ячейке, вы можете более четко исследовать различные переменные в своих вычислениях.
