对于数据分析,数据源的整理和清晰往往是最初始也是最重要的一步。大多数场景下,数据源基本都是从某些应用的后台数据库导出的,那么就数据源中某些字段的数据格式就和应用,数据库,导出所在路径的系统有关。有的时候某种类型的数据在不同的系统环境中会呈现出不同的格式和遇到不同的问题。比如下面这个截图中的日期格式就来自于某系统后台数据库中的相同字段(字段可以理解为列标题)。(几万行数据的局部)

           Excel 日期格式的终极整理方法_Excel

从截图中的日期格式可以出源系统中的日期格式应该有两种类型组成: 1. dd-mm-yyyy   2 dd/mm/yyyy. 

导出到excel之后dd-mm-yyyy  这种格式的日期并没有被识别成日期。

而dd/mm/yyyy. 这种格式的日期被excel识别成日期,但是格式变成了 mm/dd/yyyy。但并不准确,excel只是根据默认的日期格式(,如下图)粗暴的就把数据源的日期格式直接识别过去了。以AM2单元格为例,  6/10/2020 是日期格式,表示2020年6月10日。 可是数据源原本要表示的是 2020年10月6日。

Excel 日期格式的终极整理方法_TRANSFORM DATE_02

对于这列数据的整理,要多些曲折。

1首先我们先把 这列中的 “-”  都替换成 “/".

Excel 日期格式的终极整理方法_DATE_03

2 需要把这列当中将- 替换成 /的文本格式的日期 dd/mm/yyyy ,转换成 mm/dd/yyyy。 

这一步我们现需要通过筛选把靠左的那些值筛选出来。也就是把文本筛选出来。其实很简单,筛选是不选择识别出的日期就行了。

Excel 日期格式的终极整理方法_DATE_04

接下来我们需要 文本函数来帮忙:=MID(AM5,4,2)&"/"&LEFT(AM5,2)&"/"&MID(AM5,7,4)   (然后下拉复制公式)

Excel 日期格式的终极整理方法_TRANSFORM DATE_05

3 接下来开始整理excel识别有误的日期格式。这些被识别出来的日期格式数据,我们是没有办法用上一步的文本函数取操作的。 那我们需要换个思路就是把识别有误的日期格式数据转换成文本格式。 :=TEXT(AM2,"dd/mm/yyyy")

 Excel 日期格式的终极整理方法_DATE_06

4 去掉所有列的筛选。辅助列的所有日期形式的数据从表面上看就可以表达 mm/dd/yyyy了,但是整理还没有结束这些数据虽然表面是一种形式,但是实际上还是有文本有日期数据类型。

Excel 日期格式的终极整理方法_DATE_07

5 ,选中辅助列,然后全列重新设置为日期格式。

Excel 日期格式的终极整理方法_DATE_08

 

6 然后我们选中AN列并copy全列,并仅将值粘贴回来。

Excel 日期格式的终极整理方法_TRANSFORM DATE_09

 

7 然后再全选AN列,并将AN列以空格为间隔进行分列  Text to columns。。这样AN列将的到全部正确的日期格式数据。

Excel 日期格式的终极整理方法_Excel_10