이번 글에는 엑셀의 '데이터 유효성 검사' 기능을 이용하여 상위목록에 종속되는 하위목록을 만드는 방법을 알려드리겠습니다.
데이터 유효성 검사에 대한 전반적인 내용은 다음 링크를 참고하세요.
[ 엑셀에서 허용된 데이터만 입력하기 ]
예를 들어 다음 그림과 같이 '거래처별 월별 매출 현황'을 조회하기 위해 대리점유형,거래처 조건을 선택해야 할 때 상위목록에 종속되는 하위목록이 필요합니다.
아래와 같이 대리점유형은 '직영대리점과', '가맹대리'점이 있는데 만약 '직영대리점'을 선택하면 거래처에는 직영대리점에 속하는(종속되는) 거래처만 선택할 수 있어야 하고 '가맹대리점'을 선택하면 가맹대리점에 속하는 거래처면 선택할 수 있어야 합니다.
위 그림과 같은 목록을 만들려면 '데이터 유효성 검사'기능을 약간 응용해야 합니다.
먼저 다음과 같이 대리점유형(상위목록)과 대리점유형에 종속되는 거래처(하위목록)자료를 별도의 시트에 만듭니다.
(실습 파일의 [기초data] 시트에 이미 만들어져 있습니다)
[집계] 시트에서 다음 순서로 데이터 유효성 검사를 설정합니다.
① '대리점유형' 목록을 만들기 위해 [G3] 셀을 선택하고
② 리본 메뉴에서 [데이터] 탭 → [데이터 도구] 그룹 → [데이터 유효성 검사]를 누르면 [데이터 유효성] 대화상자가 나타납니다.
③ [제한 대상]에서 '목록'을 선택
④ [원본] 오른 쪽의 버튼을 클릭하고
[기초data] 시트로 이동하여 대리점 유형이 입력되어 있는
⑤ [B3:B4] 셀 범위를 선택한 후
⑥ 셀 범위가 입력된 상자의 오른쪽 버튼을 누르면
[설정] 탭의 [원본]에 범위가 입력됩니다.
⑦ <확인> 버튼을 누르면 '데이터 유효성 검사'가 적용됩니다.
여기까지 진행하면 아래와 그림과 같이 '대리점유형' 목록이 만들어 집니다.
지금부터가 중요합니다!
대리점유형 목록이 만들어 졌으니 '거래처' 목록을 만들어야 하는데 그 전에 다음 작업을 진행합니다.
① [기초data] 시트의 직영대리점 셀 범위 [D3:D6]를 선택하고
② 이름 상자에서 '직영대리점'을 입력하고 [Enter]를 누르면 이름이 정의됩니다.
가맹대리점도 직영대리점과 동일한 방법으로 범위를 선택하고 이름 상자에 '가맹대리점'을 입력해서 이름을 정의합니다.
이제부터 '대리점유형'에 종속되는 '거래처' 목록을 만들어 보겠습니다.
① '거래처' 목록을 만들기 위해 [H3] 셀을 선택하고
② 리본 메뉴에서 [데이터] 탭 → [데이터 도구] 그룹 → [데이터 유효성 검사]를 누르면 [데이터 유효성] 대화상자가 나타납니다.
③ [제한 대상]에서 '목록'을 선택하고
④ [원본] 에 =INDIRECT($G$3)를 입력하고
<확인> 버튼을 누르면
'대리점유형'에 종속되는 '거래처' 목록이 완성됩니다.
따라하긴 했는데 원리 이해가 쉽지 않았을 겁니다. 다음 그림을 보고 차근차근 따라 가면 이해할 수 있을 겁니다.
① [원본]에 입력된 =INDIRECT($G$3)수식이 핵심입니다.
② 만약에 '대리점유형' 목록에서 '직영대리점'을 선택했다면 [G3]셀에는 '직영대리점'이라는 값이 들어갈 것이고 =INDIRECT($G$3)는 결국 =INDIRECT("직영대리점")과 같습니다.
③ INDIRECT함수는 텍스트(문자열)로 입력된 인수를 참조로 만들어 주므로 =INDIRECT("직영대리점")는 앞서 설정한 '직영대리점'이라는 이름 정의와 같습니다.
④ '직영대리점'이라는 이름 정의는 결국 [기초data] 시트의 [D3:D6] 범위를 참조하므로
⑤ [집계] 시트에서 '거래처' 목록을 선택하면 아래와 같이 직영대리점에 해당하는 거래처만 표시됩니다.
※ INDIRECT 함수 사용법은 다음 글을 참고하세요.
엑셀 수식 강좌
[ 수식 다루기 ]
[ 엑셀에서 입력과 표시방법 ]
[ 수식에서 셀과 범위 참조 방식 ]
[ 엑셀을 제대로 쓰는 데이터처리 ]
- 엑셀에서 잘못된 날짜, 숫자 찾고 변환하기
- 수식으로 한 번에 텍스트 나누기
- 엑셀 고급필터로 다양하게 자료 필터링하기
- 정렬의 또 다른 방법, 사용자 지정목록/색으로 정렬
- 엑셀 오류데이터 처리하기
- 파워 쿼리(Power Query) 소개
- 파워쿼리로 외부 데이터 가져오기
- 엑셀에서 중복 데이터 제거하기
- 엑셀 텍스트 나누기의 모든 것
- 이동옵션으로 원하는 셀 한번에 선택하고 처리하기
- 데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기
- 사용자 지정 수식으로 데이터 유효성 검사하기
- 데이터 유효성 검사로 허용된 데이터만 입력
- 엑셀 데이터 다루기 첫 단추는 데이터 구조화
[ 엑셀 이름정의와 표기능 ]
[ 수식을 분석하고 오류 해결하기 ]
- 엑셀 순환참조 문제 해결하기
- 수식 계산 단계 알아보기
- 엑셀 수식 오류 검사하고 추적하기
- 엑셀에서 참조 셀 추적하기
- 엑셀 오류 유형(#DIV/0!, #N/A, #NAME?, #REF! 등)과 해결방법
종속되는 하위 항목을 한열 옆에 모든열에 적용하고싶은데 단순히 드래그를하면 첫번째 행에서 선택된 항목에 대해서만 종속된 항목이 표시됩니다 각 행마다 옆의 항목을 일일히 지정해주는 방법 말고 빠른채우기로 쓸수있나요?
안녕하세요. 이름을 참조하므로 일일이 지정해 주어야 합니다.
진짜 설명 잘하시네요, 큰 도움 되었습니다.