Excel에서 카테고리별 제품 정보 같은 범주형 데이터를 다룰 때, 데이터 유효성 검사를 위해 해당 데이터에서 중복 없이 고유한 값만 담긴 드롭다운 목록이 필요할 수 있습니다. 이 글에서는 Excel에서 고유한 값으로 드롭다운 목록을 만드는 방법을 단계별로 자세히 소개합니다.
Excel에서 고유 값으로 드롭다운 목록 만드는 4가지 방법
한 국가가 유럽 여러 나라로 수출하는 제품 정보를 담은 Excel 파일이 있다고 가정해 보겠습니다. 시트에는 제품명, 카테고리, 그리고 수출 대상 국가 열이 들어 있습니다. 이 데이터를 활용해 고유한 값만 포함된 드롭다운 목록을 만들어 보겠습니다. 아래 이미지는 국가 열에서 고유한 값만 추출해 완성한 드롭다운 목록의 예시입니다.
방법 1: 피벗 테이블로 고유 값 드롭다운 목록 만들기
피벗 테이블을 사용하면 별도의 수식 없이도 간단하게 고유한 값만 모은 드롭다운 목록을 만들 수 있습니다. 여기서는 카테고리 열을 기준으로 진행합니다.
1단계:
- 먼저 열 머리글을 포함해 데이터 범위의 모든 셀을 선택합니다.
- 그다음 리본 메뉴의 삽입 탭에서 피벗 테이블(PivotTable)을 클릭합니다.
- 아래와 같은 대화 상자가 나타나면 Excel이 데이터 범위를 자동으로 선택하며, 새 피벗 테이블의 기본 위치는 새 워크시트입니다.
- 확인을 클릭합니다.
- 새 워크시트가 열리고, 시트 오른쪽에 피벗 테이블 필드 창이 표시됩니다. Excel 2007/2010 버전에서는 시트 왼쪽에 나타납니다.
- 카테고리(Category) 필드를 행(Rows) 영역으로 끌어다 놓습니다.
- 마지막으로 아래와 같은 피벗 테이블이 생성됩니다. 피벗 테이블은 중복을 자동으로 정리하므로 고유한 카테고리 목록이 완성됩니다.
2단계:
- 드롭다운 메뉴를 만들 셀을 선택합니다. 예제에서는 C3 셀을 선택했습니다.
- 데이터 탭에서 데이터 유효성 검사 드롭다운을 클릭합니다.
- 목록에서 데이터 유효성 검사를 선택합니다.
- 데이터 유효성 검사 창이 열리면 제한 대상(Allow) 드롭다운에서 목록(List)을 선택합니다.
- 원본(Source) 입력란에 피벗 테이블의 고유 값 범위인 $A$4:$A$6을 입력합니다.
- 확인을 클릭합니다.
- 완료되면 C3 셀에 고유한 제품 카테고리로 구성된 드롭다운 목록이 생성됩니다.
방법 2: 중복 항목 제거 기능으로 고유 값 드롭다운 목록 만들기
또 하나 효율적인 방법은 중복 항목 제거 기능으로 남은 고유한 값만으로 드롭다운 목록을 만드는 것입니다. 이번에는 국가 열을 기준으로 진행합니다.
1단계:
- 열 머리글을 포함한 국가 열 전체를 복사(Ctrl+C)한 뒤, F열에 붙여넣기(Ctrl+V) 합니다.
- 새로 붙여넣은 국가 열의 모든 셀을 선택합니다.
- 데이터 탭에서 중복된 항목 제거(Remove Duplicates)를 클릭합니다.
- 중복된 항목 제거 창이 열리면 열(Columns) 항목에서 국가가 선택되어 있는지 확인합니다.
- 확인을 클릭합니다.
- 팝업창이 뜨며 중복 값 6개가 발견·제거되고 고유 값 4개만 남았다고 알려줍니다.
- 확인을 클릭합니다.
- 결과적으로 새 국가 열에는 고유한 국가 4개만 남게 됩니다.
2단계:
- 드롭다운 메뉴를 넣을 셀을 선택합니다. 예제에서는 F10 셀입니다.
- 데이터 탭의 데이터 유효성 검사 드롭다운을 클릭합니다.
- 데이터 유효성 검사를 선택합니다.
- 데이터 유효성 검사 창에서 제한 대상(Allow)을 목록(List)으로 설정합니다.
- 원본(Source) 입력란에 $F$5:$F$8을 입력합니다.
- 확인을 클릭합니다.
- 이제 F10 셀에 고유한 국가만 담긴 드롭다운 목록이 완성됩니다.
방법 3: UNIQUE 함수로 고유 값 드롭다운 목록 만들기
Microsoft 365를 사용 중이라면 UNIQUE 함수로 열, 범위 또는 목록에서 중복 값을 손쉽게 제거하고 고유한 값만 추출할 수 있습니다. F5 셀에 아래 수식을 입력해 보세요.
=SORT(FILTER(UNIQUE(D5:D14),UNIQUE(D5:D14)<>0))
수식 설명:
- UNIQUE 함수는 D5:D14 범위에서 고유한 값만 추출합니다.
- FILTER 함수는 국가 열의 고유 값 중 null(빈 셀)이 아닌 값만 반환합니다.
- SORT 함수는 추출된 고유 값을 알파벳순(A~Z)으로 정렬합니다.
- Enter 키를 누르면 고유한 국가 4개만 담긴 새 국가 열이 생성됩니다. 이후 앞서 살펴본 것과 동일한 절차로 데이터 유효성 검사를 적용하면 고유 국가 기반의 드롭다운 목록을 만들 수 있습니다.
방법 4: 수식으로 자동 업데이트되는 동적 드롭다운 목록 만들기
가장 간편하고 실용적인 방법은 도우미 수식을 활용해 동적 드롭다운 목록을 만드는 것입니다. 이 방식은 열, 범위 또는 목록에 새 항목을 추가할 때마다 드롭다운 목록이 자동으로 업데이트됩니다.
단계:
- 먼저 위에서 설명한 것과 같은 절차로 데이터 유효성 검사 창을 열고 제한 대상(Allow)에서 목록(List)을 선택합니다.
- 차이점은 이번에는 새 국가 열의 데이터 범위를 직접 선택하지 않고, 원본(Source) 입력란에 아래 수식을 입력한다는 점입니다.
=OFFSET($F$5, 0, 0, COUNTA($F$5:$F$100), 1)
수식 설명:
- COUNTA 함수는 지정한 범위에서 비어 있지 않은 셀의 개수를 계산합니다.
- OFFSET 함수는 특정 셀 참조를 시작점으로 삼아 지정한 행과 열만큼 이동한 뒤, 지정한 높이와 너비를 가진 데이터 구간을 추출합니다. 덕분에 데이터가 늘어나면 목록 범위도 함께 늘어납니다.
- 수식을 입력한 후 확인을 클릭합니다.
- F10 셀에 고유한 국가만 포함된 드롭다운 목록이 생성됩니다.
- 이제 데이터 범위에 새 국가 이탈리아(Italy)가 담긴 행을 추가해 보세요. 드롭다운 목록이 자동으로 갱신되어 이탈리아가 새 항목으로 포함되는 것을 확인할 수 있습니다.
알아두면 좋은 팁
🎯 UNIQUE 함수는 현재 Excel 365(Microsoft 365)에서만 사용할 수 있는 전용 함수입니다. PC에 Excel 365가 없다면 이 함수는 동작하지 않으니 방법 1, 2, 4를 활용하세요.
🎯 Excel OFFSET 함수를 활용한 동적 드롭다운 목록 만들기에 대해 더 자세히 알고 싶다면 관련 문서를 참고해 보세요.
마무리
이번 글에서는 Excel에서 고유한 값으로 드롭다운 목록을 만드는 방법을 피벗 테이블, 중복 제거, UNIQUE 함수, OFFSET·COUNTA 수식까지 총 4가지 방법으로 살펴보았습니다. 상황에 맞는 방법을 선택하면 누구나 쉽게 고유 값 드롭다운 목록을 만들 수 있습니다. 이 글에 대해 궁금한 점이나 추가하고 싶은 내용이 있다면 댓글로 남겨주세요. 즐거운 하루 되세요!
함께 보면 좋은 글
- Excel에서 VLOOKUP과 드롭다운 목록 함께 활용하기
- Excel에서 드롭다운 목록 만들기(독립형 및 종속형)
- Excel 드롭다운 목록 편집하기(4가지 기본 방법)
- Excel에서 다른 시트 데이터로 드롭다운 목록 만들기(2가지 방법)
- IF 문을 활용해 Excel 드롭다운 목록 만들기