ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 21.10.2020
Просмотров: 1373
Скачиваний: 11
Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных
www.specialist.ru
Центр Компьютерного обучения «Специалист»
36
Создать имена:
3.
СОЗДАНИЕ ДИАГРАММЫ
:
Выделить исходные данные – столбец
Дата
и
Курс€
.
На вкладке
Вставка
[Insert] выбрать
Гистограмма
[Column] или
График
[Line].
4.
СВЯЗЬ РЯДОМ ДИАГРАММЫ С ИМЕНОВАННЫМИ ДИАПАЗОНАМИ:
Выделить ряд на диаграмме. В строке формул отображается функция
РЯД
*SERIES+, которая
формирует данные для диаграммы:
Связать ряды данных
Дата
и
Курс€
с именованными диапазонами, используя название
файла:
'Динамические диаграммы'!$A$4:$A$125
Диаграммы.xlsx!Дата
'Динамические диаграммы'!$B$4:$B$125
Диаграммы.xlsx!КурсЕвро
Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных
Центр Компьютерного обучения «Специалист»
www.specialist.ru
37
Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных
www.specialist.ru
Центр Компьютерного обучения «Специалист»
38
Модуль 4.
В
АРИАТИВНЫЙ АНАЛИЗ
"Ч
ТО
Е
СЛИ
"
И
О
ПТИМИЗАЦИЯ
При выполнении различных расчетов в Excel возникает необходимость в проведении сценарного
анализа, т.е. смоделировать и сравнить между собой разные варианты развития событий. В
программе это реализуется через
Анализ "что-если"
, который включает в себя Таблицы данных
(Таблицы подстановок), Сценарии и Подбор параметра.
Использование инструмента Таблица данных для анализа
развития ситуации при 2-х переменных
Таблица данных позволяют одновременно проанализировать влияние 1-го или 2-х параметров на
результат.
Последовательность действий для анализа ситуаций:
1.
Построение исходных данных для анализа:
Создать таблицу с исходными данными анализируемого процесса – например, сумма
кредита (ячейка
С4
), срок (ячейка
С5
) и годовая процентная ставка (ячейка
С6
).
Написать формулу для расчета значений – например, ячейка
B11
.
В ячейки
справа от формулы
ввести значения варьируемого параметра – например, сроки
погашения кредита (ячейки
С11:G11
).
В ячейки
ниже от формулы
ввести значение другого варьируемого параметра – например,
годовые процентные ставки (ячейки
B12:B16
).
2.
Выделить всю таблицу, начиная с формулы (ячейки
B11:G16
).
3.
На вкладке
Данные
[Data] из кнопки
Анализ "что если"
[What–If Analysis], выбрать
команду
Таблица данных
[Data Table].
4.
Ввести адреса ячеек, участвующих в формуле:
Подставлять значение по столбцам в
[Row input cell]– в таблице в столбцах находятся
сроки кредита, поэтому выделяем ячейку
C5
, которая в формуле отвечает за этот
параметр.
Подставлять значения по строкам в
[Column input cell]– в таблице в строках находятся
процентные ставки, поэтому выделяем ячейку
C6
, которая в формуле отвечает за этот
параметр.
Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных
Центр Компьютерного обучения «Специалист»
www.specialist.ru
39
{=ТАБЛИЦА(C5;C6)}
– формула ячеек результатов, которая показывает, что идет варьирование
параметров, которые располагаются в ячейках
С5
и
С6
, причем саму формулу можно увидеть в
верхней левой ячейке этой таблицы.
Анализ данных с помощью таблиц подстановки является весьма эффективным, однако он имеет
несколько недостатков:
Одновременно можно анализировать расчетные данные только при изменении одного или
двух исходных параметров.
Процесс создания таблицы подстановки интуитивно не всегда понятен.
При использовании таблицы подстановки с двумя изменяемыми параметрами можно
проанализировать результаты расчетов, проведенных только по одной формуле, а для других
формул нужно создать дополнительные таблицы подстановки.
Оценка развития ситуации и выбор оптимальной стратегии с
помощью Сценариев
Сценарий позволяет создавать различные модели расчетов в зависимости от изменяемых
параметров, которые сохранены в виде сценариев и хранятся вместе с листом .
Сценарии можно использовать для прогноза результатов моделей и систем расчетов,
максимальное количество переменных в одном сценарии 32.
Создание сценариев
1.
Для удобства ввода исходных значений каждого сценария и придания наглядности отчетам
сценариев, присвоить изменяемым и результирующим ячейкам имена.
Выделить ячейки вместе с заголовками.
На вкладке
Формулы
[Formulas+ выбрать
Создать из выделенного
[Create from Selection]
или нажать клавиши
Ctrl
+
Shift
+
F3
.
Выбрать расположение заголовков по отношению к ячейкам с данными.
Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных
www.specialist.ru
Центр Компьютерного обучения «Специалист»
40
2.
На вкладке
Данные
*Data+, развернуть список
Анализ "что если"
[What-if Analysis+ и выбрать
Диспетчер сценариев
[Scenario Manager].
3.
В диалоговом окне
Диспетчер сценариев
[Scenario Manager] нажать кнопку
Добавить
[Add] и
заполнить данными:
Название сценария
[Scenario
Name] – краткое название
сценария.
Изменяемые ячейки
[Changing
cells] – ячейки, значения которых
будут меняться от одного
сценария к другому
(изменяемые параметры
расчета).
Примечание
[Comment] –
заполняется по необходимости, по умолчанию указывается Автор и дата создания,
которые можно редактировать.
ОК
.
В окне
Значения ячеек сценария
[Scenario
Values] ввести исходные значения
сценария.
Чтобы добавить следующий сценарий
нажать
Добавить
[Add], по завершении
нажать
ОК
.
Создание отчета
Отчет создается для сравнения сценариев с целью выбрать наиболее оптимальный для
конкретной ситуации. Для этого:
1.
На вкладке
Данные
*Data+, развернуть список
Анализ "что если"
[What-if Analysis+ и выбрать
Диспетчер сценариев
[Scenario Manager].
2.
Нажать кнопку
Отчет
[Summary].
3.
В окне
Отчет по сценарию
[Scenario Summary] выбрать:
Тип отчета
[Report Type]:
Структура
[Scenario summary] – отчет, в котором
показаны исходные значения и получаемые
результаты.
Сводная таблица
[Scenario PivotTable report] – отчет, в
котором показаны только получаемые результаты.
Ячейки результата
[Result cells] – ячейки с формулами, где будут получены результаты для
анализа ситуаций.
4.
ОК
.