엑셀 indirect 함수를 이용한 다중 종속(소분류) 드롭다운 목록

엑셀 indirect 다중 종속(소분류) 드롭다운 목록

 

엑셀 드롭다운 목록 만들기에서 대분류 목록에 종속하는 1차 소분류, 2차 소분류 등 다중 종속 드롭다운 목록을 만드는 방법에 대해 알아보겠습니다.
종속 드롭다운 목록을 만들기 위해서는 종속 목록에 이름을 지정하고 INDIRECT 함수에 이름을 인수로 사용합니다.

아래와 같은 도시명과 종속적인 구명, 동명을 입력하는 다중 드롭다운 목록을 예시로 들겠습니다. 각 목록은 목록 항목 추가, 삭제 시 자동 적용되도록 엑셀 표로 만들었으며 이름으로 적용하기위해 이름을 지정했습니다. 각 목록은 Sheet2에 작성했습니다.

엑셀 다중 종속 드롭다운 목록

1. INDIRECT 함수 개요
2. 상위(대분류) 목록, 하위 종속(소분류) 목록 만들기
3. 종속 드롭다운 목록 만들기

INDIRECT 함수 개요

참조 : 엑셀 도움말

텍스트 문자열로 지정된 참조를 반환합니다.

함수 구문

INDIRECT(ref_text, [a1])

▶ ref_text

• 텍스트로 표시된 셀 참조
  INDIRECT("A1") : A1셀에 들어있는 텍스트

• 셀 참조
  INDIRECT(A1) : A1셀에 들어있는 텍스트로 표시된 셀의 내용

• 텍스트로 표시된 이름이 정의된 셀의 이름
  INDIRECT("도시명") : 도시명으로 이름이 지정된 셀의 내용

• 참조셀에 텍스트로 표시된 정의된 이름의 셀의 내용
  INDIRECT(A1) : A1 셀에 들어있는 텍스트로 표시된 이름이 지정된 셀의 내용

• 이름이 범위를 지정한 경우 #VALUE! error가 나옵니다. CSE(control+shift+enter)를 누르면 범위의 첫 데이터가 표시됩니다.

• ref_text가 유효한 셀 참조가 아닌 경우에는 #REF! 오류 값이 반환됩니다.

▶ [a1] : 선택 요소입니다.

• TRUE 또는 생략하면 ref_text는 A1 스타일의 참조로 해석됩니다
• FALSE이면 ref_text는 R1C1 스타일의 참조로 해석됩니다.

▶ INDRECT 함수에 범위에 지정된 이름을 인수로 사용해 종속 드롭다운 목록을 만듭니다.

 

상위(대분류) 목록, 하위 종속(소분류) 목록 만들기

 

예시로 든 입력 영역에는 도시명, 행정구명, 행정동명 3개의 입력 필드가 있습니다. 아래표로 보면

 

드롭다운 목록 갯수

• 최상위 목록(1개)
• 1차 종속 목록(최상위 목록 항목명의 2개)
• 2차 종속 목록(1차 종속명 목록 항목명의 6개) 총 9개의 목록이 필요합니다. 각 종속 목록에 있는 항목 모두 동일 이름으로 된 목록표를 만들어야 하는군요. 

각 목록을 테이블로 만드는 손쉬운 방법은 목록 임의의 셀에 커서를 위치하고 Control+T를 누르면 목록이 표로 만들어집니다. 목록에 도시명 등 머리글이 있으면 머리글 포함을 체크합니다.

 

1. 범위에 이름 지정하기

▶ 이름은 '/', '_'(밑줄), '.'(마침표), 문자, 숫자를 제외한 특수문자는 포함할 수 없습니다.

• 아래 그림과 같이 이름을 지정할 범위를 선택하고 문서 상단 수식 입력줄 좌측 이름 상자에 범위에 적용할 이름을 입력합니다.

  

  범위에 이름 지정

2. 최상위(대분류) 목록

예시에 '도시명' 입력 목록에는 서울, 부산을 넣었습니다.

• Contrlol+T를 눌러 목록을 표로 만듭니다.(머리글 포함)

  

  목록을 표로 만들기 

• 머리글을 제외한 목록 범위를 선택합니다.

• 이름 상자에 범위에 적용할 이름을 입력합니다.

  

  목록 표에 이름 지정

2. 1차 종속(소분류) 목록

• 최상위 목록에 [서울, 부산] 2개의 목록 항목이 있습니다.
• 2개의 목록 항목 [서울, 부산]에 종속되는 2개의 행정구명 목록을 표로 만듭니다.
• 목록 항목 중 '중구'는 중복 되므로 부산에 해당되는 항목은 '부산중구'로 입력했습니다.(범위 이름은 고유해야 합니다.)
• 각 해당 목록 범위는 상위 목록 [서울, 부산]과 같은 이름을 이름상자를 이용해 지정합니다.

  

  1차 종속 목록 표

3. 2차 종속(소분류) 목록

• 1차 종속 목록에는 2개의 목록표에 총 6개의 '행정구명' 항목이 있습니다.

• 이에 대응되는 6개의 해당 '행정동명'이 들어있는 목록을 표로 만듭니다.

• 이름상자를 이용해 종로구, 중구 등 1차 종속 목록 항목과 같은 이름을 6개의 목록 표에 지정합니다.

  

  2차 종속 목록 표

종속 드롭다운 목록 만들기

 

Sheet1의 데이터 입력 영역에 Sheet2의 목록을 이용해 드롭다운 목록을 만듭니다

