我们知道在Excel/WPS中,一级下拉列表可以通过数据有效性/数据验证实现,多级下拉菜单需要借助【定义名称】。

然而定义名称有个局限性,就是不支持数字开头,这样就导致,我们无法实现前级下拉选项为数字的多级下拉菜单。

数据验证如何设置下拉选项_excel设置下拉选项筛选_excel中设置下拉选项

举个例子,下图中,需要根据A列税率的不同,B选择不同的产品,由于定义名称不支持数字(税率),所以无法用INDIRECT函数、实现A列与B列的联动。

excel设置下拉选项筛选_数据验证如何设置下拉选项_excel中设置下拉选项

在解决这个问题之前,我们先来看下正常的多级下拉菜单的实现过程。

一级下拉菜单

以及下拉菜单直接利用数据验证/数据有效性即可。

首先准备好需要下拉展示的列表(也可以后期手动输入),接着选中需要设置的单元格,依次点击【数据】—【数据验证】,验证条件选择“序列”,来源框选准备好的列表,点击【确定】,完成设置。

动图展示如下:

excel中设置下拉选项_数据验证如何设置下拉选项_excel设置下拉选项筛选

二级下拉菜单

二级下拉菜单在一级的基础上,补充一二级的对应表(示例中为省份-城市对应表)。

首先选中对应表(不要空白单元格,可用Ctrl+G定位,这里手动选择)数据验证如何设置下拉选项,点击【公式】,选择【根据所选内容创建】,仅勾选【首行】,其它取消,点击【确定】按钮,完成定义名称设置。

接着选中B列,设置数据有效性(操作同一级),只不过在来源中输入公式:

=INDIRECT(A2),意思是根据A2单元格内容动态返回。

动图展示如下:

excel设置下拉选项筛选_excel中设置下拉选项_数据验证如何设置下拉选项

这样二级下拉菜单制作完成。

三级下拉菜单

三级之后的下拉菜单,重复二级步骤即可。

首先准备一份二、三级对应列表数据验证如何设置下拉选项,建立定义名称,接着在设置数据有效性,步骤同二级下拉菜单。

动图展示如下:

excel设置下拉选项筛选_excel中设置下拉选项_数据验证如何设置下拉选项

多级下拉菜单重复上面步骤即可。

定义名称不支持数字

下图在设置二级下拉菜单时,点击定义名称,在设置完毕后,点击【名称管理器】,发现里面空空如也,没有内容,说明未建立成功(不支持数字开头)。

数据验证如何设置下拉选项_excel中设置下拉选项_excel设置下拉选项筛选

解决方法

这里利用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列建立数据有效性,来源输入:【=税率】,点击确定,完成二级下拉菜单的设置。

excel设置下拉选项筛选_数据验证如何设置下拉选项_excel中设置下拉选项

offset函数以指定的引用为从参照系(这里以D1单元格),通过给定偏移量返回新的引用。

语法:offset(参照物,偏移行,偏移列,返回的行数,返回的列数)

通过match函数动态返回A列单元格税率所在参数表的位置,参数传递给offset函数,发生相应的偏移。

注:如果后期有新的内容要加入的话,公式中的$D$5可以修改的大一点,比如$D$1000。

小结


娜娜项目网每日更新创业和副业教程

网址:nanaxm.cn 点击前往娜娜项目网

站 长 微 信: nanadh666

声明: 本站内容转载于网络,版权归原作者所有,仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任,若侵犯到你的版权利益,请联系我们,会尽快删除处理!