Excel非VBA方案：创建非连续单元格命名列表用于发票关联提案编号

我之前帮同事解决过几乎一模一样的需求，完全能用Excel原生功能搞定，根本不用写VBA代码，下面是一步步的实操方法：

第一步：创建动态筛选的提案编号名称范围 #

我们先把符合条件的提案编号（比如未转化为业务的）定义成一个动态名称，这样列表会自动跟着提案表的内容更新：

1. 切换到你的「提案表」
2. 点击顶部菜单栏的「公式」→「定义名称」
3. 在弹出的窗口里：
   • 名称：随便取个好记的，比如ValidProposalIDs
   • 引用位置：输入下面的公式（注意根据你的实际列调整！假设「是否转化为业务」是F列，编号是D列，表头在第1行）：

     =IFERROR(INDEX(提案表!$D:$D,SMALL(IF(提案表!$F:$F<>"是",ROW(提案表!$D:$D)-MIN(ROW(提案表!$D:$D))+1,""),ROW(INDIRECT("1:"&COUNTIF(提案表!$F:$F,"<>是"))))),"")
     

   • 敲黑板：如果是Excel 2019及更早版本，输入完公式要按Ctrl+Shift+Enter触发数组公式；如果是Excel 365/2021，直接回车就行，它支持动态数组自动识别。
   • 这个公式的作用是：自动提取所有「是否转化为业务」列不是“是”的提案编号，要是没有符合条件的就返回空，而且会自动更新数量。

第二步：给发票表设置数据验证下拉列表 #

现在把这个动态名称用到发票表的单元格里：

1. 切换到「发票表」，选中需要设置提案编号下拉的单元格（比如E列所有需要的单元格）
2. 点击顶部菜单栏的「数据」→「数据验证」
3. 在数据验证窗口里：
   • 允许：选择「序列」
   • 来源：输入=ValidProposalIDs（就是我们刚才定义的名称）
   • 按需勾选「提供下拉箭头」「忽略空值」「输入时显示下拉列表」，这些选项能提升使用体验
4. 点击确定，搞定！现在选中的单元格会出现下拉箭头，里面只有符合条件的提案编号，而且提案表更新后，这个列表会自动同步。

灵活调整说明 #

• 如果你的判断条件不是“未转化为业务”，比如是特定状态或者其他列的条件，只需要修改定义名称时公式里的提案表!$F:$F<>"是"部分，改成你需要的条件就行（比如提案表!$F:$F="否"或者提案表!$E:$E>=DATE(2024,1,1)）
• 要是你只是需要固定的非连续单元格（不是按条件筛选），那可以直接把这些单元格的引用定义成名称，比如=提案表!$D$2,提案表!$D$5,提案表!$D$8，然后在数据验证来源里用这个名称就行，不过这种方式需要手动维护名称内容，没法自动更新。

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