如何（以及為什麼）在excel中使用outliers函式
離群值是一個明顯高於或低於資料中大多數值的值。當使用Excel分析資料時，異常值會使結果產生偏差。例如，資料集的平均值可能真正反映了您的值。Excel提供了一些有用的函式來幫助管理異常值，讓我們來看看。
一個簡單的例子
在下圖中，異常值很容易發現分配給Eric的值2和分配給Ryan的值173。在這樣的資料集中，手動發現和處理這些異常值非常容易。
在一組更大的資料中，情況並非如此。能夠識別異常值並將其從統計計算中刪除是很重要的，這也是我們將在本文中討論的方法。
如何在資料中發現異常值
要查詢資料集中的異常值，我們使用以下步驟：
- 計算第1和第3個四分位數（我們將稍微討論一下它們是什麼）。
- 評估四分位範圍（我們還將進一步解釋這些）。
- 返回資料範圍的上限和下限。
- 使用這些邊界來標識外圍資料點。
下圖中資料集右側的單元格區域將用於儲存這些值。
我們開始吧。
第一步：計算四分位數
如果你把你的資料分成四分之一，每一組資料都被稱為四分位數。這個範圍內最低25%的數字組成第一個四分位數，下一個25%組成第二個四分位數，依此類推。我們首先採取這一步，因為最廣泛使用的離群值定義是一個數據點，它比第一個四分位數低1.5個四分位數區間（IQR），比第三個四分位數高1.5個四分位數區間。為了確定這些值，我們首先要弄清楚四分位數是什麼。
Excel提供了一個四分位數函式來計算四分位數。它需要兩條資訊：陣列和夸脫。
=QUARTILE(array, quart)陣列是要計算的值的範圍。四分位數是一個表示要返回的四分位數的數字（例如，1表示第一個四分位數，2表示第二個四分位數，依此類推）。
注意：在Excel 2010中，Microsoft釋出了四分位公司以及四分位數.EXC函式作為四分位函式的改進。當在多個版本的Excel中工作時，QUARTILE更向後相容。
讓我們回到示例表。
為了計算第一個四分位數，我們可以在單元格F2中使用以下公式。
=QUARTILE(B2:B14,1)當您輸入公式時，Excel會為quart引數提供一個選項列表。
為了計算第三個四分位數，我們可以輸入一個類似於F3單元格中前一個四分位數的公式，但使用三而不是一。
=QUARTILE(B2:B14,3)現在，我們得到了單元格中顯示的四分位資料點。
第二步：評估四分位區間
四分位間距（或IQR）是資料中數值的中間50%。它被計算為第一個四分位值和第三個四分位值之間的差值。
我們將在F4單元格中使用一個簡單的公式，從第3個四分位數中減去第1個四分位數：
=F3-F2現在，我們可以看到顯示的四分位間距。
第三步：返回上下限
下限和上限是我們要使用的資料範圍的最小值和最大值。任何小於或大於這些界限值的值都是異常值。
我們將透過將IQR值乘以1.5，然後從Q1資料點中減去它來計算單元格F5中的下限：
=F2-(1.5*F4)
注意：這個公式中的括號是不必要的，因為乘法部分將在減法部分之前計算，但它們確實使公式更容易閱讀。
為了計算單元格F6中的上限，我們將IQR再次乘以1.5，但這次將其新增到Q3資料點：
=F3+(1.5*F4)
第四步：識別異常值
現在我們已經設定了所有的基礎資料，現在是時候確定我們的外圍資料點了，即低於下限值或高於上限值的資料點。
我們將使用OR函式執行此邏輯測試，並透過在單元格C2中輸入以下公式來顯示滿足這些條件的值：
=OR(B2<$F$5,B2>$F$6)
然後我們將這個值複製到我們的C3-C14細胞中。真值表示異常值，如您所見，我們的資料中有兩個異常值。
計算平均值時忽略異常值
使用四分位函式，讓我們計算IQR，並使用最廣泛使用的離群值定義。但是，當計算一系列值的平均值並忽略異常值時，有一個更快更容易使用的函式。這種技術不會像以前那樣識別異常值，但它允許我們靈活地處理我們可能認為的異常值部分。
我們需要的函式名為TRIMMEAN，您可以在下面看到它的語法：
=TRIMMEAN(array, percent)陣列是要求平均值的值範圍。百分比是要從資料集的頂部和底部排除的資料點的百分比（可以輸入百分比或十進位制值）。
我們將下面的公式輸入到示例中的單元格D3中，以計算平均值並排除20%的異常值。
=TRIMMEAN(B2:B14, 20%)
這裡有兩個不同的函式來處理異常值。無論您是要根據某些報告需要確定它們，還是要將它們從計算（如平均值）中排除，Excel都有一個功能來滿足您的需要。
- 發表於 2021-04-04 02:15
- 閱讀 ( 42 )
- 分類：網際網路
你可能感興趣的文章
更快地搜尋excel電子表格：用索引和匹配替換vlookup
還在用VLOOKUP？以下是索引和匹配如何提供更好的解決方案。 ...
解決並解釋了3個複雜的excel提取問題
...microsoftexcel的複雜單元格中提取資訊而掙扎。我寫的關於如何用這個函式從Excel中提取數字或文字的文章中的許多評論和問題證明了這一點。顯然，如何從Excel工作表中分離出所需的資料並不總是很清楚。 ...
4個excel查詢功能，有效搜尋電子表格
... 一旦你知道如何使用查詢在Excel中搜索，不管你的電子表格有多大，你總能在Excel中找到一些東西！ ...
3個瘋狂的excel公式，做了驚人的事情
... 讓我們深入瞭解如何使用公式和其他工具更好地使用Microsoft Excel。 ...
關於在excel中編寫vba巨集的初學者教程（以及學習的原因）
...給了您更多的功能。您可以確切地告訴Excel要做什麼以及如何做。您還可以訪問更多的功能和能力。如果您經常使用Excel，那麼學習如何建立VBA巨集是值得的。 ...
如何在excel中合併和取消合併單元格：技巧和竅門
... 我們將瞭解如何在Excel中合併單元格，如何取消合併，以及另一種將兩個單元格合併為一個單元格而不丟失單元格中資料的方法。 ...
在vba中理解excel單元格與range函式
...題是，在高階階段，大多數人很難理解這些函式實際上是如何工作的。使用它們會讓人非常困惑。以下是你如何以你可能從未想象過的方式利用它們。 ...
透過本課程掌握excel電子表格函式、公式和資料分析
在任何行業，團隊中都需要知道如何管理和研究資料的人員。資料分析技能可幫助您瞭解客戶資料並做出明智的業務決策。microsoftexcel是一種電子表格軟體，它可以讓您將資料製表、分析和視覺化。 ...
excel中標準差的計算
... 如何利用insert函式計算excel中的標準差 ...
如何在excel中計算唯一值
...一值的數量，最好編寫一個公式。我們將在下面向您展示如何做到這一點。 ...
