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

注重:當你需要獲得合適搜索前提的元素之相對位置而非元素自己時,應利用 MATCH 函數,而非 LOOKUP 函數。

 

SUMPRODUCT 函數:傳回各陣列中所北京拓展公司有對應元素乘積的總和。

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

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

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

注重:
各陣列必需有不異的維度 (不異的列數,不異的欄數)。不然 SUMPRODUCT 函數會傳回毛病值 #VALUE!。
SUMPRODUCT 函數會將所有非數值資料的陣列元素當做 0 來處置。

創作者先容 vincent

學不完.教不斷.用不盡

  • 全站分類:不設分類
  • 小我分類:課本資料
  • 此分類上一篇: Excel-SUMIF+萬用字元
  • 此分類下一篇: Excel-SUMIFS+COUNTIFS利用
  • 上一篇: Excel-SUMIF+萬用字元
  • 下一篇: 離線利用Office 2010的申明
汗青上的今天
  • 2015: 在Google表單的答復內容中增添統計和闡發的公式
  • 2014: Excel-只計較日期清單中本週的金額(SUMPRODUCT,WEEKDAY)
  • 2010: 離線利用Office 2010的申明
▲top