엑셀 이중 유효성 검사 목록상자란, 이전에 선택된 조건을 만족하는 값만 불러오는 데이터 유효성 검사의 응용기능의니다. 이중 유효성 목록상자는 아래의 함수 공식으로 아주 간단하게 적용할 수 있습니다. 단, 이중유효성 목록상자 공식은 셀에 직접 입력하지 않고, '이름정의범위'를 사용합니다.
이중유효성 목록상자로 데이터 입력을 제한합니다
사용예제
이중유효성 검사로 조건별 데이터 입력을 제한할 수 있습니다
함수공식
= 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'셀)을 변경하면 조건에 따라 목록상자의 값이 바뀌는 것을 확인할 수 있습니다.
안녕하세요- 좋은 강의 감사드립니다. 유튜브에서 보다가 여기까지 왔습니다. 강의를 보다 질문을 하고 싶은데요. 쉐어포인트에 등록후 여러 직원들이 온라인으로 동시에 입력 가능한 물건발송대장 자동화 서식을 만들려고 합니다. 예를 들어 오늘 2020년5월17일에 보낸 레퍼런스가 INVOICE/2020/A1 이라고 가정하면, 그 다음에 등록을 하려는 직원이 등록을 할때 따로 작성할 필요없이 그 다음칸에 INVOICE/2020/A2 이렇게 자동으로 입력되게끔 하게 할려면 어떤 기능을 써야 하는지, 그리고 이 경우 한번 작성이 된 레퍼런스는 수정이 불가능하게 할려면 어떻게 해야 하나요? 이런 자동화 서식 강의도 올려주심 좋을거 같습니다. 감사합니다!
오빠두엑셀작성자2020.05.18 14:43
안녕하세요?
자동으로 입력되게끔 하게 할려면 어떤 기능을 써야 하는지
=> 어느정도까지 자동화하느냐, 자료가 어떻게 관리되느냐에 따라 다릅니다. 쉐어포인트로는 100% 자동화가 불가능하며 최소한의 매뉴얼작업이 필요합니다. 만약 서식시트 / 레퍼런스번호시트가 따로 관리된다면, 서식시트에서 레퍼런스 번호가 입력되는 셀에 [ =값+1 ] 로 최근 입력된 레퍼런스번호+1 이 입력되도록 관리할 수 있습니다.
한번 작성이 된 레퍼런스는 수정이 불가능하게 할려면 어떻게 해야 하나요?
불가능합니다. 사용자에게 읽기권한만 주어 아예 수정이 불가능하게 할 수 있지만, 처음 작성시에만 수정가능.. 이런 기능은 없는 것으로 알고 있습니다.^^;
쉐어포인트를 마지막으로 쓴게 2019년 초라.. 정확하지 않을수도 있으니 권한설정을 한번 확인해보시기 바랍니다.
김민수2020.05.25 16:19
좋은정보 감사합니다
GZM2020.05.27 16:08
유용한 내용입니다.
홍예지2020.06.25 13:05
너무 유용한 포스트였습니다!
고급 정보 정말 감사드려요 :)
김소정2020.08.14 07:54
안녕하세요, 종속 드롭목록 만드는거 찾다가 선생님꺼 보면서 하고있는데요~
A, B, C로 A값선택시 B바뀌고 B선택시 C바꾸고하는데요, 한번 드롭을 A B C순차적으로 선택후 B를 다시선택하면 C셀에 이전에 선택값이 그대로있는데,혹시 삭제하는방법있을까요? 유효하지않은값인데, 체크못하는거 같아요,
오빠두엑셀작성자2020.08.14 15:22
안녕하세요? :)
말씀하신 기능은 내장함수만으로는 구현이 불가능하구요..
시트의 Change 이벤트를 VBA코드로 작성하셔야만 구현할 수 있는 기능입니다.
김소정2020.08.14 20:04
아하, 네네~ 찾아보고 해보겠습니다.
orora5882020.09.19 05:34
좋은 강의 감사해요.
켄타로2020.09.23 00:27
내일 회사가서 써먹어야겠다! 감사합니다!
하윤정2020.10.30 08:56
안녕하세요 데이터유효성 목록을 만드려고 합니다. 처음 데이터를 만드는 과정이라 구분 제품처럼 목록테이블을 먼저 만들지 않았는데 혹시 따로 추출하는 방법이 있을까요? ㅠㅠ
오빠두엑셀작성자2020.10.30 20:51
안녕하세요? 따로 추출하신다는 작업이 정확히 이해되질 않습니다..ㅜㅜ
좀 더 자세히 설명해주시겠어요?
또는 엑셀 커뮤니티에 좀 더 자세한 상황설명을 적어주시면 확인 후 답변 드리겠습니다.
감사합니다.
= OFFSET($B$3, MATCH($E$3,$B$3:$B$11,0)-1,1,COUNTIF($B$3:$B$11,$E$3), 1)
