Excel数据验证下拉列表筛选：排除指定列值为0或错误值的行

嗨，我来帮你搞定这个问题！你想要的是让下拉列表只显示对应Times列（也就是你说的H列）值不为0、也不是#VALUE!/#N/A的任务，之前用OFFSET+COUNTIF没成功，主要是因为条件范围选错了，而且COUNTIF没法直接处理排除错误值的复合条件。下面分两种场景给你解决方案：

Excel 365/2021及以后版本（推荐） #

这个版本支持动态数组函数FILTER，实现起来最简洁：

• 假设你的任务名称在A列（A2:A100，A1是表头），对应的值在H列（H2:H100），直接用下面的公式生成符合条件的任务列表：
  =FILTER(A2:A100, (H2:H100<>0)*NOT(ISERROR(H2:H100)))
• 公式解释：
  • H2:H100<>0：筛选掉H列值为0的行
  • NOT(ISERROR(H2:H100))：筛选掉H列是#VALUE!/#N/A这类错误值的行
  • *相当于逻辑“与”，只有同时满足两个条件的任务才会被保留
• 操作步骤：
  1. 找一个空白单元格（比如J2）输入上面的公式，它会自动生成所有符合条件的任务列表
  2. 给需要设置下拉列表的单元格打开数据验证，选择“序列”，在“来源”里引用J2开始的动态范围（直接把公式粘贴到来源框里更省事）

旧版Excel（无动态数组功能） #

如果你的Excel版本不支持FILTER，可以用OFFSET+SUMPRODUCT组合，或者加辅助列：

方法1：直接用数组公式 #

在数据验证的“来源”框里输入下面的公式（输入完成后按Ctrl+Shift+Enter确认，旧版必须这样触发数组计算）：
=OFFSET(A$2,0,0,SUMPRODUCT((H2:H100<>0)*NOT(ISERROR(H2:H100))),1)

• 这里SUMPRODUCT会计算同时满足“H列≠0”和“H列无错误值”的行数，OFFSET就会基于这个行数截取对应的任务列范围，只保留符合条件的选项。

方法2：辅助列法（更直观） #

1. 插入一个空白辅助列（比如I列），在I2输入公式：
   =IF(AND(H2<>0,NOT(ISERROR(H2))),ROW(H2)-ROW(H$2)+1,"")
   这个公式会给符合条件的行标记序号，不符合的留空
2. 然后在数据验证的“来源”里输入：
   =OFFSET(A$2,0,0,COUNT(I:I),1)
COUNT(I:I)会统计辅助列里非空单元格的数量，也就是符合条件的任务数，OFFSET就会只取对应数量的任务行。

为啥你之前的公式没用？ #

你之前写的COUNTIF(A1:A100,"<>0")是在统计A列里不等于0的单元格数，但你要筛选的是H列的条件，范围完全错了；而且COUNTIF无法识别错误值，所以不管H列有没有错误，它都会返回整个A列的非空行数，自然下拉列表会显示所有任务。

备注：内容来源于stack exchange，提问作者MomoCode