21. 피벗테이블 처리

우리가 보는 것은 주로 우리가 찾는 것에 달려 있다. -존 루벅(생물학자이자 정치가)

 

엑셀의 가장 강력한 기능이 피벗테이블(PivotTables)이라는 주장에 대해서 대부분의 사람들이 이의를 제기하지 않는다. 그런데 놀라운 것은, 동시에 가장 사용되지 않는 기능 중 하나도 그것이라는 사실이다. 대부분의 엑셀 사용자들이 이에 대해 들어본 적은 있지만 사용한 경험은 전혀 없다. 만일 당신이 월별 판매의 집계표를 작성하거나 긴 목록에 대해 통계 분석을 하거나 어마어마한 목록에서 톱10을 찾는 것처럼 대량 데이터를 분석할 필요가 있다고 할 때, 피벗테이블의 사용 방법을 안다면 틀림없이 큰 도움이 될 것이다. 초보 수준을 넘어선 엑셀 사용자라면 누구나 피벗테이블의 개념을 익히고 배워서 이 강력한 도구를 사용할 수 있어야 한다. 피벗테이블의 힘은 회계와 재무 담당자들을 울게 만들기도 한다. 나는 소노마 주립 대학교에서 컴퓨터와 재무 및 회계를 위한 스프레드시트에 대해 강의한 적이 있다. 피벗테이블에 관한 실습이 있은 뒤 일주일 정도가 지났을 때 한 작은 회사의 회계 담당자가 전화를 걸어왔다. 그녀는 곧 울음을 터트릴 듯한 목소리였다. 그녀는 그동안 매월 말이면 하루 하고도 반나절 동안 엑셀 테이블들을 작성하고 재작성하여 약간의 검증뿐만 아니라 매출과 비용을 '여러 각도'의 다른 유형으로 구분하느라 보냈다. 그런데 이제 피벗테이블 덕분에 한 시간도 채 안 되어 완료되었다. 그녀는 나머지 시간을 회사를 위해 보다 생산적으로 쓸 수 있게 되었다. 그녀는 그 기쁨을 나누려고 나에게 전화한 것이었다! 한 피벗테이블이 분석하는 테이터는 워크시트 목록에 있을 수도 있고, 작은 액세스 데이터베이스 안에 있을 수 있고, 대형 SQL 서버 안에 있을 수도 있다. 그렇지만 최종 결과는 몇 분의 작업으로 워크시트에 데이터를 가져오기 하여 당신이 원하는 방법으로 집계(또한 화면에 표시까지)할 수 있다.

 

피벗테이블의 기본 개념

피벗테이블은 목록과 데이터베이스를 가져다 데이터를 분석하고 워크시트 안에 당신이 지정하는 배치로 결과를 반환한다. 그림 21.1은 이 장에서 예로 사용할 단순한 엑셀목록이다. 여기에는 표제 행 하나와 지역과 일자별 제품 장치와 판매 금액을 수록한 624개의 행이 들어 있다. 이것에서 집계표를 만들려면 당신은 복잡한 SUMIF함수, COUNTIF 함수 또는 다른 통계나 재무용 수식 여러개를 작성해야 할 것이다. 그러고 나서 그 데이터를 다르게 구성하고자 할 때는 수식 또는 조건을 다시 작성하기도 해야 한다. 그런데 이 데이터를 가지고 PivotTable 명령을 사용하거나 엑셀 2007에서는 삽입 탭의 표 그룹에서 피벗테이블을 선택하면 그림 21.2에서 보는 것처럼 서식이 지정된 테이블을 만들 수 있다. 여기에는 기본 구성과 배치를 위해 5회의 클릭과 5회의 드래그가 필요하다. 그런 후 추가 8회의 클릭으로 색과 경계 서식을 지정하고 일자들의 서식을 지정한다. 이것은 SUMIF 함수와 COUNTIF 함수와 같은 조건형 통합 소식의 사용보다 더욱 강력한데, 재구성을 할 수 있고 몇 번의 클릭으로 다른 분석 과제를 해결할 수도 있기 때문이다. 피벗테이블을 사용하면 엑셀 워크시트 안에 목록 형태로 되어 있는 데이터에 접근할 수 있고, 관계형 데이터베이스 안에 있는 데이터에도 접근할 수 있다. 관계형 데이터베이스에 연결하려면 그 데이터베이스와의 오피스 데이터 커넥션을 구축할 필요가 있다.

온라인분석처리와 함께 피벗테이블의 사용 : 피벗테이블은 온라인분석처리 도구와 함께 사용하고자 한다면 마이크로소프트 웹 사이트를 방문하여 그에 관한 지원 내용과 관련 백서를 참고하라.

피벗테이블 작성 방법을 학습할 수 있는 곳 : 이 장은 피벗테이블의 작성 방법을 소개하는 곳이 아니다. 피벗테이블은 대부분의 엑셀 기초 도서들이 다루고 있고, 웹에도 무료로 볼 수 있는 자료들이 많이 있다.

 

자동 확장하는 데이터베이스 이름의 작성

엑셀 워크시트 안에 데이터를 입력하거나 조회하거나 저장할 때는 피벗테이블을 생성하기 전에 그 피벗테이블이 참조할 데이터베이스 영역을 위해 자동 확장하는 범위 이름을 만들어야 한다(이것을 반드시 할 필요는 없지만, 이렇게 하면 데이터베이스 크기가 바뀔 때 작업의 수고를 덜고 오류 가능성을 줄여준다).자동 확장 범위 이름을 사용하면 데이터의 행과 열을 추가할 수 있고, 다음에 피벗테이블을 갱신할 때 그 데이터가 포함된 것을 확신할 수 있다. 데이터를 위한 자동 확장 범위 이름은 다음과 같이 정의한다.

1. 데이터를 새로운 워크시트에 셀 A1부터 시작하여 입력한다. 행 1에는 표제들을 입력한다. 이 워크시트에는 이 데이터만 입력한다.

2. 삽입 > 이름 > 정의를 선택한다. 엑셀 2007에서는 새 이름 대화 상자가 열린다.

3. 계산된 범위 이름을 정의한다. 그림 21.3에는 새로운 범위 이름인 dbToolsData가 보인다. 두문다 db는 작명 표준에 따라 데이터베이스를 나타낸다. 이 이름을 위한 셀 범위를 계산하는 수식은 참조 대상 입력 상자 안에 있다. 그 OFFSET 함수의 구문은 아래와 같다. =OFFSET(reference,rows,cols,[height],[width]) OFFSET 함수 수식에 대한 설명은 아래에서 계속한다.

4. 추가를 클릭한 뒤 확인을 누른다.

reference(참조)는 A1, 즉 데이터의 왼쪽 상단이다. 이 정의된 이름의 왼쪽 상단 참조가 이 데이터베이스의 왼쪽 상단과 일치하기 때문에 rows(행)과 cols(열)의 오프셋(offset)은 0이다. 이 이름이 부여된 데이터베이스 범위의 height(높이)는 열 A의 채워진 모든 셀을 계산하면 된다. 이 데이터베이스 범위의 width(너비)는 행 1의 채워진 모든 셀을 계산하면 된다.

COUNTA(A:A)

COUNTA(1:1)

그 OFFSET 함수의 완전한 수식은 아래와 같다.

=OFFSET(도구데이터!$A$1,0,0,COUNTA(도구데이터!A:A),COUNTA(도구데이터!1:1)

여기에서 도구데이터는 이 워크시트 탭의 이름이다.