Функция Excel GROUPBY для группировки строк и агрегирования значений.

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

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

Функция Excel ГРУППА

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

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

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

Синтаксис

Синтаксис функции ГРУППА следующий:

GROUPBY(поля_строки, значения, функция, [field_headers], [total_depth], [sort_order], [filter_array])

Функция может принимать семь аргументов, но обязательными являются только первые три.

• Row_fields (обязательно) — диапазон значений, которые вы хотите сгруппировать.
• Значения (обязательно) — значения для агрегирования.
• Функция (обязательно) – функция для суммирования сгруппированных данных, таких как СУММА, СРЗНАЧ, СЧЁТ, МИН, МАКС и т.д.
• Field_headers [optional] — указывает, есть ли в вашем наборе данных заголовки и хотите ли вы включить их в результаты. Если этот параметр опущен, заголовки не отображаются.
  • 0 – нет заголовков
  • 1 – Да, но не показывать заголовки
  • 2 – Нет заголовков, но генерируем их
  • 3 – Да, и показывать заголовки
• Общая_глубина [optional] – определяет, отображать ли итоги и промежуточные итоги.
  • 0 – нет итогов
  • 1 (по умолчанию) — общие итоги внизу.
  • 2 – Общие итоги и промежуточные итоги внизу.
  • -1 – Общие итоги наверху
  • -2 – Общие итоги и промежуточные итоги вверху
• Порядок сортировки [optional] – сортировка по порядковому номеру столбца. Числа соответствуют столбцам в row_fields слева направо, за которыми следуют столбцы в значениях. Для сортировки по возрастанию используйте положительные числа, для сортировки по убыванию — отрицательные числа. Для сортировки по нескольким полям row_fields можно предоставить одномерный массив чисел. По умолчанию используется сортировка по возрастанию.
• Фильтр_массив [optional] – фильтрация определенных строк. Может быть предоставлено как логическое выражение, которое создает одномерный массив логических значений, соответствующий длине массива row_fields.

Доступность

GROUPBY доступен только в Excel для Microsoft 365 (в настоящее время в программе предварительной оценки: бета-канал).

Примечания по использованию

Чтобы помочь вам быстро освоить функцию GROUPBY и избежать распространенных ошибок, вот несколько полезных советов:

• Динамическое поведение формулы. Формула является динамической и будет обновляться автоматически, если вы внесете изменения в текущий набор данных. Однако если вы добавите новые строки в исходный набор данных, формула не обновится, если вы не добавите дополнительные пустые строки в аргументы row_fields иvalues. Альтернативно вы можете преобразовать набор данных в таблицу Excel. В этом случае формула будет автоматически расширяемой и полностью динамической.
• Заголовки. Если аргумент field_headers не установлен, Excel автоматически определяет, есть ли в вашем наборе данных заголовки, на основе аргумента значений. В частности, если первое значение — это текст, а второе — число, Excel интерпретирует первую строку как заголовки.
• Постоянная длина диапазона. Крайне важно предоставить диапазоны одинаковой длины для аргументов row_fields и значений, чтобы предотвратить ошибку #VALUE! ошибка.
• Результаты автоформатирования. Вывод GROUPBY может быть трудным для интерпретации, особенно если включены общие итоги и промежуточные итоги. Условное форматирование может помочь визуально различать различные уровни агрегирования и сделать данные более читабельными.

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

Базовая формула Excel GROUPBY

Чтобы начать работу с функцией GROUPBY, в этом примере мы сделаем все просто. Предположим, у вас есть набор данных, подобный показанному ниже, где столбец A содержит названия проектов, столбец B — типы проектов и столбец C — доходы. Ваша цель — суммировать доходы по типам проектов. Для этого вы указываете типы проектов (B2:B32) для аргумента row_fields и доходы (C2:C32) для значений. Для функции агрегирования вы выбираете СУММ из списка доступных функций.

Полная формула будет выглядеть так:

=GROUPBY(B2:B32, C2:C32, СУММ)

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

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

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

Как использовать функцию ГРУППА в Excel — примеры формул

Теперь, когда вы знакомы с основами функции GROUPBY, давайте углубимся в несколько более сложных примеров, которые продемонстрируют ее универсальность и мощь. Мы рассмотрим, как выбрать подходящую функцию агрегирования и включить дополнительные аргументы, такие как field_headers для ясности, Total_Deep для глубины анализа и sort_order для оптимального представления данных.

