• 跳至主要導覽
  • 跳至主要內容
  • 跳至主要資訊欄
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

讀者互動方式

發佈留言 取消回覆

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

主要資訊欄

搜尋

近期文章

  • hashcat 密碼雜湊快速破解工具使用教學與範例
  • macOS 使用 GPGTools 與 GPG Mail 對郵件加密、解密、簽章、驗證教學與範例
  • wrk:HTTP 網頁伺服器效能測試工具使用教學
  • C++ 語言使用 Crypto++ 實作 RSA 數位簽章教學與範例
  • C++ 語言使用 Crypto++ 實作 RSA-OAEP 搭配 SHA256 加密教學與範例
  • C++ 語言使用 Crypto++ 實作 AES 加密、解密、認證加密教學與範例
  • C++ 語言使用 Crypto++ 實作 MD5、SHA1、SHA2、BLAKE2 雜湊教學與範例
  • Ubuntu Linux 安裝、使用 Crypto++ 加密函式庫教學與範例

推薦網站

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

關注本站

  • 電子郵件
  • Facebook

公益

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

Copyright © 2021 · Office Guide