鼠标dpi在哪里设置:【软件】Excel三级级联下拉框的设计与实现
来源:百度文库 编辑:中财网 时间:2024/04/28 15:31:18
【软件】Excel三级级联下拉框的设计与实现
(2010-06-01 11:51:51) 标签:excel
公式
三级级联
下拉框
报表
offset
match
indirect
it
分类: 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 &
例如:“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.contextures.com/tiptech.html
- (2010-11-25 16:00:51)
- (2010-10-20 17:36:31)
- (2010-06-02 11:51:40)
- (2009-12-07 13:58:31)