網友問到：在 Excel 的工作表中有一個資料表格，要轉換出不含空格的資料清單。
參考下圖，類別：甲、乙、丙、丁，各有不同數量的項目。要以連續方式列出不含空格的項目清單。（本例有使用輔助欄位：類別、項目）
【公式設計與解析】
1. 建立輔助欄位（類別、項目）
(A) 類別欄位
假設原始資料中的個類別最多10個項目。
儲存格F2：=OFFSET($A$1,0,INT((ROW(1:1)-1)/10))
複製儲存格F2，貼至儲存格F2:F41。
(1) INT((ROW(1:1)-1)/10)
公式向下複製時，ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
INT 函數為傳回不大於運算式結果的最大整數。
公式向下複製時，INT((ROW(1:1)-1)/10)會傳回：0,0,0,0,0,0,0,0,0,0, 1,1,1,1,1,1,1,1,1,1, 2,2,2,2,2,2,2,2,2,2, 3,3,3,3,3,3,3,3,3,3, …。
(2) OFFSET($A$1,0,INT((ROW(1:1)-1)/10))
根據第(1)的傳回值代入 OFFSET 函數，得到對應的傳回值（甲、乙、丙、丁）。
(B) 項目欄位
儲存格G2：=OFFSET($A$2,MOD(ROW(1:1)-1,10),INT((ROW(1:1)-1)/10))&""
複製儲存格G2，貼至儲存格G2:F41。
(1) MOD(ROW(1:1)-1,10)
公式向下複製時，ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
MOD 函數傳回運算式的餘數。
公式向下複製時，MOD(ROW(1:1)-1,10) 會傳回：0,1,2,3,4,5,6,7,8,9, 0,1,2,3,4,5,6,7,8,9, 0,1,2,3,4,5,6,7,8,9, 0,1,2,3,4,5,6,7,8,9,…。
(2) INT((ROW(1:1)-1)/10)
公式向下複製時，INT((ROW(1:1)-1)/10)會傳回：0,0,0,0,0,0,0,0,0,0, 1,1,1,1,1,1,1,1,1,1, 2,2,2,2,2,2,2,2,2,2, 3,3,3,3,3,3,3,3,3,3, …。
(3) OFFSET($A$2,第(1)式,第(2)式)
根據第(1)式和第(2)式的傳回值代入 OFFSET 函數，得到對應的傳回值（各個類別的第1項、第2項、第3項、…第10項）。
(4) OFFSET($A$2,第(1)式,第(2)式)&""
藉由公式裡串接「""」，可以將傳回值「0」顯示為空字串。（該技巧可用於很多地方）
2. 建立清單
選取儲存格F1:G41，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：類別、項目。
(A) 列出類別
儲存格I2：
{=IFERROR(INDEX(類別,SMALL(IF(項目<>"",ROW(項目),""),ROW(1:1))-1,1),"")}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。
複製儲存格I2，貼至儲存格I2:I41。
(1) IF(項目<>"",ROW(項目),"")
在陣列公式中判斷項目陣列中的每一個項目是否不是空子串，若成立，則傳回其對應的列號，否則傳回空字串。
(2) SMALL(第(1)式,ROW(1:1))
根據第(1)式傳回的列號，利用 SMALL 函數，由小至大依序取出最小值。
(3) INDEX(類別,第(2)式-1,1)
將第(2)式的傳回在 INDEX 函數中以查表方式在類別陣列中查詢對應的儲存格。
(4) IFERROR(第(3)式,"")
若公式傳回錯誤訊息，利用 IFERROR 函數顯示為空白（空子串）。
(B) 列出項目
儲存格J2：
{=IFERROR(INDEX(項目,SMALL(IF(項目<>"",ROW(項目),""),ROW(1:1))-1,1),"")}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。
複製儲存格J2，貼至儲存格J2:J41。
原理說明同「(A) 列出類別」。
【延伸閱讀】
參考：本部落格中其他關於 Excel OFFSET 函數的應用
參考：本部落格中其他關於 Excel INDEX 函數的應用
留言列表