AI로 엑셀 VBA 자동화하기

1 무엇을 자동화하는지 보기

먼저 우리가 다룰 표를 엽니다. 원문의 샘플 파일 내려받기 (월간_주문내역.xlsx)로 받아 엑셀에서 엽니다.

한 달치 주문 220건이 원본데이터라는 시트 하나에 쌓여 있습니다. 열은 주문일자, 거래처, 담당자, 지역, 품목, 수량, 단가, 금액입니다.

목표는 이것입니다. 거래처별로 시트를 따로 만들고, 각 시트에 소계를 넣고, 맨 앞에 거래처별 총매출 요약과 차트를 만드는 것. 손으로 하면 거래처마다 필터를 걸고, 복사하고, 새 시트에 붙이고, 합계를 넣는 일을 열 번 반복해야 합니다. 이 일을 매크로가 몇 초 만에 합니다.

2 개발 도구 탭 켜기

매크로를 넣고 실행하는 단추들은 개발 도구 탭에 있습니다. 이 탭은 처음에는 리본(위쪽 메뉴 줄)에 없습니다. 한 번만 켜면 됩니다.

리본의 빈 곳에서 마우스 오른쪽 버튼을 누르고, 뜬 메뉴에서 리본 사용자 지정을 고릅니다. 창이 하나 뜹니다. 오른쪽 목록에서 개발 도구 앞의 네모 칸을 눌러 체크합니다. 그리고 아래 확인을 누릅니다.

리본 맨 위 탭 줄에 개발 도구가 새로 생겼습니다. 한 번만 하면 됩니다. 이 탭은 한 번 켜면 다음부터 계속 있습니다. 다른 파일을 열어도 그대로 있습니다.

3 AI에게 매크로 만들어 달라고 하기

이제 코드를 AI에게 받습니다. 여기서 중요한 건 내 표가 어떻게 생겼는지와 무엇을 원하는지를 정확히 말해 주는 것입니다. AI는 내 화면을 못 보므로, 열 이름과 원하는 결과를 글로 알려 줍니다.

ChatGPT를 열고, 아래 문장을 그대로 붙여넣습니다. 내 표의 열이 다르면 열 이름 부분만 내 것으로 바꿉니다.

엑셀 VBA 매크로를 만들어 줘.

지금 "원본데이터"라는 시트에 아래 표가 있어. 1행은 머리글이야.
- A열 주문일자, B열 거래처, C열 담당자, D열 지역, E열 품목, F열 수량, G열 단가, H열 금액
- 데이터는 2행부터 시작하고 220행쯤 돼.

내가 원하는 건 이거야.
1. B열 거래처를 기준으로, 거래처마다 시트를 하나씩 새로 만들어서 그 거래처의 행만 옮겨 줘. 시트 이름은 거래처 이름으로 해 줘.
2. 각 거래처 시트 맨 아래에 "소계" 행을 넣고 H열 금액 합계를 넣어 줘.
3. 맨 앞에 "요약" 시트를 만들어서 거래처별 주문건수와 총매출을 표로 정리하고, 총매출이 큰 순서로 정렬해 줘. 맨 아래 합계 행도 넣어 줘.
4. 요약 시트에 거래처별 총매출 막대 차트도 하나 넣어 줘.
5. 매크로를 여러 번 실행해도 시트가 중복되지 않게, 실행할 때마다 이전에 만든 시트는 지우고 새로 만들어 줘.

엑셀을 처음 다루는 사람도 붙여넣기만 하면 되도록 전체 코드를 한 번에 주고, 실행하는 방법도 알려 줘.

4 AI가 준 코드 복사하기

AI가 검은 상자에 담긴 코드를 줍니다. 상자 오른쪽 위에 복사 버튼이 있습니다. 그 버튼을 누릅니다. 코드를 읽을 필요는 없습니다.

AI마다, 그리고 물을 때마다 코드가 조금씩 다를 수 있습니다. 아래는 원문 가이드에서 실제로 실행해 확인한 코드입니다. AI가 준 것 대신 이걸 그대로 써도 됩니다.

Option Explicit

