excel函数技巧:reduce+map+sumproduct+offset提取信息重构表格-趣帮office教程网

源数据

excel函数技巧:reduce+map+sumproduct+offset提取信息重构表格-趣帮office教程网

希望转换的数据

如图源数据字段以工艺名称为主,如何转换成以周数为主的字段,并同时完成数据的分类汇总,公式如下:

=DROP(REDUCE("",SEQUENCE(COLUMNS(D1:L1)),LAMBDA(i,y,VSTACK(i,MAP(B70:U70,LAMBDA(x,SUMPRODUCT(OFFSET(D2:D58,,y-1)*(B2:B58)*(C2:C58=x))))))),1)

公式思路:公式主题架构为reduce(map(sumproduct……,其中reduce负责将offset所要偏移的行数作成数组,依次给map赋值,map函数刚将周数作为变量依次赋值给sumproduct.

sumproduct函数根据前两者的变量,依次进行计算最终得到结果。

当然本例如果采用右拉,下拉的方式公式要简单的多:

=SUMPRODUCT(OFFSET($D$2:$D$58,,row(A1)-1)*($B$2:$B$58)*($C$2:$C$58=B$70))

将公式复制到B71单元格,向上向下拖动就会得到相对应的结果。

这样可以明显看出两者的不同之处:一键生成的公式长,复杂,但不用使用$符号。鼠标拖拉公式要简单但需要拖拉。如果数据非常多,超过万行的话,哪个公式好些呢?哪个公式又不容易出错呢,大家可以思考交流一下。