엑셀 자동화 끝판왕! ChatGPT로 10분안에 자동화 서식 만들기

일반 매크로와 이벤트 매크로의 차이점

회사에서 엑셀로 같은 작업을 반복하다 보면 자연스럽게 '자동화'에 관심을 갖게 됩니다. 그러나 막상 공부를 시작해 보면 "VBA, 모듈, 유저폼…"처럼 다소 생소한 용어들 때문에 첫 단계부터 어려움을 겪는 경우가 많은데요. 오늘 수업을 끝까지 따라오시면 엑셀 자동화 서식을 누구보다 쉽게 만들 수 있게 될 겁니다.

엑셀 자동화의 출발점은 두 종류의 매크로 코드를 정확히 이해하는 것입니다. 바로 "일반 매크로"와 "이벤트 매크로"의 차이점인데요. 두 코드는 "언제, 무엇을 트리거로 동작하는가"에서 결정적인 차이를 보입니다.

이 두 가지 개념만 정확히 이해하면, ChatGPT의 도움을 받아 누구나 손쉽게 자동화 서식을 완성할 수 있습니다.

1. 예제파일을 실행한 후 [개발도구] 탭 - [Visual Basic] 버튼을 클릭하거나 단축키 Alt + F11을 눌러 매크로 편집기를 실행합니다. 만약 [개발도구] 탭이 보이지 않는다면, 리본 메뉴를 우클릭한 뒤 [리본 메뉴 사용자 지정]에서 '개발도구' 탭을 체크해 활성화합니다.
2. 매크로 편집기를 실행하면 좌측에 [프로젝트] 창이 표시됩니다. 프로젝트 창에는 현재 열려 있는 모든 통합문서와 그 안에 포함된 시트, 모듈, 유저폼이 한 곳에 정리되어 표시됩니다.
3. 이번 수업에서는 [거래내역관리] 시트에 자동화 기능을 추가할 예정이므로, 프로젝트 창에서 '거래내역관리' 시트를 더블클릭합니다. 그러면 우측에 코드를 작성할 편집 영역이 열립니다.
4. 먼저 가장 단순한 예제로 '일반 매크로'를 작성해 보겠습니다. 편집기에 다음과 같이 테스트 코드를 입력합니다.

   Sub 테스트()
   MsgBox "안녕하세요"
   End Sub

   

5. 편집기에서 코드를 선택한 다음, 상단의 재생 버튼을 클릭하거나 F5를 눌러 코드를 실행하면 그림과 같이 "안녕하세요"라는 안내창이 출력됩니다. 이렇게 작성한 '일반 매크로'는 이후 도형이나 버튼에 등록한 뒤 클릭해서 실행할 수 있습니다.
6. 이번에는 이벤트 매크로를 추가해 보겠습니다. 편집 영역에서 좌측 상단의 "(일반)"을 클릭한 뒤 목록에서 "Worksheet"를 선택합니다. 그러면 그림과 같이 "Worksheet_SelectionChange"라는 이벤트 매크로가 자동으로 추가됩니다.
7. SelectionChange는 시트의 기본 이벤트 매크로로, "셀 선택을 변경했을 때" 동작합니다. 예를 들어 SelectionChange 안에 앞서와 동일한 메시지박스 코드를 작성한 후 시트에서 셀 선택을 바꾸면, 셀을 옮길 때마다 안내창이 자동으로 실행되는 것을 확인할 수 있습니다.

   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   MsgBox "안녕하세요"
   End Sub

   

8. 또한 SelectionChange의 인수인 'Target'을 활용해 다음과 같이 MsgBox 부분을 수정하면 선택한 셀의 셀 주소가 실시간 안내창으로 표시되는 서식을 만들 수 있습니다.

   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   MsgBox Target.Address
   End Sub

   

