이중 유효성 검사 설정 방법 총정리

방법 1: IF 중첩 수식으로 구현

(1) '대분류'를 설정할 범위를 지정하고 '데이터' 탭의 '데이터 유효성 검사' 아이콘을 클릭합니다.

(2) '데이터 유효성' 대화상자의 '설정' 탭에서 '제한 대상'을 '목록'으로 지정하고, '원본' 항목을 다음과 같이 지정합니다.

(3) 이번에는 '소분류'를 지정할 차례입니다. E3:E13 셀을 범위로 지정하고 '데이터 - 데이터 유효성 검사'를 클릭합니다.

(4) '데이터 유효성' 대화상자에서 '제한 대상'을 '목록'으로 지정하고, '원본' 항목에는 다음 수식을 입력합니다.

=IF($D3=$B$3,$B$8:$B$11,IF($D3=$B$4,$B$14:$B$17,IF($D3=$B$5,$B$20:$B$22,$B$1)))

수식이 얼핏 복잡해 보입니다만, 이해하기 그다지 어렵지는 않습니다. D열, 즉 '대분류'에서 어떤 것이 선택되었느냐에 따라 '소분류'에 표시할 영역을 다르게 설정해 준 것 뿐입니다.

(6) '확인' 버튼을 클릭하여 대화상자를 닫습니다. 이제 '대분류'를 선택하면 선택된 대분류에 해당되는 소분류 항목이 드롭다운 박스에 표시됩니다.

방법 2: COUNTA 동적 범위 + INDIRECT·HLOOKUP

(1) B3 셀을 선택하고 다음 수식을 입력한 다음, C3:E3 셀에도 이 수식을 변경하여 입력합니다. 앞의 [방법 1]과 달리, 각 팀별로 인원 변동이 생길 경우, 매번 수식을 변경하지 않아도 자동으로 범위가 변경되도록 하기 위한 과정입니다.

="B4:B"&COUNTA(B4:B10)+3

Counta 함수를 이용하여 지정한 범위 내에(즉, 각 팀 별로) 값이 들어 있는 셀이 몇 개인지를 파악합니다. 나머지 C3:E3 셀에도 이 수식을 변경하여 입력합니다.

(2) '팀'을 표시할 셀(여기서는 G5 셀)을 선택하고 '데이터 - 유효성 검사'를 선택합니다.

(3) '데이터 유효성' 대화상자의 '설정' 탭에서, '제한 대상'을 '목록'으로 지정합니다. '원본' 항목에는 팀 이름이 들어 있는 B2:E2 영역을 마우스로 드래그하여 지정한 다음, '확인' 버튼을 클릭합니다.

(4) 팀원을 표시할 셀(H5)을 선택하고 '데이터 - 유효성 검사'를 선택합니다. '데이터 유효성' 대화상자의 '설정' 탭에서, '제한 대상'을 '목록'으로 지정하고 '원본' 항목에는 아래와 같은 수식을 입력합니다.

=INDIRECT(HLOOKUP($G$5,$B$2:$E$3,2,FALSE))

Indirect 함수는 '문자열을 셀 참조 형식으로 바꿔 주는 함수'이며, 사용 형식은 아래와 같습니다. Hlookup 함수는 우리가 자주 사용하는 Vlookup 함수의 친척입니다. 참조 범위가 세로(Vertical)냐 가로(Horizontal)냐에 차이가 있을 따름입니다.

방법 3: 수식에 이름을 정의하여 해결

(1) 각종 이름을 정의합니다. 먼저 '비용항목'이라는 이름을 정의해 보도록 하죠. 예제 파일의 'Sheet4'를 열고 '수식' 탭의 '이름 정의' 아이콘을 클릭합니다.

(2) '새 이름' 대화상자에서 '이름'은 '비용항목'이라고 입력하고, '참조 대상'에는 다음 수식을 입력합니다.

Offset과 Counta 함수를 이용하여 '동적 이름 정의(Dynamic Naming)'를 해주었습니다.

=OFFSET(Sheet4!$A$2,0,0,COUNTA(Sheet4!$A:$A)-1)

(3) 급여, 식비, 의생활 등 나머지 이름들도 정의합니다. '수식' 탭의 '이름 관리자' 아이콘을 클릭하면 정의된 이름들을 볼 수 있습니다. 편의 상 범위를 직접 지정하고 이름 정의하였습니다만, 위와 같이 '동적 이름 정의'를 해두는 것도 좋겠습니다.

(4) B열에 비용항목을 입력하기 위한 '유효성 검사'를 설정합니다. B2:B20 셀을 범위로 지정하고 '데이터 - 데이터 유효성 검사'를 선택합니다.

(5) '데이터 유효성' 대화상자에서 다음과 같이 지정합니다. 다른 시트에 정의되어 있는 이름을 불러와서 사용하려면 이름 앞에 등호(=)를 붙여주면 된다는 점에 유의하세요!

(6) C열에는 B열에서 선택된 '비용항목'에 따른 '세부내역'이 표시되어야 합니다. C2:C20 셀을 범위로 지정하고 '데이터 - 데이터 유효성 검사'를 선택합니다. 이번에는 '원본' 항목에 Indirect 함수를 사용하여 다음과 같이 조건을 지정합니다.

=INDIRECT($B2)

이제 '비용항목'을 선택하면, 선택된 비용항목에 따른 '세부내역'이 화면에 표시됩니다.

▶ VBA로 이중 유효성 검사 설정하기

이상 소개해 드린 방법들 외에 다른 방법도 있을 수 있습니다. 좋은 아이디어가 있는 분은 알려주시면 반영 하겠습니다.

정리 — 이중 유효성 검사 세 가지 방법 비교

방법	핵심 함수	특징
1. IF 중첩	IF	간편하지만 항목 많아지면 관리 곤란
2. 동적 범위+조회	COUNTA, INDIRECT, HLOOKUP	인원(항목) 변동에 자동 대응
3. 동적 이름 정의	OFFSET, COUNTA, 이름 정의	여러 시트·수식에서 재사용 용이
4. VBA	-	별도 강좌 참고(이 글에서는 개요만)

자주 묻는 질문 (FAQ)

마치며

VBA에 대한 기초 지식을 공부하실 분은 아이엑셀러 닷컴 사이트 상단 메뉴에서 [Excel 강의] - [Excel 입문]을 먼저 보시면 이해하기 쉽습니다.