Design a pivot table layout
Checks whether the source data is pivot-ready first, then gives the field layout.
| Category | Office work › Spreadsheets |
|---|---|
| Tags | AnalyzingOffice workerSpreadsheet |
Design the pivot table. 1. **First judge whether my source data is shaped for a pivot at all.** Merged cells, subtotal rows, dates spread across columns — flag these and say how to fix them before anything else. *This is usually the real problem.* 2. Give the rows / columns / values / filters layout that produces what I want. 3. State the summary function for each value field and why. 4. **If another layout answers the same question, propose one and explain the difference.** 5. **If a pivot cannot do what I am asking, say so plainly and give the alternative.**
What this prompt does
Pivot problems are usually data layout problems. This diagnoses the source shape before proposing fields, and says when a pivot is the wrong tool.
Real outputs by model
Read full answers
We gave three models the same input and copied their answers unedited. Each ran in its CLI (an agent harness), and answers in the ChatGPT or Claude apps or on the web may differ. Outputs are in Korean.
Source data structure: A~F열에 주문일자, 지점명, 담당자, 상품명, 수량, 매출액이 있는 약 2만 행짜리 원본 시트. 지점별 소계 행이 중간중간 섞여 있고 상품명 열에 병합 셀이 몇 군데 있음 What I want to see: 지점별 월별 매출 합계와 담당자별 판매 수량을 한 화면에서 비교하고 싶음
1. 원본 데이터 구조 점검
현재 원본은 그대로 피벗에 넣으면 문제가 생깁니다.
- 지점별 소계 행: 원본 데이터에서 제거해야 합니다. 소계 행까지 포함하면 피벗이 매출을 이중 집계합니다.
- 상품명 열의 병합 셀: 병합을 해제하고, 비어 있는 셀에 위의 상품명을 채워 넣어야 합니다.
- 주문일자 형식: A열이 실제 날짜 값인지 확인해야 합니다. 텍스트 날짜라면 날짜 형식으로 변환해야 월별 그룹화가 가능합니다.
- 열 구조: 현재 A~F 열 구성 자체는 정상적인 세로형 거래 데이터입니다. 각 행이 하나의 주문 또는 주문상품 항목이 되도록 유지하면 됩니다.
- 필드명: 첫 행에는
주문일자,지점명,담당자,상품명,수량,매출액만 두고 빈 행이나 추가 제목 행은 제거합니다.
정리 후에는 약 2만 행 전체를 표로 변환하고, 소계 행이 다시 포함되지 않도록 관리하는 것이 좋습니다.
2. 추천 피벗 구성
목표가 서로 다른 두 가지 분석이므로, 한 화면에 피벗 테이블 2개를 배치하는 구성이 가장 읽기 쉽습니다.
피벗 1: 지점별 월별 매출 합계
- 행:
지점명→주문일자 - 열: 필요하면
주문일자의 월 - 값:
매출액 - 필터:
연도,지점명 - 날짜 그룹:
주문일자를 연도 및 월로 그룹화
값 필드는 다음과 같이 설정합니다.
매출액: 합계- 지점별·월별 전체 매출을 계산해야 하므로 합계를 사용합니다.
결과 예시는 다음과 같습니다.
| 지점명 | 1월 | 2월 | 3월 | 총합계 |
|---|---|---|---|---|
| 강남점 | ||||
| 강북점 |
피벗 2: 담당자별 판매 수량
- 행:
담당자 - 열: 필요하면
지점명또는주문일자의 월 - 값:
수량 - 필터:
연도,지점명,월
값 필드는 다음과 같이 설정합니다.
수량: 합계- 담당자가 판매한 상품 수량의 누계를 구해야 하므로 합계를 사용합니다.
월별 비교까지 필요하다면 주문일자의 월을 열에 넣습니다.
| 담당자 | 1월 | 2월 | 3월 | 총합계 |
|---|---|---|---|---|
| 김담당 | ||||
| 이담당 |
두 피벗에 동일한 연도·지점 필터 또는 슬라이서를 연결하면 한 화면에서 같은 조건으로 비교할 수 있습니다.
3. 한 개의 피벗으로 만들 경우
한 피벗에 모두 넣으려면 다음처럼 구성할 수 있습니다.
- 행:
지점명→주문일자의 월 →담당자 - 값:
매출액합계수량합계
- 필터: 연도, 지점명
다만 이 방식은 지점·월·담당자가 한 계층으로 깊어져서 매출과 수량을 비교하기 어렵고 화면이 복잡해집니다.
4. 대안 레이아웃
가장 실용적인 대안은 다음과 같습니다.
- 왼쪽: 지점별 월별 매출 피벗
- 오른쪽: 담당자별 판매 수량 피벗
- 위쪽: 연도, 지점명, 월 슬라이서
- 필요하면 매출 합계에는 세로 막대 차트, 담당자 수량에는 가로 막대 차트 추가
이렇게 하면 같은 필터 조건에서 지점 매출과 담당자 판매량을 동시에 볼 수 있습니다.
5. 피벗만으로 가능한가?
가능합니다. 다만 “지점별 월별 매출”과 “담당자별 판매 수량”은 행 기준이 서로 다르므로, 하나의 깔끔한 피벗보다는 두 개의 피벗 테이블을 한 화면에 배치하는 방식이 적합합니다.
먼저 소계 행 제거, 병합 해제 및 빈 상품명 채우기, 날짜 형식 정리를 완료해야 정확한 결과를 얻을 수 있습니다.