158、Excel 365新增函数介绍之七---SCAN函数 SCAN函数也可以遍历数组中的每个值，以指定初始值为起点，对数组元素进行连续的迭代计算，并实时输出每一步的中间结果。它像一个动态累加器，不仅给出最终答案，更清晰展示计算过程，是分析趋势、计算运行总和或移动平均值的核心函数。 SCAN函数每次只能返回一个单值，返回结果的数组和遍历的数组行和列维度相同。 语法结构为：=SCAN([initial], array, LAMBDA(acc, curr, calculation))参数说明：1、参数[initial]为累加器的初始值，可以是文本、数字、数组，也可以省略。2、参数array时要遍历的数组。3、LAMBDA函数运算体部分通过生成一个新值，每次运算完毕后这个值都会更新第一参数累加器的值。acc 代表累计值，curr代表当前元素，通过闭包机制保留计算状态，实现状态流转。应用示例一、返回累加过程如下图所示，A列中放置了一组数据，计算从上到下数据逐行累加的结果。要返回逐行累加的结果，就可以采用SCAN函数，比如在B2单元格输入公式“=SCAN(,A2:A11,LAMBDA(s,x,s+x))”。结果如下图所示：公式中s代表累加器的值，x代表A2：A11单元格中的每个值。第一次循环时，s为累加器初始值0，x的值为A2单元格数值1，通过s+x得到计算结果1，这个1更新累加器的值，变成第二次循环时的s。第二次循环时，s已更新为1，x为A3单元格的值2，通过s+x得到计算结果3，这个3更新为累加器的值，变成第三次循环时的s，后续循环以此类推。二、填充空白单元格如下图所示，单元格区域A14：A29中包含空白单元格，现在需要将空白单元格填充上方最近的一个非空单元格的值。在B14单元格输入公式“=SCAN(0,A14:A29,LAMBDA(x,y,IF(y="",x,y)))”。公式中累加器的初始值为0，y的第一值为A14单元格的数据“A”,显然不是空白单元格，条件判断返回y值，也就是“A”，并将此值作为累加器x下一次循环的初始值，当y为空值时，判断条件成立，返回此时累加器的初始值。从而达到填充的目的。结果如下图所示:三、给部门人员编序号如图所示，A列中放置了各部门的人员信息，每个部门的主管在当前部门的第一行。在B2单元格输入公式返回每个部门的连续编号。B2单元格输入公式“=SCAN(0,A2:A12,LAMBDA(X,Y,X+(ISNUMBER(FIND("主管",Y)))))”，SCAN函数累加器初始值为0，运算遍历A2：A12单元格区域中的每个值。当循环到相应单元格时，如果找到“主管”字符，则条件判断为真，返回true，加上累加器值的初始值，也就是增加1，如果未找到“主管”字符，则条件判断为假，加上累加器的初始值，相当于加0，初始值保持不变。从而达到相同部门保持相同序号的目的。结果如下图所示：四、计算连续增长月份的最大值并计算连续增长月份金额的总额如下图所示，表格中为某年某奶茶品牌各门店的销售金额数据表，需要计算每个门店销售额连续增长月份的最大值，并计算相应月份的总金额。比如，某门店1-3月销售额连续增长，4月份销售额低于3月份，则连续增长的月份数为3,4月开始重新计算，如果5月大于4月，则累积到2，直到下一个月份又下降为止，要求返回这些连续增长值的最大值，并将最大值对应月份的金额汇总。拿出一行来分析连续增长月份的数值，如下图所示，以“春熙路店”举例，在D19单元格输入公式“=SCAN(0,D18:O18,LAMBDA(x,y,IF(y>OFFSET(y,,-1),x+1,1)))”，公式累加器初始值为0，循环时，如果当前数据y大于上一个y值，则累加器值加1，否则重新从1开始计值。这样便得到了每个月份时销售额连续增长的月份数量。在返回的这些增长值数量中求最大值便得到了最大连续增长值。 基于此，在P2单元格输入公式“=BYROW(D2:O11,LAMBDA(a,MAX(SCAN(0,a,LAMBDA(x,y,IF(y>OFFSET(y,,-1),x+1,1))))))”，即可返回各门店连续增长最大月份数，在Q2单元格输入公式“=BYROW(D2:O11,LAMBDA(a,MAX(SCAN(0,a,LAMBDA(x,y,IF(y>OFFSET(y,,-1),x+y,y))))))”，即可返回各门店连续增长最大总金额。这里需要注意：连续增长最大总金额不一定是连续增长最大值的那几个月对应的金额之和，有些连续增长月份数量少，但金额可能更大，比如万达店，最大连续增长月3有两组（如亮黄色数据），但最大连续增长金额总和最大却为7-8月（如土黄色所示）。如下图：五、填充合并单元格（相当于将数据透视表转换为一维表）如下图所示，表格中存放了某制造车间常用的原材料信息，现在需要根据第一行的名称将表格进行拆分。在F1单元格输入公式“=TRANSPOSE(SCAN(,TRANSPOSE(A1:D20),LAMBDA(x,y,IF(y="",x,y))))”，因为SCAN函数遍历数组时是按先行后列的方向执行的，所以现将源数据进行转置。结果如下图：六、进行ABC分类（简单的方法较多，这里仅做演示，有兴趣的朋友可以了解）需要将数据列中存放的数据，根据累积占比进行分类，累积占比80%以下的为A类，95%以下的为B类，其它为C类。注意累积占比必须要降序排序后求。分类列输入公式为“=LET(原数据,F2:F41,排序,SORT(原数据,,-1),累积占比,SCAN(0,SCAN(0,排序,LAMBDA(x,y,(x+y)/SUM(排序))),LAMBDA(x,y,x+y)),分类,SWITCH(TRUE,累积占比<=0.8,"A",累积占比<=0.95,"B","C"),INDEX(分类,MATCH(原数据,排序,0)))”。如下图所示：
夜雨聆风
