Учебное пособие по Power Pivot для Excel: основные варианты использования и примеры

Управляющее резюме

Загрузите набор данных здесь, чтобы следовать руководству.

Новая одежда императора: руководство по Power Pivot

В различных областях и подполях, которые охватывают финансы, финансовый анализ, финансовые рынки и финансовые инвестиции, Microsoft Excel лидирует. Однако с появлением и экспоненциальным ростом больших данных, вызванным десятилетиями сбора и накопления данных, появлением дешевых облачных хранилищ и развитием Интернета вещей, т. е. электронной коммерции, социальных сетей и взаимосвязанных устройств, Excel унаследованные функциональные возможности и возможности были доведены до предела.

В частности, инфраструктура Excel предыдущего поколения и ограничения обработки, такие как ограничение на количество строк в 1 048 576 строк или неизбежное замедление обработки при работе с большими наборами данных, таблицами данных и взаимосвязанными электронными таблицами, снизили его удобство использования в качестве эффективного инструмента больших данных. Однако в 2010 году Microsoft добавила в Excel новое измерение под названием Power Pivot. Power Pivot предложила Excel функции бизнес-аналитики и бизнес-аналитики следующего поколения, благодаря своей способности извлекать, объединять и анализировать почти неограниченные наборы данных без снижения скорости обработки. Однако, несмотря на его выпуск восемь лет назад, большинство финансовых аналитиков до сих пор не знают, как использовать Power Pivot, а многие даже не подозревают о его существовании.

В этой статье я покажу вам, как использовать Power Pivot для решения распространенных проблем с Excel, и рассмотрю дополнительные ключевые преимущества программного обеспечения на нескольких примерах. Это руководство по Power Pivot предназначено для того, чтобы служить руководством для того, чего вы можете достичь с помощью этого инструмента, и в конце в нем будут рассмотрены некоторые примеры использования, в которых Power Pivot часто оказывается бесценным.

Что такое Power Pivot и почему он полезен?

Power Pivot — это функция Microsoft Excel, которая была представлена ​​как надстройка для Excel 2010 и 2013, а теперь является встроенной функцией для Excel 2016 и 365. Как объясняет Microsoft, Power Pivot для Excel «позволяет импортировать миллионы строк. данных из нескольких источников данных в одну книгу Excel, создавать связи между разнородными данными, создавать вычисляемые столбцы и показатели с помощью формул, создавать сводные таблицы и сводные диаграммы, а затем дополнительно анализировать данные, чтобы вы могли принимать своевременные бизнес-решения без помощи ИТ-специалистов. ”

Основным языком выражений, который Microsoft использует в Power Pivot, является DAX (выражения анализа данных), хотя в определенных ситуациях могут использоваться и другие языки. Опять же, как объясняет Microsoft, «DAX — это набор функций, операторов и констант, которые можно использовать в формуле или выражении для вычисления и возврата одного или нескольких значений. Проще говоря, DAX помогает вам создавать новую информацию из данных, уже имеющихся в вашей модели». К счастью для тех, кто уже знаком с Excel, формулы DAX будут выглядеть знакомыми, поскольку многие формулы имеют схожий синтаксис (например, SUM , AVERAGE , TRUNC ).

Для ясности основные преимущества использования Power Pivot по сравнению с базовым Excel можно резюмировать следующим образом:

• Он позволяет импортировать и управлять сотнями миллионов строк данных, в то время как Excel имеет жесткое ограничение чуть более миллиона строк.
• Это позволяет вам импортировать данные из нескольких источников в одну исходную книгу без необходимости создавать несколько исходных листов, которые страдают от проблем с контролем версий и переносимостью.
• Он позволяет вам манипулировать импортированными данными, анализировать их и делать выводы, не замедляя работу вашего компьютера до черепашьей скорости.
• Он позволяет визуализировать данные с помощью сводных диаграмм и Power BI.

В следующих разделах я рассмотрю все вышеперечисленное и покажу, чем Power Pivot для Excel может быть полезен.

Как использовать Power Pivot

1) Импорт больших наборов данных

Как упоминалось ранее, одно из основных ограничений Excel связано с работой с чрезвычайно большими наборами данных. К счастью для нас, Excel теперь может загружать намного больше одного миллиона строк непосредственно в Power Pivot.

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

