Функция PIVOTBY позволяет создать сводку данных с помощью формулы. Он поддерживает группирование по двум осям и агрегирование связанных значений. Например, если у вас есть таблица данных о продажах, можно создать сводку продаж по состоянию и годам.
Примечание
Хотя он может создавать аналогичные выходные данные, PIVOTBY не имеет прямого отношения к функции сводной таблицы Excel.
Синтаксис
Функция PIVOTBY позволяет группировать, агрегировать, сортировать и фильтровать данные на основе указанных полей строк и столбцов.
Синтаксис функции PIVOTBY:
PIVOTBY(row_fields;col_fields;значения;функция;[field_headers];[row_total_depth];[row_sort_order];[col_total_depth];[col_sort_order];[filter_array],[relative_to])
| Аргумент | Описание |
|---|---|
|
row_fields (обязательно) |
Ориентированный на столбец массив или диапазон, содержащий значения, используемые для группирования строк и создания заголовков строк. Массив или диапазон может содержать несколько столбцов. В этом случае выходные данные будут иметь несколько уровней группы строк. |
|
col_fields (обязательно) |
Ориентированный на столбец массив или диапазон, содержащий значения, используемые для группировки столбцов и создания заголовков столбцов. Массив или диапазон может содержать несколько столбцов. В этом случае выходные данные будут иметь несколько уровней группы столбцов. |
|
Значения (обязательно) |
Ориентированный на столбец массив или диапазон данных для агрегирования. Массив или диапазон может содержать несколько столбцов. В этом случае выходные данные будут иметь несколько агрегатов. |
|
функция (обязательно) |
Лямбда-функция или eta-сокращенная лямбда(SUM, AVERAGE, COUNT и т. д.), которая определяет способ агрегирования значений. Можно предоставить вектор лямбда-выражений. В этом случае выходные данные будут иметь несколько агрегатов. Ориентация вектора будет определять, расположены ли они на уровне строк или столбцов. |
| field_headers | Число, указывающее, имеют ли row_fields, col_fields и значения заголовки и должны ли возвращаться заголовки полей в результатах. Возможные значения: Отсутствует: автоматически. 0: Нет 1: да и не показывать 2: нет, но создать 3: Да и показать Примечание: Автоматически предполагает, что данные содержат заголовки на основе аргумента values. Если 1-е значение — текст, а 2-е — число, то предполагается, что данные имеют заголовки. Заголовки полей отображаются при наличии нескольких уровней группы строк или столбцов. |
| row_total_depth | Определяет, должны ли заголовки строк содержать итоги. Возможные значения: Отсутствует: автоматически: общие итоги и, по возможности, промежуточные итоги. 0: нет итогов 1: Общие итоги 2: общие и промежуточные итоги -1: общие итоги в верхней части -2: общие и промежуточные итоги в верхней части Примечание: Для промежуточных итогов row_fields должны содержать по крайней мере 2 столбца. Числа больше 2 поддерживаются при условии , что row_field имеет достаточно столбцов. |
| row_sort_order | Число, указывающее порядок сортировки столбцов. Числа соответствуют столбцам в row_fields за ними следуют столбцы в значениях. Если число отрицательное, строки сортируются по убыванию или в обратном порядке. Вектор чисел можно предоставить при сортировке на основе только row_fields. |
| col_total_depth | Определяет, должны ли заголовки столбцов содержать итоги. Возможные значения: Отсутствует: автоматически: общие итоги и, по возможности, промежуточные итоги. 0: нет итогов 1: Общие итоги 2: общие и промежуточные итоги -1: общие итоги в верхней части -2: общие и промежуточные итоги в верхней части Примечание: Для промежуточных итогов col_fields должны содержать по крайней мере 2 столбца. Числа больше 2 поддерживаются при условии , что col_field имеет достаточно столбцов. |
| col_sort_order | Число, указывающее, как следует отсортировать строки. Числа соответствуют столбцам в col_fields за ними следуют столбцы в значениях. Если число отрицательное, строки сортируются по убыванию или в обратном порядке. Вектор чисел можно предоставить при сортировке на основе только col_fields. |
| filter_array | Ориентированный на столбец 1D-массив логических элементов, указывающий, следует ли учитывать соответствующую строку данных. Примечание: Длина массива должна соответствовать длине, предоставленной row_fields и col_fields. |
| relative_to | При использовании агрегатной функции, для которой требуются два аргумента, relative_to определяет, какие значения предоставляются во второй аргумент агрегатной функции. Обычно используется, когда функция PERCENTOF поставляется в функцию. Возможные значения: 0: итоги по столбцам (по умолчанию) 1. Итоги строк 2: Общие итоги 3. Итог родительского столбца 4. Итог родительской строки Примечание: Этот аргумент влияет только в том случае, если функция требует двух аргументов. Если вы предоставляете настраиваемую лямбда-функцию для работы, она должна следовать следующему шаблону: LAMBDA(подмножество,totalset,SUM(подмножество)/СУММ(totalset)) |
Примеры
Пример 1. Используйте PIVOTBY для создания сводки общих продаж по продуктам и годам.
Пример 2. Используйте PIVOTBY для создания сводки по общему объему продаж по продуктам и годам. Сортировка по убыванию по продажам.
