当我们在Excel表格中录入大量数据的时候经常会出现录入错误或录入的内容不规范的问题，这对我们之后的数据处理与分析带来很大的麻烦，那怎么样才能保证保障数据录入快速，规范和准确呢，今天就给大家分享一下Excel数据验证（Excel2013版以前的数据有效性）的神奇功能。
一、基本用法：
1.手工录入：当我们需要经常录入一些重复内容时，只需要在当前录入内容下面的单元格按住ALT+ ↓,就会出现下拉菜单，直接选择就可以不需要手动录入。当然这边下拉菜单的内容是你前面有手工录入的内容，如果之前没有录入的内容，是没办法出现在下拉菜单让你选择的。
2.引用表格现有内容：比如我们表格有预先录入参数表的话，就可以直接引用参数表现有的内容，操作方式：选中需要录入数据的区域，选择数据验证——验证条件：允许 选择 序列，在来源：输入或粘贴引用的位置，点击确定即可。
3.名称框：操作方法就是先把需要引用的内容建立名称框，然后选中需要录入数据的区域，选择数据验证——验证条件：允许 选择 序列，在来源：=名称框的名字（点击F3可以调出粘贴名称的窗口，直接选择需要用到的名称），点击确定即可。
数据验证的基本用法
二、进阶用法：
1.提示信息：可以对数据验证的区域设置标题和提示内容，在用户录入数据时起到提醒的作用。当然用批注的方式也可以达到提示的效果。
2.出错警告：针对已经设置数据验证的区域，当录入不符合验证内容时就会出现错误提示。
3.圈识无效信息：当我们设置好数据验证后，选择数据验证菜单下的圈识无效信息，就会把不符合验证单元格用红色圈出来，便于我们进行改正。
三、动态下拉菜单：选择需要验证的数据区域，选择插入表，或者按CTRL+T也可以创建超级表，数据验证-允许序列来源引用该超级表，当表格新增内容时，数据验证的序列也会动态更新。
：
四、快速录入当前时间：主要是数据验证序列来源引用显示当前时间函数=now(),然后数据区域单元格格式自定义显示hh:mm:ss即可。
五、借贷只能一方输入：会计上要求借方和贷方同一行正能录入一个数据，此时我们可以利用数据验证允许自定义，在来源输入公式=COUNTA($A2:$B2)=1,点击确定即可。
六、二级下拉菜单：首先设置一级菜单选择数据区域A列，选择数据验证-序列，来源引用一级菜单数据所区域，然后选择二级菜单B列数据区域，选择数据验证-序列，来源输入公式=indirect(A2),点击确定即可。
二级下拉菜单
拓展内容：当我们数据区域有新增时，能否动态更新呢？这个用函数的方法是可以实现的。
操作方式也很简单：选择第一级菜单数据区域A2:A22后，选择数据验证--- 允许序列，来源 粘贴公式：
=OFFSET($F$1,0,0,1,COUNTA($F$1:$XFD$1))，然后选择第二级菜单数据区域B2:B22后，选择数据验证--- 允许序列，来源 粘贴公式：
=OFFSET($F$1,1,(MATCH(A2,$F$1:$XFD$1,0)-1),COUNTA(OFFSET($F$1,1,(MATCH(A2,$F$1:$XFD$1,0)-1),999,1)))
补充说明：OFFSET函数详解：
=OFFSET(reference,rows,cols,height,width)
=OFFSET(参考系或参考区域，向下移动行数，向右移动列数，取值行数，取值列数)
Reference：参考系或参考区域
Rows：向下移动行数；
Cols：向右移动列数；
Height：取值行数；
Width：取值列数；
七、联想式下拉菜单：
操作步骤：
第1步 源数据排序。目的是把相似的内容排列在一起。如上图C列所示。
第2步 选取A列要设置的区域，右键菜单 - 定义名称。在如图所示的新建名称窗口中:
名称：城市
引用位置：
=OFFSET('7.联想式下拉菜单'!$C$1,MATCH('7.联想式下拉菜单'!A2&"*",'7.联想式下拉菜单'!C:C,0)-1,,COUNTIF('7.联想式下拉菜单'!C:C,'7.联想式下拉菜单'!A2&"*"),1)
公式说明：
match（）查找输入的字符串在C列的开始位置，&"*"实现模糊查找。
Countif（）统计以输入字符串开头的车型有多少个
offset()
获取开始行数和总行数，用offset生成动态引用区域。
第3步 数据选项卡 - 数据有效性 - 允许“序列”，来源 =城市
看完是不是觉得很神奇呢？赶紧动手试试吧！
网友评论