ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 21.10.2020
Просмотров: 1379
Скачиваний: 11
Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных
Центр Компьютерного обучения «Специалист»
www.specialist.ru
41
Отчеты создаются на отдельном листе с именем, соответствующему типу отчета –
Структура
сценария
или
Сводная таблица по сценарию
:
Посмотрев отчет, можно выбрать оптимальный сценарий. Для этого в окне
Диспетчера сценариев
нажать кнопку
Вывести
[Show].
В окне
Диспетчера имен
можно:
Редактировать сценарии – нажать кнопку
Изменить
[Edit].
Удалять сценарии – нажать кнопку
Удалить
[Delete].
Объединять сценарии с других листов как этой книги, так и разных книг, с помощью команды
Объединить
[Merge].
Сценарий не запоминает значения исходных данных, поэтому для их сохранения с целью
последующего обращения, имеет смысл создать первый сценарий с исходными данными.
Решение однокритериальной задачи оптимизации с помощью
Подбора параметра
Подбор параметра выполняет поиск такого исходного значения, при котором по формуле будет
получен необходимый результат.
1.
На вкладке
Данные
[Data], в группе
Работа с данными
[Data Tools], из кнопки
Анализ "что
если"
[What–If Analysis] выбрать
Подбор параметра
[Goal Seek].
2.
В диалоговом окне
Подбор параметра
[Goal Seek]:
Установить в ячейке
[Set cell] – адрес ячейки (ячейка должна содержать формулу), в
которой нужно получить требуемый результат.
З
начение
[Value] – значение необходимого результата по формуле.
Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных
www.specialist.ru
Центр Компьютерного обучения «Специалист»
42
Изменяя значение ячейки
[By changing cell] – адрес ячейки (ячейка может содержать
только значение), изменением значения которой достигается получение нужного
результата.
ПРИМЕР
: Определить значение ежемесячной выплаты, чтобы выплатить кредит за 5 лет (60
месяцев).
Минус такого способа подбора параметра:
Подбираемое значение может быть только одно.
Нельзя задать ограничения на подбираемое значение.
Решение многокритериальных задач оптимизации с
использованием надстройки Поиск решения
Поиск решения – это надстройка Excel, предназначенная для решения сложных задач
многопараметрической оптимизации с учетом набора ограничений, заданных пользователем.
Процедура поиска решения позволяет найти оптимальное решение задачи, цель которой –
установить в целевой ячейке максимальное, минимальное или конкретное значение.
ПОДКЛЮЧЕНИЕ НАДСТРОЙКИ ПОИСК ПЕШЕНИЯ
:
Выбрать
Файл
[File],
Параметры
[Options].
Перейти в раздел
Надстройки
[Add-Ins], в списке
Управление
[Manage] выбрать
Надстройки
Excel
[Excel Add-ins], нажать кнопку
Перейти
[Go To]
В окне Надстройки [Add-Ins] включить
Поиск решения
[Solver Add-Ins],
ОК
.
РАБОТА в окне ПОИСК РЕШЕНИЯ
:
1.
На вкладке
Данные
*Data+ в группе
Анализ
[Analysis]
,
выбрать команду
Поиск решения
[Solver].
2.
Выполнить следующие настройки:
Оптимизировать целевую ячейку
[Set Objective] – ячейка, в которой нужно получить либо
минимальное, либо максимальное значение. Указать адрес ячейки, содержащей формулу
для вычисления значений оптимизируемой функции.
Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных
Центр Компьютерного обучения «Специалист»
www.specialist.ru
43
До
[To] – до какого значения оптимизировать целевую ячейку. Выбрать
Максимум
[Maximum],
Минимум
[Minimum] или
Значение
[Value] в зависимости от цели. Например,
минимизировать затраты, максимизировать прибыль или получить определенное
значение.
Изменяя ячейки переменных
[By Changing Variable Cells] – указать ячейки, значения
которых варьируются для достижения результата.
В соответствии с ограничениями
[Subject to the Constrains] – список ограничений, которые
надо учитывать при выполнении оптимизации (например, вместимость склада, бюджет
проекта, продолжительность процесса и т.д.).
Чтобы задать ограничение, нажать
кнопку
Добавить
[Add] и ввести условие.
Если условий несколько, то нажать
Добавить
[Add] и задать следующее
условие, по окончании нажать
ОК
.
3.
Нажать
Найти решение
[Solve].
4.
По окончании вычисления предлагается сделать выбор:
Сохранить найденное решение
[Keep Solver Solution] или
Восстановить исходные значения
[Restore Original Values]
Завершить работу нажатием на
ОК
или
Вернуться в диалоговое окно
параметров
[Return to Solve
Parameters Dialog] для уточнения
параметров решения.
Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных
www.specialist.ru
Центр Компьютерного обучения «Специалист»
44
Модуль 5.
К
УБЫ ДАННЫХ
OLAP
ДЛЯ ОПЕРАТИВНОГО
АНАЛИЗА ДАННЫХ В
MS
E
XCEL
Введение в кубы данных: что такое кубы OLAP и их назначение
Технология OLAP или оперативная аналитическая обработка данных (
O
n
L
ine
A
nalytical
P
rocessing)
– класс приложений и технологий, предназначенных для сбора, хранения и анализа многомерных
данных в реальном времени.
Высокую скорость работы с многомерными данными позволяет обеспечивать многомерная база
данных со специальной организацией хранения.
Данные в многомерной базе данных хранятся как совокупность измерений. Визуализация
двумерной базы данных – электронная таблица, где строки и столбцы являются измерениями.
Визуализация трехмерной базы данных – куб, где каждое измерение формирует одну из сторон
куба.
Преимущества использования кубов данных OLAP. Сравнение
запросов к внешним базам данных и кубов OLAP
Главным преимуществом использования кубов перед прямыми запросами в базу данных является
высокая производительность, так как данные перемещаются и агрегируются в промежуточном
хранилище и тем самым выборка и обработка данных производится максимально эффективным
способом. С помощью многомерного КУБА отчеты получаются динамичными.
Недостаток данного метода – данные OLAP-куба могут содержать неактуальную информацию, что
зависит от настроек хранилища.
Преимущества использования кубов данных OLAP:
Быстрый доступ к данным
, независимо от размеров массива, и является основой OLAP
систем. Хранилище данных строится по принципам, отличным от принципов реляционных
баз данных. Время на выборку простых данных измеряется в долях секунды, а запрос,
превышающий несколько секунд, скорее всего, требует оптимизации.
Преагрегация
– возможность анализировать «наиболее вероятно-используемые» значения.
Например, если есть ежедневные записи о продажах какого-то товара, система может
преагрегировать также месячные и квартальные суммы продаж, и на запрос мгновенно
выдаст результат. Преагрегация происходит не всегда – т.к. теоретически возможных
комбинаций товаров/времени/и т.д. может быть огромное количество, а значит, нужно иметь
четкие правила для каких элементов агрегация будет построена, а для каких нет.
Иерархии
– OLAP кубы изначально рассматривают данные с точки зрения иерархий и
взаимоотношений с другими параметрам одной и той же сущности. Анализируя данные,
возникает потребность учитывать то, что месяцы состоят из дней, а сами образуют кварталы,
а города входят в области, которые в свою очередь являются частью регионов или стран и т.д.
Построение и использования иерархией в кубах выполняется очень просто.
Microsoft Excel 2010. Уровень 3. Анализ и Визуализация данных
Центр Компьютерного обучения «Специалист»
www.specialist.ru
45
Работа с временем
– определив для системы, где время, в дальнейшем можно с легкостью
пользоваться функциями типа: Year To Date, Month To Date (период от начала года/месяца и
до текущей даты) и Parallel Period (в этот же день или месяц, но в прошлом году) и т.п.
Язык доступа к многомерным данным
MDX
(Multidimensional Expressions) – язык запросов
для простого и эффективного доступа к многомерным структурам данных.
Ключевые показатели эффективности
(KPI – Key Performance Indicators) – это финансовая и
нефинансовая система оценки, которая помогает организации определить достижение
стратегических целей. Ключевые показатели эффективности могут быть достаточно просто
определены в OLAP системах и использоваться в отчетах.
Дата майнинг
(Data Mining) – интеллектуальный анализ данных. Подход, позволяющий
экспериментальным путем исследовать поведение аналитических моделей для различных
входных воздействий.
Многоуровневое кэширование
– для обеспечения наиболее высокой скорости доступа к
данным, OLAP системы поддерживают многоуровневое кэширование. Происходит
кэширование: простых запросов, части вычитанных из хранилища данных, агрегированные
значения, вычисленные значения. Чем дольше работаешь с OLAP кубом, тем быстрее он
начинает работать. Существует понятие «разогрев кэша» — операция, подготавливающая
OLAP систему к работе с конкретными отчетами, запросами или всем вместе взятым.
OLAP-анализ данных в кубах Excel
OLAP-клиент – приложение оперативной аналитической обработки данных, которое позволяет
пользователям выполнять нужный анализ.
Microsoft Office Excel 2010, как элементарный OLAP-клиент, позволяет производить операции над
многомерными данными, извлекаемыми из службы поддержки принятия решений (SQL Server
Analysis Services) или локальных OLAP-кубов.
3-х мерное пространство: Время, Товары, Покупатели