怎样在excel中合并统计。如图：把图1整理成图2

答案:1 悬赏:40

解决时间 2021-01-13 22:42

提问者网友：嘚啵嘚啵
2021-01-12 22:42

最佳答案

二级知识专家网友：过活
2021-01-12 23:03

Sheet1

Sheet2

A2=INDEX(Sheet1!A:A,MIN(IF(COUNTIF(A$1:A1,Sheet1!$A$2:$A$30),4^8,ROW(Sheet1!$A$2:$A$30))))&""数组公式向下复制
【数组公式，公式输完后，光标放在公式编辑栏同时按下CTRL+SHIFT+回车键，使数组公式生效】
B2=VLOOKUP(A2,Sheet1!$A$2:$B$30,2,)公式向下复制
C2=SUMIF(Sheet1!$A$2:$A$30,A2,Sheet1!$C$2:$C$30)公式向下复制
D2=IFERROR(INDEX(Sheet1!$A$1:$J$30,INT(SMALL(IF(OFFSET(Sheet1!$D$1,MATCH($A2,Sheet1!$A$2:$A$30,),,COUNTIF(Sheet1!$A$2:$A$30,$A2),6)<>0,ROW(OFFSET(Sheet1!$D$1,MATCH($A2,Sheet1!$A$2:$A$30,),,COUNTIF(Sheet1!$A$2:$A$30,$A2),6))+COLUMN(OFFSET(Sheet1!$D$1,MATCH($A2,Sheet1!$A$2:$A$30,),,COUNTIF(Sheet1!$A$2:$A$30,$A2),6))/100,4^8),COLUMN(A1))),MOD(SMALL(IF(OFFSET(Sheet1!$D$1,MATCH($A2,Sheet1!$A$2:$A$30,),,COUNTIF(Sheet1!$A$2:$A$30,$A2),6)<>0,ROW(OFFSET(Sheet1!$D$1,MATCH($A2,Sheet1!$A$2:$A$30,),,COUNTIF(Sheet1!$A$2:$A$30,$A2),6))+COLUMN(OFFSET(Sheet1!$D$1,MATCH($A2,Sheet1!$A$2:$A$30,),,COUNTIF(Sheet1!$A$2:$A$30,$A2),6))/100,4^8),COLUMN(A1)),1)*100),"")数组公式向下向右复制

我要举报

如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！