跳至主要内容

Kutools for Office — 一套工具，五种功能。事半功倍。

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

Author Sun Last modified

在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

为了解决这个问题，有几种方案可以帮助您在排除零值或错误的情况下计算中位数，确保分析既准确又稳健。这些解决方案适用于各种场景，如清理调查数据、财务报告或科学测量，其中需要去除零值或错误以获得有意义的结果。下面，您将找到每种方法在Excel中实用的逐步指南，从直接公式到高级自动化技术。

中位数忽略零值

中位数忽略错误

VBA：中位数忽略零值和错误（UDF）

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

arrow blue right bubble 中位数忽略零值

当您的数据区域包含不想考虑在中位数计算中的零值时——比如表示缺失值的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! 错误。
  • 此解决方案非常适合清理调查响应、费用报告或销售数据，其中零值应从分析中排除。

arrow blue right bubble 中位数忽略错误

诸如 #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! 错误——确保您的数据至少包含一个有效数字。
  • 您可以通过嵌套条件组合排除标准（例如，同时排除零值和错误）。
  • 此公式在处理可能包含部分或失败计算的导入数据、调查结果或财务报表时特别有用。

arrow blue right bubble VBA：中位数忽略零值和错误（UDF）

对于需要频繁计算中位数同时忽略零值和错误，或者需要避免手动输入数组公式的场景，您可以使用自定义 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 设置中启用了宏。

arrow blue right bubble 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 提供了多种实用选项来计算中位数，同时忽略零值或错误。选择最适合您的数据集大小、更新频率和工作流程偏好的方法，以获得可靠而准确的结果。

最佳Office办公效率工具

🤖 Kutools AI 助手：以智能执行为基础，彻底革新数据分析 |代码生成 |自定义公式创建|数据分析与图表生成 |调用Kutools函数……
热门功能：查找、选中项的背景色或标记重复项 | 删除空行 | 合并列或单元格且不丢失数据 | 四舍五入……
高级LOOKUP：多条件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中进行多标签编辑与阅读。
  • 在同一个窗口的新标签页中打开和创建多个文档，而不是分多个窗口。
  • 可提升50%的工作效率，每天为您减少数百次鼠标点击！

所有Kutools加载项，一键安装

Kutools for Office套件包含Excel、Word、Outlook和PowerPoint的插件，以及Office Tab Pro，非常适合跨Office应用团队使用。

Excel Word Outlook Tabs PowerPoint
  • 全能套装——Excel、Word、Outlook和PowerPoint插件+Office Tab Pro
  • 单一安装包、单一授权——数分钟即可完成设置（支持MSI）
  • 协同更高效——提升Office应用间的整体工作效率
  • 30天全功能试用——无需注册，无需信用卡
  • 超高性价比——比单独购买更实惠