網友問到:在 Excel 有一個編號清單(如下圖左),其中每列的起始編號並不連續,但是每五個一組。想要將編號由一列轉多列呈現,該如何處理?

在下圖中,第一個編號:A000001-A000005,要轉成A000001、A000002、A000003、A000004、A000005,依此類推。

根據不連續編號將一列轉多列(INDEX,ROW,INT,MOD)

 

【公式設計與解析】

為了說明方便,先選取儲存格A1:A21,按 Ctrl+Shift+F3 鍵,定義名稱:編號。

儲存格C2:="A"&RIGHT("000000"&(INDEX(MID(編號,2,6),INT((ROW(1:1)-1)/5)
+1,1))+MOD(ROW(1:1)-1,5),6)

複製儲存格C2,往下各列貼上。

MID(編號,2,6):取得A欄中每一列不含「A」的數字(例如:000001、000002等)

其中 INT((ROW(1:1)-1)/5)+1 和 MOD(ROW(1:1)-1,5) 會產生如下圖的數列。

將以上二式代入 INDEX 函數依序取得 1→2→3→4→5→86→87→88→89→90→…。

RIGHT("000000"&(INDEX函數,6)):將INDEX函數的左側串接 6 個 0,再由右側取出 6 個字元,即可得到 000001→000002→000003→000004→000005→
000086→000087→000088→000089→000090→…。

最後,在上式的左側串接「A」,即大功告成。

根據不連續編號將一列轉多列(INDEX,ROW,INT,MOD)

 

【思考題】

如果每一列的編號個數不是固定為5,則公式如何設計。(目前我也還沒有完整的解決方案,待續囉…)

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(0) 人氣()