在excel中怎样把不同重量对应的价格自动与重量匹配算出总价格

Question

各位大神，现在遇到一个运输计费问题。不同地区间货物运输，每个地区的运价不同，一种货物种类对应一个阶梯运价，阶梯运价根据货物的重量进行划分。即45kg以上一个价，100kg以上一个价，以此类推。现在图1的sheet1表内A列是客户，B列出发地，C列是目的地，D列为货物种类，E列是货物重量。图2的sheet2表内是各地区的重量运价表.请问要怎么使用函数直接匹配计算出每一个客户在各地区根据货物种类的总运费？

表格可以到我的云盘去下载~~先谢过各位大神了！！
http://pan.baidu.com/s/1sj0eT9V
各位大神，现在又碰到新问题，就是运价是有期限的，不同时间价格会变动，总运费要根据运输日期对应的运价去计算，同时价格表增加一个基础运价，该运价针对≤45重量，低于45的货物乘以基础运价，与最低运费比较，哪个高取哪个做结果。
真的可以我会加分的！！十分感谢！！坐等高人
新表格在这里————>>>http://pan.baidu.com/s/1dD7UTBb

Answer 1

G2输入公式

=IF(F2<=45,MAX(SUMPRODUCT((Sheet2!$A$2:$A$999<=A2)*(Sheet2!B$2:B$999>=A2)*(Sheet2!$C$2:$C$999=C2)*(Sheet2!$D$2:$D$999=D2)*(Sheet2!$E$2:$E$999=E2)*Sheet2!F$2:$F$999),SUMPRODUCT((Sheet2!$A$2:$A$999<=A2)*(Sheet2!B$2:B$999>=A2)*(Sheet2!$C$2:$C$999=C2)*(Sheet2!$D$2:$D$999=D2)*(Sheet2!$E$2:$E$999=E2)*Sheet2!G$2:$G$999)*F2),SUMPRODUCT((Sheet2!$A$2:$A$999<=A2)*(Sheet2!B$2:B$999>=A2)*(Sheet2!$C$2:$C$999=C2)*(Sheet2!$D$2:$D$999=D2)*(Sheet2!$E$2:$E$999=E2)*OFFSET(Sheet2!G$2:$G$999,,LOOKUP(F2,{45,100,300,500},ROW($1:$4))))*F2)

下拉填充，注意修改适合的数据区域，要不计算会很慢。

Answer 2

F2中输入

=E2*OFFSET(Sheet2!C$1,MATCH(B2&C2&D2,Sheet2!A$2:A$19&Sheet2!B$2:B$19&Sheet2!C$2:C$19,),MATCH(E2,{0,45.0001,100,300,500}))

同时按下CTRL+SHIF和+回车，输入数组公式，下拉。

其中那些分隔的数字，大于等于什么的，自己可以修改。

补充：

G2中输入

=F2*OFFSET(Sheet2!E$1,LOOKUP(2,1/((C2&D2&E2=Sheet2!C$2:C$37&Sheet2!D$2:D$37&Sheet2!E$2:E$37)*(Sheet2!A$2:A$37<=A2)*(Sheet2!B$2:B$37>=A2)),ROW($1:$36)),MATCH(F2,{0,45.0001,100,300,500}))

下拉，就能完成带日期的了，附件中第一行2月28日的，在对照表中找不到，被我改成3月1日的了。

附件已更换

追问

大神，≤45kg的运价还是不对呀，45kg以下重量不管多少，都按一个价格算运费，不用乘重量

追答

那前面再加个IF判断
=IF(F2>45,F2,1)*OFFSET(Sheet2!E$1,LOOKUP(2,1/((C2&D2&E2=Sheet2!C$2:C$37&Sheet2!D$2:D$37&Sheet2!E$2:E$37)*(Sheet2!A$2:A$37=A2)),ROW($1:$36)),MATCH(F2,{0,45.0001,100,300,500}))

追问

那可以针对低于45的货物再加判断不，价格表增加一个基础运价，该运价针对≤45重量，低于45的货物乘以基础运价，与最低运费比较，哪个高取哪个做结果。如图

附件：http://pan.baidu.com/s/1dD7UTBb

真心感谢了！！orz

本回答被网友采纳

Answer 3

看到楼主思路清晰，问题很有水平。
我现在不给你答案，给把问题补充一下，你把第二张表改一下列序是：
客户 重量 出发地 目的地 货物种类 总运费
其中 出发地 目的地 货物种类 通过设置数据有效性后，点击下拉菜单选择输入，这样可以保证输入的一致性和便捷性。

Answer 4

能不能一次把条件搞完。

还有，如果以后的量多的话，直接做一个宏就好了。

这个表还在完善过程中，起始日期和截止日期什么的，可能以后还要增加用颜色提醒是否超过截止日期，增加某日、某月、某半年什么的总运费统计汇总什么的。

所以建议你加好友。长期在线

Answer 5

难做的是重量与上面表中的100以上、300以上等匹配不好做，如果能在下表加一列，比如在总运费后加一列，“适用等级”，张三的最后列填上“100以上”。。。这样就好做了
“适用等级”这一列可以用数据有效性来做，鼠标点一下即可，也很方便的。