一、Excel計算“最終得分”:Trimmean函式法。
功能:返回一組資料的修剪平均值,也可以理解為剔除“異常”資料後再計算平均值。
語法結構:=Trimmean(資料區域,修剪分數)。“修剪分數”其實就是需要去除的個數除以總個數。當去除的數為奇數時,將去除的數向下舍入為最接近2的倍數。
目的:計算參賽人員的“最終得分”。
方法:
在目標單元格中輸入公式:=TRIMMEAN(C3:J3,2/COUNT(C3:L3))。
解讀:
1、公式中C3:J3為需要計算的資料範圍,2為需要去掉的1個“最高分”和1個“最低分”。Count(C3:L3)計算的為實際參與評分的評委數。
2、語法結構中的“當去除的數為奇數時,將去除的數向下舍入為最接近2的倍數。”解讀,例如,將公式中的“2”替換為3或5,則在實際的計算中,按2或4來執行。暨“最高分”和“最低分”必須是“成對”出現的。
3、如果計算的“最終得分”小數位數不符合要求,只需在【設定單元格格式】中設定小數位數即可。
二、Excel計算“最終得分”:Sumproduct函式法。
功能:返回相應的陣列或區域乘積的和。
語法結構:=Sumproduct(陣列1,[陣列2],……,[陣列N])。當只有一個數組時,對陣列內的資料進行求和運算;多個數組時,陣列的維數必須相同。
目的:計算參考人員的“最終得分”。
在目標單元格中輸入公式:=SUMPRODUCT(C$3:E$3,C4:E4)。
1、從示例中可以看出,每個專案(體能,筆試,面試)所佔的比例不同,所以不同專案的成績要分別乘以對應的比例,然後再計算總和。
2、以“王東”為例計算“最終得分”:=C3*C4+D3*D4+E3*E4。由於公式中的C3:E3中的行時絕對引用,所以在計算“小西”的“最終得分”時,公式為=C3*C5+D3*D5+E5,以此類推。
三、對“最終得分”排名:Rank函式法。
功能:返回指定的值在指定範圍內的排名,如果多個值排名相同,則返回平均值。
語法結構:=Rank(排序值,數值範圍,[排序方式]),其中排序方式有兩種,“0”和“1”,“0”為降序,“1”為升序。省略[排序方式]時,預設為降序。
目的:對“最終得分”排名。
在目標單元格中輸入公式:=RANK(M3,M$3:M$9,0)。
在Rank函式的功能中已經說明,“如果多個值排名相同,則返回平均值”其實就是當出現值相同時,“名次”會出現“跳躍”的情況。如75分出現了2次且排名為3,則下一分數的排名為5,而不是4。
四、對“最終得分”排名:Sumproduct函式法。
在目標單元格中輸入公式:=SUMPRODUCT((F$4:F$10>F4)/COUNTIF(F$4:F$10,F$4:F$10))+1。
1、利用Sumproduct函式進行排名,不會出現“跳躍”的情況,如75分出現了2次且排名為3,則下一分數的排名為4,而不是5。
2、公式中F$4:F$10>F4比較形成一個以1和0為值的陣列,COUNTIF(F$4:F$10,F$4:F$10)統計出每個值出現的次數;然後對應的值進行除法運算,形成一個以1和0為值的一維陣列,最後進行求和,公式末尾的“1”為輔助值,因為排名是從“1”開始計算的,也很好理解,因為第1名的值不能“自己大於自己”,所以公式=SUMPRODUCT((F$4:F$10>F4)/COUNTIF(F$4:F$10,F$4:F$10))的計算結果為0。
一、Excel計算“最終得分”:Trimmean函式法。
功能:返回一組資料的修剪平均值,也可以理解為剔除“異常”資料後再計算平均值。
語法結構:=Trimmean(資料區域,修剪分數)。“修剪分數”其實就是需要去除的個數除以總個數。當去除的數為奇數時,將去除的數向下舍入為最接近2的倍數。
目的:計算參賽人員的“最終得分”。
方法:
在目標單元格中輸入公式:=TRIMMEAN(C3:J3,2/COUNT(C3:L3))。
解讀:
1、公式中C3:J3為需要計算的資料範圍,2為需要去掉的1個“最高分”和1個“最低分”。Count(C3:L3)計算的為實際參與評分的評委數。
2、語法結構中的“當去除的數為奇數時,將去除的數向下舍入為最接近2的倍數。”解讀,例如,將公式中的“2”替換為3或5,則在實際的計算中,按2或4來執行。暨“最高分”和“最低分”必須是“成對”出現的。
3、如果計算的“最終得分”小數位數不符合要求,只需在【設定單元格格式】中設定小數位數即可。
二、Excel計算“最終得分”:Sumproduct函式法。
功能:返回相應的陣列或區域乘積的和。
語法結構:=Sumproduct(陣列1,[陣列2],……,[陣列N])。當只有一個數組時,對陣列內的資料進行求和運算;多個數組時,陣列的維數必須相同。
目的:計算參考人員的“最終得分”。
方法:
在目標單元格中輸入公式:=SUMPRODUCT(C$3:E$3,C4:E4)。
解讀:
1、從示例中可以看出,每個專案(體能,筆試,面試)所佔的比例不同,所以不同專案的成績要分別乘以對應的比例,然後再計算總和。
2、以“王東”為例計算“最終得分”:=C3*C4+D3*D4+E3*E4。由於公式中的C3:E3中的行時絕對引用,所以在計算“小西”的“最終得分”時,公式為=C3*C5+D3*D5+E5,以此類推。
三、對“最終得分”排名:Rank函式法。
功能:返回指定的值在指定範圍內的排名,如果多個值排名相同,則返回平均值。
語法結構:=Rank(排序值,數值範圍,[排序方式]),其中排序方式有兩種,“0”和“1”,“0”為降序,“1”為升序。省略[排序方式]時,預設為降序。
目的:對“最終得分”排名。
方法:
在目標單元格中輸入公式:=RANK(M3,M$3:M$9,0)。
解讀:
在Rank函式的功能中已經說明,“如果多個值排名相同,則返回平均值”其實就是當出現值相同時,“名次”會出現“跳躍”的情況。如75分出現了2次且排名為3,則下一分數的排名為5,而不是4。
四、對“最終得分”排名:Sumproduct函式法。
目的:對“最終得分”排名。
方法:
在目標單元格中輸入公式:=SUMPRODUCT((F$4:F$10>F4)/COUNTIF(F$4:F$10,F$4:F$10))+1。
解讀:
1、利用Sumproduct函式進行排名,不會出現“跳躍”的情況,如75分出現了2次且排名為3,則下一分數的排名為4,而不是5。
2、公式中F$4:F$10>F4比較形成一個以1和0為值的陣列,COUNTIF(F$4:F$10,F$4:F$10)統計出每個值出現的次數;然後對應的值進行除法運算,形成一個以1和0為值的一維陣列,最後進行求和,公式末尾的“1”為輔助值,因為排名是從“1”開始計算的,也很好理解,因為第1名的值不能“自己大於自己”,所以公式=SUMPRODUCT((F$4:F$10>F4)/COUNTIF(F$4:F$10,F$4:F$10))的計算結果為0。