Надстройка поиск решения и подбор нескольких параметров Excel
Надстройка Excel «Поиск решения» – это аналитический инструмент, который позволяет нам быстро и легко определить, когда и какой результат мы получим при определенных условиях. Возможности инструмента поиска решения намного выше, чем может предоставить «подбор параметра» в Excel.
Основные отличия между поиском решения и подбором параметра:
- Подбор нескольких параметров в Excel.
- Наложение условий ограничивающих изменения в ячейках, которые содержат переменные значения.
- Возможность использования в тех случаях, когда может быть много решений одной задачи.
Где находится поиск решений в Excel? По умолчанию данная надстройка не установлена. О том, как ее установить читайте: подключение надстройки «Поиск решения».
Примеры и задачи на поиск решения в Excel
Рассмотрим аналитические возможности надстройки. Например, Вам нужно накопить 14 000$ за 10 лет. На протяжении 10-ти лет вы хотите каждый год откладывать на депозитный счет в банке по 1000$ под 5% годовых. Ниже на рисунке построена таблица в Excel, по которой хорошо видно остаток накопленных средств на каждый год.
Как видно при таких условиях депозитного счета и взносов накопления цель не будет достигнута даже через 10 лет. При решении данной задачи можно пойти двумя путями:
- Найти банк, который предлагает более высокую процентную ставку по депозитам.
- Увеличить размер ежегодных накопительных взносов на банковский счет.
Мы можем изменять переменные значения в ячейках B1 и B2 так, чтобы подобрать необходимые условия для накопления необходимой суммы денег.
Надстройка «Поиск решения» - позволяет нам одновременно использовать 2 этих варианта, чтобы быстро смоделировать наиболее оптимальные условия для достижения поставленной цели. Для этого:
- Перейдите в ячейку B14 и выберите инструмент: «Данные»-«Анализ»-«Поиск решения».
- В появившемся диалоговом окне заполните все поля и параметры так как указано ниже на рисунке. Не забудьте убрать галочку напротив опции: «Сделать переменные без ограничений неотрицательными». И нажмите «Найти решение».
Как видно программа немного увеличила процентную ставку и сумму ежегодных взносов.
Ограничение параметров при поиске решений
Допустим, вы пошли в банк с этой таблицей, но банк отказывается поднять Вам процентную ставку. В таком случаи нам нужно узнать, насколько нам придется повысить сумму ежегодных вложений. Мы должны установить ограничение на ячейку с одним переменным значением. Но перед началом измените значения в переменных ячейках на исходные: в B1 на 5%, а в B2 на -1000$. А теперь делаем следующее:
- Перейдите в ячейку B14 и выберите инструмент: «Данные»-«Анализ»-«Поиск решения».
- Напротив списка параметров: «В соответствии с ограничениями» нажмите на кнопку «Добавить».
- В появившемся окне «Добавление ограничения» заполните поля так как указано выше на рисунке. И нажмите ОК.
- Снова заполняем параметры и поля появившегося диалогового окна, как в предыдущем примере:
- Нажмите «Найти решение».
Данный базовый пример открывает Вам возможности использовать аналитический инструмент для более сложных задач, где нужно добавлять ограничения на некоторые показатели при анализе данных.
