강좌 > 엑셀중급강좌 > 엑셀 데이터 다루기 > 데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기

데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기

이번 글에는 엑셀의 '데이터 유효성 검사' 기능을 이용하여 상위목록에 종속되는 하위목록을 만드는 방법을 알려드리겠습니다.

데이터 유효성 검사에 대한 전반적인 내용은 다음 링크를 참고하세요.
[ 엑셀에서 허용된 데이터만 입력하기 ]

실습용 엑셀파일 다운로드 : 데이터유효성검사.xlsx

 

예를 들어 다음 그림과 같이 '거래처별 월별 매출 현황'을 조회하기 위해  대리점유형,거래처 조건을 선택해야 할 때 상위목록에 종속되는 하위목록이 필요합니다.

상위목록에 종속되는 하위목록

아래와 같이 대리점유형은 '직영대리점과', '가맹대리'점이 있는데 만약 '직영대리점'을 선택하면 거래처에는 직영대리점에 속하는(종속되는) 거래처만 선택할 수 있어야 하고 '가맹대리점'을 선택하면 가맹대리점에 속하는 거래처면 선택할 수 있어야 합니다.

데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기

위 그림과 같은 목록을 만들려면 '데이터 유효성 검사'기능을 약간 응용해야 합니다.

 

먼저 다음과 같이 대리점유형(상위목록)과 대리점유형에 종속되는 거래처(하위목록)자료를 별도의 시트에 만듭니다.
(실습 파일의 [기초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 함수 사용법은 다음 글을 참고하세요.

INDIRECT 함수 – 문자열을 참조로 바꾸기 - 엑셀의 INDIRECT 함수는 문자열로 만들어진 참조를 유효한 셀 참조로 바꿔줍니다. 수식은 바꾸지 않고 문자열로 만들어진 참조만 변경해서 결과값을 가져와야 할…

INDIRECT 함수 – 문자열을 참조로 바꾸기 더 보기 »

 


엑셀 수식 강좌 

[ 수식 다루기 ]

[ 엑셀에서 입력과 표시방법 ]

[ 수식에서 셀과 범위 참조 방식 ]

[ 엑셀을 제대로 쓰는 데이터처리 ]

[ 엑셀 이름정의와 표기능 ]

[ 수식을 분석하고 오류 해결하기 ]

 

 

 

엑셀웍스 책 출간 안내

그동안 엑셀웍스에 올린 '엑셀 강좌', '함수 사용법', 여러 '실무 예제'에서 핵심만 뽑아 '된다! 엑셀 수식 & 함수' 책으로 출간하였습니다.

엑셀웍스 책 출간안내  자세히 보기

온라인 구매처: 예스24   교보문고   알라딘 

“데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기”의 3개의 댓글

  1. 아어어

    종속되는 하위 항목을 한열 옆에 모든열에 적용하고싶은데 단순히 드래그를하면 첫번째 행에서 선택된 항목에 대해서만 종속된 항목이 표시됩니다 각 행마다 옆의 항목을 일일히 지정해주는 방법 말고 빠른채우기로 쓸수있나요?

댓글 남기기

Scroll to Top