501-3. 엑셀 VBA 자동화 (도서판매)
엑셀 VBA 자동화 (도서판매)
- AI에게 함수를 받아 도서 판매 데이터를 빠르게 가공·집계한다.
- VBA로 데이터 처리를 자동화하고, 채널·부서별 분리와 다중 파일 취합을 수행한다.
- 부서·채널·지역별 집계표와 차트를 PPTX 대시보드로 자동 삽입한다.
AI로 엑셀 다루기 — 함수와 코드
원하는 결과를 설명하면 AI가 함수·VBA 코드를 써 줍니다. 모든 실습은 아래 도서 판매 데이터로 진행합니다. 열 구성은 A 판매일자 · B 주문번호 · C 도서고유번호 · D 도서명 · E 분야 · F 유통채널 · G 판매지역 · H 영업담당부서 · I 판매량 · J 판매단가 · K 할인율 · L 금액(900행)입니다.
엑셀 함수 받기
도서판매 · 도서판매_Data학습 안내읽기 + 따라하기. 엑셀 실습을 시작합니다. 조건에 맞는 데이터가 몇 건인지 세는 함수를 AI에게 받아 봅니다.
"특정 조건의 판매가 몇 건인지"는 가장 자주 쓰는 집계입니다. 한 가지 조건이면 COUNTIF를 AI에게 받습니다.
| 필요한 결과 | 받는 함수(예) |
|---|---|
| F열(유통채널)이 '온라인서점'인 판매 건수 | =COUNTIF(F:F, "온라인서점") |
"온라인서점"). 한 가지 조건만 셀 때 쓰며, 조건이 둘 이상이면 다음 실습의 COUNTIFS를 씁니다.COUNTIFS 함수 받기
실습 · 도서판매_Data학습 안내산출. 사례를 응용해, 두 가지 조건을 동시에 만족하는 데이터 개수를 세는 COUNTIFS 함수를 AI에게 받아 셀에 넣습니다.
| 필요한 결과 | 받는 함수(예) |
|---|---|
| F열 '온라인서점' · G열 '서울' 건수 | =COUNTIFS(F:F, "온라인서점", G:G, "서울") |
실습 순서
- 실습 파일을 열고 빈 셀에 위 함수를 붙여 넣는다.
- 조건 값을 바꿔 가며(예: '서울' 대신 '경기', 채널을 '도매유통'으로) 개수가 맞는지 한두 개 검산한다.
- "조건을 하나 더 추가"해 채널·지역·부서 세 조건 COUNTIFS도 만들어 본다.
VBA 자동화란?
학습 안내읽기. 다음 실습부터 'VBA'로 엑셀을 자동화합니다. VBA가 무엇이고 왜 지금도 쓰는지 짧게 짚고 갑니다.
VBA(Visual Basic for Applications)는 마이크로소프트(Microsoft)가 만든, Office에 내장된 자동화 프로그래밍 언어입니다. 1993년 Excel 5.0에 처음 탑재된 이래 30년 넘게, 엑셀·워드·파워포인트·아웃룩에서 반복 작업을 매크로로 자동화하는 표준 도구로 쓰이고 있습니다.
누가Microsoft가 개발
마이크로소프트가 만들어 Office 제품군에 기본 탑재했습니다. 별도 설치 없이 엑셀 안에서 바로 씁니다.
언제부터1993년 Excel 5.0
1993년 Excel 5.0에 처음 도입되어 30년 넘게 이어진, 검증된 기능입니다. 그 전에는 XLM 매크로를 썼습니다.
무엇을반복 작업 자동화
셀 서식, 파일 분리·취합, 다른 Office 앱 제어까지 — 사람이 반복하던 일을 클릭 한 번으로 처리합니다.
지금은코드는 AI가
예전엔 VBA 문법을 직접 익혀야 했지만, 이제 원하는 동작만 설명하면 AI가 코드를 써 줍니다. 우리는 요구사항만 정확히 주면 됩니다.
조건별 배경색 VBA
사례 · 도서판매_Data학습 안내산출. 조건에 맞는 셀에 자동으로 배경색을 칠하는 VBA를 AI에게 받아 실행합니다. 주목할 판매(온라인·고액 주문)를 한눈에 보이게 만듭니다.
실습 순서
Alt+F11로 VBA 편집기를 열고, 삽입 → 모듈에 받은 코드를 붙여 넣는다.F5또는 개발 도구 → 매크로로 실행한다.- F열·L열에 노란색, 두 조건을 모두 만족한 행의 D열(도서명)에 주황색이 칠해졌는지 확인한다.
- 오류가 나면 메시지를 그대로 복사해 AI에 붙여 "수정된 전체 코드"를 받는다.
VBA 파일 분리
조건에 따라 데이터를 시트·파일로 나눕니다.
파일 분리 — 유통채널 시트
도서판매 · 도서판매_Data학습 안내읽기 + 따라하기. 실습 파일의 통합 데이터를 유통채널(F열: 온라인서점/대형서점/직영몰/도매유통) 기준으로 시트를 나누는 시연입니다. 프롬프트는 엑셀 구조를 길게 설명하지 않고 명령만 간결히 적습니다 — AI가 데이터를 직접 파악하게 합니다.
채널별 실적을 따로 보려면 유통채널 값으로 나눠야 합니다. 손으로 필터·복사 대신 VBA가 F열 값 기준으로 시트를 자동 생성합니다.
파일 분리 직접 해보기
도서판매 · 단계별 도전학습 안내산출. 시연을 출발점으로, 분리 기준을 바꿔 직접 프롬프트를 작성합니다. 완료한 사람이 다음 단계에 도전하세요.
NORMAL부서(H열)별로 별도 엑셀 파일 저장
H열(영업담당부서: 영업1팀/영업2팀/영업3팀) 값 기준으로 별도의 엑셀 파일로 저장한다.
HARD평일과 주말로 분리
A열(판매일자)의 요일을 따져 평일과 주말 데이터를 별도 엑셀 파일로 저장한다. 토·일이면 주말, 그 외는 평일.
VBA 파일 취합
흩어진 여러 파일을 한 번에 모읍니다.
파일 취합 — 3개를 한 시트로
도서판매 · 분리→취합학습 안내읽기 + 따라하기. 분리의 반대입니다. 영업1·2·3팀의 판매 파일 3개를 한 시트로 합치는 시연입니다. 프롬프트는 명령만 간결히 적습니다.
부서별로 따로 받은 파일을 함께 분석하려면 하나로 합쳐야 합니다. 아래 세 파일을 사용합니다.
파일 취합 직접 해보기
도서판매 · 단계별 도전학습 안내산출. 취합 방식을 자동화 수준이 높게 바꿔 갑니다. 사람이 손대는 부분이 줄어드는 것을 체감하십시오. 완료한 사람이 다음 단계에 도전하세요.
NORMAL파일 선택 팝업으로 여러 파일 취합
실행하면 파일 선택창이 떠서 여러 파일을 한꺼번에 선택할 수 있고, 고른 파일들을 한 시트로 취합한다. 사례의 고정 파일명 대신 사용자가 그때그때 고른다.
HARD폴더 선택 창으로 폴더 안의 모든 파일 취합
실행하면 폴더 선택창이 뜨고, 고른 폴더 안의 모든 엑셀(.xlsx)을 자동으로 찾아 취합한다. 매크로 파일 자신은 제외한다.
실습 순서
- NORMAL: "파일 선택창에서 여러 파일을 고르면 모두 한 시트로 합치게 해 줘"로 받아 실행한다.
- HARD: "폴더를 고르면 그 안의 모든 엑셀을 합치게 해 줘"로 바꿔 실행한다.
- 버튼(양식 컨트롤)에 매크로를 연결해 클릭 한 번으로 돌게 만든다.
집계표·대시보드 PPTX 자동화
부서·채널·지역별로 집계와 차트를 만들어 보고용 PPTX 대시보드에 자동으로 넣습니다.
엑셀 → PPTX 집계표
도서판매 · 도서판매_Data학습 안내읽기 + 따라하기. 실습 파일로 부서별 판매 실적 집계표를 만들고, 그 표를 PPTX 1페이지에 자동으로 넣는 시연입니다.
| 영업담당부서 | 판매량(권) | 매출 금액(원) | 평균 할인율 |
|---|---|---|---|
| 영업1팀 | 22,104 | 286,553,332 | 12.0% |
| 영업2팀 | 20,401 | 277,371,562 | 11.5% |
| 영업3팀 | 18,060 | 242,640,876 | 11.9% |
차트로 PPTX 대시보드 만들기
도서판매 · 단계별 도전학습 안내산출. 집계표에서 한 발 더 나아가 여러 차트를 만들어 PPTX 한 페이지에 모은 대시보드를 구성합니다. 완료한 사람이 다음 단계에 도전하세요.
NORMAL채널 점유율 파이 + 부서별 매출 막대 → PPTX 2페이지
유통채널(F열)별 매출(L열) 점유율 파이차트와, 부서(H열)별 매출 합계 막대차트 2개를 만들어 PPTX 2번 슬라이드에 나란히 넣는다.
| 유통채널 | 매출 금액(원) | 점유율 |
|---|---|---|
| 도매유통 | 416,684,584 | 51.7% |
| 온라인서점 | 226,004,375 | 28.0% |
| 대형서점 | 107,854,507 | 13.4% |
| 직영몰 | 56,022,304 | 6.9% |
HARD판매량 히스토그램·지역별 매출 → 한 슬라이드 대시보드, 위치·크기 지정
I열(판매량) 히스토그램과 G열(판매지역)별 L열(금액) 막대차트를 만들어, 위의 두 차트와 함께 한 슬라이드에 배치한다. 각 그림의 위치(Left/Top)와 크기(Width/Height)를 직접 지정해 4분할 대시보드를 만든다.
실습 순서
- NORMAL: "유통채널별 매출 점유율 파이차트와 부서별 매출 막대차트를 만들어 PowerPoint 2번 슬라이드에 넣어 줘"로 받아 실행한다.
- HARD: "판매량 히스토그램과 지역별 매출 막대차트를 추가해, 네 개 차트를 한 슬라이드에 위치·크기를 지정해 배치해 줘"로 받아, 위치·크기 값을 바꿔 가며 대시보드 레이아웃을 조정한다.
- PowerPoint 객체 오류는 "PowerPoint VBA에서 ~"라고 명시해 수정 요청한다.