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),""))
就可以愉快地使用这个简洁的函数了。
本文分享自 PowerBI x Python 微信公众号,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ,欢迎热爱写作的你一起参与!