我要开付费|Excel高手必备神技巧——数据有效性还可以这么玩!

Hello,大家好,我是罂粟姐姐,好久不见。致力于Excel教程分享快两年了,我是简书的忠实用户,也是你们贴心的大姐姐。

今天给大家分享Excel数据有效性不仅能够对单元格的输入数据进行条件限制,还可以在单元格中创建下拉列表菜单方便用户选择输入。

(注意啦!注意啦!高手必备的神技巧。)

1、单元格只能输入15位或18位身份证号

第一步:选择需要设置数据有效性的区域,打开数据-数据验证对话框。

第二步:设置-允许对话框中选择“自定义”。

第三步:在公式对话框中输入公式:=or(len(A2)=15,len(A2)=18)

第四步:单击确定,完成设置,并进行验证。

最后动态演示过程如下:

需要注意的是:单元格格式一定要提前设置为文本哦。

2、制作二级下拉菜单

第一步:选中大致的数据区域,按F5定位-定位条件-常量-确定,完成数据区域的精准选择。

第二步:点击公式-定义的名称-根据所选内容创建-首行-确定,完成一级菜单与二级菜单的关联。

第三步:选择“著作”列,点击数据-数据验证-设置-允许“序列”-来源“=$D$1:$I$1”,完成一级菜单的设置。

第四步:选择“人物”列,点击数据-数据验证-设置允许“序列”-来源“=INDIRECT($A2)”,完成二级菜单设置。

最后动态演示过程如下:

3、一级菜单和二级菜单动态引用

按照2的玩法,当增加著作或为任何一部著作添加新的名单时,数据有效性不能引用新名单。

这个时候,我们需要通过函数公式实现一级菜单和二级菜单的动态引用。

第一步:点击公式-定义名称-新建名称-名称“著作”-引用位置“=OFFSET(Sheet1!$D$1,,,,COUNTA(Sheet1!$1:$1)-2)”,这样的话,行方向上的数据增加,引用范围就会自动扩展。

第二步:选择“著作”列,点击数据-数据验证-设置-允许“序列”-来源“=著作”,完成一级菜单的设置。

第三步:点击公式-定义名称-新建名称-名称“人物”-引用位置“=OFFSET(Sheet1!$D$2,,MATCH(Sheet1!$A2,Sheet1!$1:$1,)-4,COUNTA(OFFSET(Sheet1!$D$2,,MATCH(Sheet1!$A2,Sheet1!$1:$1,)-4,100)))”,这样的话,列方向上的数据增加,引用范围就会自动扩展。

第四步:选择“人物”列,点击数据-数据验证-设置-允许“序列”-来源“=人物”,完成二级菜单的设置。

如果增加著作《碧血剑》和《天龙八部》中的人物“西夏公主”,则可以看出来下拉菜单也可同步更新。

……

好啦,剩下的就看简书给不给姐姐开通付费功能啦。

如果对姐姐我还不是很了解的话,欢迎订阅姐姐的连载《人人都能学会的Excel数据分析》,我在这里等你哦!

推荐阅读更多精彩内容

  • 转自链接 2.3.5 IF函数 2.3.6 CountIf和SumIf函数 2.3.7 Lookup函数 2.3....
    腿毛裤阅读 9,841评论 0 0
  • 也许你已经做了上千张表格,也许你用函数算了上千复杂的运算。也许认为excel不过如此,今天25招秘技希望可以帮到大...
    南屋阿米佛头阅读 2,999评论 0 51
  • VBA订制工具栏 http://club.excelhome.net/thread-1047254-1-1.htm...
    大海一滴写字的地方阅读 1,566评论 0 0
  • Spring Cloud为开发人员提供了快速构建分布式系统中一些常见模式的工具(例如配置管理,服务发现,断路器,智...
    卡卡罗2017阅读 118,223评论 14 132
  • 梦想是什么? 曾经,2000公里以外的那个叫做北京的城市,承载在我的梦想,可是到头来我连我的梦想是什么都不知道?是...
    江式微阅读 136评论 2 4