[VBA] VLOOKUP VBA 와 FIND VBA 속도 차이

어제 완전 삽질을 했다.

데이터 갯수 : 4만개

해야 할 일 : 엑셀 시트에 있는 파일명이 실제 PC의 폴더에 존재하는지 여부와 해당 폴더명을 엑셀에 기록

I5-4200 CPU 2.3GHz, 8G 메모리, SSD 를 사용하는 노트북과 AMD 6100 CPU(6-Core) 3.3GHz, 8G 메모리, SSD(Plexstor 6M Pro) 를 사용하는 데스크탑에서 동일한 VBA 코드를 가지고 돌려봤더니, 노트북은 20분이 안걸린다. 데스크탑은 49분이나 걸렸다.

왜 이런 현상이 벌어지나 하고 Desktop PC 악성코드 검사를 해서 악성코드도 몇개 발견, 치료했고, ASUS 메인보드 BIOS 를 최근에는 업데이트를 안해서 구글링을 해서 업데이트도 했다.

처음에는 USB로 BIOS Update 데이터를 받아서 재부팅하고 하려고 있는데, HDD(하드디스크)상에 존재하는 파일을 쉽게 찾을 수 있는 기능이 있어서 그걸로 BIOS 를 업데이트했다.

이런 삽질 하느라고 VBA 코드가 잘못된 것은 없나 확인하면서 돌려보고, 중간에 PC 상태 업데이트를 하면서 진행하다보니, VBA 파일이 깨졌는지 열리지가 않는다.

수정하면서 짠 VBA 코드가 다 날라가 버렸다. ㅠㅠㅠ

다시 수정하고, VBA 코드를 블로그나 홈페이지에 백업을 해두지 않으면 안되겠다는 생각이 들어 일부는 기록을 해두었다.

열심히 삽질을 했지만, AMD CPU 가 성능을 제대로 내주지 못한다는 것으로 결론을 1차 내렸다.

하지만, 내가 작업하는 파일, VBA 코드 등은 Desktop PC에서 작업하는게 편하다. 23인치 모니터 2대를 놓고 비교해보면서 작업하는데 어찌 노트북 화면에 비유할 수 있으랴..

그래서, 이번에는 PC의 특정 폴더와 서브폴더에 존재하는 모든 파일을 엑셀 시트에 기록하는 VBA 코드로 돌려보니 1분이 안걸린다. 여기까지는 좋았다.

아래 코드로 두 Sheet 의 데이터가 일치하는 것만 표시하는 것을 했더니 동일한 반복작업을 4만번씩 실행하는 통에 속도가 더 느린 거 같아서 중간에 ESC 키를 눌러서 중단시켜 버렸다.

아래 코드는 데이터가 적을 경우에는 나름 효율적(?)인 코드이다. 그러나 데이터가 방대해질 경우에는 사용해서는 안되는 코드라는게 ....

Sub 중복자료Find()  '// 중복되는 것만 가져오기
    Dim sht1, sht2    As Worksheet   '// 시트(Sheet)를 넣을 변수
    Dim Target As Range     '// 검사할 시트의 범위 구간
    Dim rngAll As Range, FindCell As Range  '// 현재시트의 구간 범위
    Dim C As Range, strAddr As String   '// 영역변수 및 주소를 저장할 변수
    Dim i&, n&, k&, s&, sRow&, oldT As Single
  
    Application.StatusBar = True

    Application.ScreenUpdating = False
    oldT = Timer()
    Set sht1 = Sheets("Main")   '// Main 워크시트는 현재 시트
    Set sht2 = Sheets("FileList")   '// FileList 워크시트는 데이터가 있는 Target 시트
    sRow = Cells(Rows.Count, "A").End(3)(2).Row    '// A열의 값이 들어있는 마지막 셀을 첫셀로 지정
    Set rngAll = sht1.Range(sht1.Cells(sRow, "G"), sht1.Cells(Rows.Count, "G").End(3))
    Set Target = sht2.Range(sht2.Cells(2, "B"), sht2.Cells(Rows.Count, "B").End(3))
  
    sht1.Select     '// 작업의 실수를 방지하기 위해 해당 시트 선택
    sht1.Range(sht1.Cells(1, "A"), sht1.Cells(Rows.Count, "A").End(3)).Offset(1).Clear
   
    On Error Resume Next
    i = Range(Cells(sRow, "G"), Cells(Rows.Count, "G").End(3)).SpecialCells(2).Count
    For Each FindCell In rngAll.Cells
        n = n + 1
        If (n Mod 500) = 0 Then
            Application.ScreenUpdating = True
            Application.StatusBar = "셀: " & FindCell.Address(0, 0) & " / " & FindCell & " / " & Format(n / i, "0.00% 진행중... ") & "경과시간: " & Format(Timer() - oldT, "0.00초 걸림"): DoEvents
            Application.ScreenUpdating = False
        End If
        Set C = Target.Find(what:=FindCell, Lookat:=xlWhole)
        '// Target 범위에서 FindCell 과 100% 일치하는 데이터를 찾아 C에 넣어라
        If Not C Is Nothing Then    '// 찾는 값이 있으면
            strAddr = C.Address     '// 최초 셀 주소를 기억하게 strAddr 에 저장
            Do  '// 무한 루프 시작
                If Len(Cells(FindCell.Row, "A")) = 0 Then
                    Cells(FindCell.Row, "A") = C.Offset(, -1)
                    s = s + 1
                Else
                    Cells(FindCell.Row, "A") = Cells(FindCell.Row, "A") & vbNewLine & C.Offset(, -1)
                    Cells(FindCell.Row, "A").Interior.ColorIndex = 26
                    k = k + 1
                End If
                Set C = Target.FindNext(C)   '// 다음셀을 찾음
            Loop While Not C Is Nothing And strAddr <> C.Address    '// 찿는 셀이 없거나 첫번째 셀이면 루프문 종료
        End If
    Next
    Application.StatusBar = "작업완료"
   Set rngAll = Nothing    '// 메모리 비우기
   MsgBox s & " 개 신규 " & vbLf & k & " 개 중복 " & vbLf & Format(Timer() - oldT, "0.00초 걸림"), 64, Now()
