Бизнес-аналитика с помощью PowerPivot в Microsoft Excel::БИТ 05.2014
 
                 
Поиск по сайту
 bit.samag.ru     Web
Рассылка Subscribe.ru
подписаться письмом
Вход в систему
 Запомнить меня
Регистрация
Забыли пароль?

Календарь мероприятий
сентябрь    2023
Пн
Вт
Ср
Чт
Пт
Сб
Вс
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

показать все 

Новости партнеров

26.09.2023

Актуальные тренды России и Индии в области ИТ и цифровизации

Читать далее 

25.09.2023

В Хабаровске объявлены победители хакатона по искусственному интеллекту

Читать далее 

22.09.2023

Искусственный интеллект на Дальнем Востоке: в Хабаровске стартовал окружной хакатон для ИТ-специалистов

Читать далее 

22.09.2023

Итоги первого дня CyberCamp 2023: инсайты от спикеров и состязание фракций

Читать далее 

показать все 

Статьи

22.09.2023

Эпоха российской ориентации на Запад в сфере программного обеспечения завершилась

Читать далее 

22.09.2023

Сладкая жизнь

Читать далее 

22.09.2023

12 бизнес-концепций, которыми должны овладеть ИТ-руководители

Читать далее 

22.09.2023

Проще, чем кажется. Эталонная модель документооборота или краткое руководство по цифровой трансформации

Читать далее 

22.09.2023

Какие hard skills вам нужны?

Читать далее 

22.09.2023

Какие hard skills вам нужны?

Читать далее 

25.07.2023

Как изменится ИТ-мир через 35 лет?

Читать далее 

25.07.2023

Тотальный контроль или разумная опека?

Читать далее 

03.07.2023

Property technologies: тренды и инновации

Читать далее 

20.06.2023

Завтра, завтра – не сегодня

Читать далее 

показать все 

Бизнес-аналитика с помощью PowerPivot в Microsoft Excel

Главная / Архив номеров / 2014 / Выпуск №5 (38) / Бизнес-аналитика с помощью PowerPivot в Microsoft Excel

Рубрика: Инструменты управления


Кулешова ОльгаМСТ, старший преподаватель Центра компьютерного обучения «Специалист» при МГТУ им. Н.Э. Баумана

Бизнес-аналитика
с помощью 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

Рисунок 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. Выбор импортируемых таблиц

Рисунок 2. Выбор импортируемых таблиц

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

Рисунок 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 – четыре диаграммы

Рисунок 4. Вариант отчета PowerPivot – четыре диаграммы

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

В отчетах сводных таблиц можно оценивать данные с помощью показателя эффективности KPI. Ключевые показатели эффективности на уровне бизнес-процессов позволяют анализировать и контролировать основные моменты. Можно строить отчеты и самостоятельно настраивать наглядное отображение данных с помощью условного форматирования, но ведь этим нужно будет заниматься снова и снова в каждом отчете (см. рис. 5). Создавая ключевой показатель эффективности в PowerPivot, его можно будет использовать в каждом создаваемом отчете, построенном по определенному источнику.

Рисунок 5. Отчет модели PowerPivot c KPI и срезом

Рисунок 5. Отчет модели PowerPivot c KPI и срезом

Во всех создаваемых отчетах для удобства фильтрации можно использовать срезы – быстро, удобно, наглядно. Срезы можно форматировать, и их можно упорядочивать как вручную, так и с помощью команд «Выровнять вертикально» или «Выровнять горизонтально». Работа со срезами аналогична работе с ними в обычных сводных таблицах начиная с Excel 2010.

Отчеты PowerView

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

Для построения отчета необходимо в окне Excel выбрать на вкладке «Вставка» команду PowerView. Чтобы добавить нужные поля в отчет, их следует переместить в область отчета или в область поля внизу области «Поля Power View» (аналог области СТРОКИ в отчетах сводных таблиц). Если перетащить поле из списка полей в область ПРАВИЛО ФРАГМЕНТИРОВАНИЯ, то затем можно будет быстро осуществлять выбор данных этого поля (фильтрация). Любые данные в отчете могут быть представлены как в табличном, так и графическом виде, как по отдельности, так и вместе.

Чтобы одновременно показать данные в двух представлениях, следует скопировать таблицу в свободное место отчета, а затем выбрать нужный вариант отображения из предлагаемых: Таблица, Линейчатая диаграмма, Гистограмма или Другая диаграмма (Круговая, Точечная). Щелкнув по элементу диаграммы, устанавливаете фильтр по этому значению, а также и в других объектах отчета (см. рис. 6).

Рисунок 6. Отчет PowerPivot

Рисунок 6. Отчет PowerPivot

Отчеты PowerView – это динамические отчеты. Фильтрация в одном элементе отчета сразу перекликается с данными другого элемента отчета, если они построены по данным одной или нескольких связанных таблиц. Можно также выполнять построение в одном отчете по данным нескольких таблиц, абсолютно не связанных друг с другом, но позволяющих анализировать данные одновременно.

Отчеты PowerView – это быстрый интерактивный просмотр данных, наглядная визуализация, различные наглядные способы предоставления данных.

В чем сходство модели данных PowerPivot и отчетов PowerView?

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

Отчеты PowerView: быстро строятся, интуитивно понятны, динамичны. Это те качества, которые крайне необходимы для пользователя, который постоянно сталкивается с большими объемами данных.

В начало⇑

 

Комментарии отсутствуют

Комментарии могут отставлять только зарегистрированные пользователи

Выпуск №6 (129) 2023г.
Выпуск №6 (129) 2023г. Выпуск №5 (128) 2023г. Выпуск №4 (127) 2023г. Выпуск №3 (126) 2023г. Выпуск №2 (125) 2023г. Выпуск №1 (124) 2023г.
Вакансии на сайте Jooble

           

Tel.: (499) 277-12-41  Fax: (499) 277-12-45  E-mail: sa@samag.ru

 

Copyright © Системный администратор

  Яндекс.Метрика