9. 이 외에도 통합문서와 시트에는 다양한 이벤트를 트리거로 활용할 수 있습니다. 실무에서 자주 사용되는 대표적인 이벤트 트리거는 다음과 같습니다.
   · 시트 이벤트

   이벤트명	발생 시점	중요도
   SelectionChange	셀을 클릭할 때	⭐⭐⭐⭐⭐
   Change	셀 값이 변경될 때	⭐⭐⭐⭐⭐
   BeforeDoubleClick	셀을 더블클릭하기 직전	⭐⭐⭐⭐
   Activate	시트가 활성화될 때	⭐⭐⭐⭐
   BeforeRightClick	셀을 우클릭하기 직전	⭐⭐⭐
   Deactivate	시트가 비활성화될 때	⭐⭐⭐

   · 통합문서 이벤트

   이벤트명	발생 시점	중요도
   Open	통합문서를 열 때	⭐⭐⭐⭐⭐
   BeforeClose	통합문서를 닫기 직전	⭐⭐⭐⭐⭐
   BeforeSave	저장하기 직전	⭐⭐⭐⭐
   SheetActivate	시트가 활성화될 때	⭐⭐⭐⭐

필터링 매크로와 버튼 만들기

이제 본격적으로 검색창에 단어를 입력한 뒤 버튼을 클릭하면 데이터가 자동으로 필터링되는 매크로를 작성해 보겠습니다. 놀라운 점은 서식에 필요한 모든 코드를 ChatGPT가 대신 작성해 준다는 것인데요. ChatGPT 무료 버전에서도 누구나 활용할 수 있으며, 미리 준비된 프롬프트를 복사해 붙여넣는 것만으로 자동화 서식을 손쉽게 완성할 수 있습니다.

1. 먼저 실시간 필터를 실행하는 매크로를 작성해 보겠습니다. 예제파일과 함께 첨부된 워드 파일을 실행한 다음, 첫 번째 항목인 "거래처 검색 매크로" 프롬프트를 복사해 ChatGPT에 입력하고 실행합니다. [요청] 영역에 정리된 전체 과정은 잠시 실습을 멈추고 꼼꼼히 살펴보시는 것을 권장합니다.

   == [핵심지침] 시작 ==
   - 당신은 지금부터 엑셀 VBA 매크로 전문가로서, 아래 요청한 작업에 필요한 매크로 코드를 작성합니다.
   - 작성한 VBA 코드를 초보자도 쉽게 이해할 수 있도록, 작성된 코드에는 모든 주요 단계 및 주석을 꼼꼼하게 친절하고 추가하세요.
   - 주석은 코드의 목적, 실행 방법, 작동 원리를 간결하고 명확하게 설명합니다.
   - Option Explicit 선언은 제외합니다.
   - 모든 엑셀 버전에서 문제없이 동작할 수 있도록 Late Binding 방식으로 작성하세요.
   - 외부 라이브러리가 필요한 경우, 참조 설정 없이도 작동할 수 있도록 코드에 포함합니다.
   == [핵심지침] 끝 ==
   == [요청] 시작 ==
   - 이번에 작성하는 매크로는 [거래처DB] 시트에 작성된 거래처 정보를 검색한 후, 검색 결과를 [거래내역관리] 시트에 출력하는 동작을 수행합니다.
   - [거래처DB] 시트에는 A1셀을 시작으로 ‘거래처ID, 거래처명, 구분, 담당자, 연락처, 최근거래일’이 작성되어 있습니다.
   - [거래내역관리] 시트에서 C2셀의 값을 기준으로 [거래처DB] 시트의 데이터를 검색합니다. B2셀의 값이 “거래처명” 이면 B열, “구분” 이면 C열을 기준으로 검색하고, C2셀의 값을 포함하는 모든 데이터를 필터링합니다.
   - 빠른 동작을 위해 코드를 시작하기 전 dictionary 를 생성해 검색 결과를 dictionary에 보관합니다. 검색을 마치면 검색 결과를 임시 배열로 옮긴 후 완성된 배열을 한 번에 출력하세요.
   - 검색 결과는 [거래내역관리] 시트의 B5셀에 “거래처ID, 거래처명, 구분, 최근거래일” 필드를 머리글을 제외한 값으로만 출력합니다. 최근 거래한 거래처가 맨 위에 나오도록, 최근 거래일 기준 내림차순으로 출력합니다. 만약 B5:E5을 포함해 그 아래에 기존 데이터가 있다면, B5:E5를 포함한 아래에 작성된 기존 데이터를 지운 후 검색 결과를 출력합니다.
   ==[요청] 끝 ==
   자, 이제 코드 작성을 시작하세요.

   