1. 최상위(대분류) 드롭다운 목록

데이터 탭의 리본 메뉴 데이터도구 중 데이터 유효성 검사로 최상위(대분류) 드롭다운 목록을 만듭니다.

엑셀 데이터 유효성 검사 메뉴

• Sheet1의 도시명(B3:B5) 입력 범위를 드래그하여 선택합니다.

• 데이터 유효성 검사에서 목록 원본 Sheet2의 목록 이름 "도시명"을 수식으로 (=도시명 ) 입력합니다.

  

  데이터 유효성 검사 창

2. 1차 종속(소분류) 드롭다운 목록

최상위 도시명에 종속된 행정구명 드롭다운 목록을 만드는 과정입니다.

• 먼저 도시명 입력 영역 첫 셀(B3)의 드롭다운 목록 중 하나(서울, 부산 중)를 선택합니다. 공란으로 진행할 경우 오류 경고가 나옵니다.

• 우측 행정구명 입력 영역 첫 셀(C3)에 커서를 위치하고 데이터 유효성 검사를 실행합니다.(입력 영역 전 범위를 선택하지 않고 해당 셀 하나만 선택합니다.)

• 데이터 유효성 검사 창에서 제한대상은 목록으로 원본(S)에는 =INDIRECT( 를 입력하고 상위 항목 B3 셀을 클릭하고 )를 입력하여 수식을 닫습니다.

• 데이터 유효성 검사 창에서 보면 INDIRECT 함수는 =INDIRECT($B$3) 절대 참조 로 표시됩니다. 본 수식은 하단의 입력 영역에 복사 적용할 것이므로 =INDIRECT(B3) 상대 참조로 변경해야 합니다. 확인을 눌러 드롭다운 목록을 만듭니다.

  

• 드롭다운 표시 단추가 생성되면 C3셀 우측 채우기 핸들을 드래그하여 다음 입력 영역에 복사합니다.

• '행정구명' 입력 셀을 선택하고 상위 '도시명' 입력영역에 데이터가 입력돼 있는 경우 종속 드롭다운 목록이 드롭다운 됩니다.

  

  1차 종속 드롭다운 메뉴

3. 2차 종속(소분류) 드롭다운 목록

1차 종속 '행정구명'의 각 목록 항목에 종속된 '행정동'명 드롭다운 목록을 만드는 과정입니다.

• 먼저 행정구명 입력 영역 첫 셀(C3)의 드롭다운 목록 중 하나(해당 행정구명 목록)를 선택합니다. 공란으로 진행할 경우 오류 경고가 나옵니다.

• 우측 행정구명 입력 영역 첫 셀(D3)에 커서를 위치하고 데이터 유효성 검사를 실행합니다.(입력 영역 전 범위를 선택하지 않고 해당 셀 하나만 선택합니다.)

• 데이터 유효성 검사 창에서 제한대상은 목록으로 원본(S)에는 =INDIRECT( 를 입력하고 상위 항목 C3 셀을 클릭하고 )를 입력하여 수식을 닫습니다.

• 데이터 유효성 검사 창의 원본(S)상자의 =INDIRECT($C$3) 를 =INDIRECT(C3) 상대 참조로 변경합니다.

  

• 드롭다운 표시 단추가 생성되면 D3셀 우측 채우기 핸들을 드래그하여 다음 입력 영역에 복사합니다.

• '행정동명' 입력 셀을 선택하고 상위 '행정구명' 입력영역에 데이터가 입력돼 있는 경우 종속 드롭다운 목록이 드롭다운 됩니다.

  

  2차 종속 드롭다운 메뉴

4. 목록의 항목 데이터가 유효한 이름으로 사용할 수 없는 경우

종속 드롭다운 목록을 만들려면 항목 데이터의 이름을 갖는 종속 목록을 만들어야 하는데 유효한 이름으로 사용할 수 없는 띄어 쓰기, 특수 문자 등이 포함된 항목이 필요한 경우가 있을 수도 있습니다

아래와 같이 목록을 만들었습니다.

드롭다운 목록

▶ 띄어 쓰기로 공백을 포함할 때

• 공백문자를 제거한 문자로 종속 목록에 이름을 지정합니다.
• C2셀에 데이터 유효성 검사에서 INDIRECT 함수에  공백 문자(" ")를 빈문자("")로 변환하는 SUBSTITUTE 함수를 인수로 사용해 야채과일 이름의 종속 목록을 참조합니다.

  =INDIRECT(SUBSTITUTE(B2," ",""))

공백 문자 드롭다운 목록

▶ 특수 문자를 포함할 경우

• 위 목록 그림에서 열:대체이름으로 표시한 목록과 같이 종속 드롭다운 목록을 만들 상위 목록 항목옆에 대체 이름을 삽입합니다.
• 데이터 유효성 검사에서 INDIRECT함수에 해당 항목의 문자를 열:대체이름이 표시하는 범위($G$3:$H$4)에서 대체하는 이름을 VLOOKUP함수로 찾아 대체이름으로 종속 목록을 참조하는 방법도 있습니다.

  =INDIRECT(VLOOKUP(B2,$G$3:$H$4,2,0))

VLOOKUP 대체 이름

 

 

 

엑셀 드롭다운 목록 표(테이블)만들기

 

구글 스프레드시트 드롭다운 목록 목록으로 데이터 입력

 

엑셀 VBA 워크시트 이벤트를 처리하는 VBA 프로시저 코드 작성