한국GPT협회 표준교재

502. 엑셀 VBA 자동화 (공사원가)

여러 부서 공사원가 파일을 취합·집계·그래프로 — 코드는 일상 언어로 설명하면 AI가 써 준다

엑셀 VBA 자동화 (공사원가)

Learning Objectives

AI에 시트 구조와 요구사항을 정확히 전달하는 프롬프트 양식을 익힌다.
생성된 VBA 코드를 엑셀에 붙여넣고 실행해 취합·집계·그래프 자동 생성 결과를 확인한다.
오류 메시지 복사 → AI 디버깅 → 재실행 루프로 매크로를 완성한다.

이해

왜 VBA인가

학습 안내읽기. VBA 문법을 배우는 섹션이 아닙니다 — "AI에 무엇을 알려줘야 동작하는 코드가 나오는가"(4요소)를 이해하는 것이 전부입니다. 매크로가 처음이라면 "왜 VBA인가" 부분만 정독하고 넘어가도 됩니다.

매월 반복되는 취합 업무라는 케이스

매월 같은 형식·다른 데이터로 여러 부서 파일이 도착하고, 담당자는 이를 한 시트로 합쳐 집계표와 그래프를 만들어 본사에 보고합니다. 사람이 30~60분에 걸쳐 처리하던 작업을 버튼 한 번으로 끝낼 수 있습니다. Python으로도 가능하지만, 회사 PC에 별도 환경 설치가 어려울 때 엑셀 안에서 끝나는 VBA가 가장 빠른 답입니다. 무엇보다 코드는 일상적인 우리말 설명만으로 AI가 써 줍니다.

단계 1

취합

여러 파일 → 한 시트로

단계 2

집계 + 그래프

분류별 합계와 막대 차트

단계 3

디버깅

오류 복사 → AI 수정 → 재실행

작업을 단계로 나누는 이유. 한 번에 모든 걸 하는 긴 코드는 오류가 났을 때 원인을 찾기 어렵고, 일부만 바꾸려 해도(예: 차트 색상) 영향 범위가 커집니다. 취합 → 집계 → 시각화로 나눠 단계마다 결과를 눈으로 확인하면, 막힌 지점을 빨리 찾고 다음 사람에게 넘기기도 쉽습니다. 이 장의 사례·실습도 이 흐름을 따라 진행합니다.

자동화 요청 프롬프트의 4요소

"매크로 만들어줘"라고 짧게 던지면 결과는 거의 실패합니다. 시트 구조·범위·요구사항·예외 처리를 분명히 적어야 코드가 한 번에 동작합니다. 어려운 코드 용어가 아니라 "무엇을 어떻게"를 일상 언어로 또박또박 적는 것이 핵심입니다.

① 역할

"너는 엑셀 자동화에 능숙한 도우미다" — 코드 품질과 주석 스타일이 달라집니다.

② 시트 구조

머리글 행 위치, 데이터 시작 행, 칼럼 구성, 파일 개수. AI는 내 화면을 못 봅니다 — 구조를 글로 옮겨야 합니다.

③ 요구사항

처리 순서를 번호로. "첫 파일은 머리글 포함, 두 번째부터는 데이터만 이어 붙이기"처럼 경계 조건까지.

④ 예외 처리

빈 칸·기존 시트 존재·데이터 없음 등 실패 상황의 처리 방법을 미리 지정.

사례

파일 취합 자동화

건설

학습 안내직접 실행. 먼저 가장 단순한 형태 — 같은 폴더에 있는 파일 두 개를 이름으로 지정해 합치는 것부터 봅니다. 코드를 해석하려 하지 말고, 우리말 요구사항이 어떻게 코드로 바뀌는지만 확인하면 됩니다.

매크로가 든 파일과 같은 폴더에 부서 파일을 두고, 파일 이름을 직접 적어 두 개를 합쳐 봅니다.

건축공사팀.xlsx 토목공사팀.xlsx 공사원가-취합.xlsm (실행 파일)

두 파일 취합

