Агрегаты — это способ группирования данных, свертывания данных и получения итоговых данных. В начале работы с необработанными данными из таблиц или других источников данных эти данные часто бывают неструктурированными, то есть представляют собой множество подробных данных, никак не упорядоченных и не сгруппированных. Такое отсутствие сводок или структуры может затруднить обнаружение закономерностей в данных. Таким образом, важную часть моделирования составляет определение агрегатов, которые упрощают и обобщают данные, выявляя закономерности, позволяющие решить поставленную бизнес-задачу.
Наиболее распространенные агрегаты, например с помощью функции AVERAGE, COUNT, DISTINCTCOUNT, MAX, MIN или SUM , можно создать в меру автоматически с помощью автосуммы. Другие типы агрегатов, такие как AVERAGEX, COUNTX, COUNTROWS или SUMX, возвращают таблицу и требуют формулы, созданной с помощью выражений анализа данных (DAX).
Основные сведения об агрегатах в Power Pivot
Выбор групп для агрегата
При агрегатной обработке данных они группируются по таким атрибутам, как продукт, цена, регион или дата, а затем определяется формула, работающая для всех данных в группе. Например, если создаются итоговые показатели за год, то это агрегат. Если создается соотношение этого года с предыдущим годом и данные представляются в виде процентов, то это другой тип агрегата.
Метод группировки данных определяется поставленным бизнес-вопросом. Например, агрегаты могут ответить на следующие вопросы.
Счетчики Сколько транзакций было выполнено за месяц?
Средние значения Какие показатели средних продаж в этом месяце у каждого менеджера по продажам?
Минимальные и максимальные значения Какие районы сбыта были в горячей пятерке по количеству проданного товара?
Чтобы создать вычисление, отвечающее на эти вопросы, необходимо иметь подробные данные с числами, которые следует подсчитать или суммировать, и эти числовые данные должны иметь определенную связь с группами, которые будут использоваться для сортировки результатов.
Если поступившие данные не содержат значений, которые можно использовать для группирования (таких как категория товара или географический регион, где расположен магазин), можно создать группы данных путем добавления категорий. При создании групп в Excel необходимо вручную ввести или выделить нужные группы из числа столбцов в рабочем листе. Однако в реляционных системах многие иерархии (например, категории продуктов) хранятся не в той таблице, где хранятся факты или значения. Обычно таблица категорий связана с данными фактов с использованием какого-либо ключа. Например, предположим, что в данных содержатся идентификаторы продуктов, но не их имена или категории. Чтобы добавить категорию в неструктурированный рабочий лист Excel, потребовалось бы скопировать столбец, содержащий названия категорий. Используя Power Pivot, можно импортировать таблицу с категориями продуктов в вашу модель данных, создать связь между таблицей с числовыми данными и списком категорий продуктов, затем использовать категории для группирования данных. Дополнительные сведения см. в разделе Create связи между таблицами.
Выбор функции для агрегата
После определения и добавления групп необходимо решить, какие математические функции следует использовать для агрегирования. Часто слово "агрегат" используется в качестве синонима математических или статистических операций, применяемых в агрегатах, таких как суммирование, определение средних значений, определение минимума или подсчет. Тем не менее Power Pivot позволяет создавать пользовательские формулы для агрегирования в дополнение к стандартным агрегатам и в Power Pivot и в Excel.
Например, при наличии того же набора значений и группирований, использованных в предыдущих экземплярах, можно создать пользовательские агрегаты, которые могут ответить на следующие вопросы.
Фильтруемый подсчет Сколько транзакций было в течение месяца, исключая период профилактического обслуживания в конце месяца?
Соотношения, использующие средние значения за период времени Каков был процентный рост или снижение продаж по сравнению с тем же периодом прошлого года?
Сгруппированные минимальные и максимальные значения Какие районы сбыта были ведущими для каждой категории продукта или для каждого стимулирования сбыта?
Добавление агрегатов к формулам и сводным таблицам
Если вы в общих чертах представляете, как нужно сгруппировать данные и с какими значениями вы хотите работать, можно выбрать построение сводной таблицы или создание вычислений в самой таблице. Power Pivot расширяет и улучшает возможности Excel по созданию агрегатов, таких как суммирование, подсчет или определение средних значений. В Power Pivot нестандартные агрегаты можно создавать либо в окне Power Pivot, либо в области сводной таблицы Excel.
-
В вычисляемом столбце можно создавать агрегаты, учитывающие контекст текущей строки для извлечения связанных строк из другой таблицы с последующим суммированием, подсчетом или вычислением среднего значения этих значений в связанных строках.
-
В меру можно создавать динамические агрегаты, использующие как фильтры, определенные в формуле, так и фильтры, налагаемые структурой сводной таблицы и выбором срезов, заголовков столбцов и заголовков строк. Меры, использующие стандартные агрегаты, можно создавать в Power Pivot с помощью автосуммы или путем создания формулы. Вы также можете создавать неявные меры с помощью стандартных агрегатов в сводной таблице Excel.
Добавление группирований в сводную таблицу
Во время разработки сводной таблицы в раздел столбцов и строк сводной таблицы для группирования данных перетаскиваются поля, представляющие группировки, категории или иерархии. Поля с числовыми значениями перетаскиваются в область значений, чтобы для них можно было выполнить подсчет, суммирование и определение среднего.
При добавлении в сводную таблицу категорий, данные которых не связаны с данными фактов, могут возникнуть ошибки или непредвиденные результаты. Обычно Power Pivot пытается устранить проблему, автоматически обнаруживая и предлагая связи. Дополнительные сведения см. в статье Работа со связями в сводных таблицах.
Также можно перетаскивать поля в срезы для выбора определенных групп данных для просмотра. Срезы позволяют в интерактивном режиме группировать, сортировать и фильтровать результаты в сводной таблице.
Работа с группированиями в формуле
Группирования и категории также можно использовать для агрегатной обработки данных, хранимых в таблицах, путем создания связей между таблицами с последующим созданием формул, использующих данные связи для поиска связанных значений.
Иначе говоря, если нужно создать формулу, группирующую значения по категориям, сначала нужно использовать связь для соединения таблицы, содержащей подробные данные, с таблицей категорий, а затем создать формулу.
Дополнительные сведения о создании формул с подстановками см. в статье Подстановка в формулах PowerPivot.
Использование фильтров в агрегатах
Новой функцией Power Pivot является возможность применения фильтров к столбцам и таблицам данных не только в пользовательском интерфейсе и в сводной таблице или диаграмме, но также и в каждой формуле, используемой для вычисления агрегатов. Фильтры можно использовать в формулах как в вычисляемых столбцах, так и в .
Например, в новых агрегатных функциях 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'[Amount])
В самом простом случае функция возвращает значения из одного неотфильтрованного столбца, и результат будет таким же, как в приложении Excel, в котором всегда просто суммируются значения в столбце Amount. Тем не менее в Power Pivot формула интерпретируется как "Получить значение в столбце Amount для каждой строки таблицы Sales и затем сложить эти отдельные значения". Power Pivot вычисляет каждую строку, для которой выполняется агрегирование, и вычисляет единичное скалярное значение для каждой строки, а затем агрегирует эти значения. Поэтому результат формулы может быть разным, если к таблице применялись фильтры или если значения вычислялись на основе других агрегатов, где могли использоваться фильтры. Дополнительные сведения см. в статье Контекст в формулах DAX.
Функции логики операций со временем DAX
В дополнение к табличным статистическим функциям, описанным в предыдущем разделе, в DAX присутствуют агрегатные функции, работающие с задаваемыми датами и временем, для предоставления встроенной логики операций со временем. Эти функции используют диапазоны дат для получения связанных значений и их статистической обработки. Сравнение значений по диапазонам дат также возможно.
Таблица ниже содержит функции логики операций со временем, которые можно использовать для статистической обработки.
|
Функция |
Использование |
|
Функция вычисляет значение на конечную дату календаря данного периода. |
|
|
Функция вычисляет значение на конечную дату календаря периода, предшествующего данному. |
|
|
Функция вычисляет значение для интервала, начинающегося в первый день периода и заканчивающегося последней датой в указанном столбце дат. |
Другие функции в разделе Функция аналитики времени (Функции аналитики времени) — это функции, которые можно использовать для получения дат или настраиваемых диапазонов дат для использования при агрегации. Например, с помощью функции DATESINPERIOD можно получить диапазон дат и использовать этот набор дат в качестве аргумента другой функции для вычисления пользовательского агрегата только по этим датам.