2. 프롬프트를 실행하면 다음과 같이 실시간 필터를 처리하는 매크로 코드가 자동으로 작성됩니다. [코드 복사] 버튼을 눌러 코드를 복사한 다음, [매크로 편집기]에서 '거래내역관리' 시트 모듈에 붙여넣습니다.

   Sub 거래처검색_출력()
    
   '------------------------------
   ' [코드 목적]
   ' [거래내역관리] 시트에서 B2, C2 조건에 맞춰
   ' [거래처DB] 시트에서 거래처를 검색 후,
   ' 결과를 [거래내역관리] 시트 B5:E열에 출력
   '------------------------------
    
   Dim wsDB As Worksheet ' 거래처DB 시트
   Dim wsMng As Worksheet ' 거래내역관리 시트
   Dim lastRow As Long ' 거래처DB 마지막 행
   Dim i As Long ' 반복문용 변수
   Dim 기준열 As Long ' 검색 기준열
   Dim 검색조건 As String ' C2 셀 값
   Dim dict As Object ' Dictionary 객체
   Dim arrDB As Variant ' DB 전체 배열
   Dim arrOut() As Variant ' 출력용 배열
   Dim key As Variant
   Dim resultCnt As Long
    
   '------------------------------
   ' 시트 객체 설정
   '------------------------------
   Set wsDB = ThisWorkbook.Sheets("거래처DB")
   Set wsMng = ThisWorkbook.Sheets("거래내역관리")
    
   '------------------------------
   ' 검색 기준 가져오기
   ' B2: "거래처명" → DB 시트의 2열 검색
   ' B2: "구분" → DB 시트의 3열 검색
   '------------------------------
   Select Case wsMng.Range("B2").Value
   Case "거래처명"
   기준열 = 2
   Case "구분"
   기준열 = 3
   Case Else
   MsgBox "B2 셀에는 '거래처명' 또는 '구분'을 입력해야 합니다.", vbExclamation
   Exit Sub
   End Select
    
   검색조건 = Trim(wsMng.Range("C2").Value)
   If 검색조건 = "" Then
   MsgBox "검색할 조건(C2 셀)이 비어있습니다.", vbExclamation
   Exit Sub
   End If
    
   '------------------------------
   ' 거래처DB 데이터를 배열로 불러오기
   ' A1:F열 (거래처ID~최근거래일)
   '------------------------------
   lastRow = wsDB.Cells(wsDB.Rows.Count, 1).End(xlUp).Row
   arrDB = wsDB.Range("A2:F" & lastRow).Value
    
   '------------------------------
   ' Dictionary 생성 (빠른 검색용)
   ' key: 최근거래일 & 구분자, value: 거래처ID~최근거래일 데이터
   '------------------------------
   Set dict = CreateObject("Scripting.Dictionary")
   resultCnt = 0
    
   For i = 1 To UBound(arrDB, 1)
   If arrDB(i, 기준열) <> "" Then
   If InStr(1, arrDB(i, 기준열), 검색조건, vbTextCompare) > 0 Then
   ' 일치하는 경우 Dictionary에 저장
   resultCnt = resultCnt + 1
   ' Key를 "최근거래일&순번" 으로 설정하여 정렬 시 중복 방지
   dict(arrDB(i, 6) & "_" & resultCnt) = Array(arrDB(i, 1), arrDB(i, 2), arrDB(i, 3), arrDB(i, 6))
   End If
   End If
   Next i
    
   If dict.Count = 0 Then
   MsgBox "조건에 맞는 데이터가 없습니다.", vbInformation
   Exit Sub
   End If
    
   '------------------------------
   ' Dictionary → 배열 변환
   ' 최근거래일 기준 내림차순 정렬
   '------------------------------
   ReDim arrOut(1 To dict.Count, 1 To 4)
    
   Dim arrKeys As Variant
   arrKeys = dict.Keys
    
   ' Key(최근거래일)를 기준으로 정렬 (내림차순)
   Dim j As Long, tmp
   For i = LBound(arrKeys) To UBound(arrKeys) - 1
   For j = i + 1 To UBound(arrKeys)
   If Split(arrKeys(i), "_")(0) < Split(arrKeys(j), "_")(0) Then
   tmp = arrKeys(i)
   arrKeys(i) = arrKeys(j)
   arrKeys(j) = tmp
   End If
   Next j
   Next i
    
   ' 정렬된 Key 순서대로 배열에 담기
   For i = 1 To dict.Count
   tmp = dict(arrKeys(i - 1))
   arrOut(i, 1) = tmp(0) ' 거래처ID
   arrOut(i, 2) = tmp(1) ' 거래처명
   arrOut(i, 3) = tmp(2) ' 구분
   arrOut(i, 4) = tmp(3) ' 최근거래일
   Next i
    
   '------------------------------
   ' 출력 전 기존 데이터 삭제
   '------------------------------
   With wsMng
   .Range("B5:E" & .Rows.Count).ClearContents
   End With
    
   '------------------------------
   ' 결과 배열 출력
   '------------------------------
   wsMng.Range("B5").Resize(dict.Count, 4).Value = arrOut
    
   MsgBox dict.Count & "건의 검색 결과가 출력되었습니다.", vbInformation
    
   End Sub

