Excel에서 동적 종속 데이터 유효성 검사 목록을 구현하는 방법

Question

웹의 소스는 종속 데이터 유효성 검사 목록을 구현하는 방법을 보여 주지만 내가 본 것은 모두 정적이며 자동으로 업데이트되지 않습니다.

이 질문에 답할 수 있는 사람은 누구나 데이터 검증 목록이 전문적인 수준으로 작동하는 방식을 이미 알고 있다고 생각합니다. 하지만 저와 같은 학습자의 목적을 위해 간략한 설명을 제공하겠습니다(Google 및 YouTube 동영상에서 자세한 내용을 읽어 보시기 바랍니다).

데이터 유효성 검사 목록을 사용하면 통합 문서에 정의된 데이터 구조를 만들 수 있습니다. 이는 사용자 상호 작용 기반에 더 적합합니다. 통합 문서 사용자가 도넛, 케이크, 머핀, 크럼펫과 같은 특정 데이터 집합만 삽입하도록 하려면 "Snacks"라는 명명된 범위를 사용하여 목록을 만들거나 테이블을 만들고 테이블 이름을 "Snacks"로 지정할 수 있습니다. 이름이 한 단어인 경우(예: 이름에 공백이 없음)

목록 사용의 단점은 크루아상이라는 목록에 또 다른 스낵을 추가해야 하는 경우 원래 생성된 목록을 다시 정의해야 한다는 것입니다. 테이블은 마지막 행에 새 행을 추가하거나 현재 테이블 행 다음에 후속 행을 쓰고 Enter 키를 눌러 테이블에 새 행을 쉽게 추가할 수 있다는 점에서 이러한 비효율성을 우회합니다.

이제 데이터 검증을 위해 설명이 질문이 너무 길어지기 때문에 자세한 내용을 보려면 아래 링크를 방문하는 것이 좋습니다.http://analyticsdemystified.com/excel-tips/excel-dropdowns-done-right/

따라서 종속 유효성 검사 목록은 이전 유효성 검사 목록에서 사용자가 선택한 값을 기반으로 하는 목록입니다. 예를 들어 도넛 유형에는 아이스 링, 초콜릿 글레이즈, 잼 및 커스터드 도넛이 포함됩니다. 머핀 종류에는 바나나, 블루베리, 초콜릿이 있습니다. 케이크 종류에는 레드벨벳, 당근, 코코넛이 있습니다. 마지막으로 Crumpets 유형에는 English, Scottish 및 Pikelets가 포함됩니다.

따라서 셀에 기본 범주 목록의 드롭다운이 있는 경우 하위 범주를 기반으로 동적 종속 유효성 검사 목록을 만드는 방법은 무엇입니까?

동적 종속 유효성 검사 목록이란 모든 열 목록이 아래와 같은 테이블임을 의미합니다.

이제 문제는 셀 C13에 스낵 테이블에 대한 유효성 검사 목록이 있는 경우 인접한 셀을 어떻게 구현합니까? 사용자가 선택한 C13 입력을 기반으로 셀 D13을 유효성 검사 목록으로 만드는 방법입니다(예: C13이 도넛으로 선택된 경우). D13의 유효성 검사 목록에서 가능한 선택은 Donuts 테이블의 열 데이터가 됩니다.

C13의 원래 유효성 검사 목록은 명명된 범위가 아닌 테이블이므로 INDIRECT 함수를 사용하여 생성되었을 것이라는 점에 유의하는 것이 중요합니다.

Answer 1

간단한 4단계로 쉽게 완료하세요

1 단계 테이블을 생성합니다:

2 단계 테이블 이름 지정: 기본 테이블의 값과 일치하도록 테이블을 선택하고 이름을 바꿉니다.1 번 테이블, 예를 들어 헤더가 "Cakes"인 Table2의 이름은 다음과 같습니다.케이크, 이는 기본 테이블의 값이므로1 번 테이블.
하지만 어떻게? 표의 모서리를 클릭하여 선택하고 해당 위치에 새 레이블을 입력하세요.표2또는 유사하고 Enter를 누르십시오. 여전히 "Table2" 등이 표시되어도 걱정하지 마세요.
(메모!이는 기본 테이블에는 필요하지 않습니다. 1 번 테이블.)

3단계 첫 번째 목록을 원하는 위치를 선택하고 데이터 탭으로 이동하여 데이터 유효성 검사를 시작하세요. "list"를 선택하고 =INDIRECT("Table1")소스 창에 다음을 입력합니다.

4단계 첫 번째 목록에 종속되는 두 번째 목록의 셀을 선택합니다. 데이터 유효성 검사로 돌아가서 "list"를 선택하고 =INDIRECT(K2)"K2"가 첫 번째 목록의 위치인 곳을 입력합니다.

완료

두 번째 목록에 종속된 세 번째 목록의 경우 4단계를 반복하고 대신 두 번째 목록을 참조하세요. 행운을 빌어요!

Answer 2

이것이 작동한다는 사실에 대해서는 내 말을 믿어야 할 것입니다. 그러나 나는 이 정확한 목적을 위해 (매우 지저분한 스파게티 코드) 매크로를 작성했습니다. 불행하게도 코드는 전혀 우아하지 않지만, 작동합니다! 사람들이 말했듯이... "작동하게 하고, 올바르게 만들고, 빠르게 만드세요."

