Excel에서 동적 종속 드롭다운 목록 만들기 (단계별)

이 튜토리얼에서는 첫 번째 드롭다운 목록에서 선택된 값에 따라 옵션을 표시하는 종속 드롭다운 목록을 만드는 방법을 단계별로 소개합니다. 즉, 다른 목록의 값을 기반으로 Excel 데이터 유효성 검사 목록을 만들 것입니다.

동적 종속 드롭다운 목록 만들기
손쉬운 도구를 사용하여 10초 만에 종속 드롭다운 목록 만들기
Excel 2021, Excel 365 및 최신 버전에서 동적 종속 드롭다운 목록 만들기
이 튜토리얼에 대해 자주 묻는 질문들

샘플 파일 무료 다운로드

비디오: Excel 종속 드롭다운 목록 만들기

동적 종속 드롭다운 목록 만들기

1단계: 드롭다운 목록 항목 입력하기

1. 먼저 드롭다운 목록에 표시하려는 항목을 입력하고, 각 목록은 별도의 열에 배치합니다.

첫 번째 열(제품)의 항목들은 나중에 종속 목록의 이름으로 사용됩니다. 예를 들어, 여기서 과일과 채소는 각각 B2:B5와 C2:C6의 이름이 됩니다.

스크린샷 보기:

2. 그런 다음 각 데이터 목록에 대한 테이블을 생성합니다.

범위 A1:A3을 선택하고 "삽입" → "표"를 클릭한 후, 표 만들기 대화 상자에서 "내 테이블에 머리글이 있음" 확인란을 선택하고 "확인"을 클릭합니다.

그런 다음 이 단계를 반복하여 나머지 두 목록에 대한 테이블을 만듭니다.

모든 테이블과 범위 참조는 이름 관리자(“Ctrl” + “F3”을 눌러 열기)에서 확인할 수 있습니다.

2단계: 범위 이름 생성하기

이 단계에서는 주 목록과 각 종속 목록에 대해 "이름"을 생성해야 합니다.

1. 주 목록("A2:A3")에 나타나는 항목들을 선택합니다.

2. 그런 다음 "수식 표시줄(F)" 옆에 있는 "이름 상자"로 이동합니다.

3. 여기에 이름을 입력합니다. 여기서는 "Product"라고 이름을 지정합니다.

4. "Enter" 키를 눌러 완료합니다.

그런 다음 위의 단계를 반복하여 각 종속 목록에 대해 별도로 이름을 생성합니다.

여기서 두 번째 열(B2:B5)을 Fruit로, 세 번째 열(C2:C6)을 Vegetable로 이름을 지정합니다.

모든 범위 이름은 이름 관리자(“Ctrl” + “F3”을 눌러 열기)에서 확인할 수 있습니다.

3단계: 주 드롭다운 목록 추가하기

다음으로 주 드롭다운 목록(Product)을 추가합니다. 이 목록은 일반적인 데이터 유효성 검사 드롭다운 목록이며 종속 드롭다운 목록이 아닙니다.

1. 먼저 테이블을 생성합니다.

셀("E1")을 선택하고 첫 번째 열 머리글("Product")을 입력한 후 다음 열 셀("F1")로 이동하여 두 번째 열 머리글("Item")을 입력합니다. 이 테이블은 드롭다운 목록을 포함하게 됩니다.

그런 다음 두 머리글("E1" 및 "F1")을 선택하고 "삽입" 탭을 클릭한 후 표 그룹에서 "표"를 선택합니다.

표 만들기 대화 상자에서 "내 테이블에 머리글이 있음" 박스를 선택하고 "확인"을 클릭합니다.

2. 주 드롭다운 목록을 삽입하려는 셀("E2")을 선택하고 "데이터" 탭을 클릭한 후 "데이터 도구" 그룹에서 "데이터 유효성 검사" → "데이터 유효성 검사"를 클릭합니다.

3. 데이터 유효성 검사 대화 상자에서

• "허용" 섹션에서 "목록"을 선택하고
• 아래 공식을 "소스" 바에 입력합니다. Product는 주 목록의 이름입니다.
• "확인"을 클릭합니다.

=Product

주 드롭다운 목록이 생성된 것을 확인할 수 있습니다.

4단계: 종속 드롭다운 목록 추가하기

1. 종속 드롭다운 목록을 추가하려는 셀("F2")을 선택하고 "데이터" 탭을 클릭한 후 데이터 도구 그룹에서 "데이터 유효성 검사" → "데이터 유효성 검사"를 클릭합니다.

2. 데이터 유효성 검사 대화 상자에서

• "허용" 섹션에서 "목록"을 선택하고
• 아래 공식을 "소스" 바에 입력합니다. E2는 주 드롭다운 목록이 포함된 셀입니다.
• "확인"을 클릭합니다.

