Как сделать сценарий в 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.

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

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

Источник: https://excel2.ru/articles/scenarii-v-ms-excel

Сценарии в Excel

31 Авг

Добрый день, уважаемые читатели и подписчики блога!

Сегодня мы поговорим о такой вещи как сценарии в Excel. Что, зачем и как.

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

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

Для строки сумма с учётом инвестиций воспользуемся функцией БС (будущая стоимость). Разделим процент годовых (8%) на количество периодов, чтобы узнать ежемесячную ставку, укажем количество периодов — 12, сумму ежемесячного пополнения и первоначальную стоимость. 

Теперь посчитаем сумму вложенных средств.

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

В выпадающем списке выбираем «Диспетчер сценариев». Нажимаем «Добавить».

В появившемся окне нужно указать:

• название сценария;
• изменяемые ячейки;
• примечание;
• настройки защиты.

После нажимаем «ОК». В этом коне появятся значения для проекта TON.

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

Нам останется нажимать кнопку «Вывести», чтобы в диапазон ячеек B9:B12 выводились соответствующие значения. Очень удобно.

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

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

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

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

Вас может заинтересовать статья — пользовательские форматы.

Источник: http://pcandlife.ru/scenarii-v-excel/

Проводим анализ данных в Excel 2010

Для чего чаще всего используется Excel? Для создания таблиц и хранения большого количества самых разных данных. Которые затем необходимо систематизировать и, чаще всего, анализировать для разных целей. Например, чтобы спрогнозировать повышение или понижение курса доллара или чего-то еще. Это позволит принять обоснованное решение. И чтобы автоматизировать данный процесс великолепно подойдет именно этот продукт Microsoft Office.

Всего есть два варианта анализа с использованием этого приложения.

Статистический анализ

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

Чтобы провести анализ данных в Excel 2010 необходимо активировать «Диспетчер сценариев». Это идет по схеме: вкладка «Данные», активируем кнопочку «Работа с данными» — «Анализ «Что если»» — «Сценарии». После вы увидите такое окошко

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

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

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

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

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

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

После заполнения данной формы также сохраните изменения. Вы автоматически вернетесь к «Диспетчеру».

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

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

Отчет представляет все изменяемые значения для любого сценария («Изменяемые») и значения формул, которые были вычислены при помощи этих значений («Результат»).

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

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

Визуальный анализ

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

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

Мы делаем это с «Датами» и «Менеджерами», но у вас, скорее всего, будут иные наименования. В графу «Значения» помещаем «Объемы продаж», «Выручку» и «Прибыль».

После этого таблица самостоятельно отформатируется и станет «лентой».

Кстати, размещение элементов в «Названии строк» играет существенную роль. В случае, если «Менеджеры» выше «Дат», то все данные будут разбиваться соответственно имен этих работников. Если выше «Даты» — то соответственно календарных дат.

Теперь необходимо провести оформление созданной таблицы. Форматируем ее, как таблицу (вкладка «Главная» — «Форматировать как таблицу»).

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

Сохраняем параметры (кнопка «Ок») и смотрим на то, что получилось.

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

Допустим, что месячная выруская каждого нашего менеджера не должна быть меньше 100 000. Просматривать все самостоятельно не нужно – это займет слишком много времени и сил. Поэтому просто проводим условное форматирование (вкладка «Вставка» — «Условное форматирование» — «Набор значков») по понравившемуся шаблону. Например, «Светофор».

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

Показатели вводим напротив каждого кружочка в «Значение», в «Тип» устанавливаем «Числа», а не «Процент».

Мы установили такой показатель, как 100 000 и 90 000, а третий выставиться автоматически, чтобы подключить оставшиеся значения. Сохраняем.

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

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

Итак, выделяем значения в ячейках и форматируем их (вкладка «Вставка» — «Условное форматирование» — «Гистограммы»). Выпадающее меню демонстрирует список шаблонов (доступен предосмотр при наведении курсора мыши на наименования). Выбираем удобную цветовую схему.

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

