엑셀 피벗테이블 - 기본사용법

들어가기

엑셀의 피벗테이블은 데이터 집계, 분석 등 아주 강력한 기능을 제공한다. 사용하기 쉬운데도 불구하고 많은 사람들이 어려워 하는데 사실 그렇지 않다. 조금만 익히면 피봇테이블이 선사하는 신세계를 경험할 수 있으며 많은 시간을 아낄 수 있다.

먼저 "피벗(pivot)"이라는 이 낯선 이름부터 알아보자. 피벗이라는 이름이 "피벗테이블"을 어려워 보이게 하는데 한몫하는 듯하다.

아래의 이미지를 보면 모니터가 돌아가는데, 중심축을 기준으로 가로/세로로 돌릴 수 있다고 하여 "피벗모니터"라고 부른다.

출처 : https://www.lge.co.kr/monitors/32un880

 

위키백과사전에는 다음과 같이 설명하고 있다.

정리를 해 보면 어떤 축을 중심으로 물체가 회전하는 행동 또는 아래와 같이 데이터를 정렬/재정렬하거나 회전시켜서 뭔가 새로운 집계표를 만드는 것 쯤으로 유추할 수 있겠다.

 

피벗테이블을 이용하여 데이터를 집계했을 때의 결과

아래와 같이 판매실적 데이터가 있다고 할 때 피벗테이블 기능을 이용하면

다음과 같은 "거래처별 판매금액 합계"를 클릭 몇번으로 만들 수 있다.

피벗테이블에 세부필드를 추가하여 세부필드의 합계도 구할 수 있다.

만약 피벗테이블 이용하지 않고 위와 같은 결과를 구하기 위해서는 아래와 같이 SUMIF, SUMIFS 등의 함수를 이용할 수 있는데, 합계 조건이 바뀐다면 수식을 바꿔줘야 하는 불편함이 있고 다양한 분석을 하기에는 한계가 있다.

=SUMIFS(H2:H34,B2:B34,"가양 아트박스")

=SUMIFS(H2:H34,B2:B34,"가양 아트박스",C2:C34,"노트")

피벗테이블을 이용하면 마우스클릭만으로 합계조건을 바꾸고 다양한 방식으로 집계결과를 구할 수 있다.

 

피벗테이블을 만들 "원본데이터" 준비

피벗테이블 기능을 이용하려면 다음과 같은 형태로 "원본데이터"가 준비되어 있어야 한다.

• 데이터의 첫줄에는 제목(타이틀)이 있어야 하고
• 중간에 집계행, 빈행, 빈열이 없어야 하고
• 병합된 셀이 없어야 한다

 

피벗테이블 만들기

위와 같은 데이터가 준비되었다면 다음 순서로 피벗테이블을 만든다.

 ※ 별도의 자료가 없다면 이 글의 실습용자료를 다운로드해서 이용한다.

1) 데이터 범위 지정

피벗테이블을 만들 원본데이터 범위를 마우스로 선택한다.

데이터의 시작열을 클릭한 후 데이터의 끝열까지 당겨서 선택한다.

 

2) 피벗테이블 삽입

데이터 범위 선택 후

• 리본메뉴의 "삽입" 탭을 선택한 후
• "피벗테이블" 메뉴를 클릭한다.

 

"피벗테이블" 메뉴를 클릭하면 다음과 같은 팝업화면이 나타난다.

• 세번째로 원본데이터 범위가 제대로 선택되었는지 확인하고
• 네번째로 피벗테이블을 넣을 위치를 선택한다. "기존 워크시트"에 넣어도 되지만 "새 워크시트"에 넣는 것이 공간이 넓어서 작업이 편리하므로 "새 워크시트"를 선택하고

"확인" 버튼을 클릭한다.

 

다음으로 아래와 같이 새로운 워크시트가 추가된다.

대략 난감... 뭘 어찌 해야 할지 감이 안온다.

뭔지 모르겠고 어려워 보이지만, 이제부터 클릭 몇 번만 하면 된다.

 

3) 피벗테이블 필드 선택

이제 피벗테이블의 마법을 볼 시간이다.

아래 그림의 오른쪽 위의 필드 "거래처명"과 "합계(a*b)"를 체크하면, 필드가 오른쪽 아래 행과 값의 영역으로 이동하고

자동으로 왼쪽에 거래처별 판매금액 합계가 구해진 것을 볼 수 있다.

 

4) 피벗테이블 필터 사용하기

피벗테이블이 만들어지면 아래와 같이 필터를 사용할 수 있는 버튼이 생긴다.

클릭하면 평상시 많이 쓰는 필터를 피벗테이블에서도 동일한 방식으로 사용할 수 있다.

원하는 항목을 선택하고 "확인"버튼을 클릭하면 필터링된 "피벗테이블"을 확인할 수 있다.

 

피벗테이블 조정하기

지금까지는 간단한 피벗테이블 기능만 맛 보았다. 실무에 적용하기에는 아쉬움이 있다.

실무에서 쓸모 있게 사용하기 위해 피벗테이블을 조정해 보자.

1) 하위필드 추가하기

"거래처별 합계" 외에 거래처 하위에 "상품분류" 필드를 추가하여 상세합계를 구해보자.