=INDIRECT(SUBSTITUTE(E2," ","_"))

E2가 비어 있는 경우(주 드롭다운 목록에서 아무 항목도 선택하지 않은 경우), 아래와 같은 메시지가 팝업됩니다. 계속하려면 "예"를 클릭하세요.

이제 종속 드롭다운 목록이 생성되었습니다.

5단계: 종속 드롭다운 목록 테스트하기

1. 주 드롭다운 목록("E2")에서 "Fruit"를 선택한 후 종속 드롭다운 목록("F2")에서 화살표 아이콘을 클릭하여 과일 항목이 목록에 있는지 확인하고 종속 드롭다운 목록에서 하나의 항목을 선택합니다.

2. "Tab" 키를 눌러 데이터 입력 테이블에서 새 행을 시작하고 "Vegetable"을 선택한 후 오른쪽의 다음 셀로 이동하여 채소 항목이 목록에 있는지 확인하고 종속 드롭다운 목록에서 하나의 항목을 선택합니다.

손쉬운 도구를 사용하여 10초 만에 종속 드롭다운 목록 만들기

Kutools for Excel은 종속 드롭다운 목록을 더 쉽고 빠르게 만들 수 있는 강력한 도구를 제공합니다:

1단계: 드롭다운 목록 항목 입력하기

먼저 아래 스크린샷과 같이 데이터를 정리하세요:

2단계: Kutools 도구 적용하기

1. 생성한 데이터를 선택하고 "Kutools" 탭을 클릭한 후 드롭다운 목록을 클릭하여 하위 메뉴를 표시하고 동적 드롭다운 목록을 클릭합니다.

2. "종속 드롭다운 목록"에서:

• 데이터 모드와 일치하는 "모드 B"를 선택하고
• "출력 범위"를 선택합니다. 출력 범위 열은 데이터 범위 열과 같아야 합니다.
• "확인"을 클릭합니다.

이제 종속 드롭다운 목록이 생성되었습니다.

Excel 2021, Excel 365 및 최신 버전에서 동적 종속 드롭다운 목록 만들기

Excel 365, Excel 2021 또는 최신 버전을 사용 중이라면 새로운 함수인 "UNIQUE"와 "FILTER"를 사용하여 동적 종속 드롭다운 목록을 빠르게 생성할 수 있습니다.

아래 스크린샷과 같이 소스 데이터가 배열되어 있다고 가정하고, 아래 단계를 따라 동적 드롭다운 목록을 생성하세요.

1단계: 주 드롭다운 목록 항목 가져오기 위한 공식 사용하기

셀을 선택하고, 예를 들어 G3 셀을 선택한 후 UNIQUE 및 FILTER 함수를 사용하여 "Product" 목록에서 고유한 값을 추출하고 "Enter" 키를 누릅니다.

=UNIQUE(FILTER(A3:A20, A3:A20<>""))

2단계: 주 드롭다운 목록 생성하기

1. 주 드롭다운 목록을 배치하려는 셀을 선택하고, 예를 들어 "D3" 셀을 선택한 후 "데이터" 탭을 클릭하고 "데이터 도구" 그룹에서 "데이터 유효성 검사" → "데이터 유효성 검사"를 클릭합니다.

2. "데이터 유효성 검사" 대화 상자에서

• "허용" 섹션에서 "목록"을 선택하고
• 아래 공식을 "소스" 바에 입력하고
• "확인"을 클릭합니다.

=$G$3#

이제 주 드롭다운 목록이 생성되었습니다.

3단계: 종속 드롭다운 목록 항목 가져오기 위한 공식 사용하기

셀을 선택하고, 예를 들어 H3 셀을 선택한 후 FILTER 함수를 사용하여 셀 "D3" (주 드롭다운 목록에서 선택된 항목)에 기반하여 항목을 필터링하고 "Enter" 키를 누릅니다.

=FILTER(B3:B20, A3:A20=D3)

4단계: 종속 드롭다운 목록 생성하기

1. 종속 드롭다운 목록을 배치할 셀을 선택하고, 예를 들어 "E3" 셀을 선택한 후 "데이터" 탭을 클릭하고 "데이터 도구" 그룹에서 "데이터 유효성 검사" → "데이터 유효성 검사"를 클릭합니다.

2. "데이터 유효성 검사" 대화 상자에서

• "허용" 섹션에서 "목록"을 선택하고
• 아래 공식을 "소스" 바에 입력하고
• "확인"을 클릭합니다.

=$H$3#

이제 종속 드롭다운 목록이 성공적으로 생성되었습니다.

새로운 항목을 추가하거나 A3:A20에서 일부 변경 사항을 수행하면 드롭다운 목록이 자동으로 업데이트됩니다.

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))

=SORT(FILTER(B3:B20, A3:A20=D3))

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)

=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)