拿到快递运价表，别再傻傻地手动计算啦！

点击上方

蓝色

文字  关注我们吧！

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

前两天有一个群友问我，如何能自动计算快递的运价。原来，她有一份某快递的运价表，有不同的始发地和目的地，每次计算运价时，她都要手动来计算，费时费力，还容易出错。她希望能够一键计算运价。可以做到吗？

计算规则是：

  • 如果重量小于30KG，则直接从表中查询基础运价

  • 如果重量大于30KG，对于始发地在上海的快递，则直接用对应的超重后的单价乘以重量，得到基础运价

  • 如果重量大于30KG，对于始发地在北京的快递，则首先查询30公斤的基础运价，再加上对应的超重后的价格乘以超重重量，得到总的基础运价

始发地是上海的基础信息如下图。

始发地是北京的基础信息如下图。

最终的计算界面是下面这个样子的：

01

两份地区的基础价格表结构上略有不同。首先我们将它们的文件结构调整一致。基础价格信息都是在单元格区域A1:J61，国家和地区信息都从地68行开始。

在单元格E2中输入“=ROUND(IF(AND(A2="北京",C2>30),VLOOKUP(C2,INDIRECT(A2&"!$A$2:$J$61"),MATCH(INDEX(INDIRECT(A2&"!$A$69:$A$81"),MATCH(B2,INDIRECT(A2&"!$B$69:$B$81"),0)),INDIRECT(A2&"!$B$1:$J$1"),)+1,TRUE)+(C2-30)*VLOOKUP(C2,INDIRECT(A2&"!$A$63:$J$65"),MATCH(INDEX(INDIRECT(A2&"!$A$69:$A$81"),MATCH(B2,INDIRECT(A2&"!$B$69:$B$81"),0)),INDIRECT(A2&"!$B$1:$J$1"),)+1,TRUE),IF(C2>30,VLOOKUP(C2,INDIRECT(A2&"!$A$63:$J$65"),MATCH(INDEX(INDIRECT(A2&"!$A$69:$A$81"),MATCH(B2,INDIRECT(A2&"!$B$69:$B$81"),0)),INDIRECT(A2&"!$B$1:$J$1"),)+1,TRUE)*C2,INDEX(INDIRECT(A2&"!$B$2:$J$61"),MATCH(C2,INDIRECT(A2&"!$A$2:$A$61"),1),MATCH(INDEX(INDIRECT(A2&"!$A$69:$A$81"),MATCH(B2,INDIRECT(A2&"!$B$69:$B$81"),0)),INDIRECT(A2&"!$B$1:$J$1"),))))*D2,2)”并向下拖曳即可。

看到这个大家一定要头晕了，失去信心了。不过没关系，这个公式不求理解，只要会按照实际情况套用即可。

02

下面简单分析一下思路。

  • 当重量小于30KG时，无论是北京还是上海，都可以直接从基础运价表中查得基础运费

  • 当重量大于30KG时，无论是北京还是上海，直接用重量乘以单价即可。这时可以利用IF函数对上述两个条件进行判断

  • 当重量大于30KG时，从北京发出的货物，需要首先查的30KG的基础运价，再计算超重部分的基础运价，两者和才是总的基础运价。这时可以再利用IF函数对上述三个条件进行判断，同时屏蔽了上面第二个条件中“从北京出发大于30KG的货物直接用重量乘以单价”这样错误的计算

  • 利用INDIRECT函数来选择不同的发货地区

03

第一个条件的公式是：

INDEX(INDIRECT(A2&"!$B$2:$J$61"),MATCH(C2,INDIRECT(A2&"!$A$2:$A$61"),1),MATCH(INDEX(INDIRECT(A2&"!$A$69:$A$81"),MATCH(B2,INDIRECT(A2&"!$B$69:$B$81"),0)),INDIRECT(A2&"!$B$1:$J$1"),))))*D2

第二个条件的公式是：

IF(C2>30,VLOOKUP(C2,INDIRECT(A2&"!$A$63:$J$65"),MATCH(INDEX(INDIRECT(A2&"!$A$69:$A$81"),MATCH(B2,INDIRECT(A2&"!$B$69:$B$81"),0)),INDIRECT(A2&"!$B$1:$J$1"),)+1,TRUE)*C2,INDEX(INDIRECT(A2&"!$B$2:$J$61"),MATCH(C2,INDIRECT(A2&"!$A$2:$A$61"),1),MATCH(INDEX(INDIRECT(A2&"!$A$69:$A$81"),MATCH(B2,INDIRECT(A2&"!$B$69:$B$81"),0)),INDIRECT(A2&"!$B$1:$J$1"),))))*D2

第三个条件的公式是：

IF(AND(A2="北京",C2>30),VLOOKUP(C2,INDIRECT(A2&"!$A$2:$J$61"),MATCH(INDEX(INDIRECT(A2&"!$A$69:$A$81"),MATCH(B2,INDIRECT(A2&"!$B$69:$B$81"),0)),INDIRECT(A2&"!$B$1:$J$1"),)+1,TRUE)+(C2-30)*VLOOKUP(C2,INDIRECT(A2&"!$A$63:$J$65"),MATCH(INDEX(INDIRECT(A2&"!$A$69:$A$81"),MATCH(B2,INDIRECT(A2&"!$B$69:$B$81"),0)),INDIRECT(A2&"!$B$1:$J$1"),)+1,TRUE),IF(C2>30,VLOOKUP(C2,INDIRECT(A2&"!$A$63:$J$65"),MATCH(INDEX(INDIRECT(A2&"!$A$69:$A$81"),MATCH(B2,INDIRECT(A2&"!$B$69:$B$81"),0)),INDIRECT(A2&"!$B$1:$J$1"),)+1,TRUE)*C2,INDEX(INDIRECT(A2&"!$B$2:$J$61"),MATCH(C2,INDIRECT(A2&"!$A$2:$A$61"),1),MATCH(INDEX(INDIRECT(A2&"!$A$69:$A$81"),MATCH(B2,INDIRECT(A2&"!$B$69:$B$81"),0)),INDIRECT(A2&"!$B$1:$J$1"),))))*D2

04

下面我们就一起来看看完成后效果吧！

-END-

长按下方二维码关注EXCEL应用之家

面对EXCEL操作问题时不再迷茫无助

戳原文，更有料！免费模板文档！

赞 (0)

相关推荐