Группировка данных в Excel с помощью функции GROUPBY

Функция GROUPBY, представленная в конце 2023 года, революционизирует создание сводных таблиц и агрегацию данных в Excel, предлагая гибкость и мощь динамических массивов.

Если вы разбираете формулы в Excel с нуля или хотите ускорить расчеты, здесь показано, как использовать функции в экселе на понятных и рабочих примерах.

Это руководство научит вас эффективно использовать GROUPBY для анализа данных, начиная с базовых примеров и переходя к продвинутым сценариям с сортировкой, фильтрацией и несколькими функциями агрегации.

Синтаксис и базовое применение

Синтаксис функции GROUPBY выглядит следующим образом: Русская версия: =ЁРНУОБЧ(row_fields; values; function; [field_headers]; [total_depth]; [sort_order]; [filter_array])English version: =GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array]).

Рассмотрим простой набор данных с колонками: Категория, Продукт, Продажи и Себестоимость.

Набор данных включает колонки: Категория, Продукт, Продажи и Себестоимость.

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

Русская версия:

=ЁРНУОБЧ(A4:A21; C4:C21; SUM)

English version:

=GROUPBY(A4:A21, C4:C21, SUM)

Формула GROUPBY возвращает три категории и общие продажи для каждой. Внизу отображается строка итогов.

Расширенные возможности GROUPBY

Несколько колонок значений и заголовки: Для агрегации продаж и себестоимости с отображением заголовков укажите аргумент заголовков как 3 ("Да, и показать").
Русская версия:
```
=ЁРНУОБЧ(A4:A21; C4:D21; SUM; 3)
```
English version:
```
=GROUPBY(A4:A21, C4:D21, SUM, 3)
```
GROUPBY с заголовками и двумя колонками значений (Продажи и Себестоимость).
Группировка по нескольким полям, итоги и сортировка: Используйте оба поля (Категория и Продукт) для группировки. Аргумент total_depth=2 добавляет промежуточные и общие итоги. sort_order=-3 сортирует по третьей колонке (Продажи) по убыванию. filter_array исключает строки (например, категорию "Травы").
Русская версия:
```
=ЁРНУОБЧ(A4:B21; C4:D21; SUM; 3; 2; -3; A4:A21<>"Herbs")
```
English version:
```
=GROUPBY(A4:B21, C4:D21, SUM, 3, 2, -3, A4:A21<,>,"Herbs")
```
Результат с группировкой по двум полям, итогами, сортировкой по убыванию продаж и фильтрацией.
Использование нескольких функций агрегации: Для отображения нескольких метрик (например, СЧЁТ / COUNT, СУММ / SUM, PERCENTOF) используйте функцию ГСТОЛБИК (HSTACK).
Русская версия:
```
=ЁРНУОБЧ(A4:A21; C4:C21; ЖСТАВЙ(COUNT; SUM; PERCENTOF))
```
English version:
```
=GROUPBY(A4:A21, C4:C21, HSTACK(COUNT, SUM, PERCENTOF))
```
GROUPBY с ГСТОЛБИК (HSTACK) для отображения количества, суммы и процента от общей суммы продаж.

Детали аргументов функции

Function (Функция): Может быть любой ЛЯМБДА (LAMBDA)-функцией. Excel предоставляет 16 Eta-ЛЯМБДА (Lambda) функций для упрощения: СУММ / SUM, PERCENTOF, СРЗНАЧ / AVERAGE, МЕДИАНА / MEDIAN, СЧЁТ / COUNT, СЧЁТЗ / COUNTA, МАКС / MAX, МИН / MIN, ПРОИЗВЕД / PRODUCT, ARRAYTOTEXT, СЦЕП / CONCAT, СТАНДОТКЛОН / STDEV.S, СТАНДОТКЛОН / STDEV.P, ДИСП / VAR.S, ДИСП / VAR.P, МОДА / MODE.SNGL.
Field Headers (Заголовки полей): 0=Нет, 1=Да, но не показывать, 2=Нет, но сгенерировать, 3=Да и показать.
Total Depth (Уровень итогов): 0=Без итогов, 1=Общие итоги внизу, 2=Общие и промежуточные итоги внизу, -1=Общие итоги вверху, -2=Общие и промежуточные итоги вверху.
Sort Order (Порядок сортировки): Номер колонки для сортировки. Положительное число — по возрастанию, отрицательное — по убыванию. Для сортировки по нескольким колонкам используйте массив, например, {3;-4}.
ФИЛЬТР / FILTER Array (Массив фильтра): Логический массив для исключения строк по условию.

Бонус: Подсчет уникальных комбинаций

Функция GROUPBY может заменить СЧЁТЕСЛИМН / COUNTIFS. Для подсчета количества вхождений каждой уникальной комбинации используйте функцию СЧЁТЗ / COUNTA.