Excel多级下拉菜单,定义名称报错,不支持数字怎么办?
我们知道在Excel/WPS中,一级下拉列表可以通过数据有效性/数据验证实现,多级下拉菜单需要借助【定义名称】。
然而定义名称有个局限性,就是不支持数字开头,这样就导致,我们无法实现前级下拉选项为数字的多级下拉菜单。
举个例子,下图中,需要根据A列税率的不同,B选择不同的产品,由于定义名称不支持数字(税率),所以无法用INDIRECT函数、实现A列与B列的联动。
在解决这个问题之前,我们先来看下正常的多级下拉菜单的实现过程。
一级下拉菜单
以及下拉菜单直接利用数据验证/数据有效性即可。
首先准备好需要下拉展示的列表(也可以后期手动输入),接着选中需要设置的单元格,依次点击【数据】—【数据验证】,验证条件选择“序列”,来源框选准备好的列表,点击【确定】,完成设置。
动图展示如下:
二级下拉菜单
二级下拉菜单在一级的基础上,补充一二级的对应表(示例中为省份-城市对应表)。
首先选中对应表(不要空白单元格,可用Ctrl+G定位,这里手动选择),点击【公式】,选择【根据所选内容创建】,仅勾选【首行】,其它取消,点击【确定】按钮,完成定义名称设置。
接着选中B列,设置数据有效性(操作同一级),只不过在来源中输入公式:
=INDIRECT(A2),意思是根据A2单元格内容动态返回。
动图展示如下:
这样二级下拉菜单制作完成。
三级下拉菜单
三级之后的下拉菜单,重复二级步骤即可。
首先准备一份二、三级对应列表,建立定义名称,接着在设置数据有效性,步骤同二级下拉菜单。
动图展示如下:
多级下拉菜单重复上面步骤即可。
定义名称不支持数字
下图在设置二级下拉菜单时,点击定义名称,在设置完毕后,点击【名称管理器】,发现里面空空如也,没有内容,说明未建立成功(不支持数字开头)。
解决方法
这里利用offset函数返回区域的属性,来解决这个问题。
首先手动建立定义名称,名称可以随意输入,这里我们输入【税率】,在公式栏输入:
=OFFSET($D$1,1,MATCH(A2,$D$1:$H$1,0)-1,COUNTA(OFFSET($D$2:$D$5,0,MATCH(A2,$D$1:$H$1,0)-1)))
之后再B列建立数据有效性,来源输入:【=税率】,点击确定,完成二级下拉菜单的设置。
offset函数以指定的引用为从参照系(这里以D1单元格),通过给定偏移量返回新的引用。
语法:offset(参照物,偏移行,偏移列,返回的行数,返回的列数)
通过match函数动态返回A列单元格税率所在参数表的位置,参数传递给offset函数,发生相应的偏移。
注:如果后期有新的内容要加入的话,公式中的$D$5可以修改的大一点,比如$D$1000。