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

Тема 6. Анализ деловых данных

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

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

Постановка задачи

Поиск решения

Изменение условий оптимизационных задач

4. Вариативность решения задачи.

5. Анализ «что-если» с помощью Диспетчера сценариев

5.1. Создание Сценария

5.2. Просмотр Сценария

5.3. Создание Отчетов по Сценарию

5.4. Редактирование, удаление и объединение Сценариев

Excel содер­жит ряд инструментов анализа, планирования и моделирования по алгоритму «что - если» для решения некоторых специфических экономических задач, таких, как:

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

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

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

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

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

Пример. Вычислить количество проданных чашек кофе по 1.75 руб., необходимое для достижения объема продаж в 30000 руб.

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

Во время подбора параметра в т.н. переменную ячейку непрерывно заносятся новые значения, пока не будет найдено решение поставленной задачи. Этот процесс - итерация, и продолжается он до тех пор, пока Excel не выпол­нит 100 попыток или не получит решение в пределах 0,001 от точного значения

Чтобы настроить оба параметра, необходимо выполнить:

Кнопка Office – кнопка Параметры Excel - соответствующее диалоговое окно - категория Формулы – раздел Параметры вычислений -здесь задаются значения попыток и шагов итераций (См. Рис. 1).

Рис. 1. Диалоговое окно Параметры Excel - категория Формулы – раздел Параметры вычислений.

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

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

· переменная (пустая) ячейка для искомого значения;

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

Ссылка на переменную (пустую) ячейку в формуле - обязательна. Она является той самой переменной, значение которой ищет Excel.

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

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

Рис. 2. Для команды Подбор параметра необходима формула и пустая ячейка с переменной величиной

2. Выделить ячейку Листа, в которой содержится формула.

3. Далее:

ВкладкаДанные – Группа Инструментов Работа с данными – кнопка раскрывающегося списка Анализ «Что-если» - команда Подбор параметра.

4. Открывается диало­говое окно Подбор параметра. (См. Рис. 3) Выделенная ячейка с формулой появляется в текстовом поле Установить в ячейке, а вокруг ячейки листа появляется пунктирная рамка.

5. Щелкнуть на ячейке, чтобы подтвердить выбор.

6. Ввести в текстовое поле Значение ту величину (например, 30000 руб.), которая должна быть возвращена формулой.

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

Рис. 3. Диалоговое окно Подбор параметра

8. Нажать кнопку ОК, чтобы найти решение. После завершения итерацион­ного цикла в диало­говом окне Результат подбора параметра появляется сообщение, а результат заносится в ячейку листа (См. Рис. 4)