Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.
Задача: ваши таблицы подстановки могут содержать несколько вхождений каждого ключевого поля. Вы хотели бы получить значения второго, третьего и т.д. вхождений.
Решение: ВПР не может решить эту проблему. Комбинация двух функций СМЕЩ и ПОИСКПОЗ могла бы это сделать, при условии, что таблица будет отсортирована по ключевому столбцу (подробнее см. Билл Джелен. Всё о ВПР: от первого применения до экспертного уровня). Но если вам по каким-то соображения не удобно делать сортировку, можете воспользоваться пользовательской функцией VLOOKUPNTH (рис. 1). Вам нужно добавить эту функцию в проект VBA (если вы никогда не делали этого, см. Сумма по цвету ячеек в Excel). Чтобы получить доступ к коду в приложенном Excel-файле можно, например, кликнуть правой кнопкой мыши на ярлык листа и выбрать Исходный текст. Функция работает, как и ВПР, но вместо использования ЛОЖЬ/ИСТИНА в качестве четвертого аргумента, вы указываете, какое по порядку вхождение вы хотите получить для искомого значения.
Рис. 1. ВПР в столбце F, и VLOOKUPNTH в столбцах, начиная с G
Скачать заметку в формате Word или pdf, примеры в формате Excel (с макросами)
Вот код этой функции
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 возвращает значение, которое находится слева от ключевого поля
Резюме: пользовательские функции позволяют расширить возможности ВПР для поиска N-го вхождения и возврата значения из столбца, расположенного слева от ключевого поля.
Источник. Пост был номинирован Matt Hohbein. Спасибо Zack Barresse и Peter Moran.
Здравствуйте, нашла у Вас много полезной информации для себя, особенно полезными для меня были статьи про цвета. Спасибо огромное за функции суммцвет и колцвет. Но мне нужно решить немного другую задачу, основываясь на цвете:
Нужно сравнить цвет ячейки со всеми цветами из диапазона и при совпадении вернуть значение ячейки слева. Нашла здесь функцию VLOOKNEW (я,как совсем неопытный человек, предположила, что это самый подходящий вариант), но не знаю, как ее переделать под анализ цвета.
Помогите, пожалуйста.
Спасибо!
Екатерина, написал для вас функцию ПоискПозЦвет. Она сравнивает цвет в ячейке с цветами ячеек из массива, и при нахождении первого соответствия, возвращает номер элемента в массиве. Затем стандартная функция Excel ИНДЕКС возвращает число из другого массива:
Код VBA
Подробности в файле.