Vlookup 빠르게 하기

전제조건

 : 오름차순 혹은 내림차순 정렬이 가능할 것

 : 중복이 없을 것 (중복이 있다 해도 if문으로 처리는 가능)

 

배경

 : 엑셀의 함수 vlookup의 경우, 정렬이 되어있다고 가정되어있지 않기 때문에 복잡도가 높음.

적은 양의 데이터면 모르겠지만, 수천, 수만 개, 수십만 개의 데이터인 경우 Vlookup의 속도가 매우 매우 심각하게 느려짐..

 

vlookup 예제

vlookup 함수의 정확한 내부 알고리즘은 모르지만, 

위 예제의 경우, 

1. 1을 6번, 2를 6번.... 10을 6번 총 60번의 비교를 강제로 하거나.

2. 1을 1번, 2는 6번, 3은 2번, 4는 6번, 5는 3번, 6은 4번, 7은 6번, 8은 5번, 9는 6번, 10은 6번

    (lookup 값을 찾으면 거기서 중지, 못 찾으면 끝까지 서칭이라는 알고리즘일 경우)

    총 45번의 비교를 할 가능성이 매우 높음

 

하지만 VBA로 

1. 기준열과 대조열을 비교하여 일치하면 대조열을 한 칸 내리고

2.  틀리다면 기준열을 한 칸 내리는 작업을 통해 총 16번의 비교만으로 vlookup을 수행 가능

 

위 예제의 경우 비교 순서 

(기준열 값, 대조열 값)

(1,1) - (1,3) - (2, 3) - (3, 3) - (3, 5) - (4, 5) - (5, 5) - (5, 6) -
(6,  6) - (6, 8) - (7, 8) - (8, 8) - (8, 10) - (9, 10) - (10, 10) - (10, ) - 종료

 

아래 코드가 최적화된 코드는 아니지만, 

이런 알고리즘도 있다는 것을 염두하면 데이터 처리를 빠르게 수행 가능.

 

---

Sub main()
    y1 = 2
    x1 = 1    
    y2 = 2
    x2 = 5    
    Do
        If Cells(y1,x1) > Cells(y2,x2) then
            y2=y2+1
        End If
        If Cells(y1, x1) = Cells(y2, x2) Then
            Cells(y1, x1 + 1) = Cells(y2, x2 + 1)
            y2 = y2 + 1
        End If
        y1 = y1 + 1
    Loop Until Cells(y1, x1) = ""
End Sub

---