• 跳至主要導覽
  • 跳至主要內容
  • 跳至主要資訊欄
Office 指南

Office 指南

辦公室工作實用教學

  • Excel
  • Word
  • PowerPoint
  • Windows
  • PowerShell
  • R

Excel 檢測離群值 Outlier,極端值判斷教學

介紹如何使用 Excel 檢測離群值(outlier)或極端值(extreme value),找出可能有問題的資料。


通常若想要檢查資料之中是否存在離群值或極端值,首先會先對資料進行常態性檢定,確認資料來自於常態分布之後,再計算第一四分位數(Q1)、第三四分位數(Q3)與四分位距(IQR),若資料超出 Q1 - 1.5 * IQR 到 Q3 + 1.5 * IQR 這個範圍,就有可能是有問題的資料。

四分位距與離群值

關於四分位距與離群值的解釋,可參考維基百科的說明。

由於 Excel 並沒有提供常態性檢定的工具,建議可以改用 R 等統計工具進行常態性檢定。

計算四分位數(Q1 與 Q3)

假設我們有一些數值資料如下:

數值資料

在檢測離群值之前,首先要使用 QUARTILE 計算 Q1 與 Q3 的值,QUARTILE 的第一個參數就是放原始的資料範圍,而第二個參數若填入 1 則代表計算 Q1,若填入 3 則代表計算 Q3:

=QUARTILE(A2:A21,1)
=QUARTILE(A2:A21,3)

計算的結果如下:

計算 Q1 與 Q3

四分位距(IQR)

接著將 Q3 的值減掉 Q1 的值,就可以算出 IQR:

=E2-E1
計算 IQR

判斷離群值

依照上面介紹的公式,算出正常值的上下界:

=E1-1.5*E3
=E2+1.5*E3
計算上下界

這裡算出來的上下界分別是 25.3 與 68.0 左右,原則上來說,資料只要超出這個範圍,就非常有可能是有問題的。

最後使用 OR 函數,檢查每一筆原始資料是否有超出上界或下界,如果超出去的話就判定為離群值。

=OR(A2<$E$4,A2>$E$5)
判斷離群值

如果感覺單純的文字標示不夠明顯的話,可以使用條件格式化儲存格的方式,將判定為離群值(TRUE)的儲存格塗上顏色。

標示離群值

參考資料:How-To Geek、AbsentData

分類:Excel

讀者互動方式

留言

  1. Boxplot outlier 判斷 表示:

    2023-08-2417:59:59

    以下 20 組資料做 excel boxplot 時 outlier 只有四組, 但以上述公式判斷卻有五組. 是哪出錯了?
    25.8
    331.2
    753.6
    26
    29.3
    28.4
    26.2
    29.3
    29.2
    23.7
    63.2
    30.3
    999.9
    28.9
    31.5
    29.1
    30.1
    26.8
    209.3
    14.7

主要資訊欄

搜尋

近期文章

  • Python 使用 PyAutoGUI 自動操作滑鼠與鍵盤
  • Ubuntu Linux 以 WireGuard 架設 VPN 伺服器教學與範例
  • Linux 網路設定 ip 指令用法教學與範例
  • Windows 使用 TPM 虛擬智慧卡保護 SSH 金鑰教學與範例
  • Linux 以 Shamir’s Secret Sharing 分割保存金鑰教學與範例
  • Linux 以 Cryptsetup、LUKS 加密 USB 隨身碟教學與範例
  • Linux 以 Cryptsetup 與 LUKS 加密磁碟教學與範例
  • Linux 使用 age 簡潔的加密、解密工具使用教學與範例

推薦網站

  • Udemy 線上教學課程
  • Coursera 線上教學課程

關注本站

  • 電子郵件
  • Facebook

公益

  • 家扶基金會
  • 台灣世界展望會
  • Yahoo 奇摩公益
  • igiving 公益網
  • 兒福聯盟

Copyright © 2021 · Office Guide