엑셀 데이터 목록 - egsel deiteo moglog

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

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

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

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

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

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

엑셀 수식 강좌 

[ 수식 다루기 ]

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

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

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

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

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