번외 편 - Excel Pivot Table

피봇테이블은 데이터를 볼 때 꼭 필요하니까 넣어주는 걸로..

Jul 18. 2025

대부분의 회사원 그리고 100%의 온라인마케터 거기 더해서 거의 모든 소상공인 분들이 사용하실(제 개인적인 의견이기는 합니다만..) 엑셀에서의 기능을 한 가지 찾으라면 단연코 그 첫 번째 기능으로 피봇테이블(Pivot Table)을 들 수 있습니다.

물론, 다양한 함수나 정의나 그런 것들도 중요하고 사용해야 할 수 있지만, 복잡한 자료들을 직관적이고 빠르게 대부분을 자동화해서 정리된 상태로 보여주는 엑셀에서의 훌륭한 데이터 정리도구인 Pivot에 대해서 잠시 이야기를 하고 가는 것이 좋을 것 같아서 번외로 간단하게라도 설명을 드리고자 합니다.

엑셀교육을 과거에는 회사에서 좀 했었는데, 최근에는 회사에서 진행하는 엑셀교육이 잘 없다 보니, 엑셀사용법에 대해서(특히 피벗테이블) 좀 더 설명을 들을 수 없었다는 점을 아쉽게 생각하시는 분들이 계셔서 살짝만 맛보기로 넣어서 현업에서 사용하시는 데에는 큰 불편함이 없으시도록 간단하게 말씀드리고 다음 통계 부분에 대한 설명을 진행할까 합니다.

간단하게 설명드리면 엑셀의 피벗 테이블(Pivot Table)이라는 도구는 기존에 만들어 놓은(하지만, 중요한 점은 피벗테이블용 데이터 형태로 만들어져 있어야만 분석이 된다는 점입니다. 처음부터 데이터를 분석할 수 있도록 수집하고 정리해야 나중에 한 개 한 개 손으로 데이터를 고치는 수고를 덜어낼 수 있으니 처음부터 정리하고 축적되는 형태!! 가 중요한 점을 잊지 마세요) 수천 건의 데이터를 빠르게 요약하고, 시각화하며, 데이터 기반의 인사이트를 도출하는 데 매우 효과적인 도구입니다.

마케팅, 회계, 영업, 교육, 제조 등 거의 모든 업계에서 광범위하게 사용되고 있으며, 초보자도 비교적 쉽게 접근할 수 있습니다. 저는 엑셀 2016을 기준으로 피벗 테이블의 기초 개념부터 제 개인적으로는 중급 수준의 실무 응용까지 가능한 한 설명드리며, 실제 스크린샷과 예시를 통해 보다 쉽게 이해할 수 있도록 해보겠습니다.

1. 피벗 테이블이란 무엇인가?

1) 개념 정의

피벗 테이블은 원본 데이터를 분석 가능한 형태로 요약, 집계, 비교할 수 있도록 해주는 엑셀의 기본적인 통계지원 기능으로 복잡한 수식을 몰라도 몇 번의 클릭으로 전체적인 자료의 대표성을 보여줄 수 있는 통계 결과를 만들 수 있습니다.

예를 들어

(1) 매출 데이터를 월/일자별/거래선별로 구분하여 총매출액을 분석(하단 예시)

(2) 고객 데이터를 연령대, 성별 기준으로 분류해 구매행동 패턴 파악

2) 피벗 테이블의 장점

(1) 빠른 분석: 수천 건, 혹은 수만건의 데이터를 실시간 요약가능

(2 ) 다양한 방식 요약: 합계, 평균, 개수, 최대/최소값 등

(3) 드래그 앤 드롭 인터페이스: 복잡한 수식 없이 변수이동으로 분석가능

(4) 자동 새로고침 가능: 데이터 업데이트 시 반영 가능

2. 피벗 테이블 생성하기

1) 데이터 준비

피벗 테이블은 정형화된 데이터 테이블을 기반으로 작동합니다. 이 형식을 지키지 않을 경우에는 피벗테이블 분석이 되지 않으므로 다음 사항을 고려해서 데이터를 만들어야 합니다.

