Перейти к содержимому

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

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

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

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

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

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

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

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

Подробнее о функции. 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.

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

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

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

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

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

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

  1. Екатерина

    Здравствуйте, нашла у Вас много полезной информации для себя, особенно полезными для меня были статьи про цвета. Спасибо огромное за функции суммцвет и колцвет. Но мне нужно решить немного другую задачу, основываясь на цвете:
    Нужно сравнить цвет ячейки со всеми цветами из диапазона и при совпадении вернуть значение ячейки слева. Нашла здесь функцию VLOOKNEW (я,как совсем неопытный человек, предположила, что это самый подходящий вариант), но не знаю, как ее переделать под анализ цвета.
    Помогите, пожалуйста.
    Спасибо!

  2. Екатерина, написал для вас функцию ПоискПозЦвет. Она сравнивает цвет в ячейке с цветами ячеек из массива, и при нахождении первого соответствия, возвращает номер элемента в массиве. Затем стандартная функция Excel ИНДЕКС возвращает число из другого массива:
    Функция VBA ПОИСКПОЗ по цвету
    Код VBA

    Подробности в файле.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *