【Excel成长记】数据有效性

字数 1342阅读 232

数据有效性

数据有效性是在尚未输入数据时,预先设置,对单元或单元格区域输入的数据从内容到数量上进行限制,对于符合条件的数据,允许输入;对于不符合条件的数据,则禁止输入。这样就可以预先系统地检查数据的正确性、有效性,避免错误的数据录入。——百度百科

01→

我们在报表制作过程中,常常需要输入不同类型的数据,例如时间、证件号码、下拉列表等。在录入数据(特别是大量录入)时,难免存在输入错误,从而影响到数据的质量和计算结果。那么如何控制这些错误的发生呢?,除了细心、细心、再细心之外,事先对相关字段进行数据有效性的设置,是个行之有效的办法。

在Excel2010中,可以通过【数据】→【数据工具】→【数据有效性】来打开“数据有效性”对话框


小树良品


小树良品

02→

学习几种常见的数据有效性的应用:预先设定数据的输入格式、限制中文输入、制作下拉菜单、设计自适应下拉菜单、数据的唯一性检验

(1)预先设定数据的输入格式:“数据有效性”可以根据字段数据类型和范围进行格式预设。如下图对于表格中年龄一列,只能输入整数,通常介于0—100岁,利用数据有效性可以进行如下设置:


小树良品

设置完成后,可在C列中进行录入,当录入的数值在0—100之外,系统就会自动弹出窗口,提示输入错误,如下图:


小树良品

同理,对于身份证号码,我们也可以限定数字的长度;对于入职日期也可以设置格式和时间。

(2)限制中文输入:在【数据有效性】“允许”中选择“整数”,的确可以限制中文的输入,但同时字母也无法进行输入。我们可以通过函数来进行设置:同上在【数据有效性】“允许”中选择“自定义”,然后在“公式”中输入:=AND(LEN(C2)=LENB(C2),LEN(C2)<=3),如下图:


小树良品

点击确定,完成有效性设置。公式含义:因为单独字母和数字均为单字节,汉字为双字节,所以采用LEN和LENB(前面函数系列已提,可以自行复习记忆)

(3)制作下拉菜单:在Excel中有两种方法制作下拉菜单,第一种就是现在的数据有效性,另外一种后续会讲到动态图表小节(后续再展开),在【数据有效性】“允许”中选择“序列”,然后在“来源”中输入选项即可,注意选项之间一定要用英文状态下的逗号,如下图:


小树良品

当然也可以在“来源”中,选择预先制作好的选项列表,假设选项列表与下拉单元格不在同一张工作表,如选项列表在Sheet 1,而下拉单元格在Sheet 2。我们需要在“来源”中输入=INDIRECT(“Sheet 1!选项列表”)这样就可以横跨两个工作表来制作下拉菜单。

(4)设计自适应下拉菜单:使用下拉菜单有两个目的:一是限定选择范围,防止输入错误,二是提供交互选择,实现灵活查询。但当遇到下拉列表太长,下拉选项数量以白计甚至千计时,要想快速从下拉菜单中找到目标选项反而不那么容易了,通过结合函数可以实现下拉菜单选项的快速选择。在【数据有效性】“允许”中选择“序列”,然后在“来源”中输入函数OFFSET()、MATCH()、COUNTIF()嵌套组合(暂不展开,利于职场新人快速吸收)

(5)数据的唯一性检验:对于不重复的数据列表,在表格录入时,为了防止重复录入,也可以使用“数据有效性”一旦录入的内容在前面已经录入过,就会出现窗口提示,我们同样需要结合函数公式设定。在【数据有效性】“允许”中选择“自定义”,然后在“公式”中输入:=COUNTIF(A:A,A1)=1,A:A为整个A列,A1为相对引用。每次录入时,公式会自动判断该数据在整个列中是否唯一,如输入数据不唯一,则系统弹出提示对话框,并拒绝录入。


小树良品



愿你如小树一样,茁壮成长!

推荐阅读更多精彩内容

  • Spring Cloud为开发人员提供了快速构建分布式系统中一些常见模式的工具(例如配置管理,服务发现,断路器,智...
  • 一、误删资料恢复 一不小心删错了,还把回收站清空了,咋办啊?只要三步,你就能找回你删掉并清空回收站的东西。 步骤:...
  • 1所有成功学都暗示人人都可以成功。说人的98/100的脑细胞没被开发。 学了成功学的去创业基本都失败了,但成功学本...
  • 还记得当初喜欢上文字是高一的时候,那时候的我单纯只是觉得好玩,只是觉得发现了一个新大陆。在班里总有那么几个...
  • 在讲解CSS布局之前,我们需要提前知道一些知识,在CSS中,html中的标签元素大体被分为三种不同的类型:块状元素...