Excel 작업 중 데이터를 체계적으로 정리해야 할 때 다단계 계층 구조가 필요한 경우가 자주 있습니다. 다단계 계층 구조를 활용하면 복잡한 데이터도 간결하고 명확하게 표현할 수 있습니다. 이 글에서는 Excel에서 다단계 계층 구조를 만드는 2가지 쉬운 방법을 단계별로 소개합니다.
Excel에서 다단계 계층 구조를 만드는 2가지 방법
첫 번째 방법은 Excel의 데이터 유효성 검사(Data Validation) 기능을 활용하는 것이고, 두 번째 방법은 파워 피벗(Power Pivot) 추가 기능을 사용하는 것입니다.
본 글은 Microsoft Excel 365 버전을 기준으로 작성되었으며, 사용 중인 다른 버전에서도 동일하게 적용할 수 있습니다.
방법 1. 데이터 유효성 검사로 다단계 계층 구조 만들기
데이터 유효성 검사 기능은 Excel에서 다단계 계층 구조를 만드는 가장 쉬운 방법 중 하나입니다. 아래 예시 데이터에는 영화관의 상영 중인 영화에 대한 영화 이름(Movie Name), 상영 시간표(Show Schedule), 좌석 유형(Seat Type) 정보가 담겨 있습니다. 이 데이터를 활용해 다단계 계층 구조를 만들어 보겠습니다.
1단계: 영화 이름 열의 데이터 유효성 검사 설정하기
영화 이름 열에는 드롭다운 목록을 만들어 원하는 영화를 선택할 수 있도록 설정하겠습니다. 절차는 다음과 같습니다.
- 먼저 영화 이름 열의 B5 셀을 선택합니다.
- 리본 메뉴에서 데이터(Data) 탭으로 이동합니다.
- 데이터 도구(Data Tools) 그룹에서 데이터 유효성 검사(Data Validation) 옵션을 클릭합니다.
그러면 아래 그림과 같이 데이터 유효성 검사 대화 상자가 열립니다.
- 아래 이미지에 표시된 드롭다운 화살표를 클릭합니다.
- 드롭다운 목록에서 목록(List)을 선택합니다.
- 다음으로 원본(Source) 입력란을 클릭하고 아래 수식을 입력합니다.
='Dataset 1 '!$B$5:$B$10여기서 $B$5:$B$10은 해당 데이터 세트에서 영화 이름 열에 해당하는 셀 범위입니다.
참고: 모든 셀에서 전체 영화 이름 목록이 선택 가능해야 하므로, 여기서는 절대 참조를 사용했습니다.
- 이후 확인(OK)을 클릭합니다.
- 그러면 아래 그림과 같이 B5 셀 옆에 드롭다운 화살표가 나타납니다. 이를 클릭하세요.
드롭다운 목록에서 원하는 영화를 자유롭게 선택할 수 있습니다.
2단계: 나머지 영화 이름 열 셀에 유효성 검사 복사하기
이제 Excel의 선택하여 붙여넣기(Paste Special) 기능을 사용해 B5 셀의 데이터 유효성 검사 설정을 영화 이름 열의 나머지 셀에 복사합니다.
- 먼저 B5 셀을 선택한 후 CTRL + C를 눌러 복사합니다.
- 영화 이름 열의 나머지 셀들을 선택합니다.
- 리본 메뉴에서 홈(Home) 탭으로 이동합니다.
- 붙여넣기(Paste) 옵션을 클릭합니다.
- 드롭다운에서 선택하여 붙여넣기(Paste Special) 옵션을 선택합니다.
- 선택하여 붙여넣기 대화 상자에서 아래 이미지와 같이 유효성 검사(Validation) 옵션을 선택합니다.
- 그다음 확인(OK)을 클릭합니다.
이제 선택한 셀들에 데이터 유효성 검사가 적용됩니다. 각 셀 옆의 드롭다운 목록에서 원하는 영화 이름을 선택하면 아래 이미지와 같은 결과를 얻을 수 있습니다.
3단계: 상영 시간표 열의 데이터 유효성 검사 설정하기
이번에는 영화 이름 열에서 선택한 값에 따라 드롭다운 목록이 달라지도록 설정하겠습니다. 예를 들어, 영화 Into the Deep은 하루에 5개의 상영 시간이 있지만, Bullet Train은 3개의 상영 시간만 있는 식입니다. 즉, 상영 시간표 열에 표시되는 옵션은 영화 이름 열에서 선택한 영화에 따라 결정됩니다. 이를 위해 OFFSET 함수와 MATCH 함수를 사용합니다.
- 먼저 C5 셀을 선택한 후 1단계의 절차를 동일하게 수행합니다.
- 그다음 원본(Source) 입력란에 아래 수식을 입력합니다.
=OFFSET('Dataset 1 '!$B$14,1,MATCH($B5,'Dataset 1 '!$B$14:$G$14,0)-1,5,1)여기서 $B$14는 Dataset 1 워크시트의 상영 시간표 헤더 셀, $B5는 영화 이름 열에서 선택한 영화, 범위 $B$14:$G$14는 Dataset 1에 있는 영화 이름 배열을 의미합니다.
수식 분석
- MATCH($B5,'Dataset 1 '!$B$14:$G$14,0) → 행, 열 또는 표에서 찾을 값의 위치를 반환합니다.
- $B5 → lookup_value 인수
- 'Dataset 1 '!$B$14:$G$14 → lookup_array 인수
- 0 → match_type 인수
- 결과 → 1
- OFFSET('Dataset 1 '!$B$14,1,MATCH($B5,'Dataset 1 '!$B$14:$G$14,0)-1,5,1) → OFFSET('Dataset 1 '!$B$14,1,1-1,5,1)로 계산됩니다.
- 'Dataset 1 '!$B$14 → reference 인수
- 1 → rows 인수
- 1-1 → cols 인수
- 5 → height 인수
- 1 → width 인수
- 결과 → {11:00 AM, 3:30 PM, 6:00 PM, 9:30 PM, 11:00 PM}
- 그다음 확인(OK)을 클릭합니다.
- 아래 이미지와 같이 C5 셀 옆에 드롭다운 화살표가 나타납니다. 이를 클릭하세요.
영화 Into the Deep의 상영 가능한 시간표가 표시됩니다.
4단계: 나머지 상영 시간표 열 셀에 유효성 검사 복사하기
이제 Excel의 선택하여 붙여넣기 기능을 사용해 C5 셀의 데이터 유효성 검사 속성을 나머지 셀에 복사합니다.
- C5 셀을 선택한 후 2단계의 절차를 동일하게 수행합니다.
- 이후 영화 Bullet Train을 선택하면 예시 데이터와 같이 상영 시간표 옵션이 3개만 표시되는 것을 확인할 수 있습니다.
- 같은 방식으로 다른 영화들의 상영 시간표도 선택하면 아래 이미지와 같은 결과를 얻을 수 있습니다.
5단계: 좌석 유형 열의 데이터 유효성 검사 설정하기
이번에는 선택한 상영 시간표에 따라 좌석 유형 열의 드롭다운 목록이 달라지도록 설정하겠습니다. 데이터를 보면 모든 상영 시간에 동일한 좌석 유형이 제공되지 않습니다. 예를 들어 오전 11:00 상영분은 3개의 좌석 유형이 모두 제공되지만, 오후 6:00 상영분은 일반(Regular)과 VIP 좌석만 이용할 수 있습니다. 이 의존형 드롭다운 목록을 만들기 위해 다시 한번 OFFSET 함수와 MATCH 함수를 사용합니다.
- 먼저 D5 셀을 선택한 후 1단계의 절차를 동일하게 수행합니다.
- 그다음 데이터 유효성 검사 대화 상자의 원본(Source) 입력란에 아래 수식을 입력합니다.
=OFFSET('Dataset 1 '!$B$23,1,MATCH($C5,'Dataset 1 '!$B$23:$F$23,0)-1,3,1)여기서 $B$23은 Dataset 1의 좌석 유형 헤더 셀, $C5는 선택한 상영 시간표, 범위 $B$23:$F$23은 Dataset 1 워크시트의 상영 시간표 배열을 의미합니다.
수식 분석
- MATCH($C5,'Dataset 1 '!$B$23:$F$23,0) → 행, 열 또는 표에서 찾을 값의 위치를 반환합니다.
- $C5 → lookup_value 인수
- 'Dataset 1 '!$B$23:$F$23 → lookup_array 인수
- 0 → match_type 인수
- 결과 → 3
- OFFSET('Dataset 1 '!$B$23,1,MATCH($C5,'Dataset 1 '!$B$23:$F$23,0)-1,3,1) → OFFSET('Dataset 1 '!$B$23,1,3-1,3,1)로 계산됩니다.
- 'Dataset 1 '!$B$23 → reference 인수
- 1 → rows 인수
- 3-1 → cols 인수
- 3 → height 인수
- 1 → width 인수
- 결과 → {Regular, VIP}
- 이후 확인(OK)을 클릭합니다.
D5 셀 옆에 드롭다운 화살표가 생성됩니다. 선택한 상영 시간표가 오후 6:00이므로, 일반(Regular)과 VIP 옵션만 표시됩니다.
6단계: 나머지 좌석 유형 열 셀에 유효성 검사 복사하기
마지막으로 D5 셀의 데이터 유효성 검사 속성을 좌석 유형 열의 다른 셀들에 복사합니다.
- D5 셀을 선택한 후 2단계의 절차를 동일하게 수행합니다.
- 이제 C6 셀 옆의 드롭다운 화살표를 클릭합니다. 선택한 상영 시간표가 오전 11:00이므로 3개의 좌석 유형이 모두 표시되는 것을 확인할 수 있습니다.
- 같은 방식으로 다른 셀들도 좌석 유형을 선택하면 아래 이미지와 같은 결과를 얻을 수 있습니다.
방법 2. 파워 피벗(Power Pivot)으로 다단계 계층 구조 만들기
파워 피벗 기능을 활용하는 것도 다단계 계층 구조를 만드는 효율적인 방법입니다. 아래 데이터 세트에는 미국 여러 주(State)의 도시별 판매(Sales) 데이터가 담긴 표가 있습니다. 이 표로 다단계 계층 구조를 만들어 보겠습니다.
1단계: 파워 피벗 창에 표 추가하기
파워 피벗 추가 기능이 활성화되어 있지 않다면 먼저 해당 기능을 활성화한 후 아래 단계를 진행하세요.
- 먼저 표 전체를 선택한 후 CTRL + C를 눌러 복사합니다.
- 리본 메뉴에서 파워 피벗(Power Pivot) 탭으로 이동합니다.
- 아래 이미지에 표시된 대로 관리(Manage) 옵션을 클릭합니다.
그러면 아래 그림과 같이 빈 파워 피벗 창이 열립니다.
- 파워 피벗 창에서 클립보드(Clipboard) 옵션을 클릭합니다.
- 드롭다운에서 붙여넣기(Paste) 옵션을 선택합니다.
그러면 붙여넣기 미리 보기(Paste Preview) 대화 상자가 나타납니다.
- 붙여넣기 미리 보기 대화 상자에서 확인(OK)을 클릭합니다.
이제 아래 이미지처럼 표가 파워 피벗 창에 추가됩니다.
2단계: 다이어그램 보기에서 계층 구조 만들기
이 단계에서는 국가 아래에 주가, 주 아래에 도시가 포함되는 계층 구조를 만듭니다.
- 먼저 파워 피벗 창의 보기(View) 탭에서 다이어그램 보기(Diagram View) 옵션을 클릭합니다.
그러면 아래 이미지와 같이 다이어그램 보기 화면이 나타납니다.
- 이제 아래 이미지에서 표시된 부분을 클릭합니다.
그러면 새로운 계층(Hierarchy)이 생성됩니다.
- Country(국가) 열을 마우스로 끌어 새로 만든 계층 구조에 놓습니다.
- 같은 방식으로 나머지 열들도 순서대로 끌어다 놓으면 아래와 같은 결과가 나타납니다.
3단계: 피벗 테이블 만들기
- 이후 피벗 테이블(Pivot Table) 옵션을 클릭합니다.
- 피벗 테이블 만들기(Create Pivot Table) 대화 상자가 나타나면 확인(OK)을 클릭합니다.
그러면 아래 그림과 같이 새 워크시트가 생성됩니다.
- 피벗 테이블 필드(PivotTable Fields) 창에서 Hierarchy1을 행(Rows) 영역으로, Sales를 값(Sum of Values) 영역으로 각각 끌어다 놓습니다.
그러면 워크시트에 아래와 같은 피벗 테이블이 완성됩니다.
- 이제 아래 이미지에 표시된 + 기호를 클릭합니다.
그러면 각 주(State)와 해당 주의 판매액 합계가 아래 이미지처럼 표시됩니다.
- 이어서 주(State)인 Arizona 이름 옆의 + 기호를 다시 클릭합니다.
Arizona 주에 속한 도시(Cities)와 각 도시의 판매액이 표시됩니다.
같은 방식으로 다른 주 옆의 + 아이콘을 클릭하면 아래와 같은 결과를 얻을 수 있습니다.
연습 섹션
Excel 통합 문서의 각 워크시트 오른쪽에는 연습 섹션이 준비되어 있습니다. 직접 실습해 보며 익혀보세요.
마무리
지금까지 Excel에서 다단계 계층 구조를 만드는 방법을 알아보았습니다. 이 글이 여러분의 업무에 도움이 되기를 바랍니다. 궁금한 점이나 개선 의견이 있다면 언제든지 댓글로 남겨주세요. Excel 관련 더 많은 팁은 저희 웹사이트 ExcelDemy를 방문해 확인하실 수 있습니다. 즐거운 학습 되세요!
함께 보면 좋은 글
- Excel에서 계층 차트 만드는 방법 (3가지 쉬운 방법)
- Excel 피벗 테이블에서 날짜 계층 구조 만들기 (단계별 가이드)
- Excel에서 SmartArt 계층 구조 활용하는 방법 (단계별 가이드)
- Excel에 행 계층 구조 추가하는 방법 (2가지 방법)