- 최초 작성일: 2026-03-07
- 최종 수정일: 2026-03-07
- 조회수: 441 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: 엑셀 다중 드롭다운, 아직도 INDIRECT 쓰시나요? 이름 정의 없이 1분 완성
들어가기 전에
실무에서 엑셀로 데이터를 입력할 때, '대분류'를 선택하면 그에 맞는 '중분류'만 나타나도록 연결하는 다중(또는 종속) 드롭다운 목록, 다들 한 번쯤 만들어 보셨으리라 생각합니다. 이 기능을 구현하기 위해 보통은 INDIRECT 함수를 많이 사용해 왔습니다.
그런데 이 방법은 항목이 늘어날 때마다 '이름 관리자'에 들어가서 일일이 이름을 정의하고, 띄어쓰기 오류 때문에 수식이 깨지거나 파일 속도가 느려져서 답답할 수 있습니다. 엑셀 최신 버전을 사용하시는 분들을 위해 INDIRECT 함수의 번거로운 사용법에서 벗어날 수 있는 방법을 소개합니다.
- 최신 글을 불러오는 중...
엑셀 다중 드롭다운, 아직도 INDIRECT 쓰시나요? 이름 정의 없이 1분 완성
항목이 늘어날 때마다 이름 관리자에서 일일이 범위를 수정하느라 지치셨거나, INDIRECT 함수 사용 시 띄어쓰기 오류로 고생하셨던 분들이라면 동적 배열과 스필 연산자(#)의 마법에 주목해 보세요.
최신 함수를 활용해 복잡한 수식을 심플하게 최적화하고, 데이터 오기입을 방지하는 조건부 서식 자동 캐치 방법까지 단숨에 완성하는 방법을 알아봅니다.
이번 강의에서 습득할 수 있는 4가지
- 표(Table) 기능과 함수로 대분류 만들기
- 종속형 하위 목록(중분류, 소분류) 실시간 연결하기
- 스필 연산자(#)로 범위 자동 확장하기
- 조건부 서식으로 잘못된 데이터 '자동 캐치'하기
1. 표(Table) 기능과 SORT, UNIQUE 함수로 대분류 만들기
대분류, 중분류, 소분류가 있는 상품 목록 데이터가 있습니다. 종속 드롭다운을 만들기 위한 첫 번째 단계는 원본 데이터를 준비하고 첫 번째 드롭다운의 소스를 만드는 것입니다. 원본 영역 내부의 셀을 하나 선택하고, Ctrl + T를 눌러 '표'로 변환합니다. 표 이름은 'T_상품마스터'라고 지정하면 데이터가 추가될 때 자동으로 범위가 확장되어 관리가 아주 편해집니다.
F4: =SORT(UNIQUE(T_상품마스터[대분류]))이제 대분류 열에서 중복이 제거되고 가나다순으로 정렬된 목록을 실제 입력 양식 셀에 연결해 줍니다. 데이터 유효성 검사에서 제한 대상을 '목록'으로 바꾸고 아래와 같이 수식을 입력합니다.
J4: =$F$4#여기서 핵심은 셀 주소 뒤에 붙은 '#' 기호, 즉 스필 연산자입니다. 이 기호 하나만 붙이면 데이터가 펼쳐진 전체 범위를 엑셀이 알아서 인식하므로, 나중에 원본에 새로운 대분류가 추가되어도 드롭다운 범위를 수동으로 수정할 필요가 없어집니다.
2. FILTER 함수로 종속형 하위 목록(중분류, 소분류) 순차적으로 만들기
두 번째는 핵심이 되는 종속형 하위 목록을 만들 차례입니다. 하위 목록은 상위 셀(J4)의 값을 참조해야 하는데, J4 셀이 비어있으면 조건 값을 찾지 못해 #CALC! 에러를 띄웁니다. 따라서 방금 만든 J4 셀의 대분류 드롭다운에서 미리 'IT자산'을 하나 선택해 두고 시작하는 것이 좋습니다.
G4: =SORT(UNIQUE(FILTER(T_상품마스터[중분류], T_상품마스터[대분류]=J4)))위 수식은 사용자가 선택한 J4 셀(IT자산)과 대분류가 같은 것만 필터링(FILTER)해서 중분류를 가져옵니다. 바로 K4 셀(중분류 입력칸)에 데이터 유효성 검사를 =$G$4# 으로 지정해 주고 드롭다운에서 값을 선택합니다. 동일한 원리로 마지막 소분류 수식도 작성해 줍니다.
H4: =SORT(UNIQUE(FILTER(T_상품마스터[소분류], T_상품마스터[중분류]=K4)))그리고 L4 셀(소분류 입력칸)에 유효성 검사를 =$H$4# 으로 적어주면 3단계 종속 드롭다운 연결이 완벽하게 끝납니다. INDIRECT 함수처럼 항목마다 이름을 정의할 필요가 없습니다.
3. 데이터 '자동 캐치'하기 (멤버십 회원용 콘텐츠)
마음이 바뀌어 상위 분류를 다른 항목으로 변경했을 때, 엑셀은 하위 셀에 이미 입력된 기존 데이터를 자동으로 지워주지 않아 데이터 입력 오류가 발생할 수 있습니다. 조건부 서식을 활용해 잘못된 데이터를 빨간색 경고등으로 자동 캐치하는 유용한 실무 팁을 확인해 보세요!
자주 묻는 질문 (FAQ)
Q1. 스필 연산자(#)는 어떤 역할을 하나요?
동적 배열 수식이 입력된 범위 전체를 엑셀이 알아서 인식하게 해주는 기호입니다. 이 기호를 데이터 유효성 검사 원본에 활용하면 원본 데이터가 늘어나거나 줄어들어도 범위를 매번 수정할 필요가 없습니다.
Q2. 하위 목록 수식을 입력했더니 #CALC! 에러가 뜹니다.
상위 드롭다운 셀(예: J4 셀)에 아직 아무 값도 선택되지 않아서 발생하는 자연스러운 현상입니다. 먼저 상위 드롭다운에서 임의의 값을 하나 선택해 두시면 조건이 성립되어 에러가 사라지고 하위 목록이 정상적으로 출력됩니다.
Q3. INDIRECT 함수는 이제 쓰지 말아야 하나요?
그렇지 않습니다. 만물은 '적재적소'가 있듯이, 데이터 시트 참조나 확장성이 중요한 다른 여러 작업에서는 INDIRECT가 여전히 매우 강력하고 필수적인 함수입니다. 상황에 맞게 적절히 활용하시기 바랍니다.
Q4. 예제 파일은 어디서 받을 수 있나요?
본문 상단에 있는 다운로드 링크를 통해 실습용 예제 파일을 내려받으실 수 있습니다. 직접 따라 해 보며 INDIRECT를 대체하는 새로운 워크플로를 경험해 보시길 바랍니다.
마치며
오늘 배운 동적 배열과 스필 연산자를 활용한 새로운 다중 드롭다운 방법, 어떠셨나요? 무겁고 제약이 많던 기존 방식을 대신해서 훨씬 빠르고 유연하게 실무 데이터를 관리해 보시기 바랍니다.
