Excel 2019无VBA实现：基于ARTICLENR的动态UNIT下拉列表

方法一：数组公式+定义名称+数据验证（轻量数据适用） #

操作步骤： #

1. 定义动态名称

   • 点击顶部「公式」选项卡 → 「定义名称」。
   • 名称栏填MatchUnits，范围选「工作簿」，引用位置粘贴以下数组公式：

     =OFFSET(Units!$B$2,SMALL(IF(Units!$A$2:$A$1000=Calculator!$A$2,ROW(Units!$A$2:$A$1000)-ROW(Units!$B$2)),ROW(INDIRECT("1:"&COUNTIF(Units!$A$2:$A$1000,Calculator!$A$2)))),0,1)
     

     注意：
     • 把Units!$A$2:$A$1000改成你实际的ARTICLENR数据范围（比如有1万行就写$A$2:$A$10000）
     • Calculator!$A$2是你输入ARTICLENR的单元格，按需修改
     • 输入完公式后必须按Ctrl+Shift+Enter触发数组公式

2. 设置数据验证下拉

   • 切换到Calculator表，选中要生成下拉的单元格（比如B2）。
   • 点击「数据」选项卡 → 「数据验证」，允许类型选「序列」，来源填=MatchUnits，勾选「提供下拉箭头」后确定。

方法二：辅助列+高级筛选逻辑+数据验证（大数据量适用） #

如果数据行数多，数组公式卡顿，用这个方法：

1. 添加唯一性标记辅助列

   • 在Units表空白列（比如D列）表头写UniqueFlag，D2单元格输入公式：

     =COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1
     

     下拉填充到所有数据行，这个公式会标记每个ARTICLENR对应UNIT的首次出现，确保下拉列表无重复。

2. 定义筛选后的数据范围

   • 同样进入「定义名称」，创建FilteredUnits，引用位置粘贴：

     =OFFSET(Units!$B$2,0,0,SUMPRODUCT((Units!$A$2:$A$1000=Calculator!$A$2)*(Units!$D$2:$D$1000=TRUE)),1)
     

     按Ctrl+Shift+Enter确认，调整数据范围到实际行数。

3. 设置数据验证
   和方法一的步骤2一致，目标单元格数据验证来源填=FilteredUnits即可。

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