오른쪽 상단에서 "상품분류"를 체크한다. 피벗테이블에 "상품분류" 필드가 자동으로 추가된 것을 확인할 수 있다.

그런데 이렇게 보여서는 그다지 쓸모가 없다.

 

추가한 "상품분류" 필드의 위치를 좀 바꿔보자.

아래 그림과 같이 "상품분류"를 클릭하여 "열"영역에 끌어다 놓는다.

 

"상품분류"가 행에서 열로 바뀐 후 피벗테이블은 다음과 같이 바뀐다. 거래처별로 상품분류별 판매금액 합계가 일목요연하게 잘 정리된 것을 알 수 있다.

 

이번에는 "월" 필드를 열 영역으로 이동시켜 보자. 자동으로 거래처별,월별 판매금액 합계가 표시된 것을 알 수 있다.

※ 원래 "월"필드는 원본데이터에 없는 필드이다. "판매일자"등 날짜필드가 존재하면 자동으로 "월"필드가 추가된다.

"상품분류" ,"월" 필드 뿐만 아니라 다른 필드도 행과 열을 이동해 보면 다양한 형태로 데이터를 분석할 수 있다.

 

2) 피벗테이블의 구조

지금까지는 그냥 따라해 보기만 했지만, 피벗테이블을 제대로 사용하려면 피벗테이블의 구조를 이해할 필요가 있다.

피벗테이블은 아래 그림과 같이 필터, 행, 열, 값의 구조로 되어 있으며 다음의 순서로 만들 수 있다.

① 원하는 피벗테이블 필드를 마우스를 체크하면 필드가 화면의 아래쪽으로 이동하고 동시에 화면의 왼쪽에 행,열, 합계가 표시된다.

체크된 필드는 다음과 같은 규칙에 의해 아래로 이동한다.

• 숫자 필드 : "값" 영역으로 이동하고 "합계"를 기본으로 표시해 준다
• 숫자가 아닌 필드 : 기본적으로 "행" 영역으로 이동, "행"영역의 필드는 다른 영역으로 이동 가능
• 날짜 필드 : "행" 영역으로 이동하고 동시에 "월" 항목이 자동으로 생긴다

② 좀 더 다양한 분석을 위해 다음과 같이 작업을 진행한다.

• "행" 영역의 필드를 "열" 영역으로 이동하면 필드가 열 단위로 표시됨
• 숫자가 아닌 필드를 "값" 영역으로 이동하면 값의 형식은 합계가 아니라 "개수"로 표시됨
• 필드를 "필터" 영역으로 이동하면 조회조건으로 사용된다

3) 값 필드 설정하기

피벗테이블의 숫자필드를 선택하면 "합계"가 기본으로 표시되었는데, 합계 이외에 최대값,최소값,평균, 갯수를 구해야 할 때도 있다.

이럴 때는 다음과 같은 순서로 값 필드 설정메뉴에서 바꿔주면 된다.

먼저 필드를 선택한다.

값 필드 설정을 클릭한다.

합계, 개수, 평균, 최대 등 여러 항목이 나온다. 그 중에 필요한 항목을 선택한다.

 

아래와 같이 피벗테이블의 합계값이 최대값으로 바뀌어서 표시된 것을 확인할 수 있다.

 

4) 필드 표시형식 바꾸기

바로 위의 피벗테이블을 보면 숫자값에 천단위 콤마가 없어서 보기 불편하다. 다음과 같이 표시형식을 바꿀 수 있다.

• 피벗테이블 내부의 아무 숫자값이나 선택한 후 마우스를 우클릭(오른쪽 버튼 클릭)한다.
• 메뉴에서 "필드 표시 형식"을 선택한다.

팝업화면의 "표시 형식"에서 다음과 같은 순서로 원하는 숫자 형식을 선택한다.

아래와 같이 피벗테이블의 숫자값에 천단위 콤마가 표시된 것을 확인할 수 있다.

 

 

피벗테이블을 사용할 때 주의할 점

1) 원본 데이터가 변경되었다면 "새로고침"을 해야 한다.

원본데이터가 변경되거나 새로운 행이 추가되어도 피벗테이블의 값은 변경되지 않는다.

다음과 같이 새로 고침을 해 주어야 피벗테이블의 값이 변경적용된다.

• 피벗테이블 내부의 아무곳이나 선택한 후 마우스를 우클릭(오른쪽 버튼 클릭)한다.
• 메뉴에서 "새로 고침"을 클릭한다.

 

2) 원본데이터 범위를 벗어나면 인식이 안된다.

만약 다음과 같이 원본데이터의 범위를 A1셀부터 G34셀까지 지정(거래처별판매실적!$A$1:$G$34)하고 피벗테이블을 만든 후, 이 범위를 벗어난 행에 데이터가 추가되면 피벗테이블에서 "새로고침"을 해도 변경되지 않는다.

이 때는 다음과 같이 피벗 테이블 분석 > 데이터 원본 변경에서 범위를 변경해 주어야 한다.

 

위와 같이 매번 변경해주는 것이 귀찮다면 다음과 같이 열단위로 범위를 지정(거래처별판매실적!$A:$G)해 주면 데이터가 추가되어도 "데이터 원본 변경"작업을 하지 않아도 된다.

 

[ 엑셀중급강좌  ]