当前位置：首页 > 科技 > 正文

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

启示号
科技
1年前
264

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 也是如此。 欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。
欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料，并通过社群加入专门的微信讨论群，更方便交流。