엑셀 이중 유효성 검사 목록상자 만들기 :: 엑셀 함수 공식

2중 조건을 만족하는 데이터유효성 목록상자를 생성하는 엑셀 함수 공식의 동작원리와 실전 예제를 알아봅니다.

홈페이지 » 엑셀 이중 유효성 검사 목록상자 만들기 :: 엑셀 공식

엑셀 이중 유효성 검사 목록상자 함수 공식

이중 유효성 검사 목차 바로가기
이중 유효성 검사 목록상자란?

엑셀 이중 유효성 검사 목록상자란, 이전에 선택된 조건을 만족하는 값만 불러오는 데이터 유효성 검사의 응용기능의니다. 이중 유효성 목록상자는 아래의 함수 공식으로 아주 간단하게 적용할 수 있습니다. 단, 이중유효성 목록상자 공식은 셀에 직접 입력하지 않고, '이름정의범위'를 사용합니다.

이중 유효성 목록상자 설명 예제
이중유효성 목록상자로 데이터 입력을 제한합니다
사용예제
엑셀 이중유효성 목록상자 완성
이중유효성 검사로 조건별 데이터 입력을 제한할 수 있습니다
함수공식

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

인수 설명
이중유효성검사 인수설명
이중 유효성검사 공식은 4개의 인수만 사용합니다
인수설명
시작셀참조범위의 첫번째 셀입니다.
참조값데이터유효성 목록의 조건이 입력된 셀입니다.
찾을범위참조값을 찾을 범위입니다.
n찾을범위를 기준('0')으로한 출력범위의 열 번호입니다.

예제파일 다운로드

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

엑셀 이중유효성 검사 목록상자 공식 자세히 알아보기

본 예제파일에서 사용된 함수 공식

이중 유효성 검사 함수 공식은 '이름정의범위'를 이용합니다. 따라서 엑셀 화면 상단의 [수식] - [이름관리자]로 이동한 뒤, [새로 만들기]를 클릭합니다. 새로운 이름정의범위를 생성하기 위한 창이 나타나면 아래 수식을 입력합니다.

수식 - 이름관리자 삽입
[수식] - [이름관리자]로 이동하여 '이름정의범위'를 생성합니다.
이름 : 제품선택
참조대상 : = OFFSET($B$3, MATCH($E$3,$B$3:$B$11,0)-1,1,COUNTIF($B$3:$B$11,$E$3), 1)
이중유효성 공식 예제
이중 유효성 목록상자를 위한 '이름정의범위'를 추가합니다.
공식 원리 이해하기
  1. COUNTIF 함수의 동작원리

    = COUNTIF ( 참조범위, 찾을값 )

    COUNTIF 함수는 '참조범위' 안에서 '찾을값'의 개수를 반환합니다. 예제에서는 '구분' 범위에서 '채소' 값의 개수를 세어 반환합니다.

    = COUNTIF($B$3:$B$11,$E$3)
    = 3  '// '구분' 범위에서 '채소'는 3개 입니다.

    COUNTIF 함수 구분범위에서 채소 개수
    '구분' 범위에서 '채소'의 개수를 반환합니다.
  2. MATCH 함수의 동작원리

    = MATCH ( 참을값, 참조범위, 0 )

    MATCH 함수는 참조범위에서 찾을값이 첫번째로 위치한 셀의 위치를 숫자로 반환합니다. 예제에서는 '구분'범위에서 '채소'가 첫번째로 위치한 셀의 순번인 '4'를 반환합니다.

    =MATCH($E$3,$B$3:$B$11,0)
    =4 '// '구분' 범위에서 '채소'의 첫번째 셀은 4번째에 있습니다.

    MATCH 함수 동작원리
    MATCH 함수는 찾을값의 위치를 반환합니다.
  3. 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칸을 넓힌 범위를 반환합니다.
  4. 이중 유효성 목록상자 적용하기
    이중 유효성 목록상자를 적용할 셀(예제파일 F3셀)을 선택합니다. 엑셀 화면 상단에서 [데이터] - [데이터 유효성검사] 로 이동합니다.
    데이터 유효성검사 이동
    [데이터] - [데이터유효성검사] 로 이동합니다.
    데이터 유효성검사 적용을 위한 창이 나타나면, [제한대상]에서 '목록'을 선택합니다. 이후 [원본]에 커서를 이동한 뒤, 키보드 F3키를 누르면 '이름정의범위'를 선택하기 위한 창이 나타납니다. 이중유효성목록으로 적용할 범위를 선택한 뒤, [확인]을 눌러 마무리합니다.

    이중 유효성검사 적용하기
    이름정의범위를 이중 유효성검사 목록상자로 지정합니다
  5. 이중 유효성 목록상자 사용하기
    이중 유효성 목록상자의 조건(예제파일 'E3'셀)을 변경하면 조건에 따라 목록상자의 값이 바뀌는 것을 확인할 수 있습니다.

    엑셀 이중유효성 목록상자 완성 GIF
    조건에 따라 목록상자의 값이 달라집니다

관련 엑셀 함수 공식 살펴보기

필터/정렬기능 없이 함수만으로 각 항목의 순번(또는 개수)를 카운트합니다.
지정한 범위에서 찾는값과 일치하는 그림을 출력합니다.
범위에서 값을 찾아 같은 행의 다른 값을 반환합니다.
INDEX / MATCH 함수의 다양한 실전 응용방법을 알아봅니다.

[링크] MS 홈페이지 엑셀 데이터유효성 드롭가운 목록 만들기 상세설명

5 6 votes
게시글평점
현재 페이지 댓글알림 신청
알림 설정
guest
7 Comments
Inline Feedbacks
View all comments
닥코드
닥코드
2020년 4월 8일 10:14 오전
게시글평점 :
     

좋은 내용 감사합니다.

엑셀고고
엑셀고고
2020년 4월 13일 10:32 오전
게시글평점 :
     

너무 유용합니다 감사합니다

parispgoon
2020년 5월 18일 4:14 오전
게시글평점 :
     

안녕하세요- 좋은 강의 감사드립니다. 유튜브에서 보다가 여기까지 왔습니다. 강의를 보다 질문을 하고 싶은데요. 쉐어포인트에 등록후 여러 직원들이 온라인으로 동시에 입력 가능한 물건발송대장 자동화 서식을 만들려고 합니다. 예를 들어 오늘 2020년5월17일에… 더보기 »

김민수
김민수
2020년 5월 25일 4:19 오후
게시글평점 :
     

좋은정보 감사합니다

GZM
2020년 5월 27일 4:08 오후
게시글평점 :
     

유용한 내용입니다.

홍예지
홍예지
2020년 6월 25일 1:05 오후
게시글평점 :
     

너무 유용한 포스트였습니다!
고급 정보 정말 감사드려요 🙂

7
0
여러분의 생각을 댓글로 남겨주세요.x