엑셀 VBA 작성해줘. 이 파일과 같은 폴더에 있는 '건축공사팀.xlsx'와 '토목공사팀.xlsx' 두 파일을 연다. '취합원가표' 시트를 새로 만들어 두 파일의 데이터를 차례로 합친다. 각 파일은 1행이 머리글, 2행부터 데이터이고, 칼럼 구성은 두 파일이 같다. 첫 파일은 머리글을 포함하고, 두 번째 파일은 머리글을 빼고 데이터만 이어 붙인다. '취합원가표' 시트가 이미 있으면 확인 없이 지우고 다시 만든다. 머리글 행은 굵게, 연한 파랑 배경으로 강조한다. 끝나면 모든 열 너비를 자동으로 맞춘다.

관찰 포인트. "첫 파일은 머리글 포함, 두 번째부터는 데이터만" 같은 경계 조건이 빠지면 머리글이 여러 번 반복되는 표가 만들어집니다. 좋은 결과는 좋은 코드가 아니라 좋은 요구사항 설명에서 나옵니다. 파일이 늘어나면 이름을 일일이 적기 번거롭죠 — 다음 실습에서 폴더째 자동으로 읽는 방법으로 넓힙니다.

실습

취합원가표 만들기

건설

학습 안내산출. 이번에는 파일 이름을 적지 않습니다. 5개 부서 파일을 '취합대상' 폴더에 모아 두고, 매크로가 폴더를 고르게 한 뒤 그 안의 파일을 전부 자동으로 합치게 합니다. 완료 기준: '취합원가표' 시트에 5개 부서 데이터가 머리글 1행 + 1,000여 행으로 쌓이면 통과.

먼저 폴더를 이렇게 준비합니다

매크로가 든 실행 파일과 같은 위치에 취합대상 폴더를 만들고, 5개 부서 파일을 그 안에 넣습니다. 매크로는 실행될 때 "어느 폴더를 합칠까요?"를 물어보고, 우리가 이 취합대상 폴더를 골라 주면 됩니다.

폴더 구조

📁 공사원가-자동화\            ← 작업 폴더(아무 데나 만들어도 됨)
├── 📄 공사원가-취합.xlsm      ← 매크로 실행 파일 (여기서 매크로 실행)
└── 📁 취합대상\               ← 5개 부서 파일을 여기에 모아 둔다
    ├── 📄 건축공사팀.xlsx
    ├── 📄 토목공사팀.xlsx
    ├── 📄 기계설비팀.xlsx
    ├── 📄 전기통신팀.xlsx
    └── 📄 조경마감팀.xlsx

건축공사팀.xlsx 토목공사팀.xlsx 기계설비팀.xlsx 전기통신팀.xlsx 조경마감팀.xlsx 공사원가-취합.xlsm (실행 파일)

달성 목표

취합대상 폴더를 고르면 그 안의 파일이 모두 '취합원가표' 시트 하나로 합쳐지는 것을 확인한다. 사람의 손은 "폴더 선택" 한 번뿐이어야 한다.

폴더째 취합

엑셀 VBA 작성해줘. - 실행하면 폴더 선택 창을 띄워 사용자가 폴더 하나를 고르게 한다. - 고른 폴더 안에 있는 모든 엑셀 파일을 자동으로 찾는다. - '취합원가표' 시트를 새로 만들어 찾은 파일들의 데이터를 차례로 합친다. - 각 파일은 1행이 머리글, 2행부터 데이터이고, 칼럼 구성은 모두 같다. - 첫 파일만 머리글을 포함하고, 나머지 파일은 데이터만 이어 붙인다. - '취합원가표' 시트가 이미 있으면 확인 없이 지우고 다시 만든다. - 머리글 행은 굵게, 연한 파랑 배경으로 강조한다. - 끝나면 모든 열 너비를 자동으로 맞추고, 합쳐진 총 행수를 알려 준다.

실습 순서

작업 폴더에 취합대상 폴더를 만들고, 5개 부서 파일을 그 안에 넣는다.
공사원가-취합.xlsm을 같은 작업 폴더에 두고 연다. (매크로 차단 시: 파일 우클릭 → 속성 → 차단 해제)
Alt+F11로 VBA 편집기를 열고 모듈을 새로 추가한다.
위 프롬프트로 받은 코드를 붙여넣고 실행한다(F5).
폴더 선택 창에서 취합대상 폴더를 고르고, 결과 시트를 확인한다.

