엑셀 이중 유효성 검사 목록상자란, 이전에 선택된 조건을 만족하는 값만 불러오는 데이터 유효성 검사의 응용기능의니다. 이중 유효성 목록상자는 아래의 함수 공식으로 아주 간단하게 적용할 수 있습니다. 단, 이중유효성 목록상자 공식은 셀에 직접 입력하지 않고, '이름정의범위'를 사용합니다.
이중유효성 목록상자로 데이터 입력을 제한합니다
사용예제
이중유효성 검사로 조건별 데이터 입력을 제한할 수 있습니다
함수공식
= OFFSET (시작셀, MATCH(참조값, 찾을범위,0)-1, n, COUNTIF(찾을범위,참조값), 1)
이중 유효성 검사 함수 공식은 '이름정의범위'를 이용합니다. 따라서 엑셀 화면 상단의 [수식] - [이름관리자]로 이동한 뒤, [새로 만들기]를 클릭합니다. 새로운 이름정의범위를 생성하기 위한 창이 나타나면 아래 수식을 입력합니다.
[수식] - [이름관리자]로 이동하여 '이름정의범위'를 생성합니다.
이름 : 제품선택
참조대상 : = OFFSET($B$3, MATCH($E$3,$B$3:$B$11,0)-1,1,COUNTIF($B$3:$B$11,$E$3), 1)
이중 유효성 목록상자를 위한 '이름정의범위'를 추가합니다.
공식 원리 이해하기
COUNTIF 함수의 동작원리
= COUNTIF ( 참조범위, 찾을값 )
COUNTIF 함수는 '참조범위' 안에서 '찾을값'의 개수를 반환합니다. 예제에서는 '구분' 범위에서 '채소' 값의 개수를 세어 반환합니다.
= COUNTIF($B$3:$B$11,$E$3)
= 3 '// '구분' 범위에서 '채소'는 3개 입니다.
'구분' 범위에서 '채소'의 개수를 반환합니다.
MATCH 함수의 동작원리
= MATCH ( 찾을값, 참조범위, 0 )
MATCH 함수는 참조범위에서 찾을값이 첫번째로 위치한 셀의 위치를 숫자로 반환합니다. 예제에서는 '구분'범위에서 '채소'가 첫번째로 위치한 셀의 순번인 '4'를 반환합니다.
=MATCH($E$3,$B$3:$B$11,0)
=4 '// '구분' 범위에서 '채소'의 첫번째 셀은 4번째에 있습니다.
MATCH 함수는 찾을값의 위치를 반환합니다.
OFFSET 함수의 동작원리
= OFFSET( 시작셀, 아래이동, 우측이동, [상하넓이], [좌우넓이] )
OFFSET 함수는 시작셀을 기준으로 아래방향, 오른쪽방향으로 이동한 뒤, 이동한 곳에서 상하/좌우로 확장된 범위를 반환합니다.
엑셀 이중유효성 목록상자 공식에서 OFFSET 함수에 들어가는 인수는 아래와 같습니다.
1. 시작셀 2. 아래이동 : MATCH 함수 결과값 -1 (*OFFSET 함수는 아래이동으로 '1' 입력시 바로 1칸을 이동하므로 MATCH 함수 결과값에서 1을 빼주는 것에 주의합니다.) 3. 우측이동 : n 4. 상하넓이 : COUNTIF 함수 결과값 5. 좌우넓이 : 1
이제 각 인수를 OFFSET 함수에 대입하면 아래의 순서로 동작합니다.
= OFFSET($B$3, MATCH($E$3,$B$3:$B$11,0)-1,1,COUNTIF($B$3:$B$11,$E$3), 1)
= OFFSET($B$3, 4-1, 1, 3, 1)
= OFFSET($B$3, 3, 1, 3, 1)
= B3셀을 기준으로 아래로 3칸 이동, 우측으로 1칸 이동한 뒤, 위아래로 3칸을 넓힌 범위를 반환합니다.
이중 유효성 목록상자 적용하기
이중 유효성 목록상자를 적용할 셀(예제파일 F3셀)을 선택합니다. 엑셀 화면 상단에서 [데이터] - [데이터 유효성검사] 로 이동합니다. [데이터] - [데이터유효성검사] 로 이동합니다.데이터 유효성검사 적용을 위한 창이 나타나면, [제한대상]에서 '목록'을 선택합니다. 이후 [원본]에 커서를 이동한 뒤, 키보드 F3키를 누르면 '이름정의범위'를 선택하기 위한 창이 나타납니다. 이중유효성목록으로 적용할 범위를 선택한 뒤, [확인]을 눌러 마무리합니다.
이름정의범위를 이중 유효성검사 목록상자로 지정합니다
이중 유효성 목록상자 사용하기
이중 유효성 목록상자의 조건(예제파일 'E3'셀)을 변경하면 조건에 따라 목록상자의 값이 바뀌는 것을 확인할 수 있습니다.
= OFFSET($B$3, MATCH($E$3,$B$3:$B$11,0)-1,1,COUNTIF($B$3:$B$11,$E$3), 1)
