Excel 3个简单技巧轻松避免格式不规范数据出错（excel函数公式大全）

很多经常需要做Excel统计的童鞋都知道，一份表发给各部门的同事去填写，收上来的表格通常都是五花八门的。格式不规范不说，就是数据本身也经常会出现错误。很多童鞋虽然苦口婆心要求填写的同事按照要求处理，但效果总是不那么如意。那么有没有更好的办法来避免这些问题呢？今天给大家简单介绍3个简单的技巧。

重点关注优先级为1的项目

规范数据录入格式

这是一切问题的根源，如果我们能从源头上对分发下去的表格做好相应的设置，就会避免掉很多后续处理方面的问题。我们就以下面的表格作为例子，一一说明如何设置才能让填表人按照我们的要求去填写。

汽车销售记录表

这是一份简单的汽车销售记录表，我们要求姓名列的所有单元格最好不要有重复记录（因为很少有人一次买两台车的）；身份证号码的长度要么15位，要么18位，长度不对即为错误，为了提高录入准确性，我们可设置身份证号码列文本长度为15位或者18位……

1.制作下拉列表的小技巧

如果要填入某些列的数据比较固定，只有有限的几项填写内容。这样的列我们可利用Excel数据有效性设置下拉选项，填入时只需选择即可。这即可提高数据录入的效率，更可以避免不规范的数据格式（便于统计，格式不统一通常会导致统计结果错误）。下面我们就来看看设置下拉列表：

1)选中C2:C10单元格区域，在“数据”选项卡 中单击“数据验证”按钮，打开“数据验证”对话框；

2)单击“设置”选项卡，在“允许”下拉列表中选择“序列”类别；注意“允许”下拉列表右侧的“提供下拉箭头”一定要勾选；

3)鼠标直接选取表中的G2:G7区域，单击“展开”按钮，返回“数据验证”对话框；

4)单击“确定”按钮即可。

可使用快捷键Alt+A+V+V

忽略空值和提供下拉箭头处于勾选状态

2.杜绝单元格中空格的技巧

很多同事输入两个字的姓名时，喜欢在姓与名之间加上空格，已达到同其他三个字的姓名对齐（美观）的目的，但这样的操作却会给我们的统计带来很大的麻烦。我们可设置“单元格中禁止空格输入”达到规范输入的目的。按以下步骤操作吧：

1)选中A2:A10单元格区域，在“数据”选项卡 中单击“数据验证”按钮，打开“数据验证”对话框；

2)单击“设置”选项卡，在“允许”下拉菜单中选择“自定义”，“公式”框中输入公式：=ISERROR(FIND(" ",$A2))

3)单击“出错警告”选项卡，左侧“样式”下拉菜单选择“停止”，右侧框中按图所示输入内容，标题输入：“姓名之间请勿插入空格”，内容输入：“请检查是否在性名之间插入了空格！”自己想怎么输入就怎么输入咯。

4.单击“确定”即可。

打开数据验证的必要步骤

自定义功能非常强大，接下来你会看到更多的例子

出错警告！

输入内容中如果有空格将无法输入

出错警告中的样式有3种，“停止”、“警告”以及“信息”。选择“停止”，你将无法再单元格中输入不符合规范的内容；“警告”会提示你是否继续输入；“信息”只是提示你是否取消输入。

3.规范录入“车牌号码”的技巧

贵阳的车牌号码为7位，以“贵A”开头，我们在录入的时候，可以简单设置，避免录入错误或者无效的数据；跟上文相同的操作步骤我不再赘述，直接进入设置--自定义--公式，公式中录入：=AND(LEFT(D2,2)="贵A",LEN(D2)=7)。

公式千万别写错了

根据自己的需要可选择其他样式

规避或者圈释无效数据的技巧

无效数据没用，而且还会影响我们的统计结果。如何在制表之初就将这个问题解决掉，避免后续诸多的麻烦？且看一下技巧：

1.限定输入本月日期的技巧

我们统计提成或者销售额也好一般都是按月统计的，那么我们每月收上来的表格所有的内容就应该是当月的内容。下面的技巧可以避免员工录入其他月份的日期或者其他不在范围内的日期。

1)单击“数据”--“数据验证”--“数据验证”，打开“数据验证”对话框；

2)单击设置--允许下拉列表选择“日期”--输入开始日期：=DATE(YEAR(TODAY()),MONTH(TODAY()),1)，也可以直接输入本月的1号--输入结束日期：=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)，也可以直接输入本月的最后一天；--出错警告--停止--输入内容--确定即可。

3)此栗子中可直接输入本月的开始日期：2016-05-01，结束日期：2016-05-30。但是这样输入非常简单，但每个月都得更改一遍，远远不如用公式来得方便。请仔细琢磨本例中的公式。

注意公式的设置技巧

顺便你输咯！

2.提高身份证录入准确率的小技巧

尽可能减少身份证号码录入的错误率，下面是一个小技巧。本例中身份证号码的验证规则包括身份证号码长度必须为15位或者18位。

1)打开“数据验证”对话框“设置”窗口，“允许”下拉菜单选择“自定义”;

2)公式框中输入：=or(len($B2)=15,len($B2)=18)

3)填写“出错警告”内容后点击确定，如图所示：

多学Excel函数吧，很实用！

不再赘述

3.Excel录入避免重复值的技巧

Excel中如何避免同列录入重复值？请参考以下设置技巧，请按图中步骤操作即可，设置的公式为：=countif(A:A,$A2)=1。

出错警告再重复就没有意思啦

4.圈释无效数据的技巧

数据验证只是针对数据录入的，但一个表格已经填写完整，我们只是设置“数据验证”是无法找出“无效数据”的。如果别人给到我们一个表格，如何快速地标示出其中的不符合规范或者无效的数据呢？这里就要用到“诠释无效数据”的技巧。

1)首先，请根据需要设置“数据验证”；

2)使用数据诠释：数据--数据验证--诠释无效数据。

3)红色标示出的即是“无效数据”。如图：

设置数据有效性

圈释无效数据

最终效果

数据验证的删除技巧

设置了数据有效性，但是有些验证不需要了如何删除呢？首先呢，我们要找出表格中或者选区中设置了数据有效性的单元格，然后再执行删除的操作。

1)利用定位功能查到设置了数据有效性的单元格区域：选择中A2:E10,按下Ctrl+G或者F5打开“定位”对话框；

2)单击定位条件，进入“定位条件”，选择“数据验证”。单元格中的数据有效性即被选中；

3)按Alt+A+V+V,进入"数据验证“--”设置“，点击最下方的”

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。