學不完．教不停．用不盡

如下圖的 Excel 資料表，其中的表格內容是散亂的資料分佈。要如何取出表格有資料的部分重新排列？

下圖左為原始資料，下圖右為重排後的結果。

【公式設計與解析】

1.

先建立一個輔助欄位，用以計算資料表中每一列有內容儲存格的數量。

儲存格N2：1

儲存格N3：=COUNTA($A$3:L3)+1

計算由儲存格N2起始的累計結果。

複製儲存格N3，貼至儲存格N3:N12。

2.

儲存格P3：=MATCH(ROW(1:1),$N$2:$N$12,1)

藉由輔助欄位的內容，依序列出有資料內容的儲存格在第幾列。

複製儲存格P3，往下各列貼上。

3.

儲存格Q3：{=OFFSET($A$2,P3,SMALL(IF(OFFSET($A$2,P3,0,1,12)<>"",
COLUMN(A:L),99),COUNTIF($P$3:P3,P3))-1,1,1)}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動產生「｛｝」。

複製儲存格Q3，往下各列貼上。

(1) OFFSET($A$2,P3,0,1,12)

根據儲存格P3的內容，取得同列資料的範圍，本例中，儲存格P3=1，資料範圍為儲存格A3:L3。

(2) IF(第(1)式<>"",COLUMN(A:L),99)

在陣列公式中，根據第(1)式傳回的資料範圍，若儲存格有內容，則傳回第幾欄，否則傳回99。(99是一個很大的數值，只要大於一列中的資料數量最大值(本例為12)即可)

COLUMN(A:L)代表 1～12。

本例傳回：{ 99,2,99,99,99,99,99,99,9,10,99,99 }

(3) SMALL(第(2)式,COUNTIF($P$3:P3,P3))

COUNTIF($P$3:P3,P3)用以傳回儲存格P3是同一列中的『第幾個』。(本例中有3個1，傳回 1, 2, 3。)

再利用 SMALL 函數取出『第幾個』的欄號，依序取得欄位 2, 9, 10。

(4) OFFSET($A$2,P3,第(3)式-1,1,1)

最後，將欄號代入 OFFSET 函數取得對應的儲存格內容，依序取得 I, C, H。