巧用Excel数据有效性实现6个数据规范效果

发布时间:2019-12-26 来源:网络


在使用Excel过程中,我们经常抱怨手头上的表格数据太不规范,处理起来简直把人累垮。比如一张学历调查表中,在最高学历一栏中,有人写“大学本科”,有人写“本科”,有人写“专科”,有人写“大专”,诸如此类的数据录入问题比比皆是。今天帮主就和大家介绍一下,如何利用数据有效性解决数据规范化的这些问题。


说明:在Excel2013版以前,数据有效性称之为“数据验证”,这里我们的操作基于2016版。


01 创建下拉列表


利用数据有效性这个功能,我们可以在表格中对指定区域创建有效性下拉列表。比如我们要在销售地区这一列里创建一个下拉列表,销售地区包括:北京,上海,广州,深圳,杭州和南京。


第一种操作:数据有效性设置来源直接输入用英文状态下的逗号隔开的文本



第二种操作:来源里直接引用单元格区域



第三种操作:利用名称定义,引用名称,这种操作方式可以跨表引用区域,也可以用来处理引用的序列不在同一行或同一列的问题。


因为数据验证的来源必须是对单一行或单一列的引用,如果引用的序列不在同一行同一列,可以先对第一列的序列定义名称后进行数据验证设置



然后编辑名称,更改里面的引用位置,下拉列表即可出现所有的序列。



02 限制数据输入范围


比如我们在销售日期这一列需要对日期范围进行限制:2018-1-1至2018-12-31。设置好后,如果我们输入这个范围外的日期,就会弹出一个提示框:“此值与此单元格定义的数据验证限制不匹配。”



03 防止重复输入


我们可以结合COUNTIF函数进行设置。比如我们要防止订单编号录入出现重复,进行数据验证自定义设置,输入公式:=COUNTIF($C:$C,$C1)=1



04 限制单元格输入的数据类型


有时候我们在某些特定的表格区域内只允许输入数值或者文本,如果只能输入文本的,结合ISTEXT函数进行自定义,检测输入的内容是否为文本,是的话返回TRUE,否则返回FALSE。如果只能输入数值,可结合函数ISNUMBER。


比如我们销量列里只允许输入数值,进行数据验证自定义设置,输入公式:

=ISNUMBER(D2)=TRUE



05 给单元格加上注释内容


给单元格添加注释,可以直接插入批注,这里我们利用数据有效性的“输入信息”设置给单元格加上注释,这样当我们在单元格内输入数据时,就会出现提示信息。



06 数据进行条件限制


利用数据有效性的自定义设置,我们也可以对输入数据进行特定条件限制。比如我们在产品类型里面,只允许输入以电器、家具这两种产品名称。我们在数据验证自定义设置里输入公式:=OR(D2="家具",D2="电器")