二维码 购物车
部落窝在线教育欢迎您!

用Excel制作快递价格查询表,自动对比出不同公司的快递价格

 

作者:EXCEL应用之家来源:部落窝教育发布时间:2021-03-02 11:04:28点击:588

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:
在繁忙的都市工作中,快递已然成了公司传递信件和物件的首要选择。所以,在眼光缭乱的报价数据中做各种查询工作也已然成了打工人的日常。这种日常又琐碎的工作,有时肉眼可以轻松搞定,但是更多时候,肉眼却只会带来低效率和失误。那么,如何快速查询快递价格,并秒速对比后做出最优判断呢?其实可以用Excel这样做……

 

现在各个快递公司间的竞争非常厉害,都为了不同的服务对象制定了不同的收价服务,试图抢占更多的用户。

 

所以,当你选择快递公司时,面对眼前这样一份报价单,眼花就是一定的了。

 

 

那么,如何快速精准的找到今日份最佳合作公司呢?这事当然是交给Excel了!

 

高效地应用Excel查询一件货物的运费,只需要动动鼠标,输入几个数据,就可以得到费用总和。效果如下。

 

 

 

 

下面是两家著名快递公司,顺丰速运(EXPRESS)和DHL的价格清单和分区。

大家一起来看看,如何在繁杂的数据中精确查询吧!

 

注意:EXPRESS(顺丰)快递公司有两个不同业务,数据分别在“国际标快”和“国际特惠”两个Excel工作簿中;DHL快递公司只有一个业务,数据在名为“暂1”的Excel工作簿中。

 

 

 

一、数据整理

 

1.  设计格式,创建名称和数据验证

 

  首先需要新建一个Excel工作簿,整理一下收货地区。过程就不详述了,最后的结果如下,格式可以随个人喜好调整。

 

 

  按照下图所示,点击菜单栏上的“公式”,打开“名称管理器”,建立名称。

 

 

  如下图所示,创建数据验证。其中E列、F列和G列是三级下拉菜单。

 

 

 

  H列创建数据验证。

 

 

2.整理源数据的结构

 

为了确保数据计算的一致性,大家还需要对源数据的结构做一些调整。

 

比如下图中右侧公司的清单上,文件和包裹的最小计费重量是2公斤,而左侧公司的清单上,最小计费重量是2.5公斤。由于不同公司的报价单在细节上有些许差异,对源数据的结构调整就是必要的前期操作。在这里,作者把右侧清单上的最小计费重量调整为2.5公斤。

 

 

另外,两家公司对于超重部分的重量规定也不一样,因此大家要把这部分的数据调整到相同的起始行,并给他们定好相同的属于区域。

 

 

 

3.整理分区

 

把各自快递业务的分区整理出来,如下图中的M列和N列。

 

 

二、设置计算公式

 

运费计算规则:

货物没有超重时,直接查询重量和目的地,即可查到快递费用;货物超重时,用重量乘以对应的价格来计算出快递费用。

 

 

1.在单元格K15中输入公式:

=IFERROR(ROUND(IF(I14<=2.5,IF(H14="文件",VLOOKUP(I14,INDIRECT(F14&"!$B$8:$K$12"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE),VLOOKUP(I14,INDIRECT(F14&"!$B$13:$K$17"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE)),IF(IF(E14="EXPRESS",I14>=20,I14>=31),VLOOKUP(I14,INDIRECT(F14&"!$B$74:$K$80"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE)*I14,VLOOKUP(I14,INDIRECT(F14&"!$B$18:$K$72"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE))),2),"")

 

函数解析:

     这么长的一串函数,剔除最外层的IFERROR函数和ROUND函数后,其实就是由一个IF函数构成的。

     IF(H14="文件",VLOOKUP(I14,INDIRECT(F14&"!$B$8:$K$12"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE),VLOOKUP(I14,INDIRECT(F14&"!$B$13:$K$17"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE))这一部分是当判断条件 “I14<=2.5” 为真时,函数的运算部分。这部分整体上是一个IF函数。如果H14是文件,那么就到“$B$8:$K$12”这个地方去查找运费;否则,就到包裹“$B$13:$K$17”那里去查找运 费。

    确定跳转到相应的工作表。需要利用INDIRECT函数根据单元格F14中的内容跳转到对应的工作表中去。

     这部分中的INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0))组合,其作用是由MATCH函数确定单元格G14中的国别在N列中的位置,再由INDEX返回分区(M列)的信息。

     MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1部分是由MATCH函数返回数据区域的列数信息,最后由VLOOKUP函数模糊查找数据。

     IF(IF(E14="EXPRESS",I14>=20,I14>=31),VLOOKUP(I14,INDIRECT(F14&"!$B$74:$K$80"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE)*I14,VLOOKUP(I14,INDIRECT(F14&"!$B$18:$K$72"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE)))这一部分,是当判断条件“I14<=2.5”为假时,函数的运算部分。这部分也是一个IF函数,它可以进行条件判断。当货物超重时,通过INDIRECT函数跳转到超重价格区域“$B$74:$K$80”,计算运费;当货物未超重时,则直接在$B$18:$K$72中查找价格。

 

2.  在单元格K15中输入公式:=IFERROR(ROUND(K14*(1+J14),2),"")

这是很简单的数学计算公式,就不再赘述了。

 

总结:

今天的教程本身并没有什么难度,不要被那么长的公式吓住了。其实它就是IF函数配合INDIRECT函数,根据不同的条件在不同的工作表区域中抓取数据的常规操作。

 

对于今天的教程,大家可以下载课件,多多练习哦~

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

IF函数,剥洋葱

用Excel制作一个实现自动提醒补货和动态查询补货数量的表

INDEX:函数中的精确制导导弹,最强大的瘸子

还不会做Excel三级下拉菜单?其实它跟复制粘贴一样简单

版权申明:

本文作者EXCEL应用之家;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。