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