여러 가지 조건을 만족하는 값을 찾을 때(2-2) - 사용자 정의 함수(2)

여러 가지 조건 검색2(완성).xlsm

0.02MB

 

 

4. 사용자 정의 함수 설명

가. Option Explicit

변수에 대한 선언을 명시적으로 하라는 것입니다.

 

5행에 커서를 둔 상태에서 위 편집 도구 모음(보기 > 도구모음 > 편집을 눌러 추가)에서 주석 블록 설정 아이콘을 눌러

 

5행을 주석처리하면 해당 줄 맨 앞에 작은따옴표가 추가되고, 글자가 녹색으로 바뀝니다.

 

이제 기본 도구 모음에서 엑셀 아이콘을 눌러 엑셀로 돌아간 다음

C2셀에서 F2키를 눌러 편집모드로 들어간 다음 엔터 키를 누르거나, 수식 입력 줄을 클릭해서 커서를 넣은 다음 엔터 키를 눌러 수식을 실행하면

 

아래와 같이 '변수가 정의되지 않았습니다'란 에러 메시지가 표시됩니다.
따라서, 변수 i엘 대한 주석을 풀어서 변수 i는  선언을 해야 합니다.

 

나. Option Base 1

배열의 인덱스 값이 0부터 시작하는데 1부터 시작하도록 변경하는 것입니다.

 

 

다. 범위를 배열로 변환

① 범위를 배열로 변환하는 구문 설명

'배열은 그대로 두고, 범위일 경우 배열로 변환     Dim Range_to_Array As Variant     Range_to_Array = lookup_array

Dim Range_to_Array As Variant : Range_to_Array 변수를 범위 또는 배열 등이 될 수 있도록 Variant(가변적) 형식으로 지정합니다.

 

Range_to_Array = lookup_array : lookup_array 변수를 Range_to_Array 배열에 저장합니다.

 

10행에 중단점을 설정한 다음

 

워크시트로 돌아가서 C2셀의 수식 입력줄에 커서를 넣은 다음 엔터키를 누르면

 

VB 에디터가 실행되면서 중단점에서 실행을 멈추는데,

 

보기 > 지역 창을 눌러 지역 창을 보면 Module의 변수 등이 표시되는데, Range_to_Array왼쪽에 +가 표시되는데,

+를 누르면 Range_to_Array 배열이 펼쳐지는데, Range_to_Array1)부터 (4)까지 보입니다. 지역 창의 높이를 더 높이

 

더 많이 보이는데, Range_to_Array(1)의 왼쪽 +를 누르면 Range_to_Array(1,1)이라는 2차원 배열에 "나라명연도"가 들어가 있습니다. 

 

Range_to_Array(2)의 왼쪽 +를 누르면 Range_to_Array(2,1)에는 "한국2021"이 들어 있습니다.

 

따라서, A2셀과 B2셀을 결합한 값 "한국2021"과 일치하기 때문에 H2셀의 값 50을 반환하는 것입니다.

 

 

② 범위를 배열로 변환하는 구문 주석 처리 후 실행

 

먼저 위 구문 2줄을 마우스를 끌어서 선택한 후 '주석 블록 설정' 아이콘을 눌러 주석 처리합니다.

그러면, Range_to_Array변수가 없어지므로

For문의 Range_to_Array를 모두 lookup_array로 바꿉니다.

 

그리고, C2셀의 수식을 실행하면 찾을 범위가 두 개 열이라서 &로 결합하는 경우는 문제가 없는데

 

찾을 범위로 F열, 반환 범위로 H열 등 한 개 열만 지정해서

=multi_vlookup(A2,F:F,H:H)라고 수식을 수정하고 엔터 키를 누르면 오류가 발생합니다.

 

중단점에서 실행이 멈추는데,

Ubound(lookup_array)에 커서를 대면 "<형식이 일치하지 않습니다>"란 에러 메시지가 표시됩니다.

이것은 lookup_array가 배열이 아니라 범위이기 때문입니다.

 

따라서, 범위를 배열로 저장하는 부분의 주석을 해제하고, for문의 lookup_array를 Range_to_Array로 수정한 다음

C2셀의 수식을 F2키를 누른 후 엔터키를 눌러 입력하면 아래와 같이 중단점에서 실행을 멈추는데,

Ubound(lookup_array)에 커서를 대면 아까와는 달리 1048576이라고 배열의 개수를 반환합니다.

 

라. For ~ Next 반복문 실행

For i = 1 To UBound(Range_to_Array)         If StrComp(lookup_value, Range_to_Array(i, 1), 1) = 0 Then             multi_vlookup = return_array(i, 1)             Exit For         Else             multi_vlookup = CVErr(xlErrNA)         End If     Next

 

① Counter와 Ubound

