실무자 필수! 엑셀 다중 드롭다운 및 INDIRECT 함수 활용법 (데이터 유효성 검사)

엑셀 데이터 유효성 검사와 INDIRECT 함수를 활용해 대분류에 따라 자동으로 연동되는 다중 드롭다운 만드는 실무 필수 자동화 팁!

이 글대로 하면 A열에서 '서울'을 고르면 B열에 서울 지역만, '경기'를 고르면 경기 지역만 나오는 연동 드롭다운을 만들 수 있습니다. 필요한 건 '선택 영역에서 이름 만들기'와 INDIRECT 함수 두 가지입니다. 엑셀 2016 이후 버전과 Microsoft 365 기준입니다.

준비: 목록 표 만들기

드롭다운에 쓸 원본 표를 먼저 만듭니다. 입력하는 시트와 다른 시트(예: '목록' 시트)에 두면 나중에 관리하기 편합니다.

• 1행에는 대분류를 가로로 적습니다. 예) 서울, 경기, 부산
• 각 대분류 아래 칸에 해당 소분류를 세로로 적습니다. 예) 서울 아래 강남구, 서초구, 마포구

다중 드롭다운 구현을 위해 열 단위로 정돈된 지역별 대분류 및 하위 소분류 데이터 원본 예시 엑셀 시트

대분류 이름에는 띄어쓰기를 넣지 않는 게 좋습니다. 이유는 마지막 단락에서 설명합니다.

따라하기

1. 표 전체를 선택합니다. 1행 대분류부터 맨 아래 소분류까지 한 번에 드래그합니다.
2. 수식 ➔ 선택 영역에서 만들기를 누르고, '첫 행'만 체크한 뒤 확인합니다. 이제 '서울'이라는 이름이 서울 아래 칸들을 가리키게 됩니다. 수식 ➔ 이름 관리자에서 서울, 경기, 부산이 만들어졌는지 확인할 수 있습니다.
3. 대분류 드롭다운 만들기: 입력 시트의 A2 셀을 선택하고 데이터 ➔ 데이터 유효성 검사 ➔ 제한 대상 목록 ➔ 원본에 목록 표의 1행(대분류 칸들)을 드래그해 넣습니다.
4. 소분류 드롭다운 만들기: B2 셀을 선택하고 같은 방법으로 목록을 고른 뒤, 원본에 =INDIRECT(A2)를 직접 입력합니다. A2를 마우스로 클릭해 넣으면 $A$2로 들어가는데, 아래 행에 복사해 쓸 거라면 달러 기호를 지워 A2로 바꿉니다.
5. 아래로 복사: A2:B2를 선택해 필요한 행까지 채우기 핸들로 끌어내리면 행마다 연동 드롭다운이 적용됩니다.

엑셀 데이터 유효성 검사 '원본' 입력란에 가변적인 범위를 참조하기 위한 =INDIRECT(A2) 수식이 입력된 설정 화면

A2가 비어 있는 상태에서 B2를 설정하면 "원본이 현재 오류로 계산됩니다"라는 경고가 나올 수 있습니다. 대분류가 아직 선택되지 않아서 그런 것이니 '예'를 누르고 진행해도 됩니다.

자주 막히는 곳

대분류에 띄어쓰기가 있을 때: '서울 강남'처럼 공백이 있으면 엑셀은 이름을 서울_강남으로 만듭니다. 이름에는 공백을 쓸 수 없기 때문입니다. 이때는 B열 원본을 =INDIRECT(SUBSTITUTE(A2," ","_"))로 넣으면 공백을 밑줄로 바꿔서 찾아갑니다. 숫자로 시작하는 대분류(예: 1팀)도 이름 규칙에 걸리니 '팀1'처럼 바꾸는 편이 낫습니다.

대분류를 바꿨는데 소분류가 그대로 남을 때: 유효성 검사는 입력할 때만 검사하기 때문에, A열을 '경기'로 바꿔도 B열에 이미 들어간 '강남구'는 지워지지 않습니다. 대분류를 바꿀 때 소분류도 다시 고르는 습관을 들이거나, 조건부 서식으로 짝이 맞지 않는 칸을 색으로 표시해 두면 실수를 줄일 수 있습니다.

소분류 목록을 나중에 늘릴 때: 이름이 가리키는 범위는 자동으로 늘어나지 않습니다. 항목을 추가했다면 수식 ➔ 이름 관리자에서 해당 이름의 참조 범위를 같이 늘려주세요.