Excel函数之SCAN

SCAN

语法

SCAN([initial_value], array, lambda(accumulator,value,calculation))

• initial_value：用于设置累加器的初始值，即accumulator的初始值，该值为空时，array的首个值将直接赋值给累加器，并且该值将不参与函数运算
• array：需要进行循环计算的数组
• accumulator：累加器，用于记录每次循环后的计算结果，并返回
• value：数组中的每个值，每次取一个值
• calculation：设定的计算处理逻辑，每次的运算结果将存储在累加器accumulator中

作用

• 依次将数组中的每个值放入lambda中进行函数运算，每次的运算结果保存在累加器accumulator中，同时返回相应的结果值

图示

案例

1. 合并单元格转换

   • 利用公式取消合并单元格

   =LET(
       HEADER,B19:C19,
       NAME,SCAN(,B20:B31,LAMBDA(x,y,IF(y<>"",y,x))),
       SCORE,C20:C31,
       VSTACK(HEADER,HSTACK(NAME,SCORE))
   )
   

   // 公式详解
   =LET(
       HEADER,B19:C19,     // 定义B19:C19为HEADER
       // 累加器初始值不做设置，循环数组为B20:B31
       // 循环判断当前y值是否为空，如不为空，则取当前值，如为空，则维持上次取值
       // 结果定义为NAME
       NAME,SCAN(,B20:B31,LAMBDA(x,y,IF(y<>"",y,x))),
       SCORE,C20:C31,      // 定义C20:C31为SCORE
       // 对NAME和SCORE进行横向拼接，结果与HEADER进行纵向拼接，得到结果表
       VSTACK(HEADER,HSTACK(NAME,SCORE))  
   )
   

   

2. 统计合并单元格对应的数据

   • 按Name统计合并单元格表中的数据

   =SUM((SCAN(,B20:B31,LAMBDA(x,y,IF(y<>"",y,x)))=I20)*C20:C31)
   

   // 公式详解
   // 4.对第3步的数组进行求和运算
   =SUM(      
   	(
   		// 1.SCAN部分得到的结果同案例1的Name列
   		// 2.将SCAN得到的结果列与I列的Name进行比较，得到一组布尔值数组
   		SCAN(,B20:B31,LAMBDA(x,y,IF(y<>"",y,x)))=I20
   		)*C20:C31     // 3.布尔值数组与C列score进行数组乘法运算
   )
   

   

3. 直接统计合并单元格整表数据

   • 在未给定统计参照值时，直接对合并单元格进行整表统计

   =LET(
       HEADER,{"Name","Sum"},
       NAME,FILTER(B20:B31,B20:B31<>""),
       SUM,MAP(NAME,LAMBDA(z,SUM((SCAN(,B20:B31,LAMBDA(x,y,IF(y<>"",y,x)))=z)*C20:C31))),
       VSTACK(HEADER,HSTACK(NAME,SUM))
   )
   

   // 公式详解
   =LET(
       HEADER,{"Name","Sum"},            // 构造表头，定义为HEADER
       NAME,FILTER(B20:B31,B20:B31<>""), // 对Name列去除空值，定义为NAME
       // 所有的NAME执行相同计算，返回的数组定义为SUM
       SUM,MAP(                          
   	    NAME,       // 将上述NAME作为循环数组
   	    LAMBDA(
   		    z,        // z每次从NAME中取出一个值
   		    // 该部分逻辑同案例2，其中z为Name中的值
   		    SUM((SCAN(,B20:B31,LAMBDA(x,y,IF(y<>"",y,x)))=z)*C20:C31)
   		   )
   			),
   		// 对NAME和SUM进行横向拼接，结果与HEADER进行纵向拼接，得到结果表
       VSTACK(HEADER,HSTACK(NAME,SUM))
   )
   

   

4. 计算量大连续值

   • 对一组数据计算最大连续数

   =MAX(
   	SCAN(
   		0,
   		B53:B62,
   		LAMBDA(x,y,IF((y-IF(OFFSET(y,-1,)="value",100,OFFSET(y,-1,)))=1,x+1,1))
   		)
   	)
   

   // 公式详解
   // 3.对SCAN的结果数组求最大值
   =MAX(
   	SCAN(
   		0,          // 设置累加器初始值为0
   		B53:B62,    // 循环数组为B53:B62
   		LAMBDA(
   			x,        // 初始值为0
   			y,
   			// 1. 首行数据判断前一单元格的值是否为value
   				// 1.1 是：设置为100（设置较大数，用于将首次计算的累加器定义为1）
   				// 1.2 否：取上一单元格的值
   			// 2. 判断当前值与当前值上一单元格的值之差是否等于1
   				// 2.1 是：累加器加1
   				// 2.2 否：累加器重置为1
   			IF((y-IF(OFFSET(y,-1,)="value",100,OFFSET(y,-1,)))=1,x+1,1))
   		)
   	)
   

   

5. 数据筛选

   • 查找包含字符串内容的值

   =TOCOL(SCAN(,B4:B9,LAMBDA(x,y,IF(FIND(D4,y),y,""))),3)
   

   // 公式详解
   =TOCOL(          // 将SCAN返回的数据转化为一列，并用参数3忽略错误值
   	SCAN(
   		,            // 累加器初始值不做设置
   		B4:B9,       // 循环数组为B4:B9
   		// 如果能在当前循环值内找到D4中字符串的值，则返回当前循环值，否则为空
   		// 此处如未找到D4值，将会返回错误值
   		LAMBDA(x,y,IF(FIND(D4,y),y,""))
   	),
   	3
   )