工作表“評語換算得分”對學生的品行以字母形式表示,而每個等級皆有對應的得分,如圖 6.68 所示。要對“成績表”中三個科目的成績加總,即成績加評分。

 

第 6 章\372.xlsx


1. 開啟範例檔案中的資料檔案,選擇 F2:F11 儲存格後輸入以下陣列公式:
=MMULT(TRANSPOSE( 評語換算得分!A$2:A$11=TRANSPOSE(E2:E11))*1, 評語換算得分!B$2:B$11)+SUBTOTAL(9,OFFSET(B2,ROW(2:11)-2,,,COLUMNS(B:D)))


按下(Ctrl)+(Shift)+(Enter)複合鍵後,公式將算出每個學生的總分,如圖 6.69 所示。
 

2.jpg

圖 6.68 評語與得分關係
 

2-2.jpg

圖 6.69 計算每個學生的總分

 


2. 公式說明
本例公式首先用 E2:A11 範圍中每個學生的評語與換算標準中的評語進行比較,產生一個二維陣列,然後與轉換標準中的得分進行矩陣乘積,得到每個學生的評分。

再用 SUBTOTAL 與 OFFSET 組合取得每個學生的會計、出納與電腦三科總分,加上評分即可得到每個學生的最後得分。

 

 

3. 使用注意
① 轉換 F 欄的評語為得分時,必須轉置後再進行比較,得到二維陣列。MMULT 將該二維陣列矩陣乘積後可再算出一維陣列。
② 比較評語時,結果為邏輯值,需要“*1”轉換成數值才能參與矩陣乘積。

 


4. 範例延伸
思考:計算哪一個學生的最後得分最高
提示:相對於本例公式,將得分擴大 100 倍後加上列號,再用 MAX 函數計算最大值,並用 MOD 函數取出列號,最後根據列號參照姓名。
 

 
 

 

 

本文同步刊載於>>

Excel職場函數468招:超完整!新人工作就要用到的計算函數+公式範例集
 

內容涵蓋財務、人事、業務、銷售、倉儲、教育等行業的實例為主,以函數語法剖析為輔,示範 468 個函數的實用範例!
內容由淺入深,每篇範例都是獨立的應用,可依需求查詢及學習!
 


歡迎加入 PCuSER 密技爆料粉絲團

, , , , , , , ,

PCuSER 發表在 痞客邦 PIXNET 留言(0) 人氣()