在数据分析时,我们经常需要将日期数据作为一个分类,例如,在下面的透视表中,我们就将日期作为一个分类,计算每个月的销售额,
Excel的数据透视表提供了非常方便的日期分组的能力,可以根据需要随时统计年/季度/月/日/小时/分钟/秒级别的汇总数据,
这一切只需要我们在源数据中有一列标准的日期列即可:
错误的日期
在Excel中,正确的日期列应该是:单元格格式为常规或日期,用“/”或“-”分隔的日期类数据,并且不包含错误的日期数据。
根据这个定义,错误(包括不规范)的日期格式主要包括下面这些形式:
1. 年月日分开三列
2. 错误的日期格式
常见的日期格式的错误有3种,分别时
用数值格式显示的日期
没有分隔符的日期
错误的分隔符的日期
错误的单元格格式
3. 错误的日期数据
尽管日期列格式正确,但是其中包含错误的日期数据:2013/6/31
上面的所有的日期错误(或不规范)类型,都会导致后续的统计分析复杂化,甚至产生错误结果。应该在数据清洗阶段对它们进行纠正。
下面就分不同的错误类型介绍如何对这些错误的日期数据进行清洗。
清洗年月日分开的日期数据
针对这样的日期数据,
我们需要使用函数。添加一列空白列,然后输入公式:
=DATE(B3,C3,D3)
清洗错误格式的日期
在前面,我们将错误的日期格式分为4类:
数值格式显示的日期,比如,41387
没有分隔符的日期,比如,20130416
错误的分隔符,比如2013.04.16
日期格式虽然正确,但是单元格格式为文本
根据错误类型的不同,我们需要使用的方式不同。
清洗方法:数值格式显示的日期
针对这种情形,只要选中日期,在开始选项卡中的数字中,将格式修改为短日期即可,
清洗方法:其他三种错误格式的日期
对于其他三种错误格式的日期,我们可以使用同一种方法进行清洗。
选中该列数据,在“数据”选项卡上点击“分列”,
在分列对话框,一直点击下一步,
在最后一步时,选择“日期”,然后点击完成,
根据错误内容的不同,有可能直接就得到了正确的结果,
也可能得到的是数值,
这就变成了前面“数值格式的日期”了,只要改变数字格式为“短日期”即可。
清洗错误的日期数据
与前面的几种情形不同,处理错误的日期数据必须首先找到哪些数据是错误的,
尽管在上图中,我们很容易就发现这个错误日期(2013/6/31)。但是在实际中,这个工作很难通过肉眼完成。想象一下,在几万行数据中发现错误的日期,应该是一个不小的挑战。
我们可以采用下面的方法。
首先在表格中添加辅助列,填充序列1,2,3,...
然后鼠标点击日期列任意单元格,在“数据”选项卡中,点击“降序”排序,
得到排序后的日期,
可以看到,多有错误的日期数据都排在最前面(这是因为所有的错误日期都会被当作文本处理,在排序时,文本大于任意的日期),这样我们就很容易发现错误日期了。
发现后,就可以进行相应的处理了。要么删除这些数据,要么修改为正确的日期。例如,在本例中,我们可以将“2013/6/31”修改为“2013/6/30”,
然后点击辅助列任意单元格,“数据”选项卡中点击“升序”排序,
排序后,删掉辅助列即可,
注,最后的排序是为了恢复原有的顺序。如果顺序不重要,就没必要进行这一步。前面的添加辅助列也没有必要进行。
总结及其他
日期数据在数据分析中扮演了非常重要的作用,Excel也提供了很多工具和函数帮助我们使用日期数据。因此,在原始数据中维护一份正确的日期数据是一个很重要的工作。如果源数据中的日期不规范,你就可以采用这里的方法来进行清洗。
如果你的数据比较复杂,这个任务可能很艰巨,你可以关注下面的公众号:ExcelEasy寻找更多方法和技巧,或者使用我们的数据清洗服务,让我们来帮助你完成这个工作。
联系客服