2010年6月16日 星期三

Excel-SUMPRODUCT+INDEX應用

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

image01

輸入公式:

儲存格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, ...,以此類推。

注意:當你需要取得符合搜尋條件的元素之相對位置而非元素本身時,應使用 MATCH 函數,而非 LOOKUP 函數。

 

SUMPRODUCT 函數:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

Array1:必要參數。要先相乘其元件再相加的第一個陣列引數。

Array2, array3,...:選用參數。第 2 個到第 255 個要將元件先相乘再相加的陣列引數。

注意:
各陣列必須有相同的維度 (相同的列數,相同的欄數)。否則 SUMPRODUCT 函數會傳回錯誤值 #VALUE!。
SUMPRODUCT 函數會將所有非數值資料的陣列元素當成 0 來處理。

沒有留言:

張貼留言

好康東東