Поиск единственного решения

Использование средства Подбор параметра

Средство Применение

Средства Excel

Подбор параметра и Поиск решения

В Excel имеются два мощных средства для анализа данных: Подбор параметра и Поиск решения.

Подбор параметра служит для вычисления величины, обеспечивающей определенное значение формулы.

Поиск решения позволяет найти величину, наилучшую среди множества возможных, или решение, оптимальное в некотором смысле при заданных ограничениях.

Здесь рассмотрены следующие темы:

· Использование средства Подбор параметра для поиска параметра, при котором зависящая от него величина принимает заданное значение

· Использование диаграммы для подбора параметров формул

· Использование средства Поиск решения для оптимизации решения уравнения при определенных ограничениях

· Подготовка нескольких наборов ограничений для задачи поиска решения.

· Оформление отчета по результатам работы Поиска решения

В Excel имеется множество инструментов для решения разнообразных задач. Средства Поиск решения и Подбор параметра предназначены для вычисления входных значений, влияющих на результаты формул.

Подбор Используется для подгонки значения величины, вычисляемой по заданной параметра формуле, путем подбора одного влияющего на эту величину значения

Поиск Используется для поиска оптимального решения некоторой задачи в решения случае, если имеется несколько входных значений, а также набор ограничений на решение. Диспетчер сценариев может запомнить несколько решений, найденных данным средством, и сгенерировать на этой основе отчет

Если известно, какой результат необходимо получить, но не известен аргумент, при котором достигается это решение, пользуйтесь командой Сервис, Подбор параметра. Достаточно указать формулу, ее значение и изменяемую ячейку, влияющую на эту формулу. Путем последовательных итераций Excel найдет ответ.

С помощью этой команды можно определить, например, необходимый для получения заданной прибыли рост производства или объем продаж, обеспечивающий окупаемость.

Изменяемая ячейка должна содержать значение (не формулу) и должна влиять на результат, который требуется получить. Это влияние не обязано быть непосредственным: ячейка может не использоваться в формуле как аргумент. Если описываемым способом ответ получить нельзя, можно попытаться его найти с помощью Диспетчера сценариев, таблицы подстановок или надстройки Поиск решения.

На рис. 1.1 приведен пример простого прогноза объема продаж, расходов и чистой прибыли. Изменяемыми исходными данными являются коэффициенты варьирования объема продаж в строке 16 и относительные затраты в ячейках В18:В20. Коэффициенты варьирования используются для прогноза объема продаж, а величины относительных затрат — для оценки затрат.

Предположим, что необходимо узнать коэффициент варьирования для 1998 года (ячейка D16), который обеспечит в 2000 году чистую прибыль в $3000 (ячейка F12). Воспользуемся для этого средством Подбор параметра.

Если на вкладке Вычисления диалогового окна Параметры установлен флажок Точность как на экране, то параметр, возможно, подобрать не удастся, даже если решение существует. При использовании средства Подбор параметра этот флажок должен быть снят.

Чтобы воспользоваться средством Подбор параметра:

1. Выделите ячейку с формулой, которую вы хотите подогнать под заданное значение. В данном примере — это ячейка F12.

2. Выберите команду Сервис, Подбор параметра. Появится диалоговое окно Подбор параметра (рис. 1.1). В поле Установить в ячейке уже будет находиться ссылка на выделенную на шаге 1 ячейку.

3. В поле Значение введите величину, которую необходимо получить. В данном примере — это 3000.

4. В поле Изменяя значение ячейки введите ссылку на исходную ячейку. Эта ячейка должна влиять на выбранную на шаге 1 формулу. В данном примере исходной ячейкой является D16. Она определяет результаты формул в ячейках строки 5 и тем самым неявно влияет на формулу в ячейке F12.

5. Нажмите кнопку ОК.

Средство Подбор параметра начнет итерационный процесс поиска решения. Каждый шаг этого процесса дает следующее приближение к искомой величине.

6. Если необходимо приостановить или отменить затянувшийся процесс подбора параметра, нажмите в открывшемся диалоговом окне Результат подбора параметра кнопку Пауза или Отмена. После нажатия кнопки Пауза можно выполнять процесс поиска по шагам. Для этого используется кнопка Шаг. Для возобновления автоматического поиска нажмите кнопку Продолжить.

Как уже отмечалось выше, ячейка, выбранная на шаге 4, должна влиять на формулу, указанную в поле Установить в ячейке, и сама не должна содержать формулу. С помощью команды Правка, Перейти, Выделить можно выделить все ячейки, влияющие на заданную.

После того как решение найдено, нажмите кнопку ОК, чтобы заменить значения на рабочем листе новыми значениями, или нажмите кнопку Отмена, чтобы сохранить прежние величины.

Дата добавления: 2014-01-15