Excel全攻略 | 二级联动菜单的4种境界,从基础到动态数组

二级联动菜单，市面上教的基本都是INDIRECT法。但新增选项要改名称范围，太麻烦！本文系统梳理4种境界：INDIRECT静态法、OFFSET动态法、超级表法、动态数组法（365专属）。从手动到全自动，一次学会，终身受益！

Excel 二级联动菜单的4种境界，从基础到动态数组

网上一搜二级菜单，全是INDIRECT。但INDIRECT有个致命伤：新增城市要手动改名称范围。4种境界，层层进化！

一、准备工作

原始数据（A列省份，B列城市）

目标：E2选省份，F2自动出现对应城市下拉菜单。

二、4种境界详解

境界1：INDIRECT静态法（传统方法）

操作：

1. 选中A:B列 → 公式 → 根据所选内容创建 → 勾选“首行”

2. 自动创建名称“北京”“上海”

3. E2数据验证→序列→来源：北京,上海

4. F2数据验证→序列→来源：=INDIRECT(E2)

缺点：新增“广东”，需手动创建名称“广东”

境界2：OFFSET动态法（不进级）

操作：

1. E2数据验证→序列→来源：

   =OFFSET(A1,0,0,COUNTA(A:A)-1,1)

2. F2数据验证→序列→来源：=OFFSET(B1,MATCH(E2,A:A,0)-1,1,COUNTIF(A:A,E2),1)

优点：新增省份/城市自动扩展缺点：公式复杂，大数据量稍慢

境界3：超级表法（推荐）

操作：

1. 选中A:B列 → Ctrl+T转为超级表，命名“省市表”

2. E2数据验证→序列→来源：=SORT(UNIQUE(省市表[省份]))

3. F2数据验证→序列→来源：=FILTER(省市表[城市],省市表[省份]=E2)

优点：自动扩展，公式简洁缺点：需Excel 2021或365

境界4：动态数组法（365专属，终极）

操作：

1. 准备横向数据结构（省份作为列标题）

2. 使用数据验证+CHOOSECOLS公式

3. 或使用LAMBDA自定义函数封装

极致体验：完全自动化，无需任何手动维护

三、4种境界对比

四、实战建议

根据版本选择：

• Excel 2007-2019：境界2（OFFSET动态法）

• Excel 2021/365：境界3（超级表法）

• 追求极致自动化：境界4（动态数组法）

推荐程度：

• 新手：境界1（先学会原理）

• 进阶：境界2（解决维护问题）

• 高手：境界3（简洁高效）

• 极客：境界4（自动化的尽头）

五、常见问题

问题1：境界3的UNIQUE/FILTER不支持怎么办？原因：Excel版本过低解决：升级或使用境界2

问题2：境界2的公式太复杂记不住？解决：做成模版，保存.xltx

问题3：境界1的新增省份如何快速创建名称？技巧：用VBA一键生成，或用境界2替代

六、总结要点

从INDIRECT到动态数组，选对境界，事半功倍