До того, как LAMBDA была представлена в Excel, вам нужно было знать, как использовать VBA или макросы, чтобы создать собственную функцию. Однако сегодня инструмент LAMBDA в Excel позволяет вам делать это, используя знакомый язык и синтаксис Excel. В этом руководстве я покажу вам, как это сделать.
Если вы впервые пытаетесь использовать LAMBDA, я настоятельно рекомендую вам следовать разделам этой статьи по порядку. Понимание синтаксиса инструмента и простого примера для начала значительно упростит использование LAMBDA в реальной жизни.
Функция LAMBDA доступна в Excel для Microsoft 365 (Windows и Mac), Excel 2024 (Windows и Mac) и Excel for the Web. К сожалению, если вы используете Excel 2019 или более раннюю версию, вы не сможете создавать собственные функции с помощью методов, описанных в этом руководстве.
Синтаксис LAMBDA
Для создания собственной функции в Excel с использованием LAMBDA необходимо ввести два аргумента:
=ЛАМБДА(x,y)
в котором
- x являются входными переменными (до 253), и
- y это расчет.
Входные переменные (аргумент x) не может конфликтовать со ссылками на ячейки или содержать точки, а вычисление (аргумент y) всегда является последним аргументом в функции LAMBDA.
LAMBDA в простом примере
Прежде чем мы рассмотрим, как можно использовать LAMBDA в Excel в реальных условиях, я покажу вам, как это работает, на простом примере.
Откройте пустую книгу Excel и в ячейке A1 листа 1 введите:
=ЛЯМБДА(а,b,a*b)
но пока не нажимайте Enter.
Возвращаясь к синтаксису в разделе выше, «а, б» — это часть формулы, в которой мы определяем и называем переменные, а «а * б«—последний аргумент в формуле — это вычисление, которое мы хотим, чтобы функция выполнила, когда мы присваиваем значения этим переменным. Так, например, если a и b равны 4 и 6 соответственно, мы ожидаем, что расчет вернет 24.
Теперь нажмите Enter. Сначала это приведет к ошибке #CALC!, поскольку вы еще не присвоили переменным никаких значений.
Однако вы можете проверить, что созданная вами LAMBDA будет работать при добавлении значений. Для этого дважды щелкните ячейку, содержащую функцию LAMBDA, и введите пример переменных в скобках в конце существующей формулы. Например:
=ЛЯМБДА(а,b,a*b)(4,6)
Теперь, когда вы нажмете Enter, в ячейке отобразится число 24, подтверждая, что созданная вами LAMBDA-формула готова к назначению значений переменным.
В этот момент вы можете подумать, что ввод текста = 4 * 6 в ячейку и нажать Enter было бы намного проще, и вы правы. Однако вся цель использования LAMBDA — дать вашему расчету имя и применять его к другим значениям, когда это необходимо, что становится особенно полезным, когда ваш расчет более сложный. Более того, если вам позже понадобится внести какие-либо изменения в расчет, внесение одной незначительной корректировки в вашу функцию в ее корне повлияет на все соответствующие расчеты, сэкономив вам массу времени.
Итак, чтобы это произошло, дважды щелкните ячейку, содержащую вашу функцию LAMBDA, выберите исходные переменные LAMBDA и созданное вами вычисление (до конца первого набора скобок) и нажмите Ctrl+C, чтобы скопировать эту формулу.
После того, как вы скопировали формулу, нажмите Esc. Затем на вкладке «Формулы» на ленте нажмите «Определить имя».
В появившемся диалоговом окне «Новое имя» вы сможете официально определить новую функцию, которую сможете использовать в любом месте вашей рабочей книги.
Вот что делает каждое из полей в этом диалоговом окне и что вам нужно ввести:
|
Поиск |
Что делает это поле |
Что тебе необходимо сделать |
|---|---|---|
|
Имя |
Здесь вы даете имя своей новой функции. |
Введите запоминающееся имя, например ПРОСТАЯЛЯМБДА. |
|
Объем |
Выбор в этом раскрывающемся меню определяет, где вы сможете использовать новую функцию. |
Выберите «Рабочая тетрадь». |
|
КОММЕНТАРИЙ |
Когда вы начнете использовать новую функцию, введенный вами текст в поле «Комментарий» отобразится в виде подсказки. |
Введите краткое описание того, что делает функция. |
|
Относится к |
Данные в этом поле используются Excel для работы функции. |
Удалите все существующие данные и нажмите Ctrl+V, чтобы вставить скопированную ранее формулу LAMBDA. |
Вот как должно выглядеть диалоговое окно «Новое имя» после заполнения всех полей.
Нажав «ОК», вы будете готовы протестировать эту новую функцию в другом месте вашей рабочей книги.
Очистите содержимое ячейки A1, выбрав ячейку и нажав Delete. Затем в той же ячейке введите знак равенства (=) и начните вводить имя, которое вы только что дали своей новой функции. Когда вы увидите ее в списке, нажимайте клавишу со стрелкой вниз, пока ваша функция не будет выделена. Обратите внимание, что комментарий, который вы добавили в диалоговом окне «Новое имя», отображается как подсказка.
Затем нажмите Tab, чтобы выбрать и активировать эту функцию. Это заставит Excel завершить имя функции и открыть скобки, готовые для добавления значений, представляющих ваши переменные.
В моем примере, где у меня есть две переменные, я введу:
=ПРОСТАЯМБДА(9,6)
и нажмите Enter. Не забудьте закрыть скобки!
И вуаля — моя новая функция успешно умножает 9 на 6 и возвращает 54.
Наконец, вместо ввода сырых данных в качестве переменных попробуйте ввести ссылки на ячейки. Например, введите:
=ПРОСТАЯМБДА(A1,A2)
в ячейку B1 и нажатие Enter умножает значение в ячейке A1 на значение в ячейке A2.
LAMBDA в реальном примере
В Великобритании НДС составляет 20%. Предположим, вы хотите создать функцию LAMBDA, которая добавляет это значение ко всем расходам в вашей рабочей книге, чтобы узнать полную стоимость с учетом НДС.
Начните с создания расчета в первой ячейке AddVAT:
= B2 * 1.2
и нажав Enter.
Если ваши данные находятся в отформатированной таблице Excel, другие ячейки в этом столбце также будут использовать тот же расчет. Пока что проигнорируйте другие значения и сосредоточьтесь на ячейке C2.
Теперь дважды щелкните ячейку C2, добавьте функцию LAMBDA, заключите формулу в скобки, дайте переменной имя (в этом случае вы можете использовать стоят) и замените ссылку на ячейку этой переменной в расчете:
=ЛЯМБДА(стоимость,стоимость*1.2)
Как и в простом примере в разделе выше, эта формула сама по себе вернет ошибку #CALC! при нажатии Enter, поскольку вы еще не присвоили никаких значений переменной стоимости.
Однако вы можете проверить вычисление LAMBDA в ячейке C2, добавив временную переменную в виде ссылки на ячейку в скобках:
=ЛЯМБДА(стоимость,стоимость*1.2)(B2)
Когда вы нажмете Enter, вы увидите, что значение в ячейке B2 и все остальные значения в столбце B успешно умножаются на 1.2, создавая вычисление +20%, которое мы собираемся автоматизировать.
Далее, чтобы вы могли использовать это вычисление LAMBDA в любом месте вашей рабочей книги, скопируйте формулу LAMBDA (все до конца первых скобок) и нажмите «Определить имя» на вкладке Формулы.
В моем примере Excel автоматически заполнил поле Имя в диалоговом окне заголовком столбца в моей таблице. После того, как вы убедились, что вас устраивает это имя (или выбрали замену), убедитесь, что в поле Область действия выбрано «Workbook», добавьте комментарий, который кратко описывает функцию, и нажмите Ctrl+V в поле Ссылается на, чтобы вставить функцию LAMBDA, которую вы только что скопировали. Затем нажмите «ОК».
Теперь удалите все исходные данные в столбце C и введите новую функцию AddVAT в ячейку C2:
=ДобавитьНДС(B2)
Вместо ввод прямые ссылки на ячейки (например, B2) в вашей формуле, если вы нажмите на Ячейка B2, Excel вставит имя столбца в формулу для создания структурированной ссылки. В результате, если вы расширяете таблицу, чтобы добавить дополнительные строки внизу, Excel автоматически расширит вашу новую функцию на эти добавленные данные.
Когда вы нажмете Enter, Excel выполнит это новое вычисление, и если вы используете отформатированную таблицу Excel, он применит вычисление к оставшимся ячейкам в столбце C.
Предположим, что через несколько лет НДС в Великобритании снизится до 15%. Преимущество использования LAMBDA в том, что вам не придется проходить все ваши расчеты и вносить в них поправки вручную. Вместо этого вам нужно будет только скорректировать функцию в ее источнике.
Для этого нажмите «Диспетчер имен» на вкладке «Формулы» на ленте, найдите и выберите функцию LAMBDA, которую вы хотите настроить (в данном случае это функция «AddVAT»), и нажмите «Изменить».
Теперь в поле Refers To измените «1.2» на «1.15», чтобы отразить снижение НДС. Также взгляните на поле Comments, чтобы увидеть, нужно ли вам изменить описание. Затем нажмите «OK».
Наконец, закройте диалоговое окно Name Manager, которое вы открыли ранее, и все существующие вычисления, которые использовали вашу функцию AddVAT, обновятся в соответствии с вашими корректировками вычислений. В моем примере значение в C2 уменьшилось с £391.20 до £374.90.
Что следует помнить при использовании LAMBDA в Excel
Прежде чем приступить к использованию LAMBDA для создания собственных функций в электронных таблицах, вот несколько ключевых моментов, которые вам следует знать:
- Функции LAMBDA можно использовать в любом месте рабочей книги, в которой они были созданы, но вам придется создать их заново, если вы захотите использовать их в других рабочих книгах.
- Имя, которое вы даете LAMBDA-функции, должно быть уникальным. Другими словами, вы не можете использовать имя существующей функции, и каждый раз, когда вы создаете другую LAMBDA-функцию в той же рабочей книге, она должна иметь имя, отличное от тех, которые вы уже создали.
- Имена, назначаемые входным переменным, не должны конфликтовать со ссылками на ячейки или содержать точки, а если ввести более 253 переменных, Excel вернет ошибку #ЗНАЧЕНИЕ.
Прелесть функции LAMBDA в Excel в том, что вы можете использовать ее любой существующая функция для генерации очень специфического вычисления. Другими словами, функция LAMBDA, добавленная в Excel, делает программу Тьюринг-полной, что означает, что люди, использующие Excel, могут выполнять любые возможные вычисления. Поэтому, как только вы попрактикуетесь в создании функций LAMBDA для некоторых простых вычислений, попробуйте поэкспериментировать с более сложными.