Используя вкладку « Данные » на ленте, я создал новый запрос из CSV-файла (см. «Создание нового запроса » ниже). Раньше эта функция называлась PowerQuery, но в Excel 2016 и 365 она была более тесно интегрирована во вкладку «Данные» в Excel.

Создание нового запроса

От пустой книги в Excel до загрузки всех двух миллионов строк в Power Pivot потребовалось около одной минуты! Обратите внимание, что я смог выполнить небольшое форматирование данных, превратив первую строку в имена столбцов. За последние несколько лет функциональность Power Query значительно улучшилась: от надстройки Excel до тесно интегрированной части вкладки «Данные» на панели инструментов. Power Query может сводить, сводить, очищать и формировать ваши данные с помощью набора параметров и собственного языка M.

2) Импорт данных из нескольких источников

Одним из других ключевых преимуществ Power Pivot для Excel является возможность легкого импорта данных из нескольких источников. Раньше многие из нас создавали несколько листов для различных источников данных. Часто этот процесс включал написание кода VBA и копирование/вставку из этих разрозненных источников. Однако, к счастью для нас, Power Pivot позволяет импортировать данные из разных источников данных непосредственно в Excel, не сталкиваясь с упомянутыми выше проблемами.

Используя функцию Query в приложении 1, мы можем получить информацию из любого из следующих источников:

• Microsoft Azure
• SQL-сервер
• Терадата
• Фейсбук
• отдел продаж
• JSON-файлы
• книги Excel
• …и многое другое

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

3) Работа с большими наборами данных

Еще одним ключевым преимуществом Power Pivot для Excel является возможность манипулирования и работы с большими наборами данных для получения соответствующих выводов и анализа. Ниже я приведу несколько распространенных примеров, чтобы дать вам представление о возможностях этого инструмента.

Меры

Любители Excel, без сомнения, согласятся с тем, что сводные таблицы — одна из самых полезных и в то же время одна из самых неприятных задач, которые мы выполняем. Разочаровывает, особенно когда дело доходит до работы с большими наборами данных. К счастью, Power Pivot для Excel позволяет легко и быстро создавать сводные таблицы при работе с большими наборами данных.

На изображении ниже под названием « Создание мер » обратите внимание на то, как окно Power Pivot разделено на две области. На верхней панели находятся данные, а на нижней — меры. Мера — это вычисление, которое выполняется для всего набора данных. Я ввел меру, введя текст в выделенную ячейку.

 Total Sales:=SUM('Accounting Data'[Amount])

Это создает новую меру, которая суммируется по столбцу Сумма. Точно так же я могу ввести другую меру в ячейку ниже

 Average Sales:=AVERAGE('Accounting Data'[Amount])

Создание мер

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

Создание сводной таблицы

Таблицы размеров

Как финансовые аналитики, использующие Excel, мы умеем использовать сложные формулы, чтобы подчинить технологию своей воле. Мы осваиваем VLOOKUP , SUMIF и даже ужасный INDEX(MATCH()) . Однако, используя Power Pivot, мы можем выбросить многое из этого.

Добавление созданной пользователем таблицы в модель Power Pivot

Чтобы продемонстрировать эту функциональность, я создал небольшую справочную таблицу, в которой каждой категории присвоил тип. При выборе «Добавить в модель данных» эта таблица загружается в Power Pivot (см. Добавление таблицы, созданной пользователем, в модель Power Pivot выше).

Я также создал таблицу дат для использования с нашим набором данных (см. Создание таблицы дат ниже). Power Pivot для Excel позволяет легко и быстро создать таблицу дат для консолидации по месяцам, кварталам и дням недели. Пользователь также может создать более настраиваемую таблицу дат для анализа по неделям, финансовым годам или любым группировкам, характерным для организации.

Создание таблицы дат

Вычисляемые столбцы

Помимо мер, существует еще один тип расчета: вычисляемые столбцы. Пользователям Excel будет удобно писать эти формулы, так как они очень похожи на написание формул в таблицах данных. Я создал новый вычисляемый столбец ниже (см. Создание вычисляемого столбца ниже), который сортирует таблицу учетных данных по сумме. Продажи ниже 50 долларов помечены как «маленькие», а все остальные — как «крупные». Разве формула не кажется интуитивно понятной?

Создание вычисляемого столбца

Отношения

