[엑셀 잘 하는 법] 알아두면 유용한 직장인을 위한 엑셀 TIP (5) 피벗 테이블

엑셀 배움의 단계는 피벗 테이블을 아는가, 모르는가로 나뉜다 라고 하면 너무 과장된 이야기일까… 적어도 필자는 피벗 테이블로 인해 엑셀 사용 생활이 달라졌다. 피벗 테이블을 알기 전까지는 워드처럼 사용하던 엑셀이었으나 피벗 테이블을 알게 된 이후로 엑셀에서 다양한 시도를 할 수 있게 되었다.  

한 매장에서 배낭 판매 실적을 기입해두었다. 만약 누군가 5월의 가방 별 구매 실적을 알려 달라고 한다면? 가방 별로 숫자를 계산하기에는 번거로울 뿐만 아니라 실수할 확률도 매우 크다. 이럴 때 활용하는 것이 바로 피벗 테이블이다!

 

잠깐) 피벗 테이블을 작업하기 위해서는 데이터 표의 첫 행에 항목이 들어가야 한다!

먼저 분석할 데이터가 있는 표 안에 마우스 셀을 둔 후, [삽입]-[피벗 테이블]을 클릭한다.

 

새로운 창이 열리며 자동으로 표 범위를 잡아준다. 만약 원하는 범위가 아니라면 새롭게 지정해주어도 된다. 피벗 테이블 보고서의 위치는 기존 워크시트에도 작업은 가능하지만 일반적으로는 [새 워크시트]에 넣어서 작업하면 더 좋다. [확인]을 클릭해준다.

새로운 시트에 [피벗 테이블 필드]가 생성되었다.

 

 

오른쪽의 [피벗 테이블 필드]에서 [품명]을 체크해주면 왼쪽에 중복된 항목은 제거한 채 품명이 나열된다.  그러나, 이상하게도 6행과 7행은 동일한 데이터 임에도 불구하고 반복해서 나온다.

왜 그런지 알아보기 위해 기존 데이터가 있던 [상품판매내역] 탭을 클릭해 돌아가보자.

12행의 남성 투톤 배낭가방은 불필요한 여백이 없다.

 

하지만 10행의 남성 투톤 배낭 가방을 선택해보니 품명 끝에 스페이스 바가 들어간 것을 확인할 수 있다. [Backspage]키로 여백을 지워줘서 12행과 동일하게 만들어준다.

이제 품명의 오른쪽 끝의 여백이 사라진 것을 확인할 수 있다.

다시 작업하던 Sheet2로 돌아갔지만 데이터는 그대로이다.

[분석]-[새로 고침]을 클릭하면 [상품 판매 내역]에서 수정했던 내역이 반영된다.

이제 중복된 품명이 보이지 않는다.

처음 데이터를 관리할 때부터 여백이 생기지 않도록 관리해주는 것이 중요하다. 그래서 필자의 경우 동일한 품명의 경우 복사하여 붙여 넣기를 선호한다.

[피벗 테이블 필드]에서 [수량]을 클릭해주면 품명에 맞는 수량의 합계가 나타난다.

이번에는 단가를 체크해줬다. 하지만 단가가 갑자기 높아진 것을 보아 잘 못된 것을 느끼게 된다.

ADD 27의 단가가 정말 280,000원일까?

[상품판매내역] 탭을 선택해 확인해보니 ADD 27은 140,000원임을 확인할 수 있다.

다시 작업하던 sheet2로 돌아가보자.

그 이유는 단가 역시 [합계]로 나타나 단가들을 더해줬기 때문이다. 오른쪽 하단에 있는 [값] 중에서 [합계:단가]라고 되어 있는 것을 클릭하여 [값 필드 설정]을 선택한다.

단가는 값이 모두 동일하기 때문에 [평균] 혹은 [최대값], [최소값]을 선택해준다.

 

 

이제 정상적으로 ADD 27의 단가가 140,000원으로 표현된 것을 확인할 수 있다.

피벗 테이블과 연동되어 있는 데이터는 편집 시 불편하므로 텍스트로 변경해보자.

완성된 피벗 테이블을 드래그로 선택한 후, 복사[Ctrl+C]해준다.

  

탭을 하나 더 추가하여 빈 워크시트를 만든 후, 방금 전 복사한 테이블을 붙여넣기 [Ctrl+V] 한다. 붙여넣기 하자 마자 오른쪽 하단에 뜨는 (Ctrl)메뉴를 클릭하여 [값 붙여넣기]에서 [값]으로 붙여넣기를 선택한다.

서식이 모두 제거되고 값만 남을 것을 확인할 수 있다.

각 열의 항목 이름이 변경되었으므로, 원래의 제목으로 수정해주고 [합계] 열도 기입해준다.

합계 열에서 =수량*단가 수식을 적어 각 품목의 월 실적 금액을 표현해준다.

동일한 수식이 적용될 수 있게 합계 열을 전체 선택한 후 [Ctrl+D]를 눌러 서식과 수식이 복사될 수 있게 한다.

 

금액으로 콤마(,)가 중요하다면 [홈]-[표시형식]에서 [콤마(,)] 아이콘을 클릭해주면 가격을 보기 편해진다.

 

이제 쉽게 5월에 판매된 품목별 수량과 실적 금액을 손쉽게 알 수 있게 되었다!

 

 

[맺음말]

필자도 업무 상 품목별 수량 혹은 실적 금액을 요청 받은 적이 많다. 그때마다 하나씩 찾아 수기로 수량을 더해 자료를 주었던 기억이 난다. 다루던 품목 수가 많다 보니 한 번에 전체 합계가 같았던 적이 없어 항상 몇 번을 다시 계산하고는 했다.   

하지만 우연히 알게 된 피벗 테이블로 그 동안 했던 고생들이 주마등같이 스쳐 지나갔다. 그 이후로 누군가 자료를 요청할 때 수기로 하기 보다는 방법을 생각했다. 어떻게 하면 요청 받은 자료를 정확하고 빠르게 찾을 수 있을까? 하고 말이다. 

이 글을 보시는 분들도 요청 받은 일이 있다면 당장에는 시간이 더 걸리더라도 어떻게 하면 정확하고 빠르게 할 수 있을까를 고민하고 찾아본다면 엑셀 실력이 어느새 부쩍 늘어있는 자신을 보게 될 것이다.

 

혜강씨(본명: 이혜강)

2012년 웹∙프로그램 부문 파워블로거로 선정된 문서 제작의 달인. 블로그 ‘친절한 혜강씨(http://leehyekang.blog.me/)’에 파워포인트 및 엑셀 강의를 게재하고 있다. 올해부터는 코오롱 팬들과 함께 감각적인 ppt강의 및 직장인의 두려움을 해소시킬 엑셀 비법도 같이 알려줄 예정이다! 

  본 칼럼의 내용은 코오롱 그룹의 공식적인 의견과 다를 수 있습니다.