Excel 에서 동적 동적 목록 만들기(단계별)

이 튜토리얼에서는 첫 번째 드롭다운 목록에서 선택한 값에 따라 선택지를 표시하는 동적 목록를 단계별로 만드는 방법을 소개합니다。 즉， 다른 목록의 값을 기준으로 Excel 데이터 유효성 검사 목록을 만드는 것입니다。

동적 동적 목록 만들기
유용한 도구로 동적 목록을 10 초 만에 만들기
Excel 2021, Excel 365 및 최신 버전에서 동적 동적 목록 만들기
이 튜토리얼에 대해 궁금할 수 있는 몇 가지 질문

샘플 파일 무료 다운로드

영상： Excel 동적 목록 만들기

동적 동적 목록 만들기

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

1. 먼저 드롭다운 목록에 표시할 항목을 입력합니다。 각 목록은 별도의 열에 입력하세요。

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

스크린샷 참조：

2. 그런 다음 각 데이터 목록에 대해 테이블을 만듭니다。

A1:A3 열 범위를 선택하고， 「삽입」 > 「테이블」을 클릭한 후， 테이블 만들기 대화상자에서 「내 테이블에 머리글이 있습니다」 확인란을 선택하고 「확인」을 클릭합니다。

다른 두 목록에 대해서도 이 단계를 반복하여 테이블을 만듭니다。

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

단계 2: 셀 이름 만들기

이 단계에서는 기본 목록과 각 종속 목록에 대해 「이름」을 만들어야 합니다。

1. 기본 목록에 표시할 항목(“A2:A3")을 선택합니다。

2. 그런 다음 「수식 표시줄」 옆에 있는 「이름 상자」로 이동합니다。

3. 여기에 이름을 입력합니다。 예： 「제품」。

4. 「Enter」 키를 눌러 완료합니다。

그런 다음 위 단계를 반복하여 각 종속 목록에 대해 개별적으로 이름을 만듭니다。

두 번째 열(B2:B5)은 과일로， 세 번째 열(C2:C6)은 채소로 이름을 지정합니다。

이름 관리자에서 모든 셀 이름을 확인할 수 있습니다(「Ctrl」 + “F3"을 눌러 열기)。

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

다음으로， 종속 드롭다운 목록이 아닌 일반적인 데이터 유효성 검사 드롭다운 목록인 기본 드롭다운 목록(제품)을 추가합니다。

1. 먼저 테이블을 만듭니다。

셀(“E1")을 선택하고 첫 번째 열 머리글(「제품」)을 입력한 후 다음 열 셀(“F1")로 이동하여 두 번째 열 머리글(「항목」)을 입력합니다。 이 테이블은 드롭다운 목록을 저장합니다。

그런 다음 두 머리글(“E1" 및 “F1")을 선택하고， 「삽입」 탭을 클릭한 후 테이블 그룹에서 「테이블」을 선택합니다。

테이블 만들기 대화상자에서 「내 테이블에 머리글이 있습니다」 확인란을 선택하고 「확인」을 클릭합니다。

2. 기본 드롭다운 목록을 삽입할 셀(“E2")을 선택하고， 「데이터」 탭을 클릭한 후 데이터 도구 그룹에서 「데이터 유효성 검사」 > 「데이터 유효성 검사」를 클릭합니다。

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

• 「허용」 섹션에서 「목록」을 선택하세요。
• 「소스」란에 아래 수식을 입력하세요. Product 는 주 목록의 이름입니다。
• 「확인」을 클릭하세요。

=Product

기본 드롭다운 목록가 생성된 것을 확인할 수 있습니다。

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

1. 동적 목록를 추가할 셀(“F2")을 선택하고， 「데이터」 탭을 클릭한 후 데이터 도구 그룹에서 「데이터 유효성 검사」 > 「데이터 유효성 검사」를 클릭합니다。

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

• 「허용」 섹션에서 「목록」을 선택하세요。
• 「소스」란에 아래 수식을 입력하세요. E2 는 주 드롭다운 목록을 포함하는 셀입니다。
• 「확인」을 클릭하세요。

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

E2 가 비어 있는 경우(기본 드롭다운 목록에서 아무 항목도 선택하지 않은 경우)， 아래와 같은 메시지가 나타납니다。 계속하려면 「예」를 클릭하세요。

이제 동적 목록이 생성되었습니다。

단계 5: 동적 목록 테스트

1. 기본 드롭다운 목록(“E2")에서 「과일」을 선택한 후 동적 목록(“F2")로 이동하여 화살표 아이콘을 클릭하고 목록에 과일 항목이 있는지 확인한 후 동적 목록에서 하나의 항목을 선택합니다。

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

유용한 도구로 동적 목록을 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 함수를 사용하여 기본 드롭다운 목록의 소스가 될 「제품」 목록에서 고유한 값을 추출한 후 「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)