Агрегатные функции в Power Pivot

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

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

Самые распространенные агрегатные функции, например тех, кто использует Среднее, СЧЕТЧИК, DISTINCTCOUNT, MAX, MINили Сумма могут создаваться в мер автоматически с помощью функции "Автосумма". Другие типы агрегатные функции, такие как 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 с помощью функции "Автосумма" или путем создания формулы. Также можно создавать Неявные меры с помощью стандартных агрегатов в сводной таблице в Excel.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция

Использование

AVERAGE

Возвращает среднее арифметическое всех чисел из столбца.

AVERAGEA

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

COUNT

Функция подсчитывает количество числовых значений в столбце.

COUNTA

Функция подсчитывает количество непустых значений в столбце.

MAX

Возвращает наибольшее числовое значение из столбца.

MAXX

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

MIN

Возвращает наименьшее числовое значение в столбце.

MINX

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

SUM

Функция добавляет все числа в столбец.

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

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

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

Функция

Использование

AVERAGEX

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

COUNTAX

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

COUNTBLANK

Функция подсчитывает количество пустых значений в столбце.

COUNTX

Функция подсчитывает общее количество строк в таблице.

COUNTROWS

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

SUMX

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

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

Имена этих функций совпадают с аналогичными функциями Excel, однако DAX-функции используют подсистему аналитики в памяти 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 присутствуют агрегатные функции, работающие с задаваемыми датами и временем, для предоставления встроенной логики операций со временем. Эти функции используют диапазоны дат для получения связанных значений и их статистической обработки. Сравнение значений по диапазонам дат также возможно.

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

Функция

Использование

CLOSINGBALANCEMONTH

CLOSINGBALANCEQUARTER

CLOSINGBALANCEYEAR

Функция вычисляет значение на конечную дату календаря данного периода.

OPENINGBALANCEMONTH

OPENINGBALANCEQUARTER

OPENINGBALANCEYEAR

Функция вычисляет значение на конечную дату календаря периода, предшествующего данному.

TOTALMTD

TOTALYTD

TOTALQTD

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

Другие функции в разделе «Логика операций со временем» (Функции логики операций со временем) — это функции, которые могут использоваться для извлечения дат или пользовательских диапазонов дат для использования в агрегате. Например, с помощью функции DATESINPERIOD можно получить диапазон дат и использовать этот набор дат в качестве аргумента другой функции для вычисления пользовательского агрегата только по этим датам.

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

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

Были ли сведения полезными?

Спасибо за ваш отзыв!

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

×