주소를 나타낼 때는 일반적으로 주(State), 도시 이름, 우편번호가 필요합니다. 하나의 주 안에는 여러 개의 도시가 있고, 하나의 도시 아래에는 여러 개의 우편번호 지역이 존재합니다. 이번 글에서는 엑셀에서 주 → 도시 → 우편번호로 이어지는 계층 구조를 만드는 방법을 소개합니다. 먼저 주를 선택하고, 이어서 해당 주에 속한 도시를 고르면, 마지막으로 각 도시별 우편번호 지역을 확인할 수 있도록 구성하는 것이 목표입니다.
계층 구조 테이블이란?
계층 구조 테이블은 서로 다른 요소 간의 관계를 표현하기 위한 표입니다. 어떤 항목이 상위(부모) 요소이고, 어떤 항목이 하위(자식) 요소인지 한눈에 보여주므로, 데이터를 체계적으로 정리하고 탐색하는 데 매우 유용합니다.
아래는 주, 도시, 우편번호로 구성된 계층 구조의 예시입니다.
엑셀에서 주·도시·우편번호 계층 구조 만드는 단계
지금부터 엑셀에서 주, 도시, 우편번호 계층 구조를 만드는 전체 과정을 단계별로 자세히 살펴보겠습니다. 먼저 5개 주를 포함한 데이터 세트를 준비했으며, 이후 모든 작업은 이 데이터를 기반으로 진행됩니다.
⦿ 1단계: 데이터 준비
이 단계에서는 계층 구조에 사용할 주, 도시, 우편번호 데이터를 미리 정리해 둡니다.
- 먼저 각 주마다 대표 도시 5개의 이름을 수집합니다.
- 그다음 조지아(Georgia) 주 도시에 속한 여러 우편번호를 정리합니다.
같은 방식으로 나머지 주의 데이터도 차례로 준비합니다.
- 플로리다(Florida) 주 데이터
- 앨라배마(Alabama) 주 데이터
- 캘리포니아(California) 주 데이터
- 하와이(Hawaii) 주 데이터
이제 계층 구조에 필요한 데이터 세트가 모두 완성되었습니다.
함께 읽으면 좋은 글: 엑셀 피벗 테이블에서 계층 구조 만드는 방법 (쉬운 단계별 가이드)
⦿ 2단계: 데이터 유효성 검사로 주 목록 만들기
계층 구조를 구현하기 위해 엑셀의 데이터 유효성 검사(Data Validation) 기능을 활용합니다.
- 먼저 계층 구조 목록을 입력할 결과 표를 만듭니다.
- B5 셀을 선택합니다.
- [데이터] 탭의 데이터 도구 그룹으로 이동합니다.
- 데이터 유효성 검사 옵션을 클릭합니다.
- 데이터 유효성 검사 대화 상자가 나타납니다.
- 허용(Assume) 드롭다운 목록에서 목록(List)을 선택합니다.
- 원본(Source) 입력란에 아래 수식을 입력합니다.
=Hierarchy!$B$5:$B$9
- 마지막으로 확인(OK) 버튼을 클릭합니다.
선택한 셀 옆에 드롭다운 버튼이 생성된 것을 확인할 수 있습니다.
- 이제 Ctrl + C를 눌러 B5 셀을 복사합니다.
- 그런 다음 B6:B9 범위를 선택합니다.
- [붙여넣기] 드롭다운 메뉴를 엽니다.
- 선택하여 붙여넣기(Paste Special) 옵션을 선택합니다.
- 선택하여 붙여넣기 창이 나타나면,
- [붙여넣기] 항목 중 유효성 검사(Validation) 옵션에 체크합니다.
- 마지막으로 확인(OK) 버튼을 클릭합니다.
나머지 셀에도 동일하게 드롭다운 버튼이 표시되는 것을 볼 수 있습니다.
- 각 셀의 드롭다운 목록에서 원하는 값을 선택해 입력합니다.
함께 읽으면 좋은 글: 엑셀에서 행 계층 구조 추가하는 방법 (2가지 쉬운 방법)
⦿ 3단계: 데이터 유효성 검사로 도시 목록 만들기
이 단계에서는 City(도시) 열에 데이터 유효성 검사를 적용합니다. 선택한 주에 따라 도시 목록이 자동으로 바뀌도록 OFFSET과 MATCH 함수를 함께 사용합니다.
- C5 셀을 선택해 데이터 유효성 검사를 적용합니다.
- 앞서와 같은 방법으로 데이터 유효성 검사 창을 엽니다.
- 원본(Source) 입력란에 아래 수식을 입력합니다.
=OFFSET(Hierarchy!B$11,1,MATCH($B5,Hierarchy!$B$11:$F$11,0)-1,5,1)
- 확인(OK) 버튼을 클릭합니다.
- 완성된 데이터 세트를 확인합니다.
드롭다운 목록과 그 안의 선택 옵션이 표시됩니다. 이 설정을 C6:C9 범위까지 확장합니다.
- C5 셀을 선택합니다.
- Ctrl + C로 셀을 복사합니다.
- C6:C9 범위를 선택한 뒤, Ctrl + Alt + V를 눌러 선택하여 붙여넣기를 실행합니다.
- 선택하여 붙여넣기 창에서 유효성 검사(Validation) 옵션에 체크합니다.
- 마지막으로 확인(OK) 버튼을 클릭합니다.
이제 모든 행에서 데이터 유효성 검사 드롭다운 목록을 통해 도시를 선택할 수 있습니다.
함께 읽으면 좋은 글: 엑셀에서 다단계 계층 구조 만드는 방법 (2가지 쉬운 방법)
⦿ 4단계: 우편번호 드롭다운 목록 만들기
이제 Zip(우편번호) 열에 데이터 유효성 검사를 적용합니다. 이 유효성 검사는 C5:C9 범위에서 선택된 도시 값을 기준으로 동작하므로, 행마다 참조 셀이 다르다는 점에 유의해야 합니다.
- 먼저 D5 셀을 선택합니다.
- 앞서와 같이 데이터 유효성 검사 창을 엽니다.
- 원본(Source) 입력란에 아래 수식을 입력합니다.
=OFFSET(Hierarchy!B$19,1,MATCH($C$5,Hierarchy!$B$19:$F$19,0)-1,5,1)
- 확인(OK) 버튼을 클릭합니다.
- 데이터 세트로 돌아가 드롭다운 목록의 아래쪽 화살표를 클릭합니다.
목록에서 해당 도시의 우편번호를 확인할 수 있습니다. 같은 방식으로 나머지 행에도 각각 맞는 수식을 적용합니다.
- D6 셀의 데이터 유효성 검사 수식
=OFFSET(Hierarchy!B$27,1,MATCH($C$6,Hierarchy!$B$27:$F$27,0)-1,5,1)
- D7 셀의 데이터 유효성 검사 수식
=OFFSET(Hierarchy!B$35,1,MATCH($C$7,Hierarchy!$B$35:$F$35,0)-1,5,1)
- D8 셀의 데이터 유효성 검사 수식
=OFFSET(Hierarchy!B$43,1,MATCH($C$8,Hierarchy!$B$43:$F$43,0)-1,5,1)
- D9 셀의 데이터 유효성 검사 수식
=OFFSET(Hierarchy!B$51,1,MATCH($C$9,Hierarchy!$B$51:$F$51,0)-1,5,1)
- 완성된 데이터 세트를 다시 확인합니다.
이제 각 행에서 드롭다운 목록을 통해 해당 도시의 우편번호를 자유롭게 선택할 수 있습니다.
마무리
이번 글에서는 엑셀의 데이터 유횅성 검사 기능과 OFFSET, MATCH 함수를 활용해 주, 도시, 우편번호로 이어지는 계층 구조를 만드는 방법을 단계별로 살펴보았습니다. 상위 선택 값에 따라 하위 목록이 자동으로 변경되는 구조는 설문 양식, 배송 정보 입력 등 다양한 실무 상황에서 활용도가 높습니다. 이 글이 여러분의 작업에 도움이 되기를 바랍니다. 더 많은 엑셀 팁은 Exceldemy.com에서 확인하실 수 있으며, 의견은 댓글로 남겨주세요.
관련 글
- 엑셀에서 계층 구조 차트 만드는 방법 (3가지 쉬운 방법)
- 엑셀 피벗 테이블에서 날짜 계층 구조 만들기 (쉬운 단계별 가이드)
- 엑셀에서 SmartArt 계층 구조 활용하는 방법 (쉬운 단계별 가이드)