Как сделать сценарий в экселе

Сценарии в EXCEL

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

Задача

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

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

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

Решение

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

tools 203

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

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

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

tools 204

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

likbezpng 1

likbezpng 2

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

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

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

tools 205

likbezpng 3

likbezpng 4

likbezpng 5

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

likbezpng 7

likbezpng 6

likbezpng 8

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

Источник

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

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

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

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

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

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

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

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

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

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

2a27fe32 6c58 456f 84ec 2e4e4cf7a096

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

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

75ba4ea1 5494 47b5 bc8a 23d4e9182588прогнозом? Анализ «что если»»/>

59ac5a3b e76f 40c9 bbb6 734e8d708682

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

e21f155c 99c4 4e3e 9803 948fdbca4f33

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

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

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

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

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

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

69a1e98c 2edd 4239 99e6 ccb162300899

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

bc286294 2b55 4b89 b31e bae08d706068

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

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

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

c5e7ca56 6a2e 4954 87fe 45ba3d6d49dd

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

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

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

a8023cfa 3435 4308 b761 1f180fd83433

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

97ca3aaf b401 4386 826f cc3822d2ebf5

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

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

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

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

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

1b4cc464 6126 43cb 9344 c06a9ffda436

b0c991f3 b740 457d 8012 be7306adfbb7

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Источник

Общие сведения о сценариях Office в Excel

Автоматизация повторяющихся задач с помощью сценариев Office в Excel в Интернете. Записывите свои действия, а затем воспроизведение в любое время. Вы также можете редактировать сценарии по мере изменения рабочего процесса. Сценарии Office хранятся в облаке, что позволяет при необходимости обновлять любые книги.

При записи действий с помощью записи действия создается сценарий. К таким действиям относятся ввод текста или чисел, выбор ячеек или команд на ленте или в меню, форматирование ячеек, строк и столбцов, форматирование данных в виде таблиц Excel и так далее. Когда все будет готово, вы увидите порядок в области задач, в которую вы можете ввести описательный список всех только что предпринятых действий. Для записи и запуска сценариев Office не требуется опыт кодирования. Если вы решите изменить действия, это можно сделать в редакторе кода, где можно изменить код TypeScript сценария. Узнайте все о написании скриптов с помощью редактора кода в записи, редактировании и создании скриптов Office в Excel в Интернете.

Активная Microsoft 365 подписки с коммерческой или учебной Microsoft 365, которая имеет доступ Microsoft 365 классическим приложениям Office. Это подразумевает выполнение указанных ниже действий.

Приложения Microsoft 365 для бизнеса

Microsoft 365 бизнес стандартный

Приложения Microsoft 365 для предприятий

Office 365 профессиональныйplus для устройств

Office 365 корпоративный E3

Office 365 корпоративный E5

OneDrive для бизнеса.

Проверка подлинности сервера. При первом запуске программы записи скриптов или редактора кода необходимо проверить подлинность на сервере, чтобы сохранить сценарии в облаке. Во sign in to your Microsoft account as usual, then when you see the Permissions requested prompt, choose Accept to continue.

Примечание: После настройки правильного параметра в Центре администрирования конечные пользователи с соответствующей лицензией смогут получить доступ к этой функции. Эта функция пока недоступна в Microsoft 365 для государственных органов США и клиентов, которые размещены на данных в одном из новых региональных геоцентров обработки данных.

Начало работы

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

7045be69 4a57 4c02 bba6 c1a23cd3f87d

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

a919a7aa 696c 4ae9 82d4 8b09f6a509d5

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

e7508e35 db1d 4e2f 8c47 b61378d08f40

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

1956dce2 bfac 4114 b178 d03230c1e56a

Воспроизведение сценария Office

Если на экране еще нет области редактора кода, ее можно запустить из средства > сценариев > редактора кода.

4e66b68c d735 4590 9bef 7227edcc34f7

Дважды щелкните сценарий, который вы хотите запустить, и в области кода будет виден код TypeScript.

730e84c1 aff3 4de9 9e07 749582d4d6a6

Примечание: В этой теме не рассматриваются вопросы редактирования и написания собственного кода TypeScript, но вы можете просмотреть нашу техническую документацию по сценариям Office.

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

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

0391a7bd 2e50 46a7 bc31 ca8a50bebee5

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

Script Details (Сведения о сценарии). Здесь вы сможете в общих сведениях о сценарии, например в описании и дате последнего изменения. Описание можно изменить, просто щелкнув его.

Logs — журнал сценария.

Параметры редактора: позволяет изменить тему редактора, название шрифта, размер шрифта и другие параметры. Скорее всего, вам стоит оставить их без них, пока не разберется со сценариями Office.

Сведения о службе сценариев Office. Вам не нужно ничего изменять.

Возможные ошибки

Важно знать, что при записи сценария Office в записи сценария записывают почти все поддерживаемые действия, которые вы принимаете. Поэтому при ошибке последовательности, например нажатии кнопки, которую не нужно нажимать, оно будет записываться в записи сценария. Для этого можно повторно записать всю последовательность или изменить код TypeScript. Поэтому перед записью процесса следует хорошо проработать его. Чем точнее вы зафиксировали последовательность, тем эффективнее она будет запускаться при ее воспроизведениях.

При первой записи сценария может быть хорошо, но некоторые действия могут быть неудались при попытке их воспроизведения. Например, в примере, где некоторые примеры данных были отформатированы как таблица, код может не сбой при запуске в обновленной таблице, так как Excel не позволяет перекрывать таблицы. На этом этапе редактор кода отобразит сообщение об ошибке.

6b84c2c1 db7f 425c a6e3 55acf956bf7b

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

c30653c0 a7c2 4aeb a13d c1db04432802

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

1c6b9f7d c7f6 495c 84e9 0f4b7b112fc8

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Источник