使用多准则/多维VLOOKUP的Vlookup

Question

我试图创建一个摊销表，其中利率取决于用户提供的两个输入。

X表示行，Y表示列。X，Y和利率的值已经设置在一个4X6表中.例如，如果用户输入X=2和Y=3，那么利率将被确定为5%。

如果功能可以工作，但它将占用大量时间，是没有效率的。

我考虑过使用数组，我认为Vlookup将是最有效的。在Excel中，我与Match一起使用了Vlookup，它起了作用，但我很难将它转换成VBA代码。

Option Explicit

Sub Amortisation()

    Dim intRate, loanLife, initLoanAmt
    Dim yrBegBal, intComp, prinComp, yrEndBal, annualPmt
    Dim outRow, rowNum, outsheet

    outRow = 3 'output table begins from row 4

    outsheet = "loan amort"
    Worksheets(outsheet).ActivateDo

    loanLife = InputBox("Enter loan life." _
      & " Loan life must be a whole number")

    If loanLife < 0 Or (loanLife - Round(loanLife) <> 0) Then
        MsgBox ("Loan life must be a whole number.")
        End
    End If

    initLoanAmt = InputBox("Enter loan amount." _
      & " Loan amount must be a positive whole number")

    If initLoanAmt < 0 Or (initLoanAmt - Round(initLoanAmt) <> 0) Then
        MsgBox ("Loan amount must be a positive whole number.")
        End
    End If

End Sub

我希望VBA使用给出的输入来从下表中选择利率，而不是像我对其他投入那样提示利率。

​

​

因此，如果X(贷款寿命)为5，Y (initloanamount)为700，那么我希望VBA使用10作为利率。

在此之后，我可以继续使用PMT函数的摊销表。

Answer 1

只需将您的表命名为"InterestRates“。然后，您可以访问VBA中的命名范围，如下所示：

Option Explicit

Sub Amortisation()

Dim intRate As Double, loanLife As Long, initLoanAmt As Long
Dim yrBegBal, intComp, prinComp, yrEndBal, annualPmt
Dim outRow, rowNum
Dim outsheet As Worksheet
Dim rng As Range

outRow = 3 'output table begins from row 4

Set outsheet = Worksheets("loan amort")
outsheet.ActivateDo

loanLife = InputBox("Enter loan life." _
  & " Loan life must be a whole number")

If loanLife < 0 Or (loanLife - Round(loanLife) <> 0) Then
    MsgBox ("Loan life must be a whole number.")
End If

initLoanAmt = InputBox("Enter loan amount." _
  & " Loan amount must be a positive whole number")

If initLoanAmt < 0 Or (initLoanAmt - Round(initLoanAmt) _
  <> 0) Then
    MsgBox ("Loan amount must be a positive whole number.")
End If

Set rng = outsheet.Range("InterestRates")

loanLife = Evaluate("MATCH(" & loanLife & ",INDEX(InterestRates,,1),0)")
initLoanAmt = Evaluate("MATCH(" & initLoanAmt & ",INDEX(InterestRates,1,),0)")

intRate = rng.Cells(loanLife, initLoanAmt).Value

End Sub

假设您已经将整个表命名为"InterestRates“(包括标题和行名)，这将适用于您。这也假设您的InterestRates范围在您的“贷款总额”表上。所需的利率将分配给变量intRate，您可以在最后添加代码来使用它。

顺便说一下，我用变量类型声明了一些变量。您应该考虑将变量类型添加到其他变量中。我还删除了两个End行，因为它们不是必要的。