wps/excel函数技巧:lambda+group+textjoin逆透视再聚合

如图A列至F列为统计函数列，其中要说明的是备注列，列出来入库或出库的明细并以文本形式体现，如何统计剩余库存并仍以文本的形式备注出剩余库存名字*数量并以+相连接的明细。公式如下：

=LET(h,UNIQUE(B2:B8), #获取不重复的品名

i,DROP(REDUCE("",F2:F8,LAMBDA(x,y,LET( #循环获取逆透视表格

a,TEXTSPLIT(y,"*","+",,,1), #将文本部分拆分成明细表格

b,TAKE(a,,1), #提取明细表名称列

c,--TAKE(a,,-1), #提取明细表数量列并转换成数字方便计算

d,OFFSET(y,,-4), #获取y同一行的品名

e,EXPAND(d,ROWS(a),1,d), #将品名进行拓展行数与明细表行数保持一致

VSTACK(x,IF(OFFSET(y,,-1)="",HSTACK(e,b,c),HSTACK(e,b,-c)))))),1),

#根据是入库还是出库进行判断，如果为空，明细表中的数量变为负数（变量c）。并汇总生成逆透视表。

j,GROUPBY(HSTACK(INDEX(i,,1),INDEX(i,,2)),INDEX(i,,3),SUM,,0), #将明细表转换为聚合表

m,IFS(INDEX(j,,3)=1,INDEX(j,,2),INDEX(j,,3)=0,"",1,INDEX(j,,2)&"*"&INDEX(j,,3)),

#将处理后的聚合表明细部分的名字和数量合并成要求的文本样式

VSTACK(HSTACK(B1,C1,"结存",F1),HSTACK(h,XLOOKUP(h,B2:B8,C2:C8),TAKE(GROUPBY(INDEX(i,,1),INDEX(i,,3)*INDEX(i,,2),SUM,,0),,-1),DROP(REDUCE("",h,LAMBDA(x,y,VSTACK(x,TEXTJOIN("+",1,FILTER(m,TAKE(j,,1)=y))))),1))))

#将名称、单位、库存、备注合并到一张二维表格里。

公式思路：这是我用过最长的公式，之所以长是因为要先将函数还原成明细表，再考虑将明细表计算后按要求合并成最终的结果。公式之所以长是因为要求不规范，需要两次处理备注中的字符串。

逆透视表结果如下：

聚合表的结果如下：

处理后的聚合表如下：

大家看看还有什么更简洁的办法么？