怎么在EXCEL中把一行变成多列?

10-27

假设原数据在A和B列,如图:

怎么在EXCEL中把一行变成多列?

F2公式:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(PHONETIC(B$2:B$100),",",""),"c",REPT(" ",LEN(SUBSTITUTE(PHONETIC(B$2:B$100),",","")))&"c"),LEN(SUBSTITUTE(PHONETIC(B$2:B$100),",",""))*ROW(A1)+1,LEN(SUBSTITUTE(PHONETIC(B$2:B$100),",",""))))

下拉F2填充柄,复制公式;

E2公式:

=INDEX(A$1:A$100,MAX(ISNUMBER(FIND(F2&",",B$1:B$100&","))*ROW($1:$100)))&""

数组公式,按组合键CTRL+SHIFT+回车完成公式,下拉E2填充柄,复制公式。