Excel中怎样实现二级联动下拉菜单

Excel中怎样实现二级联动下拉菜单?

Excel中下拉菜单功能可以帮助我们节省输入时间,通过选取下拉菜单中的值来实现输入数据,非常快捷、方便。但是日常工作中,我们常需要一个下拉菜单,让后面的下拉菜单依据前面的下拉菜单的内容的改变而改变(也就是联动的下拉菜单)。

首先看一下原始数据,原始信息在一张工作表,第一行是省市名称,下面的若干行为对应省市下面的地名和区名,如图1。需要在另外一张工作表中A列和B列建立联动的二级下拉菜单,如图2。

图1

图2

操作步骤如下:

Step1:选中原始表所有数据,按快捷键F5调出定位对话框,定位条件选择“常量”,点击“确定”按钮,这样所有非空单元格被选中。如图3。

图 3

Step2:点击功能区菜单“公式”→“根据所选内容创建”,如图4,因为标题在首行,所以选择“首行”作为名称,点击“确定”按钮,如图5。操作完毕后在菜单“公式”下的“名称管理器”就可以看到定义的名称了,如图6。

图 4

图 5

图 6

Step3:在另外一张工作表创建标题行,省/直辖市和市/区,选中A2单元格,点击菜单“数据”→“数据验证”(注:2013版本的“数据验证”在2003、2007、2010版本是“数据有效性”),验证条件选择“序列”,来源选中原始数据表的首行数据,如图7。这样,在A2菜单就生成了省市下拉菜单,如图8。如果需要在更多的单元格区域设置下拉菜单,就选中更多的单元格区域,比如A2:A20,切忌选中整列区域,如果选中整列,会导致在很多没有用的区域设置了数据有效性,增加了文件的虚拟内存,使得文件变大,文件变大会导致打开和各种操作都会非常慢。

图 7

图 8

Step4:同样的方法,选中B2单元格,点击数据验证,在“来源”处输入公式=INDIRECT($A$2),点击“确定”按钮。设置完毕后,A2单元格选择“深圳市”时B2的下拉菜单返回“深圳市”的信息,如图10;A2单元格选择“北京市”时B2的下拉菜单返回“北京市”的信息,如图11。

注意:

上述二级下拉菜单设置的公式采取了行列都绝对引用,如果要使二级下拉菜单对更多的单元格区域均可用,将公式更改为:=INDIRECT($A2)即可。

图 9

图10

图11

indirect函数功能是返回并显示指定引用的内容,可引用其他工作簿的名称、工作表名称和单元格引用。制作多级下拉菜单的原理就是利用定义名称,然后在单元格输入与定义名称相同的字符,再对含有这种字符的单元格用Indirect作引用。

操作动画如下:

(0)

相关推荐