2014年10月6日 星期一

Excel-略過空白儲存格重整資料(OFFSET,陣列公式)

網友想要了解,如下圖,在 Excel 中有一個數值清單,部分儲存格產生了空白,想要排除這些空白儲存格,將非空白儲存格集合在一起,該如何處理呢?

【公式說明】

儲存格B3:{=OFFSET($A$1,0,SMALL(IF($B$1:$O$1<>"",COLUMN($B$1:$O$1),999),
COLUMN(A:A))-1,,)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,自動產生「{}」。

上式相當於:

儲存格B3:=OFFSET($A$1,0,SMALL(IF({11,59,10,0,82,61,0,0,49,30,0,30,18,91}<>"",
{2,3,4,5,6,7,8,9,10,11,12,13,14,15},999),COLUMN(A:A))-1,,)

IF($B$1:$O$1<>"",COLUMN($B$1:$O$1),999):判斷資料陣列中是否不是空白儲存格,成立則取得列號陣列,不成立則給予一個很大的值(本例為999)。

SMALL(IF($B$1:$O$1<>"",COLUMN($B$1:$O$1),999),COLUMN(A:A)):根據上式取得的陣列,取出最小的一個欄號(COLUMN(A:A)=1),當向右複製公式時,可以取得第 2 小、第 3 小、… 的值。

最後,以 OFFSET 函數藉由欄號陣列分別查詢「資料」儲存格陣列中的內容,如此便可列出不包含空白儲存格的內容。

複製儲存格B3,貼至儲存格B3:O3。

沒有留言:

張貼留言

好康東東