Как открыть сценарий в excel - Word и Excel - помощь в работе с программами

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

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

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

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

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

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

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

Составьте таблицу так как указано на рисунке:

Выделите диапазон ячеек B1:B2 и выберите инструмент: «Данные»-«Работа с данными»-«Анализ что если»-«Диспетчер сценариев».

В диспетчере нажмите на кнопку «Добавить».

В окне «Добавление сценария» укажите имя «Макс.ставка%» и ссылку на диапазон изменяемых ячеек. И нажмите ОК.

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

Теперь в диспетчере сценариев нажмите на кнопку отчет.

Ничего не меняя жмем ОК.

Готово!!!

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

Источник

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

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

Как изменить сценарий?

В окне открытой книги перейдите к вкладке «Данные» в группе «Работа с данными» раскройте меню кнопки «Анализ что-если».
В списке команд выберите пункт «Диспетчер сценариев».
В окне «Диспетчер сценариев» в группе «Сценарии» выберите в списке строку нужного сценария и нажмите кнопку «Изменить».
В окне «Изменение сценария» внесите нужные коррективы.
Закройте окно кнопкой «ОК».

Как объединить несколько сценариев?

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

[stextbox id=»warning»]При выделении листа внизу окна отображается количество имеющихся на данном листе сценариев.[/stextbox]

Как создать итоговый отчет по сценариям?

В окне открытого листа перейдите к вкладке «Данные» в группе «Работа с данными» раскройте меню кнопки «Анализ что-если».
В списке команд выберите пункт «Диспетчер сценариев».
В окне «Диспетчер сценариев» щелкните по кнопке «Отчет».
В окне «Отчет по сценарию» (рис. 5.107) выберите вид отчета – структура или сводная таблица.

Рис. 5.107. Окно «Отчет по сценарию»

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

[stextbox id=»warning»]Не обязательно указывать ячейки результата в итоговом отчете.[/stextbox]

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

[stextbox id=»warning»]Для итогового отчета не обязательно указывать ячейки результата в отличие от отчета сводной таблицы[/stextbox]

Как удалить сценарий?

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

Источник

Сценарии — это инструмент 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

.

Автоматически созданный отчет в виде

Сводной таблицы

, конечно, требует доработки.

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

Источник

8 апреля 2022 г.

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

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

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

Как использовать диспетчер сценариев в Excel

Вот шесть шагов, которые вы можете предпринять, чтобы перемещаться по диспетчеру сценариев:

1. Найдите диспетчер сценариев

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

2. Введите данные в электронную таблицу

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

После того, как вы ввели эти изменяющиеся ячейки, вы можете добавить формулу, отражающую, как изменяющиеся ячейки соотносятся друг с другом. Чтобы ввести формулу, выберите ячейку, в которую вы хотите вставить свои вычисления, и введите знак равенства. Затем вы можете добавить остальную часть формулы. Например, если вы хотите использовать формулу, которая складывает значения затрат на рабочую силу в одной ячейке с пометкой A1 и стоимость материала в другой ячейке с пометкой A2, вы можете ввести «=A1+A2». После этого вы можете нажать «Enter», чтобы применить формулу.

3. Создайте сценарий

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

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

4. Добавьте еще один сценарий

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

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

5. Слияние сценариев

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

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

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

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

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

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

Источник

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

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

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

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

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

Содержание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Содержание

1 Создание сценариев в Excel
- 1.1 Пример сценариев в Excel
  - 1.1.1 Сценарии Excel. С помощью диспетчера сценариев возможно:
  - 1.1.2 Просмотр результатов работы сценария

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

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

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

Готово!!!

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

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

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

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

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

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

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

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

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

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

(Visited 2 534 times, 5 visits today)

Некоторое время назад мне пришлось столкнуться с такой функцией MS Excel, как Сценарии. В процессе освоения этого «НЕЧТО» у меня созрел некий небольшой обзор, которым и хочу поделиться с вами.

Для чего можно использовать функцию Сценарии? Попробую ответить на этот вопрос. Большинство людей, рано или поздно, приходят к мысли о необходимости взять кредит или же наоборот, возникает мысль вложить деньги в какой-либо инвестиционный проект, чтобы они не «лежали мертвым грузом», а «работали». И в том и в другом случае перед человеком встает множество вариантов: множество банков, предлагающих кредиты на различных условиях, множество инвестиционных проектов с различными ставками и прибылью. Какой же вариант выбрать? Как наглядно увидеть, какой проект наиболее выгоден, при каком кредите будет наименьшая переплата, ведь условий множество. Например, кредит: некоторые банки предлагают, казалось бы, выгодный процент, но берут при этом плату за открытие и обслуживание счета, другие банки не взимают платежей за обслуживание счета, но у них больший процент, а кто-то вообще берет первоначальный взнос и процент у них грабительский. Как разобраться во всем этом многообразии? Эту проблему помогают решить экселевские Сценарии. Нет, конечно, Excel не сможет принять решение за Вас куда вкладывать деньги или в какой банк идти за кредитом, но с задачей подсчета и наглядного представления информации он справляется великолепно! Итак, наглядно рассмотрим 2 варианта использования функции Сценарии: в первом варианте сравним 3 инвестиционных проекта, во втором выберем один банк из трех, предлагающий наилучшие условии кредитования.

1 вариант.

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

Год	I проект	II проект	III проект
Начальные инвестиции (год 0)	— 17 000 000 р.	-20 000 000 р.	-30 000 000 р.
Год 1	3 000 000 р.	14 000 000 р.	12 000 000 р.
Год 2	4 000 000 р	8 000 000 р.	12 000 000 р.
Год 3	17 000 000 р	4 000 000 р.	16 000 000 р.

Перенесем исходные данные в Excel.

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

Для сравнения этих проектов нам понадобятся такие показатели как 1. чистый приведенный доход или NPV (он показывает величину денежных средств, которую инвестор ожидает получить от проекта, после того, как денежные притоки окупят его первоначальные инвестиционные затраты и периодические денежные оттоки, связанные с осуществлением проекта); и 2. Внутренняя ставка доходности или IRR (это процентная ставка, при которой чистый приведенный доход (NPV) равен 0).

Чистый приведенный доход определяется функцией ЧПС. Рассчитаем ЧПС для всех трех проектов.

Делается это c помощью Мастера функций. Выделим ячейку B9, на панели инструментов, в закладке «формулы» найдем кнопку f x. В диалоговом окне мастера функций в разделе «категория» выберем «финансовые», ниже, в разделе «выберите функцию» найдем ЧПС и выделим ее, нажмем ОК.

Появилось второе диалоговое окно мастера функций для ввода аргументов.

В поле «ставка» введем адрес ячейки B2 просто щелкнув по этой ячейке мышкой.

В поле «значение 1» — адрес ячейки B5

В поле «значение 2» — адрес ячейки B6

В поле «значение 3» — адрес ячейки B7.

Нажмем ОК. В ячейке B9 появилось значение чистого приведенного дохода. Растянем эту формулу на соответствующие ячейки двух других проектов.

В ячейке B10 рассчитаем внутреннюю ставку доходности для первого проекта посредством введения финансовой функции ВСД. В открывшемся диалоговом окне в поле аргументы «значения» введем блок ячеек B4:B7.

Нажмем ОК. В ячейке B10 появилось значение внутренней ставки доходности. Растянем формулу на соответствующие ячейки двух других проектов.

Необходимые экономические показатели для сравнения проектов вычислены.

Приступаем к построению сценариев.

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

Нажимаем кнопку «добавить». В диалоговом окне «добавление сценария», поле ввода «Название сценария» пишем «проект 1». В поле ввода «изменяемые ячейки» запишем латинскими буквами абсолютные адреса блока ячеек B4:B7 ($B$4:$B$7) или просто выделим этот блок ячеек мышкой. Внизу окна необходимо отключить опцию «запретить изменения».

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

поэтому просто нажмем кнопку ОК. У нас снова открылось диалоговое окно, но теперь в поле сценарий уже есть сценарий для проекта 1, который мы только что внесли.

Но поскольку проектов у нас 3, нужно создать сценарии для проектов 2 и 3. Нажимаем кнопку «добавить». Снова открывается окно «Добавление сценария». В поле «Название сценария» пишем «проект 2». В поле ввода «изменяемые ячейки» запишем латинскими буквами абсолютные адреса блока ячеек B4:B7 ($B$4:$B$7) или просто выделим этот блок ячеек мышкой. Внизу окна необходимо отключить опцию «запретить изменения».

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

