피벗테이블 행 열 값 배치를 질문에서 AI 로 받기

판매 원본 120행에서 알고 싶은 것은 두 가지입니다. 지역별로 월마다 얼마 팔았나, 4월보다 6월에 수량이 줄어든 상품은 무엇인가. 필드를 행·열·값 어디에 끌어다 놓을지는 AI 에게 물어도 됩니다. 다만 원본이 지저분하면 배치가 맞아도 숫자가 틀립니다.

원본

날짜,지역,상품,수량,금액2026-05-12,부산,머그컵,11,990002026-04-07,서울,에코백,2,240002026-05-17,서울,에코백,4,480002026-04-05,서울,머그컵,7,630002026.04.09,부산,텀블러,9,162000…

시험용으로 만든 가상 자료입니다. 일부러 두 가지를 섞었습니다. 날짜 8칸은 2026.04.09 처럼 점으로 적힌 텍스트이고, 부산은 부산 · 부산 (끝 공백) · 부산광역시 세 가지로 적혀 있습니다.

프롬프트

AI 는 120행을 다 보지 못합니다. 그래서 처음 몇 행과 함께 「지역」 열 필터 목록에 보이는 값을 그대로 붙입니다. 답하는 순서는 번호로 고정합니다. Anthropic 의 프롬프트 작성 가이드는 순서나 빠짐없음이 중요하면 지시를 번호 매긴 단계로 주라고 합니다. 여기서는 「원본 점검」이 배치보다 먼저 나와야 합니다.

엑셀 판매 원본으로 피벗 테이블을 만들려고 합니다. 아래 순서대로 답해 주세요. <sheet>시트 이름: {판매} · 데이터 {120}행(머리글 제외) · 범위 {A1:E121}머리글과 처음 5행:{머리글과 5행을 쉼표로}「{지역}」 열 필터 목록에 보이는 값: {광주 / 대구 / 부산 / 부산  / 부산광역시 / 서울}</sheet> <questions>Q1. {지역별로 월마다 금액이 얼마인가}Q2. {4월보다 6월에 수량이 줄어든 상품은 무엇인가}</questions> 1. 피벗을 만들기 전에 원본에서 고쳐야 할 것을 목록으로 적습니다. 항목마다 「무엇이 문제인지 · 엑셀에서 어떻게 찾는지 · 어떻게 고치는지」를 한 줄씩.2. 질문마다 피벗 배치를 표로 적습니다. 열: 질문 | 필터 | 행 | 열 | 값(집계 방식) | 날짜 그룹화3. 만든 피벗이 맞는지 원본과 대조할 수식을 2개 적습니다. 하나는 피벗 총합계 대조, 하나는 Q1 의 「{부산} · {4월}」 한 칸 대조(SUMIFS).4. 위 세 가지 외의 설명은 쓰지 않습니다.

결과

두 번 돌린 것 중 첫 번째 답의 일부입니다.

| # | 무엇이 문제인지 | 엑셀에서 어떻게 찾는지 | 어떻게 고치는지 ||---|---|---|---|| 1 | 날짜가 날짜 값이 아닌 텍스트이고 형식도 섞여 있음 (`2026.04.09` 등). 이 상태로는 월 그룹화가 되지 않음 | `=SUMPRODUCT(--NOT(ISNUMBER(A2:A121)))`가 0이 아니면 텍스트가 있는 것. 왼쪽 정렬된 셀을 확인 | A열 선택 → Ctrl+H로 `.`을 `-`로 모두 바꾸기 → 서식을 `yyyy-mm-dd`로 지정. 그래도 텍스트면 보조열에 `=DATEVALUE(SUBSTITUTE(A2,".","-"))` 입력 후 값 붙여넣기 || 2 | 같은 지역이 `부산` / `부산 `(뒤 공백) / `부산광역시` 3가지로 따로 집계됨 | 지역 열 필터 목록 확인. 공백은 `=SUMPRODUCT(--(B2:B121<>TRIM(B2:B121)))`로 개수 확인 | 보조열에 `=TRIM(SUBSTITUTE(B2,CHAR(160)," "))` 입력 후 값 붙여넣기. Ctrl+H로 `부산광역시`를 `부산`으로 바꾸기(전체 셀 내용 일치 체크) |…| 질문 | 필터 | 행 | 열 | 값(집계 방식) | 날짜 그룹화 ||---|---|---|---|---|---|| Q1. 지역별 월별 금액 | 없음 (연도가 여러 개면 연도) | 지역 | 날짜 (월) | 금액: 합계 | 월 (연도가 여러 개면 연+월) || Q2. 4월보다 6월에 수량이 줄어든 상품 | 지역은 전체 유지. 날짜(월)에서 4월·6월만 체크 | 상품 | 날짜 (월) | 수량: 합계. 값 표시 형식을 「차이」로 설정 (기준 필드 `월`, 기준 항목 `4월`). 6월 열이 음수인 상품이 답 | 월 (연도가 여러 개면 연+월) |…- Q1의 「부산 · 4월」 한 칸 대조 (결과가 피벗의 해당 칸과 같아야 함):  `=SUMIFS(판매!$E$2:$E$121,판매!$B$2:$B$121,"부산",판매!$A$2:$A$121,">="&DATE(2026,4,1),판매!$A$2:$A$121,"<"&DATE(2026,5,1))`