(1) 첫 번째 행에는 열 이름(헤더)이 있어야 합니다.(예를 들어 위 표에 이용 날자, 거래서.. 이런 것들)

(2) 빈 행/열이 없어야 함(가끔 어떤 셀은 비어있어도 분석이 가능합니다)

(3) 각 열의 데이터 형식은 일관되어야 함(이용 날 자에는 일자만, 거래금액에는 숫자로만 등)

2) 피벗 테이블 만들기 (단계별)

(1) 데이터 범위 선택 (예: A1:G63)

(2) 메뉴 리본에서 삽입 → 피벗 테이블 클릭

'새 워크시트' 또는 '기존 워크시트' 선택 → 확인 클릭

가끔 내가 정리한 데이터가 있는 그 페이지에 피벗테이블을 넣고 싶을 때는 기존워크시트에 어느 셀부터 넣을 것인지를 기존워크시트를 선택하고 위치를 첫 번째 셀주소로 지정하면 거기부터 아래로 만들어집니다.

보통은 새로운 워크시트로 만드는 게(옆에 새로운 시트로 추가됩니다) 더 편하긴 해서 주로 그냥 새 워크시트로 사용합니다. 이렇게 설정하고 확인을 누르면 다음과 같은 그림이 나옵니다.

이렇게 빈 피벗테이블이 나오면 이제부터는 내가 원하는 분석하고 싶은 변수들을 오른쪽 아래 필터와 열, 행, 값에 넣어서 보기만 하면 됩니다. 이제부터 간단한 부분이지만 더 설명해 드리도록 하겠습니다.

3. 피벗 테이블 구성 요소 이해하기

1) 필드 영역 설명

피벗 테이블은 다음 4가지 영역으로 구성됩니다:

행(Row): 분석할 항목을 세로축 기준으로 나열 (예: 이용 날자)

열(Column): 항목을 가로축 기준으로 나열 (예: 거래처)

값(Values): 수치를 요약 (예: 순매출 합계)

필터(Filter): 특정 항목만 추려서 보여줌 (예: 결재구분)

2) 실전 예시 구성

행: 월, 이용날짜

열: 거래처

값: 합계 순매출

필터: 결재구분

4. 요약 방식 변경과 사용자 정의 계산

1) 기본 요약 방식: 합계(Sum)

피벗 테이블은 수치 데이터를 기본적으로 합계로 요약합니다.

2) 요약 방식 변경 방법

값 필드 옆 ▼ 클릭 → 값 필드 설정을 클릭하면

값 필드설정 테이블이 나오고 이 중에서 합계, 개수, 평균, 최대/최소값, 곱, 표준편차, 분산 등에서

본인이 원하는 데이터형태를 선택할 수 있습니다.

3) 사용자 지정 계산식 추가방법

예를 들어 사용자가 기존에 있는 피봇테이블 변수를 가지고 추가적인 계산식을 만들어 넣을 수도 있는

데 현재 Data에 순매출액과 거래물량이 있으므로 메뉴바에 피벗테이블 분석을 클릭하면

계산필드가 나오고 그곳을 클릭하면 계산필드 삽입이라는 팝업테이블이 뜹니다.

거기에 내가 원하는 계산 변수를 넣어주면 되는데, 앞서 보셨던 것처럼 물량과 금액을 알고 있으니 평균단가라는 계산식을 만들어보도록 하겠습니다.

우선 이름에 평균단가를 넣고, 수식란에 = 순매출액/거래물량을 넣고 추가를 누릅니다. 그러고 나면 아래쪽에 평균단가라는 필드가 생긴 것을 볼 수 있습니다. 그리고 확인을 누르면

다음과 같이 평균단가가 들어간 합계를 볼 수 있게 됩니다.

4) 피봇테이블 기타 설정 및 옵션

피벗테이블에 마우스를 위치하고 오른쪽 클릭을 하게 되면 다음과 같은 창이 열리게 됩니다.

