MS엑셀: 피벗 테이블 - 기초 개념

실행

삽입 - 피벗 테이블

뭣에 쓰는 것인고?

[그림 1. 피벗 테이블을 만들기 위해 주어진 데이터 테이블]

간단하게 말하자면 피벗 테이블은 위와 같은 데이터가 있을 때, 분석자가 이리 저리 여러 방향으로 '값'을 분석하고 싶을 때 쓰는 테이블이다. 
데이터 테이블을 보면, 분석하려는 '값'은 매출현황(금액)이다. 그런데 이 값을 브랜드명+생산지별로 본다든가, 또는 제조업체별+최신모델별로 2007년과 2008년 자료만 뽑아서 본다든가..이런 식으로 다각도로 값을 분류하거나 묶음으로 해서 보고 싶을 때 쓰는 것이다. 상사가 야, 그거 2009년과 2010년 자료만 필요한데, 각 제조업체의 브랜드별로 매출 얼마나 되는지 뽑고, 그 브랜드별로도 합계 매출이 각각 얼마씩인지도 정리해서 가져와봐,라고 했을 때 위의 그림에서 보이는 데이터 통째로 갖다주고서 니가 알아서 보세요,라고 할 수는 없는 것 아닌가. 그러니까 주문자 입맛에 맞추어서 이렇게 저렇게 분류와 묶음을 하는 간단한 도구가 엑셀의 피벗 테이블 기능이다. 

컴활의 문제 유형 (2급, 2010년 3회 D형 - 문제3. 분석작업 - 2)
난 컴활의 문제지에 쓰인 문제 서술이 (내 생각에 동의 안 할 사람이 훨씬 더 많겠지만) 참 더럽다고 생각하는데, 사실 위의 상사가  말한 것처럼 서술하면 알아서 뽑아야 하는 게 진짜 문제다운 문제라고 생각한다. (이거 독해(리스닝)가 안 되는 사람이 컴활1급 자격증을 가지고 있어도 일을 못 하기는 마찬가지.--;)
하지만 컴활의 문제는 이렇게 서술되어 있다. (어...대부분의 컴활 준비자들에게는 이게 더 쉽게 느껴질 것이다.)

[원하는 결과]

[그림 2. 만들어야 하는 피벗 테이블 - 문제에서 주어진다.]

위와 같이 결과로 내어야 하는 피벗 테이블을 그림으로 준다.

문제의 주 요구사항
- 생산지는 '페이지', 제조업체는 '행', 브랜드명은 '열'로 처리하고 '데이터'에는 2009년, 2010년 합계를 계산하는 피벗 테이블을 작성하시오. (1급 문제에는 이런 게 없었는데 2급에서는 이렇게 일일히 가르쳐 주더라. 2007 버전 화면을 보면 이게 무엇인지 금방 안다. 그냥 그대로 따라서 만들면 된다.) 

덧붙여 나오는 요구사항
* 데이터의 합계는 회계, 소수 자리수는 0, 화폐 표시 기호는 표시하지 않도록 하시오. (그림에 보이는 '값'의 서식 내용을 이렇게 풀어서 요구하고 있다.)
* 피벗 테이블의 보고서는 동일 시트의 [A21] 셀에서 시작하시오. (문제에서 시작점을 항상 지정을 하는데, 그러면 커서를 그 셀에 놓고 피벗 테이블을 시작하면 된다. 어, 그림에 A19부터 값이 나와있는데?하고 걱정할 필요는 없다. 실제로 시키는대로 제대로 했으면 시작점을 A21에 놓고 이 문제를 풀면 결과의 그림처럼 A19에 뭔가가 들어가게 된다는 걸 알려주는 일종의 정보 역할도 한다.)

문제 풀기
요령: 나머지는 일단 신경쓰지 말고 주 요구사항에 따라서 실행해라.

주어진 테이블 영역을 선택하지 않고 시작하면 아래와 같은 대화창에서 피벗 테이블로 만들 데이터 영역을 선택해주고 피벗 테이블을 시작할 지점도 지정해야 한다. 