Выберите функцию агрегирования

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

Здесь у вас есть классические итоговые функции, такие как СУММА, СРЗНАЧ, МЕДИАНА, МИН, МАКС, СЧЕТ и т. д.

Кроме того, есть несколько совершенно новых функций, таких как:

• ПРОЦЕНТОФ — вычисляет процент значения по сравнению с общей суммой.
• ARRAYTOTEXT — преобразует массив значений в текстовое представление.

Практическое использование этих двух функций будет продемонстрировано на дальнейших примерах.

И лямбды

Интересно, что все функции в раскрывающемся списке напоминают обычные функции рабочего листа; на самом деле, это лямбды с эта-редуцированными значениями. Такая конструкция позволяет использовать эти функции без скобок и аргументов, что на первый взгляд может показаться немного необычным. Однако такой подход упрощает процесс. Вместо того, чтобы писать полную форму, например LAMBDA(x, SUM(x)), вы можете просто написать SUM.

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

Множественные агрегации

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

Показать заголовки

Чтобы включить заголовки полей в результаты, используйте аргумент field_headers функции GROUPBY.

• Если в вашем наборе данных есть заголовки столбцов, установите для этого аргумента значение 3 (Да, показывать).
• Если в вашем наборе данных нет заголовков, вы можете выбрать 2 (Нет, но сгенерировать). Однако это может быть не очень практично, поскольку сгенерированные заголовки обычно имеют общие метки, такие как «Поле строки 1», «Значение 1» и т. д.

Например, вот как вы можете изменить нашу базовую формулу, включив в нее заголовки:

=GROUPBY(B2:B32, C2:C32, СУММ, 3)

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

Группировать строки на основе нескольких столбцов

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

Например, чтобы сгруппировать строки сначала по типу проекта (столбец B), а затем по статусу (столбец C), вы должны использовать диапазон B2:C32 для аргумента row_fields.

Чтобы узнать, сколько проектов находится в каждой группе, выберите COUNT в качестве аргумента функции. Поскольку функция COUNT подсчитывает только числовые значения, обязательно определите числовой столбец для аргумента значений — столбец «Доход» (D2:D32) отлично подходит.

Вот как выглядит окончательная формула:

=GROUPBY(B2:C32, D2:D32, COUNT)

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

Обратите внимание, что в этом примере мы не показываем заголовки автоматически по формуле. Вместо этого мы вручную ввели соответствующие заголовки столбцов в ячейки F2:H2.

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

Группировка строк на основе несмежных столбцов

В больших наборах данных ключевые столбцы не всегда могут быть соседними. Чтобы включить все нужные столбцы в многоуровневую группировку, вы можете вложить функцию CHOOSECOLS в аргумент row_fields функции GROUPBY.

Вот как это работает: в первый параметр CHOOSECOLS вы включаете весь массив данных (в этом примере A2:D32). В качестве последующих аргументов укажите целые числа, указывающие, какие столбцы включать — в нашем случае это 2 (тип проекта) и 4 (статус):

ВЫБЕРИТЕ ПУНКТЫ(A2:D32, 2, 4)

Вложите приведенную выше формулу в GROUPBY, и вы получите следующий результат:

=GROUPBY(ВЫБЕРИТЕ ЭКОЛОГИ(A2:D32, 2, 4), C2:C32, COUNT)

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

Включить или исключить общий итог и промежуточные итоги

Включение итогов и промежуточных итогов в выходные данные функции GROUPBY контролируется аргументом total_eep. Вот краткое описание того, что делает каждый параметр:

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

Например, если вы хотите отображать общие итоги внизу и промежуточные итоги под каждой категорией данных, установите для Total_Deep значение 2:

=GROUPBY(B2:C32, D2:D32, СУММ, 3, 2)

Кончик. На скриншоте примера промежуточные итоги выделены светло-зеленым цветом для лучшей наглядности. В ваших реальных рабочих листах вы можете автоматически применить предпочитаемое форматирование используя функции условного форматирования Excel.

Сортировка сгруппированных строк

По умолчанию формула GROUPBY сортирует результаты в порядке возрастания от A до Z на основе значений row_fields. При группировке по нескольким столбцам каждый столбец сортируется по возрастанию, начиная с первого (крайнего правого).

Чтобы отсортировать сгруппированные строки по-разному, вы можете настроить порядок с помощью аргумента sort_order:

• Сортировка по возрастанию – используйте положительные номера столбцов.
• Сортировка по убыванию – используйте отрицательные номера столбцов.
• Сортировка по нескольким столбцам — укажите одномерный массив чисел, соответствующих столбцам в row_fields, за которыми следуют столбцы в значениях. Разные столбцы можно сортировать в разном порядке.

Например, чтобы отсортировать результаты по значениям, а не по полям строк, вы должны использовать либо 2 (сортировать от наименьшего к наибольшему), либо -2 (сортировать от наибольшего к наименьшему) для шестого аргумента:

=GROUPBY(B2:B32, C2:C32, СУММ, , , 2)

Чтобы выполнить сортировку нескольких столбцов, сначала по типу проекта (от Z до A), а затем по статусу (от A до Z), используйте массив {-1, 2} в качестве аргумента sort_order:

=GROUPBY(B2:C32, D2:D32, СУММ, , , {-1,2})

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

Отфильтровать определенные строки

Чтобы отфильтровать какие-либо конкретные результаты, вы можете использовать седьмой аргумент с именем filter_array. Этот аргумент принимает одномерный массив логических значений, соответствующий длине массива row_fields, где TRUE указывает строки, которые нужно включить, а FALSE указывает строки, которые нужно исключить.

Например, чтобы исключить Дизайн-проекты из вывода, вы можете использовать логическое выражение «не равно» B2:B32<>“Дизайн”:

=GROUPBY(B2:B32, C2:C32, SUM, , , , B2:B32<>“Проектирование”)

Как видите, наша формула GROUPBY эффективно отфильтровывает строки на основе заданного условия — любая строка, в которой значение в столбце B равно «Проектирование», исключается из сгруппированных результатов.

Объединение нескольких столбцов

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

Объединение соседних столбцов

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

Например, чтобы получить средний доход и прибыль по типу проекта, используйте формулу:

=GROUPBY(B2:B32, D2:E32, СРЕДНЕЕ, 3)

Обратите внимание, что мы используем D2:E32 для аргумента значений, который сообщает функции GROUPBY о необходимости агрегировать значения в столбцах D (Доход) и E (Прибыль).

Суммирование несмежных столбцов

Если вы имеете дело с несмежными столбцами и вам необходимо их агрегировать, функция CHOOSECOLS пригодится, как и для группировки.

Допустим, вы хотите найти среднее значение бюджета (столбец C) и прибыли (столбец E). Чтобы заставить GROUPBY вычислять значения в этих столбцах, вы указываете CHOOSECOLS(C2:E32, 1, 3) в качестве аргумента значений. Это говорит Excel, что столбцы 1 и 3 следует брать из диапазона C2:E32:

=GROUPBY(B2:B32, ВЫБЕРИТЕ ЦЕЛОСТИ(C2:E32, 1, 3), СРЕДНЕЕ, 3)

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

Выполняйте несколько агрегаций одних и тех же данных одновременно

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

Например, чтобы получить сумму, среднее значение и процент от общей суммы дохода и разместить результаты по столбцам, используйте функцию HSTACK:

=GROUPBY(B3:B32, C3:C32, HSTACK(СУММА, СРЕДНЕЕ, ПРОЦЕНТ))

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

=GROUPBY(B3:B32, C3:C32, VSTACK(СУММА, СРЕДНЕЕ, МИН, МАКС),, 0)

Этот метод позволяет вам создать параллельное сравнение различных агрегатов для одних и тех же данных, обеспечивая быстрое многогранное представление вашего набора данных. Такие комплексные снимки имеют неоценимое значение для финансовых отчетов или отчетов о продажах, помогая выявлять тенденции, измерять производительность в соответствии с целями и принимать обоснованные бизнес-решения.

Группировать текстовые значения, разделенные запятыми.

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

Предположим, что из приведенного ниже набора данных вы хотите получить список названий проектов, которые включает в себя каждый тип. Для этого вы используете B3:B23 (типы проектов) для row_fields и A3:A23 (имена проектов) для аргумента значений. В качестве функции вы выбираете МАССИВТОТЕКСТ. Поскольку вам не нужны ни заголовки, ни итоговые значения, следующие два аргумента имеют значение 0:

=GROUPBY(B3:B23, A3:A23, МАССИВТОТЕКСТ, 0, 0)

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

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