그 창에서 다양 한 옵션의 선택과 조정을 할 수 있는데 이 부분은 한 번씩 들어가서 찾아보면 쉽게 익숙해

지실 수 있다고 생각됩니다.

예를 들어 피벗테이블 위에 마우스를 놓고 오른쪽 클릭을 하면 위와 같은 팝업창이 뜨는데 맨 위에 있는 복사, 셀서식 등은 일반적인 엑셀의 메뉴와 동일하고 정렬 또한 숫자오름차순, 내림차순 등으로 동일합니다.

합계 순매출액 제거는 말 그대로 합계에 있는 순매출액을 제거하는 것이라고 보시면 되고 보통 피벗테이블에서 의미 있는 메뉴는 값 요약기준부터입니다.

값 요약기준은 위 표에서 보시다시피, 합계나 개수 평균, 최대값, 최소값, 곱 등이 가능한데 이 기준들은 한 번씩 클릭하셔서 어떤 값이 나오는지 한번 살펴보시면 좋겠습니다. 저는 개인적으로 합계, 개수, 평균까지는 자주 사용하는데 최대, 최소, 곱등은 잘 사용하지는 않습니다.

간단하게 예를 들면 합계, 최대값과 최소값을 선택하게 되면 1월 26일 송대관 상사에 합계일 때 20,000이 일 때 1월 26일 16,000 4,000의 순매출이 있어서 합계는 20,000이 나왔다면 최대는 16,000 최소는 4,000이 나타나게 됩니다.

아래에 있는 기타 옵션을 누르게 되면 아래와 같은 테이블이 나타나고 필드데이터의 바를 아래로 내리면 숫자개수, 표준편차, 분산등을 선택할 수도 있습니다. 그리고 이 테이블에서 표시형식까지 선택할 수 있고 표시형식은 일반적인 엑셀에서의 표시형식과 동일합니다.

다음에 있는 메뉴로는 값표시형식이 있는데 이 메뉴를 클릭하면 다음과 같은 팝업이 뜹니다.

모든 메뉴에 대한 설명은 장황하게 드려봤자, 기억을 하시기는 어려울 것 같고 일단 총합계 비율, 열합계비율, 행합계 비율등 가장 많이 사용하는 비율들 정도는 직접 해보시면 이해가 빠르실 것 같습니다. 저는 여기에서 총합계 비율을 한번 해 보겠습니다.

총합계 비율을 클릭하면, 바로 위 표와 같이 전체를 기준으로 한 비율변환이 됩니다. 일자별로 보면 큰 의미가 없을 수도 있지만 변수로 나눠 보거나 월별로 바꾸게 되면 비중의 변화등을 파악하는데 도움이 될 것이라고 생각됩니다.

그리고 마지막으로 피벗데이터 옵션이 있는데 그 옵션 중에서 나머지 옵션들은 클릭을 해보시면 들어가는 것과 빠지는 것이 있어서 확인이 가능합니다. 그리고 그다지 이 옵션에서 따로 사용하는 부분은 많지 않은데 한 가지 옵션은 많이 사용하는 편이어서 설명드립니다.

위 선택상에서 클래식 피벗 테이블 레이아웃 표시라는 항목이 있습니다. 이는 과거 피벗테이블로부터 피벗테이블이 발전해 오면서 살짝 바뀐 부분을 예전 버전의 형식으로 바꿔주는 것인데 큰 차이는 다음과 같습니다. 위에 표에 보시면 1월 아래에 1월 26일로 넘어가면서 행이 바뀐 것을 보실 수 있습니다.

보기도 좋고, 이해하기가 좋은데 이 피벗 데이터를 다시 다른 데이터로 활용하기 위해서는 이 형태가 좀 애매할 때가 있습니다. 행이 안 바뀌고 그냥 옆으로 붙으면 좀 더 편할 수 있는 경우들이 있죠, 그럴 때 사용하는 옵션으로 저는 피벗을 많이 사용하고 피벗을 한 데이터로 또 피벗을 하는 경우도 많이 있기 때문에 이런 경우에 많이 사용합니다(오른쪽 테이블을 살펴보시면 되겠습니다).

