정답부터: 원본 데이터를 한 행에 한 건씩 정리하고 머리글을 한 줄로 만든 뒤 범위를 Excel 표로 변환합니다. 표 안의 셀을 선택하고 삽입 → 피벗 테이블 → 새 워크시트를 고른 다음, 필드를 행·열·값·필터 영역으로 끌어 배치합니다. 원본이 바뀌면 반드시 새로 고침합니다.
예를 들어 지역별 상품 매출을 보려면 지역은 행, 상품은 열, 매출은 값에 둡니다. 날짜를 필터에 두면 특정 기간만 볼 수 있습니다. 숫자 필드가 ‘합계’가 아니라 ‘개수’로 집계되면 원본 열에 텍스트나 빈 문자열이 섞였는지 먼저 확인합니다.
피벗테이블에 적합한 원본 구조
Microsoft는 첫 행에 열 레이블이 있는 목록 형식, 같은 열의 일관된 데이터 형식, 범위 안에 빈 행·열이 없는 구조를 권장합니다. 원본에 이미 부분합과 총합계를 넣지 말고 피벗테이블이 계산하게 합니다.
| 좋은 원본 | 문제가 되는 원본 |
|---|---|
| 한 행에 거래 한 건 | 한 거래가 여러 블록으로 나뉨 |
| 한 줄 머리글 | 병합된 2~3줄 머리글 |
| 매출 열은 모두 숫자 | 숫자와 ‘미정’ 텍스트 혼합 |
| 날짜 열은 실제 날짜값 | 2026.7월 같은 설명 텍스트 혼합 |
| 빈 행·열 없이 연속 | 부서마다 빈 행 삽입 |
| 원본에는 상세 데이터만 | 중간 부분합·총합계 포함 |
원본을 Excel 표로 바꾸는 이유
원본 범위를 선택하고 Ctrl+T로 표를 만들면 새 행이 추가될 때 표 범위가 확장됩니다. Microsoft는 Excel 표를 원본으로 쓰면 피벗테이블 새로 고침 시 새 데이터와 변경 데이터가 자동으로 포함된다고 안내합니다.
표 이름을 ‘매출원본’처럼 의미 있게 정하고 열 이름을 날짜, 지역, 상품, 수량, 매출처럼 고유하게 만듭니다. 같은 머리글을 두 번 쓰거나 빈 머리글을 두지 않습니다.
피벗테이블 만드는 기본 순서
1. 원본 범위를 확인합니다
표 안의 셀 하나를 클릭하고 Ctrl+A 또는 표 범위 표시를 확인합니다. 표 밖의 메모·합계가 포함되지 않았는지, 마지막 행까지 들어갔는지 봅니다.
2. 삽입 → 피벗 테이블을 선택합니다
원본 표 또는 범위가 올바르게 표시되는지 확인합니다. 처음에는 새 워크시트에 만드는 편이 기존 데이터와 겹치지 않아 안전합니다. 기존 시트에 놓는다면 피벗테이블이 확장될 빈 공간을 충분히 확보합니다.
3. 필드 목록을 확인합니다
원본 머리글이 피벗테이블 필드로 나타납니다. 필드가 빠졌다면 원본 범위와 머리글을 다시 확인합니다. 숫자가 아닌 필드는 기본적으로 행, 날짜·시간은 열, 숫자는 값 영역으로 자동 배치될 수 있지만 목적에 맞게 직접 이동합니다.
4. 행 영역을 배치합니다
세로로 분류할 기준을 행에 둡니다. 지역 아래 상품처럼 여러 필드를 넣으면 계층 구조가 됩니다. 순서는 위에서 아래로 큰 분류에서 작은 분류가 되도록 배치합니다.
5. 열 영역을 배치합니다
월, 분기, 상태처럼 가로 비교할 항목을 열에 둡니다. 항목 수가 너무 많으면 피벗테이블이 지나치게 넓어지므로 상세 항목은 행으로 옮기거나 필터로 제한합니다.
6. 값 영역을 배치합니다
매출, 수량, 건수처럼 계산할 필드를 값에 둡니다. 같은 필드를 두 번 넣어 한쪽은 합계, 다른 쪽은 전체 합계 대비 비율로 표시할 수도 있습니다.
7. 필터 영역을 배치합니다
보고서 전체를 좁힐 연도, 부서, 채널 등을 필터에 둡니다. 자주 클릭하는 항목은 슬라이서를 추가하면 사용자가 현재 선택 상태를 보기 쉽습니다.
행·열·값·필터 배치 예시
| 질문 | 행 | 열 | 값 | 필터 |
|---|---|---|---|---|
| 지역별 상품 매출 | 지역 | 상품 | 매출 합계 | 연도 |
| 월별 담당자 실적 | 담당자 | 월 | 매출 합계 | 부서 |
| 상태별 업무 건수 | 상태 | 우선순위 | 업무ID 개수 | 담당자 |
| 상품별 평균 단가 | 상품 | 분기 | 단가 평균 | 지역 |
피벗테이블은 질문에 따라 같은 원본을 다르게 배치하는 도구입니다. 필드 위치를 바꾸면서 합계가 무엇을 뜻하는지 제목에 명확히 적습니다.
합계가 아니라 개수로 나올 때
Microsoft는 값 필드가 숫자면 기본적으로 합계, Excel이 텍스트로 해석하면 개수로 표시될 수 있다고 설명합니다. 값 영역의 필드 메뉴 → 값 필드 설정에서 합계로 바꿀 수 있지만, 원본에 텍스트가 섞였다면 먼저 데이터를 정리해야 정확합니다.
- 숫자 앞의 작은따옴표와 공백을 제거합니다.
- ‘없음’, ‘-’ 같은 텍스트를 별도 상태 열로 분리합니다.
- 수식이 빈 문자열을 반환하는지 확인합니다.
- 숫자 서식만 바꾸지 말고 실제 저장 형식을 숫자로 변환합니다.
- 수정 후 피벗테이블을 새로 고칩니다.
값 요약 방식과 표시 방식
값 필드 설정의 ‘값 요약 기준’에서 합계, 개수, 평균, 최대, 최소 등을 선택합니다. ‘값 표시 형식’에서는 전체 합계 대비 비율, 행 합계 대비 비율, 누계, 이전 항목 대비 차이 등으로 바꿀 수 있습니다.
계산 방식이 바뀌면 필드 이름도 ‘매출 합계’, ‘매출 평균’, ‘전체 대비 비율’처럼 수정해 독자가 오해하지 않게 합니다. 숫자 서식은 값 필드 설정에서 통화·천 단위·백분율로 지정해야 새로 고침 뒤에도 일관되기 쉽습니다.
날짜를 월·분기·연도로 그룹화
피벗테이블의 날짜 항목을 선택하고 그룹화에서 연도, 분기, 월을 고를 수 있습니다. 그룹화가 안 되면 원본 날짜 열에 텍스트 날짜, 빈 값, 오류가 섞였는지 확인합니다.
여러 해가 있는 데이터를 ‘월’만 그룹화하면 서로 다른 해의 1월이 합쳐질 수 있습니다. 연도와 월을 함께 선택하거나 연도 필드를 행·열·필터에 배치합니다.
원본 변경 후 새로 고침
원본 데이터를 수정하거나 새 행을 추가한 뒤 피벗테이블 안에서 마우스 오른쪽 단추 → 새로 고침을 선택합니다. 여러 피벗테이블이 있으면 피벗테이블 분석 → 새로 고침 메뉴의 모두 새로 고침을 사용합니다.
새로 고침은 지정된 원본 범위 안의 변경을 반영합니다. 일반 고정 범위를 원본으로 사용해 새 행이 범위 밖에 생겼다면 원본 데이터 변경으로 범위를 늘리거나 Excel 표로 바꿉니다.
파일을 열 때 자동 새로 고침
피벗테이블 옵션의 데이터 탭에서 파일을 열 때 데이터 새로 고침을 설정할 수 있습니다. 외부 연결의 접근권한·속도·개인정보를 고려하고, 자동 새로 고침 결과가 보고서 서식과 충돌하지 않는지 테스트합니다.
새로 고침 후 서식이 깨질 때
피벗테이블 옵션에서 ‘업데이트 시 셀 서식 유지’와 ‘업데이트 시 열 너비 자동 맞춤’ 설정을 확인합니다. 원본 항목이 늘어나 피벗테이블이 확장될 공간도 비워 둡니다. 오른쪽이나 아래에 다른 데이터가 있으면 확장 중 #SPILL! 또는 겹침 문제가 생길 수 있습니다.
원본 합계와 결과 검수
- 피벗테이블 총합계가 원본 매출 합계와 일치하는가
- 필터에서 일부 항목이 숨겨져 있지 않은가
- 숫자 필드가 개수로 집계되지 않았는가
- 날짜 그룹화가 연도를 섞지 않았는가
- 원본의 부분합 행이 이중 집계되지 않았는가
- 새 행 추가 후 새로 고침했는가
원본 표에 SUMIFS나 필터 합계를 만들어 피벗 총합계와 비교하면 누락·이중집계를 빨리 찾을 수 있습니다. 보고서 배포 전 필터를 ‘모두’로 되돌리고 선택 상태를 제목에 표시합니다.
피벗테이블과 일반 수식의 선택
필드를 빠르게 재배치하며 여러 관점으로 탐색할 때는 피벗테이블이 편리합니다. 고정된 보고서 양식과 복잡한 셀별 계산이 필요하면 SUMIFS 등 수식이 더 적합할 수 있습니다. 반복 데이터 정리까지 필요하면 Power Query로 원본을 정제한 뒤 피벗테이블로 분석합니다.
자주 묻는 질문
Q1. 원본에 새 행을 추가했는데 피벗에 안 보입니다
새로 고침했는지, 새 행이 원본 범위 안인지 확인합니다. 원본을 Excel 표로 만들면 범위 확장 관리가 쉬워집니다.
Q2. 매출 합계가 아니라 개수로 표시됩니다
Excel이 원본 열을 텍스트로 인식했을 가능성이 큽니다. 텍스트·공백·오류를 정리하고 값 필드 설정과 새로 고침을 확인합니다.
Q3. 날짜를 월별로 묶을 수 없습니다
날짜 열에 텍스트, 빈 셀, 오류가 섞였는지 확인하고 모두 실제 날짜값으로 정리합니다.
Q4. 피벗테이블 값을 직접 수정할 수 있나요?
일반적으로 요약 셀을 직접 고치는 방식이 아니라 원본 데이터를 수정하고 새로 고침합니다. 계산은 값 필드 설정이나 계산 기능을 사용합니다.
Q5. 피벗테이블을 삭제하면 원본도 지워지나요?
피벗테이블 범위나 전용 시트를 삭제해도 원본 표 자체는 별개입니다. 다만 공유 캐시·연결을 쓰는 다른 피벗이 있는지 확인합니다.
공식 출처
2026년 7월 13일 Microsoft 피벗테이블로 워크시트 데이터 분석, Microsoft 피벗테이블 및 피벗차트 개요, Microsoft 피벗테이블 데이터 새로 고침을 확인했습니다. 메뉴와 자동 새로 고침 기능은 Excel 버전·환경에 따라 다를 수 있습니다.
