Отчет по сценариям

Excel позволяет создавать достаточно сложные модели, включающие в себя любое необходимое количество сценариев и до 32 переменных в каждом сценарии. Делается это с помощью создания отчетов по сценариям. Отчет показывает значения, которые каждый сценарий назначает изменяемым ячейкам. Кроме того, он может представить воздействие каждого сценария на одну или несколько ячеек результата.

Диспетчер сценариев предлагает два типа отчетов по сценариям:

• 1) отчет Структура — это средство, позволяющее отслеживать все варианты. В нем приводятся имя изменяемых и результирующих ячеек (если имена назначены этим ячейкам). Имена сценариев выводятся в качестве заголовков столбцов, а столбцы появляются в порядке определения сценариев. Изменяемые ячейки для каждого сценария выделяются серым цветом, что позволяет с первого взгляда определить, какими сценариями контролируются конкретные ячейки в модели «что-если». Выше и левее итогового отчета выводятся символы структуры, позволяющие отображать и скрывать детальные данные;
• 2) отчет Сводная таблица — предоставляет дополнительные инструменты анализа «что-если», позволяя работать с элементами самого отчета. Сводная таблица является инструментом, позволяющим с помощью мыши выполнять напрямую действия с ячейками для смешивания и сравнения различных сценариев в отчете и наблюдать получаемые при этом значения в ячейках результата. Этот инструмент особенно полезен для анализа сложных моделей, которые включают в себя сценарии с различными множествами изменяемых ячеек, созданных разными пользователями.

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

Подбор параметра. Команда Подбор параметра позволяет определить неизвестное значение (параметр), которое будет давать требуемый результат. Технология использования команды следующая:

• • решить нужную задачу с каким-либо начальным значением параметра;
• • выбрать ДАННЫЕ / Анализ «что-если» / Подбор параметра;
• • в диалоговом окне Подбор параметра в иоле Установить в ячейке

задать абсолютную ссылку на ячейку, содержащую расчетную формулу, а в поле Значение — то значение, которое следует получить в качестве результата формулы;

• • в поле Изменяя значение ячейки ввести ссылку на ячейку с параметром;
• • нажать кнопку ОК или клавишу Enter — на экране появится диалоговое окно Результат подбора параметра;
• • для сохранения найденного значения нажать кнопку ОК. Для восстановления значения, которое было в ячейке с параметром до использования команды Подбор параметра, нажать кнопку Отмена.

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

По умолчанию команда Подбор параметра прекращает вычисления, когда выполняется 100 итераций, или при получении результата, который находится в пределах 0,001 от заданного целевого значения.

Команда Подбор параметра находит только одно решение, даже если задача имеет несколько решений.

Поиск решений. Поиск решений может применяться для решения задач, которые включают много изменяемых ячеек, и помогает найти комбинацию переменных, которые максимизируют или минимизируют значение в целевой ячейке. Он также позволяет задать одно или несколько ограничений условий, которые должны выполняться при поиске решений. Для запуска этого инструмента следует выполнить команду ФАЙЛ / Параметры / Надстройки / Надстройки Excel — Перейти… и в диалоговом окне Доступные надстройки выбрать Поиск решений. Чтобы вызвать диалоговое окно Поиск решения, нужно выбрать пиктограмму Поиск решения группы ДАННЫЕ / Анализ (рис. 6.19).

Рис. 6.19. Диалоговое окно «Параметры поиска решения».

В диалоговом окне Параметры поиска решения в поле Оптимизировать целевую функцию задается цель, которую должен достичь поиск решения (например, значение в указанной ячейке должно стать равным минимальному значению). Целевая ячейка в поле Оптимизировать целевую функцию может быть задана ссылкой или именем. Поиск решения может искать конкретное значение целевой функции — тогда это значение нужно задать в ноле Оптимизировать целевую функцию, установив переключатель До в положение Значения. В этом случае, задав только изменяемую ячейку без указания ограничений, можно использовать Поиск решения вместо команды Подбор параметра.

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

В поле Изменяя ячейки переменных следует задать ячейки с переменными. Можно указать ссылки на ячейки или их имена. Если ячейки находятся в несмежных диапазонах, их следует разделять точкой с запятой. Поле Изменяя ячейки переменных нельзя оставить пустым и указанные в нем ячейки обязательно должны влиять на значение целевой ячейки.

