연결된 드롭다운 목록

설명

Excel에서는 빠르고 쉽게 할 수 있습니다. 드롭다운 목록 만들기 , 하지만 종속 드롭다운 목록을 만들어 본 적이 있나요? 이러한 목록을 만드는 방법에는 여러 가지가 있으므로 이를 살펴보겠습니다.

방법 1. INDIRECT 함수

이 기술은 INDIRECT 함수의 적용을 기반으로 합니다. 이 함수는 지정된 셀의 내용을 Excel이 이해하는 범위 주소로 변환하는 간단한 작업을 수행할 수 있습니다. 즉, 셀에 "라는 텍스트가 포함되어 있는 경우 А1 ", 그러면 함수는 결과적으로 셀에 대한 참조를 제공합니다. А1 . 셀에 "라는 단어가 포함된 경우 Auto "이면 함수는 이름이 지정된 범위에 대한 참조를 출력합니다. Auto 등.

예를 들어, 다음 자동차 모델 목록을 살펴보겠습니다. Toyota, Ford 그리고 Nissan :

Toyota 모델의 전체 목록을 선택하십시오(셀에서 А2 목록 끝까지) 이 범위에 이름을 지정합니다. Toyota 메뉴에서 삽입 - 이름 - 할당 (Insert - Name - Define) . 그런 다음 목록에 대해 동일한 작업을 반복합니다. Ford 그리고 Nissan , 이에 따라 범위의 이름을 지정합니다. Ford 그리고 Nissan .

이제 자동차 브랜드를 선택하기 위한 첫 번째 드롭다운 목록을 만들어 보겠습니다. 빈 셀을 선택하고 메뉴를 엽니다. 데이터 - 확인 (Data - Validation) , 드롭다운 목록에서 데이터 형식 옵션을 선택하세요 목록 그리고 현장에서 원천 - 브랜드 이름이 있는 셀을 강조 표시합니다(이 예에서는 노란색 셀). 클릭 후 ОК 첫 번째 드롭다운 목록이 준비되었습니다.

이제 첫 번째 목록에서 선택한 브랜드의 모델을 표시하는 두 번째 드롭다운 목록을 만들어 보겠습니다. 이전 경우와 마찬가지로 빈 셀을 선택하고 메뉴를 엽니다. 데이터 - 확인 - 더 나아가 목록 . 해당 영역에서 원천 다음 수식을 입력해야 합니다.

=INDIRECT(F3)

어디:

• F3 - 첫 번째 드롭다운 목록이 있는 셀의 주소 - 자신의 주소로 바꿉니다.

모두. 클릭 후 좋아요 두 번째 목록의 내용은 첫 번째 목록에서 선택한 범위의 이름으로 선택됩니다.

이 방법의 단점:

• OFFSET 유형 수식으로 지정된 동적 범위는 보조(종속) 범위로 작동할 수 없습니다. 기본(독립) 목록에 사용할 수 있지만 보조 목록은 공식 없이 엄격하게 정의되어야 합니다.
• 보조 범위 이름은 기본 드롭다운 목록 항목과 일치해야 합니다. 즉, 공백이 있는 텍스트가 포함된 경우 밑줄 등으로 바꿔야 합니다.
• 많은 명명된 범위를 수동으로 만들어야 합니다.

방법 2. 일치 목록(OFFSET 및 MATCH)

이 방법을 사용하려면 다음 유형의 정렬된 모델 일치 목록이 필요합니다.

브랜드의 기본 드롭다운 목록을 생성하려면 위에서 설명한 일반적인 방법, 즉 다음을 사용할 수 있습니다.

• D1:D3 범위의 이름을 지정합니다(예: 브랜드 )
• 탭에서 선택 데이터 (Data) 팀 데이터 검증 (Data validation)
• 드롭다운 목록에서 확인 옵션을 선택하세요. 목록 (List) 품질로 지정 출처 (Source) = 브랜드 또는 D1:D3 셀을 선택하세요(목록과 같은 시트에 있는 경우).

그러나 모델의 종속 목록의 경우 특정 브랜드의 모델 셀만 동적으로 참조하는 OFFSET 함수를 사용하여 명명된 범위를 만들어야 합니다. 이를 위해:

• Ctrl + F3을 누르거나 버튼을 사용하세요. 이름 관리자 (Name manager) 탭에 방식 (Formulas) . 2003 이전 버전에서는 메뉴 명령이었습니다. 삽입 - 이름 - 할당 (Insert - Name - Define)
• 임의의 이름으로 새 명명된 범위를 만듭니다(예: 모델 ) 그리고 현장에서 링크 (Reference) 창 하단에 다음 수식을 수동으로 입력합니다.

=OFFSET( $A$1 ; MATCH($G$7;$A:$A;0)-1 ; 1 ; COUNTIF($A:$A;$G$7) ; 1 )

OFFSET 함수는 원하는 크기의 범위에 대한 참조를 발행할 수 있으며, 주어진 행과 열 수만큼 초기 셀을 기준으로 이동됩니다. 보다 이해하기 쉬운 버전에서 이 함수의 구문은 다음과 같습니다.

=OFFSET( 시작_셀 ; Shift_Down ; Shift_right ; range_size_in_rows ; range_size_in_columns )

그래서:

• 시작_셀 - 목록의 첫 번째 셀을 선택합니다. A1 ;
• Shift_Down - 간단히 말해서 주어진 범위(열)에서 선택한 브랜드(G7)가 있는 셀의 일련 번호를 출력하는 MATCH 함수를 계산합니다. 그리고 );
• Shift_right =1 이므로 인접한 열의 모델을 참조하고 싶습니다( 안에 );
• range_size_in_rows - 필요한 값의 목록(A열)에서 발생 횟수를 계산할 수 있는 COUNTIF 함수를 사용하여 계산합니다. - 자동차 브랜드( G7 );
• range_size_in_columns =1 , 따라서 모델이 포함된 하나의 열이 필요합니다.

결과는 다음과 같아야 합니다.

생성된 수식을 기반으로 드롭다운 목록을 셀에 추가하는 작업이 남아 있습니다. G8 . 이를 위해:

• 셀 선택 G8
• 탭에서 선택 데이터 (Data) 팀 데이터 검증 (Data validation) 아니면 메뉴에서 데이터 - 확인 (Data - Validation)
• 드롭다운 목록에서 확인 옵션을 선택하세요. 목록 (List) 다음과 같이 입력합니다. 출처 (Source) 부호는 우리 범위의 이름과 같습니다. 즉 = 모델 .

주제에 관한 기사:

• 셀의 드롭다운 목록
• 셀에 데이터를 채울 때의 힌트