公司合作的快递公司要到期了,物流部同事拿着几份快递公司的报价表,请我帮忙分析下用哪个快递公司最便宜。那好吧,谁叫我数学好,EXCEL玩的也好呢~。如下图所示:
同事的意思是以我们的历史发货包裹重量数据(上图左侧,有11万多条数据!)为依据,然后用4个快递公司的报价(上图右侧)把对应的快递费用算出来,最后看哪家的总费用最低?
在算快递费前,我们要先了解下快递公司的收费模式,即:首重多少钱,续重多少钱,且不足1公斤要按1公斤收费。比如上图的快递A公司在浙江的报价是:首重(1KG)12.5元,续重1.5元,那么我们第一个单号的收费就是:12.5元,因为重量0.64小于1,第二个单号的收费就是:12.5 (1.63-1)*1.5(1.63-1=0.63,小于1,按1计算)=14元。
注意上述的难点是:不足1公斤如何表述成1?在EXCEL里很好解决的,即:把数字向上取整!于是我们在D2、E2、F2、G2分别输入如下函数:
=IF(C2<=1,VLOOKUP(B2,I:Q,2,FALSE),VLOOKUP(B2,I:Q,2,FALSE) CEILING((C2-1),1)*VLOOKUP(B2,I:Q,3,FALSE))
=IF(C2<=1,VLOOKUP(B2,I:Q,4,FALSE),VLOOKUP(B2,I:Q,4,FALSE) CEILING((C2-1),1)*VLOOKUP(B2,I:Q,5,FALSE))
=IF(C2<=1,VLOOKUP(B2,I:Q,6,FALSE),VLOOKUP(B2,I:Q,6,FALSE) CEILING((C2-1),1)*VLOOKUP(B2,I:Q,7,FALSE))
=IF(C2<=1,VLOOKUP(B2,I:Q,8,FALSE),VLOOKUP(B2,I:Q,8,FALSE) CEILING((C2-1),1)*VLOOKUP(B2,I:Q,9,FALSE))
上述函数是分别计算4个快递公司费用的,函数的意思都一样,我就解释一个就好了,以D2单元格的函数为例:
=IF(C2<=1,VLOOKUP(B2,I:Q,2,FALSE),VLOOKUP(B2,I:Q,2,FALSE) CEILING((C2-1),1)*VLOOKUP(B2,I:Q,3,FALSE))
整个函数的意思是:如果C2小于等于1,就直接把A公司在B2省份的首重收费价格取过来,否则(这时C2大于1)就用A公司在B2省份的首重收费价格加上续重的收费价格。
上面的函数中用了IF、VLOOKUP、CEILING三个函数,IF和VLOOKUP想必大家都会,就不多说了,CEILING函数比较少见,功能就是:给数字向上取整,即CEILING(0.63,1)其实就是等于1,CEILING(3.63,1)=4,依次类推。
其实除了CEILING函数,ROUNDUP函数也能实现向上取整的功能,请大家仔细拓展学习。把函数写好后填充到整个表格区域,最终写好的函数如下图所示:
然后我们把页面拉到最下面,看看到底哪个快递公司最便宜,如下图所示:
很明显,D公司最便宜!
拓展学习:这几个快递公司的报价体系都一样:即首重都是1公斤,且收费模式也一样,都是首重 续重。那如果首重不一样呢?很简单,调整C2<=1的参数1即可,比如某个快递公司的首重是3公斤,那么就把1调整为3即可。
还有些快递公司不是上述的收费模式,是这种:**元/公斤,最低收费**元。这时候只要多加个判断就行了,即:如果重量乘价格小于最低收费,就输出最低收费,否则就直接输出重量乘价格。
总之,不管快递公司的收费模式是什么样的,我们只要把这个收费模式转化成EXCEL能识别的计算机语言(函数)就行了。
“数据分析小哥哥”公众号,以后将不定时更新我在数据分析领域的见解,可能会有数据思维训练、数据分析过程解读、数据报告撰写、分析工具使用等方面的文章不断出现,如果你想学点数据分析方面的知识,想提升自己的数据分析能力,那么请跟着”数据分析小哥哥”一起前行吧,喜欢”数据分析小哥哥”的话,别忘了分享给你的同事、同学和朋友哦~