麻烦根据快递公司给出的各个地区的运费重量要求算出运费多少？

feifei6012

根据快递公司公给出的到货地址的首重以及续重的价格，求出运费，谢谢！

一把小刀闯天下

Option Explicit

Sub test()
  Dim i, j, arr, dic, mark, t
  Set dic = CreateObject("scripting.dictionary")
  arr = [g1].CurrentRegion
  For i = 2 To UBound(arr, 1)
    ReDim mark(3)
    For j = 2 To UBound(arr, 2): mark(j - 2) = arr(i, j): Next
    dic(arr(i, 1)) = mark
  Next
  mark = Array(1, 1.5, 3)
  arr = [a1].CurrentRegion.Offset(1)
  For i = 1 To UBound(arr, 1) - 1
    If dic.exists(arr(i, 1)) Then
      t = dic(arr(i, 1))
      For j = 0 To UBound(mark)
        If arr(i, 2) <= mark(j) Then Exit For
      Next
      If j = UBound(mark) + 1 Then
        arr(i, 3) = t(UBound(t) - 1) + (arr(i, 2) - mark(UBound(mark))) * t(UBound(t))
      Else
        arr(i, 3) = t(j)
      End If
    Else
      arr(i, 3) = "?"
    End If
  Next
  [a2].Resize(UBound(arr, 1) - 1, UBound(arr, 2)) = arr
End Sub

hno3861

=VLOOKUP(A2,G:J,1+MATCH(B2-1%,{0,1,1.5}),)+(B2>3)*roundup(B2-3,)*VLOOKUP(A2,G:K,5,)

renyz2006

=IF(A2="北京",LOOKUP(B2,{0,1,1.5,3,10},{4,4.5,5,5.5}),IF(A2="上海",LOOKUP(B2,{0,1,1.5,3,10},{3.5,3.8,4.5,6}),IF(A2="河南",LOOKUP(B2,{0,1,1.5,3,10},{3,3.3,4,4.5}),IF(A2="江苏",LOOKUP(B2,{0,1,1.5,3,10},{3,3.3,4,5.5}),IF(A2="浙江",LOOKUP(B2,{0,1,1.5,3,10},{3,3.3,4,5.5}),"")))))