2015年9月23日 星期三

Excel-根據雙條件來摘要資料(SUMPRODUCT,OFFSET,LARGE)

前幾天的二篇 Excel 文章,都是使用「陣列公式」來處理:
這次不要使用陣列公式,而是使用 SUMPRODUCT 函數來運算。在下圖右的上下二個摘要表中,上半部是依據一個條件來篩選資料,下半部是依據二個條件來篩選資料。請自行對照以上二篇文章比較其差異。

【公式設計與解析】
為了方便說明,先定義名稱。選取儲存格A1:E25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、客編、品名、規格、數量。
使用 SUMPRODUCT 函數可以不用使用「陣列公式」,有些人對於陣列公式望而卻步或是一之半解無法活用。

(1) 依據一個條件來篩選資料
為方便說明使用一個輔助欄位(K欄)。
儲存格K3:=SUMPRODUCT(LARGE((客編=$H$1)*ROW(日期),ROW(1:1)))-1
客編=$H$1:在 SUMPRODUCT 函數中的「客編」陣列中,判斷是和儲存格H1相同,傳回 TRUE/FALSE 陣列。
(客編=$H$1)*ROW(日期):傳回符合「客編」陣列中是和儲存格H1相同者的列號(以日期陣列來取得列號)所組成的陣列。
LARGE((客編=$H$1)*ROW(日期),ROW(1:1)):利用ROW(1:1)來依序「由大到小」取得上式中列號陣列的數值。ROW(1:1)=1向下複製時會產生ROW(2:2)=2→ROW(3:3)=3→...。
儲存格G3:=IFERROR(OFFSET($A$1,K3,,,),"")
儲存格H3:=IFERROR(OFFSET($C$1,K3,,,),"")
儲存格I3:=IFERROR(OFFSET($D$1,K3,,,),"")
儲存格J3:=IFERROR(OFFSET($E$1,K3,,,),"")
複製儲存格G3:K3,貼至儲存格G3:K13。

(2) 依據二個條件來篩選資料
儲存格K17:
=SUMPRODUCT(LARGE((客編=$H$15)*(品名=$J$15)*ROW(日期),ROW(1:1)))-1
觀察上式,使用 (客編=$H$15)*(品名=$J$15) 來篩選雙條件的結果。
儲存格G17:=IFERROR(OFFSET($A$1,K17,,,),"")
儲存格H17:=IFERROR(OFFSET($D$1,K17,,,),"")
儲存格I17:=IFERROR(OFFSET($E$1,K17,,,),"")
複製儲存格G17:K17,貼至儲存格G17:K25。

【思考一下】
如何依據三個條件、四個條件來篩選資料?

沒有留言:

張貼留言

好康東東