두 번 모두 점검 목록 앞쪽이 텍스트 날짜와 지역 표기였고, Q1 배치(행 지역 · 열 월 · 값 금액 합계)도 같았습니다. 두 번 다 Q2 는 값 표시 형식 「차이」(기준 항목 4월)로 6월 열이 음수인 상품을 찾게 했고, 2회차는 합계와 차이를 둘 다 두라고 했습니다.

검산 — 피벗과 SUMIFS 가 같이 틀리는 경우

AI 가 준 대조 수식은 원본을 고친 다음에 쓰는 것입니다. 고치기 전에 돌리면 SUMIFS 는 부산 한 표기만, 진짜 날짜만 셉니다. 피벗의 「부산 · 4월」 칸도 같을 것으로 보이고, 그러면 두 값이 일치해서 맞은 것처럼 보입니다. 피벗 쪽은 가정입니다. 엑셀을 열지 않았고, 열에 텍스트 날짜가 섞여 있으면 엑셀이 그 행만 빼는 것이 아니라 월 그룹화 자체를 거부할 수도 있습니다. 총합계 대조도 마찬가지로 통과합니다. 갈라진 행도 총합계에는 다 들어가기 때문입니다.

같은 계산을 Python 으로 해 보면 차이가 보입니다. 아래 recheck.py 는 엑셀의 SUM · COUNT · SUMIFS 가 낼 값을 고치기 전과 후로 나눠 계산합니다(쓰는 법: recheck.py 판매.csv 지역 월).

$ python3 -I recheck.py sales.csv 부산 4rows                120SUM(amount)         10434000COUNT(date)         112region spellings    ['부산', '부산 ', '부산광역시']SUMIFS raw          669000SUMIFS after fix    1047000

고치기 전 「부산 · 4월」은 669,000원, 고친 뒤에는 1,047,000원입니다. 엑셀에서 같은 함정을 피하는 대조는 이렇습니다.

그대로 따르면 막히는 곳

Q2 배치는 한국어 두 번 모두 「날짜(월)」를 필터와 열에 동시에 두라고 했습니다(영어 두 번은 피했습니다). 엑셀은 한 필드를 필터·행·열 중 한 곳에만 둡니다. 다른 영역으로 끌면 원래 자리에서 빠집니다(Microsoft 지원 문서). 4월·6월만 남기려면 필터 영역은 비우고 열 레이블의 드롭다운에서 고릅니다. 프롬프트 2번 끝에 「같은 필드를 필터와 행·열에 동시에 두지 않습니다.」를 덧붙여 한국어·영어 두 번씩 다시 돌리자, 네 번 모두 필터에는 날짜를 두지 않았습니다(비우거나 지역·연도). 4월·6월만 남기는 방법은 회차마다 달랐고, 안내한 경우는 모두 날짜(월) 열에서 고르는 것이었습니다. 영어 한 번과 한국어 한 번은 그 열에서 체크하라고 했고, 영어 다른 한 번은 선택 사항이라고 했으며, 한국어 2회차는 선택 단계를 적지 않았습니다.