ВПР для поиска N-го вхождения и возврата из левого столбца

Рубрика: 7. Полезняшки Excel

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: ваши таблицы подстановки могут содержать несколько вхождений каждого ключевого поля. Вы хотели бы получить значения второго, третьего и т.д. вхождений.

Решение: ВПР не может решить эту проблему. Комбинация двух функций СМЕЩ и ПОИСКПОЗ могла бы это сделать, при условии, что таблица будет отсортирована по ключевому столбцу (подробнее см. Билл Джелен. Всё о ВПР: от первого применения до экспертного уровня). Но если вам по каким-то соображения не удобно делать сортировку, можете воспользоваться пользовательской функцией VLOOKUPNTH (рис. 1). Вам нужно добавить эту функцию в проект VBA (если вы никогда не делали этого, см. Сумма по цвету ячеек в Excel). Чтобы получить доступ к коду в приложенном Excel-файле можно, например, кликнуть правой кнопкой мыши на ярлык листа и выбрать Исходный текст. Функция работает, как и ВПР, но вместо использования ЛОЖЬ/ИСТИНА в качестве четвертого аргумента, вы указываете, какое по порядку вхождение вы хотите получить для искомого значения.

Рис. 1. ВПР в столбце F, и VLOOKUPNTH в столбцах G-Х

Рис. 1. ВПР в столбце F, и VLOOKUPNTH в столбцах, начиная с G

Скачать заметку в формате Word или pdf, примеры в формате Excel (с макросами)

Вот код этой функции

Function VLOOKUPNTH(lookup_value, table_array As Range, _

col_index_num As Integer, nth_value)

‘ Ищет N-е вхождение искомого значения

Dim nRow As Long

Dim nVal As Integer

Dim bFound As Boolean

VLOOKUPNTH = "Not Found"

With table_array

For nRow = 1 To .Rows.Count

If .Cells(nRow, 1).Value = lookup_value Then

nVal = nVal + 1

If nVal = nth_value Then

VLOOKUPNTH = .Cells(nRow, col_index_num).Text

Exit Function

End If

End If

Next nRow

End With

End Function

Подробнее о функции. table_array – это таблица подстановки, которая передается с листа Excel внутрь функции VBA. Функция VBA работает с диапазоном table_array, начиная с 1-й строки и заканчивая последней (For nRow = 1 To .Rows.Count). Значение всегда ищется в первом столбце диапазона (If .Cells(nRow, 1).Value = lookup_value Then). Если вхождение является N-м (If nVal = nth_value Then) определяется значение для VLOOKUPNTH, которое берется из соответствующей строки и столбца таблицы подстановки (nRow, col_index_num). Если N-е вхождение не найдено возвращается ошибка (VLOOKUPNTH = "Not Found").

Дополнительные сведения: пользователи часто сокрушаются, что ВПР не может вернуть значение из столбца, расположенного слева от ключевого поля. Воспользуйтесь еще одной пользовательской функцией VLOOKNEW.

Function VLOOKNEW(lookup_value, table_array As Range, _

col_index_num As Integer, CloseMatch As Boolean)

Dim nRow As Long

Dim nVal As Integer

Dim bFound As Boolean

VLOOKNEW = "Not Found"

If col_index_num > 0 Then

VLOOKNEW = Application.WorksheetFunction.VLookup(lookup_value, _

table_array, col_index_num, CloseMatch)

Else

nRow = Application.WorksheetFunction.Match(lookup_value, _

table_array.Resize(, 1), CloseMatch)

VLOOKNEW = table_array(nRow, 1).Offset(0, col_index_num)

End If

End Function

VLOOKNEW аналогично ВПР, за исключением того, что вы можете использовать отрицательное значение для столбца Порядковый номер (рис. 2).

Рис. 2. VLOOKNEW возвращает значение, которое находится слева от ключевого поля

Рис. 2. VLOOKNEW возвращает значение, которое находится слева от ключевого поля

Резюме: пользовательские функции позволяют расширить возможности ВПР для поиска N-го вхождения и возврата значения из столбца, расположенного слева от ключевого поля.

Источник. Пост был номинирован Matt Hohbein. Спасибо Zack Barresse и Peter Moran.


Прокомментировать