시작하기 전에 준비할 것
- 1Windows용 Excel Microsoft 365 또는 2016 이상이면 됩니다. 화면 모양이 조금 달라도 순서는 같습니다.
- 2샘플 파일 아래에서 내려받습니다. 내 표로 바로 해 봐도 됩니다.
- 3AI 챗봇 하나 이 가이드는 ChatGPT 화면으로 보여 줍니다. 무료 계정으로 됩니다.
- 4코드 지식은 필요 없습니다 코드는 AI가 만들고, 우리는 붙여넣고 실행만 합니다.
무엇을 자동화하는지 보기
먼저 우리가 다룰 표를 엽니다. 아래 버튼으로 샘플 파일을 내려받아 엑셀에서 엽니다.
한 달치 주문 220건이 원본데이터라는 시트 하나에 쌓여 있습니다. 열은 주문일자, 거래처, 담당자, 지역, 품목, 수량, 단가, 금액입니다.
| 행 | 주문일자 | 거래처 | 담당자 | 지역 | 품목 | 수량 | 단가 | 금액 |
|---|---|---|---|---|---|---|---|---|
| 2 | 2026-07-01 | 대성물산 | 김민수 | 서울 | 잉크토너 | 42 | 48,000 | 2,016,000 |
| 3 | 2026-07-01 | 한빛유통 | 이서연 | 경기 | 사무용지 A4 | 120 | 3,200 | 384,000 |
| 4 | 2026-07-02 | 동방무역 | 박준호 | 부산 | 화이트보드 | 15 | 62,000 | 930,000 |
| … | … | … | … | … | … | … | … | … |
| 221 | 2026-07-31 | 태평양물류 | 정하늘 | 인천 | USB메모리 | 80 | 12,000 | 960,000 |
목표는 이것입니다. 거래처별로 시트를 따로 만들고, 각 시트에 소계를 넣고, 맨 앞에 거래처별 총매출 요약과 차트를 만드는 것. 손으로 하면 거래처마다 필터를 걸고, 복사하고, 새 시트에 붙이고, 합계를 넣는 일을 열 번 반복해야 합니다. 이 일을 매크로가 몇 초 만에 합니다.
엑셀에 원본데이터 시트가 열려 있고, 표가 220행 정도 채워져 있습니다.
개발 도구 탭 켜기
매크로를 넣고 실행하는 단추들은 개발 도구 탭에 있습니다. 이 탭은 처음에는 리본(위쪽 메뉴 줄)에 없습니다. 한 번만 켜면 됩니다.
리본의 빈 곳에서 마우스 오른쪽 버튼을 누르고, 뜬 메뉴에서 리본 사용자 지정을 고릅니다.
창이 하나 뜹니다. 오른쪽 목록에서 개발 도구 앞의 네모 칸을 눌러 체크합니다. 그리고 아래 확인을 누릅니다.
리본 맨 위 탭 줄에 개발 도구가 새로 생겼습니다.
리본 위쪽 탭 줄에 개발 도구가 보입니다.
AI에게 매크로 만들어 달라고 하기
이제 코드를 AI에게 받습니다. 여기서 중요한 건 내 표가 어떻게 생겼는지와 무엇을 원하는지를 정확히 말해 주는 것입니다. AI는 내 화면을 못 보므로, 열 이름과 원하는 결과를 글로 알려 줍니다.
ChatGPT를 열고, 아래 문장을 그대로 붙여넣습니다. 내 표의 열이 다르면 열 이름 부분만 내 것으로 바꿉니다.
엑셀 VBA 매크로를 만들어 줘.
지금 "원본데이터"라는 시트에 아래 표가 있어. 1행은 머리글이야.
- A열 주문일자, B열 거래처, C열 담당자, D열 지역, E열 품목, F열 수량, G열 단가, H열 금액
- 데이터는 2행부터 시작하고 220행쯤 돼.
내가 원하는 건 이거야.
1. B열 거래처를 기준으로, 거래처마다 시트를 하나씩 새로 만들어서 그 거래처의 행만 옮겨 줘. 시트 이름은 거래처 이름으로 해 줘.
2. 각 거래처 시트 맨 아래에 "소계" 행을 넣고 H열 금액 합계를 넣어 줘.
3. 맨 앞에 "요약" 시트를 만들어서 거래처별 주문건수와 총매출을 표로 정리하고, 총매출이 큰 순서로 정렬해 줘. 맨 아래 합계 행도 넣어 줘.
4. 요약 시트에 거래처별 총매출 막대 차트도 하나 넣어 줘.
5. 매크로를 여러 번 실행해도 시트가 중복되지 않게, 실행할 때마다 이전에 만든 시트는 지우고 새로 만들어 줘.
엑셀을 처음 다루는 사람도 붙여넣기만 하면 되도록 전체 코드를 한 번에 주고, 실행하는 방법도 알려 줘.
프롬프트를 보냈고, AI가 답을 쓰기 시작했습니다.
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에게 다시 받으면 됩니다.코드가 클립보드에 복사됐습니다(복사 버튼이 "복사됨"으로 바뀝니다).
코드 넣을 자리 열기
복사한 코드를 붙여넣을 자리를 엽니다. 엑셀로 돌아와 개발 도구 탭을 누르고, 맨 왼쪽 Visual Basic을 누릅니다.
회색 창이 하나 새로 뜹니다. 이게 코드를 넣는 곳입니다. 위 메뉴에서 삽입을 누르고 모듈을 고릅니다.
회색 창(Visual Basic 편집기)이 떴고, 오른쪽에 흰 빈 칸(모듈)이 생겼습니다.
코드 붙여넣기
방금 생긴 흰 빈 칸을 한 번 누르고, Ctrl + V를 눌러 붙여넣습니다. 4단계에서 복사한 코드가 채워집니다.
Sub 거래처별_분리()가 보이면 제대로 들어간 것입니다.흰 칸에 코드가 가득 차 있고, 맨 위에 Sub 거래처별_분리()가 보입니다.
실행하기
코드가 있는 흰 칸을 한 번 누른 채로, 위쪽의 실행 버튼(초록 삼각형 ▶)을 누릅니다. F5를 눌러도 됩니다.
몇 초 안에 다 끝나고, 몇 개를 만들었는지 알리는 작은 창이 뜹니다.
"만들었습니다" 창이 떴고, 확인을 눌렀습니다.
결과 확인하기
엑셀로 돌아옵니다. 맨 앞에 요약 시트가 생겨 있습니다. 거래처별 총매출이 큰 순서로 정렬되고, 초록 막대(데이터 막대)와 차트까지 붙어 있습니다.
엑셀 화면 아래쪽 시트 탭 줄을 보면, 거래처마다 시트가 하나씩 새로 생겼습니다.
거래처 시트를 하나 눌러 보면, 그 거래처의 주문만 모여 있고 맨 아래에 소계가 들어 있습니다.
요약 시트와 거래처별 시트가 생겼고, 거래처 시트 아래에 소계가 있습니다.
매크로 파일로 저장하기
여기서 그냥 저장하면 매크로가 사라집니다. 매크로가 든 파일은 .xlsm이라는 형식으로 따로 저장해야 합니다.
F12(다른 이름으로 저장)를 누릅니다. 파일 이름 아래 파일 형식 목록을 열고, Excel 매크로 사용 통합 문서 (*.xlsm)를 고릅니다. 그리고 저장합니다.
파일 이름 끝이 .xlsm으로 저장됐습니다.
자주 막히는 곳
실행했더니 노란 줄이 뜨고 "디버그" 버튼이 나와요
대개 시트 이름이 코드와 다릅니다. 코드는 원본데이터라는 시트를 찾습니다. 내 시트 이름이 Sheet1이거나 다른 이름이면 못 찾습니다. 시트 탭을 더블클릭해 이름을 원본데이터로 바꾸고 다시 실행하세요. 뜬 창은 종료를 누르면 됩니다.
붙여넣은 코드에 한글이 깨져 보여요
웹에서 코드를 복사할 때 가끔 생깁니다. 이 페이지 4단계의 복사 버튼으로 다시 복사해 붙여넣으세요. 흰 칸을 Ctrl + A로 전부 지운 뒤 붙여넣습니다.
웹이나 메일로 받은 파일인데 매크로가 안 돌아가요
인터넷이나 메일로 받은 파일은 엑셀이 안전을 위해 매크로를 막아 둡니다. 두 가지 중 하나로 풉니다.
먼저 노란 띠부터 봅니다. 파일을 열었을 때 위쪽에 노란 보안 경고 띠와 콘텐츠 사용 단추가 보이면, 그 콘텐츠 사용을 한 번 누르면 됩니다. 이 파일에서 한 번만 하면 다음부터 안 묻습니다.
노란 띠가 없거나 그래도 막히면 파일 차단을 해제합니다.
- 엑셀에서 그 파일을 닫습니다.
- 탐색기에서 그 파일 아이콘을 오른쪽 클릭하고 속성을 엽니다.
- 맨 아래 보안 줄에 "이 파일은 다른 컴퓨터에서 왔으며..."라는 문구가 있으면, 그 옆 차단 해제에 체크합니다.
- 확인을 누르고 파일을 다시 엽니다.
이 보안 줄과 차단 해제 항목은 인터넷에서 온 파일에만 나타납니다. 내가 직접 만든 파일에는 아예 없습니다.
한 번 더 실행했더니 시트가 두 배로 생기지 않나요?
안 생깁니다. 이 코드는 실행할 때마다 이전에 만든 시트를 먼저 지우고 새로 만듭니다. 그래서 다음 달 데이터로 바꿔 여러 번 실행해도 깔끔합니다.
내 표는 열이 달라요. 그대로 써도 되나요?
3단계 프롬프트의 열 설명(어느 열이 거래처인지, 어느 열이 금액인지)만 내 표에 맞게 바꿔 AI에게 다시 물으세요. 나머지 과정은 똑같습니다. 기준이 거래처가 아니라 담당자나 지역이어도, 그 열을 기준으로 나눠 달라고만 바꾸면 됩니다.
맥에서 한다면
순서와 결과는 같고 화면만 다릅니다. 개발 도구 탭은 Excel > 기본 설정 > 리본 및 도구 모음에서 켭니다. Visual Basic 편집기는 Option + F11로 엽니다. 데이터 막대나 차트 위치가 조금 다르게 그려질 수 있지만, 거래처별 분리와 소계, 요약은 그대로 만들어집니다.
더 해 보고 싶다면
매번 Visual Basic을 열지 않고, 시트 위 버튼 하나로 실행하고 싶다면, 개발 도구 탭의 삽입 > 단추로 버튼을 그리고 이 매크로를 연결하면 됩니다. 그러면 다음 달부터는 버튼 한 번이 끝입니다.