input验证只能输入数字可以为小数_学会Excel数据验证，数据规范不出错

知道大家平时使用 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 公式中表示 and(和，并且)，所以这里也可以用 =AND(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 技巧欢迎留言~