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

EXCEL应用之家 2021-09-17

展开全文

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

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

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

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

第二张表是价格表。

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

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

对于这种类型的问题，推荐大家使用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函数求和上述对应部分相乘后的结果，得到总价

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

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

思路：

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

-END-

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

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

我就知道你“在看”