Master Excel 드롭다운 목록:생성, 편집, 제거, 필터링 및 보호

데이터세트에는 '주문 ID', '고객 이름', '기기', '주문 날짜', '배송 날짜', '결제 방법' 등 온라인 쇼핑 정보가 표시됩니다.

Excel의 드롭다운 목록이란 무엇인가요?

드롭다운 목록은 사용자가 옵션 목록에서 요소를 선택할 수 있는 기능입니다.

Excel에서 드롭다운 목록을 만드는 방법

1. 셀 범위에서 드롭다운 목록 만들기

B열의 값을 기반으로 드롭다운 목록을 만들려면 :

• 셀 선택(C18 ).
• 데이터 로 이동 탭을 클릭하고 데이터 유효성 검사를 선택하세요. .

• 데이터 유효성 검사에서 , 설정으로 이동하세요. .
• 목록 허용을 선택하세요. .
• 범위 정의($B$6:$B$15 ) 소스 .
• 확인을 클릭하세요. .

이것이 출력입니다.

• C19 에 다음 수식을 입력하세요. C, 열의 값을 가져오려면 주문 ID 선택을 기반으로 합니다.

=VLOOKUP($C$18,$B$6:$G$15,ROW(B19)-ROW($B$18)+1,FALSE)

• 채우기 핸들 사용 값을 추출합니다.

2. 테이블에서 드롭다운 목록 만들기

• 셀 범위를 선택하고 삽입 탭을 클릭하여 테이블을 생성하세요.
• 표를 클릭하세요. .

• 내 테이블에 헤더가 있습니다를 확인하세요. .
• 확인을 클릭하세요. .

• C18 에서 드롭다운 목록을 만들려면 고객 이름의 데이터 사용 , 목록 을 선택하세요. 허용에서 .
• INDIRECT 함수를 사용하여 다음 수식을 입력하세요. 소스에서 확인을 클릭하세요. .

=INDIRECT("Table1[Customer Name]")

• C19 에 다음 수식을 입력하세요. 고객 이름 선택에 따라 기기 이름을 추출합니다.

=INDEX(Table1,MATCH(C18,Table1[Customer Name],0),3)

이것이 출력입니다.

3. 명명된 범위를 사용하여 드롭다운 목록 만들기

• 셀 범위를 선택하고 수식으로 이동합니다. 탭.
• 이름 정의를 클릭하세요. .

• 이름(ID) 설정 ) 이름 참조 대상에서 범위를 정의합니다. .

• C19 에서 주문 ID 드롭다운 목록 만들기 소스:에 다음 수식을 입력합니다.

• C19 에서 다음 수식을 사용하세요. 주문 ID 선택에 따라 고객 이름을 갖습니다.

=INDEX(B6:G15,MATCH($C$18,ID,0),2)

• 다음 공식을 사용하여 C20에 기기 이름을 입력하세요. .

=INDEX(B6:G15,MATCH($C$18,ID,0),3)

이것이 출력입니다.

4. 색상으로 드롭다운 목록 만들기

조건부 서식을 사용하여 맞춤 색상을 정의하세요. 드롭다운 목록의 각 값에 대해.

• 목록 선택 허용
• 쉼표 기호(,)로 구분하여 색상 이름을 입력하세요. ):( 검은색, 흰색, 은색, 금색 ) 소스 .

• 드롭다운의 각 요소에 대한 색상을 정의하려면 드롭다운을 선택하고 조건부 서식 으로 이동하세요. 홈에서 탭.
• 셀 규칙 강조 표시로 이동 같음…을 클릭합니다.

• 같음 , 색상 이름(검은색)을 입력합니다. ).
• 사용자 정의 형식 선택 색상 이름의 색상을 설정합니다.

• 채우기에서 색상을 선택하세요. .

• 글꼴에서 글꼴 색상을 변경할 수도 있습니다. . 여기, 흰색.

• 확인을 클릭하세요. .

• 다른 옵션의 색상을 설정합니다.

드롭다운 목록은 셀 값에 따라 색상을 표시합니다.

선택에 따라 색상이 자동으로 변경됩니다.

• 채우기 핸들 사용 자동완성 드롭다운입니다.

5. 고유한 값으로 드롭다운 목록 만들기

D 열의 고유 값으로 드롭다운 목록 만들기 및 E .

• B22 에서 다음 공식을 사용하세요. 고유한 브랜드 이름을 확인하세요.

• C5 에 드롭다운 목록을 만들려면 다음 수식을 입력하세요. 고유한 값을 사용합니다.

• C22 에 다음 수식을 입력하세요. E 열의 고유 값을 보려면 C5의 브랜드와 일치하는 .

=FILTER(E9:E18,C5=D9:D18)

• 데이터 확인으로 이동 .
• 소스에서 다음 공식을 사용하세요. .

이것이 출력입니다.

6. 다른 시트의 데이터를 기반으로 드롭다운 목록 만들기

모바일 브랜드와 해당 휴대폰 모델은 전화 목록에 있습니다. 워크시트.

브랜드 이름과 휴대폰 모델을 사용하여 다른 워크시트(AnotherSheet)에 두 개의 드롭다운 목록을 만듭니다. ).

• D6 에서 브랜드 이름 드롭다운 목록을 만들려면 PhoneList 의 데이터로 워크시트의 소스에 다음 수식을 입력합니다.

• 채우기 핸들 사용 D열에 동일한 드롭다운 목록을 생성하려면 .
• INDIRECT 와 함께 다음 수식을 사용하세요. 소스의 기능  PhoneList 에서 전화 모델을 얻으려면 D 열의 브랜드 이름을 기반으로 하는 드롭다운의 워크시트 .

이미지를 확대하려면 여기를 클릭하세요.

이것이 출력입니다.

7. 다른 통합 문서의 데이터를 기반으로 드롭다운 목록 만들기

사용 가능한 결제 방법에서 통합문서에는 온라인 쇼핑 결제수단 목록이 있습니다.

• 수식으로 이동 이름 정의를 선택하세요. .

• 새 이름 , 이름(PayType)을 정의합니다. ) 이름
• 범위 정의(Sheet1!$B$3:$B$7 ) 참조 .
• 확인을 클릭하세요. .

• Excel 통합문서의 드롭다운으로 이동 명명된 범위(PayType)를 설정합니다. ).
• [AvailablePaymentMethods.xlsx]Sheet1!PayType 을 입력하세요. AvailablePaymentMethods 에서 이름 범위를 정의합니다. 통합 문서.
• 소스에 다음 수식을 입력하세요. G6의 이름이 지정된 범위로 드롭다운 목록을 만들려면 .

• 드롭다운 목록에서 결제 수단을 선택할 수 있습니다.

• 채우기 핸들 사용 자동완성 드롭다운 목록입니다.

참고

나. 드롭다운을 만드는 동안 두 통합 문서가 모두 열려 있어야 합니다.
ii. 다른 통합 문서에서 여러 종속 드롭다운 목록을 만들지 마세요.

8. 종속 드롭다운 목록 만들기

• D6에서 독립적인 브랜드 드롭다운 목록을 만들려면 , 소스()에 다음 수식을 입력하세요. 데이터 검증) .

• E6에서 휴대폰 모델의 종속 드롭다운 목록을 생성하려면 , INDIRECT 를 사용하여 다음 수식을 입력합니다. 소스의 기능 .

이미지를 확대하려면 여기를 클릭하세요.

독립 드롭다운 목록에서 브랜드를 선택하면 종속 드롭다운 목록이 생성됩니다.

• 채우기 핸들 사용 자동완성 드롭다운 목록입니다.

9. 검색 가능한 드롭다운 목록 만들기

• 새 열 삽입(G H) 고유한 브랜드 및 모델 이름을 갖습니다. G6에서 다음 공식을 사용하세요. .

=UNIQUE(OFFSET(D6,0,0,COUNTA(D:D)-1,1))

• 소스에 다음 수식을 입력하세요. C19 에서 검색 가능한 드롭다운 목록을 만들려면 브랜드 이름으로.

C19에서 브랜드 이름을 검색할 수 있습니다. .

• H6 에 다음 수식을 입력하세요. 검색 가능한 드롭다운에서 선택한 브랜드를 기반으로 휴대전화 모델을 확인하세요.

=UNIQUE(FILTER(E6:E17,C19=D6:D17))

• 소스에 다음 수식을 입력하세요. C20 에서 검색 가능한 드롭다운 만들기 휴대전화 모델명으로 변경하세요.

독립적이고 종속적인 검색 가능한 드롭다운 목록이 있습니다.

10. 새 항목을 추가하고 자동 업데이트하기 위한 동적 드롭다운 목록 만들기

ID를 기준으로 값을 추출합니다.

이미지를 확대하려면 여기를 클릭하세요.

• 별도의 열을 삽입하고 L6에 다음 수식을 입력합니다. B 열 값을 기준으로 고유 ID를 확인합니다.

=UNIQUE(OFFSET(B6,0,0,COUNTA(B:B)-3,1))

이미지를 확대하려면 여기를 클릭하세요.

• J5 의 고유 ID로 드롭다운 목록 만들기 소스에 다음 수식을 입력하여 .

이미지를 확대하려면 여기를 클릭하세요.

• 드롭다운 목록에서 ID를 선택하고 J6 에서 다음 수식을 사용하세요. 선택한 ID의 고객 이름을 확인하세요.

=VLOOKUP($J$5,B:G,ROW(I6)-ROW($I$5)+1,FALSE)

• 채우기 핸들 사용 자동완성 나머지 값입니다.

이미지를 확대하려면 여기를 클릭하세요.

• 추출된 날짜는 정수로 표시됩니다. 숫자 형식을 간단한 날짜 로 변경하세요. 또는 자세한 날짜 숫자 형식 .

• 추가 ID 및 관련 값을 추가합니다. 드롭다운 목록과 L열에서 자동으로 업데이트됩니다. .

이미지를 확대하려면 여기를 클릭하세요.

이것이 출력입니다.

11. 검색 가능하고 동적이며 다중 종속적인 드롭다운 목록 만들기

• L6에 다음 수식을 입력하세요.

=UNIQUE(OFFSET(D6,0,0,COUNTA(D:D)-1,1))

• J5 에서 독립적인 검색 가능한 브랜드 이름 드롭다운 목록 만들기 소스에 다음 수식을 입력하여 .

• J5의 드롭다운 목록에서 선택한 브랜드를 기준으로 휴대전화 모델을 필터링합니다. . 다음 공식을 사용하세요.

=UNIQUE(FILTER(E:E,J5=D:D))

• J6에서 브랜드 이름에 따라 검색 가능한 휴대전화 모델 이름 드롭다운 목록을 만듭니다. . 소스에 다음 수식을 입력하세요. .

• J6의 드롭다운 목록에서 선택한 모델을 기준으로 칩셋을 필터링합니다. 다음 공식을 사용하세요.

=UNIQUE(FILTER(F:F,J6=E:E))

• 소스에 다음 수식을 입력하세요. J7에서 모델 이름에 따라 검색 가능한 칩셋 드롭다운 목록을 생성하려면 .

• J8 에서 다음 공식을 사용하세요. 선택한 값과 일치하는 제품의 가격을 찾으세요.

=INDEX(G:G,MATCH(1,(J5=D:D)*(J6=E:E)*(J7=F:F),0))

모든 드롭다운 목록은 동적이므로 새 항목을 추가하면 드롭다운 목록이 업데이트됩니다.

12. 사용자 정의 메시지로 드롭다운 목록 만들기

• 방법 7에 설명된 대로 휴대전화 모델의 종속 드롭다운 목록을 만듭니다. .

• 데이터 유효성 검사에서 , 메시지 입력으로 이동하세요.
• 셀을 선택하면 입력 메시지 표시를 선택합니다. .
• 제목에 제목을 설정하세요. 입력 메시지의 맞춤 메시지 .
• 확인을 클릭하세요. .

셀을 선택하면 맞춤 메시지가 표시됩니다.

13. 오류 경고 메시지가 포함된 편집 가능한 드롭다운 목록 만들기

• G6 에 다음 수식을 입력하세요. 고유한 브랜드 이름으로 동적 열을 생성합니다.

=UNIQUE(OFFSET(D6,0,0,COUNTA(D:D)-1,1))

• C19 에서 브랜드 이름의 검색 가능한 동적 드롭다운 목록을 만듭니다. 소스에 다음 수식을 입력하여 .

