Как автоматизировать Vlookup с помощью Excel VBA

Vlookup — важная функция Excel, ставшая важной частью обработки данных. Он предоставляет ряд функций, которые можно объединить с обширной базой данных.

Однако если вы хотите автоматизировать эту функцию, вы можете это сделать, особенно если вы знаете, как использовать язык автоматизации, доступный в Excel и VBA. Вот как можно автоматизировать функцию ВПР в Excel VBA .

Формула функции ВПР в Excel

=vlookup(lookup_value, lookup_array, column_index, exact_match/partial_match)

Значение аргументов в формуле:

• lookup_value: базовое значение, которое вы хотите найти в массиве.
• lookup_array: Исходные данные, искомое_значение будет использоваться в качестве ссылки.
• column_index: Столбец возвращает значение.
• exact_match/partial_match: используйте 0 или 1, чтобы указать тип комбинации.

Функция VBA Vlookup в Excel

Использование функции Vlookup не сильно отличается при использовании ее непосредственно в Excel или через VBA. Независимо от того, обрабатывает ли аналитик данных строки информации или менеджер склада постоянно работает с новыми продуктами, функция ВПР будет полезна.

При работе с динамическим диапазоном поиска лучше всего автоматизировать формулу, чтобы избежать необходимости многократного манипулирования одной и той же формулой в Excel. Автоматизируя функцию Vlookup в VBA, вы можете выполнять вычисления с несколькими столбцами одним щелчком мыши.

Иллюстрация

Этот набор данных состоит из двух частей: справочная таблица и целевые точки данных. Таблица поиска содержит три столбца с данными в двух полях: «Подкатегория» и «Название продукта»:

В целевой таблице есть соответствующие столбцы, нет подпапок или данных о названии продукта. Обратите внимание, что значения в столбце «Идентификатор заказа» также отображаются в том же столбце справочной таблицы:

1. Сделайте необходимые настройки

Начните с открытия редактора кода (нажмите Alt + F11 или перейдите на вкладку «Разработчик ») в новой книге Excel и добавьте модуль, чтобы начать кодирование. В редакторе кода добавьте следующие строки в верхнюю часть окна программирования, чтобы создать подпрограмму:

Sub vlookup_fn1()End Sub

Подпрограмма представляет собой контейнер для кода VBA и необходима для ее успешного запуска. Другой альтернативой является создание пользовательской формы VBA, чтобы сделать пользовательский интерфейс более интерактивным.

2. Объявите переменные и создайте ссылочные области

Сначала вам необходимо объявить типы данных переменных с помощью команды Dim:

Dim i as integer, lastrow as long

Затем создайте переменную lastrow для хранения значения последней заполненной строки в диапазоне поиска. Функция Lastrow использует функцию end(xldown) для вычисления ссылки на последнюю строку в определенном диапазоне.

В этом примере переменная Lastrow содержит значение последней заполненной строки в диапазоне поиска, а именно 17.

lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row

В приведенном выше примере целевая таблица простирается от A2:C17, а исходная таблица — от F2:H17. Формула ВПР будет перебирать каждое значение в столбце A, искать его в исходной таблице и вставлять объединенные записи в столбцы B и C.

Однако перед входом в цикл необходимо сохранить значения диапазона в новой переменной ( my_range ) следующим образом:

my_range = Sheets("Sheet2").Range("F3:H" & lastrow)

Вы должны явно указать ссылку на начальную ячейку (F3) и ссылку на конечный столбец (H). VBA автоматически добавляет значения строк для завершения массива поиска.

3. Напишите цикл для циклического перебора каждого искомого значения.

Прежде чем писать цикл, определите начальное значение строки. 2. Определение начального значения для цикла необходимо, когда вы имеете дело с динамическими компонентами. В целевой таблице строка 2 является первой строкой данных. Измените это значение, если данные начинаются в другой строке.

i = 2

Вы можете использовать цикл do- while для обработки каждой строки, начиная со строки 2 и заканчивая строкой 17. Как и остальной код, этот аспект является гибким; Цикл будет выполняться до тех пор, пока условие не станет ложным. Используйте условие для проверки непустого значения в первой ячейке текущей строки.

Do While len(Sheets("Sheet2").Cells(i, 1).value) <> 0

Внутри цикла вы можете вызвать функцию Vlookup для заполнения ячеек:

Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False)Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False)

Эти команды устанавливают значения ячеек в текущей строке, в столбцах 2 и 3 соответственно. Они используют объект WorksheetFunction для вызова функции Vlookup, передавая для поиска соответствующее значение из столбца 1. Они также передают диапазон, который вы изначально определили, и индекс соответствующего столбца для получения окончательного значения.

Код ВПР готов, но вам все равно придется каждый раз в цикле увеличивать переменную строки:

i = i + 1

При каждом запуске цикла значение i увеличивается на 1 . Помните, что начальное значение строки равно 2, приращение происходит каждый раз при запуске цикла. Используйте ключевое слово цикла , чтобы установить конец этого блока кода.

Когда весь код будет запущен, он заполнит оба столбца результатами на основе значений в исходной таблице.

Ниже приведен весь ссылочный код:

 Sub vlookup_fn1() Dim i As Integer lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row my_range = Sheets("Sheet2").Range("F3:H" & lastrow) i = 2 Do While Len(Sheets("Sheet2").Cells(i, 1).Value) <> 0 On Error Resume Next Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False) Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False) i = i + 1 LoopEnd Sub

4. Создайте кнопку для запуска кода.

Вы можете создать кнопку в таблице Excel, чтобы запускать ее одним щелчком мыши. Вставьте нужную фигуру на пустой лист Excel, щелкните ее правой кнопкой мыши и выберите параметр «Назначить макрос» .

В диалоговом окне выберите имя подпрограммы и нажмите «ОК» .

Когда вы захотите запустить код, нажмите кнопку, чтобы сразу увидеть, как заполняются данные.

Выше описано, как использовать Excel VBA для автоматизации функций поиска . Надеюсь, статья окажется для вас полезной.