강의소개

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

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

  1. 2단계 이상 넘어가는 데이터 유효성 검사 목록상자를 만드는데 제한
  2. 여러 개의 항목 관리시, 각 항목을 모두 표로 제작하는데 한계

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

공식 요약

1] 이중유효성검사 목록상자 공식

= OFFSET (시작셀, MATCH(참조값, 찾을범위,0)-1, 1, COUNTIF(찾을범위,참조셀), 1)

  • 시작셀 : 이중유효성검사 원본 데이터의 좌측상단 첫번째 셀입니다.
  • 참조값 : 이중유효성검사의 조건이 입력된 셀입니다.
  • 찾을범위 : 참조값을 찾을 범위입니다.

링크] 이중유효성 목록상자 공식 단계별 상세설명 보러가기
2] 다중유효성검사 목록상자 공식

= OFFSET (시작셀, MATCH(1,-((범위1=참조값1)*(범위2=참조값2)…),0)-1, n, COUNTIFS(범위1,참조값1,범위2,참조값2,…), 1)

  • 시작셀 : 다중유효성검사 원본데이터의 좌측상단 첫번째 셀입니다. 원본데이터에서 목록상자로 출력될 값은 반드시 고유값으로 정렬되어야 합니다.
  • 참조값 : 다중유효성검사의 조건이 입력된 셀입니다.
  • 범위 : 다중유효성검사의 조건을 찾을 범위입니다.
  • n : 다중유효성검사로 넘어가는 단계수입니다.

예제파일 및 E-Book 교재 다운로드

파일구분마지막수정일파일형식파일다운로드
7완성파일2019-12-04XLSX함수마스터 파일다운로드
7예제파일2019-12-04XLSX함수마스터 파일다운로드
7E-Book2019-12-04PDF함수마스터 파일다운로드
1예제파일2019-11-26XLSX함수마스터 파일다운로드
6E-Book2019-10-30PDF함수마스터 파일다운로드
6예제파일2019-10-30XLSX함수마스터 파일다운로드
5완성파일2019-10-30ZIP함수마스터 파일다운로드
4E-Book2019-10-28PDF함수마스터 파일다운로드
4완성파일2019-10-28XLSX함수마스터 파일다운로드
4예제파일2019-10-28XLSX함수마스터 파일다운로드
3E-Book2019-10-28PDF함수마스터 파일다운로드
3PPT자료2019-10-28PPT함수마스터 파일다운로드
3완성파일2019-10-28XLSX함수마스터 파일다운로드
3예제파일2019-10-28XLSX함수마스터 파일다운로드
영상강의

관련포스트
관련 기초함수
선택된 범위에서 하나의 조건을 만족하는 셀의 개수를 계산합니다.
시작지점으로부터 지정한만큼 이동한 곳의 셀 참조 또는 값을 반환합니다.

1. 이중 데이터 유효성 목록상자 – 표기능 응용

이전 강의에서 표기능을 응용한 이중 데이터유효성 검사 목록상자를 만드는 법을 알아봤습니다.

표기능을 사용하면 다중 데이터유효성 목록상자를 간소화하여 만들 수 있는 반면, 몇 가지 제한사항이 있는데요.

  • 2단계 이상 넘어가는 데이터 유효성 검사 목록상자를 만드는데 제한이 있습니다.
    (예: 조건1 ▶ 조건2 ▶ 조건3.. 으로 넘어가는 다중조건 유효성 검사)
  • 여러 개의 항목을 관리해야 할 경우, 각 항목을 모두 표로 관리하는데 한계가 있습니다. 따라서 많은 양의 데이터를 관리하려면 다른 효율적인 방법이 필요합니다.

따라서 이번 강의에서는 동적범위를 응용한 다중 데이터유효성 검사 목록상자 제작방법을 알아보겠습니다.

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

