image

VLOOKUP, COUNTIF, SUMIF와 같은 함수를 워크 시트 함수라고합니다. 일반적으로 Excel에서 미리 정의되고 워크 시트에서 사용할 준비가 된 함수는 워크 시트 함수입니다. VBA에서 이러한 함수 뒤에있는 코드를 변경하거나 볼 수 없습니다.

반면에 MsgBox 또는 InputBox와 같은 VBA에 특정한 사용자 정의 함수 및 함수는 VBA 함수입니다.

우리는 모두 VBA에서 VBA 함수를 사용하는 방법을 알고 있습니다. 그러나 VBA에서 VLOOKUP을 사용하려면 어떻게해야합니까? 어떻게하나요? 이 기사에서 우리는 정확히 그것을 탐구 할 것입니다.

VBA에서 워크 시트 함수 사용

image

워크 시트 함수에 액세스하려면 Application 클래스를 사용합니다. 거의 모든 워크 시트 함수가 Application.Worksheet Function 클래스에 나열됩니다. 그리고 도트 연산자를 사용하면 모두 액세스 할 수 있습니다.

임의의 하위에 Application.Worksheet Function을 작성합니다. 그리고 함수의 이름을 쓰기 시작하십시오. VBA의 intellisense는 사용할 수있는 기능의 이름을 표시합니다. 함수 이름을 선택하면 Excel의 다른 함수와 마찬가지로 변수를 묻습니다. 그러나 VBA가 이해할 수있는 형식으로 변수를 전달해야합니다. 예를 들어 A1 : A10 범위를 전달하려면 Range ( “A1 : A10”)와 같은 범위 객체로 전달해야합니다.

따라서 더 잘 이해하기 위해 몇 가지 워크 시트 함수를 사용하겠습니다.

VBA에서 VLOOKUP 함수를 사용하는 방법

image

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

이 코드를 실행하면이 결과를 얻을 수 있습니다.

image

VBA가 결과를 메시지 상자에 얼마나 빨리 인쇄하는지 확인할 수 있습니다. 이제 코드를 살펴 보겠습니다.

어떻게 작동합니까?

1.

Dim loginID As String Dim name, city As String 먼저 VLOOKUP 함수가 반환 한 결과를 저장하기 위해 문자열 유형의 두 변수를 선언했습니다. VLOOKUP에서 반환 된 결과가 문자열 값이 될 것이기 때문에 문자열 유형 변수를 사용했습니다. 워크 시트 함수가 숫자, 날짜, 범위 등의 값 유형을 반환 할 것으로 예상되는 경우 해당 유형의 변수를 사용하여 결과를 저장합니다. 워크 시트 함수가 어떤 종류의 값을 반환할지 확실하지 않은 경우 변형 유형 변수를 사용합니다.

2.

loginID = “AHKJ_1-3357042451”

다음으로 loginID 변수를 사용하여 조회 값을 저장했습니다. 여기서는 하드 코딩 된 값을 사용했습니다. 참조도 사용할 수 있습니다. 예를 들면.

Range ( “A2”). Value를 사용하여 범위 A2에서 조회 값을 동적으로 업데이트 할 수 있습니다.

3.

이름 = Application.WorksheetFunction.VLookup (loginID, Range ( “A1 : K26”), 2, 0)

여기에서는 VLOOKUP 함수를 사용하여 가져옵니다. 이제 함수를 수정하고 괄호를 열면 필요한 인수가 표시되지만 Excel에 표시되는 것만 큼 설명 적이지는 않습니다. 직접 확인하십시오.

image

사용해야하는 변수와 방법을 기억해야합니다. 언제든지 워크 시트로 돌아가 설명 변수 세부 정보를 볼 수 있습니다.

여기에서 조회 값은 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

image

여러 워크 시트 함수 사용 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

image

보시다시피 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

image

여기에서는 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

When you run the above sub, it will return:

5

He

o

ll

Excel의 VBA에서 VLOOKUP과 같은 워크 시트 함수를 사용하는 방법에 대한이 기사가 설명이되기를 바랍니다. 여기에서 VBA 수식 및 관련 Excel 수식에 대한 더 많은 문서를 찾을 수 있습니다. 블로그가 마음에 들면 Facebook에서 친구들과 공유하세요. 또한 Twitter와 Facebook에서 팔로우 할 수도 있습니다. 우리는 여러분의 의견을 듣고 싶습니다. 우리가 작업을 개선, 보완 또는 혁신하고 여러분을 위해 더 잘 만들 수있는 방법을 알려주십시오. [email protected]로 이메일을 보내주세요.

관련 기사 :

link : / excel-macros-and-vba-what-is-csng-function-in-excel-vba [Excel VBA에서 CSng 함수 란?]: SCng 함수는 모든 데이터 유형을 단 정밀도 부동 소수점 숫자 ( “숫자 인 경우”). 나는 주로 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 [Simplest VBA Code to Highlight Current Row and Column Using]:이 작은 VBA 스 니펫을 사용하여 강조 표시 시트의 현재 행과 열.

인기 기사 :

link : / tips-if-condition-in-excel [Excel에서 IF 함수 사용 방법]: Excel의 IF 문은 조건을 확인하고 조건이 TRUE 인 경우 특정 값을 반환하거나 FALSE 인 경우 다른 특정 값을 반환합니다. .

link : / formulas-and-functions-introduction-of-vlookup-function [Excel에서 VLOOKUP 함수 사용 방법]: 다양한 범위의 값을 조회하는 데 사용되는 Excel에서 가장 많이 사용되는 인기 함수 중 하나입니다. 및 시트.

link : / excel-formula-and-function-excel-sumif-function [Excel에서 SUMIF 함수 사용 방법]: 대시 보드의 또 다른 필수 기능입니다. 이를 통해 특정 조건에 대한 값을 합산 할 수 있습니다.

link : / tips-countif-in-microsoft-excel [Excel에서 COUNTIF 함수 사용 방법]:이 놀라운 함수를 사용하여 조건으로 값을 계산합니다. 특정 값을 계산하기 위해 데이터를 필터링 할 필요가 없습니다. Countif 기능은 대시 보드를 준비하는 데 필수적입니다.