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

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

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

Группа задач «Анализ «что-если»

Анализ «что-если» – это анализ влияния изменения значений ячеек на результат формул.

• Независимую ячейку с начальным значением;

• Зависимую ячейку с формулой от независимой ячейки.

• Требуемое значение зависимой переменной. Результат данного сценария –значение независимой переменной, при котором достигается требуемое значение зависимой переменной.

Обычно используют таблицы с одной независимой переменной или с двумя независимыми переменными. Используется 3 подхода – расположение значений независимой переменной в строке или в столбце.

-Расположение в столбце

Шаг 1. Исходное расположение информации. Позиция формулы строго определена относительно диапазона значений. В нашей ситуации С3:С5 – диапазон, D2 – ячейка с формулой (вправо на 1 позицию, вверх на одну позицию).

Ячейка аргумента формулы ( А3 в данном случае) располагается произвольно.

Шаг 2. Указать диапазон : Значения переменной + формула

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

Таблица с двумя входами

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

Предположим, что создана таблица

Обратить внимание, что начальные затраты (0 год) являются отрицательными значениями, а последующие доходы – положительными.

• Произвольный новый лист можно объединить с уже имеющимися сценариями.

• Вызвать сценарии на новом листе:

В ячейках В4;В7 отобразятся значения сохраненного диапазона.

• В произвольные ячейки нового листа можно вставить формулы

=ЧПС(B2;B5;B6;B7) или =СУММ(B4:B7).

Можно менять значения в диапазоне В4;В7, соответствующие ячейки будут изменяться.

• Построим отчет по имеющемуся сценарию

Данная структура может быть сгруппирована и занимать на листе мало места.

Сводные таблицы рассмотрим на следующих занятиях.

Источник

Запись, редактирование и создание сценариев Office в Excel в Интернете Record, edit, and create Office Scripts in Excel on the web

В этом учебнике вы ознакомитесь с основами записи, редактирования и создания сценария Office для Excel в Интернете. This tutorial teaches you the basics of recording, editing, and writing an Office Script for Excel on the web. Вы запишите сценарий, применяющий форматирование к листу продаж. You’ll record a script that applies some formatting to a sales record worksheet. После этого вы измените записанный сценарий, чтобы применить дополнительное форматирование, создать таблицу и отсортировать ее. You’ll then edit the recorded script to apply more formatting, create a table, and sort that table. Эта шаблон записи с последующим изменением является важным инструментом для просмотра ваших действий Excel в виде кода. This record-then-edit pattern is an important tool to see what your Excel actions look like as code.

Необходимые компоненты Prerequisites

Перед началом работы с этим учебником у вас должен быть доступ к сценариям Office. Для этого требуется следующее: Before starting this tutorial, you’ll need access to Office Scripts, which requires the following:

Этот учебник предназначен для пользователей с начальным и средним уровнем знаний по JavaScript или TypeScript. This tutorial is intended for people with beginner to intermediate-level knowledge of JavaScript or TypeScript. Если вы впервые работаете с JavaScript, рекомендуем начать с учебника Mozilla по JavaScript. If you’re new to JavaScript, we recommend starting with the Mozilla JavaScript tutorial. Чтобы получить дополнительные сведения о среде сценариев, ознакомьтесь со статьей Среда редактора кода сценариев Office. Visit Office Scripts Code Editor environment to learn more about the script environment.

Добавление данных и запись простого сценария Add data and record a basic script

Сначала нам потребуются некоторые данные и небольшой начальный сценарий. First, we’ll need some data and a small starting script.

Создайте книгу в Excel в Интернете. Create a new workbook in Excel for the Web.

Скопируйте следующие данные о продаже фруктов и вставьте их на лист, начиная с ячейки A1. Copy the following fruit sales data and paste it into the worksheet, starting at cell A1.

Откройте вкладку Автоматизировать. Если вы не видите вкладку Автоматизировать, проверьте переполнение ленты, нажав стрелку раскрывающегося списка. Open the Automate tab. If you do not see the Automate tab, check the ribbon overflow by pressing the drop-down arrow.

Нажмите кнопку Записать действия. Press the Record Actions button.

Выделите ячейки A2:C2 (строка «Апельсины») и установите оранжевый цвет заливки. Select cells A2:C2 (the «Oranges» row) and set the fill color to orange.

Чтобы остановить запись, нажмите кнопку Остановить. Stop the recording by pressing the Stop button.

Введите в поле Имя сценария запоминающееся имя. Fill in the Script Name field with a memorable name.

Необязательно. Введите в поле Описание понятное описание. Optional: Fill in the Description field with a meaningful description. Оно используется для предоставления контекста в отношении действий сценария. This is used to provide context as to what the script does. Для этого учебника можно использовать описание «Цветовая кодировка строк таблицы». For the tutorial, you can use «Color-codes rows of a table».