' 원본데이터 시트를 거래처별 시트로 자동 분리하고, 맨 앞에 요약 시트를 만든다.
' 다시 실행해도 되도록, 실행 때마다 이전에 만든 시트를 지우고 새로 만든다.
Sub 거래처별_분리()
    Dim wsSrc As Worksheet, ws As Worksheet, wsSum As Worksheet
    Dim dict As Object, c As Variant
    Dim lastRow As Long, i As Long, destRow As Long, r As Long
    Dim key As String
    Const COL_CLIENT As Long = 2   ' B열: 거래처
    Const COL_AMOUNT As Long = 8   ' H열: 금액

    Set wsSrc = ThisWorkbook.Worksheets("원본데이터")

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ' 1) 이전에 만든 시트 정리 (원본데이터만 남긴다)
    '    삭제 중 목록이 흔들리지 않도록 뒤에서 앞으로 지운다
    For i = ThisWorkbook.Worksheets.Count To 1 Step -1
        Set ws = ThisWorkbook.Worksheets(i)
        If ws.Name <> wsSrc.Name Then ws.Delete
    Next i

    ' 2) 거래처 목록을 중복 없이 모은다
    Set dict = CreateObject("Scripting.Dictionary")
    lastRow = wsSrc.Cells(wsSrc.Rows.Count, COL_CLIENT).End(xlUp).Row
    For i = 2 To lastRow
        key = Trim(CStr(wsSrc.Cells(i, COL_CLIENT).Value))
        If Len(key) > 0 Then
            If Not dict.Exists(key) Then dict.Add key, 0
        End If
    Next i

    ' 3) 거래처마다 시트를 만들고, 그 거래처 행만 옮긴 뒤 소계를 붙인다
    For Each c In dict.Keys
        Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = Left(CStr(c), 31)
        wsSrc.Range(wsSrc.Cells(1, 1), wsSrc.Cells(1, 8)).Copy ws.Range("A1")   ' 헤더 복사
        destRow = 2
        For i = 2 To lastRow
            If Trim(CStr(wsSrc.Cells(i, COL_CLIENT).Value)) = CStr(c) Then
                wsSrc.Range(wsSrc.Cells(i, 1), wsSrc.Cells(i, 8)).Copy ws.Cells(destRow, 1)
                destRow = destRow + 1
            End If
        Next i
        ws.Cells(destRow, 1).Value = "소계"
        ws.Cells(destRow, COL_AMOUNT).Formula = _
            "=SUM(" & ws.Cells(2, COL_AMOUNT).Address(False, False) & ":" & _
            ws.Cells(destRow - 1, COL_AMOUNT).Address(False, False) & ")"
        With ws.Range(ws.Cells(destRow, 1), ws.Cells(destRow, 8))
            .Font.Bold = True
            .Interior.Color = RGB(255, 242, 204)
        End With
        ws.Cells(destRow, COL_AMOUNT).NumberFormat = "#,##0"
        ws.Columns("A:H").AutoFit
    Next c

    ' 4) 맨 앞에 요약 시트를 만든다
    Set wsSum = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
    wsSum.Name = "요약"
    wsSum.Range("A1:C1").Value = Array("거래처", "주문건수", "총매출")
    r = 2
    For Each c In dict.Keys
        wsSum.Cells(r, 1).Value = CStr(c)
        wsSum.Cells(r, 2).Formula = "=COUNTIF(원본데이터!B:B,A" & r & ")"
        wsSum.Cells(r, 3).Formula = "=SUMIF(원본데이터!B:B,A" & r & ",원본데이터!H:H)"
        r = r + 1
    Next c
    Dim lastSum As Long
    lastSum = r - 1

    ' 정렬 전에 수식 결과를 값으로 굳힌다 (정렬해도 숫자가 흔들리지 않게)
    wsSum.Range("B2:C" & lastSum).Value = wsSum.Range("B2:C" & lastSum).Value

    ' 총매출 내림차순 정렬
    wsSum.Range("A2:C" & lastSum).Sort _
        Key1:=wsSum.Range("C2"), Order1:=xlDescending, Header:=xlNo

    ' 합계 행
    wsSum.Cells(r, 1).Value = "합계"
    wsSum.Cells(r, 2).Formula = "=SUM(B2:B" & lastSum & ")"
    wsSum.Cells(r, 3).Formula = "=SUM(C2:C" & lastSum & ")"

    ' 서식: 헤더 + 숫자 포맷 + 데이터 막대 + 합계 강조
    With wsSum.Range("A1:C1")
        .Interior.Color = RGB(31, 56, 100)
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With
    wsSum.Range("C2:C" & lastSum).FormatConditions.AddDatabar
    wsSum.Range("B2:C" & r).NumberFormat = "#,##0"
    With wsSum.Range("A" & r & ":C" & r)
        .Font.Bold = True
        .Interior.Color = RGB(255, 242, 204)
    End With
    wsSum.Columns("A:C").AutoFit

    ' 5) 요약 시트에 막대 차트 추가
    Dim ch As ChartObject
    Set ch = wsSum.ChartObjects.Add(Left:=260, Top:=10, Width:=380, Height:=260)
    ch.Chart.SetSourceData Source:=wsSum.Range("A2:A" & lastSum & ",C2:C" & lastSum)
    ch.Chart.ChartType = xlBarClustered
    ch.Chart.HasTitle = True
    ch.Chart.ChartTitle.Text = "거래처별 총매출"
    ch.Chart.HasLegend = False

    wsSum.Activate
    wsSum.Range("A1").Select
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    MsgBox dict.Count & "개 거래처 시트와 요약 시트를 만들었습니다.", vbInformation
End Sub

