excel函数技巧:reduce+vstakc+hstack+filter生成成绩单-趣帮office教程网

sheet2源数据——摘要部分

excel函数技巧:reduce+vstakc+hstack+filter生成成绩单-趣帮office教程网

sheet3源数据-帐号部分

excel函数技巧:reduce+vstakc+hstack+filter生成成绩单-趣帮office教程网

结果

本例有两个源数据,一个是账号信息,一个是差旅摘要,信息主要体现在B列,如何根据两个源数据,得到结果表格的数据。

=LET(table,DROP(REDUCE("",明细!B2:B6,LAMBDA(x,y,LET(

spl,TEXTSPLIT(TEXTBEFORE(y,"-"),,VSTACK("、","-"),1),

money,OFFSET(y,,1)/ROWS(spl),

VSTACK(x,IFERROR(HSTACK(spl,money),money))))),1),

name,UNIQUE(INDEX(table,,1)),

sum,MAP(name,LAMBDA(x,SUM(FILTER(INDEX(table,,2),INDEX(table,,1)=x)))),

bank,DROP(REDUCE("",name,LAMBDA(x,y,VSTACK(x,FILTER(账号!B2:C8,账号!A2:A8=y)))),1),

HSTACK(name,bank,sum)

)

公式思路:多表格数据的操作一般是通过辅助列的方式进行,这样有助于发挥EXCEL跨表格查询的作用,但此例由于table函数过于复杂,所有直接使用内存数组完成操作。

需要说明的是sum和bank两个变量的计算。

sum变量由于需要分类汇总各人员的数量,输出结果与姓名一一对应,所以可以使用MAP函数。

bank由于输出两列内容所以不能使用map函数,而需要使用reduce函数,同时由于顺序要与name保持一致,所以也不能直接使用filter函数。