Теперь можно уже даже скользнув взглядом по таблице понять, насколько плохо выполняет свои обязанности наш «Менеджер 5».

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

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

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

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

Воспользуемся помесячной сортировкой.

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

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

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

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

К тому же можно указать несколько значений для выборки.

Теперь немного оговорим о графиках. В Excel 2010 можно применять инфокривые. Для этого необходимо выделить ячейку напротив строки с данными и сделать ее активной.

Далее вновь используем вкладку «Вставка», раздел «Инфокривые» (он еще может носить название «Сперклайны»). Выделяем нашу строку, как диапазон данных и подтверждаем. В результате в выбранной ячейке появится небольшой график.

Сделаем это же для результатов всех наших сотрудников.

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

Как и при работе с иными полезными функциями, можно самостоятельно подбирать стиль инфокривой (верхняя панель, режим «конструктор»).

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

• Сама инфокривая может быть одного из 3 типов:
• — График, который мы и рассматривали на примере;
• — Столбец – он отображает обрабатываемые данные маленькими столбиками. Чем больше данных, тем тоньше будут столбики, но они способны наглядно продемонстрировать минимальное и максимальное значение;

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

1. Вот как это выглядит в графическом примере
2. В результате мы получили наглядное пособие того, насколько нововведения в Excel 2010 способны упростить работу аналитиков и иных специалистов.

Источник: https://AdvancedUser.ru/microsoft-excel/provodim-analiz-dannykh-v-excel-2010.html

Расчет покупки в кредит инструментом Excel — Сценарии (Scenarios)

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

• · 
  сколько придется отдавать в месяц
  («в смысле хватит ли того, что останется, чтобы дожить до следующей
  зарплаты?»)
• · 
  сколько в итоге придется переплатить
  за использование кредита («лучше еще годик на своей старой помойке поезжу,
  зато потом куплю себе новую 10-ку сразу и не буду переплачивать за кредит как
  мой сосед-неудачник, которому машина в два раза дороже обошлась»)
• · 
  какой банк выбрать, чтобы в ответах
  на два предыдущих вопроса не получилось вовсе уж жутких цифр (Банк
  «ДДД» — Делаем Детей Должниками или лучше «Алиса, Базилио
  & Co»?)
• Для расчета
  составляем простенький кредитный калькулятор с использованием функции ПЛТ
  (PMT):

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

Где-то меньше процентная
ставка, но требуется предоплата. Где-то срок может быть увеличен с 3 лет до
5-ти, но грабительский процент. Где-то процент нас устраивает, но дерут деньги
за открытие счета и за обслуживание. И т.д. и т.

п. 

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

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

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

Теперь идем в
меню Сервис — Сценарии (Tools
— Scenarios) или в Excel 2007 на вкладку Данные — Анализ
«что-если» — Диспетчер сценариев (Data — «What-if» analysis — Scenario manager).

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

После нажатия
на кнопку ОК в следующем окне нужно ввести значения изменяемых
ячеек для этого банка:

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

Когда
сценарии готовы, мы можем переключаться между ними с помощью кнопки Вывести
(Show) и — самое
главное! — можем создать сравнительный отчет по всем сценариям сразу, нажав
кнопку Отчет (Summary):

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

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

После нажатия
на ОК Excel добавит новый лист в нашу книгу и выведет на него
сравнительный отчет по нашим сценариям вот в такой форме:

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

Ну, а
дальше… Конечно, Excel не сможет принять решение за вас, но, по крайней мере, вы
сможете принять решение о выборе банка, четко понимая все плюсы и минусы своего
выбора!

Источник: https://vunivere.ru/work22217

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

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

Reviewed by Unknown on
2016-03-07T14:03:00+02:00

Rating: 5

Unknown

14:03:00

Анализ
,
Эксель
,
Excel
,
Excel для новичков
,
Excel для опытных

Edit

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

Если вы продаете 60% книг по самой высокой цене, ячейка D10 вычисляет общую прибыль в размере 60 * $ 50 + 40 * $ 20 = $ 3800.

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

