当前位置: 欣欣网 > 办公

Excel小白也能看懂的LAMBDA递归,自定义一对多查找函数DVLOOKUP

2024-04-01办公

文 前 推 荐


编按:

本篇详细介绍Excel中LAMBDA的递归运算,包括递归的含义和理解、递归公式组成、递归要点、递归条件、递归的经典应用。本篇中递归运算,没有借助LAMBDA的伴侣函数。是一篇小白都能看懂的LAMBDA递归教程,非常适合没有编程基础的小白掌握递归运算。

在前面的文章【新函数LAMBDA的用法(上)——不用VBA做自定义函数】中,小窝为大家介绍了LAMBDA函数的基本用法以及第一个优点——不用VBA实现可重复使用的自定义函数。 今天则向大家介绍LAMBDA的第二个优点,递归运算。

1.何为递归?

递归来自数学与计算机编程,指的是函数在运行中重复调用自身。

很抽象!下面用一个简单的数字累加来说明。

例:求任意两个给定的整数之间的递增数列和,如2与6之间的数列2、3、4、5、6的和。

如果已经有2、3、4、5、6数列,用SUM函数可以直接求和。

但现在只有起始数2和截止数6,如何求它们之间的数列和呢?

忘掉序列函数SEQUENCE。我们用数学的思路来看,以2开始,数列中下一个数总等于上一个数加1,直到等于截止数6为止:

假如用F(2,6)表示数列2到6的和,则运算过程可以模拟如下:

图中黄色的部分,属于循环部分。初始值2,调用函数后生成新值3;新值3作为新的初始值,调用函数后生成新值4……直到生成值等于截止数。当「递数」过程——可以是递增也可以是递减,此处是递增——结束后,就把所有数「归拢」——代入算式——得出结果。

这就是递归:先循环生成要进行计算的各个变量值,然后再来计算。

递有两层意思:

第一,通过递增或者递减的方式生成新的数。

可以是值的递增或者递减,如X+1、X+2、X-1、MOD(X,10)、X/10等;

也可以是行数的递增或者递减得到新的引用,如OFFSET(X,1,)、OFFSET(X,-1,)等;

也可以是字符串的位数的递增递减,如len(x)-1,len(x)+1)等。

第二,传递,把生成的数暂存并传递到下一次循环中。

归,把所有循环生成的数从暂存的栈中取出代入算式进行运算得到结果。

设置起始数为变量X,截止数为Y,下一个数为X+1,直到X=Y为止,我们自定义函数TWONUMB完成递归运算:

=LAMBDA(X,Y,IF(X=Y,X,X+TWONUMB(X+1,Y)))

公式中的X+TWONUMB(X+1,Y)部分对应前方图中黄色部分。函数运行时,总是判断X是否符合终止条件,不符合,就重复调用TWONUMB函数,由TWONUMB(X+1,Y)生成新的X值。

在B5中使用刚定义的TWONUM函数,即可求任意两个整数之间的序列和。

2. Exel中递归函数组成和必备条件

1)递归函数组成

初步了解什么是递归后,再来了解在Excel中递归函数的组成部分。典型的递归函数包括5部分:

变量:就是函数参数,数量1~253之间。

终止条件:停止循环的条件。

终止值:当循环停止时,变量等于多少。

运算式:包含所有变量在内的运算式。通常有两个部分,变量循环和变量应用。其中变量循环式必须有。

变量循环:包含函数名称、变量表达,按公式前方的变量顺序交代所有变量的生成方式。

变量循环要点:

(1)函数名称必须与定义框中名称一致;

(2)有多少个变量就必须有多少个变量表达,其顺序与变量一致。

变量表达解读(以XY两个变量为例):

可以是计算式,如TWONUMB(X+1,Y)中的X+1,表示每次循环X变量都会加1;

可以是变量自身,如TWONUMB(X+1,Y)中的Y,表示每次循环Y变量保持初始值不变;

