Использование VLOOKUP с несколькими критериями / многомерный Vlookup

Question

Я пытаюсь создать таблицу амортизации, где процентная ставка зависит от двух входных данных, предоставленных пользователем.

X представляет строки, а Y представляет столбцы. Значения X, Y и процентные ставки уже установлены в таблице 4 X 6. Например, если пользователь вводит X=2 и Y=3, процентная ставка будет определяться как 5%.

Функция IF могла бы работать, но это заняло бы много времени и неэффективно.

Я подумал об использовании массива, и я думаю, что Vlookup будет наиболее эффективным. В Excel я использовал Vlookup вместе с Match, и он работал, но у меня возникли трудности с переводом его на код 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". Это также предполагает, что ваш диапазон Процентных ставок находится на вашем листе "ссудный расход". Желаемая процентная ставка будет назначена переменной intRate и вы можете добавить код в конце, чтобы использовать его, как вам нравится.

Кстати, я объявил некоторые из ваших переменных с типами переменных. Вы должны рассмотреть возможность добавления типов переменных к остальным. Я также удалил два End линии как они были не нужны.