3. 이제 작성한 코드가 정상적으로 동작하는지 테스트합니다. [거래내역관리] 시트의 C2셀에 "마트"라고 입력한 뒤 매크로를 실행하면 그림과 같이 '구분'에 마트가 포함된 데이터만 필터링되어 출력됩니다. 만약 코드가 정상 동작하지 않는다면, 오류 메시지를 ChatGPT에 그대로 전달해 코드를 수정합니다.
4. 코드가 잘 동작하는 것을 확인했다면, 클릭으로 실행할 수 있는 버튼을 추가합니다. [삽입] - [도형]에서 모서리가 둥근 사각형을 추가한 다음, 채우기 색과 윤곽선을 적절히 변경하고 안에는 '검색'이라는 텍스트를 입력합니다.
5. 추가한 버튼을 우클릭한 뒤 [매크로 지정]으로 이동합니다. 매크로 지정 목록에서 앞서 작성한 매크로를 선택하고 [확인] 버튼을 클릭합니다.
6. 이제 버튼을 클릭하면 실시간으로 필터링되는 보고서가 완성됩니다.

셀 클릭 이벤트 추가하기

이제 엑셀 시트 자동화의 정점이라 할 수 있는 '이벤트 매크로'를 추가해 보겠습니다. 필터링된 거래처 목록에서 거래처ID를 클릭하면, 해당 거래처의 상세 구매내역이 옆에 실시간으로 출력되도록 만들어 보겠습니다.

