如何在不需要手动汇总三张表的数据，能够得到透视表的数据

minstate

ykcbf1100 发表于 2024-3-25 20:56
一键汇总。

附件供参考。。。

谢谢，就是运行宏总显示找不到工程

ykcbf1100

minstate 发表于 2024-3-26 16:08
谢谢，就是运行宏总显示找不到工程

没有用到特别的库，就一个字典。附件上传前调试通过的。应该是你自己电脑的问题。
代码没有问题。

云合

先回复再学习

mengrs

=LET(a,VSTACK(HSTACK(运费!A1:D1,贴标费!C1:D1,装卸费!C1:D1),FILTER(运费!A:H,(运费!A:A<>"")*(运费!A:A<>"日期")),CHOOSECOLS(FILTER(贴标费!A:H,(贴标费!A:A<>"")*(贴标费!A:A<>"日期")),1,2,5,6,3,4,7,8),CHOOSECOLS(FILTER(装卸费!A:H,(装卸费!A:A<>"")*(装卸费!A:A<>"日期")),1,2,5,6,7,8,3,4)),GROUPBY(TAKE(a,,2),TAKE(a,,-6),sum,3))

mjm04

开个 运煤专列 ~~~

=DROP(LET(x,VSTACK(HSTACK(IF(运费!C2:$C$1000<>"",运费!$A$2:$B$1000,""),IF(运费!C2:$C$1000<>"",运费!$C$1:$C$1,""),IF(运费!C2:$C$1000<>"",运费!$C$2:$C$1000,"")),HSTACK(IF(运费!D2:$D$1000<>"",运费!$A$2:$B$1000,""),IF(运费!D2:$D$1000<>"",运费!$D$1:$D$1,""),IF(运费!D2:$D$1000<>"",运费!$D$2:$D$1000,"")),HSTACK(IF(贴标费!C2:$C$1000<>"",贴标费!$A$2:$B$1000,""),IF(贴标费!C2:$C$1000<>"",贴标费!$C$1:$C$1,""),IF(贴标费!C2:$C$1000<>"",贴标费!$C$2:$C$1000,"")),HSTACK(IF(贴标费!D2:$D$1000<>"",贴标费!$A$2:$B$1000,""),IF(贴标费!D2:$D$1000<>"",贴标费!$D$1:$D$1,""),IF(贴标费!D2:$D$1000<>"",贴标费!$D$2:$D$1000,"")),HSTACK(IF(装卸费!C2:$C$1000<>"",装卸费!$A$2:$B$1000,""),IF(装卸费!C2:$C$1000<>"",装卸费!$C$1:$C$1,""),IF(装卸费!C2:$C$1000<>"",装卸费!$C$2:$C$1000,"")),HSTACK(IF(装卸费!D2:$D$1000<>"",装卸费!$A$2:$B$1000,""),IF(装卸费!D2:$D$1000<>"",装卸费!$D$1:$D$1,""),IF(装卸费!D2:$D$1000<>"",装卸费!$D$2:$D$1000,""))),a,CHOOSECOLS(x,1)*1,b,CHOOSECOLS(x,2),c,CHOOSECOLS(x,3),d,CHOOSECOLS(x,4),e,MAP(c,LAMBDA(n,XLOOKUP(n,{"运单数量","运费合计","贴标箱数","贴标费用","装卸箱数","装卸费用"},{1,2,3,4,5,6}))),PIVOTBY(HSTACK(a,b),HSTACK(e,c),d,SUM,,0,,0,,ISNUMBER(a))),1)

mjm04

mengrs 发表于 2025-3-14 14:57
=LET(a,VSTACK(HSTACK(运费!A1:D1,贴标费!C1:D1,装卸费!C1:D1),FILTER(运费!A:H,(运费!A:A"")*(运费!A:A"日 ...

用 CHOOSECOLS + FILTER 来重排区域，处理表头行~~~    这步是这个解法的灵魂哪！！！！

不象用 PIVOTBY 时还需要提前处理标题行了呀


思路 真简洁啊，大神！！