Что будет, если Вы продадите 70% книг по высокой цене? А что будет, если Вы продадите 80% книг? Или 90%, или 100%? Каждый другой процент продажи книг — это различный сценарий.Вы можете использовать «Диспетчер сценариев» для создания этих сценариев.
Примечание: Вы можете просто ввести другой процент в ячейку C4, что бы увидеть результат в ячейке C10. Однако, Анализ «что если» позволит Вам сравнить результаты различных сценариев.
Итак, приступим. 1. На вкладке Данные выберите Анализ «что если» и выберите Диспетчер сценариев из списка. Откроется диалоговое окно Диспетчер сценариев.

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

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

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

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

  Примечание: чтобы увидеть результат сценария, выберите сценарий и нажмите на кнопку Вывести. Excel изменит значение ячейки C4 в соответствии со сценарием, что бы Вы смогли увидеть результат на листе. Для того, чтобы легко сравнить результаты этих сценариев, выполните следующие действия:
1. Кликните по кнопке «Отчет» в Диспетчере сценариев.
2. Далее, выберите ячейку C10 (итого выручка) в качестве ячейки результата и нажмите ОК.

Результат:

Вывод: Если вы продаете 70% книг по высокой цене, то Вы получите общую выручку в размере $ 4100, если Вы продаете 80% книг по высокой цене, то Вы получаете общую прибыль в размере $ 4400 и т.д. Вот как легко можно использовать Анализ «что если» в Excel.

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

Источник: http://www.excelguide.ru/2016/03/what-if-analysis.html

Создание сценариев. Отчет по сценарию Excel

Подробности

Создано: 23 июня 2013

• Практическая работа №12
• Тема занятия: Работа со сценариями.
• Цель занятия: Научиться использовать сценарии для нахождения оптимального решения задач имеющих несколько условий.
• План занятия:

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

1. Сценарий — это набор значений, которые Microsoft Excel сохраняет и может автоматически подставлять на листе.
2. Создание сценариев.
3. Задача:

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

• Решение:
• Подготовка таблицы сценариев.
• •  Создайте новую рабочую книгу.
• •  Лист1 переименуйте в Покупка автомобиля.
• •  На листе Покупка автомобиля Создайте таблицу следующего вида:

•  В ячейку С9 введите формулу =СУММ(C4:C8). Эта формула будет считать общую цену всех комплектующих.

1. •  Формат ячеек столбца Цена — Денежный.
2. Создание нескольких сценариев
3. •  Выполните Сервис→Сценарии.
4. •  В окне Диспетчер сценариев Нажмите кнопку Добавить.

•  В окне Добавление сценария В поле Название сценария Впишите Ланос. В поле Изменяемые ячейки Введите $C$4:$C$8;$C$1. Нажмите ОК.

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

•  Теперь заполним значение изменяемых ячеек. В окне Значение ячеек сценария Вы увидите поля для ввода значений для каждой изменяемой ячейки.

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

Например: Если в поле 1: $C$4 Ввести значение 1000 то это будет означать что в ячейку С4 будет подставлена цена на двигатель равная тысячи ден. едениц.

• Задание: Заполните значения изменяемых ячеек так чтобы они выглядели правдоподобно. Ячейка С1 должна содержать значение «Ланос», Так как в нее будет выводиться название автомобиля
• •  После ввода всех изменяемых ячеек нажмите ОК.
• •  Добавьте еще три сценария с такими значениями:

Автомобиль	Сенс	Автомобиль	Лада	Автомобиль	Honda
Комплектующие	Цена	Комплектующие	Цена	Комплектующие	Цена
1	Двигатель	900	1	Двигатель	1200	1	Двигатель	2500
2	Ходовая	600	2	Ходовая	450	2	Ходовая	1000
3	Корпус	450	3	Корпус	523	3	Корпус	900
4	Шины	210	4	Шины	150	4	Шины	450
5	Салон	320	5	Салон	400	5	Салон	550

Примечание: Для добавления нового сценария в окне Диспетчер сценариев Нажмите кнопку Добавить.

3. Просмотр сценариев.

