Excel实用技巧之时间数据的舍入
在处理时间类型的数据时，我们会遇到舍入操作，例如，
考勤时按照15分钟为计算单位，不足15分钟时按15分钟计算
计算服务时间时按10秒为单位，不足10秒按10秒技
......
这些操作很难通过简单的四舍五入函数实现，需要一点点特殊的技巧。
01
传统上我们使用ROUND函数来处理舍入问题：
这些操作可以用来处理数值的舍入，但是在我们提到的时间数据上的舍入就不好使了。
01
这种情况下，我们可以使用MROUND函数。
需要提醒一点：
这个舍入是四舍五入，如果B3中的值是2:42:00，结果就是2:40:00。而如果B3中的值是2:42:31，结果就是2:405:00。这是因为31秒首先四舍五入，所以变成了2:43:00
MROUND函数的作用就是将一个数值（第一个参数）舍入到给定数值（第二个）的倍数。
在图中的例子中，第二个参数是5/24/60，这是因为在Excel中时间数据的处理是这样的：
1小时=1/24
1分钟=1小时/60=1/24/60
所以如果，是舍入到1分钟的倍数，那么，可以使用公式
=MROUND(B3,1/24/60)。
这么写有时候觉得不太直观，我们可以使用下面的公式：
=TIME(HOUR(B3),MINUTE(B3)+(SECOND(B3))>30,0)
这个公式中，HOUR，MINUTE，SECOND是得到一个时间值的时，分，秒的函数，而TIME是将时分秒合成为一个时间值的函数。
这个函数的意思是原来的时间值中小时保持不变，秒变为0，而改变原来的分钟值，在原来的分钟值上，加上(SECOND(B3))>30。
如果秒值大于30，这部分公式(SECOND(B3))>30返回TRUE，在公式中进行加减乘除时当作1使用，否则，返回FALSE，在加减乘除时当成你0使用
总结下来，全部公式的意思是小时不变，秒为0，分钟根据秒四舍五入。
这就是舍入到1分钟的倍数。（所以，舍入到1分钟，可以不用 MROUND函数）。
而公式：
=TIME(HOUR(B3),MROUND(MINUTE(B3)+(SECOND(B3))>30,5),0)
就可以将时间值舍入到5分钟的倍数。
比较公式=MROUND(B3,5/24/60)，我们刚写这个公式看上去还是比较麻烦的，不过它更加直观一些，我们知道是在时分秒的哪一部分进行操作。
当然，我们也可以让公式=MROUND(B3,5/24/60)直观一些，例如，可以使用“注释
”的方法：
=MROUND(B3,5/24/60)+N('5/24/60表示5分钟')
其中后一部分作为“注释”可以输入任意文字，不影响公式结果。（关于公式加注释的内容请参看这篇文章）
03
实际上，在非时间数据中，也会有类似的需求。例如：
做促销方案的时候，有优惠方案是买5赠1，那么就需要将销售数量按照5的倍数进行舍入，可以使用公式
=MROUND(123,5)
结果是125。
前面我们提过，MROUND的处理方式是四舍五入，所以，如果你希望舍掉不足5的部分，那么可以使用公式：
=MROUND(123-2.5,5)
结果就是120。
提醒，这里-2.5，实际上减的是第二个参数的一半。
相反，如果希望不足5的按照5计算，那么可以使用公式：
MROUND(B3,5)+(B3-(MROUND(B3,5))>0)*5
B3的值是123，结果就是125。
这个公式实际上看看MROUND的返回值与原值是否有区别，如果小于原值，表示被舍掉了，所以需要进位。
最后，留一个思考题大家考虑一下：
如果我们的计费系统按照15秒计费，不足15秒按15秒算，大家考虑一下这个公式怎么实现。