엑셀 피벗 테이블 만들기 방법 총정리 (지점별 합계, 값 필드 설정, 새로 고침)

이 글의 치트키

• 표 안 아무 셀이나 클릭하고 [삽입] > [피벗 테이블] > [확인]을 누르면 새 시트에 빈 피벗 테이블이 생긴다
• 필드 목록에서 글자 필드(지점)는 체크하면 [행], 숫자 필드(매출액)는 체크하면 [값]으로 들어가 바로 합계가 나온다
• 제품처럼 가로로 펼칠 항목은 [열] 영역으로 끌어 놓으면 지점×제품 교차 집계표가 완성된다
• 원본을 고친 뒤에는 피벗 표가 자동으로 바뀌지 않으니 [새로 고침](Alt + F5)을 꼭 눌러야 한다

"지점별로 제품마다 매출이 얼마인지 한 장으로 정리해 주세요." 판매 내역을 받아 놓고 SUMIFS를 칸마다 넣다 보면 시간이 금방 가죠. 이럴 때는 수식 없이 클릭 몇 번으로 집계표를 만드는 엑셀 피벗 테이블이 가장 빨라요. 피벗 테이블 만들기, 필드 배치로 교차 집계표 만들기, 값 필드 설정, 원본을 고친 뒤 새로 고침까지 순서대로 정리해 볼게요.

엑셀 피벗 테이블은 언제 쓸까?

피벗 테이블은 긴 목록형 데이터를 기준(지점, 제품, 월 등)별로 묶어서 합계·개수·평균을 내 주는 요약 표예요. 원본 데이터는 그대로 두고 새 시트에 결과만 따로 만들기 때문에, 원본을 망가뜨릴 걱정이 없어요.

오늘 예제는 3분기 지점별 판매 내역 14건이에요. 판매일, 지점, 담당자, 제품, 수량, 단가, 매출액 7개 열이 있고(매출액 = 수량 × 단가), 여기서 지점(강남점·잠실점·홍대점)별, 제품(노트북·모니터·키보드)별 매출 합계를 한 장의 표로 만들어 볼게요.

방법 1. 피벗 테이블 만들기 ([삽입] > [피벗 테이블])

먼저 빈 피벗 테이블을 만들어요. 범위를 드래그하지 않아도 엑셀이 붙어 있는 표 전체를 알아서 잡아 줘요.

STEP 1

표 안의 셀 하나를 클릭하고 [삽입] > [피벗 테이블]

표 안 아무 셀(예제에서는 B4)을 클릭한 뒤 [삽입] 탭 맨 왼쪽의 [피벗 테이블] 단추를 클릭해요.

STEP 2

범위와 위치를 확인하고 [확인]

표 또는 범위의 피벗 테이블 창이 열리고, 표/범위 칸에 Sheet1!$A$1:$G$15가 자동으로 들어가 있어요. 넣을 위치는 기본값인 새 워크시트 그대로 두고 [확인]을 눌러요.

STEP 3

새 시트에 빈 피벗 테이블과 필드 목록이 생겨요

새 시트(Sheet2)의 A3 위치에 피벗 테이블1 자리가 생기고, 오른쪽에 피벗 테이블 필드 창이 열려요. 위쪽은 원본 표의 머리글(필드) 목록, 아래쪽은 필터·열·행·값 4개 영역이에요.

실무대리 TIP

피벗 테이블의 원본은 머리글이 한 줄이고, 중간에 빈 행·빈 열·병합된 셀이 없는 목록이어야 해요. 머리글이 비어 있는 열이 있으면 "필드 이름이 잘못되었습니다" 오류가 나요.

방법 2. 필드를 배치해 지점별·제품별 집계표 만들기

이제 필드 목록에서 체크하거나 끌어 놓기만 하면 집계표가 바로 만들어져요. 어느 영역에 넣느냐에 따라 표 모양이 달라져요.

STEP 1

[지점]을 체크하면 [행]으로 들어가요

필드 목록에서 지점을 체크하면 글자 필드라서 자동으로 [행] 영역에 들어가고, 시트에는 강남점·잠실점·홍대점과 총합계가 세로로 나열돼요.

STEP 2

[매출액]을 체크하면 [값]으로 들어가 합계가 나와요

이번엔 매출액을 체크해요. 숫자 필드는 자동으로 [값] 영역에 합계 : 매출액으로 들어가요. 강남점 6,250,000, 잠실점 5,950,000, 홍대점 5,400,000, 총합계 17,600,000이 바로 계산됐어요.

STEP 3

[제품]을 [열] 영역으로 끌어 놓아요

제품은 체크하지 말고 필드 목록에서 끌어서 아래쪽 [열] 영역에 놓아요. 그러면 노트북·모니터·키보드가 가로로 펼쳐져 지점 × 제품 교차 집계표가 완성돼요. 예를 들어 잠실점 키보드는 1,100,000, 키보드 전체 합계는 1,800,000이에요.

참고

제품을 그냥 체크하면 글자 필드라서 [행] 영역의 지점 아래로 들어가 "강남점 > 노트북, 모니터…"처럼 세로로 길게 나와요. 가로로 펼치고 싶을 때는 [열] 영역으로 끌어 놓기가 정답이에요.

STEP 4

[값] 영역 ▼ > [값 필드 설정]에서 이름·계산 방식 바꾸기

