Что заполнить в окне значение ячеек сценария

Использование сценариев

Сценарий LibreOffice Calc представляет собой ряд значений ячеек, которые можно использовать в расчётах. Каждому сценарию на листе назначается имя. На одном и том же листе можно определить несколько сценариев, с разными значениями ячеек в каждом из них. Затем можно просто переключать наборы значений ячеек по их именам и немедленно наблюдать результаты. Сценарии служат для проверки вопросов типа «что если».

Создание собственных сценариев

Для создания сценария выделите все ячейки, содержащие данные для сценария.

Select the cells that contain the values that will change between scenarios. To select multiple cells, hold down the Command Ctrl key as you click each cell.
Выберите команду Сервис — Сценарии . Будет открыто диалоговое окно Создание сценария .
Введите имя нового сценария и не изменяйте другие поля с их значениями по умолчанию. Нажмите кнопку «OK», чтобы закрыть диалоговое окно. Будет автоматически активирован новый сценарий.

Использование сценариев

Сценарии можно выбрать в навигаторе.

Откройте навигатор с помощью значка Навигатор на стандартной панели.
Click the Scenarios icon in the Navigator.

В навигаторе можно увидеть определённые сценарии и комментарии, введённые при их создании.

Дважды щёлкните по имени сценария в навигаторе для применения этого сценария к текущему листу.
Для удаления сценария щёлкните правой кнопкой мыши по его имени в навигаторе и выберите Удалить .
Для изменения сценария щёлкните правой кнопкой мыши по имени в навигаторе и выберите Свойства .
Для скрытия границ ряда ячеек, которые являются частью сценария, откройте диалоговое окно Свойства для каждого сценария, воздействующего на ячейки, и удалите флажок «Показать рамку». При скрытии границ на листе также исчезает список для выбора сценариев.

Если требуется узнать, какие значения в сценарии влияют на другие величины, выберите Сервис — Зависимости — Зависимые ячейки . При этом отображаются стрелки, ведущие к ячейкам, которые непосредственно зависят от текущей ячейки.

Переключение между различными наборами значений с помощью сценариев

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

Если у нескольких пользователей есть определенные данные, которые вы хотите использовать в сценариях, то можно добавить их в отдельные книги и объединить сценарии из нескольких книг.

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

Управление сценариями выполняется с помощью диспетчера сценариев в группе Анализ «что если» на вкладке Данные.

Типы анализа «что если»

В Excel предлагаются средства анализа «что если» трех типов: сценарии, таблицы данных и подбор параметров. В сценариях и таблицах данных берутся наборы входных значений и определяются возможные результаты. Подбор параметров отличается от сценариев и таблиц данных тем, что при его использовании берется результат и определяются возможные входные значения для его получения.

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

Помимо этих трех средств можно установить надстройки для анализа «что если», например надстройку Поиск решения. Эта надстройка похожа на подбор параметров, но позволяет использовать больше переменных. Вы также можете создавать прогнозы, используя маркер заполнения и различные команды, встроенные в Excel. Для более сложных моделей можно использовать надстройку Пакет анализа.

Создание сценариев

Предположим, вам требуется создать бюджет, но доходы точно не известны. С помощью сценариев можно определить различные возможные значения дохода, а затем переключаться между сценариями для выполнения анализа «что если».

Предположим, например, что в худшем случае ожидается доход в 50 000 ₽, а стоимость проданной продукции составляет 13 200 ₽, в результате чего получается 36 800 ₽ валовой прибыли. Чтобы определить этот набор переменных в качестве сценария, сначала введите на лист значения, как показано на следующем рисунке:

Сценарий: настройка сценария с изменяющимися ячейками и ячейками результата

Изменяемые ячейки содержат введенные значения, а ячейка результата — формулу, основанную на изменяемых ячейках (на этом рисунке в ячейке B4 указана формула =B2-B3).

Затем в диалоговом окне Диспетчер сценариев эти значения можно сохранить как сценарий. Выберите Данные > Анализ «что если» > Диспетчер сценариев > Добавить.

Мастер диспетчера сценариев

В диалоговом окне Имя сценария назовите сценарий Худший случай и укажите, что ячейки B2 и B3 являются значениями, изменяющимися между сценариями. Если выбрать изменение ячеек на листе перед добавлением сценария, диспетчер сценариев автоматически вставляет ячейки. В противном случае вы можете ввести их вручную или использовать диалоговое окно выбора ячеек справа от диалогового окна Изменение ячеек.

Настройка сценария наихудшего случая

Примечание: Хотя в этом примере только две изменяющихся ячейки (B2 и B3), в сценарии может быть до 32 ячеек.

Защита: вы также можете защитить сценарии, выбрав нужные параметры в разделе «Защита».

Чтобы запретить изменение сценария на защищенном листе, установите флажок запретить изменения.
Чтобы при защите листа сценарий не отображался, установите флажок скрыть.

Примечание: Эти параметры применяются только к защищенным листам. Дополнительные сведения о защищенных листах см. в разделе Защита листа.

