엑셀 동적 드롭다운: UNIQUE·SORT·FILTER로 자동 갱신 & 종속 드롭다운
엑셀 동적 드롭다운을 동적 배열로 구현하면 새 데이터가 추가돼도 목록이 자동 갱신됩니다. UNIQUE·SORT·FILTER와 스필(#)을 이용해 카테고리→상품 종속 드롭다운까지 10분 컷으로 완성하세요.
Quick Fix: 10분 완성
- 원본을 Ctrl+T로 테이블화(Sales).
- Lists!J2:
=SORT(UNIQUE(FILTER(Sales[Category], Sales[Category]<>""))) - E열 데이터 유효성 원본:
=Lists!$J$2# - Lists!L2:
=SORT(UNIQUE(FILTER(Sales[Item], Sales[Category]=$E2))) - F열 데이터 유효성 원본:
=Lists!L2#
원리
- UNIQUE로 중복 제거, FILTER로 조건 추출, SORT로 정렬
- 스필(#) 참조를 유효성 원본에 연결해 자동 확장
실무 예제
행별 종속 드롭다운
=SORT(UNIQUE(FILTER(Sales[Item], Sales[Category]=$E2)))선택 전 플레이스홀더
=LET(sel,$E2, IF(sel="", {"(select)"}, SORT(UNIQUE(FILTER(Sales[Item], Sales[Category]=sel)))))월까지 종속
=LET(selCat,$E2, selMonth,$G2,
dMonth, TEXT(Sales[Date],"yyyy-mm")=TEXT(selMonth,"yyyy-mm"),
SORT(UNIQUE(FILTER(Sales[Item], (Sales[Category]=selCat)*dMonth ))))스필 참조가 막힐 때(대안)
이름정의로 J2#를 CategoryList로 만든 뒤 유효성 원본에 =CategoryList 사용.
레거시 환경은 OFFSET/COUNTA 동적 범위를 임시로 사용.
체크리스트
- 테이블화(Sales)
- UNIQUE+FILTER+SORT로 목록 생성
- 유효성 원본은
셀#또는이름정의 - 행별 종속 스필 구조(L2#, L3#…)
- 오류 경고 활성화
Troubleshooting
| 증상 | 원인 | 해결 |
|---|---|---|
| #SPILL! | 스필 충돌 | 아래/오른쪽 공간 비우기 |
| 목록 미갱신 | 범위 고정 | 테이블화 후 J2# 참조 |
| 행마다 동일 목록 | 공용 참조 | L2#, L3# 등 행별 스필 사용 |
| 정책으로 스필 금지 | 조직 보안/버전 | 이름정의로 우회 |
맺음말
동적 드롭다운으로 입력 오류를 줄이고 유지보수를 단순화하세요. 다음 글은 조건부 서식으로 KPI를 자동 하이라이트하는 방법입니다.