Excel 스프레드 시트에는 데이터 입력을 단순화 및 / 또는 표준화하기위한 셀 드롭 다운이 포함되는 경우가 종종 있습니다. 이 드롭 다운은 데이터 유효성 검사 기능을 사용하여 생성되어 허용 가능한 항목 목록을 지정합니다.
간단한 드롭 다운 목록을 설정하려면 데이터를 입력 할 셀을 선택한 다음 데이터 유효성 검사(데이터탭에서) 데이터 유효성 검사를 선택하고 목록(허용 : 아래에서)을 선택한 다음
이 유형의 기본 드롭 다운에서 허용 가능한 항목 목록이 지정됩니다. 데이터 검증 자체 내; 따라서 목록을 변경하려면 데이터 유효성 검사를 열고 편집해야합니다. 그러나 경험이 부족한 사용자 나 선택 목록이 긴 경우에는 어려울 수 있습니다.
다른 옵션은 스프레드 시트 내의 명명 된 범위 에 목록을 배치 한 다음 데이터 유효성 검사의 소스: 필드에 해당 범위 이름 (등호로 시작)이 표시됩니다 (그림 2 참조).
이 두 번째 방법을 사용하면 목록에서 선택 항목을 쉽게 편집 할 수 있지만 항목을 추가하거나 제거하는 데 문제가있을 수 있습니다. 명명 된 범위 (이 예제에서는 과일 선택)가 고정 된 범위의 셀 (표시된대로 $ H $ 3 : $ H $ 10)을 참조하므로 셀 H11 이하에 더 많은 선택 사항이 추가되면 드롭 다운에 표시되지 않습니다. (이러한 셀은 FruitChoices 범위에 속하지 않기 때문에).
예를 들어 Pears and Strawberries 항목이 지워지면 더 이상 드롭 다운에 나타나지 않지만 대신 드롭 다운에 2가 포함됩니다. 드롭 다운 이후 "빈"선택은 여전히 빈 셀 H9 및 H10을 포함하여 전체 FruitChoices 범위를 참조합니다.
이러한 이유로, 일반 명명 된 범위를 드롭 다운의 목록 소스로 사용하는 경우 명명 된 범위 목록에서 항목을 추가하거나 삭제하는 경우 셀을 더 많거나 적게 포함하도록 자체를 편집해야합니다.
이 문제에 대한 해결책은 동적드롭 다운 선택의 소스로 범위 이름. 동적 범위 이름은 항목이 추가되거나 제거 될 때 데이터 블록의 크기와 정확히 일치하도록 자동으로 확장되거나 축소되는 이름입니다. 이렇게하려면 고정 된 범위의 셀 주소 대신 수식을 사용하여 명명 된 범위를 정의합니다.
동적 설정 방법 Excel의 범위
일반 (정적) 범위 이름은 지정된 셀 범위를 나타냅니다 (이 예에서는 $ H $ 3 : $ H $ 10, 아래 참조).
동적 범위는 공식을 사용하여 정의됩니다 (동적 범위 이름을 사용하는 별도의 스프레드 시트에서 가져온 아래 참조) :
시작하기 전에 엑셀 예제 파일 (정렬 매크로가 비활성화되어 있음)를 다운로드해야합니다.
이 공식을 자세히 살펴 보겠습니다. 과일에 대한 선택은 제목 바로 아래 (과일) 셀 블록에 있습니다. 이 제목에도 FruitsHeading:
이름이 할당됩니다. 과일 선택은 다음과 같습니다.
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
FruitsHeading은 목록의 첫 번째 항목 위의 한 행인 제목을 나타냅니다. 숫자 20 (수식에서 두 번 사용됨)은 목록의 최대 크기 (행 수)입니다 (원하는대로 조정할 수 있음).
이 예에서는 8 개의 항목 만 있습니다. 목록에 있지만 추가 항목을 추가 할 수있는 빈 셀도 있습니다. 숫자 20은 실제 항목 수가 아니라 항목을 만들 수있는 전체 블록을 나타냅니다.
이제 어떻게 작동하는지 이해하기 위해 수식을 조각으로 나눕니다 (각 조각을 컬러 코딩). :
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
“가장 안쪽”부분은 OFFSET (FruitsHeading, 1,0,20,1)입니다. 이것은 선택이 입력 될 수있는 20 개의 셀 (과일 머리글 셀 아래)의 블록을 나타냅니다. 이 오프셋 기능은 기본적으로 다음과 같이 말합니다. FruitsHeading셀에서 시작하여 1 행 및 0 열 이상으로 이동 한 다음 길이가 20 행, 폭이 1 개인 영역을 선택합니다. 과일 선택을 입력 할 수있는 20 행 블록을 제공합니다.
다음 공식은 ISBLANK함수입니다.
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
여기서, 위에서 설명한 OFFSET 기능이 "위의"항목으로 대체되었습니다 (읽기 쉽도록). 그러나 ISBLANK 함수는 OFFSET 함수가 정의한 20 행 범위의 셀에서 작동합니다.
ISBLANK는 20 개의 TRUE 및 FALSE 값 세트를 생성하여 20- OFFSET 함수가 참조하는 행 범위가 비어 있거나 비어 있습니다. 이 예에서 처음 8 개 셀이 비어 있지 않고 마지막 12 개 값이 참이므로 세트의 처음 8 개 값은 FALSE입니다.
다음 수식은 INDEX 함수입니다.
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
다시, "위의"는 위에서 설명한 ISBLANK 및 OFFSET 기능을 나타냅니다. INDEX 함수는 ISBLANK 함수에 의해 생성 된 20 개의 TRUE / FALSE 값을 포함하는 배열을 반환합니다.
INDEX은 일반적으로 특정 값 (또는 값 범위)을 선택하는 데 사용됩니다. 특정 블록과 행을 지정하여 데이터 블록. 그러나 행 및 열 입력을 0으로 설정하면 (여기에서와 같이) INDEX는 전체 데이터 블록을 포함하는 배열을 반환합니다.
다음 수식은 MATCH 함수입니다.
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
MATCH함수는 INDEX 함수가 반환 한 배열 내에서 첫 번째 TRUE 값의 위치를 반환합니다. 목록의 처음 8 개 항목은 공백이 아니므로 배열의 처음 8 개 값은 FALSE가되고 9 번째 값은 참이됩니다 (범위의 9 th행이 비어 있기 때문에).
MATCH 함수는 9의 값을 반환합니다. 그러나이 경우 목록에 몇 개의 항목이 있는지 알고 싶기 때문에 공식은 MATCH 값에서 1을 뺍니다 (마지막 항목의 위치를 제공함). 결과적으로 MATCH (TRUE, 상기, 0) -1은 8의 값을 반환합니다.
다음 수식은 IFERROR 함수입니다 :
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
IFERROR 함수는 지정된 첫 번째 값으로 인해 오류가 발생하면 대체 값을 반환합니다. 이 함수는 셀의 전체 블록 (20 개 행 모두)에 항목이 채워지면 MATCH 함수가 오류를 반환하기 때문에 포함됩니다.
MATCH 함수가 찾도록 지시하기 때문입니다. 첫 번째 TRUE 값 (ISBLANK 함수의 값 배열에 있음)이지만 셀 중 NONE이 비어 있으면 전체 배열이 FALSE 값으로 채워집니다. MATCH가 검색중인 배열에서 목표 값 (TRUE)을 찾을 수 없으면 오류를 리턴합니다.
전체 목록이 가득 차면 (따라서 MATCH가 오류를 리턴 함) IFERROR 함수는 대신 값 20을 반환합니다 (목록에 20 개의 항목이 있어야 함을 알고 있음).
마지막으로 OFFSET (FruitsHeading, 1,0, 위의 1)은 Fruit FruitsHeading 셀에서 시작하여 1 행 아래로 0 열 이상으로 이동 한 다음 목록에 항목이 있고 길이가 1 열인 경우 행 수가 많은 영역을 선택하십시오. 따라서 전체 수식이 함께 실제 항목 만 포함 된 범위를 반환합니다 (첫 번째 빈 셀까지).
이 수식을 사용하여 드롭 다운의 소스 범위를 정의하면 자유롭게 편집 할 수 있습니다. 목록 (남은 항목이 맨 위 셀에서 시작하여 인접하는 한 항목 추가 또는 제거)과 드롭 다운에는 항상 현재 목록이 반영됩니다 (그림 6 참조).
여기에 사용 된 예제 파일 (동적 목록) 이 포함되어 있으며이 웹 사이트에서 다운로드 할 수 있습니다. 그러나 WordPress는 매크로가 포함 된 Excel 책을 좋아하지 않기 때문에 매크로가 작동하지 않습니다.
목록 블록의 행 수를 지정하는 대신 목록 블록에 해당 매크로를 지정할 수 있습니다. 자체 범위 이름을 수정 한 수식에 사용할 수 있습니다. 예제 파일에서 두 번째 목록 (이름)은이 방법을 사용합니다. 여기에서 전체 목록 블록 (예 : "NAMES"제목 아래, 예제 파일의 40 행)에 NameBlock의 범위 이름이 할당됩니다. NamesList를 정의하는 대체 공식은 다음과 같습니다.
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
여기서 NamesBlock은 OFFSET (FruitsHeading, 1,0,20,1) 및 ROWS (NamesBlock)은 이전 수식에서 20 (행 수)을 대체합니다.
따라서 쉽게 편집 할 수있는 드롭 다운 목록 (경험이없는 다른 사용자가 포함) 다이나믹 레인지 이름을 사용해보십시오! 이 기사는 드롭 다운 목록에 중점을 두었지만 동적 범위 이름은 크기가 다양 할 수있는 범위 또는 목록을 참조해야하는 모든 위치에서 사용할 수 있습니다. 즐기세요!