小伙伴们好哈,今天和大家一起学习一下如何制作动态引用的二级菜单。
先准备好数据源——不同区域的客户对照表:
需要实现的效果是:
在销售汇总表中动态选择客户区域,就会在下拉菜单中出现不同区域的客户姓名:
步骤一:
自定义名称
依次点击【公式】选项卡,【定义名称】,自定义名称“区域”,在引用位置文本框内写上公式:
=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)))
这个公式有点小复杂,咱们慢慢解析,实在看不懂的话也不用急,俗话说一口吃不成胖子,一天减不成瘦子,可以先收藏下来,以后随着函数功力不断增强,理解也就是水到渠成的事儿了。
步骤四:
最后为“销售汇总表”工作表客户姓名的所在列设置数据有效性:
OK,设置完成。
在B列选择客户区域,C列就可以选择对应的客户姓名了。
有同学可能会说了:这个方法这么复杂,有什么用啊?
别急,在客户对照表中增加数据看看:
回到销售汇总表中,
点击客户区域的下拉列表,已经自动添加了华南区的选项:
接下来看看华南区的客户姓名:
在客户对照表中增加华南区客户数据:
回到销售汇总表中,点击客户姓名的下拉列表,已经自动更新了华南区新增客户姓名:
光说不练假把式,你也试试吧,练手文件在此(长按链接复制,在浏览器中打开):
http://pan.baidu.com/s/1kUWqB2n
联系客服