≡× МЕНЮ

• Windows 7
• Windows 8
• Windows XP
• Windows общее
• Железо

С помощью PowerPivot в Microsoft Excel. Главные преимущества надстройки Excel для анализа данных PowerPivot Создание вычисляемого столбца

Модуль PowerPivot представляет собой надстройку Excel 2010, которая совершенно бесплатно распространяется командой разработчиков SQL Server Analysis Services из компании Microsoft. При создании Excel 2010 ставилась цель создания превосходного инструмента для бизнеса. И надстройка PowerPivot позволила воплотить мечту в реальность - с ее помощью можно выполнять исчерпывающий анализ огромных массивов данных в Excel.

Как и все в этом мире, надстройка PowerPivot обладает как преимуществами, так и определенными недостатками. Сначала мы рассмотрим главные преимущества надстройки, благодаря которым она завоевала свою популярность. Надстройке PowerPivot присущи пять главных преимуществ. Каждое из них является достаточным аргументом в пользу перехода к версии Excel 2010.

• Возможность обработки массивов данных, исчисляемых десятками и сотнями миллионов строк. Если приходится создавать отчеты сводных таблиц для массивов данных, объем которых превышает 1 048 576 строк, либо сортировать, фильтровать такие массивы и выполнять их прокрутку, обратитесь к надстройке PowerPivot.
• Создание сводных таблиц на основе нескольких исходных таблиц без использования функции ВПР. Теперь вам не придется обращаться к ресурсозатратной функции ВПР (VLOOKUP) для создания сводной таблицы на основе двух исходных таблиц. С помощью надстройки PowerPivot можно на основе нескольких таблиц Excel создать отчет сводной таблицы, не обращаясь к функции ВПР.
• Выборка данных из различных источников. Благодаря PowerPivot можно легко и просто импортировать текст и данные в формате Access, RSS, SQLServer либо Excel, создавая на их основе отчет сводной таблицы.
• Доступ к наборам данных. В сводных таблицах Excel 2010 появилось новый замечательный инструмент - наборы, с помощью которых обеспечивается создание асимметричных отчетов. Единственное ограничение наборов - возможность их использования исключительно при работе со сводными таблицами OLAP. Если же открыть обычную таблицу с помощью надстройки PowerPivot, она автоматически преобразуется в сводную таблицу OLAP. Именно благодаря возможности работы с наборами я создаю сводные таблицы с помощью надстройки PowerPivot.
• Никогда еще выполнение вычислений не было столь простым. При разработке надстройки PowerPivot специалисты из компании Microsoft внедрили новый язык вычисления формул, который получил название Data Analysis Expressions (DAX). Этот язык включает 117 функций, с помощью которых можно выполнять два вида вычислений. С помощью 81 обычной функции Excel можно добавлять вычисляемые столбцы в таблицу, которая отображается в окне надстройки PowerPoint. Также можно воспользоваться 54 функциями для создания новых мер в сводной таблице. С помощью этих 54 функций сводные таблицы обретают невиданную доселе мощь. Например, функция COUNTROWS (DISTINCT) позволяет подсчитать количество различающихся между собой строк. Функция CALCULATE (выражение, фильтрХ, фильтр2, …. фильтры) напоминает функцию СУММБСЛИ (SUMIFS), но является более универсальной. А 34 продвинутые функции по работе со временем (например, TOTALYTD и PARALLELPERIOD) позволят выполнять самые разные хронометрические вычисления.

Второстепенные преимущества PowerPivot

Ниже представлен ряд второстепенных преимуществ PowerPivot, которые не столь впечатляющи, но все же весьма полезны в работе.

• Компактное представление. Рабочие книги Excel, включающие данные PowerPivot, имеют меньшие размеры, чем рабочие книги, использующие традиционные сводные таблицы PivotCache. Хотя данные по-прежнему хранятся в файле рабочей книги.xlsx, но в PowerPivot используются лучшие алгоритмы сжатия данных.
• Объединение двух сводных таблиц с помощью единого набора срезов. С помощью единственного набора срезов можно контролировать две отдельные таблицы PowerPivot.
• Авторазметка среза. Срезы, созданные в стандартной программе Excel, изначально имеют один столбец и один и тот же размер. Если же срез создается в PowerPivot, предпринимается попытка автоматической настройки его размеров. Конечно, далеко не всегда достигается идеальный результат, но все же срезы, создаваемые в PowerPivot, выглядят гораздо лучше срезов, создаваемых в традиционной среде Excel.
• Создание сводных диаграмм без сводных таблиц. На самом деле это немного не так. С помощью надстройки PowerPivot диаграмма автоматически создается на рабочем листе, а затем связывается со сводной таблицей, находящейся на другом листе.

