Question

求大神帮忙，万分感谢！ 求EXCEL表格中运费计算公式

图1名称Sheet1；图2表格名称为‘’运费价格表‘’；求Sheet1中F列运费公式（运费=首重+续重*（计费重量-1）+保价费+送货进仓费）





Answer 1

假设两个工作表分别为SHEET1和SHEET2
在SHEET1的F2中输入或复制粘贴下列公式
=INDEX(Sheet2!$A:$A,MATCH("*"&LEFT(Sheet1!B2,2)&"*",Sheet2!$C:$C,))+IF(C2<=1,0,CEILING(C2-1,1)*INDEX(Sheet2!$B:$B,MATCH("*"&LEFT(Sheet1!B2,2)&"*",Sheet2!$C:$C,)))

或
=INDEX(Sheet2!$A:$A,MATCH("*"&LEFT(Sheet1!B2,2)&"*",Sheet2!$C:$C,))+(C2>1)*CEILING(C2-1,1)*INDEX(Sheet2!$B:$B,MATCH("*"&LEFT(Sheet1!B2,2)&"*",Sheet2!$C:$C,))
下拉填充

Answer 2

可用下面的公式：
=INDEX(Sheet2!$A:$A,MATCH("*"&LEFT(Sheet1!B2,2)&"*",Sheet2!$C:$C,))+(C2>1)*CEILING(C2-1,1)*INDEX(Sheet2!$B:$B,MATCH("*"&LEFT(Sheet1!B2,2)&"*",Sheet2!$C:$C,))
或
=INDEX(Sheet2!$A:$A,MATCH("*"&LEFT(Sheet1!B2,2)&"*",Sheet2!$C:$C,))+IF(C2<=1,0,CEILING(C2-1,1)*INDEX(Sheet2!$B:$B,MATCH("*"&LEFT(Sheet1!B2,2)&"*",Sheet2!$C:$C,)))
下拉填充