Затем мы можем создать связь между полем «Категория» таблицы «Учетные данные» и полем «Категория» таблицы «Категория», используя представление диаграммы. Кроме того, мы можем определить связь между полем «Дата продажи» таблицы «Учетные данные» и полем «Дата» таблицы «Календарь».

Определение отношений

Теперь, без каких-либо функций SUMIF или VLOOKUP , мы можем создать сводную таблицу, которая вычисляет общий объем продаж по годам, и ввести срез для размера транзакции.

Сводная таблица с использованием отношений

Или мы можем создать диаграмму средних продаж для каждого дня недели, используя новую таблицу календаря.

Сводная диаграмма с использованием отношений

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

Имея возможность выполнять все эти сценарии консолидированной отчетности, мы всегда можем детализировать отдельные позиции. Мы сохраняем наши детализированные данные.

Расширенные функции

До сих пор большая часть анализа, который я показал, представляла собой относительно простые расчеты. Теперь я хочу продемонстрировать некоторые из более продвинутых возможностей этой платформы.

Интеллект времени

Часто, когда мы изучаем финансовые результаты, мы хотим сравнить их с сопоставимыми временными рамками предыдущего года. В Power Pivot есть несколько встроенных функций анализа времени.

 Same Period Last Year Sales:=CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Calendar'[Date])) YOY Sales Growth:=if(not(ISBLANK([Same Period Last Year Sales])),([Total Sales]/[Same Period Last Year Sales])-1,BLANK())

Например, добавление всего двух указанных выше показателей в таблицу данных учета в Power Pivot позволяет мне создать следующую сводную таблицу за несколько щелчков мышью.

Сводная таблица операций со временем

Несоответствие детализации

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

Несоответствие детализации — бюджетная таблица

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

Несоответствие детализации — отношения

В Excel следующая сводная таблица собирается быстро.

Несоответствие детализации — бюджет и фактические результаты

Кроме того, мы можем определить новые меры, которые рассчитывают разницу между фактическими продажами и запланированными продажами, как показано ниже:

 Actual-to-Budget Variance:=DIVIDE([Total Sales],[Total Budgeted Sales])-1

Используя эту меру, мы можем показать дисперсию в сводной таблице.

Несоответствие детализации – результаты отклонения

Процент от общего

Наконец, давайте рассмотрим продажи в определенной категории как процент от всех продаж (например, вклад категории в общий объем продаж) и продажи в определенной категории как процент от всех продаж того же типа (например, вклад категории в сезонные продажи). продажи). Я создал две меры ниже:

 Total Sales as Percent of All Sales:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data')) Total Sales as Percent of Type:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data'[Category]))

Эти меры теперь можно развернуть в новой сводной таблице:

Процент от общего

Обратите внимание, как вычисления выполняются как на уровне категории, так и на уровне сезонного типа. Мне нравится, как быстро и легко выполняются эти расчеты на таком большом наборе данных. Это всего лишь несколько примеров элегантности и чистой вычислительной мощности Power Pivot.

Сжатие

Еще одним преимуществом является то, что размеры файлов уменьшаются. Первоначальный размер файла был 91 МБ, а теперь меньше 4 МБ. Это сжатие 96% исходного файла.

Размеры файлов

Как это произошло? Power Pivot использует механизм xVelocity для сжатия данных. Проще говоря, данные хранятся в столбцах, а не в строках. Этот метод хранения позволяет компьютеру сжимать повторяющиеся значения. В нашем примере набора данных есть только четыре области, которые повторяются во всех двух миллионах строк. Power Pivot для Excel может более эффективно хранить эти данные. В результате для данных, которые имеют много повторяющихся значений, хранение этих данных обходится намного дешевле.

Следует отметить, что в этом примере набора данных я использовал суммы в долларах. Если бы я включил две десятичные точки для отображения центов, эффект сжатия уменьшился бы до впечатляющих 80% от исходного размера файла.

Таблица ССОО

