如何为超千个BRAND配置关联BRAND MODEL的Excel联动Drop Down List

针对1000+品牌及对应型号的联动需求，以下两种方案无需手动逐个设置范围，直接批量实现：

数据准备前提 #

• 品牌列（如A列）与对应型号列（如B列）需相邻，表头在第1行，数据从第2行开始（示例范围：A2:A1001、B2:B1001）
• 建议先对品牌列升序排序，同品牌型号集中后公式稳定性更高

方案1：兼容旧版Excel（无动态数组功能） #

1. 定义动态名称 #

按 Ctrl + F3 打开「名称管理器」，创建两个名称：

• 名称：Brands
  引用位置：=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)
  作用：自动获取所有非空品牌（跳过表头）
• 名称：Models
  引用位置：=OFFSET($B$2,MATCH(Sheet1!$D$2,$A$2:$A$1001,0)-1,0,COUNTIF($A$2:$A$1001,Sheet1!$D$2),1)
  说明：Sheet1!$D$2 是存放选中品牌的单元格（按需修改），公式会自动定位对应品牌的所有型号范围

2. 设置品牌下拉 #

选中品牌单元格（如D2）→「数据」→「数据验证」→选择「序列」→来源输入 =Brands→确定

3. 设置型号联动下拉 #

选中型号单元格（如E2）→「数据验证」→「序列」→来源输入 =Models→勾选「忽略空值」→确定

方案2：Excel 365/2021 动态数组方案（更简便） #

利用动态数组函数自动处理，无需定义名称：

1. 生成唯一品牌列表 #

在空白列（如C列）输入公式：

=UNIQUE(A2:A1001)

公式会自动溢出所有不重复品牌，无需手动更新

2. 设置品牌下拉 #

选中品牌单元格（如D2）→「数据验证」→「序列」→来源选择动态数组范围（如 C2#）→确定

3. 设置型号联动下拉 #

选中型号单元格（如E2）→「数据验证」→「序列」→来源输入公式：

=FILTER(B2:B1001,A2:A1001=D2,"")

说明：选中品牌后，FILTER会自动筛选出对应型号；未选品牌时显示空列表

批量应用到多行 #

• Excel 365：直接下拉品牌/型号单元格，数据验证会自动适配每行的引用
• 旧版Excel：确保Models名称中的品牌单元格引用为混合引用（如 $D2），再下拉填充数据验证

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