【软件】Excel三级级联下拉框的设计与实现

【软件】Excel三级级联下拉框的设计与实现

(2010-06-01 11:51:51)

标签： excel 公式 三级级联 下拉框 报表 offset match indirect it

分类： Excel解决方案

    在网上浏览了些有关在Excel中实现多重级联的帖子，好复杂，数组都用上了，在这里，设计几个相关的示例，整理一些我的设计思路。

示例一：设计三个下拉框菜单，即大分类（Category Name），子分类（Sub Category Name）及产品名(Produt Name), 产品名下拉内容对应上层分类菜单的选项。

1 基本思路：如何实现二级级联下拉框，即子分类与大分类的联动：

a. 使用数据有效性（Data Validation）实现下拉框。

b. 子分类（Sub Category Name）（二级下拉框）有效性数据范围运用Offset + Match + Countif/Countifs公式

关键步骤：

1） 设计辅助表（表一）

在上述辅助表的基础上，例如：大分类“Category Name 02”中所有子分类“Sub Category Name 04-06”的数据范围如何取？如果运用Offset公式的话，下列2) - 4) 是主要考虑因素。

2）从哪个数据开始

从辅助表“Category Name”表标题（tmpStart）开始。

3）往下移动几个位置（Match公式）

=MATCH(CategoryName,tmpColumn,0)

公式大意是所选的大分类（一级下拉框）在辅助表Category Name列（tmpColumn）中匹配的第一个位置，“Category Name 02”返回5

4）符合条件的数据有几个(countif公式)

=COUNTIF(tmpColumn,CategoryName)

“Category Name 02”返回3

有了上述数据，对子分类（二级下拉框）设置“序列”（List）数据有效性，并设如下公式。

=OFFSET(tmpStart,MATCH(CategoryName,tmpColumn,0)-1,1,COUNTIF(tmpColumn,CategoryName),1)

 

2 如果产品（三级下拉框）与上级分类呈下表（表二）关系，且子分类Sub Category Name（二级）对应唯一一个大分类（一级），对产品（三级下拉框）设置序列有效性，并设下列公式，同基本思路。

表二：

 

=OFFSET(subCategoryStart,MATCH(subCategoryName,subCategoryColumn,0)-1,1,COUNTIF(subCategoryColumn,subCategoryName),1)

 

如果子分类Sub Category（二级）对应上级大分类存在重复，如表三所示，则产品（三级下拉框）的设计稍微会复杂些，具体请阅“示例二”部分。

表三：

示例二：

3 产品（三级下拉框）设计的关键步骤：

依旧遵循示例一的思路（数据有效性 + Offset）设计产品（三级下拉框）。

1）从哪个数据开始

2）往下移动几个位置（Match + Indirect公式），即在表三中符合所选大分类（一级）与子分类（二级）的第一个位置是什么。

a. 符合所选大分类（一级）的第一个位置

= MATCH(CategoryName,categoryColumn,0)

例如：“Category Name 02”大分类，返回12

 

b.符合所选大分类（一级）的数据范围

Range = StartRow:EndRow

StartRow = MATCH(CategoryName,categoryColumn,0), 即1）的结果

EndRow = COUNTIF(categoryColumn,CategoryName) + startRow -1

Range ="E" & startRow &  ":E" & endRow

例如：“Category Name 02”大分类的数据范围是E12:E25。

 

c.所选子分类（二级）在所选大分类（一级）数据范围中的第一个位置

subStartRow = MATCH(subCategoryName,INDIRECT(Range),0)

例如：“Category Name 02”（一级）-->“M”（二级）返回5

 

d.向下偏移量

downRows = startRow -1+ subStartRow -1

例如：“Category Name 02”（一级）-->“M”（二级）返回15

 

3) 符合条件的数据有几个(Countifs)

在表三中，既符合所选大分类又符合所选子分类的个数

Rows =COUNTIFS(categoryColumn,CategoryName,subcategorycolumn,subcategoryName)

 

4) 基于上述1-3，产品（三级下拉框）序列有效性设置下列公式。

=OFFSET(subCategoryStart,downRows,1,Rows,1)

 

基于上述两个示例，我们还可考虑

4 如何设计级联下拉框中的“ALL”？

5 若需复选选项，又如何设计？

 

对Offset, Indirect, countif/countifs的基本使用，请阅相关文章。

发现一个Excel技巧应用网址，内容挺全面，推荐，http://www./tiptech.html ，例如，关于这个Mutiple dependent dropdown list的技巧介绍，有点启发。

我的更多文章：

• 【软件】Excel VBA访问数据库的应用示例(2010-11-25 16:00:51)
• 【软件】在Excel中实现按条件查询数据（2）(2010-10-20 17:36:31)
• 【软件】在Excel中实现按条件查询数据（1）(2010-06-02 11:51:40)
• 【软件】Excel Sumifs、Offset公式等(2009-12-07 13:58:31)