Нажмем кнопку ОК. У нас снова открылось диалоговое окно, но теперь в поле сценарий уже есть сценарии для проекта 1 и для проекта 2. Создаем сценарий для третьего проекта. Нажимаем кнопку «добавить». В диалоговом окне «добавление сценария», поле ввода «Название сценария» пишем «проект 3». В поле ввода «изменяемые ячейки» запишем латинскими буквами абсолютные адреса блока ячеек B4:B7 ($B$4:$B$7) или просто выделим этот блок ячеек мышкой. Внизу окна необходимо отключить опцию «запретить изменения».

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

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

Сделаем отчет по этим сценариям. Для этого нажмем кнопку «отчет».

В открывшемся диалоговом окне «отчет по сценарию» выберем типа отчета «структура», в качестве ячеек результата выберем блок ячеек В9:В10

Нажмем ОК. Открылся вновь созданный лист с названием «Структура сценария»

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

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

Таким образом можно переключаться между проектами.

Вернемся к листу «Структура сценариев». Для наглядности внесем в полученную таблицу поясняющий текст и удалим всю лишнюю информацию.

2 вариант.

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

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

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

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

нажмем ОК.

Заполним аргументы функции. В поле «ставка» заносим адрес ячейки В2. Если бы мы рассчитывали ежегодные платежи, то мы бы ничего менять не стали в этой ячейке, но поскольку мы рассчитываем ежемесячные платежи, а размер ставка – годовой, то после адреса ячейки ставим «/12». Таким образом мы высчитываем ежемесячную процентную ставку. Поле «Кпер» заполняем адресом ячейки В4. Количество периодов у нас выжарено в месяцах, поэтому здесь больше ничего делать не нужно. В поле «Пс» нужно указать адрес ячейки, которая отображает сумму, которую мы хотим взять в кредит. Если бы у нас не было первоначального взноса, то в данном поле мы бы просто указали адрес ячейки В1, но поскольку мы берем в кредит не полную стоимость автомобиля, а стоимость автомобиля за вычетом первоначального взноса. Таким образом в это поле вносим: В1-В3. В поле Бс ставим 0, т.к. после последней выплаты наш долг банку должен быть полностью погашен, т.е. равен 0. В поле Тип также ставим 0, т.к. выплаты в нашем случае производятся в конце периода.

Нажем ОК.

Теперь рассчитаем реальную стоимость покупки. Сначала распишем что из себя представляет этот показатель, для наглядности.

Реальная стоимость покупки = (сумма ежемесячных выплат * срок кредита) – начальный взнос – (сумма кредита * % за открытие счета) – (ежемесячные платежи за обслуживание счета * срок кредита)

Рассчитаем Суммы переплаты. Она рассчитывается по формуле: Сумма кредита + реальная стоимость покупки

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

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

Отключаем опцию «запр

етить изменения». Нажимаем ОК. В открывшемся окне «значения ячеек сценария» меняем значения в полях на соответствующие значения для Бака А.

Нажимаем ОК. Снова появляется окно диспетчера сценариев. Нажимаем кнопку «добавить». Вносим название банка и диапазон изменяемых ячеек (он соответствует диапазону ячеек для Банка А)

Отключаем опцию «запретить изменения». Нажимаем ОК. В открывшемся окне «значения ячеек сценария» меняем значения в полях на соответствующие значения для Бака ББ.

Нажимаем ОК.

Теперь в диалоговом окне диспетчера сценариев появились сценарии для двух банков: Банка А и Банка ББ.

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

Нажимаем ОК. В открывшемся окне «значения ячеек сценария» меняем значения в полях на соответствующие значения для Бака ВВВ.

Нажимаем ОК. Теперь в диалоговом окне диспетчера сценариев появились сценарии для двух банков: Банка А, Банка ББ и Банка ВВВ.

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

Выбираем тип отчета «структура», в поле ячейки результата вносим адреса блока ячеек В9:В11.

Нажимаем кнопку ОК.

Мы оказываемся на новом листе, только что созданным Excel-ем, который называется «Структура сценария»

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

Excel выполнил свою работу, дальше выбор за Вами!

Источник

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

Для
получения отчета по полученным сценариям
нужно выбрать пункт «Сценарии» в
меню «Сервис». Появится «Диспетчер
сценариев» (см. рис. 5).

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

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

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

Раздел 5. Шаблоны Понятие шаблона документа

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

1.
Расширение
xlt
в отличие от расширения обыкновенных
книг xls

2.
При открытии
файла шаблона открывается не сам документ
шаблона, а его точная копия с расширением
xls

Создание шаблона документа

