Оптимизация с помощью команды Подбор параметра

Анализ деловых данных

Оптимизация с помощью команды Подбор параметра

Использование команды Поиск решения

3. Анализ “что-если” с помощью Диспетчера сценариев

 

Успех в бизнесе требует обладания многими важными навыками. Одно самых ценных качеств менеджера - умение строить модели “что-если” и на их основе составлять прогнозы на будущее. Excel содержит несколько полезных средств планирования, которые помогут отыскать путь к светлому финансовому будущему:

Подбор параметра для нахождения значения, приводящего к требуемому результату,

Поиск решения для расчета оптимальной величины по нескольким переменным и ограничениям,

Диспетчером сценариев для создания и оценки наборов сценариев “что-если” с несколькими вариантами исходных данных.

 

 

ОПТИМИЗАЦИЯ С ПОМОЩЬЮ КОМАНДЫ ПОДБОР ПАРАМЕТРА

 

Основной командой для решения оптимизационных задач в Excel является команда Подбор параметра из меню Данные (раздел Работа с данными). Эта команда определяет неизвестную величину, приводящую к требуемому результату, - например, количество проданных компакт-дисков по $14, необходимое для достижения объема продаж в S1 000 000. Простота работы с этой командой является следствием ее ограниченных возможностей - для завершения итерационного цикла меняется всего одна переменная. Если задача подразумевает изменение дополнительных переменных (эффекта от рекламы или оптовых скидок), следует воспользоваться командой Поиск решения.

Для работы с командой Подбор параметра необходимо, чтобы в листе находились;

- формула для расчета;

- пустая ячейка для искомого значения;

- все прочие величины, встречающиеся и формуле.

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

ПРИМЕЧАНИЕ. При выполнении команды Подбор параметра из меню Данные (раздел Работа с данными) ячейка с формулой должна указываться в текстовом поле Установить в ячейке.

 

 

 

 

Во время подбора параметра в переменную ячейку непрерывно заносятся новые значения, пока не будет найдено решение поставленной задачи. Такой процесс называется итерацией, и продолжается он до тех пор, пока Excel не выполнит 100 попыток или не найдет решение, лежащее в пределах точности 0,001 от точного значения (чтобы настроить оба параметра, необходимо выполнить команду Параметры из меню Сервис и задать нужные значения на вкладке Вычисления). Команда Подбор параметра существенно экономит время но сравнению с методом, основанном на грубой силе, - то есть на “ручном” переборе входящих в формулу чисел.

Оптимизация с помощью команды Подбор параметра выполняется так:

1. Создайте лист с формулой, пустой (переменной) ячейкой и любыми данными, которые могут понадобиться при вычислениях.

2. Выделите ячейку листа, в которой содержится формула (в диалоговом окне Подбор параметра этой ячейке соответствует поле Установить в ячейке).

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

4. Ведите в текстовое поле Значение ту величину, которая должна быть возвращена формулой. Например, если интересующий объем продаж равен 30.000 р., то следует ввести в поле Значение величину 30000.

5. Переместите курсор в текстовое ноле Изменяя значение ячейки. При необходимости отодвиньте диалоговое окно Подбор параметра в сторону и выделите ту ячейку, в которой должен содержаться ответ (переменная ячейка). Ее содержимое будет подобрано и подставлено в формулу командой Подбор параметра.

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

7. Закройте диалоговое окно Результат подбора параметра, щелкнув на кнопке ОК

 

 

СОВЕТ. При длительных вычислениях (например, при включении в формулу нескольких финансовых функций) можно остановить итерационный цикл кнопкой Пауза в диалоговом окне Результат подбора параметра или просматривать итерации в пошаговом режиме кнопкой Шаг