엑셀 피벗테이블 사용법 부서별, 업체별 금액을 한눈에 정리하는 방법

많은 자료를 엑셀로 정리하다 보면 수백 건의 데이터를 하나씩 계산하기보다 조건별로 묶어서 한눈에 보고 싶은 경우가 있습니다.

부서별 비용은 얼마인지, 업체별 미수금은 얼마인지, 월별 매출이 어떻게 되는지, 담당자별 처리 건수가 몇 건인지 처럼 많은 데이터를 기준별로 정리해야 할 때가 있습니다. 이럴 때 유용한 기능이 엑셀 피벗테이블입니다.

저는 회사에서 거래처별 미수금 자료를 확인하거나 금액 현황을 정리할 때 피벗테이블을 자주 사용합니다.

회사 자료를 그대로 보여드릴 수는 없지만, 실제 업무에서 어떤 식으로 활용하는지 이해할 수 있도록

예시를 만들어 정리해보겠습니다.

---

1. 피벗테이블이란?

피벗테이블은 많은 양의 데이터를 원하는 기준으로 묶어서 합계, 평균, 개수 등을 빠르게 확인할 수 있도록 정리해주는 기능입니다.

다음과 같은 원본 데이터가 있다고 가정해보겠습니다.

날짜	부서	업체명	담당자	금액
3/2	영업팀	A업체	김대리	500,000
3/5	영업팀	B업체	이대리	800,000
3/8	관리팀	C업체	박대리	300,000
3/12	영업팀	A업체	김대리	700,000
3/15	관리팀	D업체	최대리	400,000

이 자료를 피벗테이블로 만들면

부서별 금액 또는 업체별 금액 또는 담당자별 금액 처럼 원하는 기준으로 바로 요약할 수 있습니다.

 

---

2. 피벗테이블을 만들기 전에 원본 데이터부터 확인하기

피벗테이블은 원본 데이터가 깔끔하게 정리되어 있어야 제대로 사용할 수 있습니다.

가장 중요한 것은 한 행에 하나의 데이터 기록이 들어가도록 만드는 것입니다.

원본 데이터는 이런 형태가 좋습니다.

 

피벗테이블 예시 원본 데이터

 

첫 번째 행에는 반드시 열 제목이 있어야 합니다.

그리고 가능하면 아래 내용을 지키는 것이 좋습니다.

• 중간에 빈 행을 넣지 않기
• 중간에 빈 열을 넣지 않기
• 셀 병합하지 않기
• 하나의 열에는 같은 종류의 데이터 넣기

---

3. 피벗테이블 만드는 방법

① 원본 데이터 안에 커서를 놓습니다.

원본 데이터 전체를 직접 드래그해서 선택해도 되지만, 데이터 안의 아무 셀이나 클릭한 후 피벗테이블을 만들면 범위를 자동으로 인식할 수 있습니다.

---

② 삽입 → 피벗 테이블을 선택합니다.

상단 메뉴에서

삽입 → 피벗 테이블

을 선택합니다.

그러면 피벗테이블을 만들 데이터 범위를 확인하는 창이 나타납니다.

범위가 제대로 잡혔는지 확인한 후 확인을 누릅니다.

 

피벗테이블을 누르면 나오는 창

---

③ 피벗테이블 필드에서 원하는 항목을 배치합니다.

피벗테이블을 만들면 오른쪽에 필드 목록이 나타납니다.

여기에서 필요한 항목을

• 필터
• 열
• 행
• 값

영역으로 끌어다 놓으면 됩니다.

 

피벗테이블 필드

 

예를 들어 부서별 금액을 보고 싶다면

부서 → 행

금액 → 값

으로 넣습니다.

그러면 다음과 같은 형태로 자동 집계됩니다.

 

 

피벗테이블 보는법 예시

 

피벗테이블은 필드를 행·열·값 등의 영역으로 이동하면서 원하는 형태의 요약표를 만들 수 있습니다.

---

4. 합계가 아니라 평균이나 개수로 보고 싶다면?

처음 피벗테이블에 금액을 넣으면 보통 합계로 계산됩니다.

하지만 필요한 경우

• 합계
• 평균
• 개수
• 최대값
• 최소값

등으로 변경할 수 있습니다.

값 영역의 항목을 클릭한 후 값 필드 설정 → 값 요약 기준 에서 원하는 계산 방식을 선택하면 됩니다.

피벗테이블 값을 합계에서 평균으로 변경했을 때

 

 

예를 들어 직원별 급여 자료가 있다면 급여 → 평균 으로 설정해서 부서별 평균 급여를 확인하는 방식으로 사용할 수 있습니다.

 

---

5. 제가 피벗테이블을 자주 사용하는 업무

제가 회사에서 피벗테이블을 사용하는 것 중 하나가 미수금 금액을 확인하는 업무입니다.

거래처가 여러 곳이고 날짜별 거래 내역이 계속 쌓이면 원본 자료만 보고서는 전체 금액을 한눈에 확인하기 어렵습니다.

이럴 때 거래처, 날짜, 금액 등의 데이터를 기준으로 피벗테이블을 만들어두면 업체별 미수금, 날짜별 금액, 거래처별 누적 금액 등을 빠르게 확인할 수 있습니다.

회사 자료라 실제 자료를 공개할 수는 없지만, 예를 들어 다음과 같은 원본 데이터가 있다고 생각하면 됩니다.

날짜	업체	금액
3/2	A업체	500,000
3/5	B업체	800,000
3/8	A업체	300,000
3/12	C업체	700,000
3/15	A업체	400,000

업체명을 행에 넣고 금액을 값에 넣으면 업체별 금액을 바로 확인할 수 있습니다.

이런 자료는 보고서에 필요한 숫자를 먼저 빠르게 확인하고, 이후 세부 내용을 천천히 정리할 때 특히 편합니다.

---

6. 날짜가 많다면 월별로 묶어보세요

날짜 데이터를 그대로 행 영역에 넣으면 날짜가 하나씩 표시되어 자료가 길어질 수 있습니다.

예를 들어

3월 1일
3월 2일
3월 3일
3월 4일
…

처럼 표시되는 대신 월 단위로 묶어서 보는 방식을 사용할 수 있습니다.

피벗테이블의 날짜 항목을 선택한 후 그룹 기능을 이용하면 월·분기·연도 등 원하는 기준으로 날짜를 묶어서 볼 수 있습니다.

 

예를 들어 월별 매출 자료라면

 

월	매출
1월	10,000,000
2월	12,500,000
3월	9,800,000

처럼 월별 현황을 한눈에 확인할 수 있습니다.

---

7. 데이터를 추가했다면 반드시 새로 고침

피벗테이블을 만든 후 원본 데이터에 새로운 행을 추가했다고 해서 기존 피벗테이블에 항상 바로 반영되는 것은 아닙니다.

원본 데이터가 추가됐다면 피벗테이블에서 마우스 오른쪽 버튼 → 새로 고침 을 선택해 데이터를 업데이트합니다.

또는 피벗테이블을 선택한 상태에서 Alt + F5를 사용할 수도 있습니다. 

그래서 제가 피벗테이블을 사용할 때는

원본 자료 수정 → 피벗테이블 새로 고침 → 결과 확인 순서로 확인합니다.

보고서를 제출하기 전에 새로 고침을 한 번 확인하는 것이 좋습니다.

---

8. 원본 데이터를 표로 만들어두면 편합니다

원본 데이터를 선택한 후 Ctrl + T 를 누르면 엑셀 표로 변환할 수 있습니다.

데이터를 표로 만들어 피벗테이블의 원본으로 사용하면 이후 데이터를 추가할 때 관리하기가 편합니다.

 

다만 데이터를 추가했다고 바로 피벗테이블 화면이 바뀌는 것으로 생각하지 말고 새로 고침까지 확인하는 것이 좋습니다.

---

9. 슬라이서를 사용하면 보고서가 편해집니다

피벗테이블을 보고서 형태로 만들어야 한다면 슬라이서도 유용합니다.

피벗테이블을 선택한 후 피벗 테이블 분석 → 슬라이서 삽입 을 선택합니다.

예를 들어 부서 필드를 슬라이서로 만들면 영업팀 / 관리팀 / 인사팀 등의 버튼이 나타나고 원하는 부서를 클릭해서 데이터를 빠르게 필터링할 수 있습니다.

슬라이서는 현재 어떤 항목이 필터링되어 있는지도 화면에서 확인하기 쉽다는 장점이 있습니다.

특히 팀장이나 다른 사람이 직접 자료를 확인해야 하는 보고서라면 활용도가 높습니다.

 

피벗테이블 만들고 나서 피벗테이블 도구로 가면 분석에 슬라이서 삽입이 있다.

피벗테이블 슬라이서 삽입을 부서와 금액으로 하면 이렇게 나온다.

---

10. 피벗테이블의 특정 금액을 다른 셀에서 가져오고 싶다면

피벗테이블에서 계산된 값을 다른 셀에서 사용해야 할 때는 GETPIVOTDATA 함수를 사용할 수 있습니다.

예를 들어 피벗테이블이 A3부터 만들어져 있고, 값 필드가 금액이라면 다음과 같이 입력할 수 있습니다.

 

=GETPIVOTDATA("금액",A4)

 

여기서 A4는 피벗테이블 안에 있는 셀입니다.

피벗테이블의 값 필드가 합계라면 합계 금액을, 평균으로 설정되어 있다면 평균 금액을 가져옵니다.

 

피벗테이블과 함께 사용하는 함수 GETPIVOTDATA

 

또한 피벗테이블의 셀을 참조할 때 =을 입력한 후 원하는 피벗테이블의 값을 클릭하면 GETPIVOTDATA 수식이 자동으로 만들어질 수 있습니다.

 

다만 피벗테이블의 위치나 필드 구성이 바뀌면 수식 결과가 달라질 수 있기 때문에, 처음 사용할 때는 수식이 어떤 피벗테이블을 기준으로 만들어졌는지 확인하는 것이 좋습니다.

---

11. 피벗테이블에서 자주 생기는 문제

① 금액이 합계가 아니라 개수로 나오는 경우

금액 열인데 피벗테이블에서 개수로 표시된다면 원본 데이터의 일부가 숫자가 아닌 텍스트로 저장되어 있는지 확인해보세요.

피벗테이블은 데이터 형식을 기준으로 값을 요약하기 때문에 숫자와 텍스트가 섞여 있으면 예상과 다른 결과가 나올 수 있습니다.

---

② 새로 입력한 데이터가 피벗테이블에 나오지 않는 경우

가장 먼저 새로 고침을 확인합니다.

원본 데이터에 새로운 행을 추가한 뒤 피벗테이블을 새로 고치지 않았다면 기존 결과가 그대로 보일 수 있습니다.

---

③ 원본 데이터에 병합 셀이 있는 경우

피벗테이블의 원본 데이터는 가능한 한 일반적인 표 형태로 유지하는 것이 좋습니다.

특히 열 제목을 여러 줄로 만들거나 병합 셀을 사용하는 것보다 첫 번째 행에 각각의 열 제목을 넣고 아래로 데이터를 쌓는 방식이 관리하기 편합니다.

---

12. 피벗테이블을 처음 사용하는 분이라면 이것부터 기억하세요

피벗테이블은 처음 보면 오른쪽의 필드 영역 때문에 복잡해 보일 수 있습니다.

하지만 기본 구조는 생각보다 단순합니다.

부서별 금액을 보고 싶다면

부서 → 행

금액 → 값

업체별 금액을 보고 싶다면

업체명 → 행

금액 → 값

월별 금액을 보고 싶다면

날짜 → 행

금액 → 값

그리고 필요에 따라 필터나 슬라이서를 추가하면 됩니다.

결국 피벗테이블의 핵심은 어떤 데이터를 어떤 기준으로 묶어서 볼 것인지 결정하는 것입니다.

---

13. 제가 피벗테이블을 사용하는 이유

회사에서 자료를 만들다 보면 처음부터 완성된 보고서 형태로 데이터를 만들려고 하면 시간이 오래 걸릴 때가 있습니다.

저는 그럴 때 먼저 원본 데이터를 기준으로 피벗테이블을 만들어 숫자의 전체적인 흐름부터 확인하는 편입니다.

예를 들어 미수금 자료라면 업체별 금액을 먼저 확인하고, 그다음 필요한 날짜나 거래처를 다시 확인합니다.

피벗테이블을 만들어두면 보고서의 형태를 바꾸는 것도 어렵지 않습니다.

업체별로 보다가 부서별로 보고 싶다면 필드 위치만 바꾸면 되고, 전체 금액이 궁금하면 합계를 확인하면 됩니다.

그래서 반복적으로 숫자를 집계해야 하는 업무에서는 피벗테이블을 한 번 만들어두는 것만으로도 자료를 확인하는 시간이 많이 줄어듭니다.

---

14. 피벗테이블 핵심 정리

처음 피벗테이블을 사용할 때는 아래 4가지만 기억해도 충분합니다.

① 원본 데이터를 표 형태로 정리하기

↓

② 삽입 → 피벗테이블

↓

③ 필요한 필드를 행·열·값 영역에 배치하기

↓

④ 원본 데이터가 바뀌면 새로 고침하기

여기에 익숙해지면

• 날짜 그룹화
• 슬라이서
• 피벗 차트
• GETPIVOTDATA

등을 추가해서 조금 더 편리한 보고서를 만들 수 있습니다.

---

함께 보면 좋은 엑셀 글

[직장인 실무/엑셀] - 사원명단, 급여, 거래처 자료등 연결하기VLOOKUP

사원명단, 급여, 거래처 자료등 연결하기 VLOOKUP

 

[직장인 실무/엑셀] - 엑셀 COUNTIF 활용법 조건에 맞는 건수 자동으로 세는 방법

엑셀 COUNTIF 활용법 조건에 맞는 건수 자동으로 세는 방법

 

[직장인 실무/엑셀] - 엑셀 SUMIFS / COUNTIFS 사용법 여러 조건으로 매출, 건수 집계하기

엑셀 SUMIFS / COUNTIFS 사용법 여러 조건으로 매출, 건수 집계하기

 

피벗테이블과 함께 익혀두면 회사에서 반복적으로 하는 데이터 집계 업무에 활용하기 좋습니다.