여러 가지 조건을 만족하는 값을 찾을 때(3) - 동적 배열을 이용한 속도 개선

동적 배열을 이용해서 사용자 정의 함수 코드를 수정했더니

훨씬 속도가 빨라졌습니다.

 

 

1. 사용자 정의 함수 구문

 

가. 기존 코드

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

0.02MB

 

 

 

Option Explicit
Option Base 1

Function multi_vlookup(lookup_value, lookup_array, return_array As Range)
    Dim i As Double
    
    '배열은 그대로 두고, 범위일 경우 배열로 변환
    Dim Range_to_Array As Variant
    Debug.Print (vbCrLf & "범위 배열 변환1 : " & Now())
    Range_to_Array = lookup_array
    
    For i = 1 To UBound(Range_to_Array)
        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
            
        If StrComp(lookup_value, Range_to_Array(i, 1)) = 0 Then
            Debug.Print ("일치 값 반환 : " & Now())
            multi_vlookup = return_array(i, 1)
            Exit For
'        Else
'            multi_vlookup = CVErr(xlErrNA)
        End If
    Next
    
    
    If Len(multi_vlookup) = 0 Then
        Debug.Print ("에러 값 대입 : " & Now())
        multi_vlookup = CVErr(xlErrNA)
    End If
    
    Debug.Print ("완료 : " & Now())
End Function

 

 

나. 개선된 코드

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

0.02MB

 

 

 

Option Explicit
Option Base 1

Function multi_vlookup(lookup_value, lookup_array, return_array As Range)
    
    Dim i As Double
    
    ' 배열은 그대로 두고, 범위일 경우 배열로 변환
    
    ' 찾는 값인 lookup_value
    Dim Lookup_Value_to_Array As Variant
    Debug.Print (vbCrLf & "범위 배열 변환1 : " & Now())
    Lookup_Value_to_Array = lookup_value
    
    ' 찾는 범위인 lookup_array
    Dim Lookup_Array_to_Array As Variant
    Debug.Print (vbCrLf & "범위 배열 변환2 : " & Now())
    Lookup_Array_to_Array = lookup_array
    
    ' 반환 범위인 return_array
    Dim Return_Array_to_Array As Variant
    Debug.Print (vbCrLf & "범위 배열 변환3 : " & Now())
    Return_Array_to_Array = return_array
    
    ' multi_vlookp에 바로 넣을 수 없으므로 임시 배열 result_array에 저장
    Dim result() As Variant
    
    
    ' Lookup_Value_to_Array의 하한 인덱스부터 상한 인덱스까지 실행
    For i = LBound(Lookup_Value_to_Array) To UBound(Lookup_Value_to_Array)
        
        ' 워크시트 내장 함수인 Match함수 사용(속도개선은 없음)
        If Not IsError(Application.Match(Lookup_Value_to_Array(i, 1), Lookup_Array_to_Array, 0)) Then
            Debug.Print ("일치 값 반환 : " & Now())
            '배열 크기 변경
            ReDim Preserve result(i)
            
            '배열에 일치하는 값 대입
            result(i) = Return_Array_to_Array(Application.Match(Lookup_Value_to_Array(i, 1), Lookup_Array_to_Array, 0), 1)
        Else
            ' 배열 크기 변경
            ReDim Preserve result(i)
            
            ' 찾는 값이 없을 때 0이 반환되는데, #N/A로 대체
            Debug.Print ("에러 값 대입 : " & Now())
            result(i) = CVErr(xlErrNA)
        End If
    Next

    ' 배열을 가로에서 세로로 전환
    multi_vlookup = WorksheetFunction.Transpose(result)
    Debug.Print ("완료 : " & Now())
End Function

 

 

2. 기존 사용자 정의 함수와의 차이점

 

가. 셀마다 수식 입력 → 한 셀에 찾을 값의 범위를 지정

기존에는 C2셀에서 C9셀까지 셀마다 수식을 넣었는데,

[ C2셀의 수식 : =multi_vlookup(A2&B2,F:F&G:G,H:H) ]

 

개선된 코드에서는 찾을 값을 한꺼번에 선택해서 입력했더니 동적 배열로 반환됩니다.

[ C2셀의 수식 : =multi_vlookup(A2:A9&B2:B9,F:F&G:G,H:H) ]

 

나. 속도 개선

Debug.Print (vbCrLf & "범위 배열 변환1 : " & Now()) 코드를 중간중간에 넣어서 처리 속도를 측정해 봤습니다.

 

(1) 기존 코드

(Microsoft 365 버전)

C2셀부터 C9셀까지 마우스로 끌어서 선택한 후

C2셀에 =multi_vlookup(A2&B2,F:F&G:G,H:H)라고 입력하고 Ctrl + Enter키를 누르면 

C2셀에서 C9셀까지 수식이 채워집니다.

 

(Microsoft 365 이전 버전)

C2셀에 =multi_vlookup(A2&B2,F:F&G:G,H:H)라고 입력하고 Ctrl + Shift + Enter키를 눌러 배열 수식으로 입력하고,

 

C2셀의 채우기 핸들을 더블 클릭해서 아래로 수식을 복사해야 합니다.

 

