2015年8月24日 星期一

Excel-略過空白儲存格重整資料(多欄,陣列,INDEX)

有讀者根據這篇:Excel-略過空白儲存格重整資料(陣列,OFFSET)文章,想要將下圖左含有空白列的資料清單改成去除空白的資料清單(下圖右),該如何處理?
第(1)式
儲存格D2:{=SMALL(IF($A$1:$A$17<>"",ROW($A$1:$A$17),999),ROW(2:2))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」
IF(廠商<>"",ROW(廠商),999):判斷廠商陣列中是否不為空白儲存格,若成立則取得列號陣列,不成立則給予一個很大的值(本例為999)。本例結果為{2,3,5,6,7,…}。
SMALL(IF(廠商<>"",ROW(廠商),999),ROW(1:1)):根據上式取得的列號陣列,取出最小的一個列號(ROW(2:2)=2),當向下複製公式時,可以依序取得較小的第 2、3、…之值。
第(2)式
儲存格D2:{=INDEX($A$1:$B$17,第(1)式,COLUMN(A:A))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」
COLUMN(A:A)=1,當向右複製公式時,會變為 COLUMN(B:B)=2。
透過 INDEX 函數利用查表方式取得A欄中的第一筆資料「子」,而看到的「#REF!」是錯誤訊息,乃因位址參照錯誤產生查詢不到資料。
複製儲存格D2,貼至儲存格D2:E17。
第(3)式
儲存格D2:{=IFERROR(第(2)式,"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」
透過 IFERROR 函數將產生的錯誤訊息(#REF!)轉換為空白。
複製儲存格D2,貼至儲存格D2:E17。

沒有留言:

張貼留言

好康東東