17. GROUPBY и PIVOTBY

Раньше, чтобы получить итоги по группам, строили сводную таблицу или писали СУММЕСЛИ для каждой строки. В Microsoft 365 есть две функции, которые делают это одной формулой — и результат разливается сам, как у УНИК и ФИЛЬТР.

GROUPBY(поля_строк; значения; функция; [заголовки]; [итоги]; [сортировка]; [фильтр])

• поля_строк — по чему группировать (регион, менеджер);
• значения — что сворачивать (суммы);
• функция — как сворачивать. Её пишут без скобок: СУММ, СРЗНАЧ, СЧЁТ, МАКС, МИН. Можно передать и свою LAMBDA(x; …);
• заголовки: если не указать, Excel сам решит — текст над числами считается заголовком и показывается. 0 — заголовков нет, 1 — есть, но не показывать, 2 — нет, но сгенерировать, 3 — есть и показать;
• итоги: 1 (по умолчанию) — строка итога внизу, 0 — без итога, −1 — итог сверху;
• сортировка — номер столбца результата; минус — по убыванию: -2 — по второму столбцу от большего к меньшему;
• фильтр — массив ИСТИНА/ЛОЖЬ той же высоты: какие строки брать.

PIVOTBY(строки; столбцы; значения; функция; …) — то же, но группирует сразу в двух направлениях: получается маленькая сводная таблица. Если комбинации нет в данных (в Шымкенте не продавали сахар), ячейка остаётся пустой.

Подпись строки итога в английском Excel — Total; в тренажёре она показана как «Итог». Ограничения тренажёра: промежуточные итоги по уровням (итоги = 2) не поддерживаются, а в PIVOTBY — одно поле строк, одно поле столбцов и один столбец значений.