스프레드시트의 꽃: 피벗 테이블 만들기 1부에서는 데이터를 준비하고 피벗 테이블을 만드는 방법을 살펴보았습니다.

2부에서는 행과 열을 추가하고, 값을 변화시키면서 어떻게 분석할 수 있는지를 살펴봅니다.

---

분석 목표 정하기

피벗 테이블을 만드는 목적은 통계와 분석입니다.

목적을 분명히 정하지 않고 피벗 테이블을 만들다보면 단지 숫자의 합과 나열일 뿐입니다.

1부에서 만든 테이블을 보시면, 담당자의 이름 오름차순으로 정렬된 것을 볼 수 있습니다.

물론 이름순으로 보는 것도 의미가 있습니다만, 일반적으로는 매입액 순으로 또는 일자순으로 보는 것이 더 의미있지 않을까요?

모든 피벗 테이블마다 만드는 목적이 분명해야 중복되지 않는 테이블을 만들고, 헷갈리지 않을 수 있습니다.

2부에서는 이렇게 목표를 세워보겠습니다.

1. 월별 매입액을 볼 수 있을 것. 최근 월 위에 표시 → 1차 월별 정렬

2. 월별 매입액 내에서는 담당자별 매입액을 볼 수 있으면서, 매입액 큰 담당자를 위에 표시할 것 → 2차 담당자 정렬

3. 담당자별 몇 번 매입을 했고, 매입평균액을 표시 

---

피벗테이블 적용하기

하나씩 진행해보겠습니다.

1. 월별 매입액을 볼 수 있을 것. 최근 월 위에 표시 → 1차 월별 정렬

우선 월별 매입액을 보려면 행에 일자를 추가합니다.

행을 추가하면 이전에 추가한 행의 하단으로 들어가게 됩니다.

그래서 담당자 - 일자 순으로 피벗 테이블이 구성됩니다. 일자를 드래그해서 담당자 위로 올리겠습니다.

잠시 기다리면, 자동으로 피벗 테이블의 구조가 변경됩니다. 로딩 속도는 컴퓨터의 성능과 데이터의 양에 따라 달라집니다.

만약 행이나 열이 늘어나면서 부족하면 #REF 가 뜨는데, 자동으로 되지 않으면 행이나 열을 추가해주시면 됩니다.

일자가 오름차순으로 되어 있어서 1월부터 표시되는데요, 내림차순으로 바꾸어주면 역으로 12월 말부터 표시가 됩니다.

이제 월별로 그룹을 만들어 보겠습니다. 어떻게 할 수 있을까요?

피벗 테이블에 표시되는 일자에 오른쪽 클릭을 하면 메뉴가 나오는데, 피봇 날짜 그룹 만들기 > 년-월을 선택하면 알아서 됩니다. 참 쉽죠?

이렇게 1번 목표를 달성했습니다. 어렵지 않죠?

2. 월별 매입액 내에서는 담당자별 매입액을 볼 수 있으면서, 매입액 큰 담당자를 위에 표시할 것 → 2차 담당자 정렬

1번 목표를 달성한 결과를 보면 이름순으로 정렬되어 있음을 알 수 있습니다.

담당자의 정렬방법을 매입액 큰 순서대로 바꿔주려면 어떻게 해야할까요?

행의 담당자의 정렬 기준을 누르면, 정렬할 수 있는 3가지의 방법이 나옵니다.

여기서 공급 총액 (VAT 포함) 의 SUM 을 선택하고, 순서를 내림차순으로 선택합니다.

이렇게 2번 목표도 클리어!

3. 담당자별 몇 번 매입을 했고, 매입평균액을 표시

몇 번 매입했는지는 기록을 몇 번 입력했는지라고 생각할 수 있지요.

값을 추가할텐데, 여러 방법이 있습니다.

기록을 세는 것이기 때문에 담당자를 값에 추가하고 요약기준을 COUNTA 를 선택해도 되고,

수량을 추가하고 요약기준을 COUNTA 해도 동일한 결과를 얻습니다.

매입 평균액은 (총 매입액 / 매입 횟수)로 보면 되는데요.

계산된 필드를 사용할 필요도 없이~ 요약 기준의 AVERAGE 를 선택하시면 됩니다.

먼저 값에 공급총액을 추가합니다. 요약 기준의 기본값은 SUM 입니다. 이 값을 AVERAGE로 바꿉니다.

목표를 모두 달성했습니다~ 간단하게 표를 만들 수 있지요?

알면 30초이지만, 모르면 동일한 표를 만들기 위해서 3시간을 사용해야할지 모릅니다!

---

간단하지만 실무에서 필수인 피벗테이블, 생각보다 쉽죠?

3부에서는 피벗 테이블에 다이내믹 필터링과 조건부 서식을 살펴보겠습니다.

오늘 살펴본 내용을 샘플로 확인해보세요.

샘플 보기