Excel-SUMPRODUCT+INDEX應用－學不完．教不停．用不盡

這次要來練習：在 Excel中如果要根據一個報名費表格，查詢隨機輸入的人員資料中每個人的報名費，進而建立報名費的小計總表。如下圖：

輸入公式：

儲存格D2：=INDEX($F$2:$H$6,MATCH(B2,$F$2:$F$6,0),MATCH(C2,$F$2:$H$2,0))

將儲存格D2複製到儲存格D2：D24。

藉由第一個MATCH函數：MATCH(B2,$F$2:$F$6,0)，查出[檢定]項目在報名費單價表格中的第幾列。

藉由第二個MATCH函數：MATCH(C2,$F$2:$H$2,0))，查出[級別]項目在報名費單價表格中的第幾欄。

然後將欄、列數字送入INDEX函數，交叉查詢到金額。

儲存格G10：=SUMPRODUCT(($B$2:$B$24=$F10)*1,($C$2:$C$24=G$9)*1,$D$2:$D$24)

將儲存格G10複製到儲存格G10：H13。

其中($B$2:$B$24=$F10)*1或是($C$2:$C$24=G$9)*1的*1，乃是要將判斷結果(True、Fasle)轉換成數字(1、0)，如此才能和$D$2:$D$24陣列相乘。

參考資料：

INDEX 函數：傳回表格或範圍內的某個值或值的參照。INDEX 函數有兩種形式：陣列形式及參照形式。

語法：INDEX(array, row_num, [column_num])

Array：必要參數。儲存格範圍或陣列常數。

Row_num：必要參數。選取陣列中傳回值的列。

Column_num：選用參數。選取陣列中傳回值的欄。

MATCH 函數：搜尋某儲存格範圍內的指定項目，然後再傳回該項目在範圍內的相對位置。

語法：MATCH(lookup_value, lookup_array, [match_type])

lookup_value：必要參數。要在 lookup_array 中尋找比對的值。

lookup_array：必要參數。要搜尋儲存格範圍。

match_type：選用參數。這是一個數字，其值有三種可能：-1、0 或 1。

1 或省略	MATCH 函數會找到等於或僅次於 lookup_value 的值。lookup_array 引數內的值必須以遞增次序排列，例如：...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE。
0	MATCH 函數會找第一個完全等於 lookup_value 的比較值。lookup_array 引數內的值可以依任意次序排列。
-1	MATCH 函數會找到等於或大於 lookup_value 的最小值。lookup_array 引數內的值必須以遞減次序排序，例如：TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ...，以此類推。