學不完．教不停．用不盡

(進階版請參考：http://isvincent.pixnet.net/blog/post/46709338)

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

【公式設與解析】

一般使用 INDEX 函數可以很容易求得『人員：卯、項目：甲』的對應結果(儲存格C5)：

儲存格Ｍ3：=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。