Excel如何创建无0值唯一条目、可跨工作表使用的下拉列表

报错原因说明 #

你原有公式嵌套UNIQUE报错是因为OFFSET+INDIRECT的组合使用了易失函数，且COUNTA统计整列B的非空值会超出B10:B59的限定范围，不符合动态数组函数的引用规则。

适用Microsoft 365/Excel 2021及以上版本方案（无需辅助列） #

1. 按快捷键Ctrl+F3打开名称管理器，点击「新建」
2. 名称输入ValidSites，引用位置输入公式：
   =UNIQUE(FILTER(Sites!$B$10:$B$59,Sites!$B$10:$B$59<>0,""))
   公式逻辑：

• FILTER(Sites!$B$10:$B$59,Sites!$B$10:$B$59<>0,"")：筛选B10到B59区域内所有不等于0的值，无符合条件值时返回空
• UNIQUE()：自动对筛选结果去重，返回唯一值列表

3. 点击确定保存命名区域
4. 选中任意工作表需要设置下拉的单元格，点击「数据」选项卡-「数据验证」：

• 允许选择「序列」
• 来源输入=ValidSites
• 勾选「提供下拉箭头」，点击确认即可

旧版Excel兼容方案（无UNIQUE、FILTER函数） #

1. 打开Sites工作表，选择一列空白辅助列（例如D列），在D10单元格输入数组公式，输入完成后按Ctrl+Shift+Enter确认生效：
   =IFERROR(INDEX($B$10:$B$59,MATCH(0,COUNTIF($D$9:D9,$B$10:$B$59)+IF($B$10:$B$59=0,1,0),0)),"")
2. 将D10单元格公式下拉填充到D59，自动生成无0、无重复的站点列表
3. 打开名称管理器新建命名区域，名称输入ValidSites，引用位置输入：
   =OFFSET(Sites!$D$10,0,0,COUNTA(Sites!$D$10:$D$59)-COUNTBLANK(Sites!$D$10:$D$59),1)
4. 按上述数据验证步骤设置下拉列表即可

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