Модели Power Pivot также можно масштабировать на все предприятие. Допустим, вы создаете модель Power Pivot, которая начинает привлекать много пользователей в организации, или данные вырастают до десяти миллионов строк, или и то, и другое. На этом этапе вы можете не захотеть, чтобы тридцать разных пользователей обновляли модель или вносили изменения. Модель может быть легко преобразована в табличную форму SSAS. Все таблицы и отношения сохраняются, но теперь вы можете управлять частотой обновления, назначать роли (например, только для чтения, чтения и обработки) различным пользователям и развертывать только небольшой внешний интерфейс Excel, который связан с табличной моделью. В результате ваши пользователи смогут получить доступ к развернутой табличной модели с небольшой рабочей книгой, но не будут иметь доступа к формулам и показателям.

4) Визуализация и анализ данных

КУБ Формулы

Одним из постоянных запросов моих клиентов является то, что я создаю отчетность, соответствующую строго определенному макету. У меня есть клиенты, которые запрашивают определенную ширину столбцов, цветовые коды RGB и предопределенные имена и размеры шрифтов. Рассмотрим следующую панель инструментов:

Встроенные формулы CUBE

Как заполнить данные о продажах без создания сводных таблиц, если все наши продажи учитываются с помощью Power Pivot для Excel? Используя формулы КУБ! Мы можем писать формулы CUBE в любой ячейке Excel, и она будет выполнять вычисления с использованием уже созданной нами модели Power Pivot.

Например, следующая формула вводится в ячейку под «Общий объем продаж за 2016 г.»:

=КУБЗНАЧ(" ThisWorkbookDataModel "," [Показатели].[Общий объем продаж] "," [Календарь].[Год].[2016] ")

Первая часть формулы, выделенная желтым цветом, относится к названию модели Power Pivot. Как правило, это ThisWorkbookDataModel для более новых версий Power Pivot для Excel. Зеленая часть указывает на то, что мы хотим использовать показатель Total Sales. Часть, выделенная синим цветом, указывает Excel фильтровать только те строки, в которых дата продажи указана как 2016 год.

За кулисами Power Pivot построил куб интерактивной аналитической обработки (OLAP) с данными, вычисляемыми столбцами и мерами. Этот дизайн позволяет пользователю Excel затем получать доступ к данным, извлекая их напрямую с помощью функций CUBE. Используя формулы CUBE, я смог построить полные финансовые отчеты, которые соответствуют предопределенным макетам. Эта возможность является одним из основных преимуществ использования Power Pivot для Excel для финансового анализа.

Power BI

Еще одно преимущество Power Pivot для Excel заключается в том, что вы можете быстро взять любую созданную вами книгу Power Pivot и быстро преобразовать ее в модель Power BI. Импортировав книгу Excel непосредственно в приложение Power BI Desktop или Power BI Online, вы можете анализировать, визуализировать данные и делиться ими с кем-либо в своей организации. По сути, Power BI — это Power Pivot, PowerQuery и SharePoint в одном флаконе. Ниже я создал панель мониторинга, импортировав предыдущую книгу Power Pivot для Excel в настольное приложение Power BI. Обратите внимание на интерактивный интерфейс:

Power BI

Одна замечательная вещь в Power BI — это вопросы и ответы на естественном языке. Для демонстрации я загрузил модель Power BI в свою учетную запись Power BI в Интернете. На веб-сайте я могу задавать вопросы, и Power BI проводит соответствующий анализ по мере того, как я печатаю:

Вопросы и ответы на естественном языке

Этот тип возможности запроса позволяет пользователю задавать вопросы о модели данных и взаимодействовать с данными более простым способом, чем в Excel.

Еще одним преимуществом Power BI является то, что разработчики Microsoft постоянно выпускают для него обновления. Ежемесячно выпускаются новые функции, многие из которых запрашиваются пользователями. Лучше всего то, что это плавный переход от Power Pivot к Excel. Таким образом, время, которое вы потратили на изучение формул DAX, можно использовать в Power BI! Для аналитика, которому необходимо поделиться своим анализом со многими пользователями на разных устройствах, возможно, стоит изучить Power BI.

Лучшие практики

Как только вы начнете, есть несколько лучших практик, которым вы должны следовать.

Во-первых, нужно обдуманно решить, что импортировать в первую очередь. Будете ли вы когда-нибудь использовать домашний адрес продавца? Нужно ли мне знать адрес электронной почты моего клиента в контексте этой книги? Если цель состоит в том, чтобы агрегировать данные в сводную панель, то некоторые из доступных данных не понадобятся для этих расчетов. Потратив время на контроль поступающих данных, вы значительно уменьшите проблемы и использование памяти позже, когда ваш набор данных расширится.