Недостатки PowerPivot

Пользователи PowerPivot отмечают наличие следующих недостатков.

• Отсутствие группировки. В PowerPivot невозможна группировка сводных таблиц. Свойство группировки применяется для распределения дат по месяцам, кварталам и годам. Можно воспользоваться средствами языка DAX для определения столбцов года, квартала и месяца, но это намного сложнее, чем использование свойства группировки.
• Невозможно воспользоваться отменой. PowerPivot является надстройкой. Как известно, в результате выполнения макроса либо другого внешнего кода стек отмены очищается. Поэтому после запуска надстройки PowerPivot вы не сможете вернуться к выполняемым ранее действиям.
• Невозможность использования VBA. При работе со сводными таблицами можно обращаться к средствам VBA. В случае использования PowerPivot применение VBA невозможно.
• Отсутствие возможности развертывания. Для просмотра строк, образующих ячейку обычной сводной таблицы, достаточно дважды щелкнуть мышью на этой ячейке, В первой версии PowerPivot эта операция недоступна.
• Только для Excel 2010. Надстройка PowerPivot может работать только совместно с Excel 2010. Ее невозможно использовать совместно с Excel 2007 либо при работе с файлами, сохраненными в режиме совместимости.

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

Поэтому, представляем вам надстройку для MS Excel , которая позволит создавать супертаблицы - Power Pivot .

Так что такое PowerPivot?

Power Pivot - это дополнительная надстройка для Excel , которая расширяет функционал Сводных таблиц (Pivot Tables ). Используется только для MS Office 2010. В новой версии Office 2013 PowerPivot уже является неотъемлемым элементом Excel .

Как установить PowerPivot?

Перед установкой PowerPivot , вам необходимо (для Windows XP ):

деинсталлировать MS Office 2010

установить SP3 для Windows XP

установить .NET Framework 4.0 та Visual Studio 2010 Tools for Office Runtime

установить полностью MS Office 2010 (со всеми программами и общими средствами)

установить PowerPivot

После установки PowerPivot в Excel появляется дополнительная вкладка:

Нажав PowerPivot Window откроется отдельное окно, в которое можно загружать данные из разных источников.

После закачки данных из различных файлов Excel в вкладку PowerPivot Window , вы сможете работать с ними, и на обычном листе Excel , т.е. добавлять колонки, производить вычисления, используя формулы. Также, появляется уникальная как для Excel "я возможность, делать связи между загруженными таблицами как в Access . Используя таблицы со связями, мы можем делать Сводную таблицу , которая будет брать данные из разных таблиц, без предварительного их ручного объединения, без многократного использования VLOOKUP (ВПР ) , просто и быстро.

Нажав на изображение Сводной таблицы , мы получаем возможность начать ее формировать согласно наших потребностей. Таким образом, мы получим вот такой макет таблицы с доступными всеми данными, которые мы предварительно подготовили и погрузили:

Также, как мы видим, в Сводной таблице появляются новые дополнительные поля, которые называются Визуальными фильтрами (Slicers ) .

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

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

Термины "Power Query", "Power Pivot", "Power BI" и прочие "пауэры" все чаще всплывают в статьях и материалах о Microsoft Excel. По моему опыту, далеко не все ясно представляют себе что скрывается за этими понятиями, как они между собой взаимосвязаны и как могут помочь простому пользователю Excel.

Давайте проясним ситуацию.

Power Query

Еще в 2013 году специально созданная группа разработчиков внутри Microsoft выпустила для Excel бесплатную надстройку Power Query (другие названия - Data Explorer, Get&Transform), которая умеет массу полезных для повседневной работы вещей:

• Загружать данные в Excel из почти 40 различных источников, среди которых базы данных (SQL, Oracle, Access, Teradata...), корпоративные ERP-системы (SAP, Microsoft Dynamics, 1C...), интернет-сервисы (Facebook, Google Analytics, почти любые сайты).
• Собирать данные из файлов всех основных типов данных (XLSX, TXT, CSV, JSON, HTML, XML...), как поодиночке, так и сразу оптом - из всех файлов указанной папки. Из книг Excel можно автоматически загружать данные сразу со всех листов.
• Зачищать полученные данные от "мусора": лишних столбцов или строк, повторов, служебной информации в "шапке", лишних пробелов или непечатаемых символов и т.п.
• Приводить данные в порядок : исправлять регистр, числа-как-текст, заполнять пробелы, добавлять правильную "шапку" таблицы, разбирать "слипшийся" текст на столбцы и склеивать обратно, делить дату на составляющие и т.д.
• Всячески трансформировать таблицы, приводя их в желаемый вид (фильтровать, сортировать, менять порядок столбцов, транспонировать, добавлять итоги, разворачивать кросс-таблицы в плоские и сворачивать обратно).
• Подставлять данные из одной таблицы в другую по совпадению одного или нескольких параметров, т.е. прекрасно заменяет функцию ВПР (VLOOKUP) и ее аналоги.

Power Query встречается в двух вариантах: как отдельная надстройка для Excel 2010-2013, которую можно скачать с официального сайта Microsoft и как часть Excel 2016. В первом случае после установки в Excel появляется отдельная вкладка:

В Excel 2016 весь функционал Power Query уже встроен по умолчанию и находится на вкладке Данные (Data) в виде группы Получить и преобразовать (Get & Transform) :

Возможности этих вариантов совершенно идентичны.

Принципиальной особоенностью Power Query является то, что все действия по импорту и трансформации данных запоминаются в виде запроса - последовательности шагов на внутреннем языке программирования Power Query, который лаконично называется "М". Шаги можно всегда отредактировать и воспроизвести повторно любое количество раз (обновить запрос).

Основное окно Power Query обычно выглядит примерно так:

По моему мнению, это самая полезная для широкого круга пользователей надстройка из всех перечисленных в этой статье. Очень много задач, для которых раньше приходилось либо жутко извращаться с формулами, либо писать макросы - теперь легко и красиво делаются в Power Query. Да еще и с последующим автоматическим обновлением результатов. А учитывая бесплатность, по соотношению "цена-качество" Power Query просто вне конкуренции и абсолютный must have для любого средне-продвинутого пользователя Excel в наши дни.

Power Pivot

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

Общие принципы работы в Power Pivot следующие:

1. Сначала мы загружаем данные в Power Pivot - поддерживается 15 различных источников: распространенные БД (SQL, Oracle, Access...), файлы Excel, текстовые файлы, веб-каналы данных. Кроме того, можно использовать Power Query как источник данных, что делает анализ почти всеядным.
2. Затем между загруженными таблицами настраиваются связи или, как еще говорят, создается Модель Данных . Это позволит в будущем строить отчеты по любым полям из имеющихся таблиц так, будто это одна таблица. И никаких ВПР опять же.
3. При необходимости, в Модель Данных добавляют дополнительные вычисления с помощью вычисляемых столбцов (аналог столбца с формулами в "умной таблице") и мер (аналог вычисляемого поля в сводной). Всё это пишется на специальном внутреннем языке Power Pivot, который называется DAX (Data Analysis eXpressions).
4. На листе Excel по Модели Данных строятся интересующие нас отчеты в виде сводных таблиц и диаграмм.

Главное окно Power Pivot выглядит примерно так:

А так выглядит Модель Данных, т.е. все загруженные таблицы с созданными связями:

У Power Pivot есть ряд особенностей, делающих её уникальным инструментом для некоторых задач:

• В Power Pivot не предела по количеству строк (как в Excel). Можно грузить таблицы любого размера и спокойно работать с ними.
• Power Pivot очень хорошо умеет сжимать данные при загрузке их в Модель. 50 Мб исходный текстовый файл может легко превратиться в 3-5 Мб после загрузки.
• Поскольку "под капотом" у Power Pivot, по сути, полноценный движок базы данных, то с большими объемами информации он справляется очень быстро . Нужно проанализировать 10-15 млн. записей и построить сводную? И все это на стареньком ноутбуке? Без проблем!

К сожалению, пока что Power Pivot входит не во все версии Excel. Если у вас Excel 2010, то скачать её можно бесплатно с сайта Microsoft . А вот если у вас Excel 2013-2016, то всё зависит от вашей лицензии, т.к. в некоторых вариантах она включена (Office Pro Plus, например), а в некоторых нет (Office 365 Home, Office 365 Personal и т.д.) Подробнее об этом можно .

Power Maps

Эта надстройка впервые появилась в 2013 году и первоначально называлась GeoFlow. Она предназначена для визуализации гео-данных, т.е. числовой информации на географических картах. Исходные данные для отображения берутся все из той же Модели Данных Power Pivot (см. предыдущий пункт).

