Excel 스프레드시트에서는 데이터 입력을 간편하게 하고 표준화하기 위해 셀 드롭다운을 자주 사용합니다. 이러한 드롭다운은 데이터 유효성 검사 기능을 활용해 허용되는 항목 목록을 지정하는 방식으로 만들 수 있습니다.
기본 드롭다운 목록 만들기
간단한 드롭다운 목록을 설정하려면 데이터를 입력할 셀을 선택한 후 데이터 탭에서 데이터 유효성 검사를 클릭하고, 제한 대상에서 목록을 선택한 뒤, 원본 입력란에 목록 항목을 쉼표로 구분하여 입력하면 됩니다(그림 1 참조).
이러한 기본 방식에서는 허용 항목 목록이 데이터 유효성 검사 설정 자체에 포함되어 있기 때문에, 목록을 수정하려면 설정을 다시 열어 편집해야 합니다. Excel에 익숙하지 않은 사용자에게는 다소 어려울 수 있고, 선택 항목이 많은 경우에도 번거롭습니다.
명명된 범위를 사용하는 방법과 한계
또 다른 방법은 목록을 스프레드시트 내의 명명된 범위에 두고, 데이터 유효성 검사의 원본 입력란에 해당 범위 이름(앞에 등호 기호를 붙여)을 지정하는 것입니다(그림 2 참조).
이 방법은 목록 항목을 더 쉽게 편집할 수 있지만, 항목을 추가하거나 삭제할 때는 문제가 발생할 수 있습니다. 명명된 범위(예제의 FruitChoices)는 고정된 셀 범위($H$3:$H$10)를 참조하기 때문에, H11 이하 셀에 새 항목을 추가해도 해당 셀은 FruitChoices 범위에 포함되지 않아 드롭다운에 나타나지 않습니다.
마찬가지로, 예를 들어 '배(Pears)'와 '딸기(Strawberries)' 항목을 지우면 드롭다운에서는 사라지지만, 드롭다운이 여전히 빈 셀 H9와 H10을 포함한 전체 FruitChoices 범위를 참조하기 때문에 두 개의 "빈" 항목이 대신 표시됩니다.
이러한 이유로, 일반적인 명명된 범위를 드롭다운 원본으로 사용할 때는 항목을 추가하거나 삭제할 때마다 범위 자체를 편집하여 포함되는 셀 수를 조정해야 합니다.
이 문제의 해결책은 동적 범위 이름을 드롭다운 원본으로 사용하는 것입니다. 동적 범위 이름은 항목이 추가되거나 제거될 때 데이터 블록의 크기에 정확히 맞춰 자동으로 확장(또는 축소)되는 범위입니다. 이를 위해서는 고정된 셀 주소 범위 대신 수식을 사용하여 범위를 정의합니다.
Excel에서 동적 범위 설정 방법
일반적인(정적) 범위 이름은 지정된 셀 범위를 참조합니다(아래 예제에서는 $H$3:$H$10).
반면 동적 범위는 수식을 사용해 정의됩니다(아래는 동적 범위 이름을 사용하는 별도의 스프레드시트에서 가져온 예제입니다).
시작하기 전에 예제 Excel 파일을 다운로드하세요(정렬 매크로는 비활성화되어 있습니다).
이 수식을 자세히 살펴보겠습니다. 과일 선택 항목은 제목(FRUITS) 바로 아래 셀 블록에 있으며, 이 제목에도 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 셀 아래)을 참조합니다. 즉, FruitsHeading 셀에서 시작해 1행 아래, 0열 옆으로 이동한 후 20행 길이 × 1열 너비의 영역을 선택하라는 의미입니다.
다음 조각은 ISBLANK 함수입니다.
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(위의 OFFSET),0,0),0)-1,20),1)
ISBLANK 함수는 OFFSET 함수가 정의한 20행 셀 범위에 대해 작동하며, 각 셀이 비어 있는지 여부를 나타내는 20개의 TRUE/FALSE 값 집합을 생성합니다. 이 예제에서 처음 8개 셀은 비어 있지 않으므로 처음 8개 값은 FALSE, 마지막 12개 값은 TRUE가 됩니다.
다음 조각은 INDEX 함수입니다.
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(위의 ISBLANK,0,0),0)-1,20),1)
INDEX는 일반적으로 데이터 블록에서 특정 행과 열을 지정해 값을 선택하는 데 사용되지만, 행과 열 입력을 0으로 설정하면 전체 데이터 블록을 포함하는 배열을 반환합니다. 여기서는 ISBLANK가 만든 20개의 TRUE/FALSE 값 배열을 반환하는 역할을 합니다.
다음 조각은 MATCH 함수입니다.
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,위의 배열,0)-1,20),1)
MATCH 함수는 배열 내에서 첫 번째 TRUE 값의 위치를 반환합니다. 처음 8개 항목이 비어 있지 않으므로 아홉 번째 값이 TRUE가 되어 MATCH는 9를 반환합니다. 그러나 우리가 알고 싶은 것은 항목 수이므로, 수식은 여기서 1을 빼 최종적으로 8을 반환합니다.
다음 조각은 IFERROR 함수입니다.
=OFFSET(FruitsHeading,1,0,IFERROR(위의 값,20),1)
IFERROR 함수는 첫 번째 값이 오류일 경우 대체 값을 반환합니다. 목록이 가득 차면(20행 모두 채워지면) 배열에 TRUE가 존재하지 않아 MATCH가 오류를 반환하기 때문에, 이 경우 IFERROR는 목록에 20개 항목이 있음을 알고 20을 대신 반환합니다.
마지막으로 OFFSET(FruitsHeading,1,0,위의 값,1)은 실제로 원하는 범위를 반환합니다. FruitsHeading 셀에서 1행 아래로 이동한 후, 목록의 항목 수만큼의 행 길이(1열 너비)의 영역을 선택하는 것입니다. 따라서 전체 수식은 첫 번째 빈 셀까지, 즉 실제 항목만 포함하는 범위를 반환합니다.
이 수식으로 드롭다운 원본 범위를 정의하면 목록을 자유롭게 편집할 수 있으며(나머지 항목이 맨 위 셀에서 시작해 연속되어 있는 한 추가·삭제 가능), 드롭다운은 항상 현재 목록을 반영합니다(그림 6 참조).
여기서 사용된 예제 파일(Dynamic Lists)은 이 사이트에서 다운로드할 수 있습니다. 다만 WordPress가 매크로가 포함된 Excel 통합 문서를 허용하지 않아 매크로는 작동하지 않습니다.
대체 방법: 명명된 블록 활용
목록 블록의 행 수를 직접 지정하는 대신, 목록 블록 자체에 범위 이름을 지정하고 이를 수정된 수식에 사용할 수도 있습니다. 예제 파일의 두 번째 목록(Names)이 이 방법을 사용합니다. 여기서는 "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)를 대체합니다.
쉽게 편집할 수 있는 드롭다운 목록(Excel에 익숙하지 않은 다른 사용자가 편집하는 경우 포함)을 만들고 싶다면 동적 범위 이름을 활용해 보세요. 이 글에서는 드롭다운 목록에 초점을 맞췄지만, 동적 범위 이름은 크기가 변할 수 있는 범위나 목록을 참조해야 하는 모든 상황에서 유용하게 사용할 수 있습니다.