HI,大家好,我是星光。
今天一起聊聊Office 365中的新函数BYROW。
这个函数的作用是:将一个多行多列的数据矩阵,聚合为单列或单行的结构。
举几个小栗子。
▎基础语法
如下图所示,是一张成绩表。现在需要查询总分大于400的学员数据。
参考函数如下:
函数看不全可以左右拖动..
=FILTER(A2:G6,
BYROW(B2:G6,LAMBDA(_a,SUM(_a)))
>400
)
BYROW函数有两个参数,第1个参数是需要逐行遍历的数据,可以是引用也可以是数组,当是引用时会保留引用的特性——这句话暂时看不明白没关系,后面会有个相关的小案例。
BYROW函数的第2参数是一个LAMBDA函数,该函数默认第1参数是一个变量,指向BYROW函数第1参数的每行数据;第2参数表示计算方式。
以上述公式来说,BYROW函数逐行遍历B2:G6单元格区域,通过LAMBDA函数执行计算。LAMBDA函数的第1参数将每行数据设置为变量_a,然后使用SUM函数将每行的数据求和,返回一个垂直内存数组👇
最后判断该内存数组的值是否大于400,作为FILTER函数的筛选条件。
▎聚合数值
再举一个简单的案例。
如上图所示,是学员几次考试的成绩,需要按每个学员的最高分求和。
参考函数如下:
函数看不全可以左右拖动..
=SUM(
BYROW(B2:E6,
LAMBDA(_a,MAX(_a))
)
)
BYROW函数逐行遍历B2:E6区域,LAMBDA函数对每行数据执行MAX函数,得出每行最大值,最后使用SUM函数求和。
问题扩展一下,按每个学员最好的两次成绩求和。
参考函数如下:
函数看不全可以左右拖动..
=SUM(
BYROW(B2:E6,
LAMBDA(_a,SUM(LARGE(_a,{1,2})))
)
)
BYROW函数逐行遍历B2:E6区域,LAMBDA函数对每行数据执行计算,计算方式是先求出每行前2个最大值,之后SUM函数汇总求和。
▎聚合文本
BYROW函数不但支持数值聚合,也支持文本聚合。
如上图所示,A1:D6是数据源,需要从中筛选出各科成绩大于85分的姓名及明细,并形成一句话总结报告。B8单元格是模拟结果。
参考函数如下:
函数看不全可以左右拖动..
=TEXTJOIN(CHAR(10),1,
BYROW(B2:D6,
LAMBDA(_n,
LET(
_s,TEXTJOIN(',',1,IF(_n>85,B1:D1&'-'&_n,'')),
IF(LEN(_s),INDEX(A:A,ROW(_n))&':'&_s,''))
)
)
)
第2行至第8行是BYROW函数,逐行遍历B2:D6区域,第2参数LAMBDA执行计算方式。
LAMBDA第1参数是变量_n,指向B2:D6区域的每行数据。第2参数是一个LET函数。LET函数先运行以下函数公式,将每行成绩大于85的值和科目名称合并为一个字符串,将其赋值给变量_s。
_s,TEXTJOIN(',',1,IF(_n>85,B1:D1&'-'&_n,''))
LET函数最后判断_s是否为空字符串,如果非空,则在_s前添加姓名前缀👇
IF(LEN(_s),INDEX(A:A,ROW(_n))&':'&_s,'')
需要注意的是,在上面这条IF函数公式中,使用ROW(_n)返回每行的行号,再使用INDEX函数通过行号获取姓名。这就是我们前面说的,当BYROW函数的第1参数是单元格引用时,它会保留引用的特性,由此我们才可以通过ROW函数获取引用的行号。
最后使用TEXTJOIN函数将BYROW函数返回的垂直数组合并为一个字符串。
图文制作:看见星光
联系客服