엑셀 다중 데이터유효성 검사 함수 총정리 :: 공식하나로 해결!

강의소개

이번 강의에서는 실무에 바로 적용가능한 '다중 데이터 유효성 목록상자' 를 만드는 방법에 대해 알아봅니다. 이전 강의에서 '표기능'을 활용한 이중 데이터유효성 검사 목록상자를 만드는 방법을 소개해드렸는데요.

표기능을 사용하면 다중조건 목록상자를 간소화하여 만들 수 있지만, 아래 2가지 제한사항이 있어 상황에따라 실무에 바로 적용하기 어려운 단점이 있었습니다.

따라서 많은 양의 데이터를 다중조건 데이터유효성 검사로 적용하려면, '표기능'이 아닌 다른 효율적인 방법이 필요합니다. 따라서 이번강의에서는 '동적범위'를 응용한 다중 데이터 유효성 검사 공식을 사용해 2중, 3중 이상의 다중조건 목록상자를 만드는 방법을 알아봅니다.

다중 데이터 유효성 검사 목록상자 만들기

목록상자에 나열할 각 데이터의 고유값을 추출합니다. 고유값을 추출하는데 크게 3가지 방법이 있으며 각 방법마다 장/단점이 있으므로 상황에 맞춰 사용합니다.

피벗테이블로 고유값을 추출하는 방법

중복값 제거 공식을 이용하는 방법

중복된 항목 제거 기능을 이용하는 방법

다중 데이터유효성 검사 목록상자의 조건이 될 범위를 생성합니다. 본 예제에서는 ‘구분’ 항목이 다중 데이터 유효성 검사의 첫번째 조건이 됩니다.

C. 다중 유효성검사 목록상자 만들기 (1차 조건에 따른 2차목록)

1차 조건에 따른 두번째 목록상자에 사용될 사용자지정범위를 추가합니다. OFFSET 동적범위와 더불어 MATCH 함수 다중조건을 이용합니다. 해당 공식에 대한 자세한 설명은 영상강의 또는 공식 포스트를 참고하세요.

다중 데이터 유효성 검사 공식을 응용하면, 2중, 3중 이상의 데이터 유효성검사 목록상자를 자동화할 수 있습니다.

본 예제에서는 자동차의 ‘브랜드’를 선택하면 해당 브랜드의 ‘차종’이 나오고, ‘차종’을 선택하면 해당 ‘브랜드’와 ‘차종’을 만족하는 ‘제품’이 나열되도록 3중 데이터 유효성검사 목록상자를 만들어봅니다.

A. 데이터 유효성 목록상자를 위한 고유값 추출

피벗테이블을 이용하여 고유값을 추출합니다. 예제파일 ‘다중조건 3단계 이상~’ 시트의 더하기(+) 버튼을 클릭하면 미리 고유값을 나열해둔 피벗테이블이 있습니다.

각 항목별 고유값은 아래의 단계로 생성합니다.

B. 각 단계별 목록상자를 위한 사용자지정범위 만들기

각 단계별로 목록상자를 출력하기 위한 사용자지정범위를 생성합니다. ‘삽입’ – ‘이름관리자’ 또는 단축키 CTRL + F3 키를 눌러 이름관리자로 이동한 뒤, ‘새로만들기’ 버튼으로 아래 3개의 사용자 지정범위를 만들어주세요.

3개의 사용자지정범위 생성을 완료하였으면 각 항목별로 지정된 셀에 다중 데이터유효성 검사 목록상자를 적용합니다.

C. 이중, 삼중 데이터유효성 검사 목록상자 만들기

데이터 유효성검사를 적용할 셀을 선택한 뒤, ‘데이터’ – ‘데이터 유효성검사’로 목록상자를 생성합니다.

데이터유효성 검사를 적용할 때, 아래와 같은 오류가 표시될 수 있으나 무시하고 진행합니다.

각 셀에 데이터유효성 목록상자를 적용하면, [브랜드] – [차종] – [제품명]으로 이어지면서 제한된 조건안의 목록만 선택가능 하도록 사용자를 제어할 수 있습니다.

동적범위를 응용한 다중조건 데이터유효성 목록상자를 이용할 경우, 아래 2가지를 주의해야 합니다.

A. 목록상자 출력을 위한 고유값 범위는 반드시 연속되어야 합니다.

목록상자 출력을 위한 고유값 범위가 연속되어 입력되지 않을 시, 하위 단계의 데이터 유효성검사 목록상자에서 옳지 않은 값을 반환할 수 있습니다.

예를 들어, 아래와 같이 ‘커피’ 구분의 제품이 연속되지 않고 떨어져서 입력되었다고 가정하겠습니다.

그럴 경우, 하위단계의 목록상자에는 ‘커피’ 구분이 아닌 ‘얼그레이’ 제품이 보이게 됩니다.

이 문제는 피벗테이블을 이용하여 고유값을 출력하면 해결할 수 있습니다. 피벗테이블로 고유값을 출력하면 값이 ‘자동 정렬’ 되므로 원본 데이터가 순서에 상관없이 입력되어도 문제가 발생하지 않습니다.

B. 피벗테이블 사용할 경우, 신규 데이터 추가 시 ‘데이터 새로고침’ 작업 필요

피벗테이블을 사용할 경우 새로운 데이터가 추가될 때마다 ‘데이터 새로고침’으로 피벗테이블을 갱신해야 합니다. 특정 피벗테이블만 갱신하려면, 피벗테이블 선택 후 ‘데이터’ – ‘모두새로고침’ – ‘ 새로고침’을 선택합니다.

또는 ‘데이터’ – ‘모두 새로고침’ 버튼을 클릭하여 한번에 모든 피벗테이블을 업데이트 할 수도 있습니다.

데이터유효성 목록상자 완성 후 코드번호 출력

선택된 목록상자의 모든 조건이 완성되면, 각 조건들을 만족하는 특정 값(예제파일에서는 제품의 코드명)을 추출해야 합니다.

여러 개의 조건을 만족하는 VLOOKUP 검색 방법은 INDEX 함수와 MATCH 함수를 사용하여 작성합니다. 관련된 내용은 별도로 영상강의를 준비해드렸으니 관련 링크를 참고하세요.

자주묻는 질문

Q1. 다중조건 데이터 유효성 목록상자를 여러셀에 동시에 적용하고 싶어요

다중데이터 유효성 목록상자를 여러셀에 걸쳐 동시에 적용해야 할 상황이 있습니다. 그럴 경우, 공식에 사용된 참조값(예제파일의 P2셀, Q2셀)의 참조방식을 변경합니다. 기존 절대참조($A$1) 방식에서 열절대참조($A1)방식으로 변경하면 되는데요. 혼합참조에 대한 자세한 설명은 관련 포스트를 참고하세요.

절대참조에서 열절대참조형식으로 변경하기 위하여, 수식의 $P$2셀과 $Q$2셀을 선택한 뒤, F4키를 2번 누르면 열절대참조로 변경됩니다.

따라서, 예제파일의 [예제2_목록상자_2] 와 [예제2_목록상자_3] 의 수식을 아래처럼 변경하면 여러셀에 동시에 다중 데이터유효성 목록상자를 적용할 수 있습니다.

• 주의사항 : 이름정의범의 공식을 입력할 때, 절대참조($)가 붙지않은 셀이 참조될 경우 최종입력시 값이 변경될 수 있습니다.
  따라서 반드시 '데이터 유효성 목록상자'가 적용될 첫번째 셀이 선택된 상태로 이름정의범위 공식을 입력합니다.

  

링크] MS 홈페이지 데이터 유효성검사 상세설명 바로가기