在整理價格表時,經常會碰到分量計價的價格查找,寫了如下的Excel自定義函數,有需要的可以使用。
=LookBreakPrice(O3,P3,K:M)
=LookBreakPrice(要查找的物料, 要查找的數量, 分段價格區域)
物料 分段數量 單價必須挨著放,且數量要連續,並且是降序
Function LookBreakPrice(Part As String, Qty As Double, Price)
For i = 1 To Price.Rows.Count
If Price.Cells(i, 1) = Part And Price.Cells(i, 1) = Price.Cells(i + 1, 1) Then
If Price.Cells(i, 1) = Part And Price.Cells(i, 2) <= Qty And Price.Cells(i + 1, 2) > Qty Then
LookBreakPrice = Price.Cells(i, 3)
Exit For
End If
ElseIf Price.Cells(i, 1) = Part And Price.Cells(i, 1) <> Price.Cells(i + 1, 1) Then
LookBreakPrice = Price.Cells(i, 3)
Exit For
End If
Next i
End Function