Excel散点图如何设置公式自动排除系列值中的#NUM!错误单元格？

可以实现，有两种通用方案可选：

方案1：动态命名范围（适合Excel 365/2021及以上版本） #

无需新增辅助列，数据源会自动动态过滤：

• 按Ctrl+F3打开名称管理器，点击「新建」
• 第一个名称设为有效X值，引用位置填写公式（假设X值在A列、Y值在B列，自行替换为实际列和工作表名）：
  =FILTER(Sheet1!A1:A4307,NOT(ISERROR(Sheet1!B1:B4307)))
• 再新建第二个名称有效Y值，引用位置填写：
  =FILTER(Sheet1!B1:B4307,NOT(ISERROR(Sheet1!B1:B4307)))
• 右键散点图选择「选择数据」，选中对应数据系列点击「编辑」，X值输入=你的工作簿名称.xlsx!有效X值，Y值输入=你的工作簿名称.xlsx!有效Y值，确认后即可生效

方案2：辅助列（兼容所有Excel版本） #

操作更简单，低版本也能稳定运行：

• 新增两列辅助列，比如C列存过滤后的X值、D列存过滤后的Y值
• C1单元格输入公式，下拉填充到4307行：
  =IF(ISERROR(B1),NA(),A1)
• D1单元格输入公式，下拉填充到4307行：
  =IF(ISERROR(B1),NA(),B1)
• 将散点图的数据源替换为C、D列的1-4307行即可，Excel图表会自动忽略#N/A值，不会在图中展示也不会占用坐标位置

注意事项 #

• 不能直接在图表系列值的输入框中嵌入复杂数组公式，Excel不支持该用法，必须通过命名范围或辅助列实现
• 如果只需要排除#NUM!类错误、保留其他错误方便排查，可以把判断逻辑换成：
  =IF(AND(ISERROR(B1),ERROR.TYPE(B1)=6),NA(),B1)
  其中ERROR.TYPE返回6就对应#NUM!错误

内容的提问来源于stack exchange，提问作者megaprimatus