Демо-версию Power Map (почти не отличающуюся от полной по возможностям, кстати) можно совершенно бесплатно загрузить опять же с сайта Microsoft . Полная же версия включена в некоторые пакеты Microsoft Office 2013-2016 вместе с Power Pivot - в виде кнопки 3D-карта на вкладке Вставка (Insert - 3D-map) :

Ключевые особенности Power Map:

Эта надстройка появилась впервые в составе Excel 2013 и предназначена для "оживления" ваших данных - построения интерактивных графиков, диаграмм, карт и таблиц. Иногда для этого используют термины дашборд (dashboard) или панель показателей (scorecard) . Суть в том, что вы можете вставить в ваш файл Excel специальный лист без ячеек - слайд Power View, куда добавить текст, картинки и массу различного типа визуализаций по вашим данным из Модели Данных Power Pivot.

Выглядеть это будет примерно так:

Нюансы тут такие:

• Исходные данные берутся всё оттуда же - из Модели Данных Power Pivot.
• Для работы с Power View необходимо установить на вашем компьютере Silverlight - майкрософтовский аналог Flash (бесплатный).

На сайте Microsoft, кстати, есть весьма приличный обучающий курс по Power View на русском языке .

Power BI

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

1. Power BI Desktop - программа для анализа и визуализации данных, включающая в себя, помимо прочего, весь функционал надстроек Power Query и Power Pivot + улучшенные механизмы визуализации из Power View и Power Map. Скачать и установить её можно совершенно бесплатно с сайта Microsoft .

В Power BI Desktop можно:

• Загружать данные из более чем 70 различных источников (как в Power Query + дополнительные коннекторы).
• Связывать таблицы в модель (как в Power Pivot)
• Добавлять к данным дополнительные вычисления с помощью мер и вычисляемых столбцов на DAX (как в Power Pivot)
• Создавать на основе данных красивейшие интерактивные отчеты с разного типа визуализациями (очень похоже на Power View, но еще лучше и мощнее).
• Публиковать созданные отчеты на сайте Power BI Service (см. следующий пункт) и делиться ими с коллегами. Причем есть возможность давать разные права (чтение, редактирование) разным людям.

2. Онлайн-сервис Power BI - упрощенно говоря, это сайт, где у вас и у каждого пользователя в вашей компании будет своя "песочница" (workspace) куда можно загружать созданные в Power BI Desktop отчеты. Помимо просмотра, позволяет их даже редактировать, воспроизводя онлайн почти весь функционал Power BI Desktop. Также сюда можно заимствовать отдельные визуализации из чужих отчетов, собирая из них свои авторские дашборды.

Выглядит это примерно так:

3. Power BI Mobile - приложение для iOS / Android / Windows для подключения к Power BI Service и удобного просмотра (не редактирования) созданных отчетов и дашбордов прямо на экране телефона или планшета. Скачать его (совершенно бесплатно) .

На iPhone, например, созданный выше отчет выглядит так:

Причем всё это с сохранением интерактивностии и анимации + заточенность под тач и рисование по экрану пером. Очень удобно. Таким образом, бизнес-аналитика становится доступной всем ключевым лицам компании в любой момент и в любом месте - нужен только доступ в интернет.

Тарифные планы Power BI . Power BI Desktop и Mobile бесплатны изначально, большинство функций Power BI Service - тоже. Так что для персонального использования или применения в пределах небольшой компании за всё вышеперечисленное не нужно платить ни копейки и можно смело оставаться на плане Free . Если вы хотите делиться отчетами с коллегами и администрировать их права доступа, то придется перейти на Pro (10$ в месяц за пользователя). Есть еще Premium - для больших компаний (>500 пользователей), которым требуются для данных отдельные хранилища и серверные мощности.

Ссылки по теме

Здорово.
Единственный существенный минус в Power BI для организации (там где я работаю), - это хранение данных при загрузке BI на серверах Microsoft.
Собственно, из общения с коллегами, мне стало понятно, что это и стало "камнем преткновения" при выборе BI системы.
Коллеги выбрали Tableau.
(Возможно, сейчас что-то поменялось за 1.5 - 2 года)
Что касается PP, PQ, то с благодарностью и нетерпением ожидаю новых статей. Являюсь единственным (пока), в своей компании, кто активно пользуется и на практике показывает результаты (недавно собрал 4.5 млн. строк за 10 минут, вызвав удивления коллег).
Это приятно.
Очень скоро пойду к Вам на курсы, сейчас выбираю курс по моделированию и "устаканиваю" Dax"а в голове.

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

