Excel基础学习园地公众号“Excel基础学习园地”是一个免费发布Excel基础知识、函数应用、操作技巧、学习方法等资讯的公众号,请点击上方“Excel基础学习园地”添加关注,方便我们每天向您推送精彩资讯。
问题如下图所示:
这是一类比较简单的计费问题,简单指的是计算规则,相比那种阶梯计费的问题来说,计算方式比较单一。
难点在于时长的计算,需要具备时间差的计算能力,同时还要具备到数字进位方面的能力……
再说就露馅了,还是那句话:结合自己掌握的函数去分析解题的思路,动手尝试后再看下文的内容,对于增加公式函数的使用经验是非常有好处的!请仔细理解题意并做思考后再继续阅读下面的内容!!!解法1
公式1:=HOUR(B3-A3)*14.5+IF(MINUTE(B3-A3)>30,1,0)*14.5
这是一个中规中矩的公式,用到了三个很基础的函数:HOUR、MINUTE和IF。
HOUR的功能是得到一个时间中的小时,MINUTE的功能是得到一个时间中的分钟。
在本例中,直接使用B3-A3就能得到停车的时间:
HOUR(B3-A3)*14.5计算出小时部分再乘以每小时的单价,就能得到停车费的一部分:
例如第一行数据,实际停车时间是6:36(6小时36分),6✖14.5=87。
IF(MINUTE(B3-A3)>30,1,0)这部分是利用IF函数实现了超过0.5小时进位的算法:
直接按公式翻译过来就是:当停车时间的分钟部分大于30(即0.5小时),则得到1,否则得到0。
明白了这一点,整个公式的思路就很清晰了。
这个公式难度较低,对时间类函数的基本用法有所了解就能写出来,公式的巧妙之处在于利用IF函数的特性,绕开了取舍类函数的使用,比较适合初学者。
相对于第一个公式而言,下面这个公式就有点难度了。解法2
公式2:=CEILING(HOUR(B3-A3)+IF(ROUNDDOWN(MINUTE(B3-A3)/60,2)>=0.5,1,"0"),1)*14.5
这个公式可以说是完全把计费标准用Excel的语言表述出来了,除了公式1中用到的三个函数之外,另外使用了两个取舍函数CEILING和ROUNDDOWN。
这个公式是把分钟先换算成小时:
例如第一行,实际停车时间中分钟值是36,要把36分钟换算成小时只需要除以60即可,这样换算后会出现小数位比较多的情况,要将多余的小数部分舍去,就用到了ROUNDDOWN函数,这个函数的功能就是将一个数字按照按照指定的小数位进行舍去计算。使用方法与ROUND函数完全一致,区别在于ROUND是按照指定的小数位进行四舍五入。还有一个ROUNDUP则是按照指定位数直接进位,关于这三个函数可以参阅教程:
公式2中的另一个取舍函数CEILING的功能是将一个数字按照指定的基数进位用一个简单的示例来说明这个函数的功能:
可以看到,所有的数据都变成了大于原数并且是最接近原数的0.3的倍数。
公式2的原理不难理解,虽然会显得比较啰嗦,不过对于熟练多个函数嵌套的还是有好处的。
其实这个公式完全可以这样写:
=(HOUR(B3-A3)+(MINUTE(B3-A3)/60>=0.5))*14.5
公式1也可以按这个写法调整为:
=(HOUR(B3-A3)+(MINUTE(B3-A3)>=30))*14.5
这是用到了逻辑值的特性,将IF函数也省去了。
关于这个问题的解法,还有其他几种巧妙的公式,咱们下次接着讨论,如果你已经想到的话,也可以留言哦!