1. 워드 파일에서 두 번째 항목인 "선택한 거래처의 거래내역 출력하기" 프롬프트를 복사한 뒤, 이전 대화에 이어서 ChatGPT에 붙여넣고 실행합니다.

   == [핵심지침] 시작 ==
   - 당신은 지금부터 엑셀 VBA 매크로 전문가로서, 아래 요청한 작업에 필요한 매크로 코드를 작성합니다.
   - 작성한 VBA 코드를 초보자도 쉽게 이해할 수 있도록, 작성된 코드에는 모든 주요 단계 및 주석을 꼼꼼하게 친절하고 추가하세요.
   - 주석은 코드의 목적, 실행 방법, 작동 원리를 간결하고 명확하게 설명합니다.
   - Option Explicit 선언은 제외합니다.
   - 모든 엑셀 버전에서 문제없이 동작할 수 있도록 Late Binding 방식으로 작성하세요.
   - 외부 라이브러리가 필요한 경우, 참조 설정 없이도 작동할 수 있도록 코드에 포함합니다.
   == [핵심지침] 끝 ==
   == [요청] 시작 ==
   - 이번에 작성하는 매크로는 [거래내역관리] 시트에서 B5:B1000 범위 중 하나의 셀을 선택하면, 해당 셀에 입력된 거래처ID를 [거래처DB]시트와 [구매내역DB] 시트에서 검색한 후, 검색 결과를 [거래내역관리] 시트에 출력하는 동작을 수행합니다.
   - [거래내역관리] 시트 모듈에 추가할 worksheet_selectionchange 이벤트 매크로를 작성하세요.
   - Target 셀이 비어있다면 아무런 동작을 수행하지 않습니다.
   - [거래처DB] 시트에는 A1셀을 시작으로 ‘거래처ID, 거래처명, 구분, 담당자, 연락처, 최근거래일’이 작성되어 있습니다.
   - [구매내역DB] 시트에는 A1셀을 시작으로 ‘구매ID, 날짜, 거래처ID, 제품명, 수량, 금액’이 작성되어 있습니다.
   - 가장 먼저 [거래내역관리] 시트의 H2, J2 셀의 값을 지웁니다.
   - [거래처DB] 시트의 거래처ID 필드에서 선택한 셀의 값을 검색한 후, 검색 결과를 [거래내역관리] 시트에 출력합니다. 담당자는 H2셀, 연락처는 J2셀에 출력하세요.
   - 이후 [구매내역DB] 시트의 데이터를 하나씩 돌아가며, C열에 있는 거래처ID 기준으로 선택한 셀의 값을 검색합니다. 만약 거래처ID의 구매내역이 있을 경우, “구매ID, 날짜, 제품명, 수량, 금액”를 임시 배열에 저장하고, 검색을 모두 마친 후 검색 결과를 한 번에 출력합니다.
   - 검색결과는 [거래내역관리] 시트 G5셀에 출력합니다. G5:K5 를 포함해 그 아래에 기존 데이터가 있다면, G5:K5를 포함한 아래에 작성된 기존 데이터를 지운 후 검색 결과를 출력합니다.
   ==[요청] 끝 ==
   자, 이제 코드 작성을 시작하세요.

