J2=INDIRECT(TEXT(SMALL(IF($B$2:$G$16<>"",ROW($B$2:$G$16)*100+1,4^8),ROW(A1)),"r0c00"),)&INDIRECT(TEXT(MOD(SMALL(IF($B$2:$G$16<>"",ROW($B$2:$G$16)*100+COLUMN($B$2:$G$16),4^8),ROW(A1)),100)+100,"r0c00"),)&INDIRECT(TEXT(SMALL(IF($B$2:$G$16<>"",ROW($B$2:$G$16)*100+COLUMN($B$2:$G$16),4^8),ROW(A1)),"r0c00"),)&IF(ROW(A1)>COUNT($B$2:$G$16),"","支")
数组公式向下复制【数组公式，公式输完后，光标放在公式编辑栏同时按下CTRL+SHIFT+回车键，使数组公式生效】
也可以由下面数组公式
=IFERROR(INDEX(IF($B$2:$H$16="","",$A$2:$A$16&$B$1:$H$1&$B$2:$H$16&"支"),INT(SMALL(IF($B$2:$H$16="",4^8,ROW($A$1:$G$15)+COLUMN($A$1:$G$15)/1000),ROW(A1))),MOD(SMALL(IF($B$2:$H$16="",4^8,ROW($A$1:$G$15)+COLUMN($A$1:$G$15)/1000),ROW(A1)),1)*1000),"")
如图
A3=MID(J3,1,MIN(IFERROR(FIND($B$2:$H$2,J3),100)-1))
数组公式
B3=IF(FIND(B$2,$J3&B$2)>=LEN($J3),"",--SUBSTITUTE(MID($J3,FIND(B$2,$J3)+LEN(B$2),100),"支",""))
前面的表格数据结构很好啊！为什么要变成后面的？
正常情况下的EXCEL不是这样玩的！
你来问这样的问题估计是新手吧，还是思考一下思路革新吧