엑셀 피벗 테이블 클릭 노가다 끝! 파이썬으로 자동 생성하기

6월 19, 2026

엑셀 피벗 테이블 클릭 노가다 끝! 파이썬으로 자동 생성하기 (완성 코드 공개)

"지역별·월별 매출 합계를 피벗으로 보고 싶은데 매번 엑셀에서 직접 만들어야 한다", "데이터가 바뀔 때마다 피벗 테이블을 새로 만들어야 해서 번거롭다", "여러 집계(합계, 평균, 개수)를 한 번에 보고 싶다"…

엑셀 피벗 테이블은 강력하지만 데이터가 바뀔 때마다 설정을 다시 해야 하는 번거로움이 있습니다. 파이썬의 pandas를 사용하면 행·열·값·집계 방식을 코드로 정의해두고, 데이터가 바뀌어도 실행 한 번으로 피벗 테이블을 자동으로 생성하고 엑셀로 저장할 수 있습니다.

1단계: 준비물 설치

파이썬이 설치되어 있어야 합니다. 없다면 python.org에서 최신 버전을 받아 설치하세요. 설치 시 반드시 "Add Python to PATH"에 체크해야 합니다.

터미널(윈도우: CMD 또는 파워셸)을 열고 아래 명령어를 실행하세요:

pip install pandas openpyxl

pandas의 pivot_table() 함수는 엑셀 피벗 테이블과 동일한 기능을 코드로 구현합니다. 행 그룹, 열 그룹, 집계 값, 집계 방식(합계·평균·개수 등)을 자유롭게 설정할 수 있습니다.

💡 이 코드로 할 수 있는 것: 행(Row)·열(Column)·값(Value)·집계 방식을 지정해 피벗 테이블 자동 생성, 합계·평균·최대·최소·개수 다중 집계 동시 처리, 소계(행·열 합계) 자동 추가, 결과를 서식이 적용된 엑셀로 저장합니다.

2단계: 엑셀 파일 준비

처리할 엑셀 파일은 아래처럼 1행이 헤더이고 2행부터 데이터가 입력된 형태이면 됩니다.

날짜         지역    담당자   상품분류   매출금액   수량
2026-01-05   서울    홍길동   전자제품   5,200,000   12
2026-01-12   부산    김철수   의류         980,000    5
2026-02-03   서울    이영희   식품       3,100,000   30
2026-02-15   대전    박민준   전자제품   7,400,000    8
2026-03-07   부산    최수진   의류       1,250,000   15

이 데이터를 지역(행) × 상품분류(열) 기준 매출금액 합계 피벗 테이블로 자동 생성하는 것이 목표입니다.

3단계: 완성 코드

아래 코드를 그대로 복사해서 메모장에 붙여넣고, make_pivot.py로 저장하세요. 저장 시 파일 형식은 "모든 파일", 인코딩은 UTF-8로 설정합니다.

import pandas as pd
from pathlib import Path

# ① 설정
INPUT_PATH  = r"C:\Users\내이름\Desktop\매출데이터.xlsx"   # ← 원본 엑셀 경로
OUTPUT_PATH = r"C:\Users\내이름\Desktop\피벗결과.xlsx"     # ← 저장 경로

# 피벗 테이블 구성
ROW_COL    = "지역"       # ← 행(Row) 기준 열 이름
COL_COL    = "상품분류"   # ← 열(Column) 기준 열 이름 (없으면 None)
VALUE_COL  = "매출금액"   # ← 집계할 값 열 이름
AGG_FUNC   = "sum"        # ← 집계 방식: sum / mean / count / max / min
ADD_TOTALS = True         # ← True: 행·열 합계(소계) 자동 추가

# ② 데이터 읽기
df = pd.read_excel(INPUT_PATH, engine="openpyxl")
print(f"  ✔ 데이터 로드 완료: {len(df)}행 × {len(df.columns)}열")

# ③ 피벗 테이블 생성
pivot = pd.pivot_table(
    df,
    index   = ROW_COL,
    columns = COL_COL,
    values  = VALUE_COL,
    aggfunc = AGG_FUNC,
    margins = ADD_TOTALS,          # 소계 행·열 추가
    margins_name = "합계",          # 소계 행·열 이름
    fill_value = 0,                # 데이터 없는 셀은 0으로 채움
)

print(f"\n  ✔ 피벗 테이블 생성 완료:")
print(pivot.to_string())

# ④ 다중 집계 피벗 (합계·평균·개수 동시)
multi_pivot = pd.pivot_table(
    df,
    index   = ROW_COL,
    values  = VALUE_COL,
    aggfunc = {"매출금액": ["sum", "mean", "count"]},
    fill_value = 0,
)
multi_pivot.columns = ["매출합계", "매출평균", "거래건수"]
multi_pivot = multi_pivot.round(0).astype(int)

