2015年11月9日 星期一

Excel-挑出一欄中非空白的儲存格內容(SUMPRODUCT,OFFSET,非陣列公式)

在先前的文章中:Excel-挑出一欄中非空白的儲存格內容(陣列公式,OFFSET),為了挑出非空白的儲存格,使用陣列公式來處理。有網友很熱血的想要使用非陣列公式的方式來處理,今天我也花了一些時間來想想,網友們再看看是否有更恰當的做法。
如下圖,要根據『項目』這一欄中非空白的儲存格,將數值和項目集合至另一欄,該如何處理?
Excel-挑出一欄中非空白的儲存格內容(SUMPRODUCT,OFFSET,非陣列公式)

【公式設計與解析】
(1) ($B$2:$B$24<>"")*ROW($B$2:$B$24))
找出在B欄中不是空白儲存格的列號,本例傳回:{0;0;4;0;6;0;0;9;0;11; ... }。
(2) SUM(--(($B$2:$B$24<>"")*ROW($B$2:$B$24)=0))
找出在B欄中不是空白的儲存格列號等於 0 者有幾個。
(3) SUMPRODUCT(SMALL((第(1)式,第(2)式)+ROW(1:1))
找出在B欄中第1個不是空白儲存格者的列號(除了 0 以外的最小值)。
儲存格D2:=IFERROR(OFFSET($A$1,第(3)式-1,,,),"")
利用 OFFSET 函數取得不是空白儲存格者的列號所對應的儲存格內容,如果查詢不到內容而傳回錯誤訊息,再使用 IFERROR 函數將錯誤訊息轉換為空白。
完整公式:
儲存格D2:=IFERROR(OFFSET($A$1,SUMPRODUCT(SMALL((($B$2:$B$24<>"")*
ROW($B$2:$B$24)),SUM(--(($B$2:$B$24<>"")*ROW($B$2:$B$24)=0))+
ROW(1:1)))-1,,,),"")
同理:
儲存格E2:=IFERROR(OFFSET($B$1,SUMPRODUCT(SMALL((($B$2:$B$24<>"")*
ROW($B$2:$B$24)),SUM(--(($B$2:$B$24<>"")*ROW($B$2:$B$24)=0))+
ROW(1:1)))-1,,,),"")

沒有留言:

張貼留言

檢視其他文章

好康東東