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

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

Новая одежда императора:руководство по 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, мы можем получить информацию из любого из следующих источников:

• Майкрософт Азур
• 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 позволяет мне создать следующую сводную таблицу за несколько щелчков мышью.

Сводная таблица Time Intelligence

Несоответствие гранулярности

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

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

С 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 % исходного размера файла.

Таблица SSAS

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

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

Формулы CUBE

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

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

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

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

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]","[Calendar].[Year].[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, США.