Группировка данных в Excel с помощью функции GROUPBY
Функция GROUPBY, представленная в конце 2023 года, революционизирует создание сводных таблиц и агрегацию данных в Excel, предлагая гибкость и мощь динамических массивов.
Это руководство научит вас эффективно использовать GROUPBY для анализа данных, начиная с базовых примеров и переходя к продвинутым сценариям с сортировкой, фильтрацией и несколькими функциями агрегации.
Синтаксис и базовое применение
Синтаксис функции GROUPBY выглядит следующим образом: =GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array]) (=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array])).
Рассмотрим простой набор данных с колонками: Категория, Продукт, Продажи и Себестоимость.
Чтобы получить общие продажи для каждой категории, используйте формулу:
=GROUPBY(А4:А21, В4:В21, СУММ) (=GROUPBY(A4:A21, C4:C21, SUM))
Расширенные возможности GROUPBY
- Несколько колонок значений и заголовки: Для агрегации продаж и себестоимости с отображением заголовков укажите аргумент заголовков как 3 ("Да, и показать").
=GROUPBY(А4:А21, В4:Г21, СУММ, 3) (=GROUPBY(A4:A21, C4:D21, SUM, 3))
GROUPBY с заголовками и двумя колонками значений (Продажи и Себестоимость). - Группировка по нескольким полям, итоги и сортировка: Используйте оба поля (Категория и Продукт) для группировки. Аргумент
total_depth=2добавляет промежуточные и общие итоги.sort_order=-3сортирует по третьей колонке (Продажи) по убыванию.filter_arrayисключает строки (например, категорию "Травы").=GROUPBY(А4:Б21, В4:Г21, СУММ, 3, 2, -3, А4:А21<>"Herbs") (=GROUPBY(A4:B21, C4:D21, SUM, 3, 2, -3, A4:A21<>"Herbs"))
Результат с группировкой по двум полям, итогами, сортировкой по убыванию продаж и фильтрацией. - Использование нескольких функций агрегации: Для отображения нескольких метрик (например, СЧЁТ (COUNT), СУММ (SUM), PERCENTOF) используйте функцию
ГСТОЛБИК (HSTACK).=GROUPBY(А4:А21, В4:В21, ГСТОЛБИК(СЧЁТ, СУММ, PERCENTOF)) (=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).
=GROUPBY(А2:Б503, Б2:Б503, СЧЁТЗ) (=GROUPBY(A2:B503, B2:B503, COUNTA))
Функция GROUPBY — это мощный инструмент для быстрого и гибкого анализа данных прямо в формулах Excel, экономящий время и упрощающий создание сложных отчетов.