Использование пользовательских лямбда-функций в формулах 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; СУММА)
Это не только улучшает читаемость формулы, но и делает ее очень простой и интуитивно понятной в использовании. Фактически, вам не нужно ничего знать о лямбдах — просто выберите нужную функцию из списка:
Примечание. Синтаксис лямбда-выражения с 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% дохода для каждого типа проекта, как раз столько, сколько необходимо для вашего анализа:
Пример 2: GROUPBY с лямбда-выражением для возврата строк с пользовательским разделителем
Среди списка функций, доступных с GROUPBY, есть новая функция ARRAYTOTEXT, которая преобразует массив значений в текстовые строки. По умолчанию значения разделяются запятой и пробелом, как показано в этом примере, демонстрирующем стандартное использование функции.
Однако, если вы хотите использовать другой разделитель, вы можете создать собственную функцию Lambda с помощью TEXTJOIN и указать нужный символ в ее аргументе разделителя. Например, чтобы сгруппировать данные по именам менеджеров в столбце A и вернуть все проекты, связанные с каждым менеджером, в виде строк, разделенных вертикальной чертой, окруженной пробелами с обеих сторон, вы должны использовать следующую формулу:
=GROUPBY(A3:A30, B3:B30, ЛЯМБДА(x, ТЕКСТОВОЕ СОЕДИНЕНИЕ(” | “, ИСТИНА, x)), 0, 0)
В результате вы получите аккуратно организованный список, в котором проекты каждого менеджера сгруппированы и разделены выбранным вами разделителем, что обеспечивает четкое и понятное резюме.
Кроме того, вы можете расположить объединенные значения в алфавитном порядке, вложив функцию 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)
Используя эту формулу, вы получите проекты каждого менеджера, представленные в виде строки, где значения разделены указанным разделителем, перечислены только один раз и отсортированы в алфавитном порядке:
Пример 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))
Надеюсь, эти примеры дали вам представление о том, насколько мощными и гибкими могут быть новые динамические функции массива Excel в сочетании с пользовательскими лямбда-функциями. Аналогичным образом вы можете агрегировать данные различными способами, используя другие функции, которые принимают лямбда-функции, такие как BYROW, BYCOL и т. д.
Практическая рабочая тетрадь для скачивания
Пользовательская лямбда с GROUPBY и PIVOTBY — примеры (файл .xlsx)