PIVOTBY в Excel — инструкция и примеры

Important: PIVOTBY возвращает динамический «spilled array», поэтому формула должна быть введена в ячейку за пределами формата Excel Table.

Быстрый план статьи

Краткое объяснение PIVOTBY и синтаксиса
Обязательные и дополнительные аргументы с примерами
Пошаговые примеры: суммирование, подитоги, сортировка, проценты
Когда PIVOTBY не подойдёт и альтернативы
Практические приёмы, шаблоны и чек-листы
Тесты и критерии приёмки
Советы по производительности и совместимости

Что такое PIVOTBY — простыми словами

PIVOTBY — это функция Excel, которая группирует строки и столбцы и вычисляет агрегаты в пересечениях. Представьте классическую сводную таблицу, но как формулу: она автоматически обновляется при изменении данных и может быть встроена в расчёты рядом с исходной таблицей.

Определение в одну строку: PIVOTBY формирует матрицу агрегированных значений по выбранным полям строк и столбцов.

Ключевые термины

Строки — группы, которые идут по вертикали слева (аргумент a).
Столбцы — группы, которые идут по горизонтали сверху (аргумент b).
Значения — ячейки, которые нужно агрегировать (аргумент c).
Агрегат — функция, которая вычисляет итог (аргумент d), например SUM или PERCENTOF.

Синтаксис PIVOTBY

Формула принимает до 11 аргументов. Первые четыре обязательны:

=PIVOTBY(a,b,c,d)

где:

a — диапазон(ы) для заголовков строк (может быть один столбец или несколько смежных столбцов в виде Structured Reference).
b — диапазон(ы) для заголовков столбцов.
c — диапазон значений для агрегирования.
d — агрегирующая функция или LAMBDA, например SUM, AVERAGE, COUNT, PERCENTOF.

Необязательные аргументы позволяют точнее управлять заголовками, итогами, сортировкой и фильтрацией. Полная форма:

=PIVOTBY(a,b,c,d,[e,f,g,h,i,j,k])

Таблица значений необязательных аргументов (кратко):

Аргумент	Что означает	Примечания
e	Управление исходными заголовками (включать/не включать/генерировать)	0=Нет заголовков (по умолчанию), 1=Заголовки, не показывать, 2=Нет в исходном, но сгенерировать, 3=Показать
f	Итого/подитоги для аргумента a (строки)	0=Нет, 1=Grand total внизу (по умолчанию), 2=Подитоги и итоги внизу, -1=Grand total вверху, -2=Подитоги и итоги вверху
g	Сортировка по столбцам результата	Номер столбца (положительный=по возрастанию, отрицательный=по убыванию)
h	Итого/подитоги для аргумента b (столбцы)	0=Нет, 1=Grand total справа (по умолчанию), 2=Подитоги и итоги справа, -1=Grand total слева, -2=Подитоги и итоги слева
i	Сортировка по строкам результата	Аналогично g, но относится к строкам
j	Фильтр — логическое выражение для исключения строк	Можно использовать LAMBDA/условие
k	Контекст для PERCENTOF	0=по столбцам (по умолчанию), 1=по строкам, 2=по общему итогу, 3=по родительскому столбцу, 4=по родительской строке

Пример данных для демонстрации

Таблица данных Excel с колонками Year, Sport, Region, Viewers

Alt: Таблица Excel с колонками Year (Год), Sport (Вид спорта), Region (Регион) и Viewers (Зрители).

В примерах ниже используется таблица Sports_Viewers со столбцами Year, Sport, Region и Viewers.

Использование только обязательных аргументов

Если вам нужен быстрый свод по двух измерениям, достаточно четырёх аргументов. В ячейке F1 введите:

=PIVOTBY(Sports_Viewers[Sport],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM)

где:

Sports_Viewers[Sport] — строки (вид спорта).
Sports_Viewers[Year] — столбцы (год).
Sports_Viewers[Viewers] — значения для суммирования.
SUM — агрегат (сумма).

Результат PIVOTBY: спорта вниз по левому краю, годы по верхнему краю и суммы в ячейках

Alt: Результат формулы PIVOTBY с видами спорта по вертикали, годами по горизонтали и итогами.

В результате вы получаете сразу и итоги по строкам, и по столбцам (поведение по умолчанию без дополнительных аргументов). Если данные не полны, это видно сразу — пустые пересечения означают отсутствие записей.

Группировка нескольких столбцов в строках

Чтобы в аргументе a включить два столбца (например Sport и Region), используйте структурированную ссылку на смежные столбцы:

=PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM)

Результат PIVOTBY с двумя переменными в поле строк

Alt: Результат PIVOTBY с детализацией по Region внутри каждого Sport.

Если столбцы не смежные, соберите их через CHOOSECOLS:

=PIVOTBY(CHOOSECOLS(Sports_Viewers,2,4), ... )

CHOOSECOLS помогает выбрать произвольные столбцы по их порядковым номерам.

Пропуск необязательных аргументов

Чтобы пропустить необязательный аргумент, ставьте подряд две запятые: первая открывает аргумент, вторая закрывает. Excel поймёт, что вы хотите оставить значение по умолчанию.

Пример 1: Подитоги и итоги для строк

Добавим подитоги для каждой группы Sport:

=PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM,,2)

Здесь после SUM идут две запятые: первая — для e (оставляем по умолчанию), вторая — передаёт значение 2 в f, то есть генерировать подитоги и общий итог внизу.

Результат PIVOTBY с подчёркнутыми подитогами

Alt: PIVOTBY результат с выделенными подитогами и итогами внизу.

Важно: форматирование ячеек, применённое вручную к spilled array, остаётся прикреплённым к ячейкам. Если массив изменится (вырвется на другие ячейки), формат может не обновиться. Предпочтительнее использовать условное форматирование или форматирование исходных данных.

Пример 2: Сортировка по итоговой колонке

Добавим сортировку по сумме просмотров (по возрастанию):

=PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM,,2,3)

Здесь g=3 означает сортировку по третьему столбцу результата (в данном случае — общей сумме). Отрицательное значение, например -3, даст сортировку в обратном порядке.

Результат PIVOTBY с сортировкой по итогу

Alt: Результат PIVOTBY, отсортированный по общей сумме просмотров.

Пример 3: Проценты с PERCENTOF

Чтобы показать долю каждого значения от общего итога, используйте PERCENTOF:

=PIVOTBY(Sports_Viewers[Sport],Sports_Viewers[Year],Sports_Viewers[Viewers],PERCENTOF,,,2,,,,2)

Здесь:

d = PERCENTOF — агрегат: возвращает долю.
g = 2 — сортировка по значениям.
k = 2 — вычислять проценты относительно общего итога (Grand total).

Результат PIVOTBY с PERCENTOF в виде десятичных дробей

Alt: Результат PIVOTBY до форматирования в проценты (десятичные числа).

После получения десятичных дробей выделите диапазон и примените кнопку Percent Style на вкладке Home, а также настройте число знаков с помощью Increase/Decrease Decimal.

Выделение ячеек и кнопки Percent Style и Increase Decimal в Excel

Alt: Выделенные ячейки и кнопки Percent Style, Increase Decimal на панели Excel.

Окончательный результат:

Результат PIVOTBY в процентах с интерпретацией долей

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

Пример интерпретации: 11,5% — доля просмотров softбола в 2022 году от общего объёма по всем видам спорта и годам.

Когда PIVOTBY не подходит — контрпримеры и ограничения

Большие объёмы данных и сложные вычисления: если у вас миллионы строк, Power Query или PivotTable на агрегированных источниках может работать быстрее.
Требуется интерактивность и срезы (Slicers): классическая PivotTable даёт готовые средства взаимодействия пользователю.
Форматирование вывода как таблицы Excel: PIVOTBY возвращает spilled array, но не формат Table; если нужно именно Table с его возможностями (структурированные ссылки для последующих расчётов), потребуется дополнительная обработка.
Нужны сложные вычисляемые поля с зависимостями от нескольких сводок: иногда проще создать несколько PIVOTBY или использовать DAX в Power Pivot.

Альтернативные подходы

PivotTable — интерактивный инструмент с срезами, быстрыми сводками и визуализацией.
GROUPBY — группирует по строкам, можно применять LAMBDA; удобен для простых списков с одной осью группировки.
Power Query (Get & Transform) — подходит для предварительной обработки и агрегации большого объёма данных перед загрузкой в Excel.
Power Pivot / Data Model + DAX — для сложных расчётов, связанных с несколькими таблицами и временем; даёт масштабируемость и производительность.

Ментальные модели и эвристики

«PivotTable как UI, PIVOTBY как код» — если нужно встроить результат в формулы или логики листа, PIVOTBY лучше. Если нужен интерактивный отчёт для конечного пользователя — PivotTable удобнее.
Подумайте о направлении агрегирования: строки для детализации по сущности (категории), столбцы для временных срезов или групп, значения для метрик.
Всегда сохраняйте исходную таблицу в формате Excel Table: Structured References упрощают формулы и делают их понятней.

Проверки качества и критерии приёмки

Критерии приёмки для PIVOTBY-отчёта:

Правильность агрегации — значения в каждой ячейке совпадают с эталонными подсчётами (SUM/COUNT/AVG) при фильтрации по тем же измерениям.
Корректность итоговых строк/столбцов — Grand total отражает сумму видимых значений.
Стабильность при добавлении строк в исходную таблицу — PIVOTBY автоматически обновляется и не обрывает форматирование критичных ячеек.
Форматирование чисел — денежные суммы, проценты и т. п. отображаются согласно требованиям.
Производительность — время обновления приемлемо для объёма данных.

Тестовые случаи для проверки:

Добавить новую строку в исходную таблицу — проверить, что результат изменился.
Удалить несколько строк — проверить, что агрегаты уменьшились соответственно.
Применить пустые значения и текст в поле Viewers — проверить поведение SUM/COUNT.
Изменить аргументы сортировки на отрицательное значение — проверить порядок.

Шаблоны и сниппеты (cheat sheet)

Основные шаблоны PIVOTBY:

Сумма по двум осям:

=PIVOTBY(Table[RowField],Table[ColField],Table[ValueField],SUM)