A. 목록상자 출력을 위한 고유값 추출하기

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

  • 피벗테이블로 고유값을 추출하는 방법
    장점 : 만들기 편리하며 동적범위를 이용할 경우 새로운 데이터를 자동으로 반영할 수 있습니다.
    단점 : 데이터 추가시, 새로고침 기능(CTRL + ALT + F5)으로 피벗테이블을 업데이트해야 합니다.

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

      표를 선택한 뒤, 피벗테이블을 생성합니다.

    2. 우측의 '피벗필드' 창에서 '구분' 항목을 행으로 이동합니다.
      1b 구분 행 추가

      '구분' 항목을 행 필드로 이동합니다.

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

      '행 및 열 총합계 해제'를 클릭하여 '총합계' 표시를 해제합니다.

    4. 피벗테이블로 고유값 추출이 완료되었습니다.
      1d 고유값 추출 완료

      선택한 범위의 고유값 추출이 완료되었습니다.

  • 중복값 제거 공식을 이용하는 방법 (중복값 제거 공식 상세설명 보러가기)
    장점 : 새로운 데이터를 실시간으로 반영합니다.
    단점 : 초보자가 이해하기엔 다소 어려운 공식이 사용되며, 큰 데이터에 적용시 동작속도가 느려질 수 있습니다.

    1. 고유값 추출을 시작할 셀 (예제파일의 H9셀)을 선택합니다.
      다중 데이터 유효성 고유값 범위 공식

      고유값 추출을 시작할 셀을 선택합니다.

    2. 아래 공식을 복사하여 붙여넣기 한 뒤, 고유값을 나열할 범위에 자동채우기 합니다.
      =IFERROR(LOOKUP(2,1/(COUNTIF($H$8:H8,표9[구분])=0),표9[구분]),"")
      2B 중복값 제거 공식 이용 완료

      공식을 붙여넣기 한 뒤, 자동채우기를 하면 고유값 추출이 완료됩니다.

  • 중복된 항목 제거 기능을 이용하는 방법
    장점 : 아주 편리하며, 많은 양의 데이터를 다루더라도 파일이 가볍게 사용할 수 있습니다.
    단점 : 새로운 데이터 추가시, 매번 반복된 작업으로 수정해야하는 번거로움이 있습니다.

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

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

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

      중복된 항목 제거 기능을 선택합니다.

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

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

B. 데이터 유효성검사 목록상자 만들기 (1차 조건)

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

