excel根据不同分类动态设置不同下拉列列表

typ2004

已于 2023-05-19 09:38:23 修改

阅读量2.4k

点赞数

文章标签： excel

于 2023-05-18 19:07:23 首次发布

本文链接：https://blog.csdn.net/typ2004/article/details/130753128

版权

文章介绍了如何使用Excel的OFFSET、MATCH和COUNTA函数创建动态的三级下拉列表。通过INDEX获取二级目录的值，MATCH找到对应的列，COUNTA计算有效行数，OFFSET获取整个列的非空单元格作为三级目录的序列值。这种方法适用于多个系统分类的选择，不同的一级和二级分类对应不同的三级分类列表。

摘要由CSDN通过智能技术生成

有这样一个需求，有很多个系统，需要在excel中下拉选择其系统一级分类、二级分类、三级分类，不同的一级分类对应不同的二级分类列表，不同的二级分类对应不同的三级分类列表。

针对这个需求，我们采用了excel/wps中的数据有效性，设置了有效性条件为序列，来源于自己编写的公式。

公式如下：

=OFFSET(
       三级分类下拉底表!$A$2,
       ,
       MATCH( INDEX($H:$H,ROW()), 三级分类下拉底表!$A$1:$Y$1, 0 ) - 1,
       COUNTA(
               OFFSET(
                      三级分类下拉底表!$A$2,
                      ,
                      MATCH( INDEX($H:$H,ROW()), 三级分类下拉底表!$A$1:$Y$1, 0 ) - 1,
                      255
                )
       )
)

说明：

三级下拉底表如下，共Y列，如下图1：

本例子公式应用的为三级目录，其随二级目录中的数值变化，二级目录中的业务支持、资源管理就是三级下拉列表中的第一行中黄底红字，如下图2：

通过index得到图2中二级目录的具体值，如“业务支持”，通过match查到其在图1中属于第几列（-1为相对第一列的相对位置），通过counta获取该列的有效行数，最后通过office获取该列非空的所有行，作为三级目录的序列值。

ps:引用的底表名称里不要有 '-' 等特殊符号，否则会报参数错误。

函数讲解如下：

Offset

语法

表达式：OFFSET(Reference, Rows, Cols, [Height], [Width])

中文表达示：OFFSET(引用单元格, 行数, 列数, [返回引用高度], [返回引用宽度])

这个函数有5个参数：

第一个参数是基点

第二个参数是要偏移几行，正数向下，负数向上。

第三个参数是要偏移几列，正数向右，负数向左。

第四个参数是新引用几行。

第五个参数是新引用几列。

如果不使用第四个和第五个参数，新引用的区域就是和基点一样的大小。

功能：

以指定的引用为参照系，通过给定偏移量得到新的引用，指定返回的行数或列数，返回一个单元格或单元格区域。

例如使用下面这个公式：

=OFFSET(C3,4,2,4,3)

就是以C3为基点，向下偏移4行，向右偏移2列，新引用的行数是4行，新引用的列数是3列，最终得到对E7:G10单元格区域的引用。

Excel明星函数系列1：说说函数中的极品——OFFSET函数

MATCH

语法

MATCH函数的语法如下：

· MATCH(lookup_value,lookup_array,[match_type])

- lookup_value可以是文本、数值或逻辑值（是要查找的值）
- lookup_array是数组或数组引用(在单行或列中的连续单元格)
- match_type可以是-1, 0或1。如果忽略，则默认为1 （0：查找第一个与lookup_value精确匹配的值，lookup_array可采用任何顺序；1：查找小于或等于lookup_value的最大值，lookup_array必须按升序排列；-1：查找大于或等于lookup_value的最小值，lookup_array必须按降序排列。