自己动手找出Excel中的“非法数据”

平淡

在日常工作中我们经常要对ExceI中的数据进行统计，但是由于设置或者输入时使用格式错误，或者粗心大意重复输入数据，这类看似正确的“非法数据”会导致在后续统计中出现统计错误，而这些错误仅靠常规的方法很难发现。那么该如何避免非法数据的输入，并且快速找出这些非法数据并进行正确统计呢？

异常格式一件清理

在日常的报表中，由于对单元格格式进行多个设置，这样在进行汇总的时候就容易出错。比如下面的一份报表，B列是产品的销售额，但是使用求和公式计算后却发现总数是错误（图1）。

细心的朋友可能发现B4数据有些问题，切换到B4单元格，可以看到其数据类型是“文本”，默认情况下文本数据是不参与计算的，所以导致上述求和结果出错。这是典型的异常数据，解决的方法自然是将文本数据转换为常规数据即可。不过如果需要统计的数据很多，仅靠单纯肉眼是很难发现异常数据，而且单元格中格式多种多样，如何快速清除单元格中的众多格式？

这里可以使用CLEAN函数进行数据格式的删除，CLEAN是默认清除所有的非打印字符，包括可见的和不可见的，还能清除单元格的设置。只要你觉得ExceI数据有问题，那么就可以使用这个函数进行清除。新建工作表2，将A列的数据复制，在B1处输入函数“=CLEAN（Sheetl1！B1）”，然后往下填充公式，这样原来工作表中B列单元格里的各种格式都会被自动清除，现在再进行数据统计就不会出错了（图2）。

错误数据一目了然

对于部门比较多的公司，常规的子报表都是通过各部门统计人员自行统计，在日常统计输入中，除了单元格格式设置紊乱外，还出现重复、日期错误等常见的错误，这些错误导致的非法数据也很容易导致最终统计出错。比如下面的报表，使用求和公式统计时看上去并没有出现错误（图3）。

不过实际上上述表格中的2016/11/31数据是错误的（因为11月并没有31日），这样在单纯的数据统计上不会出现错误，但是在一些汇总如在透视表中使用“季度分组”查看时，此时会出现“选定的区域不能分组”的错误提示，因为数据里有不规范的日期格式。对于类似这种固定格式数据，我们可以通过Excel的数据验证快速找出来。

选中当前工作表的数据列，单击“数据一数据验证”，在弹出的窗口中，验证条件选择：允许“日期”，数据介于“2016/0I/01→2016/12/31”之间（图4）。

这样返回数据列，单击“数据一数据验证一圈释无效数据”，Excel会快速将无效日期格式使用红圈标识，只要按提示进行正确的更改即可（图5）。

当然在出现错误的时候再来查找什么地方有错误，这会给我们的工作带来很多的麻烦。其实在日常工作中类似的异常数据还有很多，比如在统计员工发放津贴表格中，如果重复输入某个员工的姓名，会导致最终统计的数据出错，而这类“软错误”很难在报表中找出来。为了避免这类数据输错，我们可以先使用数据验证将特定列的数据做好提前验证。比如统计员工姓名，这里要求不能输入重复姓名。先选中员工姓名列，同上打开数据验证设置窗口，允许选择“自定义”，在公式栏输入“=COUntif（A：A，$A2）=1”（图6）。

切换到“出错警告”，按提示设置好出错样式、标题和错误信息等内容，这样方便统计人员在输入时看到警告提示（图7）。

这样以后统计人员在输入员工姓名的时候，如果再次输入已经输过的员工姓名，Excel就会弹出出错提示，用户无法输入重复姓名数据（图8）。

使用数据验证可以很好地防止用户输入错误数据，这里主要是要根据实际输入数据设置好验证条件。比如要验证身份证号码（要求15位或18位），则在公式框中输入：=or（Ien（$B2）=15，len（$B2）=18），限定输入本月日期，则起始日期输入公式=DATE（YEAR（TODAY（）），MONTH（TODAY（）），1），结束日期输入=DATE（YEAR（TODAY（）），MONTH（TODAY（））+1，0）。由于这里是应用DATE函数设置日期，无论是哪个月份都可以自动对应，再也不会出现上述类似“2016/11/31”的错误。

总之在日常工作中，我們会遇到各种各样“非法数据”的困扰，只要我们掌握好Excel提供的工具，就可以有效地避免这类数据的出现，同时在出现错误时可以快速找出错误数据。