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