广告

HI,大家好,我是星光。今天给大家聊SCAN。

这货的基本语法如下:

=SCAN(初始值,数据源,
  LAMBDA(参数1,参数2,计算方式))

它可以遍历数据源的每一个数据,根据计算方式,返回一个与数据源尺寸相等的数组

举三个常见的用法小栗子。

1 填充空值

如下图所示,A列存在合并单元格,现在需要创建一个内存数组,将A列的数据填充完整

填充空值等于上一个值,为什么出来的是公式(填充空值等于上一个值时显示公式)插图

——既然你已经学到SCAN,函数段位起码也是星耀Ⅴ了,就别问我这个问题为什么用内存数组,而不是基础操作或者辅助列了。

SCAN函数解法参考如下:

公式看不全可以左右拖动..

=SCAN("",A1:A12,
  LAMBDA(_a,_b,IF(_b="",_a,_b)))
填充空值等于上一个值,为什么出来的是公式(填充空值等于上一个值时显示公式)插图1

第1参数是初始值,第2参数是数据源A1:A12,第3参数是一个LAMBDA表达式,它又有3个参数,前两个参数分别被命名为_a和_b,其中_a指向初始值,_b指向数据源的迭代元素,第3个参数是一条IF函数

IF(_b="",_a,_b)

IF函数的意思是如果计算元素_b为空,则返回初始值_a,否则返回_b自身。

SCAN遍历数据源的每个元素,并执行LAMBDA计算,整个公式的计算过程如下▼

第1次先计算A1单元格的值,此时初始值_a为空,_b指向A1单元格,值为"姓名"。_b不等于空,IF表达式返回_b自身,SCAN将计算结果作为新的初始值。初始值_a也就变成了"姓名"。

填充空值等于上一个值,为什么出来的是公式(填充空值等于上一个值时显示公式)插图2

第2次计算A2单元格的值,此时_b指向A2单元格,值为"看见星光",它不等于空,IF表达式返回_b自身,SCAN将计算结果作为新的初始值。初始值_a就变成了"看见星光"。

填充空值等于上一个值,为什么出来的是公式(填充空值等于上一个值时显示公式)插图3

第3次计算A3单元格的值,此时_b指向A3单元格,值为空,它等于空,IF表达式返回初始值_a,SCAN将计算结果作为新的初始值。初始值_a依然是"看见星光"。

填充空值等于上一个值,为什么出来的是公式(填充空值等于上一个值时显示公式)插图4

第4次计算A4单元格的值,此时_b指向A4单元格,值为空,IF表达式返回初始值_a,SCAN将计算结果作为新的初始值。初始值_a还是"看见星光"。

填充空值等于上一个值,为什么出来的是公式(填充空值等于上一个值时显示公式)插图5

……

其余以此类推,把每个元素遍历完成后,将计算结果以数组的形式返回。

在各种亲疏关系不同的场合里我们都讲过,工作表新函数正在加速向编程语言转换,学习这类新函数,多少都需要一点编程的循环思维。如果你学过VBA又或者其它编程语言,再看这类新函数应该简单很多,毕竟它们只是一类被封装好的基础循环语句

SCAN函数不但支持引用,也支持数组,不但支持查询,也支持聚合等计算方式。

2 累加求和

如下图所示,需要将A列的数据按B列的指定次数重复,D列为模拟结果。

星光老师的原图有公众号二维码,但是头条号里不允许,那就不好意思了……)

填充空值等于上一个值,为什么出来的是公式(填充空值等于上一个值时显示公式)插图6

这题函数解法有十几种,其中涉及到SCAN函数的解法参考如下:

=XLOOKUP(

 SEQUENCE(SUM(B2:B5)),
 SCAN(0,B2:B5,
  LAMBDA(_a,_b,_a+_b)),
 A2:A5,
 "",1,-1)

SCAN部分作为XLOOKUP函数的查询范围,对B列的数据累加求和。

SCAN(0,B2:B5,
  LAMBDA(_a,_b,_a+_b))

第1参数是初始值,为0,第2参数是数据源B2:B5,第3参数是LAMBDA表达式。系统遍历数据源的每一个数据▼

填充空值等于上一个值,为什么出来的是公式(填充空值等于上一个值时显示公式)插图7

第1次时,初始值_a为0,计算元素_b指向B2单元格,为2,计算结果是_a+_b,为2,作为新的初始值

第2次时,初始值_a为2,计算元素_b指向B3单元格,为3,计算结果是_a+_b,为5,作为新的初始值

第3次时,初始值_a为5,计算元素_b指向B4单元格,为4,计算结果是_a+_b,为9,作为新的初始值。

第4次时,初始值_a为9,计算元素_b指向B5单元格,为2,计算结果是_a+_b,为11,作为新的初始值。

最后返回计算结果组成的内存数组:

填充空值等于上一个值,为什么出来的是公式(填充空值等于上一个值时显示公式)插图8

XLOOKUP函数查找SEQUENCE函数生成的序列值,查找范围是SCAN函数返回的累加求和的数组,结果范围是A2:A5,查询方式是从后往前找,找下一个较大项。比如,当寻找1时,匹配大于等于1的最小值2,返回对应的人名看见星光。

3 次数

如下图所示,是A列各支队伍10轮比赛的情况,需要在L列统计每支队伍最大的连胜次数。

填充空值等于上一个值,为什么出来的是公式(填充空值等于上一个值时显示公式)插图9

SCAN函数解法参考如下:

L2单元格输入后向下复制填充:

=MAX(SCAN(0,B2:K2,
  LAMBDA(_a,_b,IF(_b="胜",_a+1,0))))

SCAN第1参数是初始值,为0,第2参数是数据源B2:K2,第3参数是LAMBDA表达式。遍历数据源的每一个元素,如果为胜,则累加初始值,否则将初始值归0

SCAN函数返回一个内存数组。例如,二肥队B3:K3返回结果如下:

填充空值等于上一个值,为什么出来的是公式(填充空值等于上一个值时显示公式)插图10

最后使用MAX函数从中取最大值,也就是相关队伍的最大连胜次数。

相比于传统的FREQUENCY函数而言,SCAN的计算过程是不是清晰很多?

最后给大家说一个好消息,在新函数体系里,SCAN函数并不是必须的,大部分时候,它能做到的事情,REDUCE函数都能做到(效率和公式长短有差异)。然后再给大家说一个坏消息,号称万能函数的REDUCE相比SCAN函数而言,要更……难一些。

今天给大家分享的内容就这样, 挥挥手,咱们明天再见。

原载公众号:Excel星球

作者:看见星光

广告