WPS内测版新功能揭秘:“SCAN”迭代函数的运用与实践 294
优秀创作者
周日,古老师在家中测试了WPS 内测版(如下图1中版本号:16460)中发布的新函数之一——名为“SCAN”的新型迭代函数。该函数允许用户对数组的各个元素进行逐个扫描处理,并在处理过程中实现累积或转换值的操作。这个“SCAN”函数与“REDUCE”函数有相似之处,但其独特之处在于,除了返回最终的累积结果之外,还能够提供每一次迭代过程中的中间结果。
图1
函数参数
SCAN函数有三个参数,分别为初始值、数组、函数,详细解释如下:
初始值: 函数会将此值传递给 LAMBDA 函数的第一个参数,在每次迭代中累积计算的结果
数组: 函数将按顺序处理数组中的每一个元素。
函数: 这是一个 LAMBDA 函数或其他可接受两个参数的函数表达式。在每次迭代中,LAMBDA 函数接收当前的累加器值和数组中的当前元素值,并返回一个新的累加器值。
特别要注意的就是函数LAMBDA只可以接受两个参数,一般定义为X和Y。
图2
光光看参数是很理解这个函数的功能以及使用技巧的,接下来用几个实际案例来详细介绍这个函数的用法。
累计求和
工作中经常需要进行累计求和,用累计求和来看整体的进度,以及与计划的差异等。累计求和的思路就是:
第1项的累计和是:S1=a1
第2项的累计和是:S2=a1+a2
第3项的累计和是:S3=a1+a2+a3
……
第n项的累计和就是:Sn=a1+a2+a3+……an
图3
上图3中,需要分别对计划与实际进行累计求和,此时就可以用到函数SCAN了。
在D3处录入=SCAN(0,C3:C14,LAMBDA(X,Y,X+Y)),得到一列数组,就是从1月到12月的计划累计求和。
分别结合累计求和的思路与函数SCAN参数,来还原一下运算过程:
参数1:初始值为 0。
参数2:数组为:C3:C14,这个区域有12个个元素,对应1月到12月的计划数量,都为500,定义为 Y;
参数3:函数:LAMBDA(X,Y,X+Y),对应每次将当前累加器值 X 与当前元素值 Y 相加,计算出的结果成为新的累加器值,并存储为返回数组的一项
具体的累计求和过程如下
第1次求和:0+500=500,其中0初始值,500为Y的第1个元素;
第2次求和:500+500=1000,500第1次求和的结果加上Y的第2个元素;
第3次求和:1000+500=1500,1000为第2次求和的结果加上Y的第3个元素
……
一直迭代到Y值的最后一项,也就是是12月的计划数量500,就得到了上图3的结果;
合并单元格快速填充
在数据中有些表格如果有合并单元格的内容的时候,函数引用就会变合并单元格处的引用,这可能不是我们想要的引用方式,此时可能需求转换成逐行的引用方式,如下图4中的B列的合并单元区域需要转换成D列的效果。
图4
此时可以录入函数:
=SCAN("",B3:B12,LAMBDA(X,Y,IF(Y="",X,Y)))
进行合并单元格转换成逐行显示方式;
函数释义:
合并单元格区域合并后(B3:B12)实际在引用的时候是返回{"计划";0;0;"采购";0;0;"仓库";0;0;0}这样的数组。
参数1:初始值为0。
参数2:数组为B3:B12:{"计划";0;0;"采购";0;0;"仓库";0;0;0} 中的每个元素,定义为 Y;
参数3:函数:使用 LAMBDA 函数定义X和Y后的运算步骤如下: IF(Y="",X,Y) 检查当前元素 Y 是否为空字符串:
如果 Y 是空字符串,则返回累加器变量 X 的当前值(保留之前非空字符串的结果)。
如果 Y 不是空字符串,则返回当前元素 Y 的值(将非空字符串添加到结果中)
第1个Y为计划,不为空,假值,返回对应的Y值,也就是计划
第2个Y为空,真值,返回X,也就是第1次运算的结果计划
以此类推;
员工连续出勤最大天数
某工厂人事部门需在每月结束后,核查各车间每位员工连续出勤的最大天数,以确认工厂是否已遵循集团人力资源关于员工连续工作6天后需休息1天的规定,确保员工得到合理的休息。
下图5为工厂员月度出勤明细表:
图5
在C3处录入动态数组公式:
=MAX(SCAN(0,D3:AH3,LAMBDA(X,Y,IF(Y="√",X+1,0))))
就可以得到每一名员最大的连续出勤天数;
函数释义:
初始值:0
数组:员工31天出勤的明细;
函数:LAMBDA(X,Y,IF(Y="√",X+1,0))
如果出勤了(等于"√"),返回初始值加1,没有出勤就归0,这样运算过程中,连续出勤就是1,2,3,4……,遇到没有出勤变成0后继续累加。
最后用MAX返回最大的数字,就得到连续最大出勤;
智能加编号
在进行查找引用的时候,经常会出现一对多的情况,此时如果为这些重复的数据加上数字编号,就可以形成新的唯一值,如下图6中计划所示:边上分别的1、2、3、4分别代表计划出的次数,第1、第2、第3次……;
如下图6所示:
图6
此时可以用公式来进行智能加编号:
录入函数:
=SCAN(0,C3:C10,LAMBDA(X,Y,(Y=OFFSET(Y,-1,))*X+1))
函数释义:
初始值:0
数组:C3:C10,这个就是部门,对应Y值;
函数:LAMBDA(X,Y,(Y=OFFSET(Y,-1,))*X+1)
难点就这里,通过OFFSET偏移Y值向上一行,得到标题“部门”,与Y1值对比,得到一个逻辑值,如果相等就是TRUE,不相等就是FALSE,对应1和0。
Y=OFFSET(Y,-1,)代表“计划=部门”,结果对应FALSE,也就是0,0*X+1等同于:0*0+1,得到第1次运算结果
第2次运算:Y2=OFFSET(Y2,-1,)代表“计划=计划”,结果对应TRUE,也就是1,1*X1+1等同于:1*1=2+1,得到第2次运算结果
以此类推,当Y值到这里,也就是采购=计划,结果对应FALSE,也就是0,0*X+1等同于:0*0+1,不管X是什么结果,又重新归0。所以就可以实现智能编号了;
智能分单
在工厂生产计划编制中,经常需要对同一张工单进行分单,也就是同一张工单按指定数量分,如WK-1工单数量是3000,需要分成三行,WK-1 1000,WK-2,1000,WK-3,1000,这样逐行显示模式。此时可以配合XLOOKUP函数、SEQUENCE函数、HSTACK函数、IFNA函数一键智能分单;
如下图7所示:
图7
录入函数:
=IFNA(HSTACK(XLOOKUP(SEQUENCE(SUM(D4:D6)),SCAN(0,D4:D6,LAMBDA(X,Y,X+Y)),B4:B6,,1),1000),1000)
函数解释:
配合XLOOKUP的向个参数来解释:
第1参数:查找值=SEQUENCE(SUM(D4:D6),求和D4:D6,这样就得到12,代表可以分12行,配合SEQUENCE生成一个连续的数组;
第2参数:查找数组,=SCAN(0,D4:D6,LAMBDA(X,Y,X+Y)),一个累加值,参考上文中的累计求和;
第3参数:返回数组:=B4:B6,也就是工单这一列
第4参数:未找到值:不录入
第5参数:匹配模式:录入-1,代表精确匹配或下一个较大的值;1和3找,无法精确匹配,找下一个比较大的值,就是3,所以返回WK-1,同时2也是,3是精准匹配;
第6参数:搜索模式,不录入;
这样就得到红色框框处工单累计的果。
如下图8所示:
图8
最全配合HSTACK,连接分单数量1000,此时因为行和列的维度不一致会返回错误值,配合上IFNA屏蔽错误并显示为1000,这样就得到最终的效果;
最后总结:
SCAN”函数它允许用户对数组的各个元素进行逐个扫描处理,并在处理过程中实现累积或转换值的操作。除了返回最终的累积结果之外,还能够提供每一次迭代过程中的中间结果。
SCAN函数可以显示每一步的运算结果,并实际动态扩展,配合一些高效的动态数组函数,可以实现一键建模,大大提高了工作效率,值得好好学习。学习SCAN后,对于REDUCE的学习就非常简单了。因为REDUCE就是SCAN 的最终结果。
学会SCAN基本等同于学会了REDUCE。
293 借助WPS动态数组公式实现智能扩展填充与数据转换自动化
运用WPS/Lambda及Scan函数实现动态数组累计需求分析 No291
巧用WPS AI,为上百名女员工打造3·8女神节专属祝福与精美卡片 290
从单一公式到复杂逻辑:通过实战案例揭示PMC面试中数据分析与解决问题的能力区分 No 289
MRP多阶需求运算报表-WPS表格版本 (7) No 287
MRP多阶需求运算报表-WPS表格版本 (6) No 286
MRP多阶需求运算报表-WPS表格版本 (5) No 285
MRP多阶需求运算报表-WPS表格版本 (4) No 284
MRP多阶需求运算报表-WPS表格版本 (3) No 283
MRP多阶需求运算报表-WPS表格版本 (2) No 282
MRP多阶需求运算报表-WPS表格版本 (1) No 281
巧用WPS中UNIQUE与SUM函数,一步解决跨门店商品库存成本合计问题 No 280
利用XLOOKUP快速实现培训未参加人员的扣分 NO 279
277 超预期,原来WPS AI也可以写出复杂的多层嵌套公式
276 巧妙用VSTACK、HSTACK实现出货货柜合并的表格变形
275 录入查询产品型号的首字,提取对应首字相同的所有型号,并按销量降序
创作者俱乐部成员