Последний шаг определения поиска решений — задание ограничений. Он не является обязательным. Чтобы задать ограничения, следует в окне Параметры поиска решения нажать кнопку Добавить и заполнить диалоговое окно Добавление ограничения. Ограничение состоит из трех компонентов: ссылки на ячейку, оператора сравнения и значения ограничения. В левой части от оператора сравнения кроме ссылки на ячейку может также задаваться ссылка на диапазон. В правой части может задаваться диапазон (той же размерности, что и в левой части), ссылка па ячейку или константное значение.

После заполнения диалогового окна Параметры поиска решения следует нажать кнопку Найти решение. При нахождении оптимального решения на экран выводится диалоговое окно Результаты поиска решения. Значения, отображаемые на листе, представляют собой оптимальное решение задачи. Можно либо оставить эти значения на листе, установив переключатель Сохранить найденное решение и нажав кнопку ОК, либо восстановить исходные значения, нажав кнопку Отмена или установив переключатель Восстановить исходные значения и нажав кнопку ОК. Можно также сохранить найденные значения в качестве сценария.

Найденные результаты решения задачи могут быть нецелыми. Их можно округлить или добавить новые ограничения, при которых результаты должны быть целыми числами. В последнем случае в качестве операции сравнения в окне Добавление ограничений следует выбрать Цел. Использование целочисленных ограничений в задаче поиска решений может увеличить сложность задачи в геометрической прогрессии, что приводит к существенным временным затратам. Некоторые задачи могут быть решены только при использовании целочисленных ограничений. В частности, целочисленные решения являются обязательными для задач, в которых переменные могут принимать только два значения, например 0 и 1. В этом случае в диалоговом окне Добавление ограничения можно использовать оператор сравнения Бин.

Создавая отчет, основанный на найденном решении, можно выбрать тип отчета в поле Отчеты. Отчет будет помещен на новый лист книги. Если решение не найдено, будут доступны только некоторые отчеты или они вообще не будут доступны.

Поиск решений может представлять свои результаты в виде трех отчетов: Результаты, Устойчивость и Пределы (рис. 6.20). Для генерации одного или нескольких отчетов следует выбрать их названия в диалоговом окне Результаты поиска решений. Каждый отчет сохраняется на отдельном листе текущей книги, а имена отчетов отображаются на ярлычках.

Рис. 6.20. Диалоговое окно «Результаты поиска решения».

Отчет по устойчивости содержит информацию о том, насколько целевая ячейка чувствительна к изменениям ограничений и переменных. В нем показывается, как целевая функция реагирует на увеличение значений изменяемых ячеек на единицу и на увеличение значения ограничения на единицу.

Отчет по результатам содержит целевую ячейку, список изменяемых ячеек и ограничений. В нем для каждого ограничения выводятся «статус» и «разница». Разница — это разность между значением, выводимым в ячейке ограничения при получении решения, и числом, заданным в правой части формулы ограничения. Статус может принимать три состояния: Связанное (ограничение, для которого значение разницы равно 0), Несвязанное (ограничение, которое было выполнено с ненулевым значением разницы) и Невыполненное.

Отчет по пределам сообщает о том, в каких пределах значения изменяемых ячеек могут быть увеличены или уменьшены без нарушения ограничений задачи. Для каждой изменяемой ячейки он содержит оптимальное значение, а также наименьшее и наибольшее значения, которые ячейка может принимать без нарушения ограничений.

Просмотр промежуточных результатов поиска решения возможен, если выполнить следующие действия:

• 1) открыть диалоговое окно Параметры поиска решения и определить основные компоненты, необходимые для поиска решения;
• 2) после постановки задачи нажать кнопку Параметры;
• 3) в диалоговом окне Параметры установить флажок Показывать результаты итераций и нажать кнопку ОК;
• 4) в диалоговом окне Параметры поиска решения нажать кнопку Найти решение.
• 5) в диалоговом окне Показать предварительное решение выполнить одно из указанных ниже действий:
  • — для остановки поиска решения и вывода на экран диалогового окна — Результаты поиска решения, кнопка Остановить;
  • — для продолжения процесса поиска решения и просмотра следующего варианта решения — кнопка Продолжить.