是什么让Excel的Lambda函数如此出色？

hercules028 2023-03-12 发布于四川

展开全文

excelperfect

标签：Excel新函数，LAMBDA函数

Microsoft于2020年12月宣布Lambda函数为Excel的新功能。Lambda函数是定义自定义函数的一种令人兴奋的方法，只需依赖Excel公式，无需使用VBA或编写外接程序。在这里，将看到Lambda函数是如何工作的，以及它们为什么如此出色，此外Lambda函数当前的局限性以及如何解决其中的一些问题。

为什么需要Lambda函数？

假设有一个Excel工作簿，其中列出了要转换为摄氏度的华氏温度和开氏温度，你可能会编写下图1所示的公式：

图1

这种方式有两个问题：

1.如果不是因为单元格C1中的标题，你不一定知道公式在做什么，这使得检查和发现错误变得更加困难。

2.如果在公式中出错，假设在华氏转换中使用9/5而不是5/9，则需要在整个工作簿中搜索所有公式，并确保每个公式都已得到更正。

新的Lambda函数解决了这两个问题。

创建第一个Lambda函数

让我们从定义和使用Lambda函数开始，这样在下一节中进行更详细的解释之前，我们将了解它们是如何工作的。

在Excel中，单击“公式”选项卡“定义的名称”组中的“名称管理器”，然后单击“新建”，填写以下值：

名称：ToCelsius

引用位置：=LAMBDA(degrees,source,if(source='华氏',(degrees-32)*(5/9),IF(source='开氏',degrees-273.15,NA())))

如下图2所示。

图2

此时，就可以在工作簿中使用这个新函数了，如下图3所示。

图3

这很容易理解，不是吗？并且，你可以在一个地方（通过名称管理器）对公式进行更改，在其它地方都会自动修复。

Lambda函数如何工作？

下面是Lambda函数语法：

=LAMBDA(parameter_or_calculation,…)

一般来说，前面的参数是最后自定义函数的参数，在上面的例子中，使用了两个参数：degrees和source。最后一个参数是实际的自定义函数，能够引用显式参数名称而不是单元格引用，这使得公式非常容易理解。在上面的例子中，华氏度转换：

IF(B2=”华氏”,(A2-32)*(5/9)

在Lambda函数中是：

if(source='华氏',(degrees-32)*(5/9)

如果你喜欢，那么可以像任何其他公式一样，直接在Excel单元格中尝试Lambda函数，但必须在同一个单元格中调用它，方法是在末尾加上括号和参数，否则，会得到#CALC！错误。如下所示：

=LAMBDA(degrees,source,IF(source='华氏',(degrees-32)*(5/9),IF(source='开氏',degrees-273.15,NA())))(20,'华氏')

结果如下图4所示。

图4

这可能有助于测试Lambda公式。

为什么Lambda函数如此出色

Lambda函数的优点如下：

1.用户无需学习VBA等其他语言即可编写自定义函数。

2.这意味着可以定义自定义函数，而无需将Excel工作簿保存为启用宏的xlsm文件，无需单击“启用宏”按钮，这将使操作变得更加轻松。

3.使用Lambda函数意味着只需在一个地方更改公式，并且在使用它的任何地方都会作相应更改。这是一个巨大的改进，可以使你的工作簿不易出错。

4.可以为自定义函数使用简短的描述性名称，而不是长而难理解的公式，这使你的工作簿更容易理解。

Lambda函数的局限

Lambda函数当前的局限性和缺点如下：

1.显而易见，合作的每个人都需要最新版本的Microsoft Excel才能访问Lambda函数。

2.Lambda函数是隐藏的：用户可能不会立即明白为什么两个看似相同的工作簿可以产生不同的值。如果在一个工作簿中定义ToCelsius公式时出错，而在另一个工作簿正确定义，则可能会发生这种情况。

3.Lambda函数不能在不同的工作簿中重用。

4.Lambda函数的所有参数都是可选的，如果只想在包含10个参数的函数中提供第7个参数：=MyFunction（，，，，“some argument”，，，），则会出现问题。如果Lambda函数可以接受与VBA类似的命名参数，则会有所帮助，因此可以改为=MyFunction（some_parameter:=“some argument”），而不必依赖参数的位置。

5. 在名称管理器中键入公式非常困难。尽管我们的ToCelsius函数非常简单，但即使只是查找一个拼写错误也只会很尴尬。

跨工作簿管理Lambda函数

安装xlwings包，在Python中创建文件并将其命名为lambda_report.py：

# lambda_report.pyfrom pathlib import Pathimport xlwings as xw# 修改为实际路径DIRECTORY = r'C:\Users\lambda'app = xw.App(visible=False)for path in Path(DIRECTORY).rglob('[!~$]*.xls*'): book = app.books.open(path) print(f'------ {path} ------') for name in book.names: refers_to = name.refers_to.replace('_xlfn.', '').replace('_xlpm.', '') lambda_functions = [] if refers_to.lower().startswith('=lambda'): lambda_functions.append(f'{name.name}: {refers_to}') if lambda_functions: for func in lambda_functions: print(func) print() book.close()app.quit()

在命令行终端中输入：

Python lambda_report.py

这将打印出DIRECTORY目录（包括子目录）中所有Excel工作簿中所有的Lambda函数。

如果需要在一堆工作簿中添加或更正一些Lambda函数，那么创建一个名为add_lambdas.py的新Python文件：

# add_lambdas.pyfrom pathlib import Pathimport xlwings as xw# 修改为实际路径DIRECTORY = r'C:\Users\lambda'LAMBDAS = ( ('Hypotenuse', '=LAMBDA(a, b,SQRT((a^2+b^2)))'), ('CountWords', '=LAMBDA(text,LEN(TRIM(text)) - LEN(SUBSTITUTE(TRIM(text), ' ', '')) +1)') )app = xw.App(visible=False)for path in Path(DIRECTORY).rglob('[!~$]*.xls*'): print(f'Adding Lambda Functions to:{path}') book = app.books.open(path) for func in LAMBDAS:   name, refers_to = func[0], func[1]   if name in [n.name for n in book.names]:     book.names[func[0]].delete()     book.names.add(name=name,refers_to=refers_to) book.save() book.close() app.quit()