学习LAMBDA函数：将Excel公式转换为自定义函数（下）

标签：LAMBDA函数

引言：本文学习整理自microsoft.com，LAMBDA的真正的解决了Excel公式存在的先天不足，让Excel公式真正的强大起来了。

上一篇文章：学习LAMBDA函数：将Excel公式转换为自定义函数（上）

Excel新增的LAMBDA函数彻底改变了在Excel中构建公式的方式。Excel公式是世界上使用最广泛的编程语言，但编程中缺少一个更基本的原则，那就是使用公式语言定义自己的可重用函数的能力。

=LAMBDA

简单地说，LAMBDA允许使用Excel的公式语言定义自己的自定义函数。Excel已经允许定义自定义函数，但只能通过使用完全不同的语言（例如JavaScript）编写它们。相反，LAMBDA允许使用Excel自己的公式语言定义自定义函数。而且，一个函数可以调用另一个函数，因此可以部署单个函数调用的功能没有限制。

可重用自定义函数

使用LAMBDA，可以接受在Excel中构建的任何公式，并将其包装在LAMBDA函数中，并为其命名（如“MYFUNCTION”）。然后在工作表的任何地方，都可以引用MYFUNCTION，在整个工作表中重新使用该自定义功能。

递归

可重用函数是利用LAMBDA的充分理由，此外还可以执行递归。例如，如果创建名为MYFUNCTION的LAMBDA，则可以在MYFUNCTION的定义中调用MYFUNCTION。这是以前只有在Excel中通过脚本（如VBA/JavaScript）才能实现的。下面将展示一个示例，说明如何利用它来构建以前不需要编写脚本就无法实现的东西。

可重用自定义函数

在Excel中使用公式的一个更具挑战性的部分是，经常会得到相当复杂的公式，这些公式在工作表中被多次重复使用（通常只需复制/粘贴）。这会让其他人很难阅读和理解正在发生的事情，更容易出错，并且很难发现和修复错误。使用LAMBDA，可以重复使用和可组合。为计划多次使用的任何逻辑段创建库，提供了方便并降低了出错的风险。

Station IDs

例如，假设有一个站点ID列表，其中州编码在ID中，想取出该值，如下图1所示。

图1

使用Excel函数有很多方法来实现，下面是其中的一个公式：

=LEFT(RIGHT(B3,LEN(B3)-FIND("-",B3)),FIND("-",RIGHT(B3,LEN(B3)-FIND("-",B3)))-1)

这种方式有两个挑战：

1.错误-如果在逻辑中发现需要修复的错误，必须返回并在使用它的每个地方更新它，这样可能会漏掉一些。此外，每当复杂的公式反复重复，而不是只定义一次然后引用时，就会增加错误风险。例如，如果有一些站点ID看起来像“105532-872332-WA-73”，那么上面的公式将无法使用这些站点ID。如果我发现这个错误并想修复它，那么需要返回到使用该逻辑的每个单元格并更新它。

2.可组合性/可读性-如果不是原作者，很难知道这个公式的意图是什么，也很难将此逻辑与其他逻辑结合使用，例如如果想获取站点ID并根据计算的位置进行查找。

使用LAMBDA，可以创建一个名为GETLOCATION的函数，并将公式逻辑放在该函数的定义中。

=GETLOCATION

=LAMBDA(stationID, LEFT(RIGHT(stationID,LEN(stationID)-FIND("-",stationID)),FIND("-",RIGHT(stationID,LEN(stationID)-FIND("-",stationID)))-1))

注意，我指定了函数将接受的参数（在本例中为stationID）和函数的逻辑。现在，在电子表格中，可以像其他Excel函数一样，简单地将GETLOCATION编写为公式并引用包含stationID的单元格。如果注意到有错误，会在一个地方修复它，而使用该函数的任何地方都会被修复。

图2

另一个额外的好处是，现在可以用额外的逻辑编写该函数。例如，如果有每个地点的税率表，可以编写一个简单的公式，根据stationID返回税率。

=XLOOKUP(GETLOCATION(B3), table1[locations], table1[tax])

关于如何使用此功能构建一组丰富的函数库、使工作表更易于理解、更不容易出错等，还有很多要深入研究的内容。这些函数甚至可以将数据类型作为参数。

递归

Excel公式中缺失的一个重要部分是循环的能力，以动态定义的间隔在一组逻辑上重复。有一些方法可以手动配置Excel重新计算的时间间隔，以在一定程度上模拟这种情况，但这不是公式语言固有的。随着引入LAMBDA，情况发生了变化。

举一个例子，虽然有点做作，但这是用来说明的一种简单方式。

假设有一组字符串，想指定应该从这些字符串中动态删除哪些字符，如下图3所示。

图3

因为指定的字符集不是静态的，所以确实没有任何好的方法来实现这一点。如果知道它总是一组固定的字符，可以做大量的嵌套逻辑，但这将非常复杂，而且容易出错。更不用说，如果要删除的字符数大于所考虑的数量，将会失败。

使用LAMBDA，可以创建一个名为REPLACECHARS的函数，该函数引用自身，允许遍历要删除的字符列表：

=REPLACECHARS

=LAMBDA(textString,illegalChars,

IF(illegalChars="",textString,

REPLACECHARS(

SUBSTITUTE(textString, LEFT(illegalChars, 1), ""),

RIGHT(illegalChars, LEN(illegalChars)-1)

)))

注意，在REPLACECHARS的定义中，有对REPLACECHARS自身的引用。IF语句表示，如果没有更多想移除的字符，则返回输入textString，否则删除想移除字符中最左边的每个字符。递归开始，请求使用更新的字符串再次调用REPLACECHARS，并调用其余的想移除字符。这意味着它将一直调用自己，直到解析完要删除的每个字符，从而得到所需的结果。

不仅仅是数字和字符串

如果你一直关注Excel的改进，可能会注意到Excel中可以使用的数据类型有两个显著的改进：

1.动态数组-可以传递值数组，而不是将单个值传递给函数，函数也可以返回值数组。

2.数据类型–存储在单元格中的值不再只是字符串或数字。单个单元格可以包含丰富的数据类型和大量属性。

函数可以将数据类型和数组作为参数，也可以将结果作为数据类型和阵列返回。构建的lambda也是如此。