在sheet2的D2中输入如下公式
=if(c2<=1,vlookup(left(a2,2)&"*",sheet1!a:b,2,),if(c2>3,vlookup(left(a2,2)&"*",sheet1!a:d,4,),vlookup(left(a2,2)&"*",sheet1!a:c,3,)))
或
=VLOOKUP(LEFT(A2,2)&"*",Sheet1!A:D,LOOKUP(A2,{0,1,3}+10^-5,{1,2,3}),)
或
=VLOOKUP(LEFT(A2,2)&"*",Sheet1!A:D,LOOKUP(A2,{0.001,1.001,3.001},{1,2,3}),)
下拉填充
excel中怎么计算快递的运费啊
Public flZD, jf1ZD, jf2ZD, jf3ZD
Public jgArr(1 To 6) As Double '价格数组
Const ksLh1 = 1
Const ksLh2 = 9
Const ksLh3 = 15
Sub jgJs()
'2018-07-29编制
'裴老师vba工作室 TEL：18896773754 QQ：3030490161
'flZD 'key-省份 item-类别（1,2,3）
Dim hH As Long, lH As Integer, zL As Double
Dim qY As String
Dim szJe As Double, xzJe As Double, czJe As Double '首重金额 续重金额 超重金额
'字典初始化
Set flZD = CreateObject("scripting.dictionary")
Set jf1ZD = CreateObject("scripting.dictionary")
Set jf2ZD = CreateObject("scripting.dictionary")
Set jf3ZD = CreateObject("scripting.dictionary")
With Sheets("新价格表")
For i = 1 To 3
Select Case i
Case 1
lH = ksLh1
Case 2
lH = ksLh2
Case 3
lH = ksLh3
End Select
hH = 4
Do While .Cells(hH, lH) <> ""
qY = .Cells(hH, lH).Text
flZD.Add qY, i
Select Case i
Case 1
jf1ZD.Add qY, hH
Case 2
jf2ZD.Add qY, hH
Case 3
jf3ZD.Add qY, hH
End Select
hH = hH + 1
Loop
Next i
End With
hH = 2
Do While Cells(hH, 4) <> ""
qY = Cells(hH, 6).Text
zL = Cells(hH, 4).Value
Call js_zcx(qY, zL, szJe, xzJe, czJe)
Cells(hH, 8) = szJe
Cells(hH, 9) = xzJe
Cells(hH, 10) = czJe
Cells(hH, 11) = szJe + xzJe + czJe
hH = hH + 1
Loop
End Sub
Sub js_zcx(qY, zL, ByRef szJe, ByRef xzJe, ByRef czJe)
Dim lB As Integer, hH As Long
lB = flZD(qY)
Select Case lB
Case 1
hH = jf1ZD(qY)
For i = 1 To 6
jgArr(i) = Sheets("新价格表").Cells(hH, ksLh1 + i).Value
Next i
Case 2
hH = jf2ZD(qY)
For i = 1 To 4
jgArr(i) = Sheets("新价格表").Cells(hH, ksLh2 + i).Value
Next i
Case 3
hH = jf3ZD(qY)
For i = 1 To 5
jgArr(i) = Sheets("新价格表").Cells(hH, ksLh3 + i).Value
Next i
End Select
Select Case lB
Case 1 '计算
Select Case zL
Case Is > 3
szJe = jgArr(5)
xzJe = 0.5 * Application.WorksheetFunction.RoundUp((zL - 1) / 0.5, 0) * 2 * jgArr(6)
czJe = 0
Case 1.01 To 3
szJe = jgArr(3)
xzJe = 0.5 * Application.WorksheetFunction.RoundUp((zL - 1) / 0.5, 0) * 2 * jgArr(4)
czJe = 0
Case 0.51 To 1
szJe = jgArr(3)
xzJe = 0
czJe = 0
Case 0.31 To 0.5
szJe = jgArr(2)
xzJe = 0
czJe = 0
Case Is <= 0.3
szJe = jgArr(1)
xzJe = 0
czJe = 0
End Select
Case 2 '计算
Select Case zL
Case Is > 1
szJe = jgArr(3)
xzJe = Application.WorksheetFunction.RoundUp((zL - 1), 0) * jgArr(4)
czJe = 0
Case 0.51 To 1
szJe = jgArr(3)
xzJe = 0
czJe = 0
Case 0.31 To 0.5
szJe = jgArr(2)
xzJe = 0
czJe = 0
Case Is <= 0.3
szJe = jgArr(1)
xzJe = 0
czJe = 0
End Select
Case 3 '计算
Select Case zL
Case Is > 1
szJe = jgArr(4)
xzJe = 0.5 * Application.WorksheetFunction.RoundUp((zL - 1) / 0.5, 0) * 2 * jgArr(5)
czJe = 0
Case 0.51 To 1
szJe = jgArr(3)
xzJe = 0
czJe = 0
Case 0.31 To 0.5
szJe = jgArr(2)
xzJe = 0
czJe = 0
Case Is <= 0.3
szJe = jgArr(1)
xzJe = 0
czJe = 0
End Select
End Select
End Sub
实现的方法和详细的操作步骤如下：
1、第一步，以excel格式输入要计算的基本数据，然后需要根据不同的地区和权重计算快递费用，如下图所示，然后进入下一步。
2、其次，完成上述步骤后，在E2单元格中输入公式“= ?IF（D2 <= 3,2.8,2.8 +（D2-1）* 0.3）”，如下图所示，然后进入下一步。? ?
3、接着，完成上述步骤后，按键盘上的enter键以生成计算结果：江苏地区5KG重量的运费为“ 4”元，如下图所示，然后进入下一步。
4、最后，完成上述步骤后，为了在不同区域计算不同的第一和第二权重，需要将公式更改为“= ?IF（D6 <= 3,5,5 +（D6-1）* 1”，如下图所示。这样，问题就解决了。? ?