엑셀 상위 목록에 종속되는 이중 목록상자 만들기
하위 목록 범위를 상위 항목과 같은 이름으로 정의한 뒤, 데이터 유효성 검사 원본에 =INDIRECT($C4) 를 넣으면 상위 선택에 따라 목록이 바뀝니다.
드롭다운 목록은 오타를 막고 입력을 통일해 줍니다. 여기에 한 걸음 더 나아가 상위 항목을 고르면 하위 목록이 따라 바뀌도록 만들면, 항목이 많아도 고르기 쉬워집니다.
1. 기본 드롭다운 만들기
- 목록에 넣을 항목을 시트 한쪽에 적고, 범위를 선택한 뒤 Ctrl + T 로 표로 바꿉니다. 표로 만들어 두면 나중에 항목을 추가해도 목록에 자동으로 반영됩니다.

목록 항목을 표로 지정합니다. - 드롭다운을 넣을 범위(C4:C11)를 선택하고 [데이터] - [데이터 유효성 검사] 를 누릅니다. [설정] 탭에서 제한 대상을 '목록' 으로 고르고, 원본에 머리글을 뺀 값 범위(G4:G5)를 지정한 뒤 [확인]을 누릅니다.

제한 대상을 '목록'으로 지정합니다. - 선택한 범위에 드롭다운이 생겼습니다.

드롭다운 목록이 완성되었습니다.
오빠두Tip : 목록을 펼칠 때 ▼ 를 클릭하지 않아도 됩니다. 셀을 고른 상태에서 Alt + ↓ 를 누르면 목록이 바로 펼쳐집니다.
2. 이중 종속 드롭다운 만들기
C열의 '소속'을 고르면 D열의 '본부'가 그에 맞게 바뀌도록 만들어 보겠습니다. 핵심은 하위 목록의 이름을 상위 항목 이름과 똑같이 정의하는 것입니다.
- 하위 목록을 상위 항목별로 각각 만듭니다. '서울사무소' 아래에 그 소속 본부들을, '제조공장' 아래에 그 본부들을 적고, 각 범위를 Ctrl + T 로 표로 만듭니다.
- 각 표에서 머리글을 뺀 값 범위를 선택하고, 화면 왼쪽 위 이름 상자에 표 머리글과 똑같은 이름(서울사무소 · 제조공장)을 입력해 이름을 정의합니다.
주의 : 이름이 상위 목록의 값과 한 글자라도 다르면 INDIRECT 가 그 이름을 찾지 못해 목록이 비어 버립니다. 띄어쓰기까지 똑같이 맞추세요.
- D열의 드롭다운 범위(D4:D11)를 선택하고 [데이터] - [데이터 유효성 검사] 에서 제한 대상을 '목록', 원본에 아래 수식을 넣습니다.
=INDIRECT($C4)
"원본은(는) 현재 오류 상태입니다. 계속하시겠습니까?" 가 나오면 [예] 를 누릅니다. C4 가 아직 비어 있어서 나오는 안내입니다.

원본에 =INDIRECT($C4) 를 입력합니다. - 이제 소속을 고르면 그에 맞는 본부만 목록에 나타납니다.

상위 선택에 따라 하위 목록이 바뀝니다.
오빠두Tip :
$C4 처럼 열만 고정하는 것이 중요합니다. 열을 고정하지 않으면 아래로 내려갈 때 참조가 옆으로 밀리고, 행까지 고정하면 모든 행이 4행의 값만 바라보게 됩니다.3. 삼중 이상으로 늘리기
방법은 똑같습니다. E열의 '팀' 목록을 본부별로 만들어 표로 지정하고, 각 범위의 이름을 본부 이름과 똑같이 정의합니다.


그다음 E열 범위(E4:E11)의 유효성 검사 원본에 한 칸 앞 열을 참조하는 수식을 넣습니다.
=INDIRECT($D4)

주의 : 이 방식은 이름이 겹치지 않는다는 전제에서만 동작합니다. [서울사무소]와 [제조공장] 아래에 같은 이름의 [마케팅본부]가 있다면 이름을 하나만 정의할 수 있어, 둘 중 한쪽에서만 하위 목록이 나옵니다.
관련 게시글
엑셀 People Graph 로 인포그래픽 만들기
엑셀 2016 이후 기본 제공되는 People Graph 추가 기능으로, 두 열짜리 표만 있으면 사람 모양 인포그래픽 차트를 몇 번의 클릭으로 만들 수 있습니다.
엑셀 스마트아트로 조직도 만들기
[삽입] - [SmartArt]의 계층 구조형으로 틀을 만들고 텍스트 창에서 Tab 키로 수준만 조정하면, 도형을 하나씩 그리지 않고 조직도가 완성됩니다.
엑셀에서 자꾸 뜨는 OneDrive 알림, 완전히 끄는 방법
OneDrive 백업을 꺼도 알림이 남아 있다면, Office가 따로 띄우는 메시지 표시줄을 레지스트리로 꺼야 합니다.
unique 함수가 지원되지 않는 365 이전버전에서는 이렇게 목록을 따로 관리하는게 여간 불편하지 않을 수 없습니다..ㅜ
아래 내용을 참고해주시고, 더 자세한 설명은 진짜쓰는 실무엑셀 교재 93쪽을 참고해보세요 :)
감사합니다.
실무엑셀 책에 내용이 있는지요?
엑셀에서 데이터 유효성 검사로 생성한 드롭다운 목록의 기본 표시 개수는 8개로 고정되어 있습니다. 이 개수를 직접적으로 늘리는 방법은, 현재는 아쉽게도 제공되지 않습니다.🙇♂️
다만, 개발도구의 유저 컨트롤 중, 리스트박스를 사용하면 더 많은 개수의 목록을 표시할 수 있으니, 필요하시다면 한번 사용해보시길 바랍니다.
답변이 도움이 되었길 바랍니다. 감사합니다!
소속 본부
서울사무소 마케팅본부
서울사무소 영업본부
서울사무소 경영지원본부
제조공장 생산본부
제조공장 개발본부
다중조건 목록상자, 표로 간단하게 만들기 :: 이중유효성 검사 - 오빠두엑셀
위 영상 강의를 한번 확인해보시길 바랍니다. :)
감사합니다.