G2:G23{=IFERROR(IF((INDEX(A:A,SMALL(IF(MATCH(A$2:A$21&B$2:B$21,A$2:A$21&B$2:B$21,)=ROW($1:$20),ROW($2:$21)),ROW(A1)-COUNTIF(G$1:G1,"*小计")))<>G1)*ISERR(FIND("小计",G1))*(ROW(A1)>1),G1&"小计",INDEX(A:A,SMALL(IF(MATCH(A$2:A$21&B$2:B$21,A$2:A$21&B$2:B$21,)=ROW($1:$20),ROW($2:$21)),ROW(A1)-COUNTIF(G$1:G1,"*小计")))),IF((G1="")+(RIGHT(G1,2)="小计"),"",G1&"小计"))
H2:H23{=IFERROR(INDEX(B:B,SMALL(IF((MATCH(A$2:A$21&B$2:B$21,A$2:A$21&B$2:B$21,)=ROW($1:$20))*(A$2:A$21=G2),ROW($2:$21)),COUNTIF(G$2:G2,G2))),"")
I2:K23=IF(RIGHT($G2,2)="小计",SUM(I$1:I1)-SUMIF($G$1:$G1,"*小计",I$1:I1)*2,SUMIFS(C:C,$A:$A,$G2,$B:$B,$H2))
|