1단계: VBA 코드

세 가지가 필요합니다.

1. Chip Pearson의 어레이 모듈--- 실제 VBA 코드는 끝 부분에 있습니다.

2. Chip Pearson의 정렬 모듈-- 실제 VBA 코드도 끝 부분에 있습니다.

3. 내 끔찍하게 조직된 매크로

이를 통합 문서의 VBA 모듈에 붙여넣으려면 Alt+ 를 누르고 F11프로젝트 탐색기에서 프로젝트를 찾은 다음 모듈 폴더를 마우스 오른쪽 버튼으로 클릭하고 "모듈 삽입"을 선택합니다. 위 3개 링크 각각의 코드를 별도의 모듈에 붙여넣습니다.

VBA 편집기를 사용하는 동안 참조 대화 상자( Alt+ T다음 Enter)를 열고 'Microsoft Scripting Runtime'을 선택하세요.

2단계: 데이터 구조화

이를 제쳐두고 이제 원하는 것은 마치 잘 구성된 데이터베이스인 것처럼 데이터(예: '데이터'라고 함)가 포함된 시트입니다. 내가 할 방법은 다음과 같습니다.

두 개의 시트를 더 만들었습니다. 하나는 드릴다운 유효성 검사 논리가 발생하는 위치에 대한 유효성 검사이고, 다른 하나는 최종 사용자에게 중요한 실제 콘텐츠에 대한 기본입니다.

3단계: 메인 시트

데이터 시트로 이동하여 다음 구조를 설정해 보겠습니다.

셀을 선택 C2하고 이름을 지정합니다 Snack.Selected. 셀 이름을 지정하려면 셀을 선택하고 을 누른 다음 Alt M M D그림과 같이 나타나는 대화 상자에 이름을 입력하세요. 지금은 다음 단계가 더 이해하기 쉽도록 '케이크'와 같은 값을 입력합니다.

계속해서 셀 이름을 C3'Type.Selected'로 지정하고 지금은 비워 두세요.

4단계: 데이터 시트의 테이블에서 값 가져오기

유효성 검사 시트로 이동하여 다음 구조를 설정합니다.

스크린샷에서 볼 수 있듯이 셀을 선택 B3:B20하고 다음 수식을 입력해야 합니다. 여기서 매크로가 실제로 작동합니다.

=MultiLookup("Data","Snack",TRUE,TRUE)

Enter를 누르는 대신 Ctrl+Shift+Enter를 누르십시오.배열 수식

광고한 대로 작동하면 간식 목록과 #N/A 오류가 표시됩니다. 오류는 예상된 것입니다. 이는 크기를 기준으로 예상한 것보다 스낵이 적다는 의미일 뿐입니다 B3:B20.

공식의 매개변수 설명:

1. 데이터가 위치한 시트("데이터")
2. 해당 시트에서 원하는 필드("간식")
3. 중복 항목(예: 그룹화)을 제거하시겠습니까? (진실)
4. 알파벳순으로 정렬되어 있나요? (진실)

좋아, 그것은 가치가 거의 없는 많은 작업처럼 보이지만 이제 최종 마무리가 됩니다. 셀에 C3:C20다음 수식을 배치합니다.

=MultiLookup("Data","Type",TRUE,TRUE,"Snack",Snack.Selected)

이전 단계에서 기본 시트에 입력한 값인 사용 가능한 케이크 유형이 표시됩니다. 기억하시나요?

이는 수식에 두 개의 인수를 추가하여 작동합니다.

5. 필터링할 필드("간식")
6. (Snack.Selected)와 동일한 값만 선택하십시오.

따라서 Main에서 데이터를 변경하면 Types 열이 자동으로 업데이트됩니다!

5단계: 유효성 검사 마무리

마무리하려면 Snack.Choices및 Type.Choices이름을 만들어 데이터 유효성 검사를 마무리하겠습니다. 셀 이름 B1과 C1유효성 검사 이름을 각각 지정하세요. 이 작은 상자에 입력하여 셀 이름을 지정할 수도 있습니다.

셀에 B1다음 수식을 입력해야 합니다.

="Validation!"&CELL("address",B3)&":"&CELL("address",OFFSET(B$2,COUNTIF(B3:B50,"*"),0))

이것이 수행하는 작업은 선택한 스낵이 있는 Validation!$B$3:$B$6에 대한 텍스트 참조를 작성하는 것입니다. 해당 수식을 오른쪽에 복사하면 완료됩니다!

기본 시트로 돌아가서 스낵 및 유형에 대한 유효성 검사 규칙에서 이러한 셀을 참조해 보겠습니다.

셀을 선택 C2하고 kbd>Alt를 A V V눌러 데이터 유효성 검사를 만듭니다. '목록'을 선택하고 소스를 =INDIRECT(Snack.Choices). 주위에 따옴표가 없습니다.Snack.Choices

셀에서도 동일한 작업을 수행 C3하되 소스를 =INDIRECT(Type.Choices).

및 INDIRECT()값이 통합 문서의 범위에 대한 간접적(예: 텍스트) 참조이기 때문에 사용합니다 .Snack.ChoicesType.Choices

이제 유효성 검사 옵션을 사용해 보면서 모든 것이 실제로 작동하는지 확인하세요.

궁금한 점이 있으면 알려주세요!