칼퇴 보장! 보고서 작성 시간을 줄여주는 엑셀 피벗 테이블 사용법

오늘따라 아침부터 들뜬 K씨. 콧노래를 부르며 출근하더니, 종일 입가에 미소가 가시지를 않습니다. 그도 그럴 것이 오늘은 친구의 주선으로 소개팅이 있는 날. 매번 눈물의 크리스마스를 보냈지만 올해만큼은 조금 따뜻하게 보낼 수 있을까 두근 반 세근 반으로 하루를 보내고 있는데, 아뿔싸! 퇴근 한 시간 전 과장님의 외침. “K씨~ 퇴근하기 전에 이번달 매체별 블로그 유입량좀 체크해줘. 날짜별로 볼 수 있게 분류해주고, 어디서 제일 많이 들어오는지 확인해봐”

출처: 네이버 영화 <나 홀로 집에>

나름 회사에서 ‘보고서 좀 쓴다’고 소문난 K씨이지만, 이상하게 엑셀은 한 번 익히고 돌아서면 까먹기 일쑤인데요, 결국 표를 만들어 데이터를 하나하나 붙여 넣어 수작업을 하고 마는 K씨. 이러다 이번 크리스마스도 케빈과 함께 보내게 되는 것은 아닐까요.

이럴 때 유용한 것이 바로 피벗 테이블입니다. 피벗 테이블을 이용하면 데이터를 보다 빠르고 쉽게 분석해주고 행, 열 방향으로 필드를 재배치해 데이터를 한 눈에 보기가 용이한데요, 그렇다면 피벗 테이블을 활용해 K씨의 고민을 해결해볼까요? 아마도 직장인이 회사에서 제일 많이 쓸 것 같은, 오피스 2010 버전으로 소개해드립니다.

 행과 열을 자유자재로 이동하는 데이터 계의 큐브, 피벗 테이블

왠지 자주 들어 귀에 익숙한 것만 같은 피벗 테이블은 대량의 데이터를 빠르게 요약하는 데 사용하는 대화형 테이블입니다. 피벗 테이블의 가장 큰 특징은 데이터를 내가 원하는 대로 표시할 수 있다는 점인데요, 원본 데이터를 다양한 방식으로 요약하여 표시할 수 있고, 원하는 결과만 강조할 수 있어 수치를 다룰 때에 특히 유용합니다.

관련된 합계를 분석할 때나 합계를 구할 수치 목록이 긴 경우, 혹은 각 수치에 대해 여러 가지 사실을 비교하고 싶을 때 피벗 테이블 보고서를 사용합니다. 생각보다 훨씬 유용하게 사용되는 만큼 엑셀 고수와 하수의 차이는 ‘피벗 테이블을 아는가, 모르는가’로 나뉜다고 해도 과언이 아닙니다.

 실전에서 피벗 테이블 활용하기 STEP1, 피벗테이블의 기초

K씨에게 주어진 미션을 확인해봅시다. 먼저 매체별로 얼마나 들어오는지를 확인해야 하고, 날짜별로 분류를 해야 하죠. K씨에게는 아래와 같은 자료가 있습니다. 하지만 날짜 순으로 정렬 되어 있어, 어느 매체에서 얼마나 유입되었는지 한눈에 보기는 어려운 것 같네요.

물론 필터를 이용해서 각각의 항목을 추출하는 방법도 있을 것입니다. 하지만 과장님의 요청에 따라 날짜 별로 볼 수 있도록 하기 위해서는 수작업으로 다시 한 번 정리를 해주어야겠죠. 언제까지 복붙만 반복하고 있을 수는 없잖아요. 아래와 같이 데이터를 모두 선택한 후, [삽입-피벗 테이블]을 선택해주세요.

새로운 시트에 피벗 테이블이 생성되었습니다. 앞서 피벗 테이블의 특징으로, 원하는 항목만 선택할 수 있다고 말씀드렸어요. 그래서 처음 피벗 테이블을 생성하면 아무것도 안 보이는 상태가 되는데요, 여기서 보고서에 추가할 필드를 선택해주어야 비로소 생성된 피벗 테이블을 볼 수 있습니다.

 

우측에서 유입일자와 유입매체, 유입량을 선택한 모습입니다. 날짜를 기준으로 유입량이 정리된 것을 볼 수 있어요. 하지만 과장님의 첫 번째 미션은 매체별 유입량이 아니던가요.

우측의 ‘피벗 테이블 필드 목록’을 보시면 보고서 필터와 열 레이블, 행 레이블, 값으로 나뉘어진 영역이 있을 거예요. 이곳에서 각각의 항목을 드래그하여 원하는 영역으로 옮길 수가 있어요. 기준으로 삼을 ‘유입매체’를 열 레이블에 끌어넣어보겠습니다.

