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

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

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

Функция ЕСЛИ может работать, но требует много времени и неэффективна.

Я рассматривал возможность использования массива и думаю, что 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.


multidimensional-array vlookup match vba amortization
person Samia    schedule 24.06.2016    source источник
comment
Можете ли вы опубликовать фотографию таблицы процентных ставок? И нужно ли это делать с помощью VBA?   -  person TheEngineer    schedule 24.06.2016
comment
Спасибо за ответ. Да, я должен использовать VBA. У меня нет изображения таблицы процентных ставок, и я сделал таблицу в похожем формате, но не могу разместить ее здесь. Это простая таблица с четырьмя строками и 6 столбцами. Мне нужно, чтобы VBA мог использовать входные данные, чтобы узнать процентную ставку на их пересечении.   -  person Samia    schedule 24.06.2016
comment
Дополнительные сведения: Диапазон таблицы — A2:F6. Диапазон столбцов: A2:F2.   -  person Samia    schedule 24.06.2016
comment
Можете ли вы сделать таблицу именованным диапазоном?   -  person TheEngineer    schedule 24.06.2016

Ответы (1)


arrow_upward
0
arrow_downward

Просто создайте в таблице именованный диапазон под названием «Процентные ставки». Затем вы можете получить доступ к именованному диапазону в 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

Это должно работать для вас, если вы назвали всю свою таблицу (включая заголовки и имена строк) «Процентные ставки». Это также предполагает, что ваш диапазон процентных ставок находится на вашем листе «аморта по кредиту». Желаемая процентная ставка будет назначена переменной intRate, и вы можете добавить код в конце, чтобы использовать ее по своему усмотрению.

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

person TheEngineer    schedule 24.06.2016
comment
Спасибо. Теперь моя проблема заключается в том, чтобы сообщить VBA о доступе к диапазону. Может быть, я могу использовать для этого функцию соответствия? Вот как должен работать мой код. Пользователю предлагается ввести значение X, а затем запрашивается значение Y. Из этих двух входных данных VBA предлагается определить процентную ставку как число на пересечении X и Y, полученное из таблицы на рабочем листе. Я мог бы использовать Если; ЕСЛИ x=2 и Y=3, то intRate=5... но это займет время. В excel я использую Vlookup (значение поиска, массив таблиц, Match (значение столбца, массив столбцов, 0), FALSE). - person Samia; 25.06.2016
comment
Просто введите координаты в переменные таблицы и извлеките значения из пользовательских входов. Я обновил свой ответ, чтобы отразить это. - person TheEngineer; 25.06.2016
comment
Если этот ответ удовлетворяет вашим требованиям, пожалуйста, отметьте его как правильный ответ. - person TheEngineer; 25.06.2016
comment
У меня все еще есть проблемы с этим. Я думаю, это потому, что я не указал VBA, где находятся строки и столбцы. Я пытался использовать функции «Найти» и «Пересечь», но не совсем понимаю, как это сделать. Я попытался указать диапазоны строк и столбцов в дополнение к общему диапазону, а затем попросил VBA найти местоположение X и Y в этих диапазонах, прежде чем использовать функцию пересечения, чтобы найти значение в ячейке на пересечении X и Y, т.е. InterestRate=intersect(диапазон поиска, строка, столбец). - person Samia; 25.06.2016
comment
Как насчет того, чтобы вы обновили свой вопрос с кодом, который у вас есть, и я посмотрю на него. - person TheEngineer; 25.06.2016
comment
Я обновил свой ответ и изменил ваш код, чтобы он работал. Однако сначала вам нужно будет сделать вашу таблицу именованным диапазоном. - person TheEngineer; 27.06.2016
comment
Великолепно! Большое спасибо, что нашли время :) - person Samia; 27.06.2016
comment
Рад, что смог помочь! - person TheEngineer; 27.06.2016