可以是一个常数,如TWONUMB(5,Y-1)中的5,表示除开初始值外,每次循环X变量都等于5;

可以引用另一个变量,如TWONUMB(Y,Y-1)中的Y,表示除初始值外,每次循环X都引用新的Y变量;

可以是多个变量的计算式,如TWONUMB(X+Y-1,Y)中的X+Y-1,表示每次循环X都等于X+Y-1。

变量应用:交代循环生成的各个变量怎么计算——不是每个递归函数都必须有。

譬如前方TWONUMB函数,如果删除变量应用「X+」,则返回的就是循环终止时的X值而不是和。

=LAMBDA(X,Y,IF(X=Y,X,TWONUMB(X+1,Y)))

变量应用包括变量引用和结果符号两部分。

变量引用是一个表达式,确定循环生成的变量是否使用、怎么使用。譬如当前引用就一个「X」,意思是不计算Y变量,只原样引用每个X值。小白要注意:不是所有变量都会参与结果值的运算!有些变量只是用来作为终止条件的,并不参加值的运算。这种不参与值运算的变量,就相当于你跳绳的时候站在傍边帮你计数喊停的人!

结果符号明确每个循环变量代入引用后的结果怎么归拢。譬如当前符号是「+」,就表示各变量代入引用后的结果需要累加。

最初接触递归的小白可能与小窝一样会在此处犯错:以为加,就表示直接把得到的循环变量相加。

譬如,求下方单元格中各数据包含的数字和,如数据「78」,其数字和就是15。

此处至少有两种递归求解方法。其中一种是通过余数来累加,自定义函数caishuhe:

=LAMBDA(X,IF(X=0,0,MOD(X,10)+CAISHUHE(TRUNC(X/10))))

小窝刚学LAMBDA递归时,认为这里的递归循环是这样的:

第1次循环:CAISHUHE(TRUNC(45618/10))= 4561

