作者:祝洪忠 转自:Excel之家ExcelHome
小伙伴们好啊,今天老祝和大家分享一个比较实用的技巧,一起来制作动态引用的二级菜单。
先来看效果,使用下拉菜单选择不同的客户区域,在随后的下拉菜单中就会出现对应区域的客户候选,如果某个区域内增加了客户,下拉菜单还能自动的更新:
![796d709a087d5411eb9c88107944231a.gif](https://i-blog.csdnimg.cn/blog_migrate/9a2c9a106b508044eb58f0a624a86285.gif)
首先,来准备一个客户对照表,第一行是所在区域,每一列中是对应的客户姓名:
![6934574da1203842c500043fd0b69954.png](https://i-blog.csdnimg.cn/blog_migrate/b300f4271276ed724ca508f09141172b.jpeg)
步骤一:定义名称
在客户对照表工作表中,依次点击【公式】→【定义名称】
自定义名称为“区域”
公式为:
=OFFSET($A$1,,,,COUNTA($1:$1))
![1e8e327189aa465a39659ba1f19863b9.gif](https://i-blog.csdnimg.cn/blog_migrate/cd2f113ff03fc4a3507959f775d99471.gif)
步骤二:设置数据验证
选中要输入客户区域的单元格范围,设置数据验证。
=区域
![e4a85132e2a8f9f1d4eca40f16a50583.gif](https://i-blog.csdnimg.cn/blog_migrate/5ac5711f226e5aec61a432212eaaa030.gif)
设置完成后,在B列就可以使用下拉菜单选择客户区域了。
步骤三:定义名称
单击C2单元格,依次点击【公式】→【定义名称】
自定义名称“姓名”
在引用位置文本框内写上公式:
=OFFSET(客户对照表!$A$2,,MATCH($B2,客户对照表!$1:$1,)-1,COUNTA(OFFSET(客户对照表!$A$2,,MATCH($B2,客户对照表!$1:$1,)-1,100)))
![7b99dc92274711b28d13b711d4e56490.gif](https://i-blog.csdnimg.cn/blog_migrate/9a46362ad69b8bc1e7e4c92f68de1115.gif)
有同学可能会说了,哇靠,好长的公式。嗯嗯,咱们先不管他,会套用就OK。
步骤四:设置数据验证
先单击B列下拉菜单,选择一个客户区域,然后选中需要输入客户姓名的单元格区域,设置数据验证:
![825b8f5dce0e75eab24e48b00a366190.gif](https://i-blog.csdnimg.cn/blog_migrate/bb35748c1aac7909364169b5b35e5fc5.gif)
OK,设置完成。
在B列选择客户区域,C列就可以选择与该区域对应的客户姓名。
如果在客户对照表中增加或是删除数据,下拉列表中的选项就能自动更新哦。
示例文件在此,你也试试吧~~