如何在 Excel 中計算中位數時忽略零或錯誤值？

Author Sun Last modified 2025-08-23

在 Excel 的許多數據分析任務中，準確計算中位數對於理解數據集的集中趨勢至關重要。然而，有時候數據集中包含零或錯誤值（例如 #DIV/0!, #N/A等），這些值可能會干擾直接的中位數計算。例如，使用標準公式 =MEDIAN(range) 將會把零包含在計算中，並且如果範圍內存在無效單元格，則返回錯誤，可能導致誤導結果或計算失敗，如下圖所示。
A screenshot showing when calculating the median with zeros and errors included in the data range is needed

為了解決這個問題，有幾種解決方案可以幫助你在排除零或錯誤值的情況下計算中位數，確保你的分析既準確又穩健。這些解決方案適用於各種場景，例如清理調查數據、財務報告或科學測量，其中需要排除零或錯誤值以獲得有意義的結果。下面你將找到每種方法的實用逐步指南，涵蓋從直接公式到高級自動化技術。

中位數忽略零

中位數忽略錯誤

VBA：中位數忽略零和錯誤（UDF）

Power Query：過濾零/錯誤後的中位數

中位數忽略零

太棒了！在 Excel 中像 Chrome、Edge、Firefox 和 Safari 一樣使用 Efficient Tabs！
每天為您節省 50% 的時間，並減少數以千計的滑鼠點擊！

當你的範圍包含不想考慮在中位數計算中的零時——例如表示缺失值的 0——你可以使用陣列公式來排除零。這在零是代表不可用數據的占位符而不是實際測量值的數據集中特別有用。

選擇一個你想顯示中位數的單元格（例如 C2），然後輸入以下公式：

=MEDIAN(IF(A2:A17<>0,A2:A17))

輸入公式後，不要只按 Enter，而是按下 Ctrl + Shift + Enter 使其成為陣列公式（你會看到公式欄中公式周圍出現大括號）。這樣就能確保只有 A2:A17 中的非零值被用於中位數計算。請參見截圖：
A screenshot showing how to apply the median formula in Excel while ignoring zeros

提示：

如果你使用的是 Excel 365 或 Excel 2021 及更高版本，由於支持動態陣列，只需按 Enter 即可。
確保範圍內至少有一個非零數值，否則公式將返回 #NUM! 錯誤。
此解決方案非常適合清理調查回應、費用報告或銷售數據，其中零應該從分析中排除。

中位數忽略錯誤

像 #N/A、#DIV/0! 或 #VALUE! 這樣的錯誤值會導致標準中位數函數返回錯誤，中斷你的數據分析。為了安全地計算中位數並排除這些錯誤，你可以使用以下陣列公式。

選擇任何你想顯示結果的單元格，並輸入以下公式：

=MEDIAN(IF(ISNUMBER(F2:F17),F2:F17))

輸入公式後，按下 Ctrl + Shift + Enter （除非你使用的是 Excel 365/Excel 2021 或更高版本，它們支持動態陣列）。該公式僅包括 F2:F17 中的真實數字——完全忽略任何錯誤單元格。
A screenshot showing how to apply the median formula in Excel while ignoring errors

提示與注意事項：

如果所有單元格都是錯誤值，結果將返回 #NUM! 錯誤——確保你的數據中至少包含一個有效數字。
你可以通過嵌套條件來結合排除標準（例如，同時排除零和錯誤）。
當處理可能包含部分或失敗計算的導入數據、調查結果或財務報表時，該公式特別有用。

VBA：中位數忽略零和錯誤（UDF）

對於需要經常計算中位數並忽略零和錯誤的情況，或者需要避免手動輸入陣列公式的解決方案，你可以使用自定義 VBA 函數（用戶定義函數，UDF）。這種方法提供了額外的靈活性，因為自定義函數可以封裝所有忽略條件，並像任何內置公式一樣使用，非常適合大型或頻繁更新的數據集。

如何設置 UDF：

點擊 Excel 中的開發工具選項卡。如果不可用，請通過文件 > 選項 > 自定義功能區啟用它。
點擊 Visual Basic 打開 VBA 編輯器。
在 VBA 編輯器中，點擊插入 > 模塊創建一個新模塊。
將以下代碼複製並粘貼到模塊中：

