서비스
home

스프레드시트의 꽃: 피벗테이블 만들기 2부 (정렬)

스프레드시트의 꽃: 피벗테이블 만들기 1부 (데이터 준비) 에서는 데이터를 준비하고 피벗 테이블을 만드는 방법을 살펴보았습니다.
2부에서는 행과 열을 추가하고, 값을 변화시키면서 어떻게 분석할 수 있는지를 살펴봅니다.

분석 목표 정하기

피벗 테이블을 만드는 목적은 통계와 분석입니다.
목적을 분명히 정하지 않고 피벗 테이블을 만들다보면 단지 숫자의 합과 나열일 뿐입니다.
1부에서 만든 테이블을 보시면, 담당자의 이름 오름차순으로 정렬된 것을 볼 수 있습니다.
물론 이름순으로 보는 것도 의미가 있습니다만, 일반적으로는 매입액 순으로 또는 일자순으로 보는 것이 더 의미있지 않을까요?
모든 피벗 테이블마다 만드는 목적이 분명해야 중복되지 않는 테이블을 만들고, 헷갈리지 않을 수 있습니다.
2부에서는 이렇게 목표를 세워보겠습니다.
1. 월별 매입액을 볼 수 있을 것. 최근 월 위에 표시 → 1차 월별 정렬 2. 월별 매입액 내에서는 담당자별 매입액을 볼 수 있으면서, 매입액 큰 담당자를 위에 표시할 것 → 2차 담당자 정렬 3. 담당자별 몇 번 매입을 했고, 매입평균액을 표시
SQL
복사

피벗테이블 적용하기

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

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

우선 월별 매입액을 보려면 행에 일자를 추가합니다.
행을 추가하면 이전에 추가한 행의 하단으로 들어가게 됩니다.
그래서 담당자 - 일자 순으로 피벗 테이블이 구성됩니다. 일자를 드래그해서 담당자 위로 올리겠습니다.
잠시 기다리면, 자동으로 피벗 테이블의 구조가 변경됩니다. 로딩 속도는 컴퓨터의 성능과 데이터의 양에 따라 달라집니다.
만약 행이나 열이 늘어나면서 부족하면 #REF 가 뜨는데, 자동으로 되지 않으면 행이나 열을 추가해주시면 됩니다.
일자가 오름차순으로 되어 있어서 1월부터 표시되는데요, 내림차순으로 바꾸어주면 역으로 12월 말부터 표시가 됩니다.
이제 월별로 그룹을 만들어 보겠습니다. 어떻게 할 수 있을까요?
피벗 테이블에 표시되는 일자에 오른쪽 클릭을 하면 메뉴가 나오는데, 피봇 날짜 그룹 만들기 > 년-월을 선택하면 알아서 됩니다. 참 쉽죠?
이렇게 1번 목표를 달성했습니다. 어렵지 않죠?

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

1번 목표를 달성한 결과를 보면 이름순으로 정렬되어 있음을 알 수 있습니다.
담당자의 정렬방법을 매입액 큰 순서대로 바꿔주려면 어떻게 해야할까요?
행의 담당자의 정렬 기준을 누르면, 정렬할 수 있는 3가지의 방법이 나옵니다.
여기서 공급 총액 (VAT 포함) 의 SUM 을 선택하고, 순서를 내림차순으로 선택합니다.
이렇게 2번 목표도 클리어!

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

몇 번 매입했는지는 기록을 몇 번 입력했는지라고 생각할 수 있지요.
값을 추가할텐데, 여러 방법이 있습니다.
기록을 세는 것이기 때문에 담당자를 값에 추가하고 요약기준을 COUNTA 를 선택해도 되고, 수량을 추가하고 요약기준을 COUNTA 해도 동일한 결과를 얻습니다.
매입 평균액은 (총 매입액 / 매입 횟수)로 보면 되는데요.
계산된 필드를 사용할 필요도 없이~ 요약 기준의 AVERAGE 를 선택하시면 됩니다.
먼저 값에 공급총액을 추가합니다. 요약 기준의 기본값은 SUM 입니다. 이 값을 AVERAGE로 바꿉니다.
목표를 모두 달성했습니다~ 간단하게 표를 만들 수 있지요?
알면 30초이지만, 모르면 동일한 표를 만들기 위해서 3시간을 사용해야할지 모릅니다!
간단하지만 실무에서 필수인 피벗테이블, 생각보다 쉽죠?
오늘 살펴본 내용을 샘플로 확인해보세요.

관련 포스팅