按不同的目的地分段收费,大神写了一个公式直接搞定,再也不用手工调整了!

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

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

看到这样一个题目,具有一定的代表性,这里分享出来和大家共享。

第一张表中是快递的始发地和目的地、重量等信息,我们要计算出每一行的重货费用。

第二张表是价格表。

计费原则是:总费用=0至20KG为最低收费+(20至60KG)*对应单价+(60KG以上)*对应单价

如何能够做到一个公式计算出总的费用呢?

01

对于这种类型的问题,推荐大家使用FREQUENCY函数。

在单元格E3中输入公“=SUM(TEXT(FREQUENCY(ROW(INDIRECT("1:"&F3)),{20;60}-0.1),"[<=19]1")*TRANSPOSE(VLOOKUP(D3,表2!$A$3:$E$24,{5,3,4},FALSE)))”,三键回车并向下拖曳即可。

思路:

  • ROW(INDIRECT("1:"&F3))部分,将实际重量转换成了1~100(实际重量)的自然数序列

  • FREQUENCY(ROW(INDIRECT("1:"&F3)),{20;60}-0.1)部分,利用FREQUENCY函数对这个自然数序列进行分段计频。得到的结果是各个分段区间的内的货物的总重量。{20;60}-0.1的目的是要避免分段点20和60的统计错误,因为20和60是分别属于20-60档和60以上档的

  • TEXT(FREQUENCY(ROW(INDIRECT("1:"&F3)),{20;60}-0.1),"[<=19]1")部分,将小于19的数值强制转换为1,以便后续的继续计算

  • VLOOKUP(D3,表2!$A$3:$E$24,{5,3,4},FALSE)部分,利用VLOOKUP函数分别抓取最小计费、20-60公斤的价格以及60公斤以上的价格

  • 最后利用SUM函数求和上述对应部分相乘后的结果,得到总价

02

上面的这个方法稍显复杂。我们还可以将公式略微简化一下。

在单元格E3中输入公式“=SUM(TEXT(F3-{20,60,0}+1,{"[>40]4!0;!0;0","0;!0",1})*VLOOKUP(D3,表2!A:E,{3,4,5},))”,三键回车并向下拖曳即可。

思路:

  • VLOOKUP部分和上面的类似,这里不再细讲了

  • 同样是利用TEXT行数根据分段点(F3-{20,60,0}+1)将重量分为三部分,其结果为{"40","41","1"}

  • 最后对应相乘并求和得到结果

好了,今天和小伙伴们分享的就是这些内容。大家如果有任何疑问,可以私信我哦!

-END-

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

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

我就知道你“在看”

推荐阅读
(0)

相关推荐