데이터 분석과 데이터 시각화에서 계층 구조(hierarchy)란 여러 개의 중첩된 열을 마치 하나의 항목처럼 다룰 수 있게 해주는 목록을 의미합니다. 상하 관계가 있는 조직이나 분류 체계를 한눈에 표현하는 데 매우 유용합니다. 다행히 마이크로소프트 엑셀의 피벗 테이블 기능을 활용하면 이러한 계층 구조를 손쉽게 만들 수 있습니다.
이번 글에서는 피벗 테이블, 파워 피벗(Power Pivot), 그리고 SmartArt까지 세 가지 방법으로 엑셀에서 계층 구조와 계층 차트를 만들고 표현하는 방법을 자세히 소개합니다.
예제 파일은 아래 링크에서 내려받아 직접 따라 해볼 수 있습니다.
피벗 테이블만으로 계층 구조 만들기 (단계별 절차)
이 섹션에서는 오직 피벗 테이블 기능만 사용해 계층 구조를 만드는 방법을 알아보겠습니다. 핵심 원리는 간단합니다. 피벗 테이블을 배치할 때 상위 필드(부모) 아래에 하위 필드(자식)를 배치하는 것입니다.
예시로 아래와 같은 데이터 집합을 사용하겠습니다.
데이터를 보면 주(State)가 도시(City)를 포함하는 계층 관계임을 알 수 있습니다. 아래의 자세한 단계를 따라 해 보세요.
1단계: 데이터 범위 선택
먼저 피벗 테이블로 변환할 전체 데이터 또는 일부 범위를 선택합니다. 이 단계에서 선택한 범위만 계층 구조에 반영된다는 점을 기억하세요.
여기서는 설명을 위해 전체 데이터를 선택했습니다.
2단계: 데이터를 피벗 테이블로 변환
데이터를 선택했다면, 이제 이를 피벗 테이블로 변환합니다. 리본 메뉴에서 [삽입] 탭으로 이동한 뒤 [표] 그룹의 [피벗 테이블]을 클릭합니다.
그러면 대화상자가 나타납니다. 피벗 테이블을 기존 워크시트에 배치할지 새 워크시트에 배치할지 선택할 수 있습니다. 여기서는 새 워크시트 옵션을 선택하겠습니다.
마지막으로 [확인]을 클릭하면 피벗 테이블이 들어 있는 새 워크시트가 열립니다.
3단계: 상위 필드를 행 영역으로 드래그
새로 생성된 시트에서 피벗 테이블 범위 안의 아무 셀이나 클릭합니다. 그러면 화면 오른쪽에 피벗 테이블 필드 창이 나타납니다.
예제 데이터에서는 '주'가 상위 필드입니다. 따라서 '주' 필드를 클릭한 뒤 [행] 영역으로 끌어다 놓습니다.
피벗 테이블이 아래와 같은 모습이 됩니다.
4단계: 하위 필드를 상위 필드 아래로 드래그
같은 방식으로 하위 필드도 처리합니다. 이 예제에는 '도시'라는 하위 필드가 하나만 있습니다. '도시' 필드를 클릭해 [행] 영역으로 끌어다 놓습니다.
이때 [행] 영역에서 하위 필드가 반드시 상위 필드 아래에 위치하도록 순서를 확인하세요. 하위 필드가 여러 개라면 계층의 위계 순서를 그대로 유지해야 합니다.
피벗 테이블이 아래처럼 바뀝니다.
그림에서 볼 수 있듯이 피벗 테이블 안에 계층 목록이 이미 완성되었습니다.
5단계: 필요한 다른 필드 추가하기
앞의 단계를 마쳤다면, 데이터를 더 잘 표현하기 위해 필요한 추가 필드를 같은 방식으로 끌어다 놓아 넣어줍니다.
예를 들어 인구 열도 함께 표시하고 싶다면, '인구' 필드를 [값] 영역으로 끌어다 놓으면 됩니다.
최종적으로 피벗 테이블은 아래와 같은 형태가 됩니다.
이렇게 하면 피벗 테이블 기능만으로도 손쉽게 계층 구조를 만들 수 있습니다.
더 읽어보기: 엑셀 피벗 테이블에서 날짜 계층 구조 만들기 (쉬운 단계)
파워 피벗(Power Pivot)으로 계층 구조 만들기
파워 피벗 기능을 사용하면 또 다른 방식으로 계층 구조를 만들 수 있습니다. 파워 피벗은 엑셀에 이미 오래전부터 포함되어 있지만, 기본적으로 비활성화되어 있어 직접 추가해야 합니다. 먼저 파워 피벗을 추가하고, 이어서 계층 구조를 만드는 과정을 살펴보겠습니다.
엑셀에 파워 피벗 기능 추가하기:
- 먼저 리본 메뉴에서 [파일] 탭을 클릭합니다.
- 백스테이지 화면 왼쪽에서 [옵션]을 클릭합니다.
- Excel 옵션 대화상자가 열리면 왼쪽 메뉴에서 [추가 기능]을 선택합니다.
- 하단의 관리 항목에서 [COM 추가 기능]을 선택합니다.
- 그다음 [이동] 버튼을 클릭합니다.
- 나타나는 COM 추가 기능 대화상자에서 [Microsoft Power Pivot for Excel] 항목에 체크한 뒤 [확인]을 누릅니다.
이제 리본 메뉴에 [Power Pivot] 탭이 생긴 것을 확인할 수 있습니다.
파워 피벗으로 계층 구조 만들기:
- 먼저 워크시트에서 데이터 범위를 선택합니다.
- [삽입] 탭으로 가서 [표] 그룹의 [피벗 테이블]을 클릭합니다.
- 대화상자에서 피벗 테이블을 기존 시트에 둘지 새 시트에 둘지 선택합니다.
- 이때 반드시 [이 데이터를 데이터 모델에 추가] 옵션에 체크하세요.
- 그다음 [확인]을 클릭합니다.
- 리본 메뉴의 [Power Pivot] 탭으로 이동해 [데이터 모델] 그룹의 [관리]를 선택합니다.
- 그러면 파워 피벗 창이 열립니다. 아래 그림과 비슷한 화면이 나타날 것입니다.
- 파워 피벗 창의 리본 메뉴에서 [홈](또는 파일) 탭으로 가서 [보기] 그룹의 [다이어그램 뷰]를 선택합니다.
파워 피벗 창의 시트가 아래처럼 바뀝니다.
- 계층 구조에 포함할 필드들을 모두 선택한 뒤 마우스 오른쪽 버튼을 누르고, 바로 가기 메뉴에서 [계층 구조 만들기]를 선택합니다.
- 계층 그룹의 이름을 지정합니다. 여기서는 "위치(Location)"라고 명명하고 Enter 키를 누릅니다.
- 이제 다시 피벗 테이블 시트로 돌아가 피벗 테이블 범위 안의 셀을 선택합니다. 화면 오른쪽의 피벗 테이블 필드 창에서 방금 만든 계층 그룹이 필드 목록에 나타나는 것을 볼 수 있습니다.
- "위치"를 클릭해 [행] 영역으로 끌어다 놓습니다.
피벗 테이블이 아래처럼 표시됩니다.
- 마지막으로 필요한 다른 열도 같은 방식으로 추가합니다. 예를 들어 '인구' 필드를 [값] 영역으로 끌어다 놓았습니다.
이제 계층 구조가 적용된 피벗 테이블이 완성되었습니다.
처음에는 계층 구조의 상위 항목만 보입니다. 하위 항목을 보려면 항목 앞의 플러스(+) 아이콘을 클릭하면 하위 클래스가 펼쳐집니다.
전체를 한 번에 펼치려면 계층 구조가 있는 열을 마우스 오른쪽 버튼으로 클릭한 뒤 [확장/축소] → [필드 확장]을 선택합니다.
그러면 계층 구조의 모든 항목이 펼쳐져 아래와 같이 표시됩니다.
이렇게 파워 피벗 기능을 활용해 엑셀에서 계층 구조를 만들 수 있습니다.
엑셀에서 계층 차트(Hierarchy Chart) 만들기
피벗 테이블 외에도 엑셀에서 계층 차트를 직접 만들 수 있습니다. 이를 위해 마이크로소프트 엑셀의 SmartArt 기능을 사용합니다. 아래 단계를 따라 해 보세요.
단계:
- 먼저 리본 메뉴에서 [삽입] 탭을 클릭합니다.
- [일러스트레이션] 그룹에서 [SmartArt]를 선택합니다.
- SmartArt 그래픽 선택 대화상자 왼쪽에서 [계층 구조]를 선택하고, 오른쪽에서 원하는 계층 차트 유형을 고른 뒤 [확인]을 클릭합니다.
- 그러면 워크시트에 아래와 같은 도형이 나타납니다.
- 왼쪽의 [여기에 텍스트 입력] 패널에 계층 항목을 직접 입력합니다. 하위 항목은 상위 항목 아래 줄에 입력하고, 입력 중 언제든 Tab 키를 누르면 해당 항목이 하위 수준으로 들어갑니다.
예를 들어 "New York" 주를 입력한 후 Tab 키를 누르고 "New York City"와 "Buffalo"를 입력하면 두 도시가 하위 항목으로 배치됩니다.
- 다시 상위 수준으로 올라가려면 Shift+Tab 키를 누른 뒤 같은 방식으로 상위·하위 항목을 계속 입력하면 됩니다.
- 같은 방법으로 모든 항목을 채우면 아래와 같은 결과를 얻을 수 있습니다.
- 색상과 스타일 등을 조금만 수정해도 훨씬 보기 좋게 다듬을 수 있습니다.
이렇게 SmartArt를 활용하면 엑셀에서 계층 차트를 아주 쉽게 만들 수 있습니다.
더 읽어보기: 엑셀에서 SmartArt 계층 구조 활용법 (쉬운 단계)
마무리
지금까지 피벗 테이블을 사용하거나 사용하지 않고 엑셀에서 계층 구조와 계층 차트를 만드는 모든 방법을 살펴보았습니다. 이 가이드를 통해 원하는 형태의 계층 구조를 부담 없이 만들 수 있기를 바랍니다. 내용이 도움이 되었다면 좋겠고, 궁금한 점이나 제안 사항이 있다면 댓글로 알려주세요.
이런 종류의 더 많은 가이드는 ExcelDemy.com에서 확인하실 수 있습니다.
관련 글
- 엑셀에서 다단계 계층 구조 만들기 (2가지 쉬운 방법)
- 엑셀에 행 계층 구조 추가하는 방법 (2가지 쉬운 방법)