如何在 Excel 中突出顯示統計異常值

在執行數據分析時，您通常假設您的值聚集在某個中心數據點（中位數）周圍。但有時一些值離中心點太遠了。這些值稱為異常值（它們位於預期範圍之外）。異常值可能會扭曲您的統計分析，導致您對數據得出錯誤或誤導性的結論。

您可以使用一些簡單的公式和條件格式來突出顯示數據中的異常值。

識別異常值的第一步是確定範圍的統計中心。要進行此精確定位，您首先要找到第 1 和第 3 個四分位數。甲四分位數是數據組分成四個相等的組的統計分，每組構成的數據的25％。集合的前 25% 被認為是第 1 個四分位數，而後 25% 被認為是第 4 個四分位數。

在 Excel 中，您可以使用 QUARTILE 函數輕鬆獲取四分位數。這個函數需要兩個參數：一個數據范圍和你想要的四分位數。

在顯示的示例中，單元格 E3 和 E4 中的值是范圍 B3:B20 中數據的第 1 和第 3 四分位數。

取這兩個四分位數，您可以通過從第 1 個四分位數中減去第 3 個四分位數來計算數據集的 50% 的統計數據。這個統計上的 50% 被稱為四分位距 (IQR)。圖 9-18 顯示了單元格 E5 中的 IQR。

現在的問題是，一個值離中間 50% 多遠還能被認為是“合理”的值？統計學家普遍同意IQR*1.5可以用來建立合理的上下圍欄：

下圍欄等於第 1 個四分位數 – IQR*1.5。

上圍欄等於第 3 個四分位數 + IQR*1.5。

如您所見，單元格 E7 和 E8 計算最終的上下圍欄。任何大於上圍欄或小於下圍欄的值都被視為異常值。

此時，條件格式規則很容易實現。

要構建此基本格式規則，請執行以下步驟：

選擇目標範圍內的數據單元格（本例中為單元格 B3:B20），單擊 Excel 功能區的“主頁”選項卡，然後選擇“條件格式”→“新建規則”。

這將打開“新建格式規則”對話框。

在對話框頂部的列錶框中，單擊使用公式確定要設置格式的單元格選項。

此選擇根據您指定的公式計算值。如果特定值的計算結果為 TRUE，則條件格式將應用於該單元格。

在公式輸入框中，輸入此處顯示的公式。

請注意，您使用 OR 函數將目標單元格 (B3) 中的值分別與單元格 $E$7 和 $E$8 中的上柵欄和下柵欄進行比較。如果目標單元格大於上圍欄或小於下圍欄，則將其視為異常值，因此將評估為 TRUE，從而觸發條件格式。

=OR(B3<$e$8,b3>$E$7)

請注意，在公式中，您排除了目標單元格 (B3) 的絕對引用美元符號 ($)。如果單擊單元格 B3 而不是鍵入單元格引用，Excel 會自動將單元格引用設為絕對引用。重要的是不要在目標單元格中包含絕對引用美元符號，因為您需要 Excel 根據每個單元格自己的值應用此格式規則。

單擊格式按鈕。

這將打開“單元格格式”對話框，您可以在其中使用一整套選項來設置目標單元格的字體、邊框和填充格式。完成格式選項的選擇後，單擊“確定”按鈕確認更改並返回“新建格式規則”對話框。

返回“新建格式規則”對話框，單擊“確定”按鈕以確認您的格式規則。

如果您需要編輯條件格式規則，只需將光標放在格式化範圍內的任何數據單元格中，然後轉到“主頁”選項卡並選擇“條件格式”→“管理規則”。這將打開條件格式規則管理器對話框。單擊要編輯的規則，然後單擊“編輯規則”按鈕。