엑셀 앞 칸 선택에 따라 바뀌는 드롭다운 만들기

상담 기록을 엑셀로 남기다 보면 칸마다 적는 말이 조금씩 달라집니다. 어떤 날은 "방문", 어떤 날은 "방문상담", 또 어떤 날은 "대면"이라고 적습니다. 나중에 개수를 세려고 하면 같은 뜻인데도 따로 세어집니다. 그래서 상담 종류를 드롭다운(칸을 누르면 펼쳐지는 선택 목록)으로 고르게 하고, 세부 항목도 그 종류에 맞는 것만 고르게 하면 기록이 깔끔하게 모입니다.

생활에서도 같은 모양이 자주 나옵니다. 주소록에서 시·도를 고르면 그 안의 시·군·구만 나오게 하거나, 가계부에서 "식비"를 고르면 "장보기·외식·배달"만 나오게 하는 식입니다. 이 글은 엑셀 "목적별 함수 조합" 시리즈의 한 편입니다. 컴활 시험에서도 데이터 유효성 검사 메뉴는 익혀 둘 만하지만, INDIRECT를 붙이는 이 조합은 시험보다 실무용 기술에 가깝습니다.

예제 표 — 목록 시트와 기록 시트

시트(엑셀 아래쪽 탭 하나하나)를 두 개 씁니다. 하나는 드롭다운에 쓸 항목을 모아 두는 목록 시트, 다른 하나는 실제로 적는 상담기록 시트입니다. 표의 맨 위 줄은 열 문자, 맨 왼쪽 칸은 행 번호입니다.

목록 시트 — 1행 머리글이 분류 이름, 그 아래가 분류별 세부 항목입니다.

	A	B	C
1	신규상담	보험금청구	계약관리
2	전화상담	서류안내	주소변경
3	방문상담	접수완료	연락처변경
4	화상상담	지급확인	자동이체변경

상담기록 시트 — B열과 C열에 드롭다운을 겁니다. 이름은 모두 가상입니다.

	A	B	C
1	고객명	상담 종류	세부 항목
2	김하늘	(드롭다운)	(드롭다운)
3	이도윤	(드롭다운)	(드롭다운)
4	박서연	(드롭다운)	(드롭다운)
5	최민준	(드롭다운)	(드롭다운)
6	정수아	(드롭다운)	(드롭다운)
7	한지호	(드롭다운)	(드롭다운)

핵심은 목록 시트의 머리글 글자와 B열에서 고르는 글자가 똑같아야 한다는 점입니다. 띄어쓰기 하나만 달라도 뒤 칸 목록이 뜨지 않습니다.

만드는 순서와 완성 수식

세 단계입니다. 1단계는 이름 붙이기, 2단계는 앞 칸 드롭다운, 3단계가 뒤 칸 드롭다운입니다.

1단계. 분류마다 이름 붙이기

1. 목록 시트에서 A1부터 C4까지 머리글을 포함해 끌어서 선택합니다.
2. [수식] 탭 → [정의된 이름] 묶음 → [선택 영역에서 만들기]를 누릅니다.
3. "선택 영역에서 이름 만들기" 창에서 위쪽 행만 체크하고 [확인]을 누릅니다.

이렇게 하면 머리글 글자가 그대로 이름이 됩니다. "신규상담"이라는 이름은 A2:A4, "보험금청구"는 B2:B4, "계약관리"는 C2:C4를 가리킵니다. [수식] 탭 → [이름 관리자]를 열면 세 이름이 만들어졌는지 볼 수 있습니다.

2단계. 앞 칸(B열) 드롭다운

1. 상담기록 시트에서 B2:B7을 선택합니다.
2. [데이터] 탭 → [데이터 유효성 검사]를 누릅니다.
3. [설정] 탭의 제한 대상(문서에 따라 "허용"으로 적혀 있습니다) 상자에서 목록을 고릅니다.
4. 원본 상자에 아래 수식을 넣고 [확인]을 누릅니다.

=목록!$A$1:$C$1

3단계. 뒤 칸(C열) 드롭다운 — 여기가 조합입니다

1. 상담기록 시트에서 C2부터 아래로 C7까지 선택합니다. 선택을 C2에서 시작하는 것이 중요합니다.
2. [데이터] 탭 → [데이터 유효성 검사] → [설정] 탭 → 제한 대상에서 목록을 고릅니다.
3. 원본 상자에 아래 수식을 넣고 [확인]을 누릅니다.

=INDIRECT($B2)

B열이 아직 비어 있으면 [확인]을 누를 때 원본이 오류라며 계속할지 묻는 창이 뜰 수 있습니다(영문판 문구는 "The Source currently evaluates to an error. Do you want to continue?"). B열에 아무것도 고르지 않아서 생기는 일이니 계속 진행하면 됩니다. B열을 고르고 나면 정상으로 동작합니다.

이제 B열에서 무엇을 고르느냐에 따라 C열 목록이 바뀝니다.

B열에서 고른 값	C열 드롭다운에 나오는 항목
신규상담	전화상담 · 방문상담 · 화상상담
보험금청구	서류안내 · 접수완료 · 지급확인
계약관리	주소변경 · 연락처변경 · 자동이체변경
(비어 있음)	목록이 펼쳐지지 않음

수식 풀어 보기 — 안쪽부터 한 겹씩

=INDIRECT($B2)는 짧지만 세 가지가 겹쳐 있습니다. B2에서 "보험금청구"를 골랐다고 하고 안쪽부터 봅니다.

단계	식	중간 결과
① 셀 값 읽기	$B2	"보험금청구"라는 글자
② 글자를 참조로 바꾸기	INDIRECT("보험금청구")	"보험금청구"라는 이름이 가리키는 범위 = 목록!B2:B4
③ 드롭다운에 채우기	원본 = 목록!B2:B4	서류안내 · 접수완료 · 지급확인

• INDIRECT는 칸에 적힌 글자를 셀 주소나 이름으로 읽어서 그 범위를 돌려주는 함수입니다. Microsoft 문서에 따르면 첫 번째 인수(ref_text)에는 셀 주소뿐 아니라 "참조로 정의된 이름"이 든 셀도 넣을 수 있습니다. 그래서 1단계에서 이름을 붙여 둔 것입니다.
• 이름 정의는 범위에 별명을 붙이는 기능입니다. "목록!$B$2:$B$4"라고 쓰는 대신 "보험금청구"라고 부를 수 있게 됩니다.
• $B2는 열만 고정한 주소입니다. C2에서 시작해 C7까지 한꺼번에 설정했으므로, C3에는 $B3, C4에는 $B4가 적용됩니다. $B$2처럼 행까지 고정하면 모든 행이 B2만 바라보게 되니 주의합니다.

이 계산은 파이썬 formulas 라이브러리로 확인했습니다. "보험금청구" 이름을 INDIRECT로 불렀을 때 3칸짜리 범위가 나오고, 두 번째 칸이 "접수완료"인 것까지 맞았습니다. 드롭다운 화면 자체는 엑셀 기능이라 Microsoft 문서 기준으로 적었습니다.

자주 막히는 곳

• 이름에는 공백을 쓸 수 없습니다. Microsoft 문서의 이름 규칙은 "첫 문자는 문자, 밑줄(_) 또는 백슬래시(\)", 공백 불가, 최대 255자입니다. 머리글이 "보험금 청구"처럼 띄어져 있으면 선택 영역에서 이름을 만들 때 공백이 밑줄로 바뀌어 "보험금_청구"라는 이름이 생깁니다. 이때는 원본을 =INDIRECT(SUBSTITUTE($B2," ","_"))로 바꿔 공백을 밑줄로 바꿔서 찾게 합니다.
• 숫자로 시작하는 분류(예: "1분기")는 이름 규칙에 맞지 않습니다. 분류 이름을 글자로 시작하게 바꾸는 편이 간단합니다.
• 분류마다 항목 개수가 다를 때 한꺼번에 선택해서 이름을 만들면 짧은 쪽 목록 끝에 빈칸이 같이 들어갑니다. [이름 관리자]에서 그 이름을 골라 참조 대상 범위를 항목이 있는 칸까지만 줄여 주면 됩니다.
• 앞 칸을 바꿔도 뒤 칸 값은 그대로 남습니다. Microsoft 문서에 따르면 유효성 검사는 이미 들어 있는 잘못된 데이터를 자동으로 알려 주지 않습니다. [데이터 유효성 검사] 단추 옆 화살표 메뉴에 잘못된 데이터에 원을 그려 주는 기능(영문판 Circle Invalid Data)이 있으니, 기록을 마친 뒤 한 번 돌려 보면 어긋난 칸이 표시됩니다.
• 복사해서 붙여 넣은 값은 드롭다운 검사를 거치지 않습니다. 문서에도 "데이터를 복사하거나 채우는 경우에는 메시지가 나타나지 않습니다"라고 나와 있습니다.

다른 방법과 응용

• 이름을 하나씩 붙이기 — 목록 시트에서 A2:A4를 선택하고, 수식 입력줄 왼쪽의 이름 상자에 "신규상담"을 적고 Enter를 누르면 이름이 하나 생깁니다. 분류가 두세 개뿐이면 이 방법도 빠릅니다.
• 세 단계 드롭다운 — 세부 항목에도 같은 방식으로 이름을 붙이면 D열에 =INDIRECT($C2)를 걸어 한 단계 더 내려갈 수 있습니다. 원리는 똑같습니다.
• 앞 칸 목록을 자동으로 늘리기 — Microsoft 문서는 목록 항목을 엑셀 표(표 기능)로 만들어 두면 항목을 추가·삭제할 때 드롭다운이 자동으로 바뀐다고 안내합니다. 분류가 자주 늘어나는 파일이라면 표 기능을 함께 써 보십시오.
• 엉뚱한 값 입력 막기 — [데이터 유효성 검사] 창의 [오류 경고] 탭에서 경고 방식과 메시지를 정할 수 있습니다. 목록에 없는 글자를 직접 쳤을 때 막을지, 경고만 할지 고릅니다.

자주 묻는 질문

함께 보면 좋은 글

• 엑셀 절대참조·상대참조와 $ 쓰는 법 — $B2처럼 열만 고정하는 원리
• 엑셀 표 기능으로 범위를 표로 바꾸기 — 항목이 늘면 따라 늘어나는 목록
• 엑셀 글 전체 목차 — 목적별 함수 조합 시리즈 모아 보기

참고: 드롭다운 목록 만들기 (Microsoft 지원) · INDIRECT 함수 (Microsoft 지원) · 수식의 이름 (Microsoft 지원)