[값] 영역의 합계 : 매출액 오른쪽 ▼를 눌러 [값 필드 설정]을 열어요. 사용자 지정 이름에 매출 합계를 입력하고 [확인]을 눌러요. 같은 창의 목록에서 합계 대신 개수, 평균, 최대, 최소도 고를 수 있어요.

STEP 5

A3 머리글이 "매출 합계"로 바뀌었는지 확인해요

표 왼쪽 위 머리글과 [값] 영역 이름이 모두 매출 합계로 바뀌었어요. 보고서에 그대로 붙여도 "합계 : 매출액"보다 훨씬 깔끔하게 읽혀요.

실무대리 TIP

사용자 지정 이름을 원본 머리글과 똑같이 "매출액"으로 넣으면 필드 이름이 이미 있다는 오류가 나요. "매출 합계", "매출액 " (뒤에 공백 한 칸)처럼 원본과 조금 다르게 적어 주세요.

LIST

방법 3. 원본을 고친 뒤 피벗 테이블 새로 고침

피벗 테이블은 수식이 아니라 만들 때의 데이터를 따로 저장해 둔 표예요. 그래서 원본 숫자를 고쳐도 피벗 표는 그대로예요. 꼭 새로 고침을 눌러야 반영돼요.

STEP 1

원본 시트에서 잠실점 키보드 수량을 10 → 20으로 고쳐요

Sheet1의 E4(잠실점 키보드, 수량 10)를 20으로 고쳤어요. 매출액(G4)은 수식이라 500,000 → 1,000,000으로 바로 바뀌어요. 하지만 Sheet2의 피벗 표는 아직 잠실점 키보드 1,100,000 그대로예요.

STEP 2

[피벗 테이블 분석] > [새로 고침]을 눌러요

피벗 표 안의 셀을 클릭하고 [피벗 테이블 분석] 탭의 [새로 고침]을 눌러요(단축키 Alt + F5). 잠실점 키보드가 1,600,000, 잠실점 합계 6,450,000, 키보드 합계 2,300,000, 총합계 18,100,000으로 바뀌었어요.

주의

원본 표 아래에 새 행을 추가했다면 새로 고침만으로는 포함되지 않아요. 피벗 테이블 범위가 $A$1:$G$15로 고정돼 있기 때문이에요. [피벗 테이블 분석] > [데이터 원본 변경]에서 범위를 늘리거나, 처음부터 원본을 Ctrl + T로 표로 만들어 두면 행이 늘어나도 새로 고침만으로 자동 포함돼요.

완성 결과 비교

BEFORE

원본을 고쳤는데도 잠실점 키보드 1,100,000, 총합계 17,600,000이 그대로인 피벗 표

AFTER

새로 고침 한 번으로 잠실점 키보드 1,600,000, 총합계 18,100,000까지 반영된 피벗 표

상황별로 피벗 테이블 어떻게 쓸까?

상황	이렇게 하세요	예시
기준 하나로 합계만 볼 때	기준 필드 → [행], 숫자 필드 → [값]	지점별 매출 합계
두 기준을 표로 교차해서 볼 때	두 번째 기준을 [열]로 끌어 놓기	지점 × 제품 매출
합계 말고 건수·평균이 필요할 때	[값 필드 설정] > 개수 / 평균	지점별 판매 건수
특정 기간·담당자만 보고 싶을 때	해당 필드를 [필터] 영역에 넣기	담당자 = 김하늘
원본 숫자를 고쳤을 때	[새로 고침] (Alt + F5)	수량 10 → 20 반영
원본에 행을 추가했을 때	[데이터 원본 변경] 또는 원본을 표(Ctrl + T)로	15행 → 16행 추가

같이 쓰면 좋은 단축키

Alt + F5 선택한 피벗 테이블 새로 고침

Ctrl + Alt + F5 통합 문서의 피벗 테이블 모두 새로 고침

Ctrl + T 원본 목록을 표로 만들기(행이 늘어도 범위 자동 확장)

Ctrl + Z 필드를 잘못 옮겼을 때 되돌리기

자주 묻는 질문

Q1. 피벗 테이블 숫자에 천 단위 쉼표가 안 나와요.

원본에 쉼표 서식이 없으면 피벗 결과도 쉼표 없이 나와요. [값] 영역 ▼ > [값 필드 설정] > [표시 형식]을 눌러 숫자 > 1000 단위 구분 기호(,) 사용을 체크하세요. 셀 서식으로 바꾸면 필드를 옮기거나 새로 고칠 때 풀리는 경우가 있지만, 여기서 바꾸면 필드에 붙어서 계속 유지돼요.

Q2. 필드 목록 창이 사라졌어요.

피벗 표 바깥 셀을 클릭하면 필드 목록이 숨겨져요. 피벗 표 안의 셀을 다시 클릭하면 나타나고, 그래도 안 보이면 [피벗 테이블 분석] 탭 오른쪽 끝의 [필드 목록] 단추를 눌러 주세요.

예제 파일

오늘 쓴 3분기 지점별 판매 내역 14건을 그대로 받아서 연습해 보세요. 연습 시트 오른쪽의 순서대로 피벗 테이블을 만들어 보고, 정답 시트에서 새로 고침 후 결과(총합계 18,100,000)와 비교하면 돼요.

엑셀_피벗테이블_예제.xlsx

0.01MB

오늘의 한 줄 정리

[삽입] > [피벗 테이블]로 만들고, 기준은 [행]·[열], 숫자는 [값]에. 이름과 계산 방식은 [값 필드 설정], 원본을 고쳤으면 Alt + F5 새로 고침까지 하면 끝이에요.