贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

如下圖的 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。

創作者介紹

學不完.教不停.用不盡

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


留言列表 (2)

發表留言
  • 悄悄話
找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼

【 X 關閉 】

【痞客邦】大學生網路社群使用習慣調查

親愛的讀者,痞客邦希望能了解大學生的網路社群使用習慣,
填問卷即可抽獨家好禮喔!
(注意:關閉此視窗將不再出現)