|
Бизнес-аналитика с помощью PowerPivot в Microsoft Excel
Главная /
Архив номеров / 2014 / Выпуск №5 (38) / Бизнес-аналитика с помощью PowerPivot в Microsoft Excel
Рубрика:
Инструменты управления
Facebook
Мой мир
Вконтакте
Одноклассники
Google+
Кулешова Ольга, МСТ, старший преподаватель Центра компьютерного обучения «Специалист» при МГТУ им. Н.Э. Баумана
Бизнес-аналитика
с помощью PowerPivot в Microsoft Excel
Чем новее версия Excel, тем больше инструментов для анализа данных. Рассмотрим возможности анализа больших объемов данных с помощью модели PowerPivot и отчетов PowerView
Такие инструменты, как PowerPivot сам по себе и особенно в совокупности с PowerView, представляют собой современные средства бизнес-аналитики (BI – Business Intelligence), способные хранить, обрабатывать и визуализировать огромное количество данных как из одного, так и из множества источников.
Где PowerPivot в Excel?
Возможность анализа данных с помощью PowerPivot доступна:
- пользователям Microsoft Office 2013 Professional Plus или Office 365 ProPlus;
- а также пользователям версии Microsoft Excel 2010.
Power Pivot в Microsoft Excel 2013 – это надстройка Microsoft Office PowerPivot for Excel 2013, которая встроена в Excel 2013, но не включена, поэтому для дальнейшей работы с PowerPivot необходимо ее включить в списке «Надстройки Com».
Обладателям Excel 2010 для того, чтобы пользоваться PowerPivot, необходимо предварительно скачать компонент PowerPivot (PowerPivot_for_Excel.msi) из Центра Загрузки Microsoft Download Center. Следует обратить внимание на разрядность версии надстройки – 32 или 64 разряда – и выбрать нужную.
Также надстройку нельзя будет использовать, если установлено приложение Excel 2010, но при этом не установлены общие средства Office.
Надстройка PowerPivot – это вкладка с соответствующим названием (см. рис. 1). Начало работы с моделью начинается с нажатия кнопки «Управление».
Рисунок 1. Вкладка PowerPivot в Excel 2013
Источники модели PowerPivot
Источником для PowerPivot могут служить:
- Реляционные базы данных: Microsoft SQL Server, Microsoft SQL Azure, хранилище данных Microsoft SQL Server Parallel Data, Microsoft Access, Oracle, Teradata, Sybase, Informix, IBM DB2, другие (OLEDB/ODBC).
- Многомерные источники: службы Microsoft Analysis Services.
- Веб-каналы данных.
- Текстовые файлы: файлы Excel, текстовые файлы (*.csv или *.txt).
В процессе получения внешних данных у пользователя всегда есть возможность выбрать таблицы, из которых нужно импортировать данные. Причем это можно сделать как самостоятельно, так и прибегнуть к помощи программы – для выбора связанных таблиц нужно выбрать одну таблицу, а затем воспользоваться командой «Выбрать связанные таблицы» (см. рис. 2).
Рисунок 2. Выбор импортируемых таблиц
Если в исходной таблице содержится слишком много данных, но анализировать нужно лишь определенную часть, можно на этапе импорта данных осуществить фильтрацию – выделить таблицу и нажать кнопку «Просмотр и фильтрация». В появившемся окне снимаем флажок у тех столбцов (полей), которые не нужно включать в импорт, и по любому столбцу при необходимости устанавливаем условия отбора данных с помощью фильтра, как и при работе с таблицами в Excel. В процессе импорта будет отображаться ход выполнения процесса, а по завершении будет предоставлен отчет о выполнении операции – какие таблицы с каким количество строк и как успешно импортированы (см. рис. 3).
Рисунок 3. Отчет о завершении импорта
Процесс импорта завершен. Результат – окно PowerPivot для Excel, которое содержит вкладки с названиями импортируемых таблиц: «Заказы» и «Сотрудники». Каждый из источников может иметь количество записей, значительно превышающее количество строк на листе Excel.
Особенности и возможности PowerPivot
- При выполнении импорта можно выполнять фильтрацию, переименовывать столбцы и таблицы. Каждая таблица располагается на отдельной странице (вкладке) окна PowerPivot.
- В PowerPivot нельзя редактировать данные отдельных ячеек.
- Таблицы из других источников могут быть импортированы сразу с сохранением связей, или связи могут быть созданы непосредственно в PowerPivot в двух местах: в представлении диаграммы или окне «Создание связей».
- Объемы анализируемых данных могут быть ограничены только объемом оперативной памяти.
- В PowerPivot расширенные формулы на языке выражений анализа данных (DAX).
- Для удобства работы можно выстроить подчинение данных в виде иерархий, чтобы в дальнейшем их использовать в отчетах.
- Для анализа состояния определенных значений (полей) в PowerPivot создаются ключевые показатели эффективности (KPI), которые можно использовать как в отчетах PowerView, так и в сводных таблицах.
Вычисления в источниках PowerPivot
Вместо вставки или импорта в столбец значений создается формула DAX, определяющая его значения. С помощью DAX-формул можно задавать пользовательские вычисления в таблицах PowerPivot (вычисляемые столбцы) и сводных таблицах Excel (вычисляемые поля). DAX включает некоторые функции, используемые в формулах Excel, а также дополнительные функции для работы с реляционными данными и создания динамических агрегатов.
Можно встретить следующие категории функций DAX: Дата и время, Фильтр, Логические, Арифметические и Тригонометрические функции, Статистические, Текст, Информация, Родители-потомки. В большинстве своем они полностью повторяют возможности аналогичных функций Excel, что не требует их повторного изучения.
Однако есть и маленькие отличия по сравнению с обычными вычислениями в Excel: PowerPivot не добавляет закрывающие скобки функций и не выполняет автоматический подбор скобок. Поэтому, если функция составлена синтаксически правильно, расчет произойдет, а иначе формулу нельзя ни сохранить, ни использовать.
При создании формул нужно понимать разницу между вычисляемым полем и вычисляемым столбцом.
Вычисляемое поле – это формула, созданная специально для сводной таблицы (или сводной диаграммы) и использующая данные PowerPivot. Вычисляемые поля могут быть основаны на стандартных функциях, таких как суммы, средние значения, минимальные и максимальные значения, число элементов и более сложные вычисления, создаваемые по формулам DAX.
Вычисляемое поле используется в области Значения сводной таблицы. Значение вычисляемого поля всегда изменяется в ответ на выбор строк, столбцов и фильтров. При добавлении вычисляемого поля формула составляется для каждой ячейки в области Значения сводной таблицы. Вычисляемое поле сохраняется вместе с таблицей исходных данных, отображается в списке полей сводной таблицы и доступно для всех пользователей книги.
Если нужно разместить результаты вычислений в другой области сводной таблицы, то нужно создавать вычисляемый столбец. Как и в Excel, при создании формулы, можно использовать автозавершение формул: для выбора данных из таблицы – ввести первую букву таблицы, чтобы выбрать имя столбца – ввести квадратную скобку и из предлагаемого списка столбцов текущей таблицы выбрать нужный.
При работе с данными в PowerPivot надо знать о неявных и явных вычисляемых полях.
Неявное вычисляемое поле создается приложением Excel при перетаскивании поля в область Значения списка полей сводной таблицы. Это поле создается Excel автоматически, его легко заметить – при размещении такого поля в области Значения к его имени сразу добавляется Сумма по столбцу.
Неявное вычисляемое поле может использовать только стандартные агрегатные функции: SUM, COUNT, MIN, MAX, DISTINCT, COUNT, AVG и формат данных, заданный для этого агрегирования. Неявные вычисляемые поля можно использовать только в сводной таблице или диаграмме, для которой они создавались.
Явные вычисляемые поля можно использовать в любой сводной таблице или сводной диаграмме, в книге и отчетах PowerView. Более того, их можно превратить в ключевые показатели эффективности или форматировать. Создание ключевого показателя эффективности возможно только при использовании явного вычисляемого поля.
При создании вычисляемого поля в модели PowerPivot в одной формуле, как и в формулах Excel, допустимо использование до 64 уровней вложенности функций, все строится по принципу:
Имя_вычисляемого_поля:=Формула
Отчеты по модели PowerPivot
Исходные данные модели содержатся в окне PowerPivot для Excel, а сами отчеты строятся в окне Microsoft Excel.
Отчеты по моделям данных могут быть построены из предложенных вариантов любые: сводная таблица, сводная диаграмма, диаграмма и таблица (горизонтально или вертикально), две диаграммы (горизонтально или вертикально), четыре диаграммы, плоская сводная таблица (см. рис. 4).
Рисунок 4. Вариант отчета PowerPivot – четыре диаграммы
Сделав выбор с количеством отображаемых отчетов в табличном или графическом виде, необходимо сформировать их содержимое. Особенностью построения данных отчетов является то, что каждая область является независимой от других, т.к. можно выполнять различные фильтрации, которые будут действовать только в определенной области отчета и не действовать в другой. Сложностей с построением и форматированием отчетов не возникает, так как построение сводных таблиц и диаграмм происходит так же, как просто по данным на листе Excel.
В отчетах сводных таблиц можно оценивать данные с помощью показателя эффективности KPI. Ключевые показатели эффективности на уровне бизнес-процессов позволяют анализировать и контролировать основные моменты. Можно строить отчеты и самостоятельно настраивать наглядное отображение данных с помощью условного форматирования, но ведь этим нужно будет заниматься снова и снова в каждом отчете (см. рис. 5). Создавая ключевой показатель эффективности в PowerPivot, его можно будет использовать в каждом создаваемом отчете, построенном по определенному источнику.
Рисунок 5. Отчет модели PowerPivot c KPI и срезом
Во всех создаваемых отчетах для удобства фильтрации можно использовать срезы – быстро, удобно, наглядно. Срезы можно форматировать, и их можно упорядочивать как вручную, так и с помощью команд «Выровнять вертикально» или «Выровнять горизонтально». Работа со срезами аналогична работе с ними в обычных сводных таблицах начиная с Excel 2010.
Отчеты PowerView
PowerView – это отчеты, которые также можно построить по данным одной или нескольких таблиц с созданием связи, а также предоставление информации как в табличном, так и графическом виде. В одном отчете, который строится на отдельном листе, можно построить множество вариаций, причем при фильтрации данных получается прекрасная визуализация данных.
Для построения отчета необходимо в окне Excel выбрать на вкладке «Вставка» команду PowerView. Чтобы добавить нужные поля в отчет, их следует переместить в область отчета или в область поля внизу области «Поля Power View» (аналог области СТРОКИ в отчетах сводных таблиц). Если перетащить поле из списка полей в область ПРАВИЛО ФРАГМЕНТИРОВАНИЯ, то затем можно будет быстро осуществлять выбор данных этого поля (фильтрация). Любые данные в отчете могут быть представлены как в табличном, так и графическом виде, как по отдельности, так и вместе.
Чтобы одновременно показать данные в двух представлениях, следует скопировать таблицу в свободное место отчета, а затем выбрать нужный вариант отображения из предлагаемых: Таблица, Линейчатая диаграмма, Гистограмма или Другая диаграмма (Круговая, Точечная). Щелкнув по элементу диаграммы, устанавливаете фильтр по этому значению, а также и в других объектах отчета (см. рис. 6).
Рисунок 6. Отчет PowerPivot
Отчеты PowerView – это динамические отчеты. Фильтрация в одном элементе отчета сразу перекликается с данными другого элемента отчета, если они построены по данным одной или нескольких связанных таблиц. Можно также выполнять построение в одном отчете по данным нескольких таблиц, абсолютно не связанных друг с другом, но позволяющих анализировать данные одновременно.
Отчеты PowerView – это быстрый интерактивный просмотр данных, наглядная визуализация, различные наглядные способы предоставления данных.
В чем сходство модели данных PowerPivot и отчетов PowerView?
Работать с моделью данных PowerPivot может каждый пользователь: не требуется установки и настройки множества специальных программ – все сразу в одной надстройке; данные в модель добавляются простыми операциями импорта с возможностью обновления; без дополнительных знаний о реляционных базах данных можно создавать связи между таблицами; используя уже знакомые формулы, можно создавать вычисления в модели.
Отчеты PowerView: быстро строятся, интуитивно понятны, динамичны. Это те качества, которые крайне необходимы для пользователя, который постоянно сталкивается с большими объемами данных. В начало⇑
Facebook
Мой мир
Вконтакте
Одноклассники
Google+
Комментарии отсутствуют
Комментарии могут отставлять только зарегистрированные пользователи
|
_cover.jpg)
_cover.jpg)
_cover.jpg)
_cover.jpg)
