Группировка данных в Excel с помощью функции GROUPBY
Функция GROUPBY позволяет группировать данные и рассчитывать итоги формулой, предлагая гибкость и мощь динамических массивов.
Русское имя — ГРУПППО. Примеры требуют актуальной Microsoft 365: Windows, Mac или веб. Алфавитный справочник помечает GROUPBY как Microsoft 365; на Excel 2016/2019/2021/2024 с постоянной лицензией эти инструкции не рассчитаны. Разлив размещайте вне исходной таблицы.
Если вы разбираете формулы в Excel с нуля или хотите ускорить расчеты, здесь показано, как использовать функции в экселе на понятных и рабочих примерах.
Это руководство научит вас эффективно использовать GROUPBY для анализа данных, начиная с базовых примеров и переходя к продвинутым сценариям с сортировкой, фильтрацией и несколькими функциями агрегации.
Синтаксис и базовое применение
Синтаксис функции GROUPBY выглядит следующим образом: Русская версия: =ГРУПППО(row_fields; values; function; [field_headers]; [total_depth]; [sort_order]; [filter_array]; [field_relationship])English version: =GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship]).
Рассмотрим простой набор данных с колонками: Категория, Продукт, Продажи и Себестоимость. В примерах заголовки находятся в A4:D4, записи — в A5:D21.
Чтобы получить общие продажи для каждой категории, используйте формулу:
=ГРУПППО(A5:A21;C5:C21;СУММ;0;1)=GROUPBY(A5:A21,C5:C21,SUM,0,1)
Расширенные возможности GROUPBY
- Несколько колонок значений и заголовки: Для агрегации продаж и себестоимости с отображением заголовков укажите аргумент заголовков как 3 ("Да, и показать").
Русская версия:
=ГРУПППО(A4:A21; C4:D21; СУММ; 3)English version:=GROUPBY(A4:A21, C4:D21, SUM, 3)
GROUPBY с заголовками и двумя колонками значений (Продажи и Себестоимость). - Группировка по нескольким полям, итоги и сортировка: Используйте оба поля (Категория и Продукт) для группировки. Аргумент
total_depth=2добавляет промежуточные и общие итоги.sort_order=-3сортирует по третьей колонке (Продажи) по убыванию.filter_arrayисключает строки (например, категорию "Травы").Русская версия:=ГРУПППО(A4:B21; C4:D21; СУММ; 3; 2; -3; A4:A21<>"Herbs")English version:=GROUPBY(A4:B21, C4:D21, SUM, 3, 2, -3, A4:A21<>"Herbs")
Результат с группировкой по двум полям, итогами, сортировкой по убыванию продаж и фильтрацией. - Использование нескольких функций агрегации: Для отображения нескольких метрик (например, СЧЁТ / COUNT, СУММ / SUM, PERCENTOF) используйте функцию
ГСТОЛБИК (HSTACK).Русская версия:=ГРУПППО(A5:A21;C5:C21;ГСТОЛБИК(СЧЁТ;СУММ;ПРОЦЕНТ);0;1)English version:=GROUPBY(A5:A21,C5:C21,HSTACK(COUNT,SUM,PERCENTOF),0,1)
GROUPBY с ГСТОЛБИК (HSTACK) для отображения количества, суммы и процента от общей суммы продаж.
Детали аргументов функции
- Function (Функция): агрегирующая LAMBDA или поддерживаемая функция без скобок: например, СУММ / SUM, СРЗНАЧ / AVERAGE, СЧЁТ / COUNT, СЧЁТЗ / COUNTA, ПРОЦЕНТ / PERCENTOF. Вектор функций задаёт несколько итогов; ГСТОЛБИК / HSTACK располагает их рядом.
- Field Headers (Заголовки полей): 0=Нет, 1=Да, но не показывать, 2=Нет, но сгенерировать, 3=Да и показать.
- Total Depth (Уровень итогов): 0=Без итогов, 1=Общие итоги внизу, 2=Общие и промежуточные итоги внизу, -1=Общие итоги вверху, -2=Общие и промежуточные итоги вверху.
- Sort Order (Порядок сортировки): Номер колонки для сортировки. Положительное число — по возрастанию, отрицательное — по убыванию. Для сортировки по нескольким колонкам используйте массив, по полям группировки, например
{1;-2}. Вектор сортировки документирован для столбцов row_fields; сортировку по итоговому значению задавайте одним номером. - ФИЛЬТР / FILTER Array (Массив фильтра): Логический столбец той же высоты, что и исходные диапазоны; ИСТИНА включает строку. Дополнительный field_relationship: 0 — иерархия (по умолчанию), 1 — плоская таблица; влияет на сортировку вложенных полей.
Бонус: Подсчет уникальных комбинаций
Функция GROUPBY может заменить СЧЁТЕСЛИМН / COUNTIFS. В примере A2:B503 не содержит заголовков, а B заполнен для каждой записи. Тогда для подсчёта числа вхождений каждой комбинации подходит СЧЁТЗ / COUNTA.
=ГРУПППО(A2:B503;B2:B503;СЧЁТЗ;0;0)=GROUPBY(A2:B503,B2:B503,COUNTA,0,0)
Функция GROUPBY — это мощный инструмент для быстрого и гибкого анализа данных прямо в формулах Excel, экономящий время и упрощающий создание сложных отчетов.