이중 유효성 목록상자
지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀
첫 선택에 따라 항목이 바뀌는 이중 유효성 목록상자 공식입니다.
인수 설명
01모든 버전 OFFSET 방식
=OFFSET ( 시작셀, MATCH ( 참조값, 찾을범위, 0 )-1, 열이동, COUNTIF ( 찾을범위, 참조값 ), 1 )인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
시작셀필수구분 목록의 첫 번째 셀입니다. OFFSET 함수는 다시 계산될 때마다 실행되는 휘발성 함수입니다.
참조값필수첫 번째 목록상자에서 선택한 구분이 입력된 셀입니다.
찾을범위필수참조값의 시작 위치와 개수를 찾을 구분 범위입니다. 같은 구분은 반드시 연속해서 배치해야 합니다.
열이동필수구분 열에서 실제 목록 열까지 오른쪽으로 이동할 열 수입니다.
A
B
C
D
E
F
G
1
구분
제품
선택 구분
기존 목록
FILTER 목록
2
과일
사과
채소
당근
당근
3
과일
배
오이
오이
4
과일
포도
양파
양파
5
채소
당근
6
채소
오이
7
채소
양파
8
음료
주스
9
동작 원리
MATCH 함수는 선택 항목의 시작 위치를, COUNTIF 함수는 항목 수를 계산하고 OFFSET 함수는 해당 범위를 반환합니다.
모든 버전 OFFSET 방식의 예제 시트에서 E2 셀이 표시하는 B5:B7 범위의 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
1
MATCH 함수가 시작 위치를 찾습니다
MATCH 함수는 A2:A8에서 채소가 처음 나오는 위치를 찾아 4를 반환합니다. OFFSET 함수의 행 이동값으로 사용할 때는 시작셀 자체를 0으로 계산하므로 1을 뺍니다.
=MATCH ( D2, A2:A8, 0 )
= 4
A2:A8에서 채소가 처음 나오는 순번입니다. 2
COUNTIF 함수가 항목 수를 셉니다
COUNTIF 함수는 A2:A8에서 채소가 입력된 셀을 세어 반환 범위의 높이를 3으로 정합니다.
=COUNTIF ( A2:A8, D2 )
= 3
A5:A7에 있는 채소의 개수입니다. 3
OFFSET 함수가 목록 범위를 반환합니다
OFFSET 함수는 A2에서 아래로 3칸, 오른쪽으로 1칸 이동한 B5를 시작점으로 삼고 높이 3, 너비 1인 B5:B7 범위를 반환합니다.
=OFFSET ( A2, 4-1, 1, 3, 1 )
= {"당근";"오이";"양파"}
B5:B7 범위에 있는 세 제품입니다. 022021 이후 FILTER 방식
=FILTER ( 출력범위, 조건범위=선택값 )인수 설명 자세히 보기
인수구분설명
출력범위필수선택한 구분과 일치할 때 목록으로 반환할 값 범위입니다.
조건범위필수선택값과 비교할 구분 범위입니다. 출력범위와 높이가 같아야 합니다.
선택값필수첫 번째 목록상자에서 선택한 구분이 입력된 셀입니다.
A
B
C
D
E
F
G
1
구분
제품
선택 구분
기존 목록
FILTER 목록
2
과일
사과
채소
당근
당근
3
과일
배
오이
오이
4
과일
포도
양파
양파
5
채소
당근
6
채소
오이
7
채소
양파
8
음료
주스
9
이 공식이 사용하는 함수
자동으로 입력되게끔 하게 할려면 어떤 기능을 써야 하는지
=> 어느정도까지 자동화하느냐, 자료가 어떻게 관리되느냐에 따라 다릅니다. 쉐어포인트로는 100% 자동화가 불가능하며 최소한의 매뉴얼작업이 필요합니다. 만약 서식시트 / 레퍼런스번호시트가 따로 관리된다면, 서식시트에서 레퍼런스 번호가 입력되는 셀에 [ =값+1 ] 로 최근 입력된 레퍼런스번호+1 이 입력되도록 관리할 수 있습니다.
한번 작성이 된 레퍼런스는 수정이 불가능하게 할려면 어떻게 해야 하나요?
불가능합니다. 사용자에게 읽기권한만 주어 아예 수정이 불가능하게 할 수 있지만, 처음 작성시에만 수정가능.. 이런 기능은 없는 것으로 알고 있습니다.^^;
쉐어포인트를 마지막으로 쓴게 2019년 초라.. 정확하지 않을수도 있으니 권한설정을 한번 확인해보시기 바랍니다.
고급 정보 정말 감사드려요 :)
A, B, C로 A값선택시 B바뀌고 B선택시 C바꾸고하는데요, 한번 드롭을 A B C순차적으로 선택후 B를 다시선택하면 C셀에 이전에 선택값이 그대로있는데,혹시 삭제하는방법있을까요? 유효하지않은값인데, 체크못하는거 같아요,
말씀하신 기능은 내장함수만으로는 구현이 불가능하구요..
시트의 Change 이벤트를 VBA코드로 작성하셔야만 구현할 수 있는 기능입니다.
좀 더 자세히 설명해주시겠어요?
또는 엑셀 커뮤니티에 좀 더 자세한 상황설명을 적어주시면 확인 후 답변 드리겠습니다.
감사합니다.