最新WPS办公软件学习教程_免费企业办公软件下载_金山WPS Office官网论坛

 找回密码
 
查看: 33258|回复: 323

[函数公式] 再谈excel/wps表格二级下拉列表:用Offset实现方便的扩充修改

    [复制链接]

154

主题

27

听众

5095

积分

解答支持团员

Rank: 14Rank: 14Rank: 14Rank: 14

签到天数: 1 天

[LV.1]初来乍到

金币
2784
威望
11083
帖子
2618
精华
0

技术分享团 解答支持团 测试体验团 优秀会员奖 活跃会员奖 乐于助人奖 技巧教程分享达人

发表于 2012-11-12 19:17 |显示全部楼层
分享到: 新浪微博 腾讯微博
本帖最后由 quelea 于 2012-11-14 08:47 编辑

论坛上达人们已经提供了以下简明教程,非常高效:

@NaclYh
【表格设置二级下拉列表】
http://bbs.wps.cn/thread-22331055-1-1.html
【如何设置下拉列表】
http://bbs.wps.cn/thread-22330502-1-1.html

@xiaodaisong
【征稿八】巧借“名称”,在wps表格中建立多级下拉列表
http://bbs.wps.cn/thread-21912832-1-1.html

@松风水月
使用WPS表格制作三级下拉菜单
http://bbs.wps.cn/thread-22337128-1-1.html

我这里要提供一个更复杂,但比较容易扩充的解决方案。作为教程,可能有把简单问题复杂化之嫌。

为什么复杂化?理由:
在数据很多,很复杂,常要扩充、变动的情况下,感觉上述方式都不是很方便。
上述贴子中提到的方法,非常易用,一学就会,但仍有几个问题:
  • 扩充比较麻烦
  • 各类别分项下项目数量不一致时会在下拉列表中出现空白项
  • 会在名称表中产生一大堆名称,有时项目太多时会晃眼吧

解决上述问题,提出一个思路:
  • 在表格中指定一个区域,这个区域是下拉列表定义区域
  • 这个区域中间可以方便地插入行列,名称会自动扩充
  • 能够自动去除多余的空单元格

效果如下:
再谈excel/wps表格二级下拉列表:用Offset实现方便的扩充修改_16114973



实现的方法与前贴类似,还是利用数据有效性。但这回不用Indirect函数,采用Offset函数返回一个一维数据区域。

看一下Offset函数:
语法
OFFSET(reference,rows,cols,height,width)
Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用;否则,函数 OFFSET 返回错误值 #VALUE!。
Rows 相对于偏移量参照系的左上角单元格,上(下)偏移的行数。如果使用 5 作为参数 ROWS,则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数(代表在起始引用的下方)或负数(代表在起始引用的上方)。
Cols 相对于偏移量参照系的左上角单元格,左(右)偏移的列数。如果使用 5 作为参数 Cols,则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数(代表在起始引用的右边)或负数(代表在起始引用的左边)。
Height 高度,即所要返回的引用区域的行数。Height 必须为正数。
Width 宽度,即所要返回的引用区域的列数。Width 必须为正数。

实现如下:(详见附件xls文件)
游客,如果您要查看本帖隐藏内容请回复
已有 5 人评分威望 收起 理由
翘尾的k + 10 赞一个!
木偶251900 + 10 很给力!
落寞冬日 + 15
zhouyiran1@126.com + 30 很给力!
松风水月 + 15 赞一个!

总评分: 威望 + 80   查看全部评分

6

主题

3

听众

144

积分

LV.3

Rank: 3Rank: 3

该用户从未签到

金币
20
威望
220
帖子
133
精华
0
发表于 2012-11-12 23:25 |显示全部楼层
学习中{:soso_e183:}
回复

使用道具 举报

153

主题

109

听众

1万

积分

解答支持团长

老菜鸟

Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20

签到天数: 240 天

[LV.8]以坛为家I

金币
1254
威望
25965
帖子
10314
精华
1

活跃会员奖 解答支持团 测试体验团 乐于助人奖 优秀会员奖

发表于 2012-11-13 00:21 |显示全部楼层
高手出招总是让人目不暇接,佩服+膜拜+学习中!
回复

使用道具 举报

78

主题

61

听众

5047

积分

解答支持团长

Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20

该用户从未签到

金币
257
威望
9809
帖子
3187
精华
0

WPS粉丝团 勤奋版主奖 荣誉版主奖 乐于助人奖 优秀会员奖 活跃会员奖 技术分享团 解答支持团 测试体验团 重阳节勋章 技巧教程分享达人 版主勋章

发表于 2012-11-13 09:04 |显示全部楼层
先记下。有时间再好好学学。
回复

使用道具 举报

157

主题

208

听众

2639

积分

管理员

Rank: 24Rank: 24Rank: 24Rank: 24Rank: 24Rank: 24

签到天数: 13 天

[LV.3]偶尔看看II

金币
5438
威望
4488
帖子
1040
精华
1

最佳教程奖 活跃会员奖 测试体验团 技巧教程分享达人

发表于 2012-11-13 09:39 |显示全部楼层
{:soso__4366149834173503299_1:}
加入PPT/WPP美化交流群  385256043
回复

使用道具 举报

19

主题

108

听众

5338

积分

技术分享团长

Rank: 20Rank: 20Rank: 20Rank: 20Rank: 20

