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

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

动手操作是熟练掌握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)

相关推荐