2014年3月26日 星期三

Excel-查表計算小計(OFFSET,MATCH,SUMPRODUCT)

有網友問到類似以下的問題:參考下圖右的一個資料表,其中人員的排列是隨機的,每人有三個分數,比重分別為1,3,5,如何在下圖左中查詢人員名稱以對應計算出小計。

【準備工作】

選取儲存格D2:G11,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、分數1、分數2、分數3。

選取儲存格D1:G1,按 Ctrl+Shift+F3 鍵,勾選「最左欄」,定義名稱:比重。

【輸入公式】

小計部分其實可以先算好(=分數1X1+分數2X3+分數3X5),再由查表方式來取得結果。本次來練習比重可以隨時改變,並且用 SUMPRODUCT 函數來獲取結果。

儲存格B3:=SUMPRODUCT((OFFSET($E$3,MATCH(A3,人員,0)-1,,,3))*比重)

MATCH(A3,人員,0):找出儲存格A3的人名(a1)在人員陣列中的列數,例如a1在第7列。

OFFSET($E$3,MATCH(A3,人員,0)-1,,,3)):找出儲存格A3的人名(a1)所對應的分數儲存格,例如a1對應到儲存格E9:G9。

最後,使用 SUMPRODUCT 函數將上式中的儲存格陣列儲存格E9:G9)和比重陣列取相乘積,即為所求。

沒有留言:

張貼留言

檢視其他文章

好康東東