Детализация с подитогами:

=PIVOTBY(Table[[Field1]:[Field2]],Table[Col],Table[Value],SUM,,2)

Проценты относительно общего итога:

=PIVOTBY(Table[Row],Table[Col],Table[Value],PERCENTOF,,,,,,2)

Сортировка по итоговой колонке (по убыванию):

=PIVOTBY(a,b,c,d,,, -n)

Фильтрация через j (пример LAMBDA фильтра):

=PIVOTBY(a,b,c,d,,,,,,LAMBDA(row, INDEX(row,1)<>"Exclude"))

(Примечание: синтаксис фильтра зависит от версии Excel и сложности выражения.)

Процедура (SOP) — как создать отчёт на PIVOTBY шаг за шагом

Подготовьте исходную таблицу в формате Table (CTRL+T). Дайте ей понятное имя, например Sports_Viewers.
Выберите свободную область листа вне таблицы.
Введите PIVOTBY с базовыми аргументами: строки, столбцы, значения, функция.
При необходимости добавьте f/h для подитогов и итогов.
Настройте сортировку через g/i.
Примените форматирование цифр (Percent Style и т. п.) через условное форматирование или Number format.
Проверьте поведение при изменении исходных данных.
Зафиксируйте лист/диапазон при необходимости, используя защиту листа.

Совместимость и миграция

PIVOTBY относится к современным динамическим функциям Excel. Работает в последних версиях Excel для Microsoft 365 с поддержкой динамических массивов.
В старых оффлайн-версиях Excel (без динамических массивов) PIVOTBY может отсутствовать. Для совместимости используйте PivotTable или Power Query.

Риски и рекомендации по безопасности

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

Чек-листы по ролям

Аналитик — при создании PIVOTBY отчёта:

Таблица подготовлена и очищена.
Имена столбцов читаемы и корректны.
Выбран правильный агрегат (SUM/AVERAGE/COUNT/PERCENTOF).
Добавлены подитоги при необходимости.
Результат протестирован на выборочных записях.

Разработчик отчётов:

Формулы вынесены в отдельный лист.
Используются структурированные ссылки.
Файлы документированы (где взять данные, что считать).
Обеспечен откат/резервная копия перед изменениями.

Пользователь бизнес-отчётов:

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

Тестовые случаи и приёмка

Предложенные тесты для CI/QA приёмки отчёта:

Проверка арифметики: сверить сумму итогов с суммой исходных значений.
Проверка пустых значений: вставить пустые Viewers и убедиться, что поведение корректное.
Проверка строк/столбцов: добавить новый год в исходную таблицу и убедиться, что он появился в PIVOTBY.

Нюансы форматирования и условное форматирование

Не применяйте постоянное форматирование вручную ко всем ячейкам spilled array — при изменении размера массива часть форматирования может остаться в неправильных ячейках.
Используйте правило условного форматирования на основе формул или используйте форматирование исходных данных.

Мини-словарь (1 строка на термин)

Spilled array — динамический массив, который «выплёскивается» из одной ячейки в соседние.
Structured Reference — структурированная ссылка на столбцы таблицы по имени.
PERCENTOF — агрегат, возвращающий долю относительно выбранного контекста.

Решение в виде блок-схемы (Mermaid)

flowchart TD
  A[Начало: есть таблица] --> B{Нужна ли интерактивность?}
  B -- Да --> C[Использовать PivotTable]
  B -- Нет --> D{Нужно встроить формулу в лист?}
  D -- Да --> E[Использовать PIVOTBY]
  D -- Нет --> F[Использовать GROUPBY или Power Query]
  E --> G{Объём данных большой?}
  G -- Да --> H[Рассмотреть Power Query или Power Pivot]
  G -- Нет --> I[Продолжить с PIVOTBY и тестированием]

Примеры LAMBDA внутри PIVOTBY

Вы можете передавать свои функции через LAMBDA. Пример подсчёта нестандартной метрики:

=LET(
  myCalc, LAMBDA(values, IF(COUNTA(values)=0,0,SUM(values)/COUNTA(values))),
  PIVOTBY(Table[Row],Table[Col],Table[Value],myCalc)
)

Этот приём полезен для нестандартных правил агрегации.

Подведём итог

PIVOTBY — гибкая современная функция Excel для быстрого построения матриц агрегатов без необходимости создавать PivotTable. Она отлично подходит, когда нужен динамический отчёт прямо на листе и когда необходима комбинация строк и столбцов с различными функциями агрегации.

Ключевые шаги для практики: подготовьте таблицу, выберите оси (строки и столбцы), определите агрегат, добавьте при необходимости подитоги и сортировку. Тестируйте поведение при добавлении/удалении строк и применяйте условное форматирование для визуального выделения подитогов.

Summary:

PIVOTBY даёт контролируемую матрицу агрегатов прямо в ячейке.
Используйте SUM, AVERAGE, COUNT, PERCENTOF и собственные LAMBDA.
Для больших данных и интерактивных отчётов рассматривайте Power Query, PivotTable или Power Pivot.

PIVOTBY в Excel: как сводить данные без сводной таблицы