어떻게 VBA Excel에서 VLOOKUP 등을 사용하여 워크 시트 함수에?
VLOOKUP, COUNTIF, SUMIF와 같은 함수를 워크 시트 함수라고합니다. 일반적으로 Excel에서 미리 정의되고 워크 시트에서 사용할 준비가 된 함수는 워크 시트 함수입니다. VBA에서 이러한 함수 뒤에있는 코드를 변경하거나 볼 수 없습니다.
반면에 MsgBox 또는 InputBox와 같은 VBA에 특정한 사용자 정의 함수 및 함수는 VBA 함수입니다. 우리는 모두 VBA에서 VBA 함수를 사용하는 방법을 알고 있습니다. 그러나 VBA에서 VLOOKUP을 사용하려면 어떻게해야합니까? 어떻게하나요? 이 기사에서 우리는 정확히 그것을 탐구 할 것입니다.
VBA에서 워크 시트 함수 사용
워크 시트 함수에 액세스하려면 Application 클래스를 사용합니다. 거의 모든 워크 시트 함수가 Application.WorksheetFunction 클래스에 나열됩니다. 그리고 도트 연산자를 사용하면 모두 액세스 할 수 있습니다.
임의의 하위에 Application.WorksheetFunction을 작성합니다. 그리고 함수의 이름을 쓰기 시작하십시오. VBA의 intellisense는 사용할 수있는 기능의 이름을 표시합니다. 함수 이름을 선택하면 Excel의 다른 함수와 마찬가지로 변수를 묻습니다. 그러나 VBA가 이해할 수있는 형식으로 변수를 전달해야합니다. 예를 들어 A1 : A10 범위를 전달하려면 Range ( “A1 : A10”)와 같은 범위 객체로 전달해야합니다.
따라서 더 잘 이해하기 위해 몇 가지 워크 시트 함수를 사용하겠습니다.
VBA에서 VLOOKUP 함수를 사용하는 방법
VBA에서 VLOOKUP 함수를 사용하는 방법을 보여주기 위해 여기에 샘플 데이터가 있습니다. VBA를 사용하여 메시지 상자에 지정된 로그인 ID의 이름과 도시를 표시해야합니다. 데이터는 A1 : K26 범위에 분산됩니다.
Alt + F11을 눌러 VBE를 열고 모듈을 삽입합니다. 아래 코드를 참조하십시오.
Sub WsFuncitons()
Dim loginID As String
Dim name, city As String
loginID = "AHKJ_1-3357042451"
'Using VLOOKUP function to get name of given id in table
name = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 2, 0)
'Using VLOOKUP function to get city of given id in table
city = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 4, 0)
MsgBox ("Name: " & name & vbLf & "City: " & city)
End Sub
이 코드를 실행하면이 결과를 얻을 수 있습니다.
VBA가 결과를 메시지 상자에 얼마나 빨리 인쇄하는지 확인할 수 있습니다. 이제 코드를 살펴 보겠습니다.
어떻게 작동합니까?
1.
Dim loginID As String
Dim 이름, 도시 As String
먼저 VLOOKUP 함수에서 반환 한 결과를 저장하기 위해 문자열 유형의 두 변수를 선언했습니다. VLOOKUP에서 반환 된 결과가 문자열 값이 될 것이기 때문에 문자열 유형 변수를 사용했습니다. 워크 시트 함수가 숫자, 날짜, 범위 등의 값 유형을 반환 할 것으로 예상되는 경우 해당 유형의 변수를 사용하여 결과를 저장합니다. 워크 시트 함수가 어떤 종류의 값을 반환할지 확실하지 않은 경우 변형 유형 변수를 사용합니다.
2.
loginID = “AHKJ_1-3357042451”
다음으로 loginID 변수를 사용하여 조회 값을 저장했습니다. 여기서는 하드 코딩 된 값을 사용했습니다. 참조도 사용할 수 있습니다. 예를 들면.
Range ( “A2”). Value를 사용하여 범위 A2에서 조회 값을 동적으로 업데이트 할 수 있습니다.
3.
name = Application.WorksheetFunction.VLookup (loginID, Range ( “A1 : K26”), 2, 0) 여기서는 VLOOKUP 함수를 사용하여 가져옵니다. 이제 함수를 수정하고 괄호를 열면 필요한 인수가 표시되지만 Excel에 표시되는 것만 큼 설명 적이지는 않습니다. 직접 확인하십시오.
사용해야하는 변수와 방법을 기억해야합니다. 언제든지 워크 시트로 돌아가 설명 변수 세부 정보를 볼 수 있습니다.
여기에서 조회 값은 Arg1입니다. Arg1의 경우 loginID를 사용합니다. 조회 테이블은 Arg2입니다. Arg2의 경우 Range ( “A1 : K26”)를 사용했습니다. Excel에서와 같이 A2 : K26을 직접 사용하지 않았습니다. 열 인덱스는 Arg3입니다.
Arg3의 경우 이름이 두 번째 열에 있으므로 2를 사용했습니다. 조회 유형은 Arg4입니다. Arg4로 0을 사용했습니다.
city = Application.WorksheetFunction.VLookup (loginID, Range ( “A1 : K26”), 4, 0)
마찬가지로 우리는 도시 이름을 얻습니다.
4.
MsgBox ( “이름 :”& 이름 & vbLf & “도시 :”& 도시)
마지막으로 Messagebox를 사용하여 이름과 도시를 인쇄합니다.
VBA에서 워크 시트 함수를 사용하는 이유는 무엇입니까? 워크 시트 함수는 엄청난 계산 능력을 가지고 있으며 워크 시트 함수의 힘을 무시하는 것은 현명하지 않습니다. 예를 들어 데이터 세트의 표준 편차를 원하고이를위한 전체 코드를 작성하려면 몇 시간이 걸릴 수 있습니다. 그러나 VBA에서 워크 시트 함수 STDEV.P를 사용하여 한 번에 계산을 얻는 방법을 알고 있다면.
Sub GetStdDev()
std = Application.WorksheetFunction.StDev_P(Range("A1:K26"))
End Sub
여러 워크 시트 함수 사용 VBA
일부 값을 검색하기 위해 인덱스 일치를 사용해야한다고 가정 해 보겠습니다. 이제 VBA에서 공식을 어떻게 작성 하시겠습니까? 다음과 같이 쓸 것 같습니다 :
Sub IndMtch() Val = Application.WorksheetFunction.Index(result_range, _ Application.WorksheetFunction.Match(lookup_value, _ lookup_range, match_type)) End Sub
이것은 잘못은 아니지만 길다. 여러 함수를 사용하는 올바른 방법은 With 블록을 사용하는 것입니다. 아래 예를 참조하십시오.
Sub IndMtch() With Application.WorksheetFunction Val = .Index(result_range, .Match(lookup_value, lookup_range, match_type)) val2 = .VLookup(arg1, arg2, arg3) val4 = .StDev_P(numbers) End With End Sub
보시다시피 With 블록을 사용하여 VBA에 Application.WorksheetFunction의 속성과 기능을 사용할 것임을 알 렸습니다. 그래서 모든 곳에서 정의 할 필요가 없습니다. 방금 도트 연산자를 사용하여 INDEX, MATCH, VLOOKUP 및 STDEV.P 함수에 액세스했습니다. End With 문을 사용하면 정규화 된 함수 이름을 사용하지 않고 함수에 액세스 할 수 없습니다.
따라서 VBA에서 여러 워크 시트 함수를 사용해야하는 경우 블록과 함께 사용하십시오.
Application.WorksheetFunction을 통해 모든 워크 시트 함수를 사용할 수있는 것은 아닙니다. 일부 워크 시트 함수는 VBA에서 직접 사용할 수 있습니다. Application.WorksheetFunction 개체를 사용할 필요가 없습니다.
예를 들어, 문자열, left, right, mid, trim, offset 등의 문자 수를 가져 오는 데 사용되는 Len ()과 같은 함수가 있습니다. 이러한 함수는 VBA에서 직접 사용할 수 있습니다. 여기에 예가 있습니다.
Sub GetLen() Strng = "Hello" Debug.Print (Len(strng)) End Sub
여기에서는 Application.WorksheetFunction 개체를 사용하지 않고 LEN 함수를 사용했습니다.
마찬가지로 left, right, mid, char 등과 같은 다른 기능을 사용할 수 있습니다.
Sub GetLen() Strng = "Hello" Debug.Print (Len(strng)) Debug.Print (left(strng,2)) Debug.Print (right(strng,1)) Debug.Print (Mid(strng, 3, 2)) End Sub
위의 하위를 실행하면 다음이 반환됩니다.
5 He o ll
네, 이것이 VBA에서 Excel의 워크 시트 기능을 사용하는 방법입니다. 내가 충분히 설명하고이 기사가 도움이 되었기를 바랍니다. 이 기사 또는 VBA와 관련된 다른 질문이 있으면 아래의 의견 섹션에서 질문하십시오. 그때까지 아래에서 다른 관련 주제를 읽을 수 있습니다.
관련 기사 :
link : / excel-macros-and-vba-what-is-csng-function-in-excel-vba [Excel VBA의 CSng 함수 란?]| SCng 함수는 모든 데이터 유형을 단 정밀도 부동 소수점 숫자 ( “숫자 인 경우”)로 변환하는 VBA 함수입니다. 나는 주로 CSng 함수를 사용하여 텍스트 형식의 숫자를 실제 숫자로 변환합니다.
link : / vba-how-to-get-text-number-in-reverse-through-vba-in-microsoft-excel [Microsoft Excel에서 VBA를 통해 텍스트와 숫자를 반대로 가져 오는 방법]| * 숫자를 반대로하려면 그리고 우리는 VBA에서 루프와 중간 기능을 사용합니다. 1234는 4321로, “you”는 “uoy”로 변환됩니다. 다음은 스 니펫입니다.
link : / formating-in-vba-format-table-depending-on-number-formats-using-vba-in-microsoft-excel [Microsoft Excel에서 VBA를 사용하여 사용자 지정 숫자 형식으로 데이터 서식 지정]| Excel에서 특정 열의 숫자 형식을 변경하려면이 VBA 스 니펫을 사용하십시오. 한 번의 클릭으로 지정된 숫자 형식을 지정된 형식으로 변환합니다.
link : / tips-using-worksheet-change-event-to-run-macro-when-any-change-is-made [워크 시트 변경 이벤트를 사용하여 변경시 매크로 실행]| 따라서 시트가 업데이트 될 때마다 매크로를 실행하기 위해 VBA의 워크 시트 이벤트를 사용합니다.
link : / events-in-vba-run-macro-if-any-change-made-on-sheet-range [지정된 범위의 시트에서 변경된 경우 매크로 실행]| 지정된 범위의 값이 변경 될 때 매크로 코드를 실행하려면이 VBA 코드를 사용하십시오. 지정된 범위에서 변경된 사항을 감지하고 이벤트를 시작합니다.
link : / events-in-vba-simplest-vba-code-to-highlight-current-row-and-column-using [현재 행 및 열을 사용하여 강조하는 가장 간단한 VBA 코드]| 이 작은 VBA 스 니펫을 사용하여 시트의 현재 행과 열을 강조 표시합니다.
인기 기사 :
link : / keyboard-formula-shortcuts-50-excel-shortcuts-to-increase-your-productivity [50 개의 Excel 단축키로 생산성 향상]| 작업 속도를 높이십시오. 이 50 개의 바로 가기를 사용하면 Excel에서 더 빠르게 작업 할 수 있습니다.
link : / formulas-and-functions-introduction-of-vlookup-function [Excel의 VLOOKUP 함수]| 이것은 다른 범위와 시트에서 값을 조회하는 데 사용되는 Excel의 가장 많이 사용되고 인기있는 기능 중 하나입니다.
link : / tips-countif-in-microsoft-excel [Excel 2016의 COUNTIF]| 이 놀라운 기능을 사용하여 조건으로 값을 계산합니다. 특정 값을 계산하기 위해 데이터를 필터링 할 필요가 없습니다.
Countif 기능은 대시 보드를 준비하는 데 필수적입니다.
link : / excel-formula-and-function-excel-sumif-function [Excel에서 SUMIF 함수 사용 방법]| 이것은 또 다른 대시 보드 필수 기능입니다. 이를 통해 특정 조건에 대한 값을 합산 할 수 있습니다.