많은 데이터 한번에 요약집계! 피벗테이블

안녕하세요.

매월 매출 및 결산 보고하는 건 참 스트레스고 업무 양적으로 부담이 많아 압박이 많이 오죠.

보고서를 만들기 전까지 한 달간 쌓여있는 수많은 데이터를 상급자 혹은 사장님이 보기에 체계적으로, 간략하지만

정확한 데이터를 보고하기 위하여 회의 전날 밤을 새우곤 합니다.

이번 포스팅에서는 그러한 업무 중 큰 부분을 차지하는 요약 관련한 엑셀 꿀팁을 전달해 드리려고 해요.

---

피벗테이블(Pivot table)

대화형 테이블의 일종으로, 데이터의 나열 형태에 따라서 집계나 카운트 등의 계산을 하는 기능.

또 철이 없는 팀장님이 저를 불러놓고 

"야 인마! 1일부터 6일까지 전 제품 판매수량 단가 총판매금액 좀 뽑아와라!!"라고 하길래

저는 (손발이있으면 네가 해라) "넵"이라고 했어요.

그리고는 원하시는 데이터를 뽑았습니다.

(이미지 크기상 중간에 잘라서 첨부하였습니다 양해 부탁드립니다(__) )

다 뽑아서 던져줬더니 또 하는 말이

"야 이렇게 나열하면 나보고 일일이 제품별로 어떻게 보냐.. 제품별로 일자별로 한눈에 볼 수 있게 정리해오란 말이야!!"

저는 치밀어 오르는 화를 눌러 담으며 "네^^알겠습니다 팀장님"이라고 했죠. 별 수 있나요.

이제 이 많은 데이터를 그가 원하는 대로 가공을 해야 하는데 어떻게 해야 할까요.. 좋은 방법 없을까요?

제품별 판매현황 - 루루미미아범예제(피벗테이블).xlsx

0.01MB

---

이제 그 팀장님(놈)이 원하는 방법대로 정리해 봅시다.

위처럼 제품명별로 일자별 판매수량과 금액이 나열되어 있는데요.

A상품은 A상품끼리, B상품은 B상품끼리,,,,,J상품은 J상품끼리 합쳐서 정리를 해야 해요.

이게 종류가 한두 가지면 그냥 막일로 하겠는데 10개, 100개, 500개.. 면 힘들죠.

이럴 때 쓰는 기능이 피벗테이블이라는 강력한 기능입니다.

1. 먼저 피벗테이블로 만들 구간을 블록 잡습니다.

 

2. 상단 탭의 [삽입] - [피벗 테이블]을 클릭합니다.

 

3. [확인]을 클릭합니다.

 

4. 그러면 피벗 테이블이 만들어집니다.

이제 여기에서 우리가 보기 쉽게 데이터를 요리조리 옮겨서 확인을 해볼 거예요.

여기서 말하는 데이터는 뭐냐면 아까 블록 잡을 때 첫 번째 행에 위치한 제목들의 값입니다.

데이터

5. 아까 팀장님(놈)은 분명 제품별, 일자별로 한눈에 볼 수 있게 정리하라고 했어요.

일단 어느 데이터를 한눈에 볼 수 있게 할지는 조금 이따 정하고 먼저 제품별, 일자별로 행열을 고정시켜 보아요.

저는 일단 행은 가로 값(제품명), 열은 세로 값(일자, 1일~6일)으로 할 거예요. 

오른쪽에 있는 [피벗 테이블 필드 목록]에서 아래 그림처럼 해당 값을 드래그해서 아래에 끌어다 놓아요.

 

6. 그러면 왼쪽 엑셀 창에는 아래 그림과 같이 피벗테이블이 뿅 하고 생겼는데요. 뭐가 뭔지 구경해 볼까요?

파란 부분은 열 부분으로 채워졌교, 빨간 부분은 행 부분으로 채워졌나요?

아까 저는 열은 일자(1~6일)로 할 거고, 행은 제품명으로 한다고 했었는데, 값들이 적절히 위치되었죠.

이제 아까 결정하지 못한 "어느 데이터를 볼지"가 남았어요.

일단 그 팀장님(놈)이 어떤 걸 원하지 모르니 판매수량 하고 총판매금액 두 개로 각각 볼 거예요.

7. 조금 전 했던 [피벗 테이블 필드 목록]에서 아래 그림과 같이 판매수량을 드래그해서 아래로 끌어봅시다.

8. 값 부분에는 하나 신경 써줘야 할 것이 있는데요. 아래 그림처럼 값에 있는 [합계:판매...]를 클릭합니다.(마우스 왼쪽 버튼)

9. [값 필드 설정]을 클릭합니다.

 

10. 우리는 값들의 합계를 볼 것이므로 [합계]를 선택하고 [확인]을 클릭합니다.

 

11. 어떤 결과가 나왔나요?

처음에 길게 일자별로 늘어져 있는 데이터를 한데 모아 제품별, 날짜별 총 몇 개씩 팔았는지 한눈에 볼 수 있나요?

 

12. 이번에는 판매수량 말고 제품별 총판매금액을 봅시다. 과정은 동일한데요.

기존에 체크되어있는 판매수량의 체크를 해제하고 [총판매금액] 데이터를 아래 값 부분에 드래그합니다.

 13. 어떤 결과가 나왔나요? 이 부분도 역시 [합계]로 설정하여 보아야 합니다. 왜냐하면 각 판매금액을 다 합칠꺼니까요.

1000자리마다 컴마(,)를 찍어줍시다.

14. 결과

 

자 어떤가요? 각각의 데이터를 정리해서 별도 수식 입력 등 필요 없이 마우스 클릭 몇 번만으로 엄청 많은 데이터를 한꺼번에 정리하였어요. 이게 말이 되나요?

이제 실생활에 써먹어서 그 팀장님(놈)이 원하는 바를 스트레스와 고뇌 없이 바로바로 처리합시다.

그 팀장님(놈)이 나중에 승진에 도움이 될지 모르겠지만 말이죠.. 주어진 일에 최선을 다합시다.

이번 간단 포스팅은 여기까지입니다. 혹시 질문/보완사항이 있으면 댓글 환영합니다.

감사합니다.