excel求神人~图一的报表内有重复的订单编号，但品项及金额不同，想把重复的订单编号筛选保留一笔，但品项中须将不同品项彙整在同一个品项栏位内，并将金额加总(如图二)；或是将品项变成在同一笔订单后面增加品项栏位及金额栏位(如图三)；求神人详解excel公式，跪求，感恩

1 个回答

11

ccenjor

iT邦大师 8 级 ‧ 2025-02-05 20:51:01

模式1解法：
G2:
=IF(COUNTIF($A$1:A2,A2)=1,A2,"")
H2:
=TEXTJOIN(",",,IF((G2<>"") * (A$2:A$8=G2),B$2:B$8,""))
(若2021/2024/365版本)公式可改为
=TEXTJOIN(",",,FILTER(B$2:B$8,A$2:A$8=G2,""))
I2:
=IF(G2<>"",SUM(IF((G2<>"") * (A$2:A$8=G2),C$2:C$8,"")),"")
(若2021/2024/365版本)公式可改为
=SUM(FILTER(C$2:C$8,A$2:A$8=G2,0))
J2:
=IF(G2<>"",VLOOKUP(G2,A$2:E$8,4,0),"")
K2:
=IF(G2<>"",VLOOKUP(G2,A$2:E$8,5,0),"")
将G2:K2框选起来，複製公式到G2:K8。

模式2解法：
M2:
=IF(COUNTIF($A$1:A2,A2)=1,A2,"")
将公式複製到M3:M8
N2:
=IF($M$2<>"",IFERROR(INDEX($B$1:$B$8,SMALL(IF($A$2:$A$8=$M2,ROW($A$2:$A$8),""),COLUMN()-13)),""),"")
将公式複製到N2:Q8
(若2021/2024/365版本)公式可改为
=TRANSPOSE(FILTER($B$2:$B$8,$A$2:$A$8=M2,""))
R2:
=IF($M$2<>"",IFERROR(INDEX($C$1:$C$8,SMALL(IF($A$2:$A$8=$M2,ROW($A$2:$A$8),""),COLUMN()-17)),""),"")
将公式複製到R2:T8
(若2021/2024/365版本)公式可改为
=TRANSPOSE(FILTER($C$2:$C$8,$A$2:$A$8=M2,""))
S2:
=IF(M2<>"",VLOOKUP(G2,$A$2:$E$8,4,0),"")
将公式複製到S3:S8
T2:
=IF(M2<>"",VLOOKUP(M2,$A$2:$E$8,5,0),"")
将公式複製到T3:T8


  • 1

尼克

iT邦大师 1 级 ‧
2025-02-05 22:39:12

神人

修改