Office 365版本新功能体验:
近几年来,Excel函数做了两次变革。第1次变革是动态数组,极大提高了函数的计算效率,它的核心函数是FILTER。第2次变革是编程式函数体系,用编程的方式分步骤编写函数,用显性循环替代之前的数组运算。
这批函数也有一个核心函数,就是今天要给大家分享的REDUCE函数。
▎基础语法
REDUCE函数只有3个参数,看起来简单而单纯。
=REDUCE([initial_value], array,
lambda(accumulator, value))
第1个参数是初始累加值,第2个参数是源数组,第3个参数是LAMBDA表达式自定义计算方式,它又有两个参数,其中acc指向’初始累加值’,value指向源数组每个被遍历的元素。
先举一个简单的例子,给大家演示下该函数的计算过程。
如上图所示的数据表,A列是班级、B列是姓名。假设,现在需要统计1班的人数,REDUCE的解法如下:
=REDUCE(0,A2:A8,
LAMBDA(_p,_v,
IF(_v="1班",_p+1,_p)
)
)
REDUCE第1参数为0,表示初始值为0,第2参数为A2:A8,第3参数是一个LAMBDA表达式,有两个参数_p和_v,其中_p作为初始值,目前为0,_v指向REDUCE的第2参数,即A2:A8。
LAMBDA遍历A2:A8每一个值,运行表达式IF(_v=“1班”,_p+1,_p)。
A2单元格为1班,则条件成立,返回结果_p+1,即0+1,结果为1,作为LAMBDA新的第1参数,也就是新的初始值。
A3单元格为1班,则条件成立,返回结果_p+1,即1+1,结果为2,作为LAMBDA新的第1参数,也就是新的初始值。
A4单元格为2班,则条件不成立,返回结果_p,即2,作为LAMBDA新的第1参数。
……
依次类推,直至遍历完A2:A8的所有元素,将LAMBDA第1参数的值返回,结果为3。
以上的案例是条件计数,同样的道理,我们还可以用REDUCE解决条件查询、条件求和等问题。
REDUCE条件求和
统计1班成绩总分: ▼
=REDUCE(0,ROW(2:8),
LAMBDA(_p,_v,
IF(
INDEX(A:A,_v)="1班",
_p+INDEX(D:D,_v),
_p
)
)
)
REDUCE条件查询
查询1班人员名单 ▼
=REDUCE("",ROW(2:8),
LAMBDA(_p,_v,
IF(
INDEX(A:A,_v)="1班",
_p&" " &INDEX(B:B,_v),
_p
)
)
)
……
▎常用情景
打个响指,条件查询/求和/计数这些常见问题,Excel都封装了专门的函数,比如COUNTIF/SUMIF/TEXTJOIN等,在单值查询的情况下,并不需要使用REDUCE函数,以上只是通过你熟悉的例子,来说明REDUCE的计算过程。
最常使用REDUCE函数的情景有两种,一种是必须借助该函数的特性去解决的问题,另外一种还是借助该函数的特性,以动态数组的形式,用一条函数公式,实现多值数据计算,这可以极大提高函数的计算效率。
先看第1种情景,我举2个典型的案例。
1)多表汇总
如下图所示,有3张工作表,名称分别为一月、二月、三月,现在需要将3张表的数据合并成一张总表。
REDUCE解法如下:
公式看不全可以左右拖动..
=REDUCE(
{"班级","姓名","成绩"},
{"一月","二月","三月"},
LAMBDA(_p,_v,
VSTACK(_p,
DROP(
INDIRECT(_v&"!a1:c" & COUNTA(INDIRECT(_v&"!a:a"))),1)
)
)
)
REDUCE第1参数是标题行,第2参数是需要合并的工作表的表名。LAMBDA表达式遍历每张工作表,使用INDIRECT函数获取相关工作表的实际数据区域,再使用DROP函数删除重复的标题行,最后使用VSTACK函数将新工作表的数据和累加值_p合并,实现多表数据合并的目的。
2)多重替换
如下图所示,D:E是对照表,现在需要将A列数据中的旧数据替换为对应的新数据,比如,把A替换为福建,B替换为广西等等。
B2单元格输入以下REDUCE函数公式,向下复制填充:
=REDUCE(
A2,
$D$2:$D$5,
LAMBDA(_p,_v,
SUBSTITUTE(_p,_v,
VLOOKUP(_v,D:E,2,0)
)
)
)
LAMBDA遍历REDUCE第2参数D2:D5中的每个值,将旧数据替换为VLOOKUP匹配的新数据,不断累加替换,直至将D2:D5单元格中每个旧数据都替换完毕,返回最后的累加值_p即可。
……
但这种在每个单元格都反复编写函数公式的方式,会极大拖累函数的计算效率,它会反复将单元格区域的数据,比如D2:E5,读入内存中执行计算,再将计算结果多次写入单元格中。
理想的计算方式是我们之前讲过的动态数组,用一条函数公式返回全部结果。这就是我们前面说的REDUCE的第2种应用场景了。
以多重替换案例来说,可以在B2单元格输入以下动态数组公式,直接返回全部结果:
=MAP(A2:A3,LAMBDA(_m,
REDUCE(_m,D2:D5,
LAMBDA(_p,_v,
SUBSTITUTE(_p,_v,
VLOOKUP(_v,D:E,2,0)
)))))
在上述公式中,我们在REDUCE外又嵌套了个MAP函数,用于遍历A2:A3的数据,并返回相应的计算结果。
但MAP函数有一个强规则,它返回的结果数组和数据源数组的尺寸必然保持一致。而相比之下,REDUCE函数则自由的多,它并没有这类限制,也就更适合搭配动态数组高效解决问题。
还是举个例子。
如下图所示,A列是由混合文本组成的数据,需要从中提取班级、姓名、性别、语文、数学和英语等信息。C:H列是模拟结果。
函数参考解法如下:
=LET(
_n,{"班级","姓名","性别","语文","Math","EngLish"},
REDUCE(_n,A2:A3,LAMBDA(_p,_v,
VSTACK(_p,
IFNA(
VLOOKUP(_n,
TEXTSPLIT(_v,":",CHAR(10)),2,),
"查无")
)
)
)
)
函数首先定义了一个变量_n,内容是标题信息。
{"班级","姓名","性别","语文","Math","EngLish"}
然后使用REDUCE函数遍历A2:A3。在遍历过程中,使用TEXTSPLIT函数将数据按”:”和换行符拆分为由项和内容构成的二维数组,比如A2单元格拆分结果如下:
使用VLOOKUP函数,查询标题在以上二维数组中的值,如果查无结果,则返回字符串”查无”。这里返回的结果是一个水平一维数组:
将水平数组和REDUCE的初始值通过VSTACK函数纵向合并,得到一个新的初始值,结果如下:
然后再计算A3单元格,将计算结果和之前的初始值合并:
至此就将源数组所有元素遍历完了,将LAMBDA第1参数作为结果返回。
摊手,就这么回事。
以上只是给大家介绍了下REDUCE各种用法中的极小且简单的部分,该函数除了用于数据查询、统计、排名,也常用于数据整理、结构转换等等。你几乎可以在任何复杂的函数问题中见到它的身影。
今天给大家分享的内容就这样吧,挥挥手,咱们下期再见。
微信扫一扫加关注,Office自学网官方微信公众号, 专注分享软件安装和视频教程,让你一号在手,办公无忧!