B13单元格输入公式=IFERROR(INDEX(B$5:B$7,SMALL(IF(LEN($G$5:$G$7)-LEN(SUBSTITUTE($G$5:$G$7,",",))+1>=COLUMN($A:$Z),ROW($G$5:$G$7)-4),ROW(A1))),"")
数组公式,Ctrl+Shift+回车,公式两端出现大括号{}后,右拉到F列再下拉。
G13单元格输入公式=IFERROR(TRIM(MID(SUBSTITUTE(INDEX(G$5:G$7,MATCH(B13&C13&D13&E13&F13,B$5:B$7&C$5:C$7&D$5:D$7&E$5:E$7&F$5:F$7,)),",",REPT(" ",50)),COUNTIFS(B$13:B13,B13,C$13:C13,C13,D$13:D13,D13,E$13:E13,E13,F$13:F13,F13)*50-49,50)),"")
也是数组公式,Ctrl+Shift+回车,公式两端出现大括号{}后,下拉。
如图:
首先在H4:L7区域建立辅助区域,H5用公式:
=TRIM(MID(SUBSTITUTE($G5,",",REPT(" ",200)),(COLUMN(A1)-1)*200+1,200))
数组公式,按组合键(CTRL+SHIFT+回车)完成公式,右拉到L5,再下拉;
B13公式:
=IFERROR(INDEX(B$1:B$7,SMALL(IF(LEN($G$5:$G$7)-LEN(SUBSTITUTE($G$5:$G$7,",",""))+1>=COLUMN($A:$Z),ROW($5:$7)),ROW(A1))),"")
数组公式,按组合键(CTRL+SHIFT+回车)完成公式,右拉到F13,再下拉;
G13公式:
=IFERROR(INDIRECT(TEXT(SMALL(IF(H$5:L$7<>"",ROW($5:$7)*100+COLUMN(H:L)),ROW(A1)),"R0C00"),),"")
数组公式,按组合键(CTRL+SHIFT+回车)完成公式,下拉。