Public Function CalcMoney(carType As Range, timeType As Range, carItems As Range, timeItems As Range, timeValue As Range, others As Range)
Dim temp
Dim unitValue
Dim timeTemp
'先匹配出车型的列index
temp = Application.Match(carType, carItems, 0)
'匹配出车型所对应的机油、机油格数据
unitValue = Application.Index(others, 0, temp + 1)
timeTemp = Application.Match(timeType, timeItems, 0)
'计算总结果
CalcMoney = Application.Sum(unitValue) + Application.Index(timeValue, timeTemp, temp)
'CalcMoney = SUM(INDEX(C5:AF6,0,MATCH($A$22,C3:AF3,0)+1))+INDEX(C18:AF21,MATCH($B$22,$B$18:$B$21,0),MATCH($A$22,C3:AF3,0))
End Function
Sub calc()
Dim result
Dim carItems()
Dim timeItems()
Dim timeValue()
Dim others()
Dim carType
Dim timeType
timeType = Range("$B$22")
carType = Range("$A$22")
carItems = Range("C3:AF3")
timeItems = Range("$B$18:$B$21")
timeValue = Range("C18:AF21")
others = Range("C5:AF6")
result = CalcMoney(carType, timeType, carItems, timeItems, timeValue, others)
MsgBox result
End Sub
Excel Records
最新推荐文章于 2024-04-26 15:44:16 发布