Наиболее распространенные агрегаты, например, с помощью функции СРЗНАЧ , счёт , DistinctCount , Max , min или Sum , могут автоматически создаваться в измерении с помощью функции автосуммирования. Другие типы агрегатов, например AVERAGEX , COUNTX , CountRows или SUMX возвращают таблицу и требуют формулы, созданной с помощью выражений анализа данных (DAX) .

Основные сведения об агрегатах в Power Pivot

Выбор групп для агрегата

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

Метод группировки данных определяется поставленным бизнес-вопросом. Например, агрегаты могут ответить на следующие вопросы.

Счетчики Сколько транзакций было выполнено за месяц?

Средние значения Какие показатели средних продаж в этом месяце у каждого менеджера по продажам?

Минимальные и максимальные значения Какие районы сбыта были в горячей пятерке по количеству проданного товара?

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

Если поступившие данные не содержат значений, которые можно использовать для группирования (таких как категория товара или географический регион, где расположен магазин), можно создать группы данных путем добавления категорий. При создании групп в Excel необходимо вручную ввести или выделить нужные группы из числа столбцов в рабочем листе. Однако в реляционных системах многие иерархии (например, категории продуктов) хранятся не в той таблице, где хранятся факты или значения. Обычно таблица категорий связана с данными фактов с использованием какого-либо ключа. Например, предположим, что в данных содержатся идентификаторы продуктов, но не их имена или категории. Чтобы добавить категорию в неструктурированный рабочий лист Excel, потребовалось бы скопировать столбец, содержащий названия категорий. Используя Power Pivot, можно импортировать таблицу с категориями продуктов в вашу модель данных, создать связь между таблицей с числовыми данными и списком категорий продуктов, затем использовать категории для группирования данных. Дополнительные сведения можно найти в разделе Создание связи между таблицами .

Выбор функции для агрегата

После определения и добавления групп необходимо решить, какие математические функции следует использовать для агрегирования. Часто слово "агрегат" используется в качестве синонима математических или статистических операций, применяемых в агрегатах, таких как суммирование, определение средних значений, определение минимума или подсчет. Тем не менее Power Pivot позволяет создавать пользовательские формулы для агрегирования в дополнение к стандартным агрегатам и в Power Pivot и в Excel.

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

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

Соотношения, использующие средние значения за период времени Каков был процентный рост или снижение продаж по сравнению с тем же периодом прошлого года?

Сгруппированные минимальные и максимальные значения Какие районы сбыта были ведущими для каждой категории продукта или для каждого стимулирования сбыта?

Добавление агрегатов к формулам и сводным таблицам

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

Добавление группирований в сводную таблицу

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

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

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

Работа с группированиями в формуле

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

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

Дополнительные сведения о создании формул с подстановками см. в статье Подстановка в формулах PowerPivot .

Использование фильтров в агрегатах

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

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

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

Дополнительные сведения см. в статье Фильтрация данных в формулах .

Сравнение агрегатных функций Excel с агрегатными функциями DAX

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

Стандартные агрегатные функции

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

Агрегатные функции DAX

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

В следующей таблице перечислены агрегатные функции, доступные в DAX.

	Использование
	Функция определяет среднее арифметическое для набора выражений, вычисленных в таблице.
	Функция подсчитывает набор выражений, вычисленных в таблице.
	Функция подсчитывает количество пустых значений в столбце.
	Функция подсчитывает общее количество строк в таблице.
	Функция подсчитывает количество строк, возвращенных вложенной табличной функцией, такой как функция фильтра.
	Функция возвращает сумму набора выражений, вычисленных в таблице.

Различия между агрегатными функциями DAX и Excel

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

Способ вычисления столбцов в статистическом выражении также отличается от способа обработки статистических выражений в Excel. Проиллюстрировать это поможет пример.

Предположим, требуется получить сумму значений в столбце Amount таблицы Sales, для чего создается следующая формула:

SUM("Sales")

В самом простом случае функция возвращает значения из одного неотфильтрованного столбца, и результат будет таким же, как в приложении Excel, в котором всегда просто суммируются значения в столбце Amount. Тем не менее в Power Pivot формула интерпретируется как "Получить значение в столбце Amount для каждой строки таблицы Sales и затем сложить эти отдельные значения". Power Pivot вычисляет каждую строку, для которой выполняется агрегирование, и вычисляет единичное скалярное значение для каждой строки, а затем агрегирует эти значения. Поэтому результат формулы может быть разным, если к таблице применялись фильтры или если значения вычислялись на основе других агрегатов, где могли использоваться фильтры. Дополнительные сведения см. в статье Контекст в формулах DAX .

