피벗테이블 설계 물어보는 프롬프트
무엇을 보고 싶은지 말하면 피벗테이블의 행·열·값에 무엇을 넣을지 알려줍니다. 원본 데이터가 피벗에 안 맞는 구조면 먼저 고칠 점을 짚어줍니다.
| 분류 | 사무 › 엑셀 |
|---|---|
| 태그 | 분석직장인엑셀파일 |
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.**
어떤 프롬프트인가
피벗이 안 되는 건 대개 피벗이 아니라 원본 데이터 모양 문제다. 이 프롬프트는 필드 배치 전에 원본 구조부터 진단하고, 피벗이 맞는 도구가 아니면 그렇다고 말한다.
모델별 실제 결과
답 전문 보기
같은 입력을 세 모델에 넣어 받은 답을 그대로 옮겼습니다. 각 CLI(에이전트 하네스)에서 실행했습니다. ChatGPT·Claude 앱이나 웹에서는 답이 다를 수 있습니다.
데이터구조: A~F열에 주문일자, 지점명, 담당자, 상품명, 수량, 매출액이 있는 약 2만 행짜리 원본 시트. 지점별 소계 행이 중간중간 섞여 있고 상품명 열에 병합 셀이 몇 군데 있음 목표: 지점별 월별 매출 합계와 담당자별 판매 수량을 한 화면에서 비교하고 싶음
1. 원본 데이터 구조 점검
현재 원본은 그대로 피벗에 넣으면 문제가 생깁니다.
- 지점별 소계 행: 원본 데이터에서 제거해야 합니다. 소계 행까지 포함하면 피벗이 매출을 이중 집계합니다.
- 상품명 열의 병합 셀: 병합을 해제하고, 비어 있는 셀에 위의 상품명을 채워 넣어야 합니다.
- 주문일자 형식: A열이 실제 날짜 값인지 확인해야 합니다. 텍스트 날짜라면 날짜 형식으로 변환해야 월별 그룹화가 가능합니다.
- 열 구조: 현재 A~F 열 구성 자체는 정상적인 세로형 거래 데이터입니다. 각 행이 하나의 주문 또는 주문상품 항목이 되도록 유지하면 됩니다.
- 필드명: 첫 행에는
주문일자,지점명,담당자,상품명,수량,매출액만 두고 빈 행이나 추가 제목 행은 제거합니다.
정리 후에는 약 2만 행 전체를 표로 변환하고, 소계 행이 다시 포함되지 않도록 관리하는 것이 좋습니다.
2. 추천 피벗 구성
목표가 서로 다른 두 가지 분석이므로, 한 화면에 피벗 테이블 2개를 배치하는 구성이 가장 읽기 쉽습니다.
피벗 1: 지점별 월별 매출 합계
- 행:
지점명→주문일자 - 열: 필요하면
주문일자의 월 - 값:
매출액 - 필터:
연도,지점명 - 날짜 그룹:
주문일자를 연도 및 월로 그룹화
값 필드는 다음과 같이 설정합니다.
매출액: 합계- 지점별·월별 전체 매출을 계산해야 하므로 합계를 사용합니다.
결과 예시는 다음과 같습니다.
| 지점명 | 1월 | 2월 | 3월 | 총합계 |
|---|---|---|---|---|
| 강남점 | ||||
| 강북점 |
피벗 2: 담당자별 판매 수량
- 행:
담당자 - 열: 필요하면
지점명또는주문일자의 월 - 값:
수량 - 필터:
연도,지점명,월
값 필드는 다음과 같이 설정합니다.
수량: 합계- 담당자가 판매한 상품 수량의 누계를 구해야 하므로 합계를 사용합니다.
월별 비교까지 필요하다면 주문일자의 월을 열에 넣습니다.
| 담당자 | 1월 | 2월 | 3월 | 총합계 |
|---|---|---|---|---|
| 김담당 | ||||
| 이담당 |
두 피벗에 동일한 연도·지점 필터 또는 슬라이서를 연결하면 한 화면에서 같은 조건으로 비교할 수 있습니다.
3. 한 개의 피벗으로 만들 경우
한 피벗에 모두 넣으려면 다음처럼 구성할 수 있습니다.
- 행:
지점명→주문일자의 월 →담당자 - 값:
매출액합계수량합계
- 필터: 연도, 지점명
다만 이 방식은 지점·월·담당자가 한 계층으로 깊어져서 매출과 수량을 비교하기 어렵고 화면이 복잡해집니다.
4. 대안 레이아웃
가장 실용적인 대안은 다음과 같습니다.
- 왼쪽: 지점별 월별 매출 피벗
- 오른쪽: 담당자별 판매 수량 피벗
- 위쪽: 연도, 지점명, 월 슬라이서
- 필요하면 매출 합계에는 세로 막대 차트, 담당자 수량에는 가로 막대 차트 추가
이렇게 하면 같은 필터 조건에서 지점 매출과 담당자 판매량을 동시에 볼 수 있습니다.
5. 피벗만으로 가능한가?
가능합니다. 다만 “지점별 월별 매출”과 “담당자별 판매 수량”은 행 기준이 서로 다르므로, 하나의 깔끔한 피벗보다는 두 개의 피벗 테이블을 한 화면에 배치하는 방식이 적합합니다.
먼저 소계 행 제거, 병합 해제 및 빈 상품명 채우기, 날짜 형식 정리를 완료해야 정확한 결과를 얻을 수 있습니다.
같은 분류의 프롬프트
| 에너지 데이터 분석 프롬프트 | |
| 축구 경기 예측·베팅 분석 프롬프트 | |
| Blackmagicmantra 설명 프롬프트 | |
| 조건부 서식 규칙 만드는 프롬프트 | |
| 두 표의 숫자가 안 맞을 때 대조하는 프롬프트 |