2. 그러면 아래 그림과 같이 SelectionChange (셀을 클릭했을 때 동작하는 이벤트 매크로)가 자동으로 작성됩니다. 완성된 코드를 복사한 후, '거래내역관리' 시트 모듈의 아래에 이어서 붙여넣습니다.

   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
   '----------------------------------------
   ' [코드 목적]
   ' [거래내역관리] 시트의 B5:B1000 범위 중 하나를 선택하면
   ' 해당 거래처ID를 기반으로 거래처정보(DB)와 구매내역(DB)을 검색 후 출력
   '----------------------------------------
    
   Dim wsMng As Worksheet ' 거래내역관리 시트
   Dim wsDB As Worksheet ' 거래처DB 시트
   Dim wsBuy As Worksheet ' 구매내역DB 시트
   Dim lastRow As Long
   Dim rng As Range
   Dim selID As String
    
   Dim arrDB As Variant ' 거래처DB 배열
   Dim arrBuy As Variant ' 구매내역DB 배열
   Dim arrOut() As Variant ' 구매내역 출력용 배열
   Dim i As Long, j As Long
   Dim cnt As Long
   Dim tmp
    
   '----------------------------------------
   ' 시트 객체 설정
   '----------------------------------------
   Set wsMng = Me
   Set wsDB = ThisWorkbook.Sheets("거래처DB")
   Set wsBuy = ThisWorkbook.Sheets("구매내역DB")
    
   '----------------------------------------
   ' 선택된 셀이 B5:B1000 범위 안에 있는지 확인
   '----------------------------------------
   Set rng = wsMng.Range("B5:B1000")
   If Intersect(Target, rng) Is Nothing Then Exit Sub
    
   ' 선택된 셀이 여러 개일 경우 첫 번째만 사용
   If Target.CountLarge > 1 Then Exit Sub
    
   ' 선택된 셀이 비어 있으면 동작하지 않음
   If Trim(Target.Value) = "" Then Exit Sub
    
   ' 선택된 거래처ID 저장
   selID = Trim(Target.Value)
    
   '----------------------------------------
   ' [거래내역관리] H2, J2 초기화
   '----------------------------------------
   wsMng.Range("H2").ClearContents
   wsMng.Range("J2").ClearContents
    
   '----------------------------------------
   ' 거래처DB에서 거래처ID 검색 → 담당자(H2), 연락처(J2)에 출력
   '----------------------------------------
   lastRow = wsDB.Cells(wsDB.Rows.Count, 1).End(xlUp).Row
   arrDB = wsDB.Range("A2:F" & lastRow).Value
    
   For i = 1 To UBound(arrDB, 1)
   If arrDB(i, 1) = selID Then
   wsMng.Range("H2").Value = arrDB(i, 4) ' 담당자
   wsMng.Range("J2").Value = arrDB(i, 5) ' 연락처
   Exit For
   End If
   Next i
    
   '----------------------------------------
   ' 구매내역DB에서 거래처ID 검색
   ' C열(3번째)에서 선택된 ID와 같은 행 추출
   '----------------------------------------
   lastRow = wsBuy.Cells(wsBuy.Rows.Count, 1).End(xlUp).Row
   arrBuy = wsBuy.Range("A2:F" & lastRow).Value
    
   cnt = 0
   For i = 1 To UBound(arrBuy, 1)
   If arrBuy(i, 3) = selID Then
   cnt = cnt + 1
   ReDim Preserve arrOut(1 To 5, 1 To cnt)
   arrOut(1, cnt) = arrBuy(i, 1) ' 구매ID
   arrOut(2, cnt) = arrBuy(i, 2) ' 날짜
   arrOut(3, cnt) = arrBuy(i, 4) ' 제품명
   arrOut(4, cnt) = arrBuy(i, 5) ' 수량
   arrOut(5, cnt) = arrBuy(i, 6) ' 금액
   End If
   Next i
    
   '----------------------------------------
   ' 기존 데이터 (G5:K열) 삭제
   '----------------------------------------
   wsMng.Range("G5:K" & wsMng.Rows.Count).ClearContents
    
   '----------------------------------------
   ' 검색 결과 출력 (G5셀부터)
   '----------------------------------------
   If cnt > 0 Then
   ' arrOut을 전치(transpose)하여 행 방향으로 출력
   wsMng.Range("G5").Resize(cnt, 5).Value = Application.Transpose(arrOut)
   End If
    
   End Sub

3. 이제 B열의 거래처ID를 클릭하면 선택한 거래처의 상세 구매내역이 실시간으로 필터링되는 것을 확인할 수 있습니다.
4. 완성된 파일은 [파일] 탭 - [다른 이름으로 저장]에서 파일 형식을 '매크로 포함 통합문서'로 선택해 저장하면, 거래처와 상세 구매내역을 실시간으로 필터링해 주는 자동화 보고서가 완성됩니다.