Вы можете изменить описание сценария позже в области Сведения о сценарии, расположенной в меню . редактора кода. You can edit a script’s description later from the Script Details pane, which is located under the Code Editor’s . menu.

Сохраните сценарий, нажав кнопку Сохранить. Save the script by pressing the Save button.

Ваш лист должен выглядеть, как показано ниже (не волнуйтесь, если цвет отличается): Your worksheet should look like this (don’t worry if the color is different):

Редактирование существующего сценария Edit an existing script

Предыдущий сценарий окрасил строку «Апельсины» в оранжевый цвет. The previous script colored the «Oranges» row to be orange. Давайте добавим желтый цвет для строки «Лимоны». Let’s add a yellow row for the «Lemons».

В открывшейся области Сведения нажмите кнопку Изменить. From the now-open Details pane, press the Edit button.

Должен отобразиться примерно такой код: You should see something similar to this code:

Этот код получает текущий лист из книги. This code gets the current worksheet from the workbook. Затем он настраивает цвет заливки диапазона A2:C2. Then, it sets the fill color of the range A2:C2.

Диапазоны — это фундаментальная часть сценариев Office в Excel в Интернете. Ranges are a fundamental part of Office Scripts in Excel on the web. Диапазон — это непрерывный прямоугольный блок ячеек, содержащий значения, формулы и форматирование. A range is a contiguous, rectangular block of cells that contains values, formula, and formatting. Они представляют собой базовую структуру ячеек, в которой можно выполнять большинство задач сценариев. They are the basic structure of cells through which you’ll perform most of your scripting tasks.

Добавьте следующую строку в конце сценария (между местом настройки значения color и закрывающей скобкой > ): Add the following line to the end of the script (between where the color is set and the closing > ):

Протестируйте сценарий, нажав Запустить. Test the script by pressing Run. Книга должна выглядеть следующим образом: Your workbook should now look like this:

Создание таблицы Create a table

Давайте преобразуем эти данные продаж фруктов в таблицу. Let’s convert this fruit sales data into a table. Мы воспользуемся собственным сценарием для всего процесса. We’ll use our script for the entire process.

Добавьте следующую строку в конце сценария (перед закрывающей скобкой > ): Add the following line to the end of the script (before the closing > ):

Ваш сценарий должен выглядеть так: Your script should look like this:

Запустите сценарий. Run the script. Вы увидите следующую таблицу: You should see a table like this:

При повторном запуске сценария возникнет ошибка. If you re-run the script, you’ll get an error. Это связано с тем, что вы не можете создать таблицу поверх другой таблицы. This is because you cannot create a table on top of another table. Однако вы можете запустить этот сценарий на другом листе или в другой книге. However, you can run the script on a different worksheet or workbook.

Повторный запуск сценария Re-run the script

Дальнейшие действия Next steps

Выполните инструкции учебника Чтение данных книги с помощью сценариев Office в Excel в Интернете. Complete the Read workbook data with Office Scripts in Excel on the web tutorial. С его помощью вы научитесь читать данные из книги с помощью сценариев Office. It teaches you how to read data from a workbook with an Office Script.

Источник

data_client

Использование анализа «что если» в Excel на примере

Анализ «Что Если» в Excel позволяет попробовать различные значения (сценарии) для формул.
Следующий пример поможет Вам освоить Анализ «что если» быстро и легко.

Скачать рассматриваемый пример Вы можете по этой ссылке: Пример анализа «что если» в Excel.

Создание различных сценариев

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

1. На вкладке Данные выберите Анализ «что если» и выберите Диспетчер сценариев из списка.
Откроется диалоговое окно Диспетчер сценариев.

2. Добавьте сценарий, нажав на кнопку Добавить.

3. Введите имя (60% книг по высокой цене), выберите ячейку C4 (% книг, которые продаются по высокой цене) для изменяемой ячейки и нажмите на кнопку OK.

4. Введите соответствующее значение 0,6 и нажмите на кнопку OK еще раз.

5. Далее, добавьте еще 4 других сценария (70%, 80%, 90% и 100% соответсвенно).

И, наконец, ваш Диспетчер сценариев должен соответствовать картинке ниже:

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

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

Для того, чтобы легко сравнить результаты этих сценариев, выполните следующие действия:

1. Кликните по кнопке «Отчет» в Диспетчере сценариев.

2. Далее, выберите ячейку C10 (итого выручка) в качестве ячейки результата и нажмите ОК.

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

Источник

Решение задачи с помощью Диспетчера сценариев.

Решение задачи на функцию Подбор параметра

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

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

