2015年12月21日 星期一

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

網友問到:在 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,則公式如何設計。(目前我也還沒有完整的解決方案,待續囉…)

沒有留言:

張貼留言

好康東東