엑셀 및 구글 스프레드시트에서 드롭다운 목록을 알파벳순으로 정렬하기

이 튜토리얼에서는 엑셀과 구글 스프레드시트에서 드롭다운 목록을 알파벳순으로 정렬하는 방법을 보여드리겠습니다.

반복적인 데이터를 엑셀에 입력할 때에는 드롭다운 목록을 생성하여 옵션을 선택하는 것이 유용할 때가 있습니다. Excel 365에서는 동적 배열 함수라는 강력한 새로운 기능을 통해 데이터를 정렬하고 목록에 고유한 데이터만 표시되도록 할 수 있습니다.

고유한 값이 알파벳 순서로 정렬되어 있는 드롭다운 목록을 얻으려면 이 새로운 함수 중 두 가지, 즉 UNIQUE함수와 SORT함수를 사용하면 됩니다. 그런 다음 데이터 유효성 검사를 사용하여 이러한 함수가 반환한 데이터를 갖고 드롭다운 목록을 만들 수 있습니다.

참고: VBA를 사용하여 알파벳순으로 정렬할 수도 있습니다.

SORT 함수

SORT 함수를 사용하면 데이터 목록을 알파벳 순서로 정렬할 수 있습니다.

데이터 오른쪽의 빈 셀에 다음 수식을 입력합니다:

=SORT(B3:B13)

Enter 키를 누르거나 확인 표시를 클릭하여 수식을 엑셀에 입력하면 선택한 범위의 정렬된 값 목록이 수식을 입력한 셀 아래에 나타납니다. 이를 “유출 범위(혹은 분할 범위 spill range)”라고 합니다

유출 범위는 선택한 범위에 포함되어 있는 모든 고유 값을 자동으로 출력해줍니다. 수식 입력창을 보면 이 유출 범위에서는 수식이 회색으로 표시되어 있는데 이는 수식이 동적 배열 수식이기 때문에 그렇습니다. 예를 들어 G2셀의 수식을 삭제하면 유출 범위도 삭제되는 것입니다. 유출 범위는 가는 파란색 선의 테두리로 식별할 수 있습니다.

UNIQUE 함수

UNIQUE 함수는 또 하나의 동적 배열 함수로 목록에서 고유한 값을 추출해줍니다.

데이터 오른쪽의 빈 셀에 다음 수식을 입력합니다:

=UNIQUE(B3:B13)

SORT 함수와 마찬가지로 ENTER 키를 누르는 즉시 UNIQUE 함수가 유출 범위로 넘어가 수식을 입력한 셀 아래의 열을 채웁니다. 목록에는 원본 범위에 있는 고유 값만 표시되며 동적 배열 수식이기 때문에 이 유출 범위에서는 수식을 수정하거나 변경할 수 없습니다.

SORT 및 UNIQUE 함수 결합하기

SORT 함수와 UNIQUE 함수를 함께 사용하면 고유한 값만 출력할 뿐만 아니라 이를 알파벳 순서로 정렬한 목록을 얻을 수 있습니다.

=SORT(UNIQUE((B3:B13)))

참고: 함수를 중첩하는 순서는 중요하지 않으며, =UNIQUE(SORT(B3:B13)) 수식을 사용할 수도 있습니다.

드롭다운 목록 만들기

이제 이 셀 범위를 사용하여 데이터 유효성 검사로 드롭다운 목록을 만들 수 있습니다.

1. 드롭다운 목록을 표시할 셀 범위를 선택한 다음 리본에서 데이터 > 데이터 유효성 검사를 선택합니다.

2. 제한 대상 목록에서 목록을 선택한 다음 목록 원본에 대한 수식을 입력합니다.

수식 뒤에 해시태그(#)를 넣어 개별 셀의 값(예: G2)만이 아니라 전체 유출 범위가 필요하다는 것을 엑셀이 인식할 수 있도록 해야 합니다.

3. 확인을 클릭하여 선택한 범위에 정렬된 드롭다운 목록을 만듭니다.

구글 스프레드시트에서 드롭다운 목록을 알파벳순으로 정렬하기

SORT 함수 및 UNIQUE 함수는 엑셀에서와 마찬가지로 구글 스프레드시트에서도 동일한 방식으로 작동합니다.

1. 드롭다운 목록을 만들려면 드롭다운 목록이 포함될 셀 범위를 지정한 다음 메뉴에서 데이터 확인을 선택합니다.

2. 위와 같은 선택으로 드롭다운 목록이 포함될 셀 범위는 자동으로 채워지지만 기준은 직접 입력해야 합니다. 드롭다운(범위)를 선택하고 기준이 되는 목록이 있는 전체 셀 범위를 입력합니다(예: G3: G7). 엑셀에서 사용하는 해시태그 기능이 구글 스프레드시트에는 존재하지 않습니다.

3. 완료를 클릭하여 정렬된 드롭다운 목록을 구글 스프레드시트에 삽입합니다.