Home-theater-designers
Vlookup은 Excel의 필수 기능이며 데이터 처리의 중요한 부분이 되었습니다. 일반적으로 완전한 데이터베이스와 연관시킬 수 있는 일부 기능을 제공합니다.
하지만 이 기능을 자동화하고 싶다면 어떻게 해야 할까요? 예, 가능합니다. 특히 Excel의 기본 자동화 언어인 VBA를 사용하는 방법을 알고 있다면 가능합니다. Excel VBA에서 vlookup 기능을 자동화하는 방법은 다음과 같습니다.
오늘의 메이크업 비디오
Vlookup의 구문
vlookup 함수의 구문은 매우 간단하지만 Excel VBA에서 자동화하는 동안에도 집중해야 하는 네 가지 측면이 있습니다.
=vlookup(lookup_value, lookup_array, column_index, exact_match/partial_match)함수의 인수는 다음과 같은 의미를 갖습니다.
- lookup_value: 조회 배열에서 조회하려는 기본 값입니다.
- lookup_array: 이것은 lookup_value가 참조로 사용할 소스 데이터입니다.
- 열_인덱스: 값을 반환할 열입니다.
- 정확한 일치/부분 일치: 0 또는 1을 사용하여 일치 유형을 결정합니다.
데이터 준비
이 데이터 세트는 조회 테이블과 대상 데이터 요소의 두 부분으로 구성됩니다.
조회 테이블은 하위 범주 및 제품 이름 필드를 채우는 데이터가 포함된 세 개의 열로 구성됩니다.
대상 테이블에 하위 범주 또는 제품 이름 데이터가 없는 일치하는 열이 있습니다. 주문 ID 열의 값은 조회 테이블의 동일한 열에도 표시됩니다.
Excel 스프레드시트에서 직접 Vlookup 사용
대상 열 B와 C를 채우려면 다음을 수행할 수 있습니다. Excel의 vlookup 기능 사용 .
세포 내 B2 , 다음 수식을 입력하십시오.
=VLOOKUP($A2, $F:$H, 2, 0)마찬가지로 셀에서 C2 , 다음 수식을 입력하십시오.
=VLOOKUP($A2, $F:$H, 3, 0)수식을 셀 B2에서 열의 끝인 B17까지 아래로 끌 수 있습니다. C 열에 대해서도 이 과정을 반복합니다. 각 후속 항목의 값은 두 열에서 자동으로 업데이트됩니다.
vlookup 함수에 전달하는 값은 다음과 같습니다.
- A2 : 조회 값이 이 셀에 있습니다.
- F3:H17 : 조회 범위로 구성됩니다.
- 23 : 값을 가져올 참조 열입니다.
- 0 : 정확히 일치함을 나타냅니다.
엑셀 VBA의 Vlookup 기능
vlookup 함수는 Excel에서 직접 사용하든 VBA를 통해 사용하든 많은 유사점이 있습니다. 데이터 행을 처리하는 데이터 분석가이든 정기적으로 신제품을 처리하는 재고 관리자이든 vlookup을 사용하면 이점을 얻을 수 있습니다.
동적 조회 범위로 작업할 때 Excel 내에서 수식을 반복적으로 적용하지 않도록 항상 수식을 자동화하는 것이 가장 좋습니다. VBA에서 Vlookup 기능을 자동화하면 한 번의 클릭으로 다중 열 계산을 수행할 수 있습니다.
1. 필요한 설정을 수행합니다.
코딩 편집기를 열어 시작합니다(누르기 Alt + F11 또는 개발자 새 Excel 통합 문서에서 모듈을 추가하여 코드 작성을 시작합니다. 코드 편집기 내에서 코딩 창 상단에 다음 줄을 추가하여 서브루틴을 만듭니다.
Sub vlookup_fn1()End Sub하위 루틴은 VBA 코드의 컨테이너이며 성공적으로 실행하는 데 중요합니다. 대안은 VBA 사용자 양식 만들기 UI를 보다 대화식으로 만들 수 있습니다.
2. 변수 선언 및 참조 범위 만들기
먼저 다음을 사용하여 변수 데이터 유형을 선언해야 합니다. 어둑한 성명:
Dim i as integer, lastrow as long다음으로 마지막 줄 변수, 조회 범위 내에서 마지막으로 채워진 행의 값을 저장합니다. lastrow 함수는 다음을 사용합니다. 종료(xldown) 지정된 범위 내에서 끝 행 참조를 계산하는 함수.
이 경우 lastrow 변수는 조회 범위의 마지막으로 채워진 행 값을 저장합니다. 17.
lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row위의 예에서 대상 테이블은 A2:C17 , 소스 테이블은 F2:H17 . vlookup 수식은 A 열에 저장된 각 값을 반복하고 소스 테이블 내에서 조회한 다음 B 열과 C 열에 일치하는 항목을 붙여넣습니다.
그러나 루프로 이동하기 전에 범위 값을 새 변수( 내 범위 ), 다음과 같이:
my_range = Sheets("Sheet2").Range("F3:H" & lastrow)시작 셀 참조( F3 ) 및 끝 열 참조( 시간 ). VBA는 행 값을 자동으로 추가하여 조회 배열을 완성합니다.
3. 각 조회 값을 순환하는 루프 작성
루프를 작성하기 전에 시작 행 값을 정의하십시오. 2 . 동적 요소를 처리할 때 루프의 시작 값을 정의하는 것이 필수적입니다. 대상 테이블에서 행 2는 데이터의 첫 번째 행입니다. 데이터가 다른 행에서 시작하는 경우 이 값을 변경하십시오.
i = 2당신은 사용할 수 있습니다 하는 동안 2행에서 시작하여 17행에서 끝나는 각 행을 처리하는 루프. 나머지 코드와 마찬가지로 이 측면은 동적입니다. 루프는 조건이 거짓이 될 때까지 실행됩니다. 조건을 사용하여 현재 행의 첫 번째 셀에서 비어 있지 않은 값을 확인합니다.
psd 파일을 여는 방법
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에서 현재 행의 셀 값을 설정합니다. 그들은 워크시트함수 호출하는 객체 VLookup 검색할 열 1에서 해당 값을 전달합니다. 또한 이전에 정의한 범위와 해당 열의 인덱스를 전달하여 최종 값을 가져옵니다.
vlookup 코드가 준비되었지만 여전히 루프를 돌 때마다 행 변수를 늘려야 합니다.
i = i + 1루프가 실행될 때마다 값이 증가합니다. 나 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 Sub4. 코드 실행을 위한 버튼 생성
코드를 치우면 Excel 통합 문서에 단추를 만들어 클릭 한 번으로 실행할 수 있습니다. 빈 Excel 시트에 원하는 도형을 삽입하고 마우스 오른쪽 버튼으로 클릭한 다음 매크로 할당 옵션.
대화 상자에서 서브루틴 이름을 선택하고 다음을 클릭합니다. 좋아요 .
코드를 실행하려면 버튼을 클릭하여 데이터가 어떻게 즉시 채워지는지 확인하십시오.
Excel VBA를 사용하여 조회 기능 자동화
Excel VBA는 유연한 언어로, MS Excel의 다양한 측면 내에서 자동화를 보다 쉽게 할 수 있도록 잘 조정되어 있습니다. Excel 기능 자동화에서 정교한 피벗 테이블 자동 생성에 이르기까지 MS Excel VBA에는 많은 옵션이 있습니다.
다른 Excel 세그먼트로 작업할 때 다양한 옵션을 실험하여 삶을 더 쉽고 효율적으로 만들 수 있습니다.