前段時間在一家企業培訓EXCEL課程的時候，學員小V咨詢如何計算出下方這個表格中，每個部門的考試分，去掉一個最高分和一個最低分之後的平均分。

我們先來介紹如果不分類，隻是計算考試分數列，去掉最大值和最小值之後計算平均值，應該用哪個函數來實現。

這裡需要用到TRIMMEAN函數返回數據集的内部平均值。TRIMMEAN 計算排除數據集頂部和底部尾數中數據點的百分比後取得的平均值。

函數語法：TRIMMEAN(array, percent)

Array：需要進行整理并求平均值的數組或數值區域。

percent：從計算中排除數據點的分數。例如，如果 percent=0.2，從 20 點 (20 x 0.2) 的數據集中剪裁 4 點：數據集頂部的 2 點和底部的2 點。

那這裡的公式要這樣寫：

=TRIMMEAN(C2:C17,2/COUNT(C2:C17))

第1個參數是考試分數所在的數據區域，第2個參數是要去掉幾個數字，比如這裡要去掉1個最大值和1個最小值，也就是2個數字，去除除以這一列的總個數，用COUNT函數計算出總個數，算出百分比。就得到了去掉最大值和最小值之後的平均值。

理解了這個函數的語法之後，我們再來思考學員小V要實現的分類去最大值和最小值算平均值要如何實現。也就是說，第1個參數要是動态的一個區域，根據E4到E7的部門名稱，到C列獲取對應部門的考試分。

這裡為了方便後面的計算，我們先用COUNTIFS的分類計數函數，計算出每個部門的人數。此函數的語法是：

COUNTIFS（條件範圍1，條件1，條件範圍2，條件2，……）

本例中的函數是：=COUNTIFS($A$2:$A$17,E4)

之後我們需要借助動态區域的OFFSET函數來根據E4:E7的部門名稱，到A列中動态選擇對應的部門列表。我們先來看一下OFFSET函數的語法：

OFFSET（起始地址，向上/下偏移的行數，向左/右偏移的列數，返回的行數，返回的列數）

如果是向上和向左偏移就輸入負數。

舉例說明一下這個函數的用法，比如我現在要返回“辦公室”這個部門的員工對應的考試分數所在的區域，也就是C2:C5這個範圍，那我的OFFSET函數就可以這樣來寫：

OFFSET(C1,1,0,4,1)

C1是考試分列的起始地址，第1個參數1是指向下偏移1行，移動到C2；第3個參數0是指沒有向右的偏移；第4個參數4是指一共有4個單元格是辦公室；第5個參數1是指要返回1列的數據，也就是最後的數據區域是C2:C5這個動态的區域。

現在我們就需要把第2個和第3個參數變成動态。

我們需要查詢“辦公室”在A列中的起始位置，這時候就需要用到MATCH這個查詢位置的函數，我們先來看看MATCH函數的語法。

MATCH（查詢值，包含查詢值的1行或1列，0/1）

0：精确查詢 1：區間查詢

本例中的函數是：MATCH(E2,A1:A17,0)

現在我們來把這幾個函數組合在一起，公式如下：

=TRIMMEAN(OFFSET($C$1,MATCH(E4,$A$2:$A$17,0),0,F4,1),2/F4)

到此，就實現了小V需要的去掉最大值和最小值，計算出每類的平均值。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!