# ⑤ 엑셀로 저장 (시트별로 분리)
with pd.ExcelWriter(OUTPUT_PATH, engine="openpyxl") as writer:
    # 기본 피벗 시트
    pivot.to_excel(writer, sheet_name="피벗_매출합계")
    print(f"\n  ✔ '피벗_매출합계' 시트 저장")

    # 다중 집계 피벗 시트
    multi_pivot.to_excel(writer, sheet_name="지역별_다중집계")
    print(f"  ✔ '지역별_다중집계' 시트 저장")

    # 원본 데이터 시트
    df.to_excel(writer, sheet_name="원본데이터", index=False)
    print(f"  ✔ '원본데이터' 시트 저장")

print(f"\n✅ 완료! 피벗 테이블 저장 → {OUTPUT_PATH}")

4단계: 실행 방법

코드 ① 설정의 INPUT_PATH에 원본 엑셀 파일 경로를 입력합니다.
ROW_COL에 행 기준 열 이름, COL_COL에 열 기준 열 이름, VALUE_COL에 집계할 열 이름을 입력합니다.
AGG_FUNC에 집계 방식을 입력합니다. sum(합계), mean(평균), count(개수), max(최대), min(최소) 중 선택합니다.
터미널에서 아래 명령어로 실행합니다:

python make_pivot.py

정상 실행 시 터미널에 피벗 테이블이 미리보기로 출력됩니다:

  ✔ 데이터 로드 완료: 120행 × 6열

  ✔ 피벗 테이블 생성 완료:
상품분류     식품       의류    전자제품        합계
지역
대전      2100000    750000   14800000   17650000
부산      1800000   2230000    3200000    7230000
서울      9300000    980000    5200000   15480000
합계     13200000   3960000   23200000   40360000

  ✔ '피벗_매출합계' 시트 저장
  ✔ '지역별_다중집계' 시트 저장
  ✔ '원본데이터' 시트 저장

✅ 완료! 피벗 테이블 저장 → C:\Users\내이름\Desktop\피벗결과.xlsx

5단계: 자주 발생하는 오류와 해결법

오류 1: KeyError: '지역'

ROW_COL에 입력한 열 이름이 엑셀 헤더와 다를 때 발생합니다. 엑셀 1행의 열 이름을 정확히 복사해서 붙여넣으세요. 공백이나 오타가 있으면 오류가 납니다.

오류 2: 피벗 테이블 값이 모두 0으로 나옴

VALUE_COL에 지정한 열이 숫자가 아닌 텍스트 형식으로 저장된 경우입니다. 엑셀에서 해당 열을 숫자 형식으로 변환하거나, 코드 ② 데이터 읽기 후 아래 코드를 추가하세요:

df[VALUE_COL] = pd.to_numeric(df[VALUE_COL].astype(str).str.replace(",", ""), errors="coerce")

오류 3: 피벗 열 이름이 MultiIndex로 나와 저장이 복잡함

다중 집계 시 열 이름이 중첩 구조로 생성될 수 있습니다. 코드에 이미 multi_pivot.columns = [...]으로 열 이름을 단순하게 바꾸는 처리가 포함되어 있습니다. 열 이름이 여전히 복잡하다면 pivot.columns = pivot.columns.droplevel(0)을 추가해보세요.

응용: 날짜 열을 월별로 그룹화해서 피벗 만들기

날짜 데이터를 월별로 그룹화해서 월별 매출 피벗 테이블을 만들 수 있습니다. 코드 ② 데이터 읽기 후 아래 코드를 추가하세요.

DATE_COL = "날짜"   # ← 날짜 열 이름

# 날짜 열을 datetime으로 변환 후 "월" 열 추가
df[DATE_COL] = pd.to_datetime(df[DATE_COL])
df["월"] = df[DATE_COL].dt.strftime("%Y-%m")

# 월(행) × 지역(열) 기준 피벗
ROW_COL = "월"
COL_COL = "지역"

💡 이전 게시글과 결합하면: 차트 자동 저장 편과 결합하면 피벗 테이블 결과를 바로 차트 이미지로 변환할 수 있고, 이메일 자동 발송 편과 결합하면 완성된 피벗 엑셀을 담당자에게 자동으로 발송할 수 있습니다.

핵심 요약

준비물: Python + pandas + openpyxl 설치 (pip install pandas openpyxl)
설정: INPUT_PATH, ROW_COL(행 기준), COL_COL(열 기준), VALUE_COL(집계 값), AGG_FUNC(집계 방식) 수정
실행: 터미널에서 python make_pivot.py 입력
결과: 피벗 테이블·다중집계·원본데이터 3개 시트가 포함된 엑셀 파일 자동 생성, 소계 자동 추가

이 코드를 응용하면 3개 이상의 열을 동시에 행 기준으로 사용하거나, 피벗 결과에 조건부 서식을 자동 적용하는 것도 가능합니다.

IT 생활 꿀팁 - 스마트폰·PC 활용법