i가 1부터 Ubound(Range_to_Array)까지 변하는 동안 반복하는데,

Ubound는 배열의 상한 서브스크립트(subscript)를 반환하는 함수인데,

배열의 요소가 0부터 5라면 5, 1부터 6이라면 6을 반환합니다.

 

Range_to_Array의 크기를 지역 창에서 살펴보면 1차원이 1부터 1048576, 2차원이 1부터 1까지입니다.

 

 

② StrComp 함수

StrComp함수는 문자열을 비교하는 함수로

구문은 StrComp(string1, string2 [, compare] )인데,

세 번째 인수인 compare를 생략하면 텍스트 비교를 하므로 생략해도 됩니다.

 

반환값은 정확히 일치하면 0이고, string1이 string2보다 크면 1, 작으면 -1입니다.

If StrComp(lookup_value, Range_to_Array(i, 1)) = 0 Then             multi_vlookup = return_array(i, 1)             Exit For

위 구문은 정확히 일치하면 multi_vlookup에 return_array에서 i행 1열의 값을 반환하는 것입니다.

여기서 return_array는 배열과 비슷하게 생겼는데, H:H이므로 범위입니다.

 

③ Exit For

일치하면 더 이상 비교할 필요가 없으므로 For문을 빠져나갑니다.

 

④ CVErr함수

Else             multi_vlookup = CVErr(xlErrNA)         End If

 

CVErr함수는 오류 번호에 대한 유형(영어는 subtype)을 반환하는 함수로 

개체 찾아보기 아이콘을 누른 후 xlerr의 구성원을 찾아보면

xlErrDiv0(0으로 나눌 수 없음), xlErrNA(찾을 수 없음), xlErrRef(참조 에러) 등 여러 가지가 있는데, xlErrNA 에러를 반환하도록 했습니다.

 

 

마. 열 전체를 지정할 경우 속도 문제 해결

데이터가 있는 범위를 지정하는 것이 열 전체를 지정하는 것보다 훨씬 빠릅니다.

왜냐하면 값이 있을 때는 그 행에서 멈추면 되는데,

값이 없을 때는 마지막 행인 1048676행까지 찾아야 하기 때문입니다.

 

이것을 피하려면

찾을 범위의 값이 없으면 For문을 빠져나가도록

If i >= 2 And Len(Range_to_Array(i, 1)) = 0 Then             If Len(Range_to_Array(i - 1, 1)) > 0 _             And Len(Range_to_Array(i, 1)) = 0 Then Exit For         End If

구문을 추가하면 됩니다.

 

처리행이 1일 경우 Range_to_Array(i - 1, 1)에서 i-1이 0이 되므로 에러가 발생해서

i가 2 이상이어야 한다는 조건을 추가했고,

 

처리행인 Range_to_Array(i, 1) 값이 공백일 때 다시 말해 길이(Len)가 0일 때 처리를 멈추면 될 듯한데, 

Len(Range_to_Array(i - 1, 1)) > 0이라는 조건을 추가한 것은 연속으로  2행이 공백일 때는 For문을 빠져나가면 안 되기 때문에 

Len(Range_to_Array(i - 1, 1)) > 0 And Len(Range_to_Array(i, 1)) = 0라고 이전 배열은 길이가 0보다 크고, 처리 중인 배열은 공백이어야 한다는 조건을 지정한 것입니다.

 

속도가 많이 느리긴 하는데, 수식 입력이 편해져서 참고 쓸만합니다.

 

 

5. 에러일 경우의 처리

가. 값이 일치하지 않을 경우 multi_vlookup에 에러 값 대입

multi_vlookup함수의 결과가 에러일 때

 

0을 반환하도록 하려면

ifError함수를 이용해 에러일 때 값을 0으로 지정해야 합니다.

 

나. For문을 종료한 후 에러 값 대입

If i >= 2 Then             If Len(Range_to_Array(i - 1, 1)) > 0 _             And Len(Range_to_Array(i, 1)) = 0 Then Exit For         End If                      If StrComp(lookup_value, Range_to_Array(i, 1)) = 0 Then             multi_vlookup = return_array(i, 1)             Exit For '        Else '            multi_vlookup = CVErr(xlErrNA)         End If     Next     If Len(multi_vlookup) = 0 Then multi_vlookup = CVErr(xlErrNA)

 

위와 같이 if조건문에서 multi_vlookup에 에러 값을 대입하지 않고,

반복문이 종료한 다음에

If Len(multi_vlookup) = 0 Then multi_vlookup = CVErr(xlErrNA)

를 넣어 봤는데, 여전히 속도가 느립니다.

 

If Len(multi_vlookup) = 0는 For 반복문에서 일치하는 값이 없을 경우 multi_vlookup에 값이 할당되지 않기 때문에 길이가 0이 니다.