Board logo

标题: Excel知识兴趣点十四：联合函数制作二级联动下拉列表 [打印本页]
作者: zhendeaini    时间: 2014-11-19 14:05     标题: Excel知识兴趣点十四：联合函数制作二级联动下拉列表

用到公式：
OFFSET($B$1,MATCH($D12,$A:$A,)-1,,,COUNTA(OFFSET($B$1:$F$1,MATCH($D12,$A:$A,)-1,)))

公式分解：
一、MATCH函数（获取位置）
=MATCH($D8,$A:$A,)-1，表示获取$D8所在的位置。因为是从A1（$A:$A）开始，而我们的名称是从A2开始的，所以要-1。$D8的值会随着所选不同而不同，所以取得位置值也是跟着改变。例如当$D8=“水果”时，得到“水果”位置为2；当$D8=“笔”时，得到“笔”位置为3，也就是对等它所在的行数（第几行），为匹配OFFSET函数做准备。

二、OFFSET函数（里）（这里主要是获取区域）

=OFFSET($B$1:$F$1,MATCH($D8,$A:$A,)-1,)，$B$1:$F$1表示有5个可选项目，这个公式表示$D8的值（$D8=“水果”等）所在行的可选区域为$B$1:$F$1。
三、COUNTA函数（统计条件区域每个可选项目非空单元格的个数）
=COUNTA(OFFSET($B$1:$F$1,MATCH($D8,$A:$A,)-1,))

四、OFFSET函数（外）
=OFFSET($B$1,MATCH($D8,$A:$A,)-1,,,COUNTA(OFFSET($B$1:$F$1,MATCH($D8,Sheet1!$A:$A,)-1,)))
这里$B$1（即“明细”）为参照点，MATCH($D8,$A:$A,)-1为偏移的行数，COUNTA(OFFSET($B$1:$F$1,MATCH($D8,$A:$A,)-1,))为返回的引用区域的列数（宽度）。

简单的思路：就是首先获取第一列表$D8的值所在的行，其次在根据$D8的值获取第二列表$E8的可选范围。




图片附件: 联合函数制作二级联动下拉列表.gif (2014-11-19 14:05, 475.83 KB) / 该附件被下载次数 25
http://hahabbs.w1.luyouxia.net/bbs/attachment.php?aid=118593

作者: 肥仔恩    时间: 2014-11-19 14:08

[104] [104] [104]
作者: zhendeaini    时间: 2014-11-19 14:36

因为录制时疏忽，单元格F10中公式里的D8或D12应更正为D7，在此声明更正！[32] ，录制时考虑看的见而忘记更正了。
作者: seaxin    时间: 2014-11-19 15:43

哦～～原来偏移函数用来干这个～[32]
作者: 肥仔恩    时间: 2014-11-20 10:01

其实可以```更简单咯```用 =INDIRECT(    )  这个来弄
作者: 肥仔恩    时间: 2014-11-20 10:09

二级菜单.xls (14 KB)

这个貌似简单一点 不过就多了个定义的一步

附件: 二级菜单.xls (2014-11-20 10:09, 14 KB) / 该附件被下载次数 24
http://hahabbs.w1.luyouxia.net/bbs/attachment.php?aid=118624
作者: seaxin    时间: 2014-11-20 10:33

[39]
作者: zhendeaini    时间: 2014-11-20 12:30

引用:
原帖由 肥仔恩 于 2014-11-20 10:01 发表
其实可以```更简单咯```用 =INDIRECT(    )  这个来弄
是的，迟点介绍INDIRECT函数！[39]
作者: zhendeaini    时间: 2014-11-20 12:59

引用:
原帖由 肥仔恩 于 2014-11-20 10:09 发表
118624

这个貌似简单一点 不过就多了个定义的一步
这个只是简单引用，简单是简单，具有局限性！[32]
作者: 肥仔恩    时间: 2014-11-20 14:06

引用:
原帖由 zhendeaini 于 2014-11-20 12:59 发表



这个只是简单引用，简单是简单，具有局限性！[32]
恩恩 是非常简单````说复杂了 大家也不好理解啊```
作者: seaxin    时间: 2014-11-20 15:01

引用:
原帖由 zhendeaini 于 2014-11-20 12:59 发表



这个只是简单引用，简单是简单，具有局限性！[32]
是不是如果选项太多，那个工作量也多了～[39] 要定义一堆名字。
作者: seaxin    时间: 2014-11-20 15:04

引用:
原帖由 肥仔恩 于 2014-11-20 14:06 发表



恩恩 是非常简单````说复杂了 大家也不好理解啊```
EXCEL复杂的，这远远还不到啊～～[32] 没有用到VBA的都不咋的。
作者: zhendeaini    时间: 2014-11-20 17:08

引用:
原帖由 seaxin 于 2014-11-20 15:04 发表


EXCEL复杂的，这远远还不到啊～～[32] 没有用到VBA的都不咋的。
能用公式解决的不一定用到VBA，不要以为会用VBA就是高大上了。[39]

力求用最简单的方法去解决EXCEL问题。
作者: 肥仔恩    时间: 2014-11-21 08:27

引用:
原帖由 seaxin 于 2014-11-20 15:01 发表


是不是如果选项太多，那个工作量也多了～[39] 要定义一堆名字。
有多少个种类就多少个定义咯``其实也不会太大工作量吧``
作者: zhendeaini    时间: 2014-11-21 16:00

引用:
原帖由 肥仔恩 于 2014-11-21 08:27 发表



有多少个种类就多少个定义咯``其实也不会太大工作量吧``
也是[39]



欢迎光临 BBS (http://hahabbs.w1.luyouxia.net/bbs/) Powered by Discuz! 6.0.0