Использование пользовательских лямбда-функций в формулах Excel GROUPBY и PIVOTBY

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

Среди последних дополнений к семейству динамических функций массива Excel есть два инструмента агрегации: GROUPBY и PIVOTBY. Хотя они полезны в своей базовой форме, они действительно блистают в паре с пользовательскими функциями Lambda, позволяя пользователям суммировать данные способами, выходящими за рамки того, что предлагают предопределенные Eta lambdas. Это руководство проведет вас через эффективное использование этих расширенных функций.

Что такое лямбда-функция?

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

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

Например, приведенная ниже лямбда добавляет 1 к числу x:

=ЛЯМБДА(х, х + 1)

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

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

Что такое Эта Лямбда?

Эта-лямбда — это сокращенный термин для Эта-редуцированной лямбды, также известной как η-конверсия или η-восстановление.

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

Простой пример

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

Предположим, у вас есть простая функция 𝑓, которая добавляет 1 к числу:

𝑓(х) = х + 1

И еще одна функция g, которая принимает число x, а затем применяет 𝑓 к x:

г(х) = 𝑓(х)

Используя преобразование эта, вы можете увидеть, что g на самом деле просто 𝑓 в маскировке, потому что g делает то же самое, что и 𝑓. Таким образом, уравнение можно упростить до:

г = 𝑓

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

Эта-сокращенные лямбды в Excel

В Microsoft Excel термин «эта-лямбда» обычно относится к более простому синтаксису функций агрегации (таких как SUM, AVERAGE, COUNT и т. д.), используемых в новых функциях динамических массивов, таких как GROUPBY, PIVOTBY, BYROW, BYCOL и других функциях, которые принимают лямбда-выражения.

Например, чтобы сгруппировать данные в диапазоне B2:B30 и суммировать значения в диапазоне C2:C30, полный синтаксис будет следующим:

=ГРУППИРОВАТЬПО(B2:B30, C2:C30, ЛЯМБДА(x, СУММ(x)))

В этой формуле x — это просто фиктивная переменная или заполнитель для значений в каждой строке C2:C30. Функция GROUPBY проходит по каждой строке в этом диапазоне и применяет функцию LAMBDA, которая в свою очередь вызывает SUM.

Хотя полный синтаксис работает отлично, он излишне сложен для такой простой операции. С eta lambda вы можете удалить обертку LAMBDA и вызывать функцию SUM напрямую по ее имени:

=ГРУППИРОВАТЬПО(B2:B30; C2:C30; СУММА)
Функция агрегации Excel с лямбда-выражением

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

Примечание. Синтаксис лямбда-выражения с eta-сокращением возможен только для функций, которые принимают один аргумент, например SUM, MIN, MAX, COUNT и т. д. Его нельзя использовать для функции, которая требует два или более аргументов.

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

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

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

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

Используя набор данных ниже, предположим, что вы хотите сгруппировать данные в столбце B по типу проекта и объединить числа в столбце C, чтобы показать 10% от дохода. Это может быть полезно для расчета налогов, бонусов или других подобных целей.

Чтобы добиться этого, вы можете использовать следующую лямбда-функцию, которая вычисляет 10% от суммы заданного диапазона. Точнее, функция принимает диапазон x в качестве входных данных и возвращает сумму диапазона, умноженную на 10 процентов:

ЛЯМБДА(x, 10%*СУММ(x))

Используйте эту лямбду для аргумента функции GROUPBY:

=ГРУППИРОВАТЬПО(B2:B30, C2:C30, ЛЯМБДА(x, 10%*СУММ(x)))

После ввода формулы, скажем, в E3, Excel отобразит сгруппированные данные с новым столбцом, показывающим 10% дохода для каждого типа проекта, как раз столько, сколько необходимо для вашего анализа:
Формула Excel GROUPBY с лямбда-функцией для выполнения пользовательской агрегации

Пример 2: GROUPBY с лямбда-выражением для возврата строк с пользовательским разделителем

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

Однако, если вы хотите использовать другой разделитель, вы можете создать собственную функцию Lambda с помощью TEXTJOIN и указать нужный символ в ее аргументе разделителя. Например, чтобы сгруппировать данные по именам менеджеров в столбце A и вернуть все проекты, связанные с каждым менеджером, в виде строк, разделенных вертикальной чертой, окруженной пробелами с обеих сторон, вы должны использовать следующую формулу:

=GROUPBY(A3:A30, B3:B30, ЛЯМБДА(x, ТЕКСТОВОЕ СОЕДИНЕНИЕ(” | “, ИСТИНА, x)), 0, 0)

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

Кроме того, вы можете расположить объединенные значения в алфавитном порядке, вложив функцию SORT в TEXTJOIN следующим образом:

=GROUPBY(A3:A30, B3:B30, ЛЯМБДА(x, ТЕКСТОВОЕ СОЕДИНЕНИЕ(” | “, ИСТИНА, СОРТ(x))), 0, 0)

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

Пример 3: GROUPBY с лямбда-выражением для агрегации уникальных значений

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

=GROUPBY(B3:B30, C3:C30, LAMBDA(x, TEXTJOIN(” | “, ИСТИНА, СОРТ(УНИКАЛЬНЫЙ(x)))), 0, 0)

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

Пример 4: PIVOTBY с пользовательской лямбда-функцией

Использование пользовательской лямбды с функцией PIVOTBY аналогично использованию ее с GROUPBY.

Рассмотрим набор данных ниже с отдельными столбцами для регионов (A), наименований продуктов (C) и годовых продаж (D).

Чтобы получить средний годовой объем продаж для каждой категории продукта-региона, вы можете использовать встроенную лямбда-функцию AVERAGE — это будет хорошо работать, поскольку в столбце C перечислены годовые продажи:

=СВОДНОЙ_ОБЪЕМ(C2:C30; A2:A30; D2:D30;СРЗНАЧ)

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

=PIVOTBY(C2:C30, A2:A30, D2:D30, ЛЯМБДА(x, СРЗНАЧ(x)/4))
PIVOTBY с пользовательской лямбда-функцией

Надеюсь, эти примеры дали вам представление о том, насколько мощными и гибкими могут быть новые динамические функции массива Excel в сочетании с пользовательскими лямбда-функциями. Аналогичным образом вы можете агрегировать данные различными способами, используя другие функции, которые принимают лямбда-функции, такие как BYROW, BYCOL и т. д.

Практическая рабочая тетрадь для скачивания

Пользовательская лямбда с GROUPBY и PIVOTBY — примеры (файл .xlsx)

Вас также может заинтересовать:

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *