Progress-servis55.ru

Новости из мира ПК
42 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Сценарии в excel примеры

Сценарии в excel примеры

На этом шаге мы рассмотрим диспетчер сценариев.

Анализ данных с помощью таблиц подстановки является весьма эффективным. Однако он имеет несколько недостатков:

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

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

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

Чтобы Вы смогли получить общее представление о средстве Диспетчер сценариев , начнем с простого примера — производственной модели (рис. 1).

Рис. 1. Пример производственной модели

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

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

Рис. 2. Диалоговое окно Диспетчер сценариев

Когда Вы впервые вызываете это диалоговое окно, в нем имеется сообщение, в котором сказано, что ни один сценарий не определен, и это не удивительно, ведь Вы только начали. Как только будут добавлены новые сценарии, их названия появятся в диалоговом окне. Чтобы добавить сценарий, в диалоговом окне Диспетчер сценариев щелкните на кнопке Добавить . Появится диалоговое окно Добавление сценария , которое показано на рисунке 3. Это диалоговое окно разделено на четыре части:

Рис. 3. Диалоговое окно Добавление сценария

  • Название сценария. В этом поле можно указать любое имя сценария, но желательно, чтобы оно что-то обозначало.
  • Изменяемые ячейки. Ячейки, в которых находятся исходные данные для сценария. В это поле можно ввести абсолютный адрес ячейки или ее имя. Разрешается выбирать несколько ячеек, причем все они не обязательно должны быть смежными. В каждом сценарии, которому присвоено имя, можно использовать одни и те же наборы изменяемых ячеек или разные изменяемые ячейки. Количество изменяемых ячеек для одного сценария ограничено числом 32.
  • Примечание. По умолчанию в это поле Excel помещает информацию о том, кто создал сценарий, а также дату его создания. Однако Вы можете отредактировать этот текст, добавить к нему новый текст или вовсе удалить его.
  • Защита. Две опции, которые позволяют защитить сценарий от изменений и скрыть его, можно активизировать только в том случае, если рабочий лист защищен и в диалоговом окне Защитить лист активизирована опция Сценарии . Защита сценария предотвращает модификацию его кем-либо другим, а скрытый сценарий вообще не появляется в диалоговом окне Диспетчер сценариев .

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

Рис. 4. Диалоговое окно Значение ячеек сценария

Примечание . Для удобства использования сценариев необходимо поместить на любую панель инструмент Сценарий :

  • Выполнить команду Сервис | Настройка .
  • В диалоговом окне Настройка выбрать вкладку Команды .
  • Выбрать категорию Сервис .
  • Выделить инструмент Сценарий и перетащить его на любую панель инструментов.
  • Щелкнуть на кнопке Закрыть .

На следующем шаге мы рассмотрим отображение, изменение и объединение сценариев.

Сценарии в excel примеры

  • Главная Страница
  • Поиск По Сайту
  • Контакты Обратная связь
  • Главная
  • Информатика
  • Курс занятий на Оператора компьютерного набора
  • Создание сценариев. Отчет по сценарию Excel

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

Практическая работа №12

Тема занятия: Работа со сценариями.

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

План занятия:

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

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

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

Задача:

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

Решение:

Подготовка таблицы сценариев.

• Создайте новую рабочую книгу.

• Лист1 переименуйте в Покупка автомобиля.

• На листе Покупка автомобиля Создайте таблицу следующего вида:

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

• Формат ячеек столбца Цена — Денежный.

Создание нескольких сценариев

• Выполните Сервис→Сценарии.

• В окне Диспетчер сценариев Нажмите кнопку Добавить.

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

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

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

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

• После ввода всех изменяемых ячеек нажмите ОК.

• Добавьте еще три сценария с такими значениями:

Сценарии в Excel позволяют прогнозировать результат

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

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

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

  • банки повысят процентные ставки по кредитам;
  • существенно измениться уровень инфляции;
  • поднимут налоги и т.п.

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

Пример сценариев в Excel

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

  1. Составьте таблицу так как указано на рисунке:
  2. Выделите диапазон ячеек B1:B2 и выберите инструмент: «Данные»-«Работа с данными»-«Анализ что если»-«Диспетчер сценариев».
  3. В диспетчере нажмите на кнопку «Добавить».
  4. В окне «Добавление сценария» укажите имя «Макс.ставка%» и ссылку на диапазон изменяемых ячеек. И нажмите ОК.
  5. Появится окно «Значения ячеек сценария», в нем введите новое значение 7% для ячейки B1, а в B2 не изменяйте как выше указано на рисунке. И нажмите ОК.
  6. Повторите выше указанные пункты с 3 по 5. Только на этот раз в 4-ом пункте укажите имя «Макс.взнос»; в 5-том пункте укажите новое значение взноса -1100 для ячейки B2, а B1 оставьте без изменений как ниже на рисунке:
  7. Теперь в диспетчере сценариев нажмите на кнопку отчет.
  8. Ничего не меняя жмем ОК.

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

Сценарии в Excel

Сценарии в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Vba excel номер строки
Ссылка на основную публикацию
Adblock
detector