powerpivot что это такое
Надстройка для Excel — Power Pivot, или жизнь после 1 048 576 строк
Как показывает практика, если в файле Excel больше 50 тысяч строк, да еще формулы типа ВПР, он падает и умирает. Потом восстает, как зомби, чтобы выпить нашу кровь и нервы. Ведет он себя тоже как зомби — еле двигается и «ни черта» не соображает.
Что же делать? Ответ простой: начать работать с надстройкой для Excel — Power Pivot. Этот инструмент создан для работы с данными. Он может легко обрабатывать миллионы строк!
Надстройка Power Pivot в Excel
Power Pivot – это надстройка Excel, с помощью которой можно работать с данными в несколько миллионов строк, объединять таблицы в модель данных и создавать аналитические вычисления.
В «обычном» Excel пользователи ограничены количеством строк в таблице – не более размера листа в 1 048 тысяч строк, но в Power Pivot такого ограничения нет. Надстройка может подключаться к данным из внешних источников и работать с большими объемами информации в миллионы строк.
Открыть надстройку Power Pivot можно, нажав на вкладке меню Power Pivot кнопку Управление. Эта вкладка выглядит одинаково во всех версиях Excel.
Хорошая новость: начиная c версий после 2019 года компания Microsoft анонсировала включение Power Pivot во все версии Excel.
Работа с данными в Power Pivot
Как правило, разработка отчетов в Power Pivot происходит в следующем порядке:
Подключения к источникам, связи и вычисления настраиваются в отчете один раз. При изменении исходных данных отчеты можно обновить в меню Данные → Обновить все. Давайте разберем подробнее, как это работает.
Добавление данных в Power Pivot
Чтобы начать работать с Power Pivot, перейдите на вкладку меню Power Pivot → нажмите Управление. Добавить данные в открывшейся надстройке можно несколькими способами:
Способ 1. Подключение к данным с помощью встроенных инструментов импорта.
В Power Pivot есть свои инструменты для импорта внешних данных, которые можно найти на вкладке Главная → кнопки Из базы данных, Из службы данных, Из других источников.
С помощью встроенных инструментов настраивается подключение к 15 видам источников данных.
Увидеть весь список можно в окне «Мастер импорта таблиц», которое открывается в меню Главная → Из других источников.
Настроим подключение к данным на примере файла Excel. Укажите путь к файлу, поставьте галочку «Использовать первую строку в качестве заголовков столбцов», выберите таблицы, жмем «Готово». У вас в окне включится счетчик импорта строк — работает довольно быстро. В результате импорта в окне Power Pivot появятся вкладки с таблицами.
Способ 2. Добавить данные из Power Query.
Загрузка данных с помощью инструментов Power Pivot делается легко, но Power Query лучше подходит для импорта и значительно расширяет возможности аналитики. В нем намного больше доступных источников и возможностей для обработки таблиц произвольного вида.
Чтобы настроить подключение с помощью Power Query, вам нужно создать запрос к источнику данных. Список ранее созданных запросов находится на вкладке «Запросы и подключения». Нажмите на запрос правой кнопкой мышки и выберите Загрузить в… В открывшемся окне доступных вариантов импорта поставьте галочку «Добавить эти данные в модель данных». Задать настройки импорта также можно в самом редакторе Power Query.
К сожалению, в Excel 2010 Power Pivot почти невозможно «подружить» с Power Query и этот новый функционал в старом Excel сильно ограничен.
Интерфейс Power Pivot
Разберем подробнее интерфейс Power Pivot.
В окне Power Pivot есть:
Модель данных и связи
Чтобы перейти к настройке связей между таблицами, выберите в меню Главная → Представление диаграммы (вернутся обратно к просмотру таблиц можно, нажав Представление данных).
Модель данных в Power Pivot – это набор таблиц, объединенных связями.
Графически связь таблиц обозначается линией между ними, как в примере на рисунке. Чтобы создать связь, выделите мышкой поле в одной таблице и «перетащите» его на соответствующее ему поле в области другой таблицы.
Power Pivot поддерживает типы связей «один к одному», «один ко многим».
Если выделить мышкой линию связи в модели данных, то можно увидеть, с помощью каких полей задана связь. Выделенные линии можно удалять. Или, щелкнув по ним дважды, менять связи в открывшемся окне. Также управление связями доступно в окне, которое открывается в меню Конструктор → Управление связями.
Вычисления в Power Pivot
Формулы Power Pivot пишут на языке DAX (Data Analysis Expressions, выражения для анализа данных). DAX-формулы позволяют, по аналогии с формулами Excel, выполнять вычисления и/или настраивать произвольную фильтрацию и представление данных в таблицах.
Язык DAX впервые появился в 2010 году вместе с надстройкой Power Pivot. В этом языке сотни функций, с помощью которых можно создавать аналитические расчеты. Кроме Power Pivot в Excel, DAX-формулы также доступны в Power BI и Analysis Services. То есть эти формулы вам точно пригодятся.
Вычисления с помощью DAX-формул создаются в виде:
Вычисляемые столбцы представляют собой столбцы в таблицах данных, созданные с помощью формул. Чтобы добавить такой столбец, щелкните мышкой дважды по столбцу слева «Добавление столбца», введите название вычисления, а затем знак «=» и формулу в строке формул.
Вычисляемый столбец похож на любой другой столбец в таблице. Также с помощью таких столбцов можно создавать связи в модели. Вычисляемые столбцы в Excel рассчитываются в момент загрузки (обновления) данных, а значения в самом столбце сохраняются, увеличивая размер файла. То есть столбцы – это статические вычисления.
Как и вычисляемые столбцы, меры тоже создаются с помощью DAX-формул. В отличие от столбцов, меры – это динамические вычисления, результат которых зависит от контекста – его можно увидеть в отчете, где мы задаем в разрезе каких полей, фильтров и др. вычислить меру. Меры записываются под таблицей в области вычислений.
Меры в Power Pivot можно превратить в KPI – ключевые показатели эффективности. Для этого выделите меру и нажмите на кнопку Создать KPI в меню Главная. Кроме мер, созданных пользователями, в Excel также есть неявные меры. Они создаются автоматически при формировании сводной таблицы, когда пользователь помещает данные в область значений. Чтобы посмотреть, есть ли у вас в Power Pivot неявные меры, выберите на вкладке Главная → Показать скрытые.
Power Pivot: мощные средства анализа и моделирования данных в Excel
Power Pivot — это Excel надстройка, которая используется для выполнения мощного анализа данных и создания сложных моделей данных. С помощью Power Pivot можно работать с большими объемами данных из различных источников одновременно, быстро анализировать их и распространять результаты.
Примечание. Прежде чем приступить к подробной информации, вы можете вернуться к видеоролику или воспользоваться учебным руководством по & преобразованию и Power Pivot.
Импорт миллионов строк данных из нескольких источников С помощью Power Pivot для Excel можно импортировать миллионы строк данных из нескольких источников в одну книгу Excel, создавать связи между разнородными данными, создавать вычисляемые столбцы и меры с помощью формул, создавать систетные и свиты, а затем дополнительно анализировать данные, чтобы принимать вовремя деловые решения, не требуя помощи ИТ-поддержки.
Быстрое вычисление и анализ Обработка миллионов строк примерно одновременно с тысячами и максимальное количество многоядерных процессоров и гигабайт памяти для быстрой обработки вычислений. Преодолеет существующие ограничения для масштабного анализа данных на компьютере с помощью эффективных алгоритмов сжатия для загрузки даже самых крупных наборов данных в память.
Практически неограниченная поддержка источников данных Служит основой для импорта и объединения исходных данных из любого места для масштабного анализа на компьютере, включая реляционные базы данных, многомерные источники, облачные службы, веб-каналы данных, Excel файлы, текстовые файлы и данные из Интернета.
Панель управленияPower Pivot безопасности и управления позволяет ИТ-администраторам отслеживать общие приложения и управлять ими для обеспечения безопасности, высокой доступности и производительности.
Выражения анализа данных (DAX) DAX — это язык формул, расширяющий возможности работы с данными в Excel для более сложной группировки, вычислений и анализа. Синтаксис формул DAX очень похож на синтаксис Excel формул.
Задачи в Power Pivot или в Excel
Основное различие между Power Pivot и Excel заключается в том, что в окне Power Pivot можно создавать более сложные модели данных. Вот некоторые задачи для сравнения.
Импорт данных из различных источников, таких как большие корпоративные базы данных, публичные ленты новостей, электронные таблицы и текстовые файлы на локальном компьютере.
Импорт всех данных из источника данных.
Фильтрация данных и переименование столбцов и таблиц при импорте.
Прочитайте о том, как получать данные с помощью надстройки Power Pivot
Таблицы могут находиться на любом листе книги. Листы могут содержать несколько таблиц.
Таблицы организованы в виде отдельных страниц с вкладками в окне Power Pivot.
Редактирование данных в таблице
Можно изменять значения в отдельных ячейках таблицы.
Нельзя изменять отдельные ячейки.
Создание связей между таблицами
В диалоговом окне «Связи».
В представлении диаграммы или диалоговом окне «Создание связей».
Расширенные формулы на языке выражений анализа данных (DAX).
Иерархии можно создавать и использовать везде в книге, в том числе в Power View.
Создание ключевых показателей эффективности
Создавайте KPIS для использования в свиттах и отчетах Power View.
Создаваемые перспективы позволяют ограничить число столбцов и таблиц, которые видны пользователям книги.
Создание сводных таблиц и сводных диаграмм
Нажмите кнопку PivotTable в окне Power Pivot.
Расширение модели для Power View
Создается базовая модель данных.
Доступны расширения, например определение полей по умолчанию, изображений и уникальных значений.
Использование Visual Basic для Applications (VBA)
VBA не поддерживается в окне Power Pivot окна.
DaX используется в вычисляемом столбце и вычисляемом поле.
Способ хранения данных
Данные, которые вы работаете в Excel и в окне Power Pivot, хранятся в аналитической базе данных внутри книги Excel, а мощный локальный механизм загружает, запрашивает и обновляет данные в этой базе данных. Так как данные Excel, они сразу же доступны для сводных и сводных книг, Power View и других функций в Excel, которые используются для агрегированных данных и взаимодействия с ними. Все презентации данных и взаимодействие предоставляются Excel; данные и Excel презентации содержатся в одном файле книги. Power Pivot поддерживает файлы размером до 2 ГБ и позволяет работать с данными размером до 4 ГБ в памяти.
Сохранение в SharePoint
Книги, изменяемые с помощью Power Pivot, можно сделать общими, как и любые другие файлы. Однако публикация книги в среде SharePoint с включенными службами Excel имеет некоторые преимущества. На сервере SharePoint службы Excel обрабатывают данные и готовят их к просмотру в окне браузера, где другие пользователи смогут продолжить анализ данных.
В SharePoint вы можете добавить Power Pivot для SharePoint, чтобы получить дополнительную поддержку для совместной работы и управления документами, включая галерею Power Pivot, панель мониторинга управления Power Pivot в Центре администрирования, запланированное обновление данных и возможность использования опубликованной книги в качестве внешнего источника данных в SharePoint.
Получение справочной информации
Все о Power Pivot можно узнать в справке По Power Pivot.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Power Pivot — обзор и обучение
Power Pivot — это технология моделирования данных, которая позволяет создавать модели данных, устанавливать отношения и добавлять вычисления. С помощью Power Pivot вы можете работать с большими наборами данных, создавать развернутые отношения и сложные (или простые) вычисления — и все это в знакомой высокопроизводительной среде Excel.
Power Pivot — одно из трех средств анализа данных, доступных в Excel:
Ресурсы по Power Pivot
Приведенные ниже ссылки и сведения помогут вам освоиться с Power Pivot (в том числе узнать, как включить Power Query в Excel и как начать работу с Power Pivot), а также найти учебники и подключиться к сообществу.
Как получить Power Pivot?
Power Pivot можно использовать в качестве надстройки для Excel, которую можно включить, выполнив несколько простых действий. Базовая технология моделирования Power Pivot используется также в конструкторе Power BI Designer, который является частью службы Power BI, предлагаемой корпорацией Майкрософт.
Начало работы с Power Pivot
Когда надстройка Power Pivot включена, на ленте появляется вкладка Power Pivot, которая показана на следующем изображении.
На вкладке ленты Power Pivot в разделе Модель данных выберите управление.
После выбора элемента Управление появляется окно Power Pivot, в котором вы можете просматривать модель данных и управлять ею, добавлять вычисления, устанавливать отношения и видеть элементы своей модели данных Power Pivot. Модель данных — это коллекция таблиц или других данных, между которыми зачастую установлены отношения. На рисунке ниже показано окно Power Pivot с таблицей.
В окне Power Pivot также можно устанавливать и графически выражать отношения между данными, включенными в модель. Щелкнув значок представления схемы в правом нижнем углу окна Power Pivot, вы увидите имеющиеся отношения в модели данных Power Pivot. На приведенном ниже изображении показано окно Power Pivot в представлении схемы.
Краткое руководство по использованию Power Pivot вы найдете в следующей статье:
В дополнение к этому руководству по следующей ссылке вы найдете исчерпывающую подборку ссылок, ресурсов и дополнительных сведений о Power Pivot:
В последующих разделах перечислены дополнительные ресурсы и руководства, в которых подробнее рассказывается о том, как использовать Power Pivot, в том числе в сочетании с Power Query и Power View, для самостоятельного выполнения комплексных, интуитивно понятных задач бизнес-аналитики в Excel.
Учебники по PowerPivot
Посмотрите на Power Pivot в действии — это поможет вам понять принципы работы и ознакомиться с полезными вариантами использования, демонстрирующими возможности Power Pivot. Начать работу вам помогут следующие руководства:
Создание модели данных в Excel (начинается с базовой модели данных, которая затем настраивается с помощью Power Pivot)
Импорт данных в Excel и создание модели данных (первый из шести конечных рядов учебников)
Дополнительные сведения о Power Pivot
Power Pivot проста в использовании и быстро работает. Эта надстройка позволяет создавать многофункциональные и сложные вычисления, индикаторы и формулы. Приведенные ниже ссылки помогут вам определиться со многими задачами, которые можно выполнять с помощью Power Pivot.
Power Pivot: Оконные функции под соусом DAX
[в связи со спорным переносом 1 части поста на geektimes (при том что 2-я часть осталась на хабре) возвращаю 1-ю часть на место]
Работая в сфере аналитики и мониторя различные инструменты BI рано или поздно наталкиваешься на обзор или упоминание надстройки Power Pivot Excel. В моем случае знакомство с ним произошло на конференции Microsoft Data Day.
Особых впечатлений после презентации инструмент не оставил: Да, бесплатен (в рамках лицензии Office), да — есть некий ETL функционал в части получения данных с разрозненных источников (БД,csv,xls, и т.д.), Join-ов этих источников и скармливания в оперативку записей на порядки выше 1 млн.строк в Excel. Короче, посмотрел и забыл.
А вспомнить пришлось, когда появилась необходимость идентификации определённых явлений в данных
На написание данной статьи сподвигло то что в рунете по этому инструменту не много какой либо детальной информации о конкретных приемах работы, все больше о звездах, поэтому решил, изучая этот инструмент, написать данный обзор.
Собственно, постановка задачи (на обезличенном примере) следующая:
В исходных данных csv файла:
Есть торговые точки, детализированные до строк накладных, при этом допускается для точек с одинаковым наименованием иметь разные адреса только в том случае если они расположены в разных городах, но в исходном массиве данных есть точки, у которых попадаются разные адреса в одном и том же городе при том, что названия точек одинаковые (имя торговой точки уникально, т.е. это единица сети или отдельно стоящая точка). Как частный случай в агрегированном виде:
Поиску и очистке данных штатными средствами office мешают следующие обстоятельства:
• Детализация данных до строк накладной
• Количество записей в несколько миллионов строк
• Отсутствие sql инструментария (К примеру: Access — не в комплекте)
Конечно можно залить любую бесплатную СУБД (хоть десктоп версию, хоть серверную) но для этого во-первых нужны админские права, во-вторых статья была бы уже не про Power Pivot.
Задача: для каждой атомарной записи требуется дополнительное вычисляемое поле, которое посчитает для каждого наименования торговой точки уникальное количество адресов в рамках того же города. Данное поле требуется для быстрого нахождения всех имен торговых точек в городе, где адресов больше 1.
Думаю, удобнее всего решать и рассказывать итерационно, с допущением что у нас знания по DAX в зачаточном уровне.
Поэтому предлагаю пока оторваться от задачи и рассмотреть некоторые базовые аспекты.
Шаг 1. Чем отличается вычисляемый столбец от вычисляемой меры?
Вот пример вычисляемого столбца для выделения НДС из поля отгрузки с НДС используя встроенные формулы DAX:
=ROUND([Отгрузка с НДС]*POWER(1,18;-1)*0,18;2)
Как видно из примера вычисляемый столбец (Назовем его НДС) работает с каждой атомарной записью по горизонтали.
Теперь добавим вычисляемое поле для цены за штуку без НДС:
=ROUND([Отгрузка с НДС]*POWER(1,18;-1)/[Отгрузка шт];2)
Теперь для сравнения добавим в меру расчет средней цены за штуку:
Средняя цена за штуку без НДС: =ROUND(AVERAGE([Поле_Цена за штуку без НДС]);2)
Как видно из формулы, мера работает со столбцом исходных данных по вертикали, поэтому она всегда должна содержать в себе какую то работающую с множеством функцию (Сумму, среднюю, дисперсию и т.д.)
При возврате в сводную таблицу Excel это выглядит так:
Обратите внимание, если вычисляемое поле НДС на каждом уровне данных (зеленая обводка на уровне торговой точки, города или итого по таблице) показывает сумму, что в принципе – корректно, то сумма цен вычисляемого поля «Цена за штуку без НДС» (красная обводка) вызывает вопросы.
А вот вычисляемая мера «Средняя цена за штуку без НДС» вполне имеет право на жизнь в рамках данного аналитического куба.
Отсюда делаем вывод, что вычисляемое поле «Цена за штуку без НДС» является вспомогательным инструментом для расчета меры «Средняя цена за штуку без НДС» и дабы не смущать пользователя этим полем мы скроем его из списка клиентских средств, оставив меру средней цены.
Еще одно отличие меры от столбца – она позволяет добавить визуализацию:
К примеру, построим KPI степени разброса цен с целевой границей 35% путем деления корня из дисперсии на среднюю арифметическую.
К_вар:=STDEV.P([Поле_Цена за штуку без НДС])/AVERAGE([Поле_Цена за штуку без НДС])
В итоге видим такую таблицу в Excel (кстати расчетное вспомогательное поле цен уже не в списке доступных полей справа):
Двойной клик на 80%-м коэффициенте показывает, что цены действительно колбасит вокруг средней:
Cильнее чем при коэффициенте 15%:
Итак, на данном шаге мы рассмотрели основные отличия мер от полей в рамках PowerPivot.
Шаг 2. Усложняем: Посчитаем долю каждой записи в общих продажах.
Вот первый пример сравнения подходов оконных функций MS SQL Server и DAX:
Понятно, что в рамках сводных таблиц это делается буквально в 2 клика мышкой не касаясь клавиатуры, но для понимания попробуем это непосредственно в PowerPivot с применением формул.
На sql я бы это написал так (за огрехи не пинать, ибо Word синтаксис SQL Server не проверяет):
Здесь, как можно заметить окно открывается через все записи датасэта, попробуем аналогичную вещь в PowerPivot:
=[Отгрузка шт]/CALCULATE(SUM([Отгрузка шт]);ALL(‘Таблица1’))
Основное внимание обратим к знаменателю: Я уже упоминал выше что основное отличие вычисляемого поля от меры заключается в том что в поле формулы считают по горизонтали ( в рамках одной записи) а меры – по вертикали ( в рамках одного атрибута). Здесь мы смогли скрестить свойства поля и свойство меры через метод CALCULATE. И если ширину окна в SQL мы отрегулировали через Over() то здесь мы сделали это через All().
Попробуем теперь, обладая данным навыком, сделать с нашими данными что –нибудь полезное, например, вспомнив что показатель разброса цен вокруг средних варьировался в широком диапазоне, попробуем выделить статистические выбросы цен через правило 3-х сигм.
Оконные функции на sql будут смотреться так:
А вот то же самое в DAX:
=if(ABS([Поле_Цена за штуку без НДС]-CALCULATE(AVERAGE([Поле_Цена за штуку без НДС]);ALL(‘Таблица1’)))>(3*CALCULATE(STDEV.P([Поле_Цена за штуку без НДС]);all(‘Таблица1’)));1;0)
Как видите, цена несколько высоковата при средней арифметической 40,03 руб.
Шаг 3. Сужаем окна.
Попробуем теперь посчитать в вычисляемом поле каждой записи общее количество записей в рамках того города, к которому принадлежит и данная запись.
На MS sql Server оконные функции будут выглядеть так:
В DAX:
=CALCULATE(COUNTROWS(‘Таблица1’);ALLEXCEPT(‘Таблица1’;’Таблица1′[Город]))
Обратите внимание на разницу в отображении данных в таблице, я специально бросил адреса в область мер что бы посчитать их количество и сравнить с новым полем которое вывел в заголовок строк после имени торговой точки.
Отчетлива видна разница: если обычный расчет количества адресов идет для каждой точки в городе и потом только выводит промежуточный итог для агрегата «Город» то использование оконных функций позволяет присвоить каждой атомарной записи значение любого агрегата, либо использовать его в каких-то промежуточных расчетах вычисляемого поля ( как было показано выше).
Возвращаемся к исходной задаче
Итак, напомню, исходная постановка задачи: для каждой атомарной записи требуется дополнительное вычисляемое поле, которое посчитает для каждого наименования торговой точки уникальное количество адресов в рамках того же города. Не забываем, что датасэт у нас детализирован до строк накладной, поэтому перед подсчетом адресов внутри окна их необходимо сгруппировать.
Запрос на SQL Server:
Теперь нам ничего не мешает это сделать и в DAX:
В итоге у нас появилась возможность отобрать подозрительные записи, где на одну и ту же точку в одном городе приходится более 1 адреса. 
Конечно в процессе изучения (пробежавшись взглядом на другие формулы) становится понятно что DAX в PowerPivot гораздо мощнее чем показано в данном топике, но объять необъятное за раз – точно не получится.
Надеюсь было интересно.
Продолжение статьи здесь