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

作者Sun修改日期2025-08-13

在許多 Excel 數據分析作業中，準確計算中位數對於掌握資料集的集中趨勢至關重要。然而，當資料集中包含零值或錯誤值（例如）#DIV/0!、#N/A 等）時，這些數值會干擾直接的中位數計算。舉例來說，若使用標準公式 =MEDIAN(範圍)，不僅會將零值納入計算，一旦範圍內存在任何無效儲存格，更會直接傳回錯誤，可能導致誤導性結果或計算失敗，如下圖所示。
顯示在需要計算包含零值與錯誤值之資料範圍的中位數時的螢幕截圖

為解決此問題，有多種方案可協助您在排除零值與錯誤值的情況下計算中位數，確保分析結果既準確又穩健。這些方法適用於各種情境，例如清理問卷資料、財務報表或科學測量數據——當中零值或錯誤值必須剔除，才能得出有意義的結論。以下將為您提供 Excel 中每種方法的實用逐步指南，涵蓋從直接公式到進階自動化技巧。

計算中位數並忽略零值

計算中位數並忽略錯誤值

VBA：計算中位數並忽略零值與錯誤值（使用者自訂函數）

適用於 Excel 的 Microsoft Power Query：篩選零值/錯誤值後再計算中位數

計算中位數並忽略零值

太神奇了！在 Excel 中使用高效 Tabs，就像 Chrome、Edge、Firefox 和 Safari 一樣！
每天為您節省 50% 的時間，並減少數千次滑鼠點擊！

當您在計算中位數時，若資料範圍內包含不希望納入計算的零值（例如以 0 表示的遺漏值），可運用陣列公式予以排除。此方法在零值代表資料缺失、而非實際測量值的資料集中尤為實用。

選取您希望顯示中位數的儲存格（例如 C2），並輸入下列公式：

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

輸入公式後，請勿僅按 Enter，而應按下 Ctrl + Shift + Enter，將其轉換為陣列公式（您會在公式列中看到大括號）。如此一來，系統便會僅針對 A2:A17 中的非零值計算中位數。請參閱下方截圖：
顯示如何在 Excel 中套用忽略零值的中位數公式的螢幕截圖

提示：

若您使用的是 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 中的真正數值——完全忽略所有錯誤儲存格。
顯示如何在 Excel 中套用忽略錯誤值的中位數公式的螢幕截圖

提示與注意事項：

若所有儲存格皆為錯誤值，結果將傳回 #NUM!錯誤—請務必確保資料中至少包含一個有效數字！
您可透過巢狀條件，同時排除零值與錯誤值等多項準則。
當您在處理匯入的資料、問卷結果，或可能包含部分計算錯誤或失敗的財務報表時，此公式格外實用。

VBA：計算中位數並忽略零值與錯誤值（使用者自訂函數）

當您經常需要計算中位數，同時忽略零值與錯誤值，或希望省去手動輸入陣列公式的麻煩時，不妨使用自訂 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 設定中已啟用巨集功能！

適用於 Excel 的 Microsoft Power Query：篩選零值/錯誤值後再計算中位數

適用於 Excel 的 Microsoft Power Query 是 Excel 中強大的資料處理工具，能輕鬆匯入、轉換與分析資料——特別適合在執行中位數等計算前，對大型資料集進行清理與預先處理。透過 Power Query，您可快速篩除零值與錯誤值，確保計算僅包含有效數字。若您的原始資料經常更新或從外部系統匯入，此方法尤為實用。

使用適用於 Excel 的 Microsoft Power Query 計算中位數並忽略零值與錯誤值的步驟：

請先選取您數據區域中的任意儲存格，接著前往資料選項卡，並點選從表格/範圍。若您的資料尚未轉換為表格格式，Excel 將提示您建立表格—請立即按一下「確定」！
適用於 Excel 的 Microsoft Power Query 編輯器視窗將會開啟。點擊相關欄位的下拉箭頭，取消勾選 0，即可篩除零值。（若要篩除錯誤值，請在欄位標題上按一下滑鼠右鍵，並選擇）移除錯誤。）
篩選完成後，點選首頁 > 關閉並載入，即可將清理過的資料送回工作表。
現在，由於資料已排除所有不需的項目，可對僅包含篩選後數值的欄位直接套用標準 =MEDIAN()公式。

此方法能確保原始資料完好無損，並在資料新增或更新時提供高度一致性，特別適合用於定期報表任務或處理大型及外部資料集。當您的來源資料發生變動時，只需單擊一次，即可重新整理適用於 Excel 的 Microsoft Power Query 工作流程，大幅降低手動操作與出錯風險。

適用於 Excel 的 Microsoft Power Query 可用於 Excel 2016 及更新版本（亦可作為 Excel 2010 與 2013 的增益集使用）。
轉換後，即可對所產生的清理資料進行計算，為後續分析提供更高的可靠性。

若出現非預期結果，請重新檢查 Microsoft Power Query for Excel 中的篩選步驟，並確認清理後的資料仍保留有效數值。

總結來說，無論您偏好直接使用陣列公式、建立自訂 VBA 解決方案以實現自動化，還是運用 Microsoft Power Query for Excel 來處理大型工作流程的自動化，Excel 都提供多種實用選項，可計算中位數並忽略零值或錯誤值。請根據您的資料集大小、更新頻率及工作流程偏好，選擇最適合的方法，以確保輸出結果既可靠又準確。

最佳 Office 生產力工具

🤖	KUTOOLS AI 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多