Excel VBA를 사용하여 Vlookup을 자동화하는 방법

Vlookup 은 Excel의 필수 기능이자 데이터 처리의 중요한 부분이 되었습니다. 이는 포괄적인 데이터베이스와 결합할 수 있는 다양한 기능을 제공합니다.

그러나 이 기능을 자동화하려는 경우, 특히 Excel 및 VBA에서 사용할 수 있는 자동화 언어를 사용하는 방법을 알고 있는 경우 가능합니다. Excel VBA에서 vlookup 기능을 자동화하는 방법은 다음과 같습니다 .

Excel의 Vlookup 함수 수식

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

공식에서 인수의 의미:

• lookup_value: 배열에서 조회하려는 기본 값입니다.
• lookup_array: 소스 데이터, lookup_value가 참조로 사용됩니다.
• column_index: 열이 값을 반환합니다.
• exact_match/partial_match: 0 또는 1을 사용하여 조합 유형을 지정합니다.

Excel의 VBA Vlookup 기능

Vlookup 기능의 사용법은 Excel에서 직접 사용할 때나 VBA를 통해 사용할 때나 크게 다르지 않습니다. 데이터 분석가가 정보 행을 처리하든, 창고 관리자가 지속적으로 신제품을 다루든 관계없이 vlookup 기능은 유용합니다.

동적 조회 범위로 작업할 때 Excel에서 동일한 수식을 여러 번 조작할 필요가 없도록 수식을 자동화하는 것이 가장 좋습니다. VBA에서 Vlookup 기능을 자동화하면 한 번의 클릭으로 여러 열 계산을 수행할 수 있습니다.

삽화

이 데이터 세트는 조회 테이블과 대상 데이터 포인트라는 두 부분으로 구성됩니다. 조회 테이블에는 Sub-Category 및 Product Name 두 필드의 데이터가 포함된 3개의 열이 포함되어 있습니다.

대상 테이블에는 적절한 열이 있고 하위 폴더나 제품 이름 데이터는 없습니다. 주문 ID 열의 값은 조회 테이블의 동일한 열에도 나타납니다.

1. 필요한 설정을 합니다

새 Excel 통합 문서에서 코드 편집기를 열고( Alt + F11을 누르거나 개발자 탭 으로 이동 ) 모듈을 추가하여 코딩을 시작합니다. 코드 편집기에서 프로그래밍 창 상단에 다음 줄을 추가하여 서브루틴을 만듭니다.

Sub vlookup_fn1()End Sub

서브루틴 은 VBA 코드의 컨테이너이며 이를 성공적으로 실행하는 데 필요합니다. 또 다른 대안은 VBA 사용자 양식을 만들어 UI를 보다 대화형으로 만드는 것입니다.

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에서 확장됩니다. vlookup 수식은 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열의 해당 값을 검색에 전달합니다. 또한 처음에 정의한 범위와 관련 열의 인덱스를 전달하여 최종 값을 가져옵니다.

vlookup 코드가 준비되었지만 루프에서 매번 행 변수를 증가시켜야 합니다.

i = i + 1

루프가 실행될 때마다 i 값이 1 씩 증가합니다 . 시작 행 값은 2이며 루프가 실행될 때마다 증가가 발생한다는 것을 기억하십시오. loop 키워드를 사용하여 이 코드 블록의 끝을 설정합니다.

전체 코드가 실행되면 소스 테이블의 값을 기반으로 두 열에 결과가 채워집니다.

다음은 전체 참조 코드입니다.

 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를 사용하여 조회 기능을 자동화하는 방법 입니다 . 이 기사가 당신에게 도움이 되기를 바랍니다.