![e906b89fc432d8436067e01f39b0f49c.png](https://i-blog.csdnimg.cn/blog_migrate/45cd438c61dbcc1d77ad935ccd5ecb39.jpeg)
2020017 | Excel高效数据管理与分析
前段时间给一家银行企业的员工做了一场2个小时Excel的直播课,结束完课程之后,收到培训机构的F向我咨询了一个问题。F说她从直播平台的后台导出了昨天直播课的数据,里面包含登陆平台的学员信息和观看时长。
![041e1fb457abc7b62be147c4147672df.png](https://i-blog.csdnimg.cn/blog_migrate/c9faa042db73a3067f00252bcb13d649.jpeg)
F的问题是客户要求每个学员观看直播的时间至少要达到1.5小时才算达标,可是现在的时间除了都是中文的时间格式之外,还不完整,要如何才能判断出每个学员的观看时长是否达到1.5小时的标准?
如果要用函数来把不完整的时间变成完整的时间,会比较麻烦,要先用find函数检查是否包含“小时”,没有的话则补0小时。之后还要再做第二次检查,判断是否包含“分钟”,没有的话再补0分钟。
鉴于最终并不是需要一个规范的时间数据,那我们就可以将现在的时间想办法转成秒数,之后再跟1.5小时(5400秒)进行比较就可以了。
首先,我们先将B列的单元格修改为“文本”类型,之后用替换功能将“小时”替换成“*3600+”。
![9f8c5733bd946a9852f68d7a2e648d35.png](https://i-blog.csdnimg.cn/blog_migrate/5e56785372219af2d4d9358e0efcc789.jpeg)
这是第一次替换的结果:
![bafb6f31f2acc45dd41d07078921ce93.png](https://i-blog.csdnimg.cn/blog_migrate/fba3b7153e1569190444a11635f50dff.jpeg)
之后再用替换功能将“分钟”替换成“*60+”,这是第二次替换的结果。
![19be0696daa695a69204e500d3799a55.png](https://i-blog.csdnimg.cn/blog_migrate/fc13ab888d5deeba43d74e1982cf2f28.jpeg)
最后一次用替换将“秒”替换为空,也就是删除秒字,这是替换之后的结果。
![19b936518cd7d80f8ff844bab9821b54.png](https://i-blog.csdnimg.cn/blog_migrate/3c65a2bf26740d6b68a8639e6e23bc75.jpeg)
现在B列的数据其实已经就是一个公式的过程了,那要如何把这个过程变成一个结果呢?我们需要使用宏表函数evaluate。evaluate函数只有在定义名称下才能使用,可以实现将计算过程得到计算结果。
点击“公式”中的“名称管理器” ,在弹出的新对话框中点击“新建”, 然后输入名称,我这里输入的是“计算”,然后在引用位置输入:evaluate(引用的起始单元格地址),点击确定。
![a30471b602570cd146ddfe9e1a816d26.png](https://i-blog.csdnimg.cn/blog_migrate/a56fbcb4dca77efbc5732d99d8d4d588.jpeg)
然后在选择的单元格中输入定义的名称,B2单元格的公式的计算过程就变成了结果。
![9a095005a9429ec7d25b6e7baa09e039.png](https://i-blog.csdnimg.cn/blog_migrate/fe649bae9692e3213be7a1aa96e67819.jpeg)
最后再用if函数将这个计算出来的秒数和1.5小时的5400秒进行比较,就可以判断出每个学员的观看时长是否达标。
![0f0099514f76e986260bd76c558cd2e7.png](https://i-blog.csdnimg.cn/blog_migrate/b1a25c268889c30e370cc288fe3518eb.jpeg)
为了显示的更清晰直观,还可以用“条件格式”将不达标的单元格用颜色标示出来。
![1bbee7e4745ee651d3497c0143e4a1c5.png](https://i-blog.csdnimg.cn/blog_migrate/e28ed0302ead00a581eb19ed294c482d.jpeg)
这样操作是不是非常简单高效呢!