SCAN函数基本用法和典型应用
作者:小窝来源:部落窝教育发布时间:2024-03-27 14:36:12点击:1504
编按:
教程介绍SCAN函数的基本用法和3个典型应用。由于SCAN函数与REDUCE函数类似,所以教程中还会比较它们的不同。
SCAN函数与前面介绍的REDUCE函数类似,都是LAMBDA的好帮手。这两个函数有很多相同的地方,因此下方小窝在介绍的时候,会比较SCAN函数与REDUCE函数的不同。
1.作用与语法
SCAN函数将初始值、数组代入LAMBDA函数中进行逐步累加运算并把每步运算结果都输出。
=SCAN(初始值,数组,LAMBDA(参数1,参数2,计算式)
函数的各参数与REDUCE含义一致。
REDUCE只将最后一步的结果输出,而SCAN会把每步运算的结果都输出,输出的肯定是数组。两者具体不同处看下方的基本用法。
2.基本用法
1)给出具体初始值
譬如做如下数据乘积运算。
REDUCE公式=REDUCE(1,B2:E3,LAMBDA(x,y,x*y)),结果如下图:
将公式中的函数替换为SCAN, =SCAN(1,B2:E3,LAMBDA(X,Y,X*Y)),结果如下图:
公式参数完全一样,REDUCE只输出了最后一次运算的结果“1209600”,SCAN则把数组Y中每个数据参与的运算结果都输出了。
取SCAN的最后一行最后一列的值,=TAKE(SCAN(1,B2:E3,LAMBDA(X,Y,X*Y)),-1,-1),就是REDUCE的结果。
2)省略初始值
如果省略初始值,则公式会把数组中的第一个数据作为第一次运算的结果。
譬如连接单元格中字符。
先来看给定初始值为1的效果。
再来看省略初始值的效果。
顺便看看REDUCE省略初始值的结果,只返回最后一次运算的结果。
重要提示
在基础用法中,我们更换公式函数,前后两个公式都可以用。但这并不意味着每个REDUCE公式都可以直接更换为SCAN函数公式进行使用。因为Excel函数不支持嵌套的数组(数组中的数组),所以只有REDUCE每步运算中的结果不是数组,才可以将REDUCE直接改成SCAN。
譬如按次数复制数据。
REDUCE公式=REDUCE("数据",B21:B24,LAMBDA(X,Y,VSTACK(X,EXPAND(Y,OFFSET(Y,,1),,Y))))
如果你将公式中函数改成SCAN,将出现#CALC!错误。
原因就在于公式中VSTACK在每步计算中输出的都是数组而不是一个值!
3.典型应用实例
1)拆分并填充合并单元格
譬如下方,公式可以写成=SCAN(,B34:B43,LAMBDA(X,Y,IF(Y<>"",Y,X)))
说明:
初始值省略,代表第一次运算的结果就是A;第二次运算,Y是B35,等于空,运算结果就是上一次X,即A;第三次运算,B36同样等于空,结果还是上次的X,即A;第四次运算,B37不等于空,运算结果就是Y即B……
如果用传统函数来完成,则可以用LOOKUP座字法查找。
如果用REDUCE函数,则公式=REDUCE(,B34:B43,LAMBDA(X,Y,VSTACK(X,IF(Y<>"",Y,TAKE(X,-1)))))
2)求连续出现的最高次数
譬如求下方各职员第5周的最大连续加班天数。
公式=MAX(SCAN(0,C48:I48,LAMBDA(X,Y,IF(Y="√",X+1,0))))
说明:
传统上,统计某连续出现的次数使用FREQUENCY函数。此处用FREQUENCY的话,公式=MAX(FREQUENCY(IF(C48:I48="√",COLUMN(A:G)),IF(C48:I48="",COLUMN(A:G))))。该公式不但长,并且不易理解。
但用SCAN则既简短,又好理解。从周1到周7,如果单元格数据等于√,则天数加1;不等于,则天数归0。以何冀川来说,周1加班,结果是0+1=1;周2没有加班,结果是0;周3加班,结果是0+1=1;周4加班,结果是1+1=2;周5、6、7都没有加班,结果都是0。
最后用MAX取最大值。
3)求累计值首次达标时间
下方是个月的销售值,求累计销售突破2万的月份。
小窝曾用LAMBDA递归运算完成累计值首次达标月份的查找,估计很多伙伴看得头大。这次用SCAN搭配XLOOKUP则简单易懂。
公式=XLOOKUP(TRUE,SCAN(,C58:C69,LAMBDA(x,y,x+y))>=F57,B58:B69)
说明:
SCAN(,C58:C69,LAMBDA(x,y,x+y))部分可以生成销售金额累加数组;然后将该组数与达标金额F57进行比较得到一串由FALSE和TRUE组成的数组;最后用XLOOKUP查找TRUE,得到第一个符合条件的月份。
4.总结
SCAN函数与REDUCE函数一样,自带累加器,可以对数组进行累加运算。与REDUCE不同的是,SCAN函数会将中间的运算结果也一并输出。
本文配套的练习课件请添加客服微信buluowojiaoyu索取。
做Excel高手,快速提升工作效率,部落窝教育Excel精品好课任你选择!
扫下方二维码关注公众号,可随时随地学习Excel:
相关推荐:
版权申明:
本文作者小窝;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。