Извлечь значение ячейки таблицы Excel на основе двух параметров

Question

У меня есть 2 столбца в Excel, A и B. В AI есть проценты (ставки) и в целых числах B (годы).

 rating PD  year
    0.39%   3
    0.88%   2
    1.32%   17
    0.88%   1
    0.26%   15
    0.17%   2
    0.17%   2
    0.59%   2
    0.59%   2

Затем у меня есть таблица, в которой в столбце F I указаны годы, а в строке - текст.

Вот так (таблица намного больше, а годы идут до 30):

    Rating          
Year AAA     AA+      AA      AA-
1   0.003%  0.008%  0.018%  0.049%
2   0.016%  0.037%  0.074%  0.140%
3   0.041%  0.091%  0.172%  0.277%
4   0.085%  0.176%  0.318%  0.465%
5   0.150%  0.296%  0.514%  0.708%

И так далее (таблица намного больше этой).

Поэтому мне нужна функция или ярлык, который для заданной ставки в столбце A и для данного года в столбце B дает мне в столбце C соответствующий рейтинг (AAA,AA+,AA и т. Д.).

В таблице указаны максимальные ставки. Так что если у меня есть A1=0.50% а также B1=2, тогда я иду, чтобы посмотреть на таблицу, год 2 и соответствующий курс, который 0.74% (и, следовательно, AA), потому что AA + 0.37% и слишком низко.

Другими словами, AA + и год 2 - это ставки между 0,16% и 0,37%. А АА с года 2 - все ставки между 0,37% и 0,74%.

Вы знаете, как я мог выполнить эту задачу?

Большое спасибо.

Answer 1

Для удобства чтения я использовал два functions, наряду с основной процедурой, показанной здесь. Иначе это был бы огромный дамп кода.

Прежде чем начать, вы должны изменить / проверить эти поля данных.

• (Синяя) таблица данных должна быть названа "scores"(или изменили внутренний код на свое имя)
• То же самое касается (зеленой) таблицы оценок - быть названным "grades"и начать в F1
• И последнее, но не менее важное: код предполагает, что эти две таблицы находятся на листе под названием "Sheet1"

Так что все это нужно изменить в коде, если имена не совпадают!

Теперь к процедуре:

Option Explicit
Private Sub run_through_scores()

    Dim scores As ListObject ' table from A1
    Dim grades As ListObject ' table from F1
    Set scores = Sheets("Sheet1").ListObjects("scores")
    Set grades = Sheets("Sheet1").ListObjects("grades")

    Dim cell As Range ' for "for" loop
    Dim inrow As Long ' will store in which row the year is
    Dim resultColumn As Integer ' will store in which column the percentage is

    'for every cell in second column of scores table (except header)
    For Each cell In scores.ListColumns(2).DataBodyRange
        inrow = get_year(cell).Row - 1
        ' ^ returns Row where result was found, -1 to accoutn for header

        'using our get_interval() function, _
         determines in which column is the sought percentage
        resultColumn = get_interval(cell.Offset(0, -1), inrow).Column
        cell.Offset(0, 1) = Sheets("Sheet1").Cells(1, resultColumn) 
        'write result in Column C   ^
    Next cell

End Sub

И к функциям:

get_year()

возвращает Range Объект из "grades"Таблица, в которой мы нашли соответствующий год от нашего"scores"Таблица. Если нужный год не найден, возвращается ближайший к нему год (последняя строка таблицы)

' Returns a Range (coordinates) for where to search in second table
Private Function get_year(ByVal year As Variant) As Range

    Dim grades As ListObject ' table from F1
    Set grades = Sheets("Sheet1").ListObjects("grades")

    Dim testcell As Range
    Set testcell = grades.ListColumns(1).DataBodyRange.Find(year, LookIn:=xlValues)

    'if found
    If Not testcell Is Nothing Then
        Set get_year = testcell
    Else
        Dim tbl_last_row As Long 'if year not found, return last row
        tbl_last_row = grades.ListColumns(1).DataBodyRange.Rows.Count
        Set get_year = grades.ListColumns(1).Range(tbl_last_row)
    End If

End Function

И вторая функция:

get_interval()

возвращает Range Объект из "grades"таблица. Она сравнивает отдельные диапазоны ячеек и возвращает а) если искомый процент от"scores"меньше или равно (<=) затем текущий процент ячейки или б) если мы прошли все ячейки, она возвращает последнюю ячейку (потому что она должна быть выше, чем максимум указанного интервала)

Private Function get_interval(ByVal what As Variant, ByVal inyear As Long) As Range

    Dim grades As ListObject ' table from F1
    Set grades = Sheets("Sheet1").ListObjects("grades")

    Dim cell As Range
    For Each cell In grades.ListRows(inyear).Range

    'check for interval 
        If what <= cell And cell.Column <> 6 Then 'we don't want to check year column
            Set get_interval = cell
            Exit Function
        End If
    Next cell

    ' if we arrived here, at this stage the result will always be the last cell
    Set get_interval = grades.ListRows(inyear).Range(, grades.ListColumns.Count)

End Function

---

После стрельбы run_through_scores() Процедура, мы получаем результаты, как и ожидалось:

---

если у вас есть какие-либо вопросы, пожалуйста, дайте мне знать:)