E2处输入:
=INDEX($A$2:$A$4,SMALL(IF(MATCH($A$2:$A$4,$A$2:$A$4,)=ROW($2:$4)-1,ROW($2:$4)-1),ROW(A1))) 下拉
F2:
=SUM(($A$2:$A$4=E2)*$B$2:$B$4) 下拉
G2:
=OFFSET($C$1,MATCH(MIN(IF($A$2:$A$4=E2,--$C$2:$C$4)),IF($A$2:$A$4=E2,--$C$2:$C$4),),) 下拉
H2:
=OFFSET($D$1,MATCH(MAX(IF($A$2:$A$4=E2,--$D$2:$D$4)),IF($A$2:$A$4=E2,--$D$2:$D$4),),) 下拉
以上都是数组公式,输完按CTRL+SHIFT+回车
=INDEX($A$2:$A$4,SMALL(IF(MATCH($A$2:$A$4,$A$2:$A$4,)=ROW($2:$4)-1,ROW($2:$4)-1),ROW(A1))) 下拉
F2:
=SUM(($A$2:$A$4=E2)*$B$2:$B$4) 下拉
G2:
=OFFSET($C$1,MATCH(MIN(IF($A$2:$A$4=E2,--$C$2:$C$4)),IF($A$2:$A$4=E2,--$C$2:$C$4),),) 下拉
H2:
=OFFSET($D$1,MATCH(MAX(IF($A$2:$A$4=E2,--$D$2:$D$4)),IF($A$2:$A$4=E2,--$D$2:$D$4),),) 下拉
以上都是数组公式,输完按CTRL+SHIFT+回车