엑셀 Indirect 함수로 다중 대중소분류 드롭다운 만들기

엑셀 Indirect 함수로 다중 대중소 분류 드롭다운 만들기

인터넷에 보면 여러 분류로 된 선택 박스가 있고 첫 번째 대분류 항목을 선택하면  첫 번째 항목에 소속된 리스트들이 연관돼서 뜨는 사이트들이 있습니다. 업무에 따라서 대분류 , 중분류, 소분류 등을 만들어 작업자들이 가이드에 맞게 선택되도록 해야 할 경우 엑셀에서 VB 프로그램을 사용하지 않아도 구현한 수 있습니다.
바로 InDirect 함수를 사용하면 되는데요.

InDirect 함수 정의와 사용방법을 통해 대, 중, 소 리스트 박스 선택하는 것을 만들어보겠습니다.

InDirect 함수는 엑셀에서 다음과 같이 정의하고 있습니다.
"텍스트 문자열로 지정한 셀주소로 돌려줍니다."라고 정의합니다.

이해가 쉽게 되시나요? 간단한 예제를 통해서 알아보겠습니다.
셀에 알파벳, 숫자, 한글 등의 문자가 입력되어 있습니다.
InDirect 함수를 사용해서

=Inderect(b3) 를 입력합니다.  결괏값이 "#REF!" 이라는 오류가 발생되는데요. 셀 주소를 "로 감싸서 테스트로 인식해야 합니다.

=Inderect("b3") 를 입력하면 B3에 입력된 글자가 반환됩니다.

이제 InDirect 함수를 사용해서 대, 중, 소 분류가 나오도록 구현해보겠습니다.
대, 중, 소뷴류를 입력했습니다. 데이터가 소속 없이 입력되어 있습니다.

우선 컴퓨터 대분류에 중분류는 데스크톱, 모니터인데요.
컴퓨터 대분류에 속한 중분류값을 드래그해서 영역 설정 후 마우스 오른쪽 버튼 클릭 후 [이름 정의]를 클릭합니다.

이름 정의 창이 열리면 이름에 대분류 값인 "컴퓨터"를 입력합니다.

다른 중분류도 동일하게 작업합니다. 음식에 하위분류인 중식, 한식을 드래그 후 마우스 오른쪽 버튼 클릭 후
[이름 정의]를 클릭합니다.
새 이름 창이 열리면 이름에 대분류값인 "음식"을 입력합니다.

소분류도 중분류 이름 정의한 것처럼 동일하게 진행합니다.
소분류인 CPU 를 선택 후 마우스 우 클릭하여 [이름 정의]를 클릭해서 중분류 값을 입력합니다.

다른 소분류 값도 모두 이름 정의를 해주세요.

이름 정의를 잘못했을 경우나 정의된 이름 목을 보고 수정, 또는 삭제하고자 하는 경우
상위 메뉴 즁 [수식]-[정의된 이름]을 클릭합니다.

이름 관리장 창이 열리면 정의된 이름 목록이 조회됩니다.
필요에 따라 수정, 삭제 하시면 됩니다.

이제 셀을 클릭하면 목록이 나오고 소속된 하위분류만 나오도록 하겠습니다.

대분류 목록이 나와야 하는 셀을 선택 후 상위 메뉴 중 [데이터]-[데이터 유효성 검사]를 클릭합니다.

데이터 유효성 창이 열리면 제한 대상을 [목록]으로 변경 후 원본 입력 박스를 선택 후 대분류가 입력된 셀을 드래그합니다. 원본에 값이 입력되면 [확인]버튼을 클릭합니다.

창이 닫히고 수식이 유효성 검사가 적용된 셀을 클릭하면 우측에 화살표가 나옵니다.
화살표를 클릭하면 대분류가 나오게 됩니다

수식이 입력 된 셀 우측 하단 셀을 클릭 상태로 아래로 드래그합니다. 수식이 복사되어 다른 대분류 셀에도 우측에 값을 선택할 수 있는 화살표가 뜹니다.

이제 중분류를 입력해보겠습니다. 중분류가 나올 셀을 선택 후 상단 메뉴 중 [데이터]-[데이터 유효성]을 클릭합니다.
창이 열리면 제한 대상을 "목록"으로 선택합니다.
이제 원본에 =InDirect( 까지 입력 후 대분류가 입력되어 있는 셀을 선택하거나 $가 들어간 절대 주소로 직접 입력합니다.
절대 주소 입력은 셀 주소가 "a1" 이면 "$a$1"로 하시면 됩니다.