Теперь предположим, что в лучшем случае ожидается доход в 150 000 ₽, а стоимость проданной продукции составляет 26 000 ₽, в результате чего получается 124 000 ₽ валовой прибыли. Чтобы определить этот набор значений как сценарий, создается другой сценарий с именем «Лучший случай» и для него вводятся другие значения ячеек B2 (150 000) и B3 (26 000). Поскольку ячейка валовой прибыли (B4) представляет собой формулу — разницу между доходами (B2) и расходами (B3) — ячейка B4 для сценария «Лучший случай» не изменяется.

Переключение между сценариями

После сохранения сценария он становится доступным в списке сценариев, которые можно использовать для анализа «что если». Если используются значения, приведенные на предыдущем рисунке, при отображении сценария «Лучший случай» значения на листе будут изменены, как показано на рисунке ниже.

Настройка сценария наилучшего случая

Объединение сценариев

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

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

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

Диалоговое окно

При получении разных сценариев из различных источников в каждой из книг необходимо использовать одинаковую структуру ячеек. Например, значение доходов всегда должно находиться в ячейке B2, а значение расходов — в ячейке B3. Если вы используете разные структуры для сценариев из различных источников, слияние будет сложно выполнить.

Совет: Рекомендуется сначала создать сценарий, а затем разослать коллегам копию книги с ним. Это позволяет обеспечить одинаковую структуру всех сценариев.

Сводные отчеты по сценариям

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

Диалоговое окно

Сводный отчет по сценариям, основанный на двух приведенных выше примерах, может выглядеть так:

Отчет по сценарию со ссылками на ячейки

Как можно заметить, что Excel автоматически добавил уровни группировки, которые можно разворачивать и сворачивать.

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

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

Отчет по сценарию с именованными диапазонами

Отчета сводной таблицы по сценариям

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Сценарии в EXCEL

Сценарии — это инструмент MS EXCEL из группы Анализ «что-если» ( Вкладка Данные/ Группа Работа с данными ). Диспетчер сценариев позволяет создавать и подставлять различные значения исходных данных в модель, а также составлять автоматические отчеты, отображающие результаты вычислений.

Рассмотрим инструмент Диспетчер сценариев на конкретном примере.

Задача

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

Необходимо составить Сценарии для следующих вариантов:

размер кредита – $8 000, ежегодн. выпл. – $1 500, годовая проц. ставка – 16%;размер кредита – $12 000, ежегодн. выпл. – $2 000, годовая проц. ставка – 15%;размер кредита – $15 000, ежегодн. выпл. – $3 000, годовая проц. ставка – 14%;размер кредита – $20 000, ежегодн. выпл. – $3 500, годовая проц. ставка – 13%;размер кредита – $25 000, ежегодн. выпл. – $4 000, годовая проц. ставка – 12%.

Необходимо также создать итоговый отчет по сценариям в виде структуры на отдельном рабочем листе.

Решение

Создадим простую модель для расчета количества периодов выплаты.

Наша «Модель» состоит из одной формулы =КПЕР(B17;B18;B16) и 3-х значений аргументов (исходных данных).

Всего необходимо рассчитать модель для 5-и различных сценариев. В каждом сценарии задаются различные значения суммы Кредита, Ставки и суммы Ежегодных выплат.

Сначала рассмотрим создание сценариев без использования Диспетчера сценариев.

Недостатком этого подхода является, то что нам пришлось создать 5 одинаковых моделей. В случае более сложных моделей, такой подход является очень затратным (время создания копий модели и дополнительные вычислительные ресурсы для одновременного расчета 5-и моделей), а также не гибким: при изменении модели ее придется переделывать в нескольких экземплярах (переписывать функции 5 раз).

Но, не будем сдаваться сразу — усовершенствуем ввод наших исходных данных в модель. Будем выбирать нужный сценарий с помощью Выпадающего списка (см. Файл примера лист Без сценариев2 ).

Теперь мы избавились от лишних копий модели. Исходные данные вводятся в модель не непосредственно в предназначенные для этого ячейки, а выбираются с помощью формулы =СМЕЩ(D16;;ПОИСКПОЗ($B$15;$D$15:$H$15;0)-1) из отдельного диапазона с исходными данными.

Сравним рассмотренный нами подход с работой Диспетчера сценариев.

Для создания сценария сделайте следующее:

Вызовите Диспетчер сценариев ( Данные/ Работа с данными/ Анализ «что-если» );

Нажмите кнопку Добавить;
Введите название сценария и диапазон ячеек, в которые должны подставляться значения исходных данных (см. Файл примера лист Сценарии );

Нажмите ОК, откроется еще одно диалоговое окно для ввода данных;

Нажмите Добавить, чтобы ввести еще один сценарий или ОК, чтобы вернуться в окно Диспетчера сценариев.

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

Нажмите кнопку Отчет. чтобы вывести на отдельный лист все сценарии и полученные результаты. Результаты могут быть выведены в виде Структуры или Сводной таблицы .

Не забудьте указать ячейки результата (в которые выводятся результаты вышей модели). Для указания несвязных диапазонов используйте клавишу CTRL .

Автоматически созданный отчет в виде Сводной таблицы , конечно, требует доработки.

Основным недостатком инструмента Сценарии является тот факт, что значения исходных данных не находятся на листе, а скрыты в Диспетчере сценариев. Конечно, их можно вывести в отчете, но менять, по-моему, их в нем не очень удобно.