里的数据验证也太好用了叭

　　文施娟儀首发于一周进步
　　不知道大家平时使用Excel有没有这样饱受折磨的情况：
　　分发给别人填写的表格，明明已经把各项填写规范的文档一起打包好发给对方了，收回来的表格还是惨不忍睹
　　举个栗子：
　　填写规范17物流管理（1）班，对方填写17物流1班；
　　填写规范身份证18位数字，对方填写要么多一位，要么少一位
　　数据填写不规范直接影响了后续的统计和运算，特别是数据透视表这种功能，对数据源的规范要求很高。这时候就出现填表人很爽啊随心所欲，回收表格汇总统计的人却总是偷偷抹泪的情况。
　　真的是时候打一架！哦不不不，是时候做出一些措施了！
　　先放本篇干货大纲，如果你觉得知识点你已经会了，可以直接滑到需要的地方
　　01。什么是数据验证
　　数据验证位于Excel中数据选项卡下的数据工具功能组，根据Excel中的提示，它用于限制数据类型或用户输入单元格的值，最常见用法之一是创建下拉列表。
　　对方不爱认真看填写规范，那我们就让他们在填写时按照我们的指示来填写。这样既方便了他人填写，显得我们很贴心的样子，同时也防止自己统计出现错误，大家又可以一起好好玩耍了呢。
　　这里我概括一下几个基础用法。
　　02。数据验证有哪些基础用法
　　设置基础的验证条件
　　数据验证中的限定条件有整数、小数、序列、日期、时间、文本长度、自定义，用于限制输入的数据类型或输入单元格的值。
　　整数、小数和文本长度：
　　这三项比较简单，本质上都是限制范围，即限制对方只能录入你规定范围内的整数小数，或者规定对方录入的文本长度。
　　比如身份证的输入，可以先将单元格格式改为文本（因为Excel中超过15位数字会记为科学记数法），然后再到数据验证中限制其文本长度为18。
　　日期和时间：
　　其实这两者也是限制范围，为什么单独拎出来说呢主要是因为设置的时候，首先格式要注意好。
　　日期的规范录入是用横杠或斜杠隔开年月日：201911或者201911，如果输成2019。1。1，不好意思，不认你的。
　　时间一般用英文冒号隔开时分秒，但经过尝试这里用中文冒号它可以自动识别为英文，就不多说了～
　　序列和自定义：
　　序列用于创建下拉列表；自定义用于编写公式来限定单元格的填写内容。
　　选定单元格时显示输入信息
　　顾名思义，这个设置用于对方在点中单元格准备输入信息的时候，可以看到我们设置的提示，减少输入错误的几率：
　　出错警告
　　设置出错警告后，如果你已经利用数据验证设置好了填写规范，这时对方如果填错就会弹出警示框：
　　圈释无效数据
　　数据验证并不能完全制止他人录入规定外的数据，比如将额外的数据复制粘贴进来时，你设置的数据验证就已经被破坏了，有种防不胜防的感觉
　　所以表格填完后如果不放心，我们可以把原来设置的数据验证再设置一次，然后圈释无效数据：
　　这样就算被强制改动过的地方也逃不过数据验证的火眼金睛啦虽然这圈圈的外观需要再美观一些emmm（另外建议在这个操作前保存一下数据，这一步可能会有点卡）。
　　复制数据验证条件
　　如果间断地有好几个地方我们都想用同一个验证条件，这时候不用一个个地去设置，只要把验证条件粘贴过去就可以啦。
　　操作路径：复制选择性粘贴验证
　　03。如何制作下拉列表
　　相信一些比较基础的验证条件设置大家稍微琢磨一下都会操作了，比如设置下数值范围、设置下文本长度，都比较简单，那下面分享一些下拉列表以及结合函数的实际运用。
　　下拉列表是很常用的填写数据的方法，在谈数据验证如何设置下拉列表前，先说下单元格自带的下拉列表。
　　大家都知道在Excel中输入数据，如果下方开始输入内容时跟上面单元格有相同的字，它会智能地识别供你直接填充：
　　这就是Excel单元格下的隐藏列表，我们可以按键盘上alt向下箭头把这个列表调出来：
　　但其实每次按快捷键也没有特别高效，所以用数据验证设置下拉列表快点pick起来
　　一级下拉列表
　　前面我们说到啦，数据验证中的序列用于设置下拉列表，先来看最基础的一级下拉列表的两种方法。
　　方法1：直接输入列表项目
　　在数据验证窗口中，选择序列后，直接在来源中输入列表项目。
　　比如，性别的下拉菜单需要设置男和女两项，即输入男，女，注意逗号是英文状态下的逗号
　　方法2：将含有列表项目的单元格区域导入来源
　　二级下拉列表
　　先看数据源和效果图（通过设置下拉列表，使得各个大类中有对应的科目可供下拉选择）
　　step1：选中数据源区域，点击公式选项卡下定义的名称根据所选内容创建，勾选首行
　　这一步操作后，我们选中每一列细分课程，就可以在左上角名称框处看到它们已经被命名好啦，名称为首行的内容，即word、excel、ppt这个区域被命名为office，以此类推。
　　ps：你也可以选择在名称管理器中查看命名区域，这里是命名区域汇总的地方：
　　step2：设置好一级列表，这个就不用多说了跟前面的方法一样：
　　step3：设置二级下拉列表，选中区域，同样设置序列，来源中公式为INDIRECT（G4）：
　　效果达成啦。顺带解释下INDIRECT在这里的用法，INDIRECT的中文意思为间接的，这里可以理解为间接地引用：
　　比如上面这个例子中，直接地引用情况下，用A3表示返回A3单元格的内容，就是A2；用上INDIRECT间接引用后，返回A3单元格内容A2，这里A2又可以指向A2单元格中的内容，所以返回的是一周进步。
　　所以上面的INDIRECT（G4），间接返回的是以office、adobe和新媒体为名称的区域，即它们对应的细分课程。
　　自动更新：
　　为避免我们下次还要添加类别，重复设置二级下拉列表的操作，我们可以给每一列运用表格样式，使得数据源自动扩展区域。
　　操作路径：选中各列数据插入表格（快捷键ctrlT）
　　这样即使每一列会新增科目，下拉列表中也可以实时更新啦。
　　04。结合函数的实例运用
　　只能输入整数
　　诶，不就是限制录入整数嘛挺简单啊我会啊，上面不是说过了吗基础的验证条件。
　　然后一设置发现噢！基础的设置一定要给一个范围，不然不给限制：
　　这个时候就要用到函数啦。大家一定记得前面说到编写公式需要到自定义里设置对吧！这里用到公式：A1INT（A1）
　　INT是取整函数，它会向数值减小的方向取整，即返回小于等于参数的最大整数。
　　公式A1INT（A1），如果A1输入小数的话，那它永远也不会跟它取整后的数值相等，所以就会出错。
　　只能输入文本
　　公式：ISTEXT（A1）。ISTEXT用于判断值是否为文本。
　　只能输入数值
　　公式：ISNUMBER（A1）。
　　ISNUMBER用于判断值是否为数值，这里不多演示啦。
　　如果想输入范围在a到b内的数值，可以用（A1aamp；gt；0）（A1bamp；lt；0），这样就不受整数和小数限制了。
　　比如范围在100到200内的数值，可以用（A1100amp；gt；0）（A1200amp；lt；0）。乘号excela1100amp；gt；0，A1200amp；lt；0）。lt；！0）。乘号gt；
　　如果是小于100或大于200，即或的关系，可以用OR（A1100amp；gt；0，A1200amp；lt；0）。
　　只能录入日期
　　公式：DAY（A1）或MONTH（A1）或YEAR（A1）
　　DAY、MONTH、YEAR函数分别用于提取日期中的日、月、年，若单元格是文本等其它内容则无法提取。
　　另外，由于日期的本质为数字，所以输入数字依然不受影响，后续可选择将单元格格式改为日期：
　　限制录入重复值
　　限制录入重复值，即这一列中数下来只能有一个这样的值。
　　公式：COUNTIF（A：A，A1）1。
　　COUNTIF表示满足某个条件则计数上加一，即表示在A列中等于A1中这个值的单元格只有一个，如果有重复，则不止一个，就会出错。
　　05。写在最后
　　今天为Excel里的数据验证写了一篇应用文章，相信一定会有一些功能对你有所帮助。
　　内容很多，再来复习下今天的知识点：
　　什么是数据验证
　　数据验证有哪些基础用法
　　如何制作下拉列表
　　结合函数的实例运用
　　其中下拉列表在工作场景中还是非常常用和好用的，相信大家已经get到做法啦，函数只列举了一些简单的使用，具体还是得看使用场景、问题来结合，复杂的限制难度则较高。
　　那下次再见啦，有什么问题或者还有想要学习的Excel技巧欢迎留言