엑셀, indirect 함수를 활용한 유효성 검사 동적목록 만들기

엑셀 유효성 검사 완전정복: INDIRECT 함수로 동적 목록 만들기

핵심 포인트

유효성 검사의 목록 기능과 INDIRECT 함수를 결합하면 선택한 값에 따라 자동으로 변하는 드롭다운 목록을 만들 수 있어요. 이를 통해 더욱 스마트하고 효율적인 데이터 입력이 가능합니다.

엑셀에서 데이터를 입력할 때 실수를 줄이고 일관성을 유지하는 가장 좋은 방법 중 하나가 바로 유효성 검사입니다. 특히 목록 기능을 활용하면 드롭다운으로 선택해서 입력할 수 있어 매우 편리하죠. 오늘은 여기에 INDIRECT 함수를 더해서 더욱 똑똑한 유효성 검사를 만드는 방법을 알아보겠습니다.

유효성 검사란?

유효성 검사는 셀에 입력할 수 있는 데이터의 종류나 범위를 제한하는 기능입니다. 잘못된 데이터가 입력되는 것을 방지하고, 데이터의 일관성을 유지할 수 있어요.

유효성 검사의 장점

• 데이터 입력 오류 방지
• 일관된 데이터 형식 유지
• 드롭다운으로 편리한 선택
• 데이터 품질 향상

활용 분야

• 상품 카테고리 선택
• 직급, 부서 입력
• 지역별 세부 분류
• 날짜 범위 제한

기본 유효성 검사 설정하기

1

유효성 검사 적용할 셀 선택

먼저 유효성 검사를 적용하고 싶은 셀이나 범위를 선택합니다.

2

유효성 검사 메뉴 접근

데이터 탭 → 데이터 도구 → 데이터 유효성 검사

또는 Alt + D + L 단축키를 사용할 수 있어요.

3

목록 옵션 선택

유효성 검사 대화상자에서 '허용' 드롭다운에서 '목록'을 선택합니다.

기본 목록 만들기

가장 간단한 방법은 직접 목록 항목을 입력하는 것입니다.

직접 입력 방식

원본: 사과,바나나,오렌지,포도
(쉼표로 구분하여 입력)

설정 전

직접 타이핑으로 입력
오타 발생 가능성

설정 후

드롭다운에서 선택
정확한 데이터 입력

INDIRECT 함수를 활용한 동적 목록

INDIRECT 함수의 진짜 힘은 다른 셀의 값에 따라 목록이 자동으로 변경되는 동적 목록을 만들 때 발휘됩니다. 예를 들어, 지역을 선택하면 해당 지역의 도시만 나타나는 드롭다운을 만들 수 있어요.

INDIRECT 함수란?

INDIRECT 함수는 텍스트로 된 참조를 실제 셀 참조로 변환하는 함수입니다. 즉, 다른 셀의 값을 범위 이름으로 사용할 수 있게 해줍니다.

INDIRECT 함수 기본 문법

=INDIRECT("참조할_범위_이름")

동적 목록 만들기 실습

1

데이터 준비

먼저 카테고리별로 분류된 데이터를 준비합니다.

A열 (서울)	B열 (부산)	C열 (대구)
강남구	해운대구	중구
강서구	부산진구	동구
종로구	서구	서구
마포구	동래구	남구

2

범위 이름 정의

각 열에 대해 범위 이름을 정의합니다.

수식 탭 → 정의된 이름 → 이름 정의

• A1:A4 범위를 "서울"로 명명
• B1:B4 범위를 "부산"으로 명명
• C1:C4 범위를 "대구"로 명명

3

첫 번째 드롭다운 만들기

E1 셀에 지역 선택 드롭다운을 만듭니다.

E1 셀 유효성 검사 설정

원본: 서울,부산,대구

4

INDIRECT를 활용한 두 번째 드롭다운

F1 셀에 선택된 지역에 따른 구 목록을 만듭니다.

F1 셀 유효성 검사 설정

원본: =INDIRECT(E1)

작동 원리:
E1에서 "서울"을 선택하면 → INDIRECT(E1)은 INDIRECT("서울")이 되고 → "서울"이라는 범위 이름을 참조하여 해당 목록을 보여줍니다.

추가로 표 기능을 활용하여 동적 목록을 넣고 싶을 때 : INDIRECT("표1[목록]") 과 같이 따옴표를 이용하여 입력하면 정확하게 작동합니다!

고급 활용: 3단계 연동 드롭다운

INDIRECT 함수를 더 활용하면 3단계 이상의 연동 드롭다운도 만들 수 있습니다.

1

데이터 구조 설계

대분류	중분류	소분류
전자제품	스마트폰	갤럭시,아이폰
전자제품	노트북	삼성,LG,애플
의류	상의	셔츠,티셔츠
의류	하의	바지,치마

2

복합 범위 이름 만들기

언더바(_)를 사용해서 결합된 범위 이름을 만듭니다.

• 전자제품_스마트폰
• 전자제품_노트북
• 의류_상의
• 의류_하의

3

3단계 INDIRECT 공식

3번째 드롭다운 유효성 검사

=INDIRECT(A1&"_"&B1)

A1(대분류)과 B1(중분류)의 값을 결합하여 해당하는 범위를 참조합니다.

자주 발생하는 문제와 해결방법

#REF! 오류

원인: 범위 이름이 정의되지 않았거나 철자가 틀림

해결: 이름 관리자에서 범위 이름 확인 및 수정

빈 드롭다운

원인: 참조하는 셀이 비어있거나 잘못된 값

해결: IF 함수로 빈 값 처리 추가

빈 값 처리를 위한 개선된 공식

=IF(A1="","",INDIRECT(A1))

주의사항:
• 범위 이름에는 공백을 사용할 수 없습니다 (언더바 사용)
• 범위 이름은 대소문자를 구분하지 않습니다
• 한글 범위 이름도 사용 가능하지만 영문을 권장합니다
• 셀을 삭제하면 범위 이름도 함께 확인해야 합니다

실무 활용 팁

효율적인 범위 이름 관리:
• 일관된 명명 규칙 사용 (예: 카테고리_세부항목)
• 이름 관리자(Ctrl + F3)를 통한 정기적인 점검
• 불필요한 범위 이름은 삭제하여 정리
• 범위가 변경될 때는 동적 범위(OFFSET, COUNTA 활용) 고려

동적 범위를 활용한 자동 확장

데이터가 추가되어도 자동으로 범위가 확장되도록 설정할 수 있습니다.

동적 범위 공식

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

이 공식을 범위 이름으로 정의하면 A열에 데이터가 추가될 때마다 자동으로 범위가 확장됩니다.

마무리

INDIRECT 함수를 활용한 유효성 검사는 처음엔 복잡해 보일 수 있지만, 한 번 익혀두면 데이터 입력의 효율성과 정확성을 크게 향상시킬 수 있는 강력한 도구입니다. 범위 이름 정의부터 차근차근 따라하시면서 여러분만의 스마트한 데이터 입력 시스템을 만들어보세요!

 

추가로 궁금하시거나 응용 방법에 대해 문의하고 싶으시면 언제든지 댓글 부탁 드립니다! 

구독~