怎样根据单元格内容智能显示相应列表供选择（级联菜单）
最近推送的五篇文章：
· 正 · 文 · 来 · 啦 ·
表格不会做，照搬即可；
公式不会写，套用就行。
有一定实操经验的朋友都知道，数据管理中，同样的信息被叫出多个名称是大忌。在《偷懒的技术：打造财务Excel达人》中我们将其总结为一致性原则。在输入数据时，要履行一致性原则，一个很好的工具就是：“数据验证”（2007版以前叫“数据有效性”）-“序列”，我们利用这个功能将填空题转换成单选题，以避免土豆一会被写成洋芋，一会又被称谓马铃薯。
有时候我们会发现，连续两个选择题之间，是有联动关系的。比如我们在当当、天猫等电商平台购物输入地址时，你选定省级单位后，后面的县市级选项，就仅仅是和你选定的省相关的城市。也就是说，县市级的序列是随着省级确定的内容而联动的，这就是本文介绍的联动菜单。效果如下图：
上面的级联菜单是如何实现的呢？
源数据区域如下图A6：C6所示，现需在E2、F2单元格做一个下拉列表框，F2单元格的下拉列表可根据E2单元格的内容变化而变化。
Step01：批量定义名称
选定A1：C6单元格区域，点击“公式选项卡下的根据所选内容创建”，批量创建名称。
Step02：给E2单元格添加数据验证
使用数据验证（数据有效性）给E2单元格添加数据验证
数据验证-序列
Step03：给F2单元格添加序列
数据验证-序列，然后在来源中输入公式：
=INDIRECT(E2)公式解释：
INDIRECT函数是将文本变为引用。
在第一步中我们已经分别定义了名称“重庆市、山东省、江办省”，他们分别对应A2:A6、B2:B6、C2:C6单元格区域，
在上一步我们使用数据验证在E2输入了“重庆市”这些文本字符，在本步骤使用INDIRECT函数，将E2单元格中的文本字符，变为引用，也就是说去引用上一步所定义的”重庆市“这个定义名称（即A2:A6单元格区域）。
按上面的步骤设置后，具体效果如下图：
本方法使用与第一种方法不同的数据结构。
第一步：建立架构表
架构表主要就是定义清楚上下级层级关系（注意，同一级别相同名称信息需排列在一起），如图1：
图1 建立联动架构表
第二步：将各级明细提取唯一值列表
这一步的目的是为了后续制作序列时，每一个项目仅出现一次，提取唯一值的公式及其原理，可参见《“偷懒”的技术2：财务表格轻松做》第125页【提取唯一值列表（顺序）】部分内容，本文不再赘述。
D2单元格的公式为：
=LOOKUP(1,0/FREQUENCY(1,1-COUNTIF($D$1:D1,$A$2:$A$13)),$A$2:$A$13)
提取唯一值效果如图2。
图2 各层级唯一值列表
什么？这就完成2/3了？当然不是，因为第三步，比“把冰箱门关上”复杂多了……
第三步：设置公式
我们先来看如何设置选择省的序列。
由于省级单位已经有了唯一值序列，所以此时我们直接指定相关列表区域即可，但是考虑到扩展性，我们将选择范围的逻辑界定为：
以D2单元格为起点，向下取X行，X为非空单元格个数。
用公式表达为：
=OFFSET(D2,0,0,COUNTA(D2:D13),1)
【注意：COUNTA只能排除真正意义上的空值单元格（即单元格没有输入任何内容），如果是通过公式生成的空格，该函数会“选择性失明”将其视为非空格。】
此时我们选择需要录入省级名称的单元格（假设为G2单元格），依次点击【数据】选项卡-【数据验证】-【数据验证】，将“允许”设置为“序列”，并在来源中输入上述公式即可（如图3）。
图3 省级序列公式
最后就是相对复杂一些的选择城市的序列。
同样的，城市序列也是要在E2:E13的序列里截取。但是，此时选择范围的需要与省份具有隶属关系。从图3中可以看出两个规律：
1、E列中每个省份第一次出现的城市，刚好与A列中该省份第一次出现的行次相同。
2、E列中每个省份的个数，刚好与A列中该省份的个数相同。
所以，城市的截取逻辑就可以概括为：以E1单元格为起点，从向下第X行开始截取，一共截取Y行。其中X是G2单元格所选省份在A2:A13单元格区域中第一次出现的位置，Y为G2单元格所选的省份在A2:A13单元格区域的个数。
用公式表达为：=OFFSET(E1,MATCH(G2,A2:A13,0),0,COUNTIF(A2:A13,G2),1)
公式已经确定，我们参照G2单元格设置方式对H2单元格进行设置，即可完成二级联动菜单的设置了。
定义名称
数据验证
INDIRECT函数
OFFSET函数
MATCH函数
Excel畅销书推荐：
《“偷懒”的技术2：财务Excel表格轻松做》
《“偷懒”的技术：打造财务Excel达人》
2017年当当网畅销榜Excel类第一名，办公类第二名，好评率99.8%，学Excel必选书籍！
👇滑动下面的列表查看更多
如何正确使用本公众号，学习Excel技巧，提高工作效率
怎样才算精能Excel？看完再也不敢在简历上写精通Excel了！
用sumif对超15位的代码条件求和居然出错了，原因是...