2015年10月21日 星期三

Excel-查表後多欄位加總(SUMPRODUCT,OFFSET,MATCH)

在 Excel 中,如下圖的資料表,如果想要根據『人員』和『項目』交叉得到一個數值,只要使用類似 INDEX 函數即可完成,但是觀察這個資料表,其中有的項目具有多個重覆,如果要將某個人員的所有相同項目予以加總,該如何處理?
Excel-查表後多欄位加總(SUMPRODUCT,OFFSET,MATCH)

【公式設與解析】
一般使用 INDEX 函數可以很容易求得『人員:卯、項目:甲』的對應結果(儲存格C5):
儲存格M3:=INDEX(B2:J13,MATCH(M1,A2:A13,0),MATCH(M2,B1:J1,0))
但是因為甲有多個項目,如何在一個公式中將多個予以加總?
改用下列公式,儲存格M3:
=SUMPRODUCT(OFFSET(A1,MATCH(M1,A2:A13,0),1,,9)*(B1:J1=M2))
MATCH(M1,A2:A13,0):利用 MATCH 函數,求得儲存格M1(卯)在儲存格A2:A13中位於第幾個。本例傳回:4。
OFFSET(A1,MATCH(M1,A2:A13,0),1,,9):在 OFFSET 函數中,利用上式的傳回值,取得儲存格M1所在的列資料範圍,本例傳回:儲存格B5:J5。
最後,透過 SUMPRODUCT 函數,利用條件 B1:J1=M2,取出儲存格B5:J5中的第 2,4,7 個內容。公式中的「*」乃執行邏輯 AND 運算,運算時會將傳回值 TRUR/FALSE 轉換為 1/0
Excel-查表後多欄位加總(SUMPRODUCT,OFFSET,MATCH)

沒有留言:

張貼留言

好康東東