当前位置:首页教育技巧WPS技巧wps图表

Excel多级下拉菜单,定义名称报错,不支持数字怎么办?

2024-07-20 09:53:58


我们知道在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。

小结

相关信息


电脑版

【免责声明】本站信息来自网友投稿及网络整理,内容仅供参考,如果有错误请反馈给我们及时更正,对文中内容的真实性和完整性本站不提供任何保证,不承但任何责任。
版权所有:学窍知识网 Copyright © 2011-2024 www.at317.com All Rights Reserved .