이 옵션을 적용하면 전에 보셨던 왼쪽 데이터와 오른쪽 테이블로 달라지는 것을 보실 수 있습니다.

5. 그룹화 기능으로 더 정교한 분석하기

1) 날짜 그룹화

날짜 데이터를 연도/분기/월/일 단위로 자동 그룹화가 가능합니다. 사용 방법은 날짜 필드를 행에 넣은 후 우클릭 → 그룹 → 원하는 단위 선택하면 그룹이 이루어집니다. 아래 표에서는 이용 날자 셀에 마우스를 위치시키고 오른쪽 클릭을 하면 팝업이 뜨고 거기에서 그룹을 선택하면

이런 팝업이 또 뜹니다.

여기에서, 월을 선택하고 확인을 누르면 다음과 같이 그룹이 된 결과가 나타나게 됩니다.

이렇게 그룹화할 수 있기는 하지만 잘 사용은 안 하는 것 같기는 합니다. 처음부터 Data에 월별데이터를 월과 일 시간 등으로 구분해서 넣어두고 필요할 때마다 월을 선택하고 빼고 할 수 있도록 만드는 것이 더 편리하기 때문에 최초에 데이터셋을 구성할 때 먼저 월을 데이터에 넣어서 원하는 대로 설정하는 것이 분석할 때 보다 편리합니다.

2) 필요한 데이터 선택

이 장에 넣어야 할지를 고민한 부분인데, 모두 하실 줄 아시겠지만 데이터 중에서 필요한 자료만 선택하는 기능이 피벗테이블에는 있습니다. 어떤 변수건 개별적으로 선택해서 보다 세부적인 분석이 가능합니다.

필터를 사용하여 어떤 부분에 대한 선택만을 할 수 있도록 하기도 하고, 데이터 자체에서 몇 개의 조건을 충족하는 데이터 만을 분석하기도 합니다.

간단하게 사례를 보여드리도록 하겠습니다. 먼저 필터를 사용하는 방법은 다음과 같습니다.

저는 필터에 결재구분이라는 데이터를 넣어두었습니다. 이 결재구분은 맨 앞에 있는 데이터를 보면 3가지의 종류로 구분되어 있습니다. 외상결재, 카드결제, 현금결제 이렇게 말입니다. 현재는 어떤 데이터만을 고르지 않았기 때문에 모두 나오는 상황인데 이 필터(결재구분 (모두))오른쪽에 ▼ 을 클릭하면 위와 같은 팝업이 뜹니다. 이 팝업에서 여러 항목 선택을 클릭하면 위에서는 보지 않던 개별 항목을 선택할 수 있는 선택박스가 생기게 되고 현재는 모두이므로 모두 선택되어 있는데 그중에서 몇 개를 선택하거나 선택에서 제외시킬 수 있습니다.

저는 그래서 이번에는 현금결제를 클릭하여 선택에서 제외하고 확인을 누르면 현금결제 한 실적을 제외한 외상결제와 카드결제만의 순매출이 보이게 됩니다. 아래 테이블에서 보시면 결재구문이 모두에서 다중항목으로 바뀐 부분과 금액이 달라진 것이 보이시죠?

필터로도 이렇게 필요한 데이터를 넣고 뺄 수 있지만 각 데이터 레이블에서도 가능합니다. 예를 들어 행레이블의 옆에 있는 ▼ 을 클릭하면 아래와 같은 창이 열리고 그곳에서 앞서 필터에서 했던 것처럼 필요한 데이터에만 클릭을 남겨놓고 필요 없는 데이터의 체크박스에 선택을 클릭하면 없어집니다. 같은 방식으로 열레이블의 ▼ 을 클릭하면 동일하게 박스가 나오고 이곳에서 필요한 거래선만 선택을 설정할 수 있습니다.