亲，戳上面的蓝字关注我们哦！
小伙伴们好哈，今天和大家一起学习一下如何制作动态引用的二级菜单。
先准备好数据源——不同区域的客户对照表：
需要在销售汇总表中动态引用区域，以及不同区域的客户姓名：
接下来就是自定义名称的环节了：
依次点击【公式】选项卡，【定义名称】，自定义名称“区域”，在引用位置文本框内写上公式：
=OFFSET($A$1,,,,COUNTA($1:$1))
COUNTA($1:$1)的意思是计算第一行内不为空的单元格个数。
OFFSET函数的基本语法是：
=OFFSET(基点,向下偏移行数,向右偏移列数,新引用行数,新引用列数)
本例中第二至第四参数省略，意思是以A1为基点，向下偏移的行数为0，向右偏移的列数为0，新引用的列数为COUNTA($1:$1)的计算结果。
这样的话，基础数据增加，引用范围就会自动扩展了。
为“销售汇总表”工作表客户区域的所在列设置数据有效性：
第一步完成了，依次点击【公式】选项卡，【定义名称】，自定义名称“姓名”，在引用位置文本框内写上公式：
=OFFSET(客户对照表!$A$2,,MATCH($B2,客户对照表!$1:$1,)-1,COUNTA(OFFSET(客户对照表!$A$2,,MATCH($B2,客户对照表!$1:$1,)-1,100)))
这个公式有点小复杂，咱们慢慢解析，实在看不懂的话也不用急，可以先收藏下来，以后随着函数功力不断增强，理解也就是水到渠成的事儿了：
MATCH($B2,客户对照表!$1:$1,)部分，返回“销售汇总表”B2单元格（也就是客户区域）在“客户对照表”工作表第一行的位置。
如果B2是“华中区”，则MATCH函数返回结果为2：
MATCH函数返回的结果作为OFFSET函数的列偏移参数。
再来看下面这一部分：
OFFSET(客户对照表!$A$2,,MATCH($B2,客户对照表!$1:$1,)-1,100)
意思是：
以客户对照表!$A$2为基点，
向下偏移行数为0行，
向右偏移列数为MATCH函数计算结果减1，
新引用的行数为100。
这里的100可以写成一个较大的数值，只要能保证比你的实际数据最大行数多一些就可以。
这样就等于引用了客户区域所在列100行的范围。
再用COUNTA函数计算客户区域所在列100行的范围内有多少个非空单元格。
COUNTA函数得到的结果再作为最外层OFFSET函数的新引用行数。
整个公式的意思是：
以客户对照表!$A$2为基点，向下偏移行数为0，向右偏移列数为客户区域在“客户对照表”工作表第一行的位置减1，新引用的行数为该列实际的不为空单元格个数，这样如果数据增加，COUNTA的结果也会发生变化，再反馈给OFFSET函数，就得到了动态的引用区域。
最后为“销售汇总表”工作表客户姓名的所在列设置数据有效性：
OK，设置完成。
在B列选择客户区域，C列就可以选择对应的客户姓名了。
有同学可能会说了：这个方法好复杂，有什么优越性啊？
别急，在客户对照表中增加数据看看：
回到销售汇总表中，点击客户区域的下拉列表，已经自动添加了华南区的选项：
接下来看看华南区的客户姓名：
在客户对照表中增加华南区客户数据：
回到销售汇总表中，点击客户姓名的下拉列表，已经自动更新了华南区新增客户姓名：
图文：祝洪忠
长按下面二维码图片，点”识别图中二维码“然后再点关注，每天都会收到最新excel教程。
ExcelHome，微软技术社区联盟成员
易学宝微视频教程，1290个Office技巧精粹，每个技巧都与实际工作密切相关。轻松学习技巧，练就职场达人。淘宝搜索关键字：ExcelHome易学宝
本公众号回复关键字：大礼包 立刻免费获取50集精选易学宝教程