Еще одна рекомендация — помнить, что Power Pivot — это не Excel. В Excel мы привыкли создавать расчеты, постоянно расширяя листы вправо. Power Pivot для Excel наиболее эффективно обрабатывает данные, если мы ограничиваем это стремление к манифестной судьбе. Вместо того чтобы постоянно создавать вычисляемые столбцы справа от ваших данных, научитесь записывать показатели в нижней панели. Эта привычка обеспечит меньшие размеры файлов и более быстрые вычисления.

Наконец, я бы предложил использовать простые английские имена для мер. Этот мне потребовалось много времени, чтобы принять. Первые несколько лет я придумывал такие имена, как SumExpPctTotal , но как только другие люди начали использовать те же книги, мне пришлось многое объяснять. Теперь, когда я начинаю новую книгу, я использую имена мер, такие как Expense Line Item as Percent of Total Expenses . Хотя имя длиннее, его гораздо проще использовать кому-то другому.

Реальные варианты использования

В этой статье я представил лишь несколько способов, с помощью которых Power Pivot для Excel позволяет вам сделать важный шаг за пределы обычного Excel. Я подумал, что было бы полезно выделить несколько реальных случаев использования, в которых я обнаружил, что Power Pivot для Excel чрезвычайно полезен.

Вот некоторые:

• Анализ производительности большого портфеля активов в различных временных диапазонах. Поскольку Power Pivot для Excel позволяет нам определять показатели, сравнивающие период времени с предыдущим, мы можем быстро получать данные по кварталам, по годам, по годам. и производительность из месяца в месяц на непрерывной основе, записав всего несколько показателей.
• Суммируйте учетные данные, используя настраиваемые уровни агрегирования: идентифицируя каждую статью главной книги по имени, категории и финансовому отчету, можно быстро создавать отчеты, включающие соответствующие позиции.
• Сети могут определять продажи в одном и том же магазине: с помощью таблицы, отображающей, когда магазины открываются в сети, можно сравнивать финансовые результаты для одного и того же магазина.
• Выявляйте самые высокие и низкие показатели продаж: можно создавать сводные таблицы, в которых выделяются пять лучших и пять худших SKU по продажам, валовой прибыли, срокам производства и т. д.
• Розничные продавцы могут создавать календарные таблицы, использующие конфигурацию 4-4-5: с помощью настраиваемой таблицы дат продавец может легко назначать каждый день определенному месяцу 4-4-5, после чего ежедневные результаты продаж могут быть объединены в соответствующий месяц.

От неуклюжих электронных таблиц к современным рабочим тетрадям

Как финансовые аналитики, мы должны выполнять сложные расчеты на постоянно расширяющихся наборах данных. Поскольку Excel уже является аналитическим инструментом по умолчанию, освоить Power Pivot несложно, а многие функции отражают собственные функции Excel.

Благодаря функциям CUBE Power Pivot для Excel органично вписывается в ваши существующие книги Excel. Прирост вычислительной эффективности нельзя не заметить. Если предположить, что скорость обработки будет на 20 % выше, то финансовый аналитик, проводящий шесть часов в день в Excel, может сэкономить 300 часов в год!

Кроме того, теперь мы можем анализировать наборы данных, которые намного больше, чем мы могли раньше с нашим традиционным Excel. Благодаря эффективно разработанным моделям мы можем легко получить в 10 раз больше данных, чем раньше было разрешено в традиционном Excel, сохраняя при этом быструю аналитическую гибкость. Благодаря возможности конвертировать модели из Power Pivot в SSAS Tabular объем данных, которые можно обработать, в 100–1000 раз больше, чем мы можем получить в Excel.

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

Если вы хотите попробовать Power Pivot для Excel, ниже приведены некоторые полезные материалы, которые помогут вам начать работу.

Полезные ссылки и руководства

Колли, Р., и Сингх, А. (2016). Power Pivot и Power BI: руководство пользователя Excel по DAX, Power Query, Power BI и Power Pivot в Excel 2010–2016. Соединенные Штаты: Святой макрос! Книги.

Феррари, А., и Руссо, М. (2015). Полное руководство по DAX: бизнес-аналитика с Microsoft Excel, SQL Server Analysis Services и Power BI. США: Microsoft Press, США.