第2次循环:CAISHUHE(TRUNC(4561/10)=456

第3次循环:CAISHUHE(TRUNC(456/10)=45

第4次循环:CAISHUHE(TRUNC(45/10)=4

第5次循环:CAISHUHE(TRUNC(4/10)=0

结果= MOD(45618,10)+4561+456+45+4+0=5074

这可是大大的错了!

并非直接加上各循环变量,而要把各循环变量代入变量引用中运算后再相加。

结果=MOD(45618,10)+ MOD(4561,10) + MOD(456,10) + MOD(45,10) + MOD(4,10) + 0=24

2) 递归运算必备条件

递归运算必须具备三个条件。

第一:存在终止条件。

循环必须有限,要设置终止条件让其能停止。LAMBDA自定义递归函数时,使用IF函数表达终止条件。

实际上在Excel中,不但要有终止条件,还得满足循环次数不大于5459。(下方会带大家来测试这点)

第二:具体的变量循环方式。

在「递」的过程中需要循环生成变量值。怎么生成?就必须在变量循环部分交代。

前方求两个数之间的序列和的TWONUMB函数,是基于初始值2,采用X+1的方式不断生成新的初始值,而截止值Y保持不变。

=LAMBDA(X,Y,IF(X=Y,X,X+TWONUMB(X+1,Y)))

如果倒过来基于截止数6,则需要按Y-1的方式生成。定义TWONUMB2函数:

=LAMBDA(X,Y,IF(Y=X,Y,Y+TWONUMB2(X,Y-1)))

第三:至少有一个变量是递增或者递减并能指向终止条件。

小窝在测试中发现,不管有多少个变量,至少得有一个变量是递增或递减的,并能指向终止条件,否则出错。

譬如TWONUMB2函数,包含两个变量X和Y,起始数是2,截止数是6。变量循环部分不能写成TWONUMB2(X,Y)、TWONUMB2(2,Y)、TWONUMB(X,Y-3)等。

TWONUMB2(X,Y)表示重复循环XY初始值,没有递增或递减,无法指向终止条件;

TWONUMB2(2,Y)表示X变量循环值都等于2,Y变量等于初始值,也不可能达到Y=2的终止条件;

TWONUMB(X,Y-3)表示Y变量每次循环都减去3,其不可能达到Y=2的终止条件。

各位伙伴可以试试是否如此。

3. Excel当前最大递归循环次数

递归运算必须有终止条件,让循环有限。不仅如此,Excel自身也限制了递归循环的次数。

没有设置终止条件或者循环变量无法指向终止条件,递归函数会出现#NUM!错误!

除此外,即便正确设置了终止条件,如果循环次数超过了Excel递归循环最大次数的限制,也会提示#NUM!错误!

测试:

小窝准备了两个变量X和Y。如果Y每减少1,X就增加1,终止条件是Y等于0。如此,Y是多少,就表示递归循环了多少次。设置X的初始值是0,大家一起来看看Y最大等于多少。

自定义最大循环数函数MAXXH:

=LAMBDA(X,Y,IF(Y=0,X,MAXXH(X+1,Y-1)

从下方动图可以看出,当Y值大于5459,就出现了#NUM!错误。这说明Excel当前支持的最大递归循环次数是5459。

4. LAMBDA的经典递归运用

小窝例举部分Excel中LAMBDA递归运算典型案例,方便大家更好地理解递归运算。

1)根据替换表替换字符

按替换表中的规则替换字符。

如果采用「查找和替换」则需要进行5次操作;如果直接用SUBSTITUTE函数,公式会随着替换字符数的增多嵌套多层SUBSTITUTE。因此,多字符替换更适合用自定义递归函数TIHUAN进行。

=LAMBDA(text,old,new,IF(old="",text,TIHUAN(SUBSTITUTE(text,OFFSET(old,0,),OFFSET(new,0,)),OFFSET(old,1,),OFFSET(new,1,))))

在B26中输入公式=TIHUAN(A26,$D$26,$E$26)并向下填充即可。

说明:

公式含三个变量(参数),text代表原数据,old代表首个原字符,new代表首个新字符。

该公式没有变量应用,只有变量循环:

TIHUAN(SUBSTITUTE(text,OFFSET(old,0,),OFFSET(new,0,)),OFFSET(old,1,),OFFSET(new,1,))

循环中的第一部分SUBSTITUTE(text,OFFSET(old,0,),OFFSET(new,0,)),交代text的生成方式——引用每个new变量替换每个old变量。

循环式中的第二部分OFFSET(old,1,)交代了old的生成方式——引用old的下一行字符;

循环式中的第三部分OFFSET(new,1,)交代了new的生方式——引用new的下一行字符。

以A26单元格为例:

当前的递归公式有一个小缺点: 替换表最后一个原字符下必须是空单元格。

小窝再分享一个不需要空单元格的递归函数TIHUAN2:

=LAMBDA(text,old,new,LET(X,CONCAT(old),Y,CONCAT(new),IF(LEFT(X,1)="",text,TIHUAN2(SUBSTITUTE(text,LEFT(X,1),LEFT(Y,1)),RIGHT(X,LEN(X)-1),RIGHT(Y,LEN(Y)-1)))))

2)反转字符串的排列

譬如将「我爱她」反转后就变成「她爱我」。

小窝带大家看看怎么用递归完成字符串的反转排列。

自定义fanzhuan函数:

=LAMBDA(text,IF(text="","",RIGHT(text,1)&fanzhuan(LEFT(text,LEN(text)-1))))

然后在B34中输入公式=fanzhuan(A34)并下拉填充即可。

说明:

变量应用中的引用表达式RIGHT(text,1),表示循环生成的每个text都需要代入该式中运算;

结果符号「&」,表示将经过RIGHT提取后的结果链接成字符串。

变量循环fanzhuan(LEFT(text,LEN(text)-1)),表示每次用LEFT减少一位提取生成新的text。

3.查找累加销售金额首次大于某值的月份

下面是2023年各月的销售数量。现在需要查累计销售第一次突破3500的月份。

如果采用传统函数,可能要建立辅助列才能找到月份。用自定义递归函数,可以一步到位。

自定义leijia函数:

=LAMBDA(x,y,n,IF(y>3500,n,leijia(OFFSET(x,1,),y+OFFSET(x,1,),OFFSET(n,1,))))

在D41中输入公式=leijia(B41,B41,A41)即可。

说明:

公式中用到3个变量。X变量代表首月销量地址,Y变量代表首月销量,N代表首个月份。

公式没有变量应用部分。

在变量循环中,OFFSET(x,1,)表示引用下一行销量;y+OFFSET(x,1,)表示累加销量;OFFSET(n,1,)表示引用下一行月份。

最后,当累计销量Y>3500的时候,返回相应的N——月份。

这里最难理解的部分是为何要用两个变量X和Y分别来表达首月销量。

X代表具体单元格值,而OFFSET(x,1,)中的X代表的是单元格地址,这是两个不同的属性,不能在变量循环中写成X+OFFSET(x,1,),否则就出现#VALUE!错误。各位小白一定要注意这点!!这就是为何用两个变量的原因。

4)提取所有字母

在【新函数LAMBDA的用法(上)——不用VBA做自定义函数】中小窝分享了用递归方法提取所有数字,这里用递归法提取所有字母。

使用自定义tizimu函数:

=LAMBDA(x,IF(x="","",IF((LEFT(x,1)>="a")*(LEFT(x,1)<="z"),LEFT(x,1)," ")&tizimu(RIGHT(x,LEN(x)-1))))

说明:

公式只有一个变量X,代表要提取的原始数据。

变量循环tizimu(RIGHT(x,LEN(x)-1)),用RIGHT逐次递减一位提取。

变量应用IF((LEFT(x,1)>="a")*(LEFT(x,1)<="z"),LEFT(x,1)," ")&,将每个变量值用LEFT提取一位并判断是否属于字母,是保留,不是则变成空格;最后将各结果用&链接成字符串。

5)自定义一对多dvlookup查找函数

很多人都像小窝一样,喜欢Vlookup函数,遗憾的是它只返回第一个符合要求的值。小窝试着自定义dvlookup函数,实现一对多查找。

自定义dvlookup函数:

=LAMBDA(x,y,z,IF(OR(x="",y=""),"",IF(OFFSET(y,0,0,1,1)=x,INDEX(y,1,z)," ")&dvlookup(x,OFFSET(y,1,0,1),z)))

说明:

自定义dvlookup函数用法与vlookup非常接近,有3个变量,X是查找值,y是查找区域,z是返回列。

6)元素组合

要求将等级、年级、班级组合成具体的班级名称。

自定义zuhe函数:

=LAMBDA(arr,y,TOCOL(IF(y=1,"",zuhe(arr,y-1))&TOROW(INDEX(arr,,y),1)))

说明:

含arr 和y两个变量。Arr变量表示要组合的区域,Y变量表示列数。

变量循环部分zuhe(arr,y-1),arr不变,列数每次循环减去1。

变量应用部分TOCOL( &TOROW(INDEX(arr,,y),1)),将Arr中的最后一列先变成横排再变成竖排,再与倒数第2列的横排值用&相连,相连后竖排……

5. 总结

LAMBDA递归在Excel表格实际处理中可能用得不多。只有涉及到循环处理的地方才考虑用它。

LAMBDA递归运算在刚开始接触的时候,不易理解。小窝也是摸索很久才有所得,如果文中有错误,欢迎指出。

LAMBDA还有6个伴侣函数,MAP、REDUCE、SCAN、MAKEARRAY、BYCOL、BYROW。结合这6大函数,LAMBDA在实际工作中运用更大。后续小窝将为大家一一介绍。

感谢您的点赞,分享!

Excel基础技巧训练营 ↓↓↓


点"阅读原文",学习更多的Excel视频教程