设置
  • 日夜间
    随系统
    浅色
    深色
  • 主题色

Excel 中高效率的日期数据整理方法分享

秋叶Excel 2023/2/21 21:47:09 责编:梦泽

原文标题:《比你笨法快 10 倍,这才是 Excel 中最牛的日期数据整理方法!》

在日常工作中,处理数据时除了查找、求和等之外,我们还会经常与日期数据打交道。

其中我们经常使用的函数,有 Date(合并日期)、Year(获取日期的年份)、Month(获取日期的月份)、Day(获取日期的天数)等等。

这不,最近朋友发来一个数据,是从系统里面导出来的。如下图:

该系统自动将日期与型号混在一起,现在需要将其中的日期提取出来。

提取规则是:

❶ 前三个字符代表的是月份数。

❷ 第 5 和第 6 个字符代表两位年份数。

❸ 第 7 和第 8 个字符代表两位天数。

提取结果如下图:

也就是将英文表示的【月-年-日】转换成纯数字的【年-月-日】形式的日期。

问题分析

我们平时处理的日期大部分是由纯数字组成的,可能很少接触到这类英文日期。

不过一旦遇到了,我们还是要想办法解决的,对不对?

比如前面提到的这个案例,我们可以使用下面的函数来提取出【年-月-日】形式的日期。

=DATE(MID(A2,5,2),LEFT(A2,3),MID(A2,7,2))

结果却是错误值!

为啥?接着往下看你就知道啦!

先来看下公式解析:

❶ 我们先用 MID (A2,5,2),提取出年份数

从该文本的第 5 个字符开始,截取 2 个字符,结果如下图:

❷ 再用 LEFT (A2,3),提取出月份数

从该文本的第 1 个字符开始,截取 3 个字符,结果如下图:

❸ 之后用 MID (A2,7,2),提取出天数

从该文本的第 7 个字符开始,截取 2 个字符:

❹ 最后用 Date 函数将【年月日】整合在一起。

=DATE(MID(A2,5,2),LEFT(A2,3),MID(A2,7,2))

看上去没有问题呀?为什么会出现错误值#VALUE 呢?

其实,问题就出在月份是英文字母。而不是数字。

Date 函数要求【年月日】三个参数都必须是数字。(不论是文本型数字,还是数值型数字)

思路有了,下面我们就来看看怎么解决吧!

解决方案

方案 A

将英文月份转变成数字,我们可以使用 Match 函数。

公式如下:

=MATCH("Mar",{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0)

公式解析:

MATCH(lookup_value, lookup_array, [match_type])

该函数有三个参数:

❶ 查找值。

第一参数:就是我们这里要查找的月份,比如:"Mar" ,

❷ 在哪个区域或者数组中查找。

第二参数:我们需要构造一个 1 月-12 月内存数组:

{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}

用 Match 函数去查找第一参数在第二参数中的位置,如果在第三个,就返回 3,正好也是 3 月份的意思。

❸ 精确或者是模糊匹配。

第三参数:输入 0,表示精确查找。

最终我们将上面公式整合在一起,结果为:

公式如下:

=DATE(MID(A2,5,2),MATCH(LEFT(A2,3),"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec",0),MID(A2,7,2))

公式的第二部分由 Match 函数组成。返回数字月份。

这样结果就出来了!

但是,小伙伴有没有发现年份是错的,Excel 自动给我们加了 19 数字。

这个还不是我们想要的,所以我们在年份前面加上 20 即可。

公式如下:

=DATE(20&MID(A2,5,2),MATCH(LEFT(A2,3),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),MID(A2,7,2))

如果小伙伴们不想手动输入月份的常量数组,也可以用下面的公式:

公式如下:

=DATE(20&MID(A2,5,2),MATCH(LEFT(A2,3),TEXT(20&-ROW($1:$12),"mmm"),0),MID(A2,7,2))

其中:

TEXT(20&-ROW($1:$12),"mmm")

构造一个英文月份的常量数组。这个公式的结果正好是上面的英文月份数组。

有兴趣的小伙伴们可以参考下。

方案 B

另外,除了上面常规的套路之外,还有一种非常非常简单的方法,就是下面这种方法。

在【设置单元格格式】==》【数字】==》【日期】中,查看内置英文日期的显示方法。

从图中可以看出:

英文日期的显示方式是:【日-月-年】

我们可以通过这种方法来组合成日期形式,然后再转换成目标日期。如下图:

【B2】公式如下:

=MID(A2,7,2)&"-"&LEFT(A2,6)

公式解析:

❶ 先用 MID (A2,7,2) 提取出天数:12

❷ 再用 LEFT (A2,6) 提取出月和年:Mar-22

❸ 最后用 & 符号再连接一个日期符号「-」:12-Mar-22

日期样式已经组合成系统内置的日期形式了。

但是,现在的问题是如何转换成纯数字的目标日期呢?

其实方法很简单!

就是进行一次数学运算即可!如下图:

【B2】公式如下:

=--(MID(A2,7,2)&"-"&LEFT(A2,6))

公式解析:

先使用一对括号 ( ) 放在原公式的外面,再使用两个负号(--)将其转换为纯数字日期。

就这样一个简单的转换就实现了我们的需求了,还是挺简单的吧!

写在最后

今天,我们分享了如何将英文日期转换为符合中国人习惯的纯数字日期。

❶ 使用日期和文本截取函数的常规方法将【月-年-日】转换成【年-月-日】。

❷ 模拟套用系统内置样式进行转换。

可见使用第二种方法,不仅公式非常简单,而且也大大的提高表格运行效率。

希望小伙伴们在平时的工作中,可以多多留意下系统里面内置的各种设置,肯定能发现不少好用的功能!

本文来自微信公众号:秋叶 Excel (ID:excel100),作者:明镜在心

广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,IT之家所有文章均包含本声明。

相关文章

关键词:Excel教程Excel学院

软媒旗下网站: IT之家 最会买 - 返利返现优惠券 iPhone之家 Win7之家 Win10之家 Win11之家

软媒旗下软件: 软媒手机APP应用 魔方 最会买 要知