Как использовать функцию GROUPBY в Excel

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

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

Синтаксис GROUPBY

Функция GROUPBY имеет восемь аргументов:

=ГРУППИРОВАТЬПО(a,b,c,d,e,f,g,h)

аргументы a в c являются обязательными:

a (поля строк): диапазон (один или несколько столбцов), содержащий значения или категории, по которым должны быть сгруппированы данные.
b (значения): Диапазон (один или несколько столбцов), содержащий значения, которые объединяют данные.
c (функция): Функция, используемая для агрегирования значений аргумента. b.

аргументы d в h являются необязательными, и вы можете узнать о них больше в заключительном разделе этой статьи:

d (заголовки полей): число, указывающее, выбрали ли вы заголовки в аргументах a и bи следует ли их отображать в выходных данных.
e (общая глубина): число, определяющее, должны ли выходные данные отображать итоговые значения.
f (порядок сортировки): число, указывающее, в каком порядке будет упорядочен результат.
g (фильтр массива): ориентированная на массив формула, которая отфильтровывает нежелательную информацию.
h (связь полей): число, которое определяет связи полей, когда в аргументе указано несколько столбцов. a.

GROUPBY в действии: использование только обязательных аргументов

Если вы ошеломлены большим количеством аргументов, которые имеет функция GROUPBY, на этом этапе важно отметить, что функция GROUPBY работает отлично, даже если вы заполняете только аргументы a, bи c. Итак, сначала я покажу вам, как работает функция GROUPBY только с этими тремя аргументами.

Давайте представим, что вы являетесь владельцем сети ресторанов, в которых подают блюда разных кухонь мира, и вы подсчитали общий объем продаж и среднюю оценку клиентов для каждой комбинации кухни и блюда.

Хотя эти цифры полезны, возможно, вас больше интересует, как данные сравниваются по разным категориям. В частности, вы можете захотеть узнать общую сумму денег, которую приносит каждая кухня, и среднюю оценку клиентов для каждого типа блюда.

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

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

Чтобы узнать общие объемы продаж по каждой кухне, в ячейке F2 введите:

=ГРУППИРОВАТЬПО(

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

=GROUPBY(TabFood[Кухня],

Затем, поскольку вы хотите увидеть общий объем продаж для каждой из этих кухонь, выберите ячейки, содержащие эти цифры, и добавьте еще одну запятую:

=GROUPBY(TabFood[Кухня],TabFood[Продажи],

Последний обязательный аргумент — это функция, которая будет использоваться для агрегации данных. В этом случае, поскольку вы хотите узнать общий продаж для каждой кухни, вам нужно вставить функцию СУММ и закрыть скобки:

=GROUPBY(TabFood[Кухня],TabFood[Продажи],SUM)

А также использование простых функций, таких как СУММА и СРЗНАЧ в аргументе c, вы также можете использовать инструмент LAMBDA в Excel для создания функции, отвечающей вашим потребностям.

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

Поскольку значения в столбце G являются финансовыми, выберите данные и щелкните значок «Учет» в группе «Число» вкладки «Главная» на ленте.

Теперь вам нужно узнать среднюю оценку клиентов для каждого типа блюда, и процесс для этого очень похож.

В ячейке I2 введите:

=ГРУППИРОВАТЬПО(

Далее выберите ячейки, содержащие категорию, по которой вы хотите сгруппировать данные. В данном случае это разные блюда. Не забудьте добавить запятую после каждого аргумента, чтобы перейти к следующему.

=GROUPBY(TabFood[Блюдо],

Теперь выберите ячейки, содержащие данные для агрегирования, и добавьте еще одну запятую:

=GROUPBY(TabFood[Блюдо],TabFood[Оценка клиента]

Наконец, поскольку ваша цель в этом случае — выяснить, в среднем оценка клиента для каждого типа блюда, аргумент функции должен быть СРЕДНИМ.

=GROUPBY(TabFood[Блюдо],TabFood[Оценка клиента],СРЕДНЕЕ)

После нажатия Enter Excel усреднит оценки клиентов для каждого типа блюд. Опять же, при отсутствии дополнительных аргументов данные сортируются в алфавитном порядке в соответствии со значениями в левом столбце по умолчанию, а внизу есть удобная итоговая строка.

Поскольку значения в столбце J представляют собой средние значения в десятичной системе счисления, отрегулируйте количество отображаемых десятичных знаков, нажав кнопки «Увеличить десятичную дробь» и «Уменьшить десятичную дробь» в группе «Число» на вкладке «Главная».

Если вы довольны результатом GROUPBY на этом этапе, вы можете прекратить чтение. Однако продолжайте читать, чтобы узнать о дополнительных аргументах GROUPBY.

GROUPBY в действии: использование необязательных аргументов

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

Ниже я расскажу о каждом из необязательных аргументов, чтобы вы могли увидеть, как они повлияют на ваши данные, если вы решите их включить.

Используйте запятую для перехода от одного аргумента к другому. Например, если вы хотите включить четвертый и шестой аргументы, но не пятый, введите [четвертый аргумент],,[шестой аргумент]. Пятый аргумент должен был находиться между двумя запятыми, но поскольку в этом месте ничего нет, Excel знает, что вы намеренно оставили этот аргумент пустым.

Заголовки полей

В приведенных выше примерах я вручную ввел заголовки выходных столбцов, поскольку по умолчанию они не включены в результат. Однако, если вы хотите, чтобы ваши выходные данные включали заголовки столбцов, а также содержащиеся в них данные, используйте аргумент заголовков полей.

Начните с ввода формулы GROUPBY, включая первые три (обязательных) аргумента. В этом случае предположим, что вы хотите сгруппировать кухни по средним оценкам клиентов:

=GROUPBY(A1:A21,D1:D21,СРЕДНЕЕ

Обратите внимание, как строки заголовков включены в выборки. Действительно, при выборе данных для первых двух аргументов вам следует заранее подумать, хотите ли вы, чтобы ваши выходные данные дублировали заголовки в вашей таблице.

Поля строк и значения аргументов должны быть одинакового размера. Если вы выбираете заголовки в одном, вы должны выбрать заголовки в другом.

Наконец, введите запятую, чтобы перейти к аргументу заголовков полей, и введите:

1 если вы выбрали заголовки в первых двух аргументах, но не хотите, чтобы они отображались в результате,
2 если вы не выбрали заголовки в первых двух аргументах, но хотите, чтобы Excel сгенерировал общие заголовки в результате, или
3 если вы выбрали заголовки в первых двух аргументах и хотите, чтобы Excel отобразил их в результате.

Вот результат, когда я печатаю:

=GROUPBY(A1:A21,D1:D21,СРЕДНЕЕ,3)

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

Преимущество включения заголовков полей	Недостаток включения заголовков полей
Если вы измените заголовки в исходной таблице, выходные заголовки примут эти изменения.	Вы не можете изменить заголовки выходных данных, если хотите сделать их более конкретными, чем исходные заголовки таблицы.

Общая глубина

Аргумент общей глубины позволяет вам решить, хотите ли вы, чтобы результат отображал общие итоги, и если да, то должны ли они располагаться вверху или внизу ваших данных. Этот аргумент также позволяет вам выбрать, показывать ли промежуточные итоги.

Для аргумента общей глубины введите:

0 если вы не хотите, чтобы отображались какие-либо итоги или промежуточные итоги,
1 если вы хотите, чтобы в нижней части результата отображалась только общая сумма,
2 если вы хотите, чтобы промежуточные итоги отображались в нижней части каждой категории результатов, а общая сумма — в нижней части общего результата,
-1 если вы хотите, чтобы в верхней части результата отображалась только общая сумма, или
-2 если вы хотите, чтобы промежуточные итоги отображались в верхней части каждой категории результатов, а общий итог — в верхней части общего результата.

Параметры отображения промежуточных итогов (2 и -2) работают только в том случае, если аргумент полей строк содержит более одного столбца данных (другими словами, подполей).

В этом примере я набрал:

=ГРУППИРОВАТЬПО(A1:B21,C1:C21,СУММ,,2)

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

Сортировку

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

Число, которое вы вводите для этого аргумента, представляет столбец в результате. Например, если вы вводите 1, это отсортирует результат по первому столбцу в порядке возрастания или алфавита. С другой стороны, ввод -1 сортирует результат по первому столбцу в порядке убывания или в обратном алфавитном порядке.

В этом примере я набрал:

=ГРУППИРОВАТЬПО(A1:A21,C1:C21,СУММ,,,-2)

который сортирует второй столбец (продажи) в порядке убывания.

Фильтр массива

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

В этом примере годы в ячейках A2, A8 и A17 прерывают результат функции GROUPBY.

Я могу использовать аргумент массива фильтра, чтобы указать Excel игнорировать все ячейки в столбце A, содержащие числа, с помощью функции ISNUMBER:

=GROUPBY(A1:A24,C1:C24,СУММ,,,,ЕСТЬЧИСЛО(A1:A24)=ЛОЖЬ)

Полевое отношение

Наконец, аргумент отношения полей управляет тем, как группируются данные, когда аргумент полей строк ссылается более чем на один столбец.

В этом примере, когда аргумент отношения поля содержит 0 (что является значением по умолчанию, если аргумент опущен), GROUPBY возвращает иерархическую таблицу результатов, в которой каждый столбец индивидуально представлен отдельными строками данных.

=GROUPBY(A1:B21,C1:C21,SUM,,,3,,0)

С другой стороны, когда аргумент отношения поля содержит 1, GROUPBY возвращает таблицу результатов, которая игнорирует иерархию и сортирует каждый столбец независимо. Другими словами, категории не являются вложенными, поэтому вы также не можете включать промежуточные итоги в результат, когда выбираете этот параметр связи полей.

=GROUPBY(A1:B21,C1:C21,SUM,,,3,,1)

Помимо использования SUM и AVERAGE в аргументе функции GROUPBY, вы можете использовать функцию PERENTOF, которая преобразует данные в проценты, чтобы показать долю, которую подмножество составляет от всего набора данных.