‘유입매체’ 항목을 열 레이블로 끌어놓았더니, 달라진 테이블의 모습이 보이시나요? 이대로 과장님께 보고하고 퇴근하면 따뜻한 크리스마스를 보낼 수 있을 것만 같네요. 이제 퇴근까지 남은 시간 30분, 그리고 들려오는 과장님의 한 마디.

“응~ 수고했어. 근데, 여기서 인트라넷은 빼주라. 그리고 매체별로 일 평균 수치도 좀 부탁해”

이렇게 K씨의 따뜻한 크리스마스는 물 건너 가는 건가요.

 피벗 테이블 활용하기 STEP2, 필터 이용하기와 평균 구하기

다시 처음으로 돌아가 원본 자료에서 ‘인트라넷’ 항목을 하나 하나 삭제하는 K씨, 그러나 어쩐지 피벗 테이블로 돌아와도 수정된 항목이 보이지 않네요. 처음부터 다시 피벗 테이블을 만들기 시작하려는 K씨. 그는 피벗 테이블에서도 필터를 적용할 수 있다는 사실을 몰랐던 것입니다. 지금부터 설명해 드릴 테니, 천천히 따라해보세요~ 어렵지 않아요.

피벗 테이블의 장점은 데이터를 원하는 대로 표시할 수 있다는 점이었습니다. 당연히 필터를 이용해 특정 항목을 제외할 수도 있고요. ‘인트라넷’이 포함돼있는 ‘유입 매체’ 항목에 마우스를 살포시 갖다 대면, 우측의 필터 모양의 아이콘을 볼 수 있을 거예요. 해당 아이콘을 클릭하여 ‘인트라넷’ 항목의 체크박스를 해제해주세요.

이번에는 매체별 평균을 구해볼까요. 위 표에서는 매체별 총 합계와 일자별 총 합계가 표시되어있어요. 매체별 합계가 표시되어있는 14행의 모든 항목을 선택한 후, 마우스 우측을 클릭하여 ‘값 필드 설정’ 항목을 선택합니다.

 

값 필드 설정에서 합계와 값의 개수뿐만 아니라 평균, 최대값, 최소값, 표준 편차까지 구할 수 있어요. ‘평균’ 항목을 클릭한 후 확인을 눌러주세요.

그림과 같이 표시된 것을 볼 수 있습니다. 소수점 뒷자리는 마우스 오른쪽 클릭 후 ‘셀 서식>표시 형식>숫자>소수 자릿수’에서 설정해주시고, ‘총합계’라고 적힌 부분은 직접 수정해주시면 완료. 이제 남은 시간 20분, K씨는 무사히 퇴근할 수 있을까요?

 피벗 테이블 활용하기 STEP3, 항목 그룹핑하기

“K씨, 정말 미안한데, 지난주랑 이번주랑 구분해서 정리 좀 해줄래?”

이 때 들려오는 과장님의 요청. 아무래도 K씨의 따뜻한 크리스마스는 물 건너 간 것 같네요. 이번에는 피벗 테이블의 마지막 단계, 항목별 그룹핑을 설명해드릴게요. 클릭 한 번이면 되는지라, 설명이 금세 끝날 거니 집중해주세요.

지난주가 12월 6일까지였으니, 1일부터 6일까지의 모든 항목을 선택한 후, ‘마우스 우클릭>그룹’을 선택해줍니다.

마찬가지로 7일부터 10일까지의 데이터도 블록 지정하여 그룹화 해줍니다. ‘그룹1’이라고 되어 있는 명칭을 수정해주면 완료.

2주차도 마찬가지로 블록 지정해준 후, 그룹화해주세요.

 

그림과 같이 그룹화를 해준 후, 항목명을 바꿔주면 완성! 이렇게 해서 K씨는 약속시간에 무사히 소개팅을 나갈 수 있었다는 후문입니다.ㅎㅎ 

보고서 작성 시간을 단축시켜주는 피벗테이블, 참~ 쉽죠? 계산기를 두드리거나 수작업으로 했다면 어쩌면 몇 시간이 걸릴지도 모르는 작업을 손쉽게 끝내주니 피벗테이블의 활용은 무궁무진합니다. 지금까지 소개해드린 내용들은 사실 기초에 불과하지만, 이만큼만 알고 있어도 웬만한 보고서 작성에 필요한 작업들은 끝낼 수 있을 거예요. 이제 피벗테이블로 보고서 달인이 되어보세요~