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

2017-04-25 03:05平淡
电脑爱好者 2017年4期
关键词:统计人员单元格姓名

平淡

在日常工作中我们经常要对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提供的工具,就可以有效地避免这类数据的出现,同时在出现错误时可以快速找出错误数据。

猜你喜欢
统计人员单元格姓名
梁潮印笺·姓名章戢孴
梁潮篆痕·姓名类集
玩转方格
玩转方格
浅谈Excel中常见统计个数函数的用法
姓名的『姓』字为什么是『女』旁?
试论如何提高财政局统计人员的素质与能力
试分析街道基层统计工作的存在问题和对策
提高统计分析水平始于提高统计人员素质
找朋友