Поиск в нескольких столбцах и возврат соответствующего значения в следующем столбце (ближайшее значение)

Вот что я пытаюсь сделать, у меня есть два листа:

Справочный лист: Нажмите, чтобы увидеть изображение

Code     Length     Width     Height

A         78         48        25     
B         78         48        34 
C         12         7.4        5
D         12         15         5
E         12         15       7.5
F         12         15         9
G         24         15         5
H         24         15         7

Лист решения:

Нажмите, чтобы увидеть пример решения

Length    Width   Height  Returning Code   Match_L   Match_W   Match_H

 10        6       8         C                12        7.4        5

Формула в столбце "Код возврата" должна искать ближайшее значение в соответствующем справочном листе, то есть длина <-> длина, ширина <-> ширина, высота <-> высота и возвращать соответствующий "код" из соответствующей строки.

Было бы проще, если бы я хотел сопоставить его, когда значения равны, но в моем случае он будет искать ближайшее значение (большее или меньшее) в каждом из соответствующих столбцов и возвращать соответствующий "Код" и значения в столбцах Match_L, Match_W, Match_H.

Любая помощь или указатели высоко ценятся!

Источник

2 ответа

Решение

Следующий VBA сделает эту работу.

Sub LookupNearestValue()

    Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
    Dim LastRow As Long: LastRow = ws.UsedRange.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim i As Long, RowCounter As Long: RowCounter = 2

    Dim tRowCounter As Long
    Dim tValue As Long
    Dim tempValue As Long

    Dim tLength As Long, tWidth As Long, tHeight As Long
    Dim tempLength As Long, tempWidth As Long, tempHeight As Long

    tLength = ws.Cells(2, 6)
    tWidth = ws.Cells(2, 7).Value
    tHeight = ws.Cells(2, 8).Value

    With ws
        For i = 2 To LastRow

            tempLength = ws.Cells(RowCounter, 2)
            tempWidth = ws.Cells(RowCounter, 3).Value
            tempHeight = ws.Cells(RowCounter, 4).Value

            tempValue = Abs(tLength - tempLength) + Abs(tWidth - tempWidth) + Abs(tHeight - tempHeight)

            If RowCounter = 2 Then
                tValue = tempValue
                tRowCounter = RowCounter
            ElseIf RowCounter > 2 And tempValue < tValue Then
                tValue = tempValue
                tRowCounter = RowCounter
            End If

            RowCounter = RowCounter + 1
        Next i

        ws.Cells(2, 9) = ws.Cells(tRowCounter, 1)
        ws.Cells(2, 10) = ws.Cells(tRowCounter, 2)
        ws.Cells(2, 11) = ws.Cells(tRowCounter, 3).Value
        ws.Cells(2, 12) = ws.Cells(tRowCounter, 4).Value


    End With

End Sub

Чтобы этот Макрос работал, вам нужно иметь данные на листе в соответствии с расположением этих столбцов:

На моем листе я настроил запуск этого макроса при изменении значения в H2 клетка.

Источник

Предполагая, что есть только одно место для ввода требуемой длины, ширины и высоты, и, таким образом, только одно возвращаемое значение в максимуме:

В вашем справочном листе добавьте еще три столбца от E до G: length_dif, width_dif а также height_dif,

Формула для этих столбцов будет в ячейке E2: =ABS(B2-SolutionSheet!A$2) затем разверните его до G2 и нарисуйте его до конца таблицы решений.

Добавьте еще один столбец в свой справочный лист в H: dif_abs с формулой: =Sum(E2:G2)

Затем, чтобы вернуть ваше значение, добавьте следующую формулу в таблицу решений в ячейку D2: =Index(ReferenceSheet!$A$2:$H$9;MATCH(Min(ReferenceSheet!$H$2:$H$9);ReferenceSheet!$H$2:$H$9);1)

Источник
Другие вопросы по тегам