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