上档次——动态引用的二级菜单

制作动态引用的二级菜单

小伙伴们好哈，昨天给大家推送的是一期关于二级菜单的制作方法。该方法的优点是操作简单，缺点是不能随着数据的变化，实现动态引用。

今天咱们趁热打铁，和大家一起学习一下如何制作动态引用的二级菜单。

先准备好数据源——不同区域的客户对照表：

需要实现的效果是在销售汇总表中动态引用区域，以及不同区域的客户姓名：

接下来就是自定义名称的环节了：

依次点击【公式】选项卡，【定义名称】，自定义名称“区域”，在引用位置文本框内写上公式：

=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单元格（也就是客户区域）在“客户对照表”工作表第一行的位置。

OFFSET(客户对照表!$A$2,,MATCH($B2,客户对照表!$1:$1,)-1,100)部分，以客户对照表!$A$2为基点，向下偏移行数为0，向右偏移列数为MATCH函数计算结果减1，新引用的行数为100。（这里的100可以写成一个较大的数值，只要能保证比你的实际数据最大行数多一些就可以。）

这样就等于引用了客户区域所在列100行的范围。

用COUNTA函数计算客户区域所在列100行的范围内有多少个非空单元格，计算的结果作为前一个OFFSET函数的新引用行参数，也就是客户区域所在列100行的范围内有多少个非空单元格，前一个OFFSET函数就引用多少行。

整个公式的意思是：

以客户对照表!$A$2为基点，向下偏移行数为0，向右偏移列数为客户区域在“客户对照表”工作表第一行的位置减1，新引用的行数为该列实际的不为空单元格个数。

最后为“销售汇总表”工作表客户姓名的所在列设置数据有效性：

OK，设置完成。在B列选择客户区域，C列就可以选择对应的客户姓名了。

有同学可能会说了：这个方法这么复杂，也看不出和咱们昨天设置的下拉菜单有何不同啊？

别急，在客户对照表中增加数据看看：

回到销售汇总表中，点击客户区域的下拉列表，已经自动添加了华南区的选项：

接下来看看华南区的客户姓名：

在客户对照表中增加华南区客户数据：

回到销售汇总表中，点击客户姓名的下拉列表，已经自动更新了华南区新增客户姓名：

练手文件在此：

http://vdisk.weibo.com/s/Dt_F76IDtZRa

也可以点击左下角【阅读原文】下载附件。

图文制作：祝洪忠

原创不易，转载请注明出处

【老朋友】→请点击右上角的按钮，将本文分享到朋友圈。

【新朋友】→请点击标题下的ExcelHome，添加关注。或者直接查找公众号iexcelhome