Быстрые ссылки
Раньше для создания собственных функций в Excel требовались знания VBA или макросов. Теперь с помощью инструмента LAMBDA в Excel вы можете создавать функции, используя привычный язык и синтаксис Excel. В этом руководстве я расскажу, как это сделать.
Если вы впервые пробуете LAMBDA, настоятельно рекомендую следовать разделам статьи в том порядке, в котором они расположены. Ознакомившись с синтаксисом инструмента и простым примером, вам будет проще применять LAMBDA на практике.
Функция LAMBDA доступна в Excel для Microsoft 365 (Windows и Mac), Excel 2024 (Windows и Mac) и Excel для веба. К сожалению, если у вас версия Excel 2019 или более ранняя, вы не сможете создавать собственные функции с помощью методов, описанных в этом руководстве.
Синтаксис LAMBDA
Для создания функции в Excel с использованием LAMBDA необходимо задать два аргумента:
где
Входные переменные (аргумент x) не должны совпадать с ссылками на ячейки и не могут содержать точки, а вычисление (аргумент y) всегда будет последним аргументом в функции LAMBDA.
LAMBDA на простом примере
Перед тем как приступить к практическому применению LAMBDA в Excel, давайте разберем его на базовом примере.
Откройте новый файл Excel и введите в ячейку A1 следующее:
только не нажимайте Enter.
Согласно синтаксису из предыдущего раздела, "a,b" — это часть формулы, где мы определяем и именуем переменные, а "a*b" — это финальный аргумент формулы, который будет выполнен при присвоении значений переменным. Например, если a и b равны 4 и 6 соответственно, мы ожидаем получить результат 24.
Теперь нажмите Enter. В начале вы получите ошибку #CALC!, так как еще не назначили значения для переменных.
Как исправить распространенные ошибки формул в Microsoft Excel
Узнайте, что значит эта ошибка и как её исправить.
Тем не менее, вы можете проверить, будет ли ваша LAMBDA работать, когда добавите значения. Для этого дважды щелкните на ячейку с функцией LAMBDA и введите пример переменных в скобках в конце уже существующей формулы. Например:
Теперь, когда вы нажмете Enter, в ячейке появится 24, что подтверждает, что формула LAMBDA готова к присвоению значений переменным.
На данном этапе у вас может возникнуть мысль, что проще просто ввести =4*6 в ячейку и нажать Enter. Вы правы. Однако основная цель использования LAMBDA заключается в том, чтобы присвоить название вашему расчету и использовать его с другими значениями по мере необходимости, что особенно полезно, когда расчеты становятся более сложными. Более того, если вам когда-либо потребуется изменить расчет, небольшое изменение в основной функции повлияет на все соответствующие вычисления, что сэкономит время.
Итак, для этого дважды щелкните на ячейку с вашей функцией LAMBDA, выделите оригинальные переменные и расчет LAMBDA (до конца первой скобки) и нажмите Ctrl C, чтобы скопировать эту формулу.
После того как вы скопировали формулу, нажмите Esc. Затем на вкладке "Формулы" в ленте нажмите "Определить имя".
В появившемся диалоговом окне "Новое имя" вы сможете формально определить новую функцию, которую сможете использовать в любом месте вашей книги.
Вот что делают каждое из полей в этом диалоговом окне и что вам нужно будет ввести:
Поле
Что делает это поле
Что вам нужно сделать
Имя
Здесь вы задаете новое имя для вашей функции.
Введите запоминающееся имя, например SIMPLELAMBDA.
Область видимости
Значение, которое вы выберете в этом выпадающем меню, определяет, где вы сможете использовать свою новую функцию.
Выберите "Книга".
Комментарий
Когда вы будете использовать новую функцию, введенное вами в поле Комментарий появится в виде подсказки.
Введите краткое описание того, что делает функция.
Ссылается на
Данные в этом поле используются Excel для выполнения функции.
Удалите любые существующие данные и нажмите Ctrl V, чтобы вставить ранее скопированную формулу LAMBDA.
Вот как должно выглядеть ваше диалоговое окно "Новое имя" после заполнения всех полей.
Когда вы нажмете "OK", вы сможете протестировать эту новую функцию в других местах вашей книги.
Очистите содержимое ячейки A1, выбрав ее и нажав Delete. Затем в той же ячейке введите знак равенства (=) и начните вводить название, которое вы только что присвоили вашей новой функции. Когда вы увидите его в списке, нажмите клавишу стрелки вниз, чтобы выделить вашу функцию. Обратите внимание, что комментарий, добавленный в диалоговом окне "Новое имя", появится как подсказка.
Затем нажмите Tab, чтобы выбрать и активировать эту функцию. Это позволит Excel завершить имя функции и открыть набор скобок для ввода значений, представляющих ваши переменные.
В моем примере, где у меня есть две переменные, я введу:
и нажму Enter. Не забудьте закрыть скобки!
И вот — моя новая функция успешно умножает 9 на 6, возвращая 54.
Наконец, вместо того чтобы вводить фиксированные данные в качестве переменных, попробуйте использовать ссылки на ячейки. Например, введя:
в ячейку B1 и нажав Enter, вы умножите значение в ячейке A1 на значение в ячейке A2.
LAMBDA в реальном примере
В России НДС составляет 20%. Допустим, вы хотите создать функцию LAMBDA, которая добавляет это значение ко всем затратам в вашей книге, чтобы вы знали полную стоимость с НДС.
Начните с создания расчетов в первой ячейке "Добавить НДС":
и нажмите Enter.
Если ваши данные находятся в отформатированной таблице Excel, другие ячейки в этом столбце автоматически примут такой же расчет. Пока что игнорируйте остальные значения и сосредоточьтесь на ячейке C2.
Теперь дважды щелкните на ячейку C2, добавьте функцию LAMBDA, заключите формулу в скобки, дайте вашей переменной имя (например, стоимость) и замените ссылку на ячейку на эту переменную в расчетах:
Как и в простом примере из предыдущего раздела, эта формула вернет ошибку #CALC!, когда вы нажмете Enter, так как вы еще не назначили значения для переменной стоимость.
Тем не менее, вы можете протестировать расчеты LAMBDA в ячейке C2, добавив временную переменную в виде ссылки на ячейку в скобках:
Когда вы нажмете Enter, вы увидите, что значение в ячейке B2 и все другие значения в столбце B успешно умножаются на 1,2, создавая расчет на 20%, который мы собираемся автоматизировать.
Далее, чтобы использовать этот расчет LAMBDA где угодно в вашей книге, скопируйте формулу LAMBDA (всё вплоть до конца первой скобки) и выберите "Определить имя" на вкладке "Формулы".
В моем случае Excel автоматически заполнил поле Имя в диалоговом окне заголовком столбца в моей таблице. Убедитесь, что вас устраивает это имя (или выберите другое), выберите "Книга" в поле Область видимости, добавьте комментарий, который кратко описывает функцию, и нажмите Ctrl V в поле Ссылается на, чтобы вставить скопированную функцию LAMBDA. Затем нажмите "OK".
Теперь удалите все исходные данные в столбце C и введите вашу новую функцию Добавить НДС в ячейку C2:
Вместо того чтобы вводить прямые ссылки на ячейки (такие как B2) в вашу формулу, если вы нажмёте на ячейку B2, Excel вставит имя столбца в формулу, создавая структурированную ссылку. Таким образом, если вы добавите новые строки в вашу таблицу, Excel автоматически распространит вашу новую функцию на эти данные.
Когда вы нажмете Enter, Excel проведет новый расчет, и если вы используете отформатированную таблицу, он применит вычисление к оставшимся ячейкам в столбце C.
Предположим, что через несколько лет НДС в России снизится до 15%. Преимущество использования LAMBDA заключается в том, что вам не придется просматривать все ваши расчеты и вносить изменения вручную. Вам нужно будет просто актуализировать функцию в её исходном коде.
Для этого нажмите "Диспетчер имен" на вкладке "Формулы", найдите и выберите функцию LAMBDA, которую хотите изменить (в нашем случае это функция "Добавить НДС"), и нажмите "Изменить".
Теперь в поле Ссылается на замените "1.2" на "1.15", чтобы отразить снижение НДС. Также посмотрите на поле Комментарий, нужно ли его обновить. Затем нажмите "OK".
Наконец, закройте диалоговое окно Диспетчер имен, и все ваши существующие вычисления, использующие функцию Добавить НДС, обновятся в соответствии с внесенными вами изменениями. В моем случае значение в C2 снизилось с 391,20 ₽ до 374,90 ₽.
Что помнить при использовании LAMBDA в Excel
Перед тем как начать использовать LAMBDA для создания собственных функций в ваших таблицах, вот несколько важных моментов, которые стоит учесть:
Очарование функции LAMBDA в Excel заключается в том, что вы можете использовать любую существующую функцию для создания очень специфических вычислений. Другими словами, функция LAMBDA, добавленная в Excel, делает программу Тьюринговым полным, позволяя пользователям Excel выполнять любые возможные вычисления. Поэтому, когда вы освоите создание функций LAMBDA для простых расчетов, обязательно экспериментируйте с более сложными вариантами.
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Вы также можете читать наши материалы в:
- Telegram: https://t.me/gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru