145 엑셀 피벗테이블 그룹, 날짜 그룹편

145_엑셀 초급 강좌 – 피벗 테이블 기본 (5)

엑셀 피벗 테이블은 원본 데이터를 가지고 분석된, 즉 가공된 테이블을 원하는 데로 몇 번의 클릭만 가지고 만드는 아주 강력한 기능입니다. 기능을 사용하기는 쉽지만 그동안 배운 모든 개념들을 포괄하고 있어서 모든 엑셀 관련 책에서는 상당히 뒤에 배치되어 있지만 사용하기는 아주 쉽습니다. 이번 포스팅에서 다루게 될 내용은 엑셀 피벗테이블의 주 기능중의 하나인 데이터의 그룹화 입니다. 피벗테이블의 그룹화 기능은 일반 시트에서보다 훨씬 사용하기 쉽고 직관적이며 더 강력합니다. 특히 엑셀에서의 원본 데이터가 시간, 날짜, 그리고 다른 일반 데이터인가에 따라서 모두 다른 그룹화 기능을 제공합니다. 엑셀에서 원본데이터를 올바르게 유지하는 것은 그래서 매우 중요합니다. 원본데이터의 서식은 신경 쓰지 않아도 얼마든지 피벗 테이블로 멋진, 가공된 데이터를 만들 수 있습니다.

1. 피벗 테이블 만들기

   전 블로그 내용을 올리기 전에 항상 같은 내용으로 검색을 해봅니다. 정말 많은 엑셀 블로그 분들이 있고 모두 비슷한 내용을 소개하고 있지만 정말 엑셀에 대하여 많이 알고 있다고 생각하는 블로그는 사실 많이 없습니다. 이 피벗테이블을 만드는 과정만 해도 엑셀 97년도 버전 방식으로 아직도 소개하는 블로그도 많습니다.

   피벗 테이블을 만들기 위해서는 데이터를 모두 선택할 필요가 없이 표 안에 커서만 위치하면 됩니다. 엑셀은 빈 행과 빈 열을 기준으로 피벗 테이블에 포함 시킬 영역을 자동으로 잡아주고 만약 내 표가 특이한 형태를 가지고 있다면 그때 수정해 주면 됩니다. 이런 과정을 생략하기 위해 제가 항상 깨끗한 원본 데이터를 유지하는 비결을 강조하는 것입니다. 원본만 깨끗하다면 어떤 형태로든 데이터의 가공은 쉽습니다.

   너무 중요한 내용이라 한번 더 강조 하겠습니다.

   첫째, 하나의 열에 하나의 필드 원칙을 지킵니다.

   둘째, 하나의 표 안에는 절대 빈 행과 빈 열이 없게 합니다.

   셋째, 만약 표의 제목 행을 넣고 싶다면 테이블과 제목 행 사이에 빈 행을 추가 합니다.

   넷째, 표와 표 사이에는 항상 최소 1개 이상의 빈 열을 삽입합니다.

   만약 데이터가 이와 같은 형태로 저장되어 있다면 피벗 테이블은 이미 완성된 거라고 보시면 됩니다.

    

   데이터 레이블 개수만 741개의 테이블 입니다. 이런 데이터를 수동으로 영역 지정을 한다면요? 몇 만개의 데이터 레이블을 가진 엑셀 파일도 많습니다.

   

    

   엑셀은 빈 행과 빈 열을 기준으로 빠르게 피벗 테이블에 포함할 영역을 자동으로 계산 합니다. 피벗 테이블 생성은 기존 워크시트에 할수도 있고 새 워크시트에 할 수도 있지만 새 워크시트를 추천 드립니다.

   

    

    

   확인을 누르면 새로운 워크시트에 피벗 테이블이 생성 됩니다.

   

    

2. 피벗 테이블의 날짜 그룹화 단위의 이해

   지금까지의 내용은 검색으로 제 블로그를 찾으신 분들을 위한 복습이었고 지금부터 엑셀 피벗테이블의 날짜 그룹화에 대해 총정리를 시작 하겠습니다. 피벗 테이블은 데이터의 형태에 따라서 다른 그룹화 옵션을 제공 합니다. 특히 날짜의 경우는 그 그룹화의 종류가 무척 많은데 이 모든 그룹화 기능을 이용하기 위해서는 무엇보다 데이터가 엑셀이 알아 먹을 수 있는 날짜 형태로 입력되어 있기만 하면 됩니다. 만들어진 피벗 테이블에 직원에 고용된 날짜를 기준으로 그룹화를 이해하는 시간을 갖도록 하겠습니다.

    

   이해를 쉽게 돕기 위해 피벗 테이블 행이 고용 일만 추가 하겠습니다. 고용을 추가 하는 순간 엑셀은 자동으로 해당 년도를 행에 추가하며 그룹 만듭니다. 구 버전은 다를 수 있는데 정확히 기억이 나질 않습니다.

   

    

    

   지금은 고용된 년 단위로 그룹화가 되어있는 상태입니다. 우클릭을 하여 그룹을 해제해 보겠습니다.

   

    

    

   그룹화가 되지 않은 모든 데이터 레코드 입니다.

   

    

    

   다시 우클릭을 하여 그룹을 해 보겠습니다.

   

    

    

   단위를 월 만 클릭하고 확인을 누릅니다.

   

    

    

   월로만 그룹화가 되어있는 상태이고 + 버튼이 보이질 않죠? 행 필드에 월만 들어가 있기 때문입니다. 최초의 테이블은 년도 밑에 고용일이라는 세부 메뉴가 들어가 있던 상태여서 확장이 가능했고 지금은 세부 목록은 넣지 않고 오로지 월로만 그룹화를 시켰습니다.

   

    

   값이나 열 필드는 추후에 설명 하겠습니다. 지금은 제일 쉬운 행으로만 진행 하고 있으니 일단 행 그룹화 만 확실히 이해 하시면 됩니다.

    

   다시 마우스 우클릭을 해서 이번엔 월, 분기, 연으로 모두 그룹화를 해 보겠습니다. 이때 쉬프트나 컨트롤을 클릭하지 않아도 중복 클릭이 됩니다.

   

    

    

   행에 1개 이상의 필드가 추가 되면 자동으로 이렇게 그룹화가 됩니다. 년 밑에 분기 그 밑에 지금 내가 선택한 단위 중 젤 낮은 월이 그룹화가 되었습니다. 이 밑에 고용 일을 날짜 별로 추가 할 수도 있습니다.

   

    

    

   다시 우 클릭을 해서 이번엔 월, 분기를 모두 지우고 연만 선택하고 확인을 누릅니다.

   

    

    

   + 표시가 없어졌죠? 그룹화의 개념을 확실히 이해하셨길 바랍니다.

   

    

   지금 까지는 그룹화가 어떤 것인지 이해하는 시간을 가졌다면 이제부터 그룹화를 내가 원하는 데로 커스터마이징 해 보겠습니다.

    

3. 피벗 테이블 날짜 그룹화 순서의 이해

   보통 년, 분기, 월의 그룹화라고 하면 분기가 년 밑에, 또 월이 분기 밑에 그룹화가 되어 들어간다고 생각하기 쉽습니다. 또 그게 엑셀의 기본 설정 입니다. 그러나 엑셀에서 그룹화의 상하 관계를 바꾸는 것은 엄청 쉽고 또 모두 그 의미를 갖습니다.

    

   엑셀의 기본 그룹화 설정 입니다. 여기서 피벗 테이블 필드 창 (우측)의 행 필드의 순서가 중요합니다. 연이 제일 밑에 있고 분기 고용 일이 마지막에 있으므로 이 상하 관계에 따라 그룹화가 된 것 뿐 입니다.

   

    

    

   만약 분기를 연 위로 올린다면 모든 각 분기에 해당하는 고용 일이 여기에 그룹화 됩니다. 예를 들어 1분기라 하면 2001년도 1분기부터 2015년도 1분기에 해당되는 모든 고용 일이 그룹화 되어 1분기에 들어 갑니다.

   

    

    

   같을 원리로 고용 일(여기서 일이라고 표기 되어 있지만 월로 그룹화 된 것입니다. )을 제일 위로 올리면 1월에 해당하는 모든 데이터들이 2001년부터 2015년 까지 그룹화 되어 1월하위 메뉴에 들어가게 됩니다. 다른 월들도 같습니다.

   

    

   방금 예에서 보셨듯이 행에서 필드의 순서는 중요 합니다. 각 분기별로 매출을 비교 하고 싶다면 분기를 행의 제일 위에 위치하면 각 년도에 각 분기에 해당하는 값들을 쉽게 비교 할 수 있습니다.

    

4. 피벗 테이블 필드 >>>> 값

   피벗 테이블 행의 그룹화를 이해 하셨다면 값을 추가 하여 확실히 그 활용도를 비교 해 보실 수 있습니다. 값은 위의 그룹화를 예로 들자면 분기 별 직원 고용 숫자, 각 분기별로 채용된 직원의 현재 평균 연봉 등을 그룹화를 통해 매우 쉽게 비교 할 수 있습니다. 또 그 값은 내 데이터의 원본에 없더라도, 함수를 하나도 모르더라도, 최대값, 평균, 최소값 등 거의 모든 값들을 마우스 클릭 만으로 추가 하실 수 있습니다.

    

   직원 이름을 값 에다가 추가했습니다. 직원 이름의 계산값은 엑셀이 자동으로 개수라고 넣습니다. 직원이름을 가지고 평균, 최대값등은 정의 조차 되질 않죠? 지금 이 테이블의 의미는 각 년도당 채용된 직원의 숫자 입니다.

   

    

    

   자 이번엔 값 에다 급여를 추가 해 보겠습니다. 급여의 값이 개수로 되어있으니 당연히 직원 이름과 똑 같겠죠?

   

    

    

   우측 화살표를 클릭하고 값 필드 설정을 클릭 합니다.

   

    

    

   여기서 이름을 레이블 이름을 바꾸기도 하고 계산 값을 지정 합니다. 최소, 최대값 모두 지정 가능하며 평균을 지정해 보겠습니다.

   

    

   각 년도당 채용된 직원들의 평균 급여 입니다. .

   

    

   피벗 테이블도 워크 시트에서의 서식과 같은 서식 적용이 가능합니다.

   

    

   엑셀 피벗테이블의 유용 함은 여기까지만 봐도 감탄이 나오지만 여기가 끝이 아닙니다. 아직 멀었습니다. 저기 값을 지금은 평균, 직원숫자만 넣었지만 넣고 싶은 값을 얼마든지 넣을 수가 있고 이 데이터를 바탕으로 더 고난도의 분석도 가능해 집니다. 그래서 엑셀에서 데이터를 입력할 때 서식은 중요하지 않다고 제가 수백 번도 넘게 강조한 것입니다. 올바른 데이터만 있으면 원하는 데이터를 원하는 서식으로 바꾸는 것은 몇 분 걸리지 않습니다.

    

5. 사용자 지정 그룹화

   엑셀에서 기본으로 지정된 년, 분기, 월 이외에도 사용자가 임으로 그룹을 만들어 그룹화를 시킬 수 있습니다. 예를 들어 우리 회사는 10일마다 인센티브가 있다고 하면 그 10일을 기준으로 평균 급여를 비교 할 수 있습니다.

   그룹화가 지정된 아무 테이블에 우클릭을 하시고 그룹을 선택 합니다.

   

    

    

   모든 그룹을 해제 하시고 일을 선택한 후 날짜 수, 시작일, 끝일을 지정합니다.

   

    

    

   기준 날짜 2015년 1월 1일 1부터 10일 단위로 그룹화 되어 10일 단위로 채용 되었던 직원들의 평균 급여를 보여 줍니다.

   

   지금은 10일 단위로 했지만 만약 달리기 기록이라면 초, 분 단위로의 그룹화 등 모든 종류의 그룹화가 가능합니다.

    

   엑셀에서 원본 데이터의 서식은 중요하지 않습니다. 올바른 깨끗한 데이터만 있으면 언제나 가공된 원하는 데이터를 얻는 것은 함수를 몰라도 이렇게 간단합니다.