Создание нового сценария
Откройте диалоговое окно Диспетчер сценариев, выполнив команду Данные/(Работа с данными)Анализ «что-если»/Диспетчер сценариев.
Нажмите в нем кнопку Добавить. Откроется диалоговое окно Добавление сценария.
В поле Имя введите Лучший случай. В поле Изменяемые ячейки укажите В1:В2. Нажмите кнопку ОК.
Откроется диалоговое окно Значения ячеек сценария. В поле Валовая_прибыль введите 150,000, а в поле Стоимость_товара 26,000. Нажмите кнопку ОК.
В открывшемся окне Диспетчер сценариев отобразится список доступных сценариев.
Просмотр сценария.
Не закрывая окна Диспетчер сценариев, можно отобразить на листе любой сценарий.
Для просмотра сценария на листе необходимо:
1. Выделить его название (Лучший случай) в списке сценариев; 2. Нажать кнопку Вывести. Выбранный сценарий отобразиться на листе.
Нажмите кнопку Закрыть.
Изменение сценария
Уже созданные сценарии можно изменять.
Любой сценарий можно изменить или дополнить. Для этого:
Выполните команду Данные/(Работа с данными)Анализ «что-если»/Диспетчер сценариев.
В окне Диспетчер сценариев выберите нужный сценарий и нажмите кнопку Изменить.
Откроется диалоговое окно Изменение сценария, в котором можно изменить имя сценария а также диапазон ячеек. Нажмите кнопку ОК.
В полях открывшегося окна Значения ячеек сценария можно ввести другие значения изменяемых ячеек, после чего следует нажать кнопку ОК. 5. Измененный сценарий отобразится в окне Диспетчер сценариев.
Сведение сценариев
Для удобства просмотра результаты всех сценариев можно свести на один лист, создав отчет по сценариям. Для этого следует выполнить:
Команду Данные/(Работа с данными)Анализ «что-если»/Диспетчер сценариев.
В окне Диспетчер сценариев нажать кнопку Отчет.
Откроется диалоговое окно Отчет по сценариям.
Можно выбрать тип создаваемого отчета (структура или сводная таблица). Установите флажок около типа отчета Структура.
В поле Ячейки результатов необходимо указать, какие ячейки показывать в отчете. Введите В3.
Созданный отчет будет автоматически помещен на новый лист текущей книги.
Поиск решений
При анализе данных может потребоваться подобрать не одно значение, а целую группу значений исходя из наперед заданного результата и выполнения определенных заданных условий.
Для решения таких задач используется надстройка Поиск решения.
Данная надстройка позволяет решать задачи нахождения оптимального значения для формулы, содержащейся в одной ячейке, называемой целевой.
Так же данная надстройка решает задачи подбора группы ячеек, прямо или косвенно связанных с формулой в целевой ячейке.
Для того, чтобы надстройка Поиск решения загружалось сразу при запуске Excel, выполните следующие действия
Нажмите кнопку Office . В открывшемся меню нажмите кнопку
Параметры Exсel. Откроется диалоговое окно Параметры
Exсel.
В открывшемся окне выберите пункт Надстройки.
В поле Управление из раскрывающегося списка выберите Надстройки Exсel. Нажмите кнопку Перейти. Откроется диалоговое окно Надстройки.
В списке Доступные надстройки установите флажок напротив надстройки Поиск решений. Нажмите кнопку ОК.
Теперь рассмотрим следующее задание.
В таблице необходимо подобрать объемы закупки товаров (подобрать значения ячеек В2:В4), чтобы при известных ценах (значения в ячейках С2:С4) общая сумма затрат (результат в ячейке D5) составила 2500 руб. При этом товара «Марс» необходимо купить не менее 120, а товара «Мечта» в два раза больше, чем товара «Люкс». Эта задача решается с помощью надстройки Поиск решения.
Создадим таблицу.
Введем названия исходных данных. Укажем значения в ячейках С2:С4
В ячейку D2 введем формулу =В2*С2.
Аналогично определите формулы для ячеек D3 и D4.
В ячейке D5 введем =сумм(D2:D4).
Перейдем к поиску решения.
В данном случае целью будет достижение заданного значения в ячейке D5 (общая сумма затрат составляет 2500 руб.), а условиями: В3>=120 (товара «Марс» необходимо купить не менее 120), В2=2*В4 ( товара «Мечта» необходимо купить в два раза больше, чем товара «Люкс»).
Выделите ячейку D5. Выполните команду Данные/(Анализ)Поиск решения
.
Откроется диалоговое окно Поиск
решения.
В поле Установить целевую ячейку укажите D5. Для указания ячеек можно поставить курсор в поле , а затем на листе выделить нужные ячейки.
Для целевой ячейки можно выбрать одно из следующих действий:
чтобы максимизировать значение путем изменения значений влияющих ячеек, установить переключатель в положение максимальному значению; • чтобы минимизировать значение конечной ячейки путем изменения значений влияющих ячеек, установить переключатель в положение минимальному значению;
чтобы установить значение в конечной ячейке равным некоторому числу, установить переключатель в положение значению и ввести в соответствующее поле требуемое число.
В данном примере установите флажок в положении значению и введите число 2500.
В поле Изменяя ячейки следует указать, какие ячейки влияют на результат вычислений в целевой ячейке. В нашем примере это ячейки В2:В4.
Для указания ячеек можно поставить курсор в поле , а затем на листе выделить нужные ячейки.