Для просмотра сценариев в окне Диспетчер сценариев Выделите название сценария который вас интересует и нажмите кнопку Вывести.

Сценарий для автомобиля Ланос Сценарий для автомобиля Honda

2. Отчет по сценарию.

1. •  В окне Диспетчер сценариев Нажмите кнопку Отчет.
2. •  В окне Отчет по сценарию Укажите тип отчета Структура, а в качестве ячейки результата укажите ячейку С9.
3. •  Нажмите ОК.
4. Примечание: Созданный отчет будет помещен на отдельный лист и будет представлен в виде структурированных данных.
5. Контрольные вопросы:

Что такое сценарий? Для чего целесообразно использовать сценарии? Как создать сценарий? Как отобразить сценарий? Как изменить сценарий? Как создать отчет по сценарию?

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

Источник: http://po-teme.com.ua/informatika/kurs-zanyatij-na-operatora-kompyuternogo-nabora/778-sozdanie-scenariev-otchet-po-scenariyu-excel.html

Как использовать сценарии в Excel 2016 — манекены 2019

Excel 2016 позволяет создавать и сохранять наборы входных значений, которые приводят к различным результатам в сценариях с помощью опции «Диспетчер сценариев» в раскрывающемся меню кнопки «Что-если» на вкладке «Данные» на ленте. Сценарий состоит из группы входных значений на листе, которому вы назначаете имя, например Лучший случай, наихудший случай, наиболее вероятный случай, и т. Д.

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

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

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

Чтобы создать сценарии с помощью диспетчера сценариев, выполните следующие действия:

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

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

2. Нажмите кнопку команды «Что-если анализировать» на вкладке «Данные ленты», а затем нажмите «Диспетчер сценариев» в раскрывающемся меню или нажмите «Alt + AWS».

   Это действие открывает диалоговое окно «Диспетчер сценариев».

3. Нажмите кнопку «Добавить» в диалоговом окне «Диспетчер сценариев».

   Это действие открывает диалоговое окно «Добавить сценарий», аналогичное показанному. Диалоговое окно «Добавить сценарий» содержит текстовое поле «Имя сценария», в котором вы даете новому сценарию описательное имя, например «Лучший случай, наиболее вероятный случай», и так далее.

   Это диалоговое окно также содержит текстовое поле «Изменение ячеек», содержащее адреса ячеек переменных, выбранных на листе, поле «Комментарий», содержащее примечание с вашим именем и текущей датой, поэтому вы всегда будете знать, когда вы создали конкретный сценарий и Защитные флажки, которые запрещают пользователям вносить изменения и / или позволяют скрыть сценарий, когда рабочий лист защищен.

   Добавление нового наиболее вероятного сценария для прогноза продаж.

4. Введите описательное имя для нового сценария в текстовое поле «Имя сценария».

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

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

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

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

   Если вы хотите, чтобы Excel также скрывал сценарий, когда включена защита рабочего листа, установите флажок «Скрыть».

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

5. В разделе «Защита» диалогового окна «Добавить сценарий» выберите, какая защита сценариев вам нужна, если таковая имеется, с флажками «Предотвратить изменения и скрытие».

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

6. Нажмите «ОК» в диалоговом окне «Добавить сценарий».

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

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

   Указание изменяемых значений в диалоговом окне «Значения сценариев».

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

7. Проверьте значения в текстовом поле каждой меняющейся ячейки и измените значения по мере необходимости.

   Теперь вы готовы закрыть диалоговое окно «Значения параметров сценария», которое завершает определение нового сценария.

8. Нажмите кнопку «Добавить» в диалоговом окне «Сценарные значения».

   Это действие закрывает диалоговое окно «Значения параметров сценария» и возвращает вас в диалоговое окно «Добавить сценарий», где вы можете определить новое имя сценария для меняющихся ячеек.

9. Повторите шаги с 4 по 7, чтобы добавить все другие сценарии, которые вы хотите создать.

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

10. Нажмите «ОК» в диалоговом окне «Добавить значения», а затем нажмите кнопку «Закрыть» в диалоговом окне «Диспетчер сценариев».

