|
发送【交流】 立即进【秋叶同学会】交流Excel！
本文作者：小爽 本文编辑：雅梨子、竺兰 大家好，我是在研究 Excel 数据的小爽，今天我们来看看日期时间数据格式的相关知识。 用户在进行网站界面浏览操作的时候 , 系统会自动记录下这些时间。 对网站用户停留时间的分析，有助于我们判断，用户对于该网站的粘性程度，以及该网站的内容质量是否还需要优化。 我们先将这些日期时间数据从系统导出到 Excel 中，现在，我们想要知道两者之间的操作时长，应该怎么做呢?
日期时间的本质是数值，它们可以直接进行相加减。 但是，如下图，上面这些从系统中导出的日期时间格式不规范，所以我们还需要事先将其转化为标准的日期时间格式。
下面，我们就来看看两种做法： ① Excel 函数 (需要用到 Left 函数 , Right 函数 , Text 函数)。 ② PowerQuery。 PS. PowerQuery(简称 PQ) , 是 Office 的 Excel2016 以及以上版本自带的插件 , 2013 版本需要另行安装插件 , 公众号后台回复【插件】可获取下载地址。
Excel 函数 在 Excel 中，将日期格式转化为标准日期，一般我们最先想到的函数是——Text 函数，它能够让单元格数值按照指定的格式显示出来。 在案例中，20220613 17:10:19，包含两部分内容，日期+时间。 ▋对于日期的提取 : ❶ 利用 Left 函数将前面的日期数字提取出来。
❷ 将日期数字串格式转为日期格式的文本串，这里我们用的是 Text 函数。 =TEXT(数字,格式代码)
❸ 将文本型的日期字符串改成数字，我们只需在公式前面加上-- 。
然后修改数字格式为短日期，即可显示为日期。数字格式转化为日期格式后 :
▋对于时间的提取 : 如下图，我们利用 Right 函数提取时间。
我们知道日期的本质是数值，而时间的本质是个小数，所以日期时间就是日期+时间。
这时，我们就已经将不规范的日期时间，通过公式改成规范的日期时间格式了。 再合并一下公式。
▲ 左右滑动查看
回到案例中，利用公式分别将提交时间和操作时间 ，转为规范的格式。
两者日期时间相减，由于我们需要的是时长，也就是小时数，因为 1 天=24 小时，所以还需要*24。
小补充 : 利用数组公式合并三个公式，如果这里看不懂也没关系，我们也可以直接公式写长点，两个日期时间相减后再乘。 PS. 除 Office365 之外，数组公式需要按三键【Ctrl+Shift+Enter】结束，输入公式后按回车是不行滴~
▲ 左右滑动查看
到此，Excel 函数做法，就介绍完啦~
接下来，我们进入 PQ 法的讲解。
PQ 做法 说到 PQ，第一步，大家知道我们要干啥了吧? 不管三七二十一，先将我们的数据源导入到 PQ 中。 选中数据源 - 【数据】选项卡 -【 来自表格/区域】 - 【确定】。 PS. 不同版本的 Excel 导入操作路径可能略有些不同。
在 PQ 中对于 20220823 18:00:00 这类格式，虽然不能利用 DateTime.From 函数直接识别，转化为日期时间格式，但是它可以识别 T 左边日期，右边时间。 所以我们可以通过将空值替换为 T，再来进行转换。 我们来看看界面操作。 ❶ 替换值。 选中两列 - 在【转换】选项卡下 - 单击【替换值】 - 要查找的值为:空 - 替换为:T。
❷ 识别日期。 选中两列，在【转换】选项卡下，单击日期下面的分析按钮 , 然后简单修改一下公式。
由于界面上的分析功能只有日期 / 时间，没有日期时间类型的分析，所以我们还需要更改公式。
更改前 :
▲ 左右滑动查看 更改后 : ▲ 左右滑动查看 动图操作效果:
❸ 增加一列计算两列之间差值。 在【添加列】选项卡下 - 【自定义列】 - 输入公式 - 【确定】。
▲ 左右滑动查看
此时效果已经完成了，后面导出数据进行下一步分析就可以啦。
如果你还在疑惑怎么导出 PQ 数据，那就不应该啦~
PQ 的界面做法，到这里，就介绍完啦~ 接下来我要讲的是 PQ 的另一种方法（M 函数），难度略高，目的是为了拓展一下大家的思路，如果你不感兴趣的话可以跳过这一 Part~
延伸拓展 思路一 : 将字符串替换为 PQ 可以识别的文本型日期时间 , 利用 DateTime.From 函数返回。 公式如下:
▲ 左右滑动查看
思路二 : 通过 DateTime.FromText 一一识别对应的格式信息 小 Tips: 格式 : 'yyyyMMdd HH:mm:ss' 月(month)和分(minute)缩写都是 m，所以为了区分，大写 M 代表月，小写 m 代表分，H(24 小时)，h(12 小时)。 这里的时间是 24 小时的形式，所以小时这里是 H。 公式如下 :
▲ 左右滑动查看
电量只剩下 1%。
最后的话 本文介绍的是「如何计算两个日期时间之间的时长？」 如果日期时间规范的话，就很简单，直接相减。 但在实际工作中，我们面临的绝大多数是不规范的日期和时间，这时就需要掌握转换的方法了。 Excel 函数中经常用到的是 Text 函数，将值文本格式化为标准的日期格式，然后再来进行数值转换。 而在 PQ 中，分析功能可以识别大多数不规则的日期，但是少数还需要我们进行技巧性转换。 衍生阅读: Text 函数 : https://mp.weixin.qq.com/s/RtgS2cjs62IUp3u0wlhnQw PQ 文本格式化函数 : https://mp.weixin.qq.com/s/rxeC0WIql8eAIji8W3Vl0Q
大家在工作中，有没有遇到 各种 奇奇怪怪的不规范的日期时间呢?在留言区与小伙伴一起一吐为快吧。
小 E 推荐你学习《秋叶 Excel 3 天集训营》！助你全面、快速提升 Excel 技能，消灭工作中常见的效率黑洞！
 |
|
|