예를 들어,
'커피'를 선택하면 '에스프레소', '아메리카노', '카푸치노' … 등이 나열되며,
'차'를 선택할 경우 '얼그레이', '카모마일', '보이차', … 등이 나열됩니다.
  1. 이전 강의에서 알아본 OFFSET 함수 동적범위를 응용하여 사용자지정범위를 생성합니다.
    '이름관리자' (단축키: CTRL + F3) 로 이동한 뒤 아래와 같이 사용자지정범위를 만들어주세요.

    이름 : 예제1_목록상자_1
    참조대상 : =OFFSET($D$9,,,COUNTA($D$9:$D$100))
    엑셀 다중 데이터 유효성 목록상자 만들기

    OFFSET 함수 동적범위로 사용자지정범위를 생성합니다.

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

    4B 데이터유효성 목록상자 추가

    데이터유효성 목록상자를 생성합니다.

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

    1차 데이터 유효성검사 목록상자가 생성되었습니다.

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

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

  1. 예제시트의 아무 셀이나 선택한 후, 아래 공식을 복사하여 붙여넣기 합니다. (#VALUE! 오류가 나와도 괜찮습니다.)
    =OFFSET($A$4,MATCH($O$4,표9[구분],0)-1,1,COUNTIF(표9[구분],$O$4),1)
    5A 공식 붙여넣기

    시트의 아무 곳이나 선택한 뒤, 위 공식을 복사/붙여넣기 합니다.

  2. 붙여넣기 한 셀을 선택한 후, 상단의 '수식입력줄'을 클릭합니다. 이후 키보드 F9키를 누르면 1차 조건에 대한 제품목록이 나열됩니다.
    5B 수식입력줄 수식 검사

    수식입력줄에서 키보드 F9 키를 누르면 해당 공식의 결과값이 배열로 출력됩니다.

  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차 조건에 대한 다중 데이터 유효성검사 목록상자가 완성되었습니다.
    5E 다중조건 목록상자 완성

    다중 데이터 유효성검사 목록상자가 완성되었습니다.

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

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

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

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

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

6A 고유값 추출 완료

더하기버튼을 클릭하면 미리 만들어 둔 피벗테이블이 나옵니다.

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

  • 1단계 : 최상위 항목인 '브랜드'의 고유값을 추출합니다.
  • 2단계 : '브랜드'별 '차종'의 고유값을 추출합니다.
  • 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. 이중, 삼중 데이터유효성 검사 목록상자 만들기

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

  • P2셀(브랜드) : 예제2_목록상자_1
  • Q2셀(차종) : 예제2_목록상자_2
  • R2셀(제품명) : 예제2_목록상자_3

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

다중 데이터 유효성 오류 발생가능

상위항목의 데이터가 비어있을시, 위와 같은 오류가 발생할 수 있으나 무시하고 진행합니다.

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

다중 데이터 유효성 검사 목록상자 사용예제

3중으로 조건이 적용되는 데이터 유효성 검사 목록상자가 완성되었습니다.

4. 동적범위 다중 데이터유효성 목록상자 사용시 주의사항

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

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

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

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

8A 떨어진 고유값 입력

고유값범위 항목이 떨어져 입력될 경우 목록상자는 옳지않은 값을 반환합니다.

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

8B 옳지않은 목록상자 값 출력

'커피' 구분이 아닌 '얼그레이'가 목록상자에 나타납니다.

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

다중 데이터 유효성 목록상자 문제해결

피벗테이블을 활용하면 고유값이 '자동정렬' 됩니다.

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

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

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

8E 모두 새로고침

피벗테이블을 사용할 경우, 새로운 데이터가 추가되면 '새로고침'으로 피벗테이블을 갱신합니다.

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

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

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

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

별점주기 (5 / 1)

14
댓글 남기기

avatar
5 Comment Thereads
9 Thread replies
5 팔로워
 
가장 좋아요가 많은 댓글
가장 인기많은 댓글
9 작성자
무우쭝BOLL 최영민오빠두엑셀 진석쓰s 최근 작성자
  현재 페이지 댓글알림 신청  
알림 설정
띵나
손님
띵나

이 함수 많이 사용하면 엑셀이 느려지지는 않나요?

오빠두엑셀
손님

INDEX 함수는 지속적으로 계산되는 함수이므로 속도에 영향을 줄수 있습니다.
다만 10만행/50열이하 작은 데이터에서는 영향이 없다고 보셔도 괜찮습니다.^-^
그 이상의 큰 데이터라면 직접 사용해보신 뒤 고려해보세요 ^_^

고모두`
손님
고모두`

안녕하세요 선생님 ~
혹시 피벗테이블에 필터를 각각 거는 방법이 궁굼합니다.

그리고 + – 숨기기 해놓은 방법을 알수 있을까요?

오빠두엑셀
손님

고모두님 안녕하세요? 오빠두엑셀이에요 ^_^*
음.. 필터를 각각 거는 방법이 어떤걸 의미하는지 정확히 모르겠으나, 아래 방법이 맞는지 확인해보시겠어요?
피벗테이블 우클릭 – [피벗테이블 옵션] 에서 아래와 같이 설정해보세요.
comment image

+ – 숨기기 해놓는 방법도 동일하게, 피벗테이블 옵션에서 설정하실 수 있습니다.^_^*comment image

최영민
손님
최영민

오빠두 선생님 좋은 강의 잘 보고 있습니다. 많은 도움되고있습니다.
제가 직장인이라 정식 공부는 못하고 오빠두님께서 올려주신 파일 보면서 따라하는 정도인데
한가지 질문드립니다.
위의 수식에 보면 — 이부호가 많이 들어가는데 이게 그냥 빼기 의미인지 2번연속으로 들어가는 이유가 뭐지 궁금합니다.
그리고 3중 또는 4중5중으로 연결할 경우 수식이 어떻게 되는지도 궁금합니다.

최영민
손님
최영민

comment image

선생님 한가지 질문 추가 드립니다.

이번에 강의해주신 내용대로 다 해보니 한가지 궁금한점이

이번 수식이 셀한개만 고정이 되어있다보니 첨부그림에 표시해놓은 것처럼 아래로 복사해서 여러개로

사용하려니 되지가 않네요….이거 해결할 방법이 있을까요???

진석쓰s
손님
창창한하루
별점주기 :
     

너무 도움 되는 내용이에요!!