Анализ деловых данных
Оптимизация с помощью команды Подбор параметра
Использование команды Поиск решения
3. Анализ “что-если” с помощью Диспетчера сценариев
Успех в бизнесе требует обладания многими важными навыками. Одно самых ценных качеств менеджера - умение строить модели “что-если” и на их основе составлять прогнозы на будущее. Excel содержит несколько полезных средств планирования, которые помогут отыскать путь к светлому финансовому будущему:
Подбор параметра для нахождения значения, приводящего к требуемому результату,
Поиск решения для расчета оптимальной величины по нескольким переменным и ограничениям,
Диспетчером сценариев для создания и оценки наборов сценариев “что-если” с несколькими вариантами исходных данных.
ОПТИМИЗАЦИЯ С ПОМОЩЬЮ КОМАНДЫ ПОДБОР ПАРАМЕТРА
Основной командой для решения оптимизационных задач в Excel является команда Подбор параметра из меню Данные (раздел Работа с данными). Эта команда определяет неизвестную величину, приводящую к требуемому результату, - например, количество проданных компакт-дисков по $14, необходимое для достижения объема продаж в S1 000 000. Простота работы с этой командой является следствием ее ограниченных возможностей - для завершения итерационного цикла меняется всего одна переменная. Если задача подразумевает изменение дополнительных переменных (эффекта от рекламы или оптовых скидок), следует воспользоваться командой Поиск решения.
Для работы с командой Подбор параметра необходимо, чтобы в листе находились;
- формула для расчета;
- пустая ячейка для искомого значения;
- все прочие величины, встречающиеся и формуле.
Ссылка на пустую ячейку должна присутствовать в формуле, так как она является той самой переменной, значение которой ищет Excel.
ПРИМЕЧАНИЕ. При выполнении команды Подбор параметра из меню Данные (раздел Работа с данными) ячейка с формулой должна указываться в текстовом поле Установить в ячейке.
 |
Во время подбора параметра в переменную ячейку непрерывно заносятся новые значения, пока не будет найдено решение поставленной задачи. Такой процесс называется итерацией, и продолжается он до тех пор, пока Excel не выполнит 100 попыток или не найдет решение, лежащее в пределах точности 0,001 от точного значения (чтобы настроить оба параметра, необходимо выполнить команду Параметры из меню Сервис и задать нужные значения на вкладке Вычисления). Команда Подбор параметра существенно экономит время но сравнению с методом, основанном на грубой силе, - то есть на “ручном” переборе входящих в формулу чисел.
Оптимизация с помощью команды Подбор параметра выполняется так:
1. Создайте лист с формулой, пустой (переменной) ячейкой и любыми данными, которые могут понадобиться при вычислениях.
2. Выделите ячейку листа, в которой содержится формула (в диалоговом окне Подбор параметра этой ячейке соответствует поле Установить в ячейке).
3. Выполните команду Подбор параметра из меню Данные. Открывается диалоговое окно Подбор параметра. Выделенная ячейка с формулой появляется в текстовом поле Установить в ячейке, а вокруг ячейки листа появляется пунктирная рамка. Снова щелкните на ячейке, чтобы подтвердить выбор.
4. Ведите в текстовое поле Значение ту величину, которая должна быть возвращена формулой. Например, если интересующий объем продаж равен 30.000 р., то следует ввести в поле Значение величину 30000.
5. Переместите курсор в текстовое ноле Изменяя значение ячейки. При необходимости отодвиньте диалоговое окно Подбор параметра в сторону и выделите ту ячейку, в которой должен содержаться ответ (переменная ячейка). Ее содержимое будет подобрано и подставлено в формулу командой Подбор параметра.
6. Щелкните на кнопке ОК, чтобы запустить поиск решения. После завершения итерационного цикла в диалоговом окне Результат подбора параметра появляется сообщение, а результат заносится в ячейку листа.
7. Закройте диалоговое окно Результат подбора параметра, щелкнув на кнопке ОК
СОВЕТ. При длительных вычислениях (например, при включении в формулу нескольких финансовых функций) можно остановить итерационный цикл кнопкой Пауза в диалоговом окне Результат подбора параметра или просматривать итерации в пошаговом режиме кнопкой Шаг
