Диспетчер сценариев «что – если»
Решение уравнений и задач оптимизации Для решения задач оптимизации широкое променение находят различные средства Excel. В этом разделе рассмотрим команды: · Подбор параметров для нахождения значения, приводящего к требуемому результату. · Надстройку Поиск решения для расчета оптимальной величины по нескольким переменным и ограничениям; · Диспетчер сценариев для создания и оценки наборов сценариев «что – если» с несколькими вариантами исходных данных. Подбор параметров Основной командой для решения оптимизационных задач в Excel является команда Сервис/Подбор параметра. Эта команда определяет неизвестную величину, приводящую к требуемому результату. Если команда Подбор параметра отсутствует в меню Сервис, выполните команду Сервис/Надстройка и установите флажок Пакет анализа в окне диалога Надстройка Для работы с командой Подбор параметра необходимо подготовить лист, чтобы в листе находились: · формула для расчета; · пустая ячейка для искомого значения; · другие величины, которые используются в формуле. Такой процесс называется итерацией, и продолжается он до тех пор, пока редактор не выполнит 100 попыток или не найдет решения, лежащее в пределах точности 0,001 от точного значения (настройка этих параметров осуществляется с помощью команды Сервис/Параметры, вкладка Вычисления) Оптимизация с помощью команды Подбор параметров выполняется так: 1. Создайте лист, например, с формулой =B1*B2 в ячейке B3, пустой (переменной) ячейкой (B2) и другими данными (B1), которые могут понадобиться при вычислениях. Например, необходимо определить количество книг по цене 23,75 грн., которые необходимо продать, чтобы объем продаж составил 10000,00 грн.
2. Выделите ячейку листа (B3), в которой содержится формула (эта ячейка появится в поле "Установить в ячейке" в окне диалога Подбор параметра). Выполните команду Сервис/Подбор параметра. Открывается окно диалога Подбор параметра..
3. Введите в текстовое поле Значение число, соответствующее объему продаж - 10000. Переместите курсор в текстовом поле Изменяя значения ячейки. Выделите ту ячейку, в которой должен содержаться ответ (переменная ячейка). Ее содержимое будет подобрано и подставлено в формулу командой Подбор параметра. Выделенная ячейка (B2) выделяется на листе рамкой. Нажмите кнопку ОК, чтобы найти решение.
После завершения итерационного цикла в окне диалога Результат подбора параметра появляется сообщение, а результат заносится в ячейку листа. Решение показывает, что для достижения объема продаж 10000 грн. необходимо продать 421 книгу по цене 23,75 грн. Для закрытия окна диалога Результат подбора параметра щелкните на кнопке ОК.
Команда Поиск решения Для решения сложных задач, требующих применения линейного и нелинейного программирования, а также методов исследования операций применяется надстройка - Поиск решения. Чтобы использовать надстройку Поиск решения не обязательно знать методы программирования и исследования операций, но необходимо определять, какие задачи можно решать этими методами. Пользователь должен уметь с помощью диалоговых окон надстройки Поиск решения правильно сформулировать условия задачи, и если решение существует, то “Поиск решения” отыщет его. В основе надстройки лежат итерационные методы. В том случае, когда оптимизационная задача содержит несколько переменных величин, для анализа сценария необходимо воспользоваться надстройкой Поиск решения. “Поиск решения” позволяет использовать одновременно большое количество изменяемых ячеек (до 200) и задавать ограничения для изменяемых ячеек. Общие свойства, которые характерны для задач, решаемых с помощью надстройки Поиск решения: · Существует единственная целевая ячейка, содержащая формулу, значение которой должно быть сделано максимальным, минимальным или же равным, какому-то конкретному значению. · Формула в этой целевой ячейке содержит ссылки на ряд изменяемых ячеек. Поиск решения заключается в том, чтобы подобрать такие значения переменных в изменяемых ячейках, которые бы обеспечили оптимальное значение для формулы в целевой ячейке. · Может быть задано некоторое количество ограничений — условий или соотношений, которым должны удовлетворять некоторые из изменяемых ячеек. Постановка задачи Первым шагом при работе с командой Поиск решения является создание специализированного листа. Для этого необходимо создать целевую ячейку, в которую вводится основная формула. Кроме того, лист может включать другие значения и формулы, использующие значения целевой и переменных ячеек. Формула в целевой ячейке должна опираться в вычислениях на значения переменных ячеек. После того, как задача оптимизации будет подготовлена на листе, можно приступать к работе.
Диспетчер сценариев «что – если» При работе с командами Подбор параметра и Поиск решения не существует удобного способа сравнения результатов вычислений – при каждом изменении данных предыдущее значение пропадает. Чтобы устранить эти ограничения, разработчики Excel создали Диспетчер сценариев, помогающий работать с несколькими моделями «что – если». Командой Сервис/Сценарии можно создавать новые и просматривать существующие сценарии для решения задач, и отображать консолидированные отчеты. Создание сценария Создание сценариев происходит следующим образом: · Выполните команду Сервис/Сценарии. Открывается изображение окна диалога Диспетчер сценариев.
· Нажмите кнопку Добавить, чтобы создать первый сценарий. Откроется окно диалога Добавление сценария.
· Введите Лучший вариант (или любое другое имя) в поле Название сценария, затем с помощью окон диалога введите изменяемые ячейки. Когда этот сценарий будет готов, введите следующий. · Нажмите кнопку Добавить, чтобы создать второй сценарий. Введите название Худший вариант. После завершения создания двух сценарием можно приступить к просмотру результатов. · Закройте окно диалога Диспетчер сценариев кнопкой Закрыть. Просмотр сценария · Выполните команду Сервис/Сценарии. Открывается окно диалога: · Выберите из списка сценарий для просмотра. · Нажмите кнопку Вывести. Excel заменяет содержимое ячеек листа значениями из сценария и отображает результаты на листе. · Выберите из списка другие сценарии и воспользуйтесь кнопкой Вывести для сравнения результатов моделей «что – если». После завершения нажмите кнопку Закрыть. Значения последнего активного сценария остаются в ячейках листа. Создание отчетов по сценарию Сравнивать различные сценарии можно, переходя от сценария к сценарию с помощью кнопки показать в окне диалога Диспетчер сценариев, но иногда возникает необходимость в создании отчета с обобщенной информацией о различных сценариях листа. Эту задачу можно выполнить с помощью кнопки Отчет в окне диалога Диспетчер сценариев. Созданный сводный отчет будет автоматически отформатирован и скопирован на новый лист текущей книги. · Выполните команду Сервис/Сценарии. Откроется окно диалога Диспетчер сценариев. · Нажмите кнопку Отчет. Открывается окно диалога Отчет по сценарию, в котором предлагается выбрать ячейки, входящие в отчет, а также его тип. Отчет типа структура представляет собой форматированную таблицу, которая выводится на отдельном листе. Отчет сводная таблица является специальной таблицей, которую можно настраивать за счет перестановки столбцов и строк.
Пусть предприятие (например, мебельная фабрика) производит столы и стулья. Расход ресурсов на их производство и прибыль от их реализации представлены ниже:
Кроме того, на производство 80 столов заключен контракт с муниципалитетом, который, безусловно, должен быть выполнен. Необходимо найти такую оптимальную производственную программу, чтобы прибыль от реализации продукции была максимальной. Пусть x1 – количество столов; х2 – количество стульев. Тогда система ограничений и целевая функция запишутся следующим образом: 180×1 + 20х2 max (целевая функция ); 0.5×1 + 0.04х2 200 (ограничения по древесине); 12×1 + 0.6х2 1800 (ограничения по труду); x1 80 (контракт с муниципалитетом); x1 0; х2 0; x1, х2 – целые числа. Для решения задачи в Excel запишем ее виде, представленном на рис. 3.4. Рис. 3.4. Запись исходных данных для решения задачи линейной оптимизации Для решения задачи вызовем меню Сервис-Поиск решения (Tools-Solver). В открывшемся диалоговом окне Поиск решения (рис. 3.5.) укажем: адрес целевой ячейки (в нашем примере D5); диапазон искомых ячеек (А2:A3); ограничения: А2>=80 A2:A3=целое A2:A3>=0 В2<=D2 B3<=D3 . Добавления, изменения и удаления ограничений производятся с помощью кнопок Добавить, Изменить, Удалить (Add, Change, Delete). Для нахождения оптимального решения нажмем кнопку Выполнить (Solve). В результате в таблице получим значение целевой функции – 42400 млн руб. при x1 = 80 и x2 = 1400. Рис. 3.5. Диалоговое окно Поиск решения Диалоговое окно Результаты поиска решения позволяет (рис. 3.6.): сохранить на текущем рабочем листе найденное оптимальное решение; восстановить первоначальные значения; сохранить сценарий; выдать отчеты по результатам, устойчивости, пределам, необходимые для анализа найденного решения. Рис.3.6. Рабочий лист с найденным оптимальным решением
Популярное: Как вы ведете себя при стрессе?: Вы можете самостоятельно управлять стрессом! Каждый из нас имеет право и возможность уменьшить его воздействие на нас... Генезис конфликтологии как науки в древней Греции: Для уяснения предыстории конфликтологии существенное значение имеет обращение к античной... Модели организации как закрытой, открытой, частично открытой системы: Закрытая система имеет жесткие фиксированные границы, ее действия относительно независимы... ©2015-2024 megaobuchalka.ru Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав. (1334)
|
Почему 1285321 студент выбрали МегаОбучалку... Система поиска информации Мобильная версия сайта Удобная навигация Нет шокирующей рекламы |