End Sub

이번에는 비교할 자료가 중복이 존재하는지 정렬(sort)를 하고 나서 위 아래 셀간에 비교문을 만들어서 중복된 자료를 찾아서 제거했다.

그리고 VLOOKUP VBA 코드를 돌렸다.

Sub Vlookup_VBA()
'Application.VLOOKUP(lookup_value, table_array, column_index, range_lookup)
    Dim sht1, sht2    As Worksheet   '// 시트(Sheet)를 넣을 변수
    Dim lookFor As Range
    Dim table_array As Range
    Dim varResult As Variant
    Dim table_array_col As Integer
    Dim lookFor_col As Integer
    Dim oldT As Single  '// 코드 시작시점 넣을 변수
   
   
    oldT = Timer()
    Set sht1 = Sheets("Main")   '// Main 워크시트는 현재 시트
    Set sht2 = Sheets("FileList")   '// FileList 워크시트는 데이터가 있는 Target 시트
    sht1.Select     '// 작업의 실수를 방지하기 위해 해당 시트 선택
   
    Set lookFor = Range([G2], Cells(Rows.Count, "G").End(3))
'    Set table_array = Range("F7:H21")  '// 같은 Sheet 에 있는 테이블을 지정할 때
    Set table_array = sht2.Range("B2:C" & Cells(Rows.Count, "B").End(3).Row)
      '// 다른 Sheet 에 있는 테이블을 지정할 때
    'Set table_array = Workbooks("Book1.xls").Sheets("Sheet1").Range("F2:Q35602")
      '// 다른 File 에 있는 Sheet 를 지정할 때
   
    table_array_col = 2  '// table_array 에서 몇번째 열의 값을 가져올 것인지 설정
   
    varResult = Application.VLookup(lookFor.Value, table_array, table_array_col, 0)
   
    lookFor_col = -6  '// lookFor.Value 열로부터 몇번째 열인지 지정. 0/1/2/3 순으로 카운트 함
    lookFor.Offset(0, lookFor_col) = varResult
   
     MsgBox "총 " & Format(Timer - oldT, "#0.00  초 소요")
End Sub

위 VBA 코드로 4분만에 결과가 나왔다.

VLOOKUP 함수는 동일 자료가 존재하는 경우 무조건 첫번째 만난 셀의 값을 반환한다. 그러다보니 혹시라도 모를 중복자료가 있는 걸 찾아낼 수 없다는 점 때문에 처음에 사용을 하지 않았었다.

그러나 PC 성능의 문제가 있기는 하지만, 동일 PC에서 속도 차이가 10배 이상 차이가 발생했다.

코드 구조상 반복작업을 엄청나게 해야 하는 FIND VBA 코드이니까 당연한 것이지만....

데이터가 더 많아질 경우에는 FIND VBA 코드로는 결과를 얻는데 더 많은 시간을 요구할 것이다.

리눅스 서버에 자료를 올리고 나서 Join 을 사용해서 해야 할 거 같다.

엑셀에서 SQL 문으로 Join 을 하면 어느 정도 성능이 나올까 궁금하기는 하다.

* MySQL 에서 두개의 테이블을 JOIN 하여 칼럼 업데이트를 한 결과 2초도 안되는 시간에 결과를 돌려줬다.

   엑셀 VBA 로 작업하면 적어도 90 분 이상은 소요되었을 거라고 본다.