Function MedianIgnoreZeroError(rng As Range) As Variant
    Dim cell As Range
    Dim tempList() As Double
    Dim count As Integer
    
    count = 0
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    For Each cell In rng
        If IsNumeric(cell.Value) Then
            If cell.Value <> 0 And Not IsError(cell.Value) Then
                count = count + 1
                ReDim Preserve tempList(1 To count)
                tempList(count) = cell.Value
            End If
        End If
    Next cell
    
    On Error GoTo 0
    
    If count = 0 Then
        MedianIgnoreZeroError = CVErr(xlErrNum)
    Else
        MedianIgnoreZeroError = Application.WorksheetFunction.Median(tempList)
    End If
End Function

如何使用 UDF：
返回 Excel 後，只需在任何單元格中輸入公式 =MedianIgnoreZeroError(A2:A17) （將 A2:A17 替換為你的目標範圍）。與陣列公式不同，你只需要按 Enter——不需要 Ctrl + Shift + Enter.

此方法適用於非常大的數據集，避免了陣列公式的怪癖，並且可以通過進一步編輯代碼來適應忽略其他不需要的值。
如果範圍內僅包含零或錯誤，結果將顯示 #NUM!
如果你收到 #NAME? 錯誤，檢查 VBA 宏是否正確安裝以及 Excel 設置中是否啟用了宏。

Power Query：過濾零/錯誤後的中位數

Power Query 是 Excel 中用於導入、轉換和分析數據的強大工具——尤其是當你的目標是在執行像中位數這樣的計算之前清理和預處理大型數據集時。使用 Power Query，你可以輕鬆過濾掉零和錯誤，確保只有有效數字保留在你的計算中。這種方法特別有益於源數據定期更新或從外部系統導入的情況。

使用 Power Query 計算忽略零和錯誤的中位數的步驟：

選擇數據範圍內的任何單元格，然後轉到數據選項卡並點擊 從表格/範圍。如果數據尚未轉換為表格格式，Excel 將提示你創建表格——點擊確定。
Power Query 編輯器窗口將打開。點擊相關列的下拉箭頭，取消勾選 0 以過濾掉零值。（對於錯誤過濾，右鍵點擊列標題，選擇移除錯誤。）
過濾完成後，點擊主頁 > 關閉並加載將清理後的數據發送回工作表。
現在，將標準 =MEDIAN() 公式應用於僅包含已過濾值的列，因為數據現在已排除所有不需要的項目。

此方法確保原始數據保持不變，對新數據或更新數據具有強大的可重複性，特別適合於定期報告任務或處理大型或外部數據集時使用。Power Query 工作流程可以在源數據更改時通過單擊刷新，最大限度減少手動干預和出錯風險。

Power Query 在 Excel 2016 和更新版本中可用（或者作為 Excel 2010 和 2013 的插件）。
轉換後，可以在清理後的數據上進行計算，為下游分析提供更高的可靠性。

如果出現意外結果，請仔細檢查 Power Query 中的過濾步驟，並確認清理後的數據中仍有有效的數值剩餘。

總之，無論你喜歡直接使用陣列公式、創建自定義 VBA 解決方案進行自動化，還是利用 Power Query 進行更大型的工作流自動化，Excel 都提供了多種實用選項來計算忽略零或錯誤的中位數。選擇最適合你的數據集大小、更新頻率和工作流偏好的方法，以獲得可靠且準確的結果。

最佳 Office 生產力工具

🤖	Kutools AI 助手：以智能執行為基礎，革新數據分析｜生成程式碼｜創建自訂公式｜分析數據並生成圖表｜調用 Kutools 增強函數…
	熱門功能：查找、選取項目的背景色或標記重複值｜刪除空行｜合併列或單元格且不遺失數據｜四捨五入（免公式）...
	高級 LOOKUP：多條件 VLookup｜多值 VLookup｜多表查找｜模糊查找...
	高級下拉列表：快速創建下拉列表｜依賴型下拉列表｜多選下拉列表...
	列管理器：添加指定數量的列｜移動列｜切換隱藏列的顯示狀態｜比較區域及列...
	精選功能：網格聚焦｜設計檢視｜增強編輯欄｜工作簿及工作表管理器｜資源庫（快捷文本）｜日期提取器｜合併資料｜加密/解密儲存格｜按列表發送電子郵件｜超級篩選｜特殊篩選（篩選粗體/傾斜/刪除線...）...
	15 大工具集：12 項文本工具（添加文本、刪除特定字符…）｜50+ 儀表類型（甘特圖等）｜40+ 實用公式（基於生日計算年齡等）｜19 項插入工具（插入QR码、根據路徑插入圖片等）｜12 項轉換工具（金額轉大寫、匯率轉換等）｜7 項合併與分割工具（高級合併行、分割儲存格等）｜...及更多