Любая рабочая
книга Excel
может быть сохранена в качестве шаблона.
Чтобы сохранить книгу как шаблон, нужно
выбрать в меню «файл» команду «Сохранить
как» и в появившемся диалоговом окне
установить расширение сохраняемого
файла «xlt»
(см. рис. 1). При этом автоматически
откроется папка «Шаблоны» куда и следует
сохранить книгу Excel.

Рис.1.
Сохранение файла как шаблон документа

Для создания нового
документа на основе шаблона нужно
выбрать пункт «Создать» из меню «Файл»
и выбрать созданный вами шаблон для
создания нового документа. (см. рис. 2).
При этом откроется точная копия шаблона
но с расширением «xls».

Рис
2. Фрагмент диалога создания документа

Шаблон документа
можно редактировать как обыкновенную
книгу, открыв его в Excel.
С помощью шаблонов можно изменить вид
рабочей книги и листов Excel,
открывающихся по умолчанию. Новый вид
рабочей книги сохраняется под именем
«book.xlt»
в каталоге «XLStart»,
который создается программой установки
Excel.
Для создания нового вида рабочего листа
создается книга с единственным листом
и сохраняется в каталоге «XLStart»
под именем «лист.xlt».
В случае с шаблоном листа в Excel
97 имя каждого нового листа не меняется.

Так, чтобы
при открытии новой книги в меню «Сервис»
в диалоге «Автозамена» пункт «Делать
первые буквы предложения прописными»
не был отмечен по умолчанию (см. рис. 3).
нужно снять отметку этого пункта в новой
книге Excel
и сохранить ее как шаблон рабочей книги
по умолчанию. После чего нужно закрыть
шаблон командой «Закрыть» из меню
«Файл». Все вновь созданные книги уже
не будут иметь отмеченным данный пункт
диалога «Авозамена».

Рис.
3. Фрагмент диалогового окна «Автозамена»

Использование
мастера шаблонов

Мастер шаблонов
позволяет связать рабочую книгу Excel
с базой данных или списком Excel.
В этом случае лист Excel может
служить формой для ввода данных. Мастер
шаблонов позволяет связать определенные
ячейки с полями списка или базой данных.
Например, есть следующий лист Excel
(см. рис.4), сохраненный в файле «Книга
4.xls»

Рис.4.
Использование листа Excel
как формы для ввода данных

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

Рис.
5. Мастер шаблонов – шаг 1

Рис.6.
Мастер шаблонов — шаг 2

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

Рис.7.
Мастер шаблонов – шаг 3

Нажав на кнопку
выбора диапазона

нужно
щелкнуть на ячейке «C2»,
чтобы ввести ее адрес в поле «Ячейка».
Затем просто щелкнуть мышкой на соседнем
с ней слева пустом поле. Excel
автоматически введет в него имя «Товар».
Это имя можно назначить также
самостоятельно. Заполнив сходным
образом все четыре поля включая «C5»,
переходим к следующим шагам мастера.
Оставив все настройки Excel
по умолчанию на этих шагах, завершаем
работу мастера и закрываем рабочую
книгу.

Для ввода данных
в список Excel
при помощи созданного шаблона нужно
выбрать команду «Создать» из меню «Файл»
и выбрать появившийся во вкладке общие
наш шаблон. Затем нужно заполнить поля
«C2:C4»
открывшийся рабочей книги и выбрать
пункт «Сохранить» в меню «Файл». Появится
следующий диалог (см. рис.8).

Рис
8. Диалог добавления записи в список

Нажав на кнопку
«ОК», введем запись в список. Возникнет
диалог сохранения документа. Сохраним
его, в дальнейшем, этот диалог больше
показываться не будет. Заполним заново
ячейки «C2:C4»
и вновь выберем пункт «Сохранить». После
ответ на возникающий диалог добавления
записи новые данные будут сохранены в
списке, примерный вид которого показан
на рис. 9.

Рис.
9. Полученный список в файле «Книга 4 –
данные.xls»

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

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

Источник

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

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

Как изменить сценарий?

Как объединить несколько сценариев?

Как создать итоговый отчет по сценариям?

Как удалить сценарий?

Задача

Решение

Как использовать диспетчер сценариев в Excel

1. Найдите диспетчер сценариев

2. Введите данные в электронную таблицу

3. Создайте сценарий

4. Добавьте еще один сценарий

5. Слияние сценариев

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

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

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

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

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

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

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

Раздел 5. Шаблоны Понятие шаблона документа

Создание шаблона документа

А вот еще интересные статьи: