Диспетчер сценариев
Таблица подстановок – хорошее средство, когда в расчетах принимает участие не более двух переменных. Однако реальные ситуации могут оказаться более сложными, и на помощь придет другое средство анализа – диспетчер сценариев.
Механизм сценариев Excel позволяет задавать и использовать альтернативные наборы данных в одной рабочей книге. Вместо создания отдельных версий книги для экспериментирования с различными значениями и формулами, вы можете применить сценарии для более удобной работы, не боясь повредить основную рабочую книгу.
Создание рабочего листа для сценариев. Первым шагом применения сценария является создание листа, с которым вы собираетесь работать, и присвоение имен ячейкам, чьи значения будут затронуты сценарием. Задание имен не является обязательным, так как вы можете ссылаться на ячейки с помощью их адресов, однако наличие имен придает процессу большую наглядность.
Создайте рабочий лист по образцу, представленному на рис.5.7.
Рисунок 5.7. Рабочий лист для демонстрации сценариев
В нем суммируются продажи, расходы, прибыль, рентабельность и доля прибыли шести категорий продуктов. В рабочем листе содержатся следующие данные:
· Столбец Продажи – входные данные. Общий итог внизу столбца суммирует вместе объемы продаж по каждому продукту.
· Столбец Расходы – итоговые показатели затрат (входные данные). Общий итог внизу столбца суммирует вместе затраты по каждому продукту.
· Столбец Прибыль – данные получены вычитанием стоимости каждого продукта из его продаж. Общий итог внизу столбца суммирует вместе прибыль по каждому продукту.
· Столбец Рентабельность – данные получены делением прибыли от каждого продукта на его итоговые продажи. Значение внизу столбца – общая рентабельность, вычисленная делением общей прибыли на общее число продаж.
· Столбец Доля – данные получены делением прибыли от каждого продукта на общую прибыль компании (ячейка D11). Общий итог внизу столбца суммирует вместе показатели доли прибыли. Сумма должна составлять 100%.
После создание рабочего листа, сохраните его перед продолжением работы.
Для работы со сценарием используется диалоговое окно Диспетчер сценариев, которое открывается командой Данные, Работа с данными, Анализ «что-если», Диспетчер сценариев. При первом появлении это окно не содержит сценариев.
Создание стартового сценария. Сначала создайте сценарий, представляющий стартовую позицию вашего рабочего листа. Это позволит вам легко вернуться к начальным значениям и предположениям для листа.
Рисунок 5.8. Средства для создания и изменения сценария
2. Откроется диалоговое окно Значения ячеек сценария.
3. Вам не нужно изменять существующие значения для начального сценария. Нажмите кнопку ОК. Excel закроет диалоговое окно Значения ячеек сценария и вновь откроет диалоговое окно Диспетчер сценариев.
Добавление новых сценариев. Новый сценарий создается аналогично стартовому, но со следующими отличиями:
1. Введите другое имя для сценария в диалоговом окне Добавление сценария.
2. Не меняйте выделение изменяемых ячеек без необходимости. Excel автоматически выделит те ячейки, которые вы определили при создании первого сценария.
3. Измените значения в диалоговом окне Значения ячеек сценария для отображения изменений в рабочем листе. Кроме значений в данном диалоговом окне вы можете ввести формулы для изменения текущего содержимого ячеек. Например, чтобы посмотреть, какой эффект будет при уменьшении расходов на 25%, введите перед существующим значением =,75*.
4. Если вы хотите запретить изменения сценария, то установите флажок Запретить изменения (рис. 5.8). Если вы хотите скрыть сценарий от других пользователей, установите флажок Скрыть. После этого вам необходимо настроить защиту с помощью диалогового окна Защита листа, выбрав команду Рецензирование, Изменения, Защитить лист.
Самостоятельно создайте новый сценарий – Сценарий №1.
Создание отчета по сценариям. Excel может создать отчет по сценариям либо в форме структуры либо в форме сводной таблицы. Для создания отчета необходимо:
1. Открыть диалоговое окно Диспетчер сценариев и нажать кнопку Отчет. Откроется диалоговое окно Отчет по сценарию. Установите переключатель Тип отчета в положение структура. В поле Ячейки результата введите ссылки на ячейки, значения которых изменяются с помощью сценариев. Нажмите кнопку ОК.
2. Excel добавит новый лист Структура сценария, содержащий отчет.
3. Если вы выбрали отчет в виде сводной таблицы, Excel добавит новый лист отчета Сводная таблица по сценарию. Сводные таблицы подробно описаны в разделе 8 данного учебного пособия.
- Таблицы подстановок являются удобным механизмом экономического анализа. Однако область применения этого механизма ограничена.
- Таблица подстановки с одной ячейкой позволяет анализировать множество формул (до 32), каждая из которых зависит от одной и той же переменной.
- Таблица подстановки с двумя ячейками позволяет обрабатывать только одну формулу, зависящую от двух переменных.
- Если реальный процесс не укладывается в рамки указанных моделей, то нужно применять диспетчер сценариев.
studopedia.org — Студопедия.Орг — 2014-2022 год. Студопедия не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования (0.025 с) .
Диспетчер сценариев в excel 2024 — IT Новости из мира ПК
Вывод: Рассмотренный выше примеры показывают, что размещение исходных данных в отдельные ячейки упрощает анализ зависимости выходного результата от изменения исходных данных с использованием анализа данных «Что если» в Exceel.
Диспетчер сценариев в Excel
Электронная таблица представляет собой прямоугольную матрицу, состоящую из ячеек, каждая из которых имеет свой номер. Рассмотрим структуру электронных таблиц на примере работы с электронной таблицей Excel.
Программа Microsoft Excel предназначена для работы с таблицами данных, преимущественно числовыми.
Рис. 5.1. Окно программы Excel |
При формировании таблицы выполняется ввод, редактирование и форматирование текстовых, числовых данных, а также формула (рис. 5.1).
Документ Excel называется рабочей книгой. Рабочая книга представляет собой набор рабочих листов, каждый из которых имеет табличную структуру и может содержать одну или несколько таблиц.
В окне документа в программе Excel отображается только текущий рабочий лист, с которым и ведется работа. Каждый рабочий лист имеет название, которое отображается на ярлыке листа, расположенного в его нижней части. С помощью ярлыков можно переключаться к другим рабочим листам, входящим в ту же книгу.
Для переименования рабочего листа надо дважды щелкнуть на его ярлычке и выбрать команду Переименовать.
Для выделения нескольких смежных рабочих листов необходимо выделить первый рабочий лист и, зажав на клавиатуре клавишу Shift, выделить последний лист. Для выделения нескольких несмежных рабочих листов выделить первый рабочий лист и, зажав Ctrlна клавиатуре, выделить следующий лист.
Электронные таблицы состоят из столбцов и строк. Столбцы озаглавлены буквами латинского алфавита и их двухбуквенными комбинациями (А, В, С, . АА, . IV). Строки озаглавлены цифрами (1,2,3. ). Всего рабочий лист может содержать до 256 столбцов и до 65536 строк.
Место пересечения столбца и строки называется ячейкой. Каждая ячейка имеет свой уникальный адрес, состоящий из имени столбца и номера строки, например А28, Р45 и т.п.
Блок используемых ячеек можно выделить двумя путями: непосредственно набором с клавиатуры начального и конечного адресов ячеек, формирующих диапазон, либо выделением соответствующей части таблицы с помощью клавиш управления курсором.
Вычисления в электронных таблицах Excel осуществляются при помощи формул. Формула может содержать числовые константы, ссылки на ячейки и функции Excel, соединенные знаками математических операций.
Ссылки на ячейки можно задать разными способами. По умолчанию ссылки на ячейку в формулах рассматриваются как относительные.
Относительные ссылки— это ссылки, которые при копировании формулы изменяются автоматически в соответствии с относительным расположением исходной ячейки и создаваемой копией (Н4).
Абсолютные ссылки— это ссылки, которые при копировании не изменяются ($Н$4).
Смешанные ссылки— это ссылки, которые сочетают в себе и относительную и абсолютную адресацию ($Н4, Н$4).
Для изменения способа адресации при редактировании формулы надо выделить ссылку на ячейку и нажать клавишу .
Функции в Excelпредназначены для вычисления базовых величин, необходимых при проведении сложных финансовых, статистических, математических и т. д. расчетов. Методика использования функций требует соблюдения определенной технологии.
1. На рабочем листе в отдельных ячейках осуществляется подготовка значений основных аргументов функции.
2. Осуществляется вызов Мастера функции с помощью команды Вставка —> Функция или нажатием одноименной кнопки на панели инструментов Стандартная .
4. Выполняется выбор в списке требуемой функции, в результат выбора появляется диалоговое окно для ввода аргументов. Для каждой функции существует регламентированный по составу и формату значений перечень аргументов.
5. В поле ввода диалогового окна можно вводить как ссылки на адреса ячеек, содержащих собственно значения аргументов, так и сами значения аргументов.
6. Если аргумент является результатом расчета другой встроенной функции Excel, возможно организовать вычисление вложенной, встроенной функции путем вызова Мастера функции одноименной кнопкой, расположенной перед полем ввода аргументов.
7. Для отказа от работы со встроенной функцией нажимается кнопка Отмена.
8. Завершение ввода аргументов и запуск расчета значения встроенной функции выполняется нажатием кнопки Готово.
9. Формула начинается со знака = (равно). Далее следует имя функции, а в круглых скобках указываются аргументы в последовательности, соответствующей синтаксису функции. В качестве разделителей аргументов используется выбранный при настройке Windows разделитель, обычно это точка с запятой (;) или запятая (,).
Отдельные аргументы функции могут быть как константами, так и ссылками на адреса ячеек.
Подбор параметров Excel.Вычислительные возможности Excel позволяют решать как прямые, так и обратные задачи. Выполнять исследование области допустимых значений аргументов, подбирать значение аргументов под заданное значение функции.
При нажатии кнопки ОК подобранное значение аргумента сохраняйся в ячейке аргумента, при нажатии кнопки Отмена происходит восстановление значения аргумента. При неуспешном завершении подбора параметра выдается соответствующее сообщение о невозможности подбора аргументов.
Для вариантных финансовых расчетов, основанных на задании различных значений аргументов функции, целесообразно воспользоваться сценарным подходом, реализованным средствами Excel.
Сценарий — именованная совокупность значений изменяемых ячеек. Для ячеек, являющихся аргументами функций, можно задавать различные значения. Команда Сервис —> Сценарий вызывает диалоговое окно Диспетчер сценариев для ячеек текущего рабочего листа.
В окне Сценарии представлен список сценариев текущего рабочего листа. Возможно объединение сценариев, находящихся в открытых книгах или на других листах текущей рабочей книги при нажатии кнопки Объединить. Для создания нового сценария следует нажать кнопку , при этом появляется новое диалоговое окно.
В поле Название сценария вводится имя нового сценария — последовательность символов, максимальная длина имени не более 255 знаков.
В окне Примечание можно записать поясняющий сценарий текст. По умолчанию сюда заносится имя пользователя и дата создания сценария. Имя пользователя можно изменить с помощью команды Сервис -> Параметры, вкладка Общие, поле Имя пользователя.
С помощью переключателя Запретить изменения реализуется защита значений изменяемых ячеек от редактирования. Переключатель Скрыть позволяет не показывать имя сценария в списке. При нажатии на появляется диалоговое окно для ввода значений изменяемых ячеек.
Для просмотра результатов подстановки значений изменяемых ячеек по определенному сценарию в диалоговом окне Диспетчера сценариев следует выбрать из списка имя сценария и нажать кнопку Вывести.
Excel выполняет подстановку значений изменяемых ячеек сценария и производит расчет значения функции. Все изменения будут отражены на рабочем листе в ячейках, содержащих формулы и имеющих ссылки на изменяемые ячейки сценария, новые результаты выводятся.
Кнопка Закрыть обеспечивает выход из окна Диспетчера сценариев, при этом в изменяемых ячейках сохраняются значения последнего участвовавшего в просмотре сценария. Кнопка Отчет предназначена для подготовки отчетов по сценариям, при ее нажатии появляется диалоговое окно для выбора типа итогового отчета.
В поле Ячейки результата указывается адрес ячеек, значения которых зависят от изменяемых ячеек сценариев.
итоги сценария— табличный отчет, содержащий для каждого сценария состав изменяемых ячеек и значение выбранных результатных ячеек;
свободная таблицарезультатов подстановки значений в изменяемые ячейки и вычисления результатов подстановки.
Диспетчер сценариев в Microsoft Excel
В Excel предлагаются средства анализа «что если» трех типов: сценарии, таблицы данных и подбор параметров. В сценариях и таблицах данных берутся наборы входных значений и определяются возможные результаты. Подбор параметров отличается от сценариев и таблиц данных тем, что при его использовании берется результат и определяются возможные входные значения для его получения.
Рис. 5.1. Окно программы Excel |
Лабораторная работа №7. Диспетчер сценариев
Во многих экономических задачах результат расчета зависит от нескольких параметров, которыми можно управлять. Часто бывает удобно проанализировать ситуацию для нескольких возможных параметров.
Диспетчер сценариев MS Excel позволяет автоматически выполнить анализ «что-если» для различных моделей. Можно создать несколько входных наборов данных (изменяемых ячеек) для любого количества переменных и присвоить имя каждому набору.
По имени выбранного набора данных MS Excel сформирует на рабочем листе результаты анализа. Кроме этого, диспетчер сценариев позволяет создать итоговый отчет по сценариям, в котором отображаются результаты подстановки различных комбинаций входных параметров.
Диспетчер сценариев открывается командой Сервис| Сценарии(рис. 29).
В окне диспетчера сценариев с помощью соответствующих кнопок можно добавить новый сценарий, изменить, удалить или вывести существующий, а также— объединить несколько различных сценариев и получить итоговый отчет для существующих сценариев.
Задание 1. Рассчитать внутреннюю скорость оборота инвестиций
Исходные данные: затраты по проекту составят 700 млн руб. Ожидаемые доходы в течение последующих 5 лет составят, соответственно, 70, 90,300,250, 300 млн руб. Оценить экономическую
целесообразность проекта по скорости оборота инвестиции, если рыночная норма дохода 12%. Рассмотреть также следующие варианты (затраты на проект представлены числом со знаком минус):
Для вычисления внутренней скорости оборота инвестиции (внутренней нормы доходности) используется функция ВСД.:
В нашем случае функция для решения задачи использует только аргумент Значения, один из которых обязательно отрицателен (затраты по проекту). Если внутренняя скорость оборота инвестиций окажется больше рыночной нормы доходности, то проект считается экономически целесообразным. В противном случае проект должен быть отвергнут.
•в ячейкеВ11:=ВСД(В75:В80)
•в ячейкеС11:=ЕСЛИ(В84>В82;»Проект экономически целесообразен»; «Проект необходимо отвергнуть»)
Рис. 30. Расчет внутренней скорости оборота инвестиций
2. Рассмотрим этот пример для всех комбинаций исходных данных. Для создания сценария следует использовать команду Сервис | Сценарии| кнопка Добавить(рис. 31).
Рис. 31. Добавление сценария для первой комбинации исходных данных
После нажатия на кнопку ОК появляется возможность внесения новых значений для изменяемых ячеек (рис. 32).
Для сохранения результатов по первому сценарию нет необходимости редактировать значения ячеек— достаточно нажать кнопку ОК для подтверждения значений, появившихся по умолчанию, и выхода в окно Диспетчер сценариев(рис.33).
Рис. 33 Окно Диспетчер сценариев с первым сохраненным сценарием
3. Для добавления к рассматриваемой задаче новых сценариев достаточно нажать кнопку Добавитьв окне Диспетчер сценариеви повторить вышеописанные действия, изменив значения в ячейках исходных данных (рис. 34).
На рис. 34 сценарий Скорость_оборота_1 соответствует данным (-700; 70; 90; 300; 250; 300), сценарий Скорость_оборота_2 — данным (-600; 50; 100; 200; 200; 300), сценарий Скорость_оборота_З— данным (-650; 90; 120; 200; 250; 250), сценарий скорость_оборота_4— данным (-500, 100, 100, 200,
Нажав кнопку Вывести,можно просмотреть на рабочем листе результаты расчета для соответствующей комбинации исходных значений.
Рис. 34. Окно Диспетчер сценариев с добавленными сценариями
Рис. 4.31. Добавление ячеек результата в окно Отчет по сценарию
Рис. 36 Отчет типаСтруктурапо сценариям расчета скорости оборота инвестиций
Рис. 37 Отчет типаСводная таблицапо сценариям расчета скорости оборота инвестиций
Отчет по производственной практике по экономической безопасности: К основным функциональным целям на предприятии ООО «ХХХХ» относятся.
История государства Древнего Египта: Одним из основных аспектов изучения истории государств и права этих стран является.
Лабораторная работа №7. Диспетчер сценариев
Отчеты сценария не перерассчитываются автоматически. Если вы измените значения сценария, эти изменения не будут отображаться в существующем сводном отчете, но они будут отображаться, если вы создадите новый сводный отчет.
Диспетчер сценариев в Microsoft Excel
Целью данной лабораторной работы является изучение возможностей средства Поиск решения в Microsoft Excel для решения оптимизационных задач, Диспетчера сценариев в Microsoft Excel для моделирования экономических задач.
К защите лабораторной работы студент должен предоставить файлы Поиск решения.xls и Сценарии.xls путь сохранения D:\номер группы\Анализ данных.
Какими должны быть расходы на Маркетинг и Рекламу, чтобы годовой бюджет стал равным $500 000 при стоимости каждого проекта $50 000 и расходах на маркетинг не менее, чем $6 000?.
В окне Поиск решения нажать кнопку Выполнить. Сохраните и проанализируйте найденное решение.
Постановка задачи. Имеются данные по инвестиционному портфелю:
Какие суммы необходимо вложить в каждый из шести активов (A,B,C,D,E,F), чтобы получить максимально возможную доходность портфеля с учетом определенных ограничений:
2. Сумма вложений в каждый актив – положительное число.
3. Сумма вложений в каждый актив не превышает четверти от Общей суммы вложений.
4. Сумма в надежных активах составляет не менее 2/3 от Общей суммы вложений.
5. Сумма в долгосрочных активах составляет не менее половины от Общей суммы вложений.
В рабочей книге Поиск решения.xls На листе Задача 2 задайте произвольные значения столбца Сумма вложений.
Рассчитайте значения столбца Прибыль как произведение Доходности и Суммы вложений.
С помощью функции СУММ() рассчитайте общую сумму вложений в портфель и общую прибыль портфеля.
С помощью функции СУММЕСЛИ() рассчитайте значение суммы в надежных активах и суммы в долгосрочных активах. Надежным считается актив, у которого значение Надежности не менее 4. Долгосрочным считается актив, Срок выкупа по которому наступает позднее 2007 года.
Рассчитайте в процентах Доходность портфеля как отношение Общей прибыли портфеля к Общей сумме вложений.
Выполните команду Сервис – Поиск решения. В качестве целевой ячейки выберите Общую прибыль портфеля, установите ее равной максимальному значению. Выберите столбец, содержащий значения Суммы вложений в каждый актив в качестве ячеек для изменения.
Добавьте ограничения 1-5 из условия задачи (кнопка Добавить).После заполнения диалога, нажмите кнопку Выполнить. Сохраните и проанализируйте найденное решение. Задача 3. О планировании производства.
Сколько компьютеров обеих серий необходимо произвести, чтобы получить максимальную суммарную выручку. Необходимо учесть, что компьютеры офисной серии пользуются большим спросом и их должно быть произведено в два раза больше чем компьютеров серии домашнего применения.
В рабочей книге Поиск решения.xls На листе Задача 3 задайте произвольные значения столбца Количество.
Рассчитайте значение ячейки Расход запасов (трудозатраты) как сумму произведений количества каждого вида компьютеров на нормы трудозатрат для производства 1 компьютера каждого вида. Аналогично рассчитайте Расход запасов комплектующих на производство всех компьютеров.
Выручка рассчитывается как сумма произведений количества каждого вида компьютеров на цену.
Выполните команду Сервис – Поиск решения. В качестве целевой ячейки выберите ячейку с расчетом Выручки, установите ее равной максимальному значению. Выберите столбец, содержащий значения Количество в качестве ячеек для изменения.
Добавьте ограничения из условия задачи, а также учтите, что количество произведенных компьютеров не может быть отрицательным числом и должно быть целочисленным. После заполнения диалога, нажмите кнопку Выполнить. Сохраните и проанализируйте найденное решение.
Некоторая фирма имеет 4 фабрики и 4 склада в различных городах. Фабрики имеют производственные мощности для выпуска соответственно 25, 30, 50, 20 единиц продукции ежедневно. Центры распределения товаров (склады) имеют ежедневные потребности в продукции 30, 20, 60, 15 единиц соответственно. Стоимость перевозки единицы продукции с фабрик на склады приведена в таблице
Необходимо так спланировать перевозки, чтобы минимизировать суммарные транспортные расходы при этом полностью использовать мощности производств и полностью обеспечить потребности складов.
В рабочей книге Поиск решения.xls в таблице решения рассчитайте значения строки Объемы потребления (B16:E16) и столбца Объем производства (F12:F15) как сумму по строке и столбцу соответственно. Суммарные транспортные расходы рассчитываются как сумма произведений стоимости перевозки на количество перевезенного товара (=СУММПРОИЗВ(B3:E6;B12:E15)).
Выполните команду Сервис – Поиск решения. В качестве целевой ячейки выберите ячейку с расчетом Суммарные транспортные расходы, установите ее равной минимальному значению. Выберите ячейки, содержащие Объем перевозок (B12:E15), в качестве ячеек для изменения.
Добавьте ограничения из условия задачи (полностью использовать мощности производств и полностью обеспечить потребности складов, Объемы перевозок не могут быть отрицательными числами и должны быть целочисленными) .После заполнения диалога, нажмите кнопку Выполнить. Сохраните и проанализируйте найденное решение. Задача 5.
Постановка задачи. Пусть имеются следующие экономические и статистические показатели деятельности магазина.
Использование средства Диспетчер сценариев — КиберПедия
4. Если вы хотите запретить изменения сценария, то установите флажок Запретить изменения (рис. 5.8). Если вы хотите скрыть сценарий от других пользователей, установите флажок Скрыть. После этого вам необходимо настроить защиту с помощью диалогового окна Защита листа, выбрав команду Рецензирование, Изменения, Защитить лист.