목차
팀장님: “서울시에 해운대구가 왜 있어?”
안녕하세요. 실무 엑셀의 빈틈을 채워드리는 직장인 엑셀 꿀팁 저장소입니다!
지난 포스팅에서는 조건부 서식으로 데이터의 흐름을 시각화하는 방법을 배웠습니다. 시각화도 중요하지만, 애초에 데이터가 ‘정확하게’ 입력되지 않으면 아무 소용이 없겠죠?
여러 사람이 함께 입력하는 파일을 관리하다 보면 기상천외한 오타들이 등장합니다.
‘서울특별시’를 ‘서울시’로 적는 건 애교고, ‘서울’을 선택했는데 세부 지역에 부산의 ‘해운대구’를 적어넣는 논리적 오류도 빈번하죠.
오늘은 이런 실수를 원천 봉쇄하는 기술, 엑셀 이중 드롭다운(Dependent Drop-down List)을 만들어보겠습니다.
앞의 항목을 무엇을 고르냐에 따라 뒤의 목록이 자동으로 바뀌는 마법 같은 기능입니다.
기초 공사: 이름 정의(Name Range)가 핵심이다
이 기능을 구현하려면 ‘이름 정의’라는 개념을 먼저 잡아야 합니다. 엑셀의 특정 범위에 별명을 붙여주는 것이죠. 우리는 각 카테고리의 하위 목록들에 ‘대분류의 이름’과 똑같은 별명을 붙여줄 겁니다.
- 서울 목록 범위 → 이름: “서울”
- 경기 목록 범위 → 이름: “경기”
일일이 지정할 필요 없습니다. 단축키 하나면 끝납니다.
- 원본 데이터를 블록 지정합니다.
- 단축키 [Ctrl + Shift + F3]을 누릅니다.
- ‘선택 영역에서 이름 만들기’ 창이 뜨면 [첫 행]에만 체크하고 확인을 누릅니다.
이제 엑셀은 각 열의 데이터를 제목(첫 행)의 이름으로 기억하게 됩니다.
1단계: 대분류 목록 만들기
이제 데이터를 입력할 시트로 와서 첫 번째 드롭다운(대분류)을 만듭니다. 이건 아주 간단합니다.
- 입력할 셀을 선택하고 [데이터] 탭 – [데이터 유효성 검사]를 클릭합니다.
- 제한 대상을 ‘목록’으로 바꿉니다.
- 원본 칸에 대분류 타이틀이 있는 범위를 드래그합니다 (예:
=$A$1:$C$1).
2단계: INDIRECT 함수로 소분류 연결하기
여기가 하이라이트입니다. 두 번째 드롭다운(소분류)은 첫 번째 셀의 값에 따라 참조해야 할 ‘이름’이 바뀌어야 합니다.
이때 사용하는 것이 INDIRECT 함수입니다. 텍스트로 된 주소를 실제 참조 가능한 주소로 바꿔주는 함수죠.
INDIRECT 함수의 원리가 궁금하다면 MS 공식 도움말을 확인해보세요. 하지만 지금은 공식만 외우셔도 됩니다.
=INDIRECT(대분류가_있는_셀)
소분류를 넣을 셀에서 [데이터 유효성 검사]를 켜고, 원본 칸에 다음과 같이 입력하세요.
=INDIRECT(D2)
(단, A2는 대분류가 입력된 바로 옆 셀 주소여야 합니다.)
이렇게 하면 A2 셀에 “서울”이 입력되어 있을 때, 엑셀은 자동으로 아까 우리가 정의해둔 “서울” 범위(강남구, 서초구…)를 불러옵니다. “경기”로 바뀌면 “경기” 범위를 불러오고요.
주의사항: 이름에 공백이 있다면?
이름 정의를 할 때 주의할 점이 있습니다. 엑셀의 이름에는 띄어쓰기(공백)가 들어갈 수 없습니다.
만약 대분류가 “충청 남도”처럼 띄어쓰기가 되어 있다면, 엑셀은 자동으로 이름을 “충청_남도”로 밑줄(_)을 넣어 저장합니다. 그래서 이중 드롭다운을 만들 때는 가급적 대분류 이름에 공백을 없애거나, =INDIRECT(SUBSTITUTE(A2," ","_")) 처럼 공백을 밑줄로 바꿔주는 추가 작업이 필요합니다. 처음에는 공백 없이 실습해 보시는 것을 추천합니다.
마무리하며
오늘은 엑셀 이중 드롭다운을 활용해 오타 없는 완벽한 입력 서식을 만드는 법을 배웠습니다. ‘이름 정의’와 ‘INDIRECT 함수’, 이 두 가지 콤비만 기억하면 삼중, 사중 드롭다운도 문제없습니다.
다음 시간에는 조건에 맞는 데이터만 쏙쏙 골라 합계를 구해주는 다중 조건 합계의 끝판왕, SUMPRODUCT 함수에 대해 알아보겠습니다. 여러분의 칼퇴를 응원합니다!