Функции логики операций со временем DAX

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

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

Другие функции в разделе "функция логикиопераций со временем" - функции, которые можно использовать для получения дат или настраиваемых диапазонов дат для использования в агрегате. Например, с помощью функции DATESINPERIOD можно получить диапазон дат и использовать этот набор дат в качестве аргумента другой функции для вычисления пользовательского агрегата только по этим датам.

Сведения

1049\ReadMe_PowerPivot.htm

1049\x64\PowerPivot_for_Excel_amd64.msi

1049\x86\PowerPivot_for_Excel_x86.msi

Дата публикации:

• Microsoft PowerPivot для Microsoft Excel 2010 представляет собой революционную технологию, которая обеспечивает быструю обработку крупных наборов данных (до нескольких миллионов строк), упрощенную интеграцию данных и возможность легко обмениваться результатами анализа через Microsoft SharePoint 2010.
  • Расположенный в памяти обработчик PowerPivot, располагающий эффективными алгоритмами сжатия, позволяет обрабатывать крупные наборы данных (вплоть до миллионов строк) примерно с той же производительностью, что и небольшие (несколько сотен строк)
  • При помощи PowerPivot можно получить прямой доступ к доверенным платным и общедоступным данным из раздела DataMarket в Windows Azure Marketplace
  • Интегрировать данные из многих источников, включая корпоративные базы данных, электронные таблицы, отчеты, текстовые файлы и веб-каналы данных.
  • Язык DAX (выражения анализа данных) в PowerPivot расширяет возможности стандартных выражений Excel и позволяет производить многофункциональную обработку данных. Пройти по связям между таблицами, как в базе данных, и определить сложные вычисления с помощью привычных и интуитивно понятных выражений.
  • Просматривать, анализировать и создавать отчеты в интерактивном режиме без экспертных навыков и специального обучения, применяя стандартные функции Excel 2010, такие как сводные таблицы, срезы и другие знакомые аналитические функции.

  Примечание. Windows Azure Marketplace DataMarket - это удобный поставщик доверенных платных и общедоступных данных. В разделе DataMarket можно найти данные, изображения и онлайн-услуги от ведущих коммерческих поставщиков данных и из официальных открытых источников данных. Информационные работники могут использовать DataMarket для поиска, покупки и управления платными подписками на данные. Кроме того, они могут просматривать наборы данных в DataMarket, используя богатые возможности представления данных, и находить приложения сторонних разработчиков, построенные на базе этих наборов данных.

  Наиболее свежую версию документации по PowerPivot можно найти в Дополнительные статьи, блоги и другое содержимое, созданное пользователями, можно найти на

  Внимание!

  Следует учитывать, что PowerPivot для Microsoft Excel работает только с последней версией Microsoft Office 2010. Чтобы загрузить или приобрести пакет Microsoft Office 2010, перейдите

  Требования к системе

  • Поддерживаемая операционная система

    Windows 7; Windows Server 2008 R2; Windows Server 2008 Service Pack 2; Windows Vista Service Pack 2

    • Windows 8**
      Windows 7
      Windows Server 2008 R2 (64-разрядная версия)
      Windows Vista с пакетом обновления 2 (SP2) (32-разрядная или 64-разрядная версия)*
      Windows Server 2008 с пакетом обновления 2 (SP2) (32-разрядная или 64-разрядная версия)*
      Windows XP с пакетом обновления 3 (SP3) (32-разрядная версия)
      Windows Server 2003 R2 (32-разрядная или 64-разрядная версия) с установленным MSXML 6.0+
      Поддерживаются сервер терминалов и Windows on Windows (WOW), что позволяет устанавливать 32-разрядные версии ПО Office 2010 на 64-разрядные версии операционных систем
      ** Также необходимо наличие
      * Также необходимо
      + 64-разрядная версия Windows Server 2003 R2 поддерживает только 32-разрядную версию Microsoft® Office 2010/PowerPivot для Microsoft® Excel 2010.
      • 32-разрядный или 64-разрядный процессор с тактовой частотой 500 МГц или выше
      • Минимум 1 ГБ ОЗУ (рекомендуется 2 ГБ и выше)
      • 3,5 ГБ свободного места на диске
      
      
      • Доступ к сети Интернет
      
      

  Инструкции по установке