这类问题,我回答好几次了,可惜无人识得其中妙处,今天再次抄录如下,希望您能采用。
如数据在A2:B100,在C2中输入公式:=INDEX(A$2:A$100,MATCH(,COUNTIF($C$1:C1,$A$2:A$100),))&"" ,同时按下ENTER键,SHIFT键,和CTRL键, 公式下拉。
在D2中输入公式:=IF(C2="","",IF(SUM(IF($A$1:$A$100=C2,1))=1,INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$1))),IF(SUM(IF($A$1:$A$100=C2,1))=2,INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$1)))&","&INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$2))),IF(SUM(IF($A$1:$A$100=C2,1))=3,INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$1)))&","&INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$2)))&","&INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$3))),IF(SUM(IF($A$1:$A$100=C2,1))=4,INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$1)))&","&INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$2)))&","&INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$3)))&","&INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$4))),IF(SUM(IF($A$1:$A$100=C2,1))=5,INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$1)))&","&INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$2)))&","&INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$3)))&","&INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$4)))&","&INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$5))),IF(SUM(IF($A$1:$A$100=C2,1))=6,INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$1)))&","&INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$2)))&","&INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$3)))&","&INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$4)))&","&INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$5))))&","&INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100=C2,ROW(A$1:A$100)),ROW(A$6))))))))) ,同时按下ENTER键,SHIFT键,和CTRL键,公式下拉。
公式虽然长了点儿,但要是复制粘贴到您的表中,应当是十分简单。
温馨提示:内容为网友见解,仅供参考