• 수정 가능한 드롭다운 목록을 만들려면 오류 경고 로 이동하세요. 탭.
• 잘못된 데이터를 입력한 후 오류 알림 표시 확인란을 선택합니다. .
• 잘못된 검색값에 대한 스타일, 제목, 오류 메시지를 설정하고 확인을 클릭하세요. .

• 모델의 값으로 드롭다운 목록 만들기 .

편집 가능한 드롭다운 목록에 잘못된 내용을 입력하면 다시 시도하거나 취소할 수 있는 옵션과 함께 오류 경고가 표시됩니다.

Excel 드롭다운 목록에서 항목을 추가하거나 제거하는 방법

소스에서 범위를 정의하는 동안 셀을 추가하거나 제거하여 항목을 추가하거나 제거할 수 있습니다. .

Excel에서 드롭다운 목록을 보호하는 방법

C18에서 고객 이름 드롭다운 목록이 생성되었습니다. .

• 표 전체를 선택하고 홈으로 이동합니다. 탭.
• 정렬 을 클릭하세요. 연장하려고요.

• 셀 서식 지정 , 잠김을 선택하세요. 보호 조치  탭.

• 테이블이 보호되어 있습니다. 검토로 이동 탭을 클릭하고 통합문서 보호를 클릭하세요. .

• 비밀번호를 입력하고 잠긴 셀 선택을 선택하세요. .
• 확인을 클릭하세요. .

• 비밀번호를 다시 입력하세요.

테이블과 드롭다운 목록이 모두 보호됩니다.

Excel에서 드롭다운 목록을 삭제하는 방법

• 드롭다운 목록을 선택하고 데이터  탭.
• 데이터 확인을 클릭하세요. .

• 데이터 유효성 검사에서 에서 모두 지우기를 클릭하세요.
• 확인을 클릭하세요. .

드롭다운 목록이 제거됩니다.

드롭다운 목록을 필터링하고 Excel의 선택 항목을 기반으로 데이터를 추출하는 방법

• 테이블을 만듭니다.

• D열에 드롭다운 목록을 만듭니다.

• 선택 항목에 따라 전체 데이터를 추출하려면 다음 수식을 입력하세요.

=FILTER(Table3,Table3[Device]=$C$18,"No Information Available")

• 드롭다운 목록의 값을 변경하면 데이터 추출이 자동으로 업데이트됩니다.

Excel 드롭다운 목록 문제를 해결하는 방법

1. 드롭다운 목록이 표시되지 않습니다

해결책:

• 셀 내 드롭다운 확인 드롭다운 목록을 표시합니다.

2. 드롭다운 목록에 공백 표시

해결책:

• 공백을 무시하도록 드롭다운 목록 범위를 정의합니다.

3. 드롭다운 목록에 유효한 항목이 허용되지 않습니다

해결책:

• 유효한 항목을 입력할 때 적절한 대소문자를 사용하세요.

4. 드롭다운 목록에 잘못된 항목이 허용됨

소스 범위가 실제 값 목록보다 크므로 해당 셀에 입력된 값이 유효하지 않더라도 나열될 수 있습니다.

해결책:

• 잘못된 요소의 입력을 중지하려면 드롭다운 목록 범위를 정의하세요.

연습 학습서 다운로드
연습 문제집을 다운로드하세요.

Excel 드롭다운 목록:지식 허브

• Excel에서 드롭다운 목록 만들기
• Excel에서 드롭다운 목록 편집
• Excel의 필터가 포함된 드롭다운 목록
• Excel 종속 드롭다운 목록
• Excel에서 드롭다운 목록을 제거하는 방법
• 셀 값을 Excel의 드롭다운 목록과 연결하는 방법
• Excel 드롭다운 목록이 작동하지 않음
• Excel에서 드롭다운 목록을 생성하는 IF 문
• Excel의 드롭다운 목록 선택을 기반으로 데이터 추출
• Excel의 셀 값을 기준으로 목록 채우기
• Excel의 여러 열에 있는 드롭다운 목록
• Excel의 드롭다운 목록에 대한 공백 옵션
• 드롭다운에서 선택하고 다른 시트에서 데이터 가져오기
• [수정됨!] 드롭다운 목록 공백 무시 Excel에서 작동하지 않음
• Excel의 자동 완성 데이터 유효성 검사 드롭다운 목록

<