2016年8月27日 星期六

Excel-取出表格有資料的部分重新排列(OFFSET,VLOOKUP,COUNTIF)

如下圖的 Excel 資料表,其中的表格內容是散亂的資料分佈。要如何取出表格有資料的部分重新排列?
下圖左為原始資料,下圖右為重排後的結果。
Excel-取出表格有資料的部分重新排列(OFFSET,VLOOKUP,COUNTIF)
【公式設計與解析】
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。

沒有留言:

張貼留言

好康東東