如图:
G2用公式:
=IFERROR(INDIRECT(TEXT(SMALL(IF(B$2:E$100<>"",ROW($2:$100)),ROW(A1)),"R0C1"),),"")
H2用公式:
=IFERROR(INDIRECT(TEXT(RIGHT(SMALL(IF(B$2:E$100<>"",ROW($2:$100)*100+COLUMN(B:E)),ROW(A1)),2),"R1C0"),),"")
以上两个公式都是数组公式,需要按组合键(CTRL+SHIFT+回车)完成公式,再下拉。