다중유효성 목록상자 만들기

실습 가이드

강의소개

이번 강의에서는 실무에 바로 적용할 수 있는 '다중 데이터 유효성 목록상자'를 만드는 방법을 살펴봅니다. 이전 강의에서는 '표 기능'을 활용해 이중 데이터 유효성 검사 목록상자를 만드는 방법을 소개해 드렸습니다.

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

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

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

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

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

1. 고유값을 추출할 범위를 선택한 뒤, '삽입' – '피벗테이블' 또는 단축키 ALT – N – V 로 피벗테이블을 생성합니다.

   

2. 오른쪽 '피벗 필드' 창에서 '구분' 항목을 행 영역으로 이동합니다.

   

3. 상단의 '디자인' 탭에서 '총합계' – '행 및 열의 총합계 해제'를 클릭하여 총합계 표시를 해제합니다.

   

4. 피벗테이블을 활용한 고유값 추출이 완료되었습니다.

   

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

1. 고유값 추출을 시작할 셀(예제파일의 H9셀)을 선택합니다.

   

2. 아래 공식을 복사하여 붙여넣은 뒤, 고유값을 나열할 범위까지 자동 채우기로 수식을 확장합니다.
   =IFERROR(LOOKUP(2,1/(COUNTIF($H$8:H8,표9[구분])=0),표9[구분]),"")

   

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

1. 고유값을 추출할 범위를 복사한 뒤, 다른 위치에 붙여넣기 합니다.

   

2. 붙여넣은 범위를 선택한 후 상단의 '데이터' – '중복된 항목 제거'를 클릭합니다. 고유값을 추출할 열을 선택한 뒤 '확인' 버튼을 누릅니다.

   

3. 고유값 추출이 완료되었습니다.

   

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

1. 이전 강의에서 살펴본 OFFSET 함수 동적범위를 응용하여 사용자 지정 범위를 생성합니다.
   '이름 관리자'(단축키: CTRL + F3)로 이동한 뒤, 아래와 같이 사용자 지정 범위를 만들어 주세요.
   이름 : 예제1_목록상자_1
   참조대상 : =OFFSET($D$9,,,COUNTA($D$9:$D$100))

   

2. 데이터 유효성 목록상자를 적용할 셀(예제파일의 O4셀)을 선택한 뒤, '데이터' – '데이터 유효성 검사'로 이동합니다.
   이어서 '제한 대상'은 '목록'을 선택하고 '원본'에서 키보드 F3키를 누르면 '이름 붙여넣기' 창이 열립니다. 방금 추가한 '예제1_목록상자_1'을 선택해 목록상자를 적용합니다.

   

3. 선택한 셀에 데이터 유효성 목록상자가 적용되었습니다.

   

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

1차 조건에 따라 동작하는 두 번째 목록상자에 사용할 사용자 지정 범위를 추가합니다. OFFSET 동적범위와 함께 MATCH 함수를 활용해 다중조건을 처리합니다. 공식의 동작 원리에 대한 자세한 설명은 영상강의 또는 공식 정리 포스트를 참고하세요.

1. 예제 시트의 빈 셀을 선택한 후, 아래 공식을 복사하여 붙여넣습니다. (#VALUE! 오류가 표시되어도 정상 동작이니 그대로 진행하세요.)
   =OFFSET($A$4,MATCH($O$4,표9[구분],0)-1,1,COUNTIF(표9[구분],$O$4),1)

   

2. 붙여넣은 셀을 선택한 후 상단의 '수식 입력줄'을 클릭합니다. 이어서 키보드 F9키를 누르면 1차 조건에 해당하는 제품 목록이 펼쳐져 나타납니다.

   

3. 공식이 정상적으로 동작하는 것을 확인했으니 '사용자 지정 범위'를 추가합니다. 앞서와 동일하게 '수식' – '이름 관리자' 또는 단축키(CTRL + F3)로 이름 관리자에 진입하여 사용자 지정 범위를 만듭니다.
   이름 : 예제1_목록상자_2
   참조대상 : =OFFSET($A$4,MATCH($O$4,표9[구분],0)-1,1,COUNTIF(표9[구분],$O$4))

   

4. 다중 데이터 유효성 목록상자를 적용할 셀을 선택합니다(예제파일의 O5셀). 이후 '데이터' – '데이터 유효성 검사'를 클릭합니다. '제한 대상'은 '목록'을 선택합니다. '원본'을 클릭한 뒤 키보드 F3키를 눌러 방금 추가한 '예제1_목록상자_2'를 원본 범위로 입력하고 '확인'을 눌러 마무리합니다.

   

5. 1차 조건에 연동되는 다중 데이터 유효성 검사 목록상자가 완성되었습니다.

   

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

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

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

피벗테이블을 이용해 고유값을 추출합니다. 예제파일 '다중조건 3단계 이상~' 시트에서 더하기(+) 버튼을 클릭하면 미리 고유값을 정리해 둔 피벗테이블이 펼쳐집니다.

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

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

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

1. '브랜드' 항목 사용자 지정 범위
   이름 : 예제2_목록상자_1
   참조대상 : =OFFSET($G$2,,,COUNTA($G$2:$G$100))
2. '차종' 항목 사용자 지정 범위
   이름 : 예제2_목록상자_2
   참조대상 :
   =OFFSET($I$2,MATCH($P$2,$I$2:$I$100,0)-1,1,COUNTIF($I$2:$I$100,$P$2),1)
3. '제품명' 항목 사용자 지정 범위
   이름 : 예제2_목록상자_3
   참조대상 :
   =OFFSET($L$2,MATCH(1,--(($L$2:$L$100=$P$2)*($M$2:$M$100=$Q$2)),0)-1,2,COUNTIFS($L$2:$L$100,$P$2,$M$2:$M$100,$Q$2),1)

3개의 사용자 지정 범위 생성을 마쳤다면, 각 항목별 지정 셀에 다중 데이터 유효성 검사 목록상자를 적용합니다.

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

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

데이터 유효성 검사를 적용하는 과정에서 아래와 같은 오류 메시지가 나타날 수 있으나, 무시하고 진행해도 됩니다.

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

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

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

목록상자 출력에 사용할 고유값 범위가 연속되지 않고 떨어져 있으면, 하위 단계의 데이터 유효성 검사 목록상자에서 잘못된 값이 반환될 수 있습니다.

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

이런 경우 하위 단계 목록상자에는 '커피' 구분이 아닌 '얼그레이' 제품이 잘못 표시됩니다.

이 문제는 피벗테이블로 고유값을 출력하면 해결할 수 있습니다. 피벗테이블은 고유값을 추출하는 과정에서 값이 '자동 정렬'되므로, 원본 데이터의 입력 순서와 관계없이 항상 정상 동작합니다.

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

피벗테이블을 사용하는 경우, 새로운 데이터가 추가될 때마다 '데이터 새로고침'으로 피벗테이블을 갱신해야 합니다. 특정 피벗테이블만 갱신하려면 해당 피벗테이블을 선택한 뒤 '데이터' – '모두 새로고침' – '새로고침'을 차례로 선택합니다.

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

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

선택된 목록상자의 모든 조건이 채워지면, 각 조건을 모두 만족하는 특정 값(예제파일에서는 제품의 코드번호)을 추출해야 합니다.

여러 조건을 동시에 만족하는 VLOOKUP 검색은 INDEX 함수와 MATCH 함수를 조합하여 작성합니다. 관련 내용은 별도 영상강의로 정리되어 있으니 관련 링크를 참고하세요.

자주묻는 질문

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

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

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

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

1. [ 예제2_목록상자_2 ] 이름 정의 범위의 수식을 변경합니다.
   =OFFSET($I$2,MATCH($P2,$I$2:$I$100,0)-1,1,COUNTIF($I$2:$I$100,$P2),1)
2. [ 예제2_목록상자_3 ] 이름 정의 범위의 수식을 변경합니다.
   =OFFSET($L$2,MATCH(1,--(($L$2:$L$100=$P2)*($M$2:$M$100=$Q2)),0)-1,2,COUNTIFS($L$2:$L$100,$P2,$M$2:$M$100,$Q2),1)
3. 이름 정의 범위 생성을 마쳤다면, 적용 범위를 선택한 뒤 데이터 유효성 검사 목록상자를 적용합니다.

   

4. 여러 셀에 동시에 다중 데이터 유효성 검사 목록상자를 적용했습니다.

   

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

  

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