2014年12月4日 星期四

Excel-略過空白儲存格重新列出清單(OFFSET,ISBLANK)

網友提到在 Excel 中,想要將資料清單中空白儲存格濾掉,只留下非空白的儲存格資料(參考下圖),這該如何處理呢?

(1) 使用人工方式

先選取所有含有資料的儲存格,按一下 Ctrl+G 鍵,開啟[到]對話框,接著按一下[特殊]按鈕。

在[特殊目標]對話框中選取「空格」,按一下[確定]按鈕。

在選取的儲存格上按一下右鍵,選取「刪除」。

選取:整列,刪除這些空白列,即可留下非空白的儲存格資料。

 

(2) 利用輔助欄位建立公式

儲存格C2:=IF(ISBLANK(A2),9999,ROW(A2))

使用 ISBLANK 函數來判斷儲存格是否為空白,如果是則傳回9999(給予一個很大的值)。ROW(A2) 傳回儲存格A2所在列(=2)。

複製儲存格C2,往下各列貼上。ROW(A2)→ROW(A3)→ROW(A4)→...,結果傳回 2, 3, 4, ...。

儲存格D2:=SMALL($C$2:$C$20,ROW(1:1))-1

利用 SMALL 函數,根據上式中C欄的傳回值,找出第1, 2, 3, ... 小的值,本例傳回 1, 3, 5, ...。

儲存格E2:=OFFSET($A$1,D2,)

透過 OFFSET 函數,找出相對於儲存格A1的每個儲存格內容。

 

(3) 利用陣列公式

儲存格C2:{=SMALL(IF(NOT(ISBLANK($A$2:$A$20)),ROW($A$2:$A$20),9999)-1,
ROW(1:1))}

儲存格D2:{=OFFSET($A$1,SMALL(IF(NOT(ISBLANK($A$2:$A$20)),
ROW($A$2:$A$20),9999)-1,ROW(1:1)),)}

以上二式為陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,公式會自動產生「{}」。

沒有留言:

張貼留言

好康東東