비교 자료. 완성된 취합 매크로 예시가 취합 매크로(.bas)로 제공됩니다. 내가 받은 코드와 비교하거나, 막혔을 때 가져오기(VBA 편집기 → 파일 → 파일 가져오기)로 사용하십시오. AI는 매번 조금씩 다른 코드를 생성합니다 — 다른 것이 정상입니다.

사례

집계·그래프 자동화

건설

학습 안내직접 실행. 앞과 같은 방식입니다 — 프롬프트 두 개를 차례로 실행해 코드를 받아 두십시오. 두 번째(집계) 프롬프트의 "빈 칸 → 미분류" 같은 예외 처리가 4요소 중 어디에 해당하는지 짚어 보면 복습이 됩니다.

취합원가표가 만들어졌으니, 이제 보고에 쓸 집계표와 차트를 만듭니다. 앞 작업의 출력 시트가 다음 작업의 입력이 되는 연결 구조에 주목하십시오.

분류별 집계표 만들기

엑셀 VBA 작성해줘. '취합원가표' 시트의 데이터로 '집계자동화' 시트를 새로 만든다. 다음 세 가지 합계표를 차례로 만든다. 1) 대분류별 금액 합계 (예: 건축, 토목, 기계, 전기, 조경) 2) 중분류별 금액 합계 3) 공급업체별 금액 합계 각 표 위에 굵은 머리글 한 줄을 넣고, 표와 표 사이는 빈 줄 한 줄로 띄운다. 금액은 천 단위 콤마로 표시한다. 분류가 빈 칸이면 '(미분류)'로 묶는다. 데이터가 없으면 안내 메시지를 띄우고 멈춘다.

막대 차트 만들기

엑셀 VBA 작성해줘. '집계자동화' 시트의 '대분류별 금액 합계'로 세로 막대 차트를 만든다. '그래프자동화' 시트를 새로 만들어 그 위에 차트를 놓는다. 차트 제목은 '대분류별 공사 원가 합계'. 가로축은 대분류 이름, 세로축은 금액(천 단위 콤마)으로 한다. 막대는 진한 파랑으로 하고, 막대 위에 금액을 표시한다.

실습

집계표·차트 실행

건설

학습 안내산출. 집계 → 그래프 순서로 실행합니다(그래프는 집계가 만든 시트를 입력으로 씁니다). 완료 기준: 시트 3개(취합원가표·집계자동화·그래프자동화)가 모두 생성되고 차트가 보이면 통과.

달성 목표

세 작업(취합·집계·그래프)을 모두 실행해 시트 3개가 자동 생성된 통합 파일을 완성한다. 매월 이 파일에서 버튼 두 번이면 본사 보고 자료가 끝나는 상태가 목표다.

세 작업이 모두 성공하면 결과가 아래 모습이 됩니다.

최종 결과물 — 공사원가-취합.xlsm 시트 구성

시트	생성 단계	내용
취합원가표	취합	여러 부서 데이터가 머리글 1행 + 1,000여 행으로 누적된 단일 표
집계자동화	집계	대분류 / 중분류 / 공급업체별 합계 3블록 — 매월 본사 보고의 핵심 표
그래프자동화	그래프	대분류별 금액 막대그래프 1개

그래프자동화 시트 미리보기

건축

토목

기계

전기

조경

건축공사토목공사기계설비전기통신조경마감

실습 순서

VBA 편집기에 모듈 두 개를 추가하고, 집계·그래프 코드를 각각 붙여넣는다.
집계 매크로를 실행해 "집계자동화" 시트의 3개 블록을 확인한다.
그래프 매크로를 실행해 "그래프자동화" 시트의 막대 차트를 확인한다.
차트 색을 회사 색으로 바꾸고 싶다면 — 그래프 프롬프트에서 "막대는 ○○색으로"만 고쳐 다시 요청해 본다.

비교 자료. 완성 매크로 예시 집계 매크로(.bas) · 그래프 매크로(.bas)가 제공됩니다.

사례

에러 디버깅 대화법

학습 안내읽기. 디버깅 양식의 5개 항목(상황·오류 메시지·발생 위치·환경·요청)을 기억해 두십시오. 바로 아래 실습에서 직접 쓰게 됩니다.