1. Создайте таблицу по образцу (Рис. 28)

Рис. 28. Исходная таблица

2. Установите курсор в ячейку С6 и выполните команду Вставка – Функция.

3. В открывшемся окне выберите категорию Финансовые и выделите функцию БС. Подтвердите выбор, нажав ОК.

4. Заполните параметры функции по образцу (Рис. 29).

Рис. 29. Параметры функции БС

5. Нажмите ОК.

Рис. 30. Параметры функции Подбор параметра

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

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

Рассмотрим второй пример. Клиент открыл счет в банке и внес начальную сумму 1000 руб. Требуется определить, через какое время размер суммы увеличится до 1500 руб., если процентная ставка в месяц составляет 5%.

1. Запустите EXCEL и создайте таблицу по образцу (Рис. 31).

Рис. 31. Исходная таблица

2. В ячейку D1 поместите размер начальной суммы – 1000 руб. Сначала определим размер накопленной суммы через 1 месяц. В ячейку D4 введите формулу, по которой определяется накопленная сумма: =D1*(1+D2/100)^D3.

После ввода формулы в ячейке будет виден результат, а в строке формул будет отображена формула (Рис. 32).

Рис. 32. Выполненный расчет

Рис. 33. Параметры функции Подбор параметра

4. Нажмите ОК и получите окончательный результат (Рис. 34).

Рис. 34. Полученный результат

Следовательно, через 8,3 месяца на счете накопиться требуемая сумма.

Решение задачи с помощью Диспетчера сценариев.

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

Задача. Фирма желает взять ссуду в банке в размере 1 500 000 руб. под 9,2% годовых на 10 лет. Определить размер ежемесячных выплат и общую сумму выплат. После этого проанализировать, как влияет изменение процентной ставки и срока кредита на ежемесячные выплаты.

1. Создайте таблицу следующего вида:

Рис. 35. Таблица к задаче о ссуде

2. Установите курсор в ячейку С6.

3. Вставьте в ячейку С6 функцию ПЛТ и заполните ее параметры (Рис. 36). Так как платежи должны быть ежемесячными, то ячейку С4 надо разделить на 12, а ячейку С5 умножить на 12. В поле ПС ячейку С3 указали с минусом, чтобы результат функции был не отрицательным.

Рис. 36. Параметры функции ПЛТ

4. В ячейку С8 введите формулу =C5*C6*12.

Проанализируем, что произойдет, если процентная ставка уменьшится, или кредит будет взят на меньшее число лет или составит меньшую сумму? Как это повлияет на ежемесячные выплаты и общую сумму?

3. В поле Изменяемые ячейки укажите диапазон $C$3:$C$5. Т.е. все исходные данные могут изменяться.

4. Защитим сценарий от изменений. Флажок Запретить изменения означает, что никто, кроме вас, не сможет изменить сценарий. Скрыть означает, что он не появится в списке сценариев. В итоге у вас будут заданы все параметры, требуемые для расчета (Рис. 37).

Рис. 37. Сценарий Минимум

5. Щелкнув на кнопке ОК, вы увидите окно диалога, отображающее изменяемые ячейки и их текущее содержание. Измените их следующим образом:

· ячейка С3 – 1 000 000

· ячейка С4 – 0,05

· ячейка С5 – 20.

Т.е. окно будет иметь следующий вид:

Рис. 38. Значения ячеек сценария Минимум

6. Нажмите кнопку ОК. Вы вернетесь в исходное окно.

7. Для создания следующего сценария, щелкните на кнопке Добавить. Появится окно диалога Добавление сценария.

8. Введите название Желаемые значения и щелкните на ОК. В окне диалога Значения ячеексценария нужно ввести новые значения:

· ячейка С3 – 1 500 000

· ячейка С4 – 0,1

· ячейка С5 – 15.

9. Создайте третий сценарий с именем – Максимум. Для него укажите следующие данные:

· ячейка С3 – 2 000 000

· ячейка С4 – 0,05

· ячейка С5 – 10.

10. После последнего набора чисел нужно щелкнуть на кнопке ОК, чтобы вернуть на экран окно Диспетчер сценариев, на этот раз содержащее список из трех созданных сценариев.

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

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

13. Создав отчеты по сценариям, можно сравнить переменные и результаты на одном рабочем листе. Для этого вызовите Диспетчер сценариев (Сервис – Сценарии).

14. Щелкните на кнопке Отчет. В появившемся окне установите флажок Структура и щелкните на ОК (Рис. 39).

Рис. 39. Создание отчета по сценариям

15. После завершения диалога EXCEL создаст новый рабочий лист Структура сценария и представит отчет, который можно распечатать и проанализировать (Рис. 40).

Источник