[그림 3. "실행 > 피벗 테이블"을 실행한 모습 - 피벗 테이블 만들기 대화 상자]

지정이 끝난 후 확인을 누르면 다음과 같이 피벗 테이블 만들 준비를 끝낸 화면이 나온다.

[그림 4. 피벗 테이블 필드 목록 설정하기]

아래 그림과 같이 주 요구사항에서 시키는대로 필드목록 창에서 그대로 넣는다. 

[그림 5. 피벗 테이블 필드 목록 설정하기 2] 
페이지를 생산지로 설정하라는 말은 피벗 테이블 보고서가 있는 페이지(여기서는 원 데이터 테이블의 아랫 부분)가 생산지 별로 생성된다는 말로 이해하면 된다. 즉, 피벗 테이블 보고서 필터를 생산지로 설정해두면 생산지를 어느 지역(모두, 미국, 일본, 한국...)으로 변경(필터)해주면 그 아래 피벗 테이블에서 생산지에 따라 각 값들이 통째로 변경(페이지 변경)된다는 것으로 이해하면 되겠다.

시키는대로 했는데 결과 그림과 모양이 다르다. "2009년 합계"같은 것이 안 생기고 "2009년", "2010년"이라는 이름을 가진 열이 세로로 보았을 때 브랜드별로 각각 값을 뿌리고 있다.

[그림 6. 지금까지 설정을 완료했을 때 모습을 드러내는 기본적인 피벗 테이블] 

현재 기본적으로 설정된 것+아까 내가 설정한 필드 목록의 설정 상태는 아래와 같다.

[그림 7. 피벗 테이블 필드 목록의 설정 상태] 
('시그마 값'은 이렇게 지가 알아서 '열 레이블'로 들어가 앉아 버린다.)

여기서 뭔가 더 해보려고 하지 말고, 가만히 생각을 한번 해보자.
열 레이블, 행 레이블이란 무엇인가?
대체, 저기 필드 목록에 있는 '시그마 값'이라는 건 뭔가?

열 레이블은 아래 그림에서 진한 글씨 부분이다.

[그림 8. 참고: 열 레이블]
행 레이블이란 아래 그림에서 가장 왼쪽에 있는 진한 글씨 부분이다.

[그림 9. 참고: 행 레이블, 열 레이블] 

그럼, 다시 돌아가서 우리가 원하는 결과의 그림을 보자.

[그림 2. 만들어야 하는 피벗 테이블 - 문제에서 주어진다.] 
즉, 제조업체 이름 옆에 두 개씩 뿌려지고 있는 "합계 : 2009년", "합계 : 2010년"은 행의 레이블로 쓰이고 있다.

이해가 되었으면 열 레이블에 있는 시그마 값을 아래 그림처럼 행 레이블로 끌어다 옮겨라. 
문제에서 원하는 그림에는 2009년, 2010년의 합계(합계: 2009년, 합계: 2010년)이 각 행의 제목(=레이블)로 쓰이고 있으니까, 저걸 변경해주어야 하는 것이다.

[그림 10. 피벗 테이블 필드 목록 대화창 - '시그마 값' 이동시키기] 
즉, 이 필드 목록의 설정 상태는 오른쪽 아래의 '시그마 값'에는 "합계 : 2009년"과 "합계: 2010년" 두 개가 있는데 이것들을 '행 레이블'로 쓰겠다고 '행 레이블' 영역으로 (원래 '열 레이블'영역에 있던) "시그마 값"을 옮겨준 것이다.

그럼 아래와 같이 원하는 모양의 피벗 테이블이 만들어진다. 

[그림 11. 필드 목록을 수정한 후 완성된 피벗 테이블의 모습] 

피벗 테이블 문제에서 물어보는 기본적인 배열의 문제는 이걸로 해결될 거라고 생각한다. 나머지 문제로는 값의 서식 등등이 있는데 지금 이 예제에서 시키는 데이터 합계의 표시 방식 설정은 매우 쉬워서 어딘가를 클릭하면 그냥 알게 될 것이고...1급 문제들을 보면 좀 해볼만한 것들이 있다. 나중에 시간 나면 또.......끝.