매크로는 한 번에 동작하지 않는 경우가 많습니다. 흔한 실패는 (1) 시트명 오타, (2) 빈 칸으로 인한 오류, (3) 보호된 시트 접근 거부, (4) 차트 위치 충돌. 오류가 나면 메시지 전체를 그대로 복사해 AI에 붙여넣는 것이 가장 빠릅니다.

에러 디버깅 — 프롬프트 양식

# 상황 앞서 만든 취합 매크로를 실행하니 다음 오류가 발생함. # 오류 메시지 (엑셀이 보여준 오류 메시지를 그대로 붙여넣기) # 발생 위치 - 어떤 동작을 할 때 멈췄는지 (예: 두 번째 파일을 합치려는 순간) # 환경 - 엑셀 365, 통합 파일은 공사원가-취합.xlsm, 합치려는 파일은 부서별 엑셀 # 요청 1) 가장 가능성 높은 원인 3가지 2) 각각의 수정 방법 3) 수정된 전체 코드 (바뀐 부분에 표시)

주의. 디버깅 과정에서 실제 부서 파일을 그대로 첨부하지 않습니다. 공급업체명·단가가 노출되기 때문입니다. 가상 데이터로 같은 칼럼 구조의 샘플 파일을 만들어 디버깅합니다.

실습

디버깅 루프 실습

학습 안내산출. 오류가 나는 것이 정상 과정입니다 — 오류 복사 → 디버깅 양식 → 재실행 루프를 직접 한 바퀴 도는 것이 이 실습의 전부이자 핵심입니다. 완료 기준: 고장 낸 매크로가 다시 정상 동작하면 통과.

달성 목표

실무에서 매크로가 멈췄을 때 당황하지 않도록, 오류 발생 → 양식대로 질문 → 수정 코드 적용 → 재실행의 디버깅 루프를 통제된 환경에서 미리 경험한다.

실습 순서

집계 코드에서 시트명 "취합원가표"를 일부러 "취합원가"로 고쳐 오류를 만든다.
실행해 나타난 오류 메시지 전체를 복사한다.
위 디버깅 양식 5개 항목을 채워 AI에 질문한다.
AI가 짚은 원인 후보 중 진짜 원인(시트명 불일치)을 찾고, 수정 코드로 교체해 재실행한다.
(시간이 남으면) 다른 고장도 시도: 데이터 일부를 지우고 빈 칸 오류를 만들어 같은 루프를 반복한다.

이 루프가 곧 실무 역량입니다. AI 시대의 VBA 활용 능력은 코드를 읽는 능력이 아니라, 오류 상황을 정확히 설명하는 능력입니다. 양식의 5개 항목이 그 설명의 표준입니다.

실습

부록 · 더 해보기

학습 안내선택 · 부록. 학습이 빠른 분을 위한 추가 미션입니다. 필수가 아니며, 관심 가는 것 1개만 골라 진행해도 충분합니다.

MISSION 1역방향 — 취합본을 부서별 파일로 분리

취합원가표를 입력으로, 대분류별로 별도 엑셀 파일을 만들어 저장하는 매크로를 AI로 작성해 보십시오. 프롬프트에 ① 분리 기준 칼럼 ② 파일 이름 규칙(예: 공사원가_건축.xlsx) ③ 저장 폴더를 우리말로 분명히 적어야 합니다.

MISSION 2집계 결과를 부서 담당자에게 메일 발송

Outlook이 설치된 환경이라면, 집계자동화 시트의 요약을 본문에 담아 부서별 담당자에게 이메일을 자동 발송하는 자동화까지 확장해 보십시오. 수신자 명단 시트(부서·이름·메일주소)를 추가하고, "시트의 명단을 읽어 한 명씩 개인화된 메일을 보내 줘"라고 우리말로 요청합니다. 보안 원칙: 비밀번호 같은 민감 정보를 코드에 직접 적지 않습니다.

MISSION 3같은 작업을 Python으로

동일한 취합·집계를 ChatGPT의 데이터 분석 기능에 파일을 첨부해 수행시켜 보고, VBA 방식과 비교해 보십시오. 어떤 상황에서 어느 쪽이 유리한지(설치 환경·파일 크기·반복 주기) 한 표로 정리하면 완성입니다.