Когда вы вернетесь в диалоговое окно «Диспетчер сценариев», имена всех сценариев, которые вы добавили, появятся в списке «Сценарии».Например, на рисунке вы видите, что три сценария — «Вероятнее всего, лучший случай» и «Худший случай» — теперь перечислены в списке «Сценарии».

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

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

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

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

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

Вы также можете объединить сценарии из других открытых файлов рабочей книги Excel. (Разумеется, в книгах должен быть одинаковый макет электронной таблицы и изменение ячеек.

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

Затем Excel копирует все сценарии, определенные для этого рабочего листа, и объединяет их с любыми сценариями, которые вы определили для текущей рабочей таблицы.

Источник: https://ru.howtodou.com/how-to-use-scenarios-in-excel-2016

Сценарии Excel. Excel. Статьи. Решения. Лучшее по Excel

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

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

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

Сценарии Excel могут применяться для прогнозов результатов моделей расчета листа.

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

Сценарии Excel. С помощью диспетчера сценариев возможно:

1. создавать сразу множество различных сценариев (каждый может иметь не более 32 значений для изменений),
2. присваивать имена сценариям,
3. выполнять и сохранять сценарии листов,
4. защищать сценарии от всевозможных изменения,
5. скрывать сценарии,
6. отслеживать изменения сценариев,
7. создавать итоговые расчеты,
8. объединять вместе сценарии.

Сценарием называют именованную совокупность данных изменяемых ячеек. Отметим, что для ячеек аргументов функций можно задать разные значения. С помощью команды Меню Данные — группа Работа с данными — Анализ «что-если» — можно вызвать диалоговое окно «Диспетчер сценариев», для значений ячеек текущего листа Excel. Как показано на первом рисунке

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

Чтобы создать новый сценарий, нужно кликнуть по кнопке «Добавить», после чего появится новое диалоговое окно.

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

Последний показатель — автора, можно изменить, используя команду Сервис -> Параметры, графа «Общие», поле «Имя пользователя».

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

Просмотр результатов работы сценария

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

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

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

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

Можно наблюдать два типа отчетов:

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

Поделитесь нашей статьей в ваших соцсетях:

Источник: https://excelworks.ru/2016/07/17/scenarii-excel/

Как создать сценарий в Excel 2013?

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

Перед тем, как запустить технологию, даем имя каждой ячейки с данными. Дело в том, что если мы этого не сделаем, то в отчете у нас будут классические имена ячеек — А1,А2 и т.д., и отчет при этом будет не читаемый.

Чтобы дать имена ячейкам используем команду в русской версии Формулы- Присвоить имя, в английской версии — Formulas -Define name. Имена даем каждой ячейке, где находится цифра. Именем будет название показателя.

После этого можем запускать технологию сценария.

В русской версии это будет Где искать сценарий в русской версии, в английской:

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

В следующем окне нам нужно будет внести в поле Название сценария (Scenario Name) внести название первого сценария, а в поле Изменяемые ячейки (Changin Cell) вносим адреса ячеек из нашей модели, в которых нет формул. После этого жмем ОК.

И у нас на экране появляется окно, в котором мы вносим изменения для наших ячеек без форму окно внесения изменений в сценарий. Если хотим добавить еще сценарий, то жмем кнопку Добавить (ADD), если это наш единственный сценарий, жмем кнопку OK.

После нажатия ОК на экране появится окно диспетчера сценариев, в котором нам нужно выбрать кнопку для создания отчета — В русской версии это кнопка ОТЧЕТ, в английской — Summary. После этого на экране появится маленькое окно под названием ОТЧЕТ ПО СЦЕНАРИЮ (SCENARIO SUMMARY), в которое нам нужно внести адреса ячеек с формулами. После этого нажимаем ОК. На экране появляется отчет по результатам наших сценариев

.

Теперь можно делать отчет по сценарию

Источник: http://www.bolshoyvopros.ru/questions/1320345-kak-sozdat-scenarij-v-excel-2013.html