Агрегаты в формулах
В этом разделе рассказывается об агрегатах и приводятся общие сведения о типах агрегатов, поддерживаемых в PowerPivot для Excel. PowerPivot для Excel содержит следующие средства создания агрегатов.
Можно создавать сводные таблицы и сводные диаграммы на основе данных PowerPivot. Сводные таблицы Excel являются популярным средством для группирования данных на листах и составления сводных показателей. PowerPivot интегрируется с функциями сводной таблицы в Excel и реализует множество улучшений.
Можно задавать пользовательские агрегаты с помощью формул на языке DAX. С помощью DAX можно создавать вычисляемые столбцы в таблицах PowerPivot, а также меры в сводных таблицах и диаграммах.
В последней части этого раздела приводятся ссылки на более подробные сведения о построении агрегатов.
Общие сведения об агрегатах
Агрегаты — это способ группирования данных, свертывания данных и получения итоговых данных. В начале работы с необработанными данными из таблиц или других источников данных эти данные часто бывают неструктурированными, то есть представляют собой множество подробных данных, никак не упорядоченных и не сгруппированных. Такое отсутствие сводок или структуры может затруднить обнаружение закономерностей в данных. В связи с этим важной частью работы аналитика является определение агрегатов, упрощающих, резюмирующих или сводящих закономерности для ответа на конкретный бизнес-вопрос.
Выбор групп для агрегата
При агрегатной обработке данных они группируются по таким атрибутам, как продукт, цена, регион или дата, а затем определяется формула, работающая для всех данных в группе. Например, если создаются итоговые показатели за год, то это агрегат. Если создается соотношение этого года с предыдущим годом и данные представляются в виде процентов, то это другой тип агрегата.
Метод группировки данных определяется поставленным бизнес-вопросом. Например, агрегаты могут ответить на следующие вопросы.
Подсчет числа элементов. Сколько транзакций было выполнено за месяц?
Средние значения. Каковы средние продажи в этот месяц на каждого менеджера по продажам?
Минимальные и максимальные значения. Какие районы сбыта находятся в высшей пятерке по количеству проданного товара?
Чтобы создать вычисление, отвечающее на эти вопросы, необходимо иметь подробные данные с числами, которые следует подсчитать или суммировать, и эти числовые данные должны иметь определенную связь с группами, которые будут использоваться для сортировки результатов.
Если поступившие данные не содержат значений, которые можно использовать для группирования (таких как категория товара или географический регион, где расположен магазин), можно создать группы данных путем добавления категорий. При создании групп в Excel необходимо вручную ввести или выделить нужные группы из числа столбцов в рабочем листе.
Однако в реляционных системах многие иерархии (например, категории продуктов) хранятся не в той таблице, где хранятся факты или значения. Обычно таблица категорий связана с данными фактов с использованием какого-либо ключа. Например, предположим, что в данных содержатся идентификаторы продуктов, но не их имена или категории. Чтобы добавить категорию в неструктурированный рабочий лист Excel, потребовалось бы скопировать столбец, содержащий названия категорий. А в рабочей книге PowerPivot можно импортировать таблицу с категориями товаров, создать связь между таблицей числовых данных и списком категорий товаров, а затем сгруппировать данные по категориям. Дополнительные сведения см. в разделе Связи между таблицами.
Выбор функции для агрегата
После того как были определены и добавлены группирования, следует решить, какие математические функции следует использовать в агрегате. Иногда слово «агрегат» является синонимом математических или статистических операций, которые обычно применяются в агрегатах, например операций суммирования, определения среднего, определения минимума или подсчета числа элементов. Однако PowerPivot для Excel позволяет создавать пользовательские агрегатные формулы в дополнение к стандартным агрегатам в Excel.
Например, при наличии того же набора значений и группирований, использованных в предыдущих экземплярах, можно создать пользовательские агрегаты, которые могут ответить на следующие вопросы.
Фильтруемый подсчет числа элементов. Сколько было транзакций в месяц, исключая период обслуживания в конце месяца?
Соотношения, использующие средние значения за период времени. Каков был процент роста или сокращения продаж по сравнению с тем же периодом в прошлом году?
Сгруппированные минимальные и максимальные значения. Какие районы сбыта были ведущими для каждой категории продукта или для каждого продвижения товара?
Добавление агрегатов к формулам и сводным таблицам
Если готов общий план группирования данных и известно, с какими значениями нужно работать, можно принять решение о построении сводной таблицы или создании вычислений в таблице. PowerPivot для Excel расширяет возможности Excel по созданию агрегатов, таких как суммирование, подсчет числа элементов и определение среднего. В PowerPivot можно создавать нестандартные статистические выражения — в окне PowerPivot или в области сводной таблицы Excel.
В вычисляемом столбце можно создавать агрегаты, которые учитывают текущий контекст строки для получения связанных строк из другой таблицы, а затем суммирования этих значений в связанных строках, подсчета их числа и определения среднего.
В мере можно создавать динамические агрегаты, в которых используются фильтры, определенные в формуле, и фильтры, определяемые структурой сводной таблицы и набором срезов, заголовков столбцов и заголовков строк.
Дополнительные сведения см. в разделе Создание формул для вычислений.
Добавление группирований в сводную таблицу
Во время разработки сводной таблицы поля, представляющие группирования, категории или иерархии, перетаскиваются в раздел столбцов и строк сводной таблицы для группирования данных. Поля с числовыми значениями перетаскиваются в область значений, чтобы для них можно было выполнить подсчет, суммирование и определение среднего.
При добавлении в сводную таблицу категорий, данные которых не связаны с данными фактов, может возникнуть ошибка или непредвиденные результаты. Обычно PowerPivot для Excel предпринимает попытку устранить проблему, автоматически обнаруживая и предлагая связи. Дополнительные сведения см. в разделе Работа со связями в сводных таблицах.
Также можно перетаскивать значения в область срезов для выбора определенных групп данных для просмотра. Срезы — это новая функция в Excel и PowerPivot для Excel, позволяющая интерактивным образом группировать, сортировать и фильтровать результаты в сводной таблице.
Работа с группированиями в формуле
Группирования и категории также можно использовать для агрегатной обработки данных, хранимых в таблицах, путем создания связей между таблицами с последующим созданием формул, использующих данные связи для поиска связанных значений.
Иначе говоря, если нужно создать формулу, группирующую значения по категориям, сначала нужно использовать связь для соединения таблицы, содержащей подробные данные, с таблицей категорий, а затем создать формулу.
Дополнительные сведения о создании формул, использующих уточняющие запросы, см. в разделе Связи и уточняющие запросы в формулах.
Использование фильтров в агрегатах
Новой функцией PowerPivot является возможность применения фильтров не только в столбцах и таблицах данных, пользовательском интерфейсе и сводных таблицах или диаграммах, но также и в самих формулах, используемых для вычисления агрегатов. Фильтры могут использоваться в формулах как в вычисляемых столбцах, так и в мерах.
Например, в новых статистических функциях DAX вместо указания значения для суммирования или подсчета элементов в качестве аргумента можно указать целую таблицу. Если к данной таблице не были применены фильтры, то функция агрегата обработает все значения в заданном столбце таблицы. Однако в DAX можно создать динамический или статический фильтр для таблицы, чтобы агрегат работал относительно разных подмножеств данных в зависимости от условия фильтра и текущего контекста.
Сочетая условия и фильтры в формулах, можно создать агрегаты, изменяющиеся в зависимости от значений, поставляемых в формулах, или в зависимости от выбора заголовков строк и столбцов в сводной таблице.
Дополнительные сведения см. в разделе Фильтрация данных в формулах.
Сравнение статистических функций Excel со статистическими функциями DAX
В следующей таблице приводятся некоторые стандартные статистические функции, предоставляемые в Excel, а также указываются ссылки на реализацию этих функций в PowerPivot для Excel. Версия этих функций в DAX работает во многом аналогично версии в Excel с незначительными различиями в синтаксисе и обработке некоторых типов данных.
Стандартные статистические функции
Функция |
Назначение |
Возвращает арифметическое среднее всех чисел из столбца. |
|
Возвращает среднее арифметическое всех значений из столбца. Обрабатывает текстовые и нечисловые значения. |
|
Подсчитывает количество числовых значений в столбце. |
|
Подсчитывает количество непустых значений в столбце. |
|
Возвращает самое большое числовое значение в столбце. |
|
Возвращает наибольшее значение из набора выражений, вычисленных для таблицы. |
|
Возвращает наименьшее числовое значение в столбце. |
|
Возвращает наименьшее значение из набора выражений, вычисленных для таблицы. |
|
Складывает все числа в столбце. |
Статистические функции DAX
В DAX включены агрегатные функции, позволяющие указать таблицу, в которой следует выполнить статистическую обработку. Таким образом, эти функции вместо простого сложения значений в столбце или определения среднего позволяют создавать выражение, которое динамически определяет данные для статистической обработки.
В следующей таблице перечислены имеющиеся агрегатные функции, доступные в DAX.
Функция |
Назначение |
Определяет среднее для набора выражений, вычисляемых в таблице. |
|
Подсчитывает набор выражений, вычисляемых в таблице. |
|
Подсчитывает количество пустых значений в столбце. |
|
Подсчитывает общее количество строк в таблице. |
|
Подсчитывает количество строк, возвращаемых вложенной табличной функцией, например функцией фильтра. |
|
Возвращает сумму значений набора числовых выражений, вычисленных для таблицы. |
Разница между статистическими функциями DAX и Excel
Имена этих функций совпадают с аналогичными функциями Excel, однако они используют ядро VertiPaq в PowerPivot и были переписаны для работы с таблицами и столбцами. Формулы DAX нельзя использовать в книге Excel, и наоборот. Они могут использоваться только в окне PowerPivot и в сводных таблицах, основанных на данных PowerPivot. Кроме того, правила работы одноименных функций могут несколько отличаться. Дополнительные сведения см. в разделах справочника по отдельным функциям.
Способ вычисления столбцов в статистическом выражении также отличается от способа обработки статистических выражений в Excel. Проиллюстрировать это поможет пример.
Предположим, требуется получить сумму значений в столбце Amount таблицы Sales, для чего создается следующая формула:
=SUM('Sales'[Amount])
В самом простом случае функция получает значения от одного неотфильтрованного столбца, и результат будет такой же, как в Excel, где происходит простое сложение значений в столбце Amount. Однако в PowerPivot формула интерпретируется следующим образом: «получить значение в столбце Amount для каждой строки таблицы Sales, а затем сложить отдельные значения». PowerPivot рассматривает каждую строку, для которой выполняется статистическая обработка, и вычисляет скалярное значение для каждой строки, а затем выполняет статистическую обработку полученных значений. Таким образом, результат вычисления по формуле может зависеть от того, применялись ли к таблице фильтры и вычислялись ли значения на основе других агрегатов, где могли использоваться фильтры. Дополнительные сведения см. в разделе Контекст в формулах DAX.
Функции логики операций со временем DAX
В дополнение к новым табличным статистическим функциям, описанным в предыдущем разделе, в DAX присутствуют статистические функции, работающие с задаваемым временем и датами, для предоставления встроенной логики операций со временем. Эти функции используют диапазоны дат для получения связанных значений и их статистической обработки. Сравнение значений по диапазонам дат также возможно.
Следующая таблица содержит данные по функциям логики операций со временем, которые могут использоваться для статистической обработки.
Функция |
Назначение |
Вычисляет значение на календарный конец заданного периода. |
|
Вычисляет значение на календарный конец периода, предшествующего данному периоду. |
|
Вычисляет значение для интервала, который начинается в первый день периода и заканчивается последней датой в указанном столбце дат. |
Другие функции в разделе функций логики операций со временем (Функции логики операций со временем (DAX)) — это функции, которые можно использовать для получения дат или пользовательских диапазонов дат для использования в агрегате. Например, с помощью функции DATESINPERIOD можно получить диапазон дат и использовать этот набор дат в качестве аргумента другой функции для вычисления пользовательского агрегата только по этим датам.
См. также
Основные понятия
Связи и уточняющие запросы в формулах