이 코드에서 고칠 곳은 없습니다. 샘플 파일의 시트 이름이 원본데이터, 거래처가 B열, 금액이 H열이라 코드와 맞습니다. 내 표로 할 때 열 위치가 다르면, 위 프롬프트의 열 설명만 내 것으로 바꿔 AI에게 다시 받으면 됩니다.

5 코드 넣을 자리 열기

복사한 코드를 붙여넣을 자리를 엽니다. 엑셀로 돌아와 개발 도구 탭을 누르고, 맨 왼쪽 Visual Basic을 누릅니다. Alt + F11을 눌러도 열립니다.

회색 창이 하나 새로 뜹니다. 이게 코드를 넣는 곳입니다. 위 메뉴에서 삽입을 누르고 모듈을 고릅니다. 모듈은 코드를 담는 빈 종이라고 보면 됩니다.

6 코드 붙여넣기

방금 생긴 흰 빈 칸을 한 번 누르고, Ctrl + V를 눌러 붙여넣습니다. 4단계에서 복사한 코드가 채워집니다. 맨 윗줄에 Sub 거래처별_분리()가 보이면 제대로 들어간 것입니다.

붉은 줄이 보여도 지금은 괜찮습니다. 한글이 깨져 보이거나 한 줄이 붉게 나오면, 붙여넣다 줄이 어긋난 것입니다. Ctrl + Z로 되돌리고, 흰 칸을 비운 뒤 다시 붙여넣으세요.

7 실행하기

코드가 있는 흰 칸을 한 번 누른 채로, 위쪽의 실행 버튼(초록 삼각형 ▶)을 누릅니다. F5를 눌러도 됩니다.

몇 초 안에 다 끝나고, 몇 개를 만들었는지 알리는 작은 창이 뜹니다. 원문 화면 예시는 “10개 거래처 시트와 요약 시트를 만들었습니다.”입니다. 거래처 수는 표에 따라 달라집니다.

8 결과 확인하기

엑셀로 돌아옵니다. 맨 앞에 요약 시트가 생겨 있습니다. 거래처별 총매출이 큰 순서로 정렬되고, 초록 막대(데이터 막대)와 차트까지 붙어 있습니다.

엑셀 화면 아래쪽 시트 탭 줄을 보면, 거래처마다 시트가 하나씩 새로 생겼습니다. 원문 예시 탭 줄은 요약 · 원본데이터 · 대성물산 · 한빛유통 … 입니다.

거래처 시트를 하나 눌러 보면, 그 거래처의 주문만 모여 있고 맨 아래에 소계가 들어 있습니다.

9 매크로 파일로 저장하기

여기서 그냥 저장하면 매크로가 사라집니다. 매크로가 든 파일은 .xlsm이라는 형식으로 따로 저장해야 합니다.

F12(다른 이름으로 저장)를 누릅니다. 파일 이름 아래 파일 형식 목록을 열고, Excel 매크로 사용 통합 문서 (*.xlsm)를 고릅니다. 그리고 저장합니다.