签到天数: 5 天

[LV.2]偶尔看看I

金币
5316
威望
11825
帖子
2334
精华
1

WPS粉丝团 荣誉版主奖 活跃会员奖 技术分享团 解答支持团 重阳节勋章

发表于 2012-11-13 09:50 |显示全部楼层
在介绍利用“数据有效性”做下拉菜单的教程中,常见的都是仅仅使用 INDIRECT 函数,它的优点是方便,缺点就如楼主上面所说的,不利于调整,可能会在列表中出现较多空白项。使用 OFFSET 函数来解决这个问题的教程,今天还是第一次见。虽然会比单用 INDIRECT 函数复杂,但效果更好。非常谢谢楼主的分享!学习了!{:soso_e179:}
回复

使用道具 举报

2

主题

0

听众

90

积分

LV.2

Rank: 2

该用户从未签到

金币
22
威望
196
帖子
49
精华
0
发表于 2012-11-13 16:42 |显示全部楼层
学习了                     
回复

使用道具 举报

0

主题

0

听众

13

积分

LV.1

Rank: 1

该用户从未签到

金币
0
威望
19
帖子
15
精华
0
发表于 2012-11-18 04:52 |显示全部楼层
在WPS表格中为了方便规范数据的录入,我们经常会在相应的单元格中提供下拉列表以便选择录入。但如果需要建立多级下拉列表,那又该如何处理呢?
在某学校设有高一、高二、高三共三个级部,每个级部的分别有十个左右的班级。以前在统计学生相关信息的时候,经常发现各班级、级部上交的表格很不规范,因此为了规范起见,有必要建立多级的下拉列表,如图1所示。


2011-1-18 20:31 上传下载附件 (61.48 KB)
  

图1

为此,我们应该借助“名称”以达到目的。
一、定义名称
如图2所示。

2011-1-18 20:33 上传下载附件 (52.42 KB)
图2
在Sheet1工作表中建立如图表格。各列标题就以相应的级部名称来表示,其下则分别录入各相应的班级,四个级部分别在A-C列。在E1单元格录入“级部”,其下录入四个级部名称。注意此处级部名称应与各列标题一致。
选中A列单元格,点击菜单命令“插入→名称→定义”,打开“定义名称”对话框。在上方的输入框中输入“高一”,并确认下方的“引用位置”输入框中数据为“=Sheet1!$A:$A”,如图3所示。

2011-1-18 20:33 上传下载附件 (23.89 KB)
图3

点击确定后,就可以将A列单元格区域定义为“高一”了。不信,你可以在表格的名称框中输入“高一”,回车,是不是整个A列就被选中了?
可以用同样的方法,分别选中B列、C列、E列单元格区域后,在“定义名称”对话框中将它们分别定义为“高二”、“高三”、“级部”名称。也可以直接在上方的输入框中输入名称,然后将下方的“引用位置”修改成相应的单元格区域,然后点击“添加”按钮,将名称添加到当前工作簿的名称列表中。比如定义“高三”名称时,其引用位置应改为“=Sheet1!$C:$C”,定义“级部”名称时,其引用位置应改为“=Sheet1!$E:$E”。
二、设置数据有效性
如图4所示表格。
 

2011-1-18 20:33 上传下载附件 (36.39 KB)
 

图4

选中C2及以下单元格区域,点击菜单命令“数据→有效性”,打开“数据有效性”对话框。在“允许”下拉列表中选择“序列”,然后在下方的“来源”输入框中输入“=级部”,其它设置如图5所示。

2011-1-18 20:33 上传下载附件 (36.06 KB)
图5

确定后,将鼠标定位于C2或其下单元格,可以发现右侧出现下拉按钮,点击即可出现在Sheet1工作表E列中所列的级部名称列表。
关键是表格中的D列,如何根据C列所选级部名称在D列单元格出现相应级部的班级列表呢?其实,这一步也是属于窗户纸性质的,一捅就破。选中D2及以下单元格区域,在“数据有效性”对话框中,“允许”列表仍选“序列”,但在“来源”输入框中输入公式“=INDIRECT($C2)”,如图6所示。

2011-1-18 20:34 上传下载附件 (33.26 KB)
图6

确定后再单击相应单元格,就可以看到图1所示的效果了。
函数INDIRECT($C2)的作用是返回在C2单元格中的数据的值。比如本例图1中“=INDIRECT($C2)”的结果就是“=高一”,而“=INDIRECT($D2)”的结果就是“=高三”。
如有更多级别的下拉列表也不用愁,就是这么两步,先定义名称,然后在数据有效性对话框中进行引用名称就可以了。
回复

使用道具 举报

0

主题

0

听众

20

积分

LV.1

Rank: 1

该用户从未签到

金币
0
威望
16
帖子
12
精华
0
发表于 2012-11-22 14:31 |显示全部楼层
顶 楼主 一下~~~
回复

使用道具 举报

1

主题

0

听众

32

积分

LV.1

Rank: 1

该用户从未签到

金币
0
威望
57
帖子
20
精华
0
发表于 2012-11-23 18:18 |显示全部楼层
...................
回复

使用道具 举报

*滑动验证:
您需要登录后才可以回帖 登录 | 更多账号登录:

快速回帖:

WPS论坛更新日志|WPS Office官方论坛     

GMT+8, 2017-11-22 15:36

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部