KutoolsforOffice — 一套方案，五大工具。事半功倍。

如何在 Excel 中计算忽略零值或错误值的中位数？

作者孙修改日期

在众多 Excel 数据分析任务中，准确计算中位数对把握数据集的集中趋势至关重要。然而，当数据集中包含零值或错误值（例如）#DIV/0!、#N/A 等）时，这些干扰项会影响直接的中位数计算。例如，使用标准公式 =MEDIAN(区域)会将零值纳入计算，并在区域中存在任何无效单元格时返回错误，从而导致误导性结果或计算失败，如下图所示。
一张截图，展示在需要计算包含零值和错误值的数据范围的中位数时的情景

为解决此问题，多种解决方案可助您在排除零值或错误值的同时准确计算中位数，确保分析结果既精准又稳健。这些方法适用于各类场景，例如清理调查数据、财务报告或科学测量数据——在这些情况下，剔除零值或错误值是获得有意义结果的关键。以下内容为您提供了 Excel 中每种方法的实用分步指南，涵盖从直接公式到高级自动化技术。

忽略零值的中位数

忽略错误的中位数

VBA：忽略零值和错误的中位数（用户自定义函数）

Power Query：过滤零值/错误后的中位数

蓝色右箭头气泡忽略零值的中位数

当您的区域包含不应纳入中位数计算的零值（例如用 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：

  1. 在 Excel 中单击开发工具选项卡。如果该选项卡不可用，请通过文件 > 选项 > 自定义功能区将其启用。
  2. 单击 Visual Basic，即可打开 VBA 编辑器。
  3. 在 VBA 编辑器中，点击插入 > 模块即可创建一个新模块。
  4. 将以下代码复制并粘贴到该模块中：
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 计算忽略零值和错误的中位数的步骤：

  1. 在您的数据区域中任选一个单元格，然后切换到数据选项卡，单击从表格/区域。如果您的数据尚未设置为表格格式，Excel 将提示您创建表格——点击“确定”即可。
  2. Power Query 编辑器窗口将打开。单击相关列的下拉箭头，取消勾选 0 即可过滤掉零值。（如需清除错误值，请右键单击列标题，选择）删除错误。）
  3. 过滤完成后，单击主页 > 关闭并加载，即可将清理后的数据返回到工作表中。
  4. 现在，可对仅包含筛选后数值的列直接应用标准 =MEDIAN()公式，因为此时数据已排除所有不需要的项。

此方法可确保原始数据毫发无损，同时为新数据或更新数据提供卓越的可重复性，尤其适用于定期报告任务及大型或外部数据集的处理。每当源数据发生变化时，只需单击一次即可刷新 Power Query 工作流，大幅减少手动操作与出错风险。

  • Power Query 可用于 Excel 2016 及更高版本（也可作为 Excel 2010 和 2013 的加载项使用）。
  • 转换后，即可在生成的清理数据上执行计算，为下游分析提供更高的可靠性。

若出现意外结果，请仔细检查 Power Query 中的筛选步骤，并确认清理后的数据仍保留有效数值。

总之，无论您倾向于直接使用数组公式、通过自定义 VBA 解决方案实现自动化，还是借助 Power Query 应对更大规模的工作流自动化，Excel 都为您提供了多种实用选项，轻松计算忽略零值或错误值的中位数。请根据您的数据集规模、更新频率及工作流偏好，选择最合适的方法，确保结果既可靠又精准。

最佳办公效率工具

🤖KUTOOLS AI 助手：基于以下内容革新数据分析：智能执行   |  生成代码|  创建自定义公式  |  数据分析及生成图表|  调用 Kutools Functions……
热门功能：查找、高亮或标记重复项   |  删除空白行   |  合并列或单元格且不丢失数据   |  不使用公式的四舍五入……
高级 LOOKUP：多条件 VLookup  |  多值 VLookup  |   跨多工作表 VLookup   |   模糊查找……
高级下拉列表：快速创建下拉列表   |  级联下拉列表   |  多选下拉列表……
列管理器：添加指定数量的列|移动列|切换隐藏列的可见性状态|比较区域与列……
特色功能：网格聚焦   |  设计视图   |增强编辑栏   | 工作簿和表管理器   |  资源库（自动文本）|  日期提取   |  汇总工作表  |  加密/解密单元格   | 按列表发送邮件   |  超级筛选   |   特殊筛选（筛选粗体单元格/斜体/删除线……） ......
精选 15 工具集：12 文本工具（添加文本，删除特定字符，……）|   50+ 图表 类型（甘特图，……）|   40+ 实用公式（基于生日计算年龄，……）|   19 插入工具（插入二维码，从路径插入图片，……）|   12 转换工具（小写金额转大写，汇率转换，……）|   7 合并和拆分工具（高级合并行，分割单元格，……）|……更多
在您的首选语言中使用 Kutools – 支持英语、西班牙语、德语、法语、中文及 40+ 种其他语言！

使用 Kutools for Excel 大幅提升您的 Excel 技能，体验前所未有的高效。Kutools for Excel 提供 300 多项高级功能，助您提升生产力、节省时间。立即点击此处，获取您最需要的功能……

Office Tab 为 Office 带来标签式界面，让您的工作更轻松

  • 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中启用标签式编辑和阅读。
  • 在同一个窗口的新标签页中打开并创建多个文档，而非在新窗口中。
  • 将您的工作效率提升 50%，每天减少数百次鼠标点击！

所有 Kutools 插件，一个安装程序

Kutools for Office 套件捆绑了适用于 Excel、Word、Outlook 和 PowerPoint 的插件以及 Office Tab Pro，非常适合需要跨多个 Office 应用高效协作的团队。

ExcelWordOutlookTabsPowerPoint
  • 一体化套件— Excel、Word、Outlook 和 PowerPoint 插件 + Office Tab Pro
  • 一个安装程序，一个许可证— 几分钟内完成设置（支持 MSI）
  • 协同效果更佳— 在多个 Office 应用中实现高效协同
  • 30 天全功能试用— 无需注册，无需信用卡
  • 超值之选— 比单独购买插件更省钱