대용량 엑셀 데이터를 다루다 보면 드롭다운 목록을 생성한 뒤, 목록 안의 중복 값을 제거해야 하는 경우가 자주 발생합니다. 생각보다 어렵지 않으며, 몇 가지 방법만 익혀두면 작업 시간과 수고를 크게 줄일 수 있습니다. 이번 글에서는 엑셀에서 드롭다운 목록의 중복 값을 효과적으로 제거할 수 있는 4가지 실전 방법을 그림과 함께 자세히 소개합니다.
엑셀 드롭다운 목록에서 중복을 제거하는 4가지 방법
예시로, 한 국가가 유럽 3개국에 수출한 다양한 과일과 채소 정보가 담긴 대형 워크시트를 사용하겠습니다. 데이터에는 품목 이름, 품목 분류, 수출 수량, 수입국(Country)이 포함되어 있습니다. 이 데이터로 드롭다운 목록을 만들고, 키보드 단축키, 데이터 유효성 검사, 피벗 테이블, 그리고 SORT, FILTER, UNIQUE 함수 조합을 활용해 중복을 제거하는 방법을 하나씩 살펴보겠습니다.
1. 키보드 단축키로 드롭다운 목록의 중복 제거하기
키보드 단축키를 활용하면 빠르고 간편하게 드롭다운 목록을 만들고 중복까지 제거할 수 있습니다. 아래 단계를 순서대로 따라 해 보세요.
1단계:
- 먼저 아래 키를 차례대로 누릅니다.
Alt + A + V + V
2단계:
- 단축키를 누르면 데이터 유효성 대화상자가 나타납니다. 먼저 설정 탭을 선택하고, 제한 대상 상자에서 목록을 선택하세요. 이어서 원본 상자에 =$E$5:$E$46을 입력하고 확인을 클릭합니다.
- 이렇게 하면 국가(Country) 기준 드롭다운 목록이 완성됩니다. 다만 현재 목록에는 중복 값이 그대로 남아 있으므로, 이를 제거해 주어야 합니다.
3단계:
- 중복을 제거하려면 데이터 탭에서 아래 경로로 이동합니다.
데이터 → 데이터 도구 → 중복된 항목 제거
- 중복된 항목 제거 대화상자가 열리면 먼저 모두 선택 취소를 클릭한 뒤, Country 항목만 체크하고 확인을 누릅니다.
- 그러면 39개의 중복 값이 제거되고 3개의 고유 값만 남았습니다라는 메시지가 표시됩니다.
- 과정이 끝나면 아래 스크린샷처럼 중복이 제거된 깔끔한 드롭다운 목록을 확인할 수 있습니다.
2. 데이터 유효성 검사 명령으로 중복 제거하기
단축키 없이 리본 메뉴의 데이터 유효성 명령을 직접 사용하는 방법도 있습니다. 메뉴 위치에 익숙해지면 오히려 더 빠르게 작업할 수 있습니다.
단계:
- 먼저 데이터 탭에서 아래 경로로 이동합니다.
데이터 → 데이터 도구 → 데이터 유효성 → 데이터 유효성
- 데이터 유효성 대화상자가 열리면 앞선 1번 방법의 2~3단계와 동일하게 진행합니다. 완료 후 아래 스크린샷과 같은 결과를 얻을 수 있습니다.
3. 피벗 테이블을 활용해 중복 제거하기
피벗 테이블은 별도의 설정 없이도 자동으로 고유 값만 추려 줍니다. 먼저 피벗 테이블을 만든 뒤, 이를 기반으로 드롭다운 목록을 구성하는 방식입니다.
1단계:
- 먼저 삽입 리본에서 아래 경로로 이동합니다.
삽입 → 피벗 테이블 → 테이블/범위에서
- 테이블/범위에서 옵션을 클릭하면 피벗 테이블 대화상자가 나타납니다. 테이블/범위 상자에 데이터 범위인 'Pivot Table'!$B$4:$B$46을 입력하고, 기존 워크시트를 선택한 뒤 확인을 클릭하세요.
- 피벗 테이블이 생성되면 아래 스크린샷처럼 품목(Product) 필드만 선택합니다. 피벗 테이블은 중복 값을 자동으로 정리해 고유 항목만 보여 줍니다.
2단계:
- 이제 데이터 표의 H4 셀에 '드롭다운 목록'이라는 제목을 입력하고, 데이터 탭에서 아래 경로로 이동합니다.
데이터 → 데이터 도구 → 데이터 유효성 → 데이터 유효성
- 데이터 유효성 대화상자에서 설정 탭을 선택하고, 제한 대상에서 목록을 고른 뒤 원본 상자에 =$G$5:$G$18을 입력하고 확인을 클릭합니다.
- 완료하면 중복 없는 품목(Product) 이름 기반 드롭다운 목록이 완성됩니다.
4. SORT, FILTER, UNIQUE 함수 조합으로 중복 제거하기
마지막으로 SORT, FILTER, UNIQUE 함수를 결합해 중복을 제거하는 방법입니다. 이 방법은 원본 데이터를 수정하지 않고도 별도 영역에 고유 값 목록을 자동 생성할 수 있다는 장점이 있습니다. 여기서는 품목의 분류(Category)별 고유 값을 추출해 보겠습니다.
단계:
- 먼저 품목 분류(Category)가 담긴 C열에서 중복 값을 제거합니다. 아래 수식을 E5 셀에 입력하세요.
=SORT(FILTER(UNIQUE(C5:C46),UNIQUE(C5:C46)<>0))수식 설명:
- UNIQUE 함수: C5부터 C46까지의 범위에서 고유 값만 추출합니다.
- FILTER 함수: 추출된 고유 값에서 빈 값(0)을 걸러냅니다.
- SORT 함수: 최종 결과를 분류(Category) 기준으로 오름차순 정렬합니다.
- 수식을 입력한 뒤 Enter 키를 누르면 분류(Category)의 고유 값이 자동으로 표시됩니다.
- 이제 데이터 표의 F4 셀에 '분류 드롭다운 목록'이라는 제목을 추가하고, 데이터 탭에서 아래 경로로 이동합니다.
데이터 → 데이터 도구 → 데이터 유효성 → 데이터 유효성
- 데이터 유효성 대화상자에서 설정 탭을 열고, 제한 대상에서 목록을 선택한 뒤 원본 상자에 =$E$5:$E$6을 입력하고 확인을 클릭합니다.
- 이제 중복 없이 정렬된 드롭다운 목록이 완성됩니다.
알아두어야 할 사항
👉 FILTER 함수는 Excel 365 버전에서만 사용할 수 있습니다. 구버전 엑셀을 사용 중이라면 피벗 테이블이나 '중복된 항목 제거' 기능을 활용하는 것이 좋습니다.
마무리
지금까지 소개한 4가지 방법을 활용하면 엑셀 드롭다운 목록의 중복 값을 손쉽게 제거하고 더욱 생산적으로 데이터를 관리할 수 있습니다. 궁금한 점이나 추가 문의 사항이 있다면 언제든 댓글로 남겨 주세요.
함께 보면 좋은 글
- 엑셀 드롭다운 목록과 VLOOKUP 함께 활용하기
- 엑셀에서 독립형·종속형 드롭다운 목록 만드는 방법
- 선택 값에 따라 바뀌는 엑셀 드롭다운 목록 만들기
- 엑셀 드롭다운 목록 편집하는 4가지 기본 방법
- 엑셀에서 동적 종속 드롭다운 목록 만들기