Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Формулы и функции

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

forest_paw 19.01.2026 713

Группировка данных в 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)
English version:
=GROUPBY(A5:A21,C5:C21,SUM,0,1)
Формула GROUPBY возвращает три категории и общие продажи для каждой. Внизу отображается строка итогов.
Формула GROUPBY возвращает три категории и общие продажи для каждой. Внизу отображается строка итогов.

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

  1. Несколько колонок значений и заголовки: Для агрегации продаж и себестоимости с отображением заголовков укажите аргумент заголовков как 3 ("Да, и показать").
    Русская версия:
    =ГРУПППО(A4:A21; C4:D21; СУММ; 3)
    English version:
    =GROUPBY(A4:A21, C4:D21, SUM, 3)
    GROUPBY с заголовками и двумя колонками значений (Продажи и Себестоимость).
    GROUPBY с заголовками и двумя колонками значений (Продажи и Себестоимость).
  2. Группировка по нескольким полям, итоги и сортировка: Используйте оба поля (Категория и Продукт) для группировки. Аргумент 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")
    Результат с группировкой по двум полям, итогами, сортировкой по убыванию продаж и фильтрацией.
    Результат с группировкой по двум полям, итогами, сортировкой по убыванию продаж и фильтрацией.
  3. Использование нескольких функций агрегации: Для отображения нескольких метрик (например, СЧЁТ / 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 для отображения количества, суммы и процента от общей суммы продаж.
    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)
English version:
=GROUPBY(A2:B503,B2:B503,COUNTA,0,0)
Подсчет уникальных комбинаций текста в двух колонках с помощью GROUPBY и COUNTA.
Подсчет уникальных комбинаций текста в двух колонках с помощью GROUPBY и СЧЁТЗ / COUNTA.

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

Поделиться:

Мы используем cookies для улучшения работы сайта. Продолжая использовать сайт, вы соглашаетесь с политикой конфиденциальности.