피벗테이블은 많은 양의 데이터를 직접 계산식으로 정리하지 않고 합계, 개수, 평균, 비교, 기간별 현황 등을 빠르게 요약하는 기능입니다. 수천 개의 판매 데이터가 있어도 상품별 매출이나 지역별 판매량처럼 원하는 기준으로 표를 다시 구성할 수 있습니다. Microsoft도 피벗테이블을 데이터를 계산·요약·분석하고 비교와 추세를 확인하는 도구로 설명하고 있습니다.
1. 피벗테이블은 왜 사용할까?
2. 피벗테이블을 만들기 전 데이터 정리하기
3. 가장 쉬운 피벗테이블 만드는 방법
4. 행·열·값 영역을 이해하는 방법
5. 상품별 매출 합계를 한 번에 보는 방법
6. 지역별·월별 데이터를 비교하는 방법
7. 합계 대신 개수와 평균으로 바꾸는 방법
8. 원하는 데이터만 필터링하는 방법
9. 원본 데이터가 추가됐을 때 새로고침하는 방법
10. 피벗테이블을 사용할 때 자주 생기는 문제
11. 자주 묻는 질문
12. 마무리
1. 피벗테이블은 왜 사용할까?
판매 내역이 몇 줄 정도라면 직접 계산해서 정리할 수 있지만 데이터가 수천 개로 늘어나면 이야기가 달라집니다.
예를 들어 다음과 같은 판매 데이터가 있다고 해보겠습니다.
9/1 | 서울 | 키보드 | 3 | 135000
9/1 | 부산 | 마우스 | 5 | 125000
9/2 | 서울 | 모니터 | 2 | 500000
9/2 | 대구 | 키보드 | 4 | 180000
데이터가 수천 줄이라면 "서울에서 키보드가 얼마나 팔렸는지", "상품별 매출이 얼마인지", "월별 매출이 어떻게 변했는지"를 직접 계산하는 것은 번거롭습니다.
피벗테이블을 사용하면 이런 데이터를 필드를 끌어다 놓는 방식으로 원하는 형태로 다시 정리할 수 있습니다.
원본 데이터는 그대로 두고
필요한 기준만 선택해서 새로운 요약표를 만드는 것입니다.
2. 피벗테이블을 만들기 전 데이터 정리하기
피벗테이블을 만들기 전에 원본 데이터의 형태를 먼저 확인하는 것이 중요합니다.
Microsoft는 피벗테이블 원본 데이터를 열 형태로 구성하고 하나의 헤더 행을 사용하는 표 형식으로 정리할 것을 안내합니다.
날짜 | 지역 | 상품 | 수량 | 매출
9/1 | 서울 | 키보드 | 3 | 135000
9/2 | 부산 | 마우스 | 5 | 125000
9/3 | 대구 | 모니터 | 2 | 500000
• 중간에 빈 행을 넣는 경우
• 하나의 데이터 표에 여러 헤더 행을 사용하는 경우
• 같은 열에 숫자와 문자를 섞는 경우
• 하나의 셀에 여러 정보를 합쳐 넣는 경우
가능하면 원본 데이터를 Excel 표로 만들어 사용하는 것도 좋습니다. 데이터가 표로 구성되어 있으면 새 행이 추가된 뒤 피벗테이블을 새로 고칠 때 새 데이터가 범위에 포함되기 편리합니다.
3. 가장 쉬운 피벗테이블 만드는 방법
이제 실제로 피벗테이블을 만들어보겠습니다.
① 원본 데이터 안의 아무 셀이나 클릭합니다.
② 상단 메뉴에서 삽입을 선택합니다.
③ 피벗 테이블을 클릭합니다.
④ 원본 데이터 범위를 확인합니다.
⑤ 피벗테이블을 배치할 위치를 선택합니다.
⑥ 확인을 누릅니다.
그러면 새로운 워크시트에 빈 피벗테이블과 함께 피벗 테이블 필드 창이 나타납니다.
이제 원본 데이터의 열 이름을 원하는 영역에 넣으면 피벗테이블이 자동으로 만들어집니다.
상품 → 행
매출 → 값
이렇게 넣으면 상품별 매출 합계를 볼 수 있습니다.
Microsoft 공식 안내에서도 원본 범위를 선택한 뒤 삽입 → 피벗테이블을 선택해 피벗테이블을 만드는 방식으로 설명하고 있습니다.
4. 행·열·값 영역을 이해하는 방법
피벗테이블을 처음 사용할 때 가장 헷갈리는 부분이 바로 행·열·값·필터입니다.
어떤 기준으로 데이터를 아래로 나눌지 결정합니다.
예: 상품, 지역, 담당자
열
어떤 기준을 가로 방향으로 비교할지 결정합니다.
예: 월, 분기, 판매 유형
값
실제로 계산할 숫자를 넣습니다.
예: 매출, 수량, 비용
필터
전체 피벗테이블에서 원하는 조건만 골라 볼 때 사용합니다.
Microsoft도 피벗테이블 필드 목록에서 이 네 영역을 이용해 데이터를 재배치할 수 있다고 설명합니다.
행 = 세로로 분류
열 = 가로로 비교
값 = 계산할 숫자
필터 = 보고 싶은 조건만 선택
처음에는 행 + 값 두 가지만 사용하는 것으로 시작하면 어렵지 않습니다.
5. 상품별 매출 합계를 한 번에 보는 방법
가장 많이 사용하는 예제로 상품별 매출 합계를 만들어보겠습니다.
원본 데이터에 다음과 같은 열이 있다고 가정하겠습니다.
피벗테이블 필드에서 다음처럼 배치합니다.
값 → 매출
그러면 피벗테이블이 상품별로 매출을 자동으로 묶어 보여줍니다.
마우스 → 2,180,000원
모니터 → 5,420,000원
USB허브 → 1,630,000원
수천 개의 원본 데이터를 직접 필터링하거나 SUMIF 같은 함수를 여러 번 작성하지 않아도 상품별 합계표를 만들 수 있습니다.
상품 → 행
매출 → 값
이 기본 구조만 익혀도 피벗테이블의 절반 이상을 이해했다고 볼 수 있습니다.
6. 지역별·월별 데이터를 비교하는 방법
피벗테이블의 진짜 장점은 여러 기준을 동시에 넣어 데이터를 비교할 수 있다는 점입니다.
예를 들어 지역별 월간 매출을 보고 싶다면 다음과 같이 구성할 수 있습니다.
열 → 월
값 → 매출
그러면 다음처럼 지역과 월을 함께 비교할 수 있습니다.
서울 | 500만 | 620만 | 710만
부산 | 320만 | 410만 | 450만
대구 | 280만 | 360만 | 390만
이렇게 구성하면 어느 지역에서 매출이 높았는지뿐만 아니라 월별 변화까지 한눈에 확인할 수 있습니다.
상품별 판매량, 부서별 비용, 담당자별 실적처럼 기준을 바꿔가면서 같은 원본 데이터를 다양한 방식으로 분석할 수도 있습니다.
같은 원본 데이터라도 필드를 어떻게 배치하느냐에 따라 전혀 다른 요약표를 만들 수 있습니다.
7. 합계 대신 개수와 평균으로 바꾸는 방법
피벗테이블의 값 영역에 숫자를 넣으면 기본적으로 합계로 표시되는 경우가 많습니다.
하지만 항상 합계가 필요한 것은 아닙니다. 판매 건수나 평균 판매금액처럼 다른 방식으로 계산하고 싶다면 값 요약 기준을 바꾸면 됩니다.
① 피벗테이블의 값 영역에 있는 숫자를 마우스 오른쪽 버튼으로 클릭합니다.
② 값 요약 기준을 선택합니다.
③ 합계, 개수, 평균 등 원하는 계산 방식을 선택합니다.
예를 들어 매출을 합계로 보면 총매출을 확인할 수 있고, 평균으로 바꾸면 평균 판매금액을 확인할 수 있습니다.
합계 → 총매출
개수 → 주문 건수
평균 → 평균 판매금액
숫자가 아닌 텍스트 데이터가 값 영역에 들어가면 Excel이 이를 COUNT로 처리할 수 있으므로, 값 열의 데이터 형식을 일관되게 유지하는 것이 중요합니다.
8. 원하는 데이터만 필터링하는 방법
수천 개의 데이터를 모두 볼 필요가 없다면 필터 영역을 활용하면 됩니다.
예를 들어 전체 매출 데이터 중에서 서울 지역의 매출만 보고 싶다면 지역 필드를 필터 영역에 넣을 수 있습니다.
행 → 상품
값 → 매출
필터 → 지역
그다음 피벗테이블 상단의 지역 필터에서 서울을 선택하면 서울에 해당하는 데이터만 요약해서 확인할 수 있습니다.
여러 개의 조건을 동시에 사용하면 특정 지역의 특정 상품처럼 원하는 범위만 좁혀서 볼 수도 있습니다.
지역 → 서울
상품 → 키보드
기간 → 9월
→ 서울에서 9월에 판매된 키보드 데이터만 분석
필드 배치를 바꾸거나 필터를 변경하는 것만으로 같은 원본 데이터를 다른 기준으로 계속 분석할 수 있다는 것이 피벗테이블의 장점입니다.
9. 원본 데이터가 추가됐을 때 새로고침하는 방법
피벗테이블을 사용하다 보면 원본 데이터에 새로운 행이 추가되는 경우가 많습니다.
이때 원본 데이터가 변경됐다고 해서 피벗테이블이 항상 즉시 자동으로 업데이트되는 것은 아니므로 새로 고침이 필요할 수 있습니다.
① 피벗테이블 안의 아무 셀이나 클릭합니다.
② 마우스 오른쪽 버튼을 클릭합니다.
③ 새로 고침을 선택합니다.
여러 개의 피벗테이블을 한꺼번에 업데이트하려면 피벗 테이블 분석 → 새로 고침 → 모두 새로 고침을 이용할 수 있습니다.
Microsoft는 원본 데이터에 새 항목을 추가한 경우 해당 데이터 원본을 사용하는 피벗테이블을 새로 고쳐야 한다고 안내합니다.
원본 데이터를 Excel 표로 만들어두면 새 데이터를 추가했을 때 피벗테이블을 새로 고칠 때 새 행이 데이터 원본에 포함되기 편리합니다.
10. 피벗테이블을 사용할 때 자주 생기는 문제
피벗테이블은 편리하지만 처음 만들다 보면 결과가 예상과 다르게 나오는 경우가 있습니다.
값이 숫자가 아니라 텍스트로 인식됐을 가능성이 있습니다.
② 새로 입력한 데이터가 안 보인다
피벗테이블을 새로 고쳐야 하거나 원본 데이터 범위에 새 데이터가 포함되지 않았을 수 있습니다.
③ 원하는 형태의 표가 만들어지지 않는다
행·열·값·필터의 필드 배치를 다시 조정해보세요.
④ 빈 항목이 많이 보인다
원본 데이터에 빈 셀이나 불완전한 데이터가 있는지 확인합니다.
⑤ 같은 상품인데 여러 줄로 나뉜다
상품 이름의 띄어쓰기나 오타가 서로 다를 수 있습니다.
특히 피벗테이블이 이상하게 계산될 때는 수식을 수정하기보다 원본 데이터의 형식과 값부터 확인하는 것이 좋습니다.
원본 데이터의 헤더 → 빈 행·열 → 숫자 형식 → 동일한 항목 이름 → 피벗테이블 새로 고침
11. 자주 묻는 질문
A. 네. 피벗테이블은 많은 데이터를 계산하고 요약·분석하는 데 사용할 수 있습니다. 다만 데이터 양과 PC 환경에 따라 처리 시간은 달라질 수 있습니다.
A. 기본적인 피벗테이블을 만드는 데 복잡한 수식을 입력할 필요는 없습니다. 필드를 행·열·값·필터 영역에 배치하는 방식으로 만들 수 있습니다.
A. 가능합니다. 값 영역의 데이터를 마우스 오른쪽 버튼으로 클릭한 뒤 값 요약 기준에서 평균을 선택하면 됩니다.
A. 원본 데이터에 따라 다릅니다. 피벗테이블은 새 데이터가 추가된 뒤 새로 고침이 필요할 수 있습니다. 원본을 Excel 표로 구성하면 새 데이터를 포함해 새로 고치기 편리합니다.
A. 피벗테이블 자체를 삭제하는 것은 원본 데이터와 별개의 작업입니다. 원본 데이터를 보존하면서 피벗테이블만 제거할 수 있습니다.
12. 마무리
엑셀 피벗테이블은 수천 개의 데이터를 일일이 계산하지 않고 상품별·지역별·월별·담당자별로 빠르게 요약할 수 있는 기능입니다.
처음에는 복잡해 보이지만 기본 원리는 어렵지 않습니다.
삽입 → 피벗 테이블 → 행·열·값·필터 배치 → 원하는 기준으로 분석 → 새로 고침
가장 먼저 상품 → 행 / 매출 → 값 형태로 상품별 매출 합계를 만들어보면 기본 원리를 쉽게 익힐 수 있습니다.
그다음 지역이나 월을 열이나 필터에 추가하면 같은 데이터를 다양한 방식으로 분석할 수 있습니다.
특히 원본 데이터가 계속 추가되는 업무라면 Excel 표와 피벗테이블을 함께 사용해두면 반복적인 데이터 정리 작업을 줄이는 데 도움이 됩니다.
댓글 쓰기