만약 C2셀에서 C9셀까지 범위를 선택한 후 수식을 입력하고 Ctrl + Shift + Enter키를 누르면

C2셀부터 C9셀까지 동일한 수식이 채워집니다.

 

 

(소요 시간)

 

총 8건 처리하는데 5초 정도 걸렸는데,

 

사용자 정의 함수 하나를 처리할 때는 1초 미만으로 걸리는데

사용자 정의 함수 간에는 1초 까지도 걸려서

이것이 처리 지연의 원입니다.

 

또한 에러 값 대입 부분이 순서상 여섯 번째와 일곱 번째에 있어야 하는데,

다섯 번째와 여섯 번째에 있는 것이 임의로 처리순서가 정해지는 듯합니다.

 

 

(2) 개선된 코드

(Microsoft 365 버전)

한 셀에만 수식을 입력하고 엔터키를 눌러도 찾을 범위로 지정한 셀에 수식이 자동으로 적용되지만,

 

(Microsoft 365 이전 버전)

먼저 수식을 적용할 범위를 선택하고 수식을 입력한 다음 Ctrl + Shift +Enter키를 눌러야 합니다.

 

그러면 선택된 범위에 수식이 모두 적용됩니다. 모든 셀 범위를 선택했기 때문에 찾을 범위를 절대 참조 형식으로 입력할 필요가 없습니다.

 

만약 한 셀만 선택하고 수식을 입력한 후 Ctrl + Shift + Enter키를 누르면 한 셀에만 수식이 들어갑니다.

 

(소요 시간)

 

개선된 사용자 정의 함수는 총 3초 정도 걸렸는데,

사용자 정의 함수 3개가 1초 안에 처리가 이뤄졌고,

에러 값 대입 부분도 순서에 맞게 처리됐습니다.

 

 

다. 공통 사항

debug.print문을 빼면 속도가 더 개선되고,

검색 및 반환 범위를 열 전체로 지정하지 않고 데이터가 있는 범위인 2행에서 7행까지 지정하면 훨씬 속도가 빨라집니다.

수식 : =multi_vlookup($A$2:$A$9&$B$2:$B$9,F2:F9&G2:G9,H2:H9)

 

 

3. 개선된 코드 설명

 

가. 찾을 값을 배열로 저장

Function multi_vlookup(lookup_value, lookup_array, return_array As Range)          Dim i As Double          ' 배열은 그대로 두고, 범위일 경우 배열로 변환          ' 찾는 값인 lookup_value     Dim Range_to_Array1 As Variant     Range_to_Array1 = lookup_value          ' 찾는 범위인 lookup_array     Dim Range_to_Array2 As Variant     Range_to_Array2 = lookup_array

 

기존에는 lookup_value를 한 줄씩 지정했으나,

A2:A9&B2:B9식으로 찾을 범위 전체를 범위로 지정해서

기존에는 찾을 범위만 배열로 변환했는데,

개선된 코드는 찾을 값도 전체 범위로 지정하므로 배열로 변환하는 부분을 추가했습니다.

 

 

나. 값을 저장할 임시 배열 선언

' multi_vlookp에 바로 넣을 수 없으므로 임시 배열 result_array에 저장     Dim result() As Variant

 

다. 찾을 값의 배열을 순서대로 처리하는 반복문 추가

' Range_to_Array1의 하한 인덱스부터 상한 인덱스까지 실행     For i = LBound(Range_to_Array1) To UBound(Range_to_Array1)

 

라. Match함수를 이용해 찾을 범위에서 일치하는 행을 찾고, 반환 값 범위에서 해당 값을 찾음

' 워크시트 내장 함수인 Match함수 사용(속도개선은 없음)         If Not IsError(Application.Match(Range_to_Array1(i, 1), Range_to_Array2, 0)) Then             '배열 크기 변경             ReDim Preserve result(i)                          '배열에 일치하는 값 대입             result(i) = return_array(Application.Match(Range_to_Array1(i, 1), Range_to_Array2, 0), 1)

 

result 배열을 선언하면서 크기를 지정하지 않았기 때문에

값을 대입하기 전에 배열의 크기를 하나씩 늘리고, 기존 값은 보존(preserve)합니다.

 

마. 일치하는 값이 없을 때 에러 값 대입

Else             ' 배열 크기 변경             ReDim Preserve result(i)                          ' 찾는 값이 없을 때 0이 반환되는데, #N/A로 대체             result(i) = CVErr(xlErrNA)         End If

 

바. 배열을 가로에서 세로로 전환

' 배열을 가로에서 세로로 전환     multi_vlookup = WorksheetFunction.Transpose(result)

 

만약 위와 같이 하지 않고,

multi_vlookup = result라고 하게 되면

처리 결과가 가로로 표시됩니다.

그런데, 배열 부분에 값이 있어서 #분산! 에러가 발생했습니다.

 

WorksheetFunction.Transpose는 행/열 전환을 해주는 함수로,

 

VBA에 없기 때문에 워크시트의 내장함수인 Transpose를 사용한 것으로

워크시트 함수라는 것을 알려주기 위해 Transpose에 WorksheetFunction.을 붙였습니다.