【Excel 教學】如何使用 TRIMMEAN 函數找出離群值？解決多組資料的挑戰！

今天我們要來解決一個網友在 Excel 中常遇到的問題：如何使用 TRIMMEAN 函數來找出資料中的「離群值」（Outliers）？

TRIMMEAN 是一個很棒的函數，可以幫我們計算排除掉極端值後的平均，讓平均數更具代表性。但問題來了，它只會告訴我們結果，卻不會告訴我們「哪些值」被當作離群值排除了。

更進一步的挑戰是，如果我們的資料是分組的（例如，依據不同的產品料號），我們要如何找出「各組內」的離群值呢？這就是我們今天要探討的重點，我們將結合 TRIMMEAN 和 IF 函數，利用陣列公式的概念來完美解決這個問題！

什麼是 TRIMMEAN 函數？

在我們開始實作前，先來快速了解一下 TRIMMEAN 的功能。

根據微軟官方的說明，TRIMMEAN 會傳回資料集內部的平均值。它會從資料集的頂端和底端依指定的百分比來排除資料點，然後再計算剩餘資料的平均值。這個功能在分析含有極端值的資料時非常有用。

函數語法：
=TRIMMEAN(array, percent)

• array：您要計算平均值的數值資料範圍。

• percent：要從計算中排除的資料點百分比。例如，如果您設定為 0.2，Excel 將會從總共 20 個資料點中排除 4 個 (20 x 0.2)，也就是頂端 2 個和底端 2 個。

簡單範例：

假設我們有 10 位同學的考試成績，其中兩位同學的成績特別極端（1 分和 100 分）。

成績
1
20
24
26
20
30
27
29
21
100

• 若使用傳統的 AVERAGE 函數，平均分數會是 30.8。

• 若使用 TRIMMEAN 排除前後各 10% 的極端值（共 20%），公式為 =TRIMMEAN(A2:A11, 0.2)，得到的平均分數會是 25.875，這顯然更能反映大多數同學的成績水準。

步驟一：找出單一資料群組中的離群值

雖然 TRIMMEAN 幫我們算出了去頭去尾的平均值，但要如何標示出哪些是離群值呢？我們可以這樣做：

1. 計算去頭去尾平均值：在 D2 儲存格輸入 =TRIMMEAN(A2:A11,0.2)，得到 25.875。

2. 建立判斷規則：我們可以自訂一個規則，例如，當一個數值大於「去頭去尾平均值」的 1.5 倍，或小於其 0.5 倍時，就將其標示為離群值。（這個倍數可以依據您的資料特性自行調整）

3. 使用 IF 和 OR 函數判斷：在 E2 儲存格輸入以下公式，並向下拖曳。

Generated excel

=IF(OR(A2 > $D$2 * 2, A2 < $D$2 * 0.5), "是", "否")

這樣，1 分和 100 分就會被正確地標示為離群值。

步驟二：【進階挑戰】找出多個群組中的離群值

現在，我們來處理網友真正的問題。如下圖所示，我們有多個「料號」，每個料號對應多筆「price」。我們需要找出每個料號「各自」的離群值。例如，料號 6 的價格中，600 明顯是離群值，但如果跟料號 3 的 105 相比，它可能就不算。

要解決這個問題，我們需要用到陣列公式的概念。

1. 計算各料號的去頭去尾平均值

   我們需要在每一列都計算出「該列料號所屬群組」的去頭去尾平均值。在 N2 儲存格輸入以下公式：

   Generated excel

   =TRIMMEAN(IF(K$2:K$21=K2, L$2:L$21), 0.5)

   公式解析：

   • IF(K$2:K$21=K2, L$2:L$21)：這是整個公式的核心。它會建立一個暫時的陣列。

     • K$2:K$21=K2：判斷整個料號範圍 (K$2:K$21) 是否等於目前這一列的料號 (K2)。

     • 如果等於，就回傳對應的價格 (L$2:L$21)；如果不等，就回傳 FALSE。

   • TRIMMEAN(...)：TRIMMEAN 函數會自動忽略陣列中的 FALSE 值，只針對符合條件的價格進行計算。

   • K$2:K$21 和 L$2:L$21 使用 $ 符號鎖定列，是為了讓我們能方便地向下拖曳公式。

2. 找出各群組的離群值

   有了各群組的平均值後，我們就可以套用步驟一的判斷邏輯。在 O2 儲存格輸入：

   Generated excel

   =IF(OR(L2 > N2 * 2, L2 < N2 * 0.5), "是", "否")

   將公式向下拖曳，您就能看到料號 6 的 600 被正確標示為「是」（離群值）。

總結

透過今天的教學，我們學會了如何從簡單到複雜，一步步運用 TRIMMEAN 來找出資料中的離群值。

• 基礎：TRIMMEAN 能計算排除極端值後的平均數。

• 進階：結合 IF 和 OR，我們可以根據 TRIMMEAN 的結果來標示出離群值。

• 高手：當面對多組資料時，在 TRIMMEAN 中嵌入 IF 陣列判斷式，是解決問題的關鍵！