Excel 取出一列中相同名字的行信息，并组合在同一个单元格内！求大神帮忙

Question

如题所述

Answer 1

示意图如下:

在C2中输入公式:=INDEX(A$2:A$96,MATCH(,COUNTIF($C$1:C1,$A$2:A$96),))&""  ,同时按下ENTER键,SHIFT键,和CTRL键,公式下拉.

在D2中输入公式:=IF(C2="","",IF(SUM(IF($A$2:$A$93=C2,1))=1,INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$1))),IF(SUM(IF($A$2:$A$93=C2,1))=2,INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$1)))&","&INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$2))),IF(SUM(IF($A$2:$A$93=C2,1))=3,INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$1)))&","&INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$2)))&","&INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$3))),IF(SUM(IF($A$2:$A$93=C2,1))=4,INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$1)))&","&INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$2)))&","&INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$3)))&","&INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$4))),IF(SUM(IF($A$2:$A$93=C2,1))=5,INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$1)))&","&INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$2)))&","&INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$3)))&","&INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$4)))&","&INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$5))),IF(SUM(IF($A$2:$A$93=C2,1))=6,INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$1)))&","&INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$2)))&","&INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$3)))&","&INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$4)))&","&INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$5)))&","&INDEX($B$2:$B$93,SMALL(IF($A$2:$A$93=C2,ROW(A$1:A$92)),ROW(A$6)))))))))) ,同时按下ENTER键,SHIFT键,和CTRL键,公式下拉.