【Excel新函数】-Lambda

Power BI飞速发展，Excel也没有停滞不前。接下来几篇推文，打算分享一些Excel近几年推出的新函数。本文介绍的是Lambda，一个用来自定义函数的函数。稍微遗憾的是，这个函数只能在Mircrosoft 365订阅版中使用。

一、基本场景

武老师在《Excel Lambda函数打包复杂公式及业务逻辑》一文中，举了非常实用的案例。这里再补充些例子给大家加深印象。

假设现在要求近年收入的复合增长率，复合增长率计算逻辑为：

=（期末收入/期初收入）^ (1/期间年数）-1

那么常规公式可以这样写：

=(B6/B2)^(1/(ROW(B6)-ROW(B2)))-1

这个公式虽然不难，但需要输入不少字符。复用的时候，还需要再敲一遍。lambda函数就能解决复用麻烦的问题。它可以帮我们把复合增长率这个公式定义成一个简洁易用的函数，比如定义成：

=复合增长率(B2,B6)

二、语法结构

lambda函数语法结构：

LAMBDA([parameter1, parameter2, …,] calculation)

前面parameter是参数，可以任意多个。后面calculation是计算公式。

在本例中，参数是期末和期初的值。计算公式是上文提到的：

（期末/期初）^ (1/(ROW(期末)-ROW(期初))) - 1 

那么我们可以这样写：

=LAMBDA(期初,期末, (期末/期初) ^ (1/(ROW(期末)-ROW(期初))) - 1 )

注意，上式中的参数可以不限于2个，且参数可以用英文也可以用中文来表示。但最好不要写成B2和B6，这种单元格的名称格式容易引起混乱。

三、存储公式

接下来我们需要知道，在哪里编辑和保存这个自定义函数。

1. 点击【公式】——【定义名称】

2. 在弹框中这么填写。

值得一提的是，除了函数名称、说明、参数名可以用中文以外，所有的数学符号都需要用英文输入法。

3. 点击【OK】确认。

接下来，我们看看新函数调用效果：

函数提示

使用函数

四、跨文件使用

从上文我们可以知道，这个新函数是通过名称管理器+lambda来实现的。名称管理器定义的名称只存在于本文件，无法跨文件调用。也即在另一个Excel文件里，我们不能直接使用这个自定义的复合增长率函数。

解决办法也很简单。复制当前文件的表（sheet，可以是空的sheet），到另一个Excel文件中即可。当前文件的所有自定义名称和公式，都会随着这个空表直接复制到新文件中。

五、再举一个例子

自定义函数非常实用。比如我们实际工作中定义了一个逻辑复杂的指标，并在不同sheets中多次使用到这个指标公式。当指标计算逻辑需要改动的时候，常规做法需要修改每一处的公式。假设我们预先通过lambda定义了这个函数，那么我们只需要修改lambda里的计算逻辑，即可使所有用到的地方同时变动。

我们再来看一个场景：从文本中提取数字，比如从E列提取数字。

考虑到E列数字位置的不规则，整个提取公式其实相当复杂：

=IF(
    SUM(LEN(E2)-
      LEN(
        SUBSTITUTE(
          E2, {"0","1","2","3","4","5","6","7","8","9"}, ""))
       )>0,
   SUMPRODUCT(
     MID(0&E2, 
       LARGE(INDEX(ISNUMBER(--MID(E2,ROW(INDIRECT("$1:$"&LEN(E2))),1))* ROW(INDIRECT("$1:$"&LEN(E2))),0), 
       ROW(INDIRECT("$1:$"&LEN(E2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(E2)))/10),
    "")

复杂的公式复用容易出错，哪怕该公式只用到了一个参数(E2)。我们可以用lambda把这个公式自定义成一个简单的函数，比如：

=提取数字(文本）

lambda写法如下：

=LAMBDA(文本, IF(SUM(LEN(文本)-LEN(SUBSTITUTE(文本, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&文本, LARGE(INDEX(ISNUMBER(--MID(文本, ROW(INDIRECT("$1:$"&LEN(文本))),1))* ROW(INDIRECT("$1:$"&LEN(文本))),0), ROW(INDIRECT("$1:$"&LEN(文本))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(文本)))/10),""))

就可以愉快地使用这个简洁的函数了。