대량의 데이터를 다룰 때 고유 값(Unique Value) 필터링은 필수적인 작업입니다. Excel은 중복 데이터를 제거하거나 고유한 값만 추출할 수 있는 다양한 기능을 제공합니다. 이 글에서는 샘플 데이터셋을 활용해 고유 값을 추출하는 8가지 방법을 단계별로 소개합니다.
예시로 주문 날짜(Order Date), 카테고리(Category), 제품(Product) 세 개의 열로 구성된 간단한 데이터셋을 사용합니다. 우리의 목표는 전체 데이터셋에서 주문된 제품의 고유 목록을 추출하는 것입니다.
Excel 워크북 다운로드
Excel에서 고유 값을 필터링하는 8가지 쉬운 방법
방법 1: '중복된 항목 제거' 기능으로 고유 값 추출하기
방대한 데이터셋을 파악하려면 때때로 중복 항목을 제거해야 합니다. Excel의 데이터 탭에는 데이터셋에서 중복 항목을 손쉽게 삭제할 수 있는 '중복된 항목 제거' 기능이 있습니다. 여기서는 카테고리와 제품 열에서 중복을 제거해 보겠습니다.
1단계: 범위(카테고리와 제품)를 선택한 후 데이터 탭으로 이동하여 데이터 도구 섹션에서 '중복된 항목 제거'를 선택합니다.
2단계: '중복된 항목 제거' 창이 나타나면 아래와 같이 설정합니다.
- 모든 열에 체크 표시
- '내 데이터에 머리글 포함' 옵션 체크
- 확인 클릭
3단계: 확인 대화상자가 나타나며 중복 값 8개가 발견되어 제거되었고, 고유 값 7개가 남았다는 메시지가 표시됩니다. 확인을 클릭합니다.
위 과정을 모두 완료하면 아래 이미지처럼 결과가 나타납니다.
⚠️ 참고: 이 방법은 원본 데이터에서 중복 항목을 영구적으로 삭제합니다. 원본을 보존해야 한다면 복사본을 만들어 진행하세요.
방법 2: 조건부 서식으로 고유 값 강조하기
고유 값을 필터링하는 또 다른 방법은 조건부 서식을 활용하는 것입니다. Excel의 조건부 서식은 다양한 기준으로 셀 서식을 지정할 수 있는데, 여기서는 수식을 사용해 제품(Product) 열의 셀에 서식을 적용합니다. 두 가지 접근 방식이 있습니다. 하나는 고유 값만 색상으로 강조하는 것이고, 다른 하나는 중복 값을 숨기는 것입니다.
2.1. 조건부 서식으로 고유 값 강조하기
수식을 활용한 조건부 서식으로 고유 항목만 강조해 보겠습니다.
1단계: 범위(예: Product 1)를 선택하고 홈 탭 → 스타일 섹션의 조건부 서식 → 새 규칙을 선택합니다.
2단계: '새 서식 규칙' 창이 나타나면 다음과 같이 설정합니다.
- 규칙 유형 선택에서 '수식을 사용하여 서식을 지정할 셀 결정' 선택
- 규칙 설명 편집 영역에 아래 수식 입력
=COUNTIF($D$5:D5,D5)=1
이 수식은 D열의 각 셀이 고유(즉, 개수가 1)한지 확인합니다. 조건과 일치하면 TRUE를 반환하고 해당 셀에 색상 서식을 적용합니다. 설정 후 서식 버튼을 클릭합니다.
3단계: '셀 서식' 창이 나타나면 글꼴 탭에서 원하는 색상을 선택하고 확인을 클릭합니다.
4단계: 다시 '새 서식 규칙' 창으로 돌아오면 미리 보기에서 고유 항목에 적용될 서식을 확인할 수 있습니다. 확인을 클릭하면 완료됩니다.
최종 결과로 고유 항목이 아래 그림과 같이 원하는 색상으로 강조 표시됩니다.
2.2. 조건부 서식으로 중복 값 숨기기
고유 값을 건드리지 않고 중복 값만 숨기고 싶다면, 위에서 사용한 수식의 조건을 반대로 바꾸면 됩니다. 즉, 개수가 1보다 큰 경우를 찾아 흰색 글꼴을 적용하면 중복 값이 배경과 같아져 사라진 것처럼 보입니다.
1단계: 방법 2.1의 1~2단계를 반복하되, 아래 수식으로 변경합니다.
=COUNTIF($D$5:D5,D5)>1
이 수식은 D열의 각 셀이 중복(즉, 개수가 1보다 큼)인지 확인합니다. 조건에 일치하면 TRUE를 반환하고 해당 셀에 서식(숨김 처리)을 적용합니다. 서식 버튼을 클릭합니다.
2단계: '셀 서식' 창에서 글꼴 색을 흰색으로 선택한 후 확인을 클릭합니다.
3단계: '새 서식 규칙' 창으로 돌아오면 글꼴 색이 흰색이라 미리 보기가 흐릿하게 보이는 것이 정상입니다. 확인을 클릭합니다.
모든 단계를 완료하면 아래 이미지처럼 중복 값이 숨겨진 상태로 표시됩니다.
💡 팁: 반드시 글꼴 색을 흰색으로 선택해야 중복 항목이 제대로 숨겨집니다.
방법 3: '고급 필터' 기능으로 고유 값 추출하기
앞선 방법들은 데이터셋에서 항목을 삭제하거나 수정하는 방식이었습니다. 하지만 원본 데이터를 변경할 수 없는 상황도 있습니다. 이럴 때 고급 필터 옵션을 사용하면 원본을 그대로 유지하면서 원하는 위치에 고유 값만 추출할 수 있습니다.
1단계: 범위(예: 제품 열)를 선택한 후 데이터 탭 → 정렬 및 필터 섹션의 고급을 클릭합니다.
2단계: '고급 필터' 창에서 다음과 같이 설정합니다.
- 작업에서 '다른 위치에 복사' 선택 — '제자리에 필터링'도 가능하지만, 원본 데이터를 보존하기 위해 후자를 권장합니다.
- '복사 위치'에 출력 위치 지정 (예: F4)
- '동일한 레코드는 하나만' 옵션 체크
- 확인 클릭
확인을 누르면 지정한 위치에 고유 값이 추출됩니다.
방법 4: UNIQUE 함수로 고유 값 추출하기
다른 열에 고유 값을 표시하려면 UNIQUE 함수를 사용하는 것이 가장 간편합니다. UNIQUE 함수는 범위 또는 배열에서 고유 항목 목록을 자동으로 추출합니다. 함수 구문은 다음과 같습니다.
UNIQUE(array, [by_col], [exactly_once])
- array: 고유 값을 추출할 범위 또는 배열 (필수)
- [by_col]: 비교 방향 설정. 행 기준 비교 = FALSE(기본값), 열 기준 비교 = TRUE (선택)
- [exactly_once]: 한 번만 나타나는 값만 추출 = TRUE, 모든 고유 값 추출 = FALSE(기본값) (선택)
1단계: 빈 셀(예: E5)에 아래 수식을 입력합니다.
=UNIQUE(D5:D19)
2단계: ENTER 키를 누르면 모든 고유 항목이 한 번에 표시됩니다.
UNIQUE 함수는 결과를 자동으로 인접 셀에 확장(Spill)하여 표시합니다. 단, 이 함수는 Excel 365 버전에서만 사용할 수 있습니다.
방법 5: UNIQUE + FILTER 함수 조합으로 조건별 고유 값 추출하기
방법 4에서는 단순히 고유 값을 추출했습니다. 그렇다면 특정 조건에 맞는 고유 값만 얻으려면 어떻게 할까요? 예를 들어, 특정 카테고리에 속한 고유한 제품 이름만 추출한다고 가정해 봅시다.
여기서는 Bars 카테고리(셀 E4)에 해당하는 고유 제품 이름을 추출해 보겠습니다.
1단계: 아무 셀(예: E5)에 아래 수식을 입력합니다.
=UNIQUE(FILTER(D5:D19,C5:C19=E4))
이 수식은 C5:C19 범위가 E4와 일치하는 행을 기준으로 D5:D19 범위를 필터링한 뒤, 그중 고유 값만 반환하도록 지시합니다.
2단계: ENTER 키를 누르면 Bars 카테고리에 속한 제품들이 아래 스크린샷과 같이 표시됩니다.
원하는 어떤 카테고리든 지정해 고유 제품을 추출할 수 있어, 대용량 판매 데이터를 관리할 때 매우 유용합니다. FILTER 함수 역시 Excel 365에서만 사용 가능합니다.
방법 6: MATCH + INDEX 함수 조합(배열 수식)으로 고유 값 추출하기
간단한 설명을 위해 공백이나 대소문자 구분 항목이 없는 데이터셋을 사용했습니다. 그렇다면 공백이 있거나 대소문자가 섞인 데이터셋은 어떻게 처리할까요? 해결 방법을 살펴보기 전에, 먼저 공백 없는 범위(예: Product 1)를 MATCH와 INDEX 함수 조합으로 필터링해 보겠습니다.
6.1. 공백 없는 범위에서 고유 값 추출하기
1단계: 셀 G5에 아래 수식을 입력합니다.
=IFERROR(INDEX($D$5:$D$19, MATCH(0, COUNTIF($G$4:G4, $D$5:$D$19), 0)),"")
수식의 작동 원리를 단계별로 살펴보면 다음과 같습니다.
- COUNTIF($G$4:G4, $D$5:$D$19): $D$5:$D$19 범위의 값이 $G$4:G4(이미 추출된 값)에 존재하면 1, 아니면 0을 반환합니다.
- MATCH(0, COUNTIF(...), 0): 아직 추출되지 않은 값(첫 번째 0)의 상대 위치를 찾습니다.
- INDEX($D$5:$D$19, ...): 해당 위치의 실제 셀 값을 반환합니다.
- IFERROR: 오류 발생 시 빈 칸(" ")을 표시해 에러 메시지를 숨깁니다.
2단계: 이 수식은 배열 수식이므로 CTRL+SHIFT+ENTER를 함께 눌러야 합니다. Product 1 범위의 모든 고유 항목이 표시됩니다.
6.2. 공백이 있는 범위에서 고유 값 추출하기
이번에는 Product 2 범위처럼 여러 개의 빈 셀이 존재하는 경우입니다. 공백을 무시하고 고유 값만 추출하려면 ISBLANK 함수를 추가해야 합니다.
1단계: 셀 H5에 아래 수식을 붙여넣습니다.
=IFERROR(INDEX($E$5:$E$19, MATCH(0,IF(ISBLANK($E$5:$E$19),1,COUNTIF($H$4:H4, $E$5:$E$19)), 0)),"")
이 수식은 6.1절과 동일하게 작동하지만, 추가된 IF 함수와 ISBLANK 논리 검사 덕분에 범위 내 빈 셀을 무시할 수 있습니다.
2단계: CTRL+SHIFT+ENTER를 누르면 수식이 빈 셀을 건너뛰고 모든 고유 항목을 가져옵니다.
6.3. 대소문자가 구분되는 범위에서 고유 값 추출하기
데이터셋에 대소문자가 섞인 항목이 있다면, FREQUENCY, TRANSPOSE, ROW 함수를 함께 사용해야 정확한 고유 값을 걸러낼 수 있습니다.
1단계: 셀 I5에 아래 수식을 적용합니다.
=INDEX($F$5:$F$19, MATCH(0, FREQUENCY(IF(EXACT($F$5:$F$19, TRANSPOSE($I$4:I4)), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19)), ""), MATCH(ROW($F$5:$F$19), ROW($F$5:$F$19))), 0))
수식의 구성 요소를 살펴보면 다음과 같습니다.
- TRANSPOSE($I$4:I4): 이전에 추출한 값들을 배열 형태로 변환합니다. 예를 들어 TRANSPOSE({"unique values (case sensitive)";"Whole Wheat"})는 {"unique values (case sensitive)","Whole Wheat"}가 됩니다.
- EXACT($F$5:$F$19, TRANSPOSE($I$4:I4)): 문자열이 대소문자까지 완전히 동일한지 검사합니다.
- IF(EXACT(...), MATCH(ROW(...), ROW(...))): 조건이 TRUE일 때 문자열의 상대 위치를 반환합니다.
- FREQUENCY(IF(...)): 문자열이 배열에 몇 번 나타나는지 계산합니다.
- MATCH(0, FREQUENCY(...), 0): 배열에서 처음으로 False(비어 있음)인 값을 찾습니다.
- INDEX($F$5:$F$19, ...): 최종적으로 배열에서 고유 값을 반환합니다.
2단계: CTRL+SHIFT+ENTER를 함께 누르면 대소문자가 구분된 고유 값들이 셀에 표시됩니다.
모든 종류의 항목이 각각의 열에 정렬되면 전체 데이터셋은 아래 이미지처럼 완성됩니다.
제품 데이터 유형에 맞게 수식을 응용하면 어떤 상황에서도 대응할 수 있습니다.
방법 7: VBA 매크로 코드로 고유 값 추출하기
제품 열에서 고유 값만 추출하고 싶다면 VBA 매크로 코드를 활용할 수도 있습니다. 선택 영역의 값을 할당한 뒤 반복문을 통해 모든 중복을 제거하는 코드를 작성하는 방식입니다.
VBA 매크로를 적용하기 전에, 아래와 같은 유형의 데이터셋이 준비되어 있고 고유 값을 추출할 범위를 선택했는지 확인합니다.
1단계: 매크로 코드를 작성하려면 ALT+F11을 눌러 Microsoft Visual Basic 창을 엽니다. 그런 다음 도구 모음에서 삽입 탭 → 모듈을 선택합니다.
2단계: 모듈 창이 나타나면 아래 코드를 붙여넣습니다.
Sub Unique_Values()
Dim Range As Variant, prdct As Variant
Dim mrf As Object
Dim i As Long
Set mrf = CreateObject("scripting.dictionary")
Range = Selection
For i = 1 To UBound(Range)
mrf(Range(i, 1) & "") = ""
Next
prdct = mrf.keys
Selection.ClearContents
Selection(1, 1).Resize(mrf.Count, 1) = Application.Transpose(prdct)
End Sub코드의 작동 원리는 다음과 같습니다.
- 변수 선언 후 mrf = CreateObject("scripting.dictionary")로 딕셔너리 객체를 생성해 mrf에 할당합니다.
- Selection을 Range 변수에 할당합니다.
- For 루프가 각 셀을 순회하며 중복 여부를 검사합니다.
- 이후 코드가 선택 영역을 지우고 고유 값만 다시 표시합니다.
3단계: F5 키를 눌러 매크로를 실행한 후 워크시트로 돌아가면, 선택 영역의 모든 고유 값이 추출된 것을 확인할 수 있습니다.
방법 8: 피벗 테이블로 고유 값 추출하기
피벗 테이블(Pivot Table)은 선택한 셀에서 고유 항목 목록을 손쉽게 추출할 수 있는 강력한 도구입니다. Excel에서 피벗 테이블을 삽입하는 것만으로 원하는 결과를 얻을 수 있습니다.
1단계: 원하는 범위(예: 제품)를 선택한 후 삽입 탭 → 표 섹션의 피벗 테이블을 클릭합니다.
2단계: '피벗 테이블 생성' 창에서 다음과 같이 설정합니다.
- 범위(예: D4:D19)가 자동으로 선택됩니다.
- '피벗 테이블 위치' 옵션에서 '기존 워크시트'를 선택합니다.
- 확인을 클릭합니다.
3단계: '피벗 테이블 필드' 창이 나타나면 필드 목록에서 제품(Product) 필드 하나만 체크합니다. 그러면 아래 그림과 같이 고유 제품 목록이 자동으로 생성됩니다.
마치며
고유 값 필터링은 Excel에서 가장 흔히 수행되는 작업 중 하나입니다. 이 글에서는 UNIQUE, FILTER, MATCH, INDEX 같은 다양한 함수와 VBA 매크로, 그리고 내장 기능들을 활용해 고유 값을 추출하는 방법을 살펴보았습니다. 함수를 사용하면 원본 데이터는 그대로 유지되고 결과값만 다른 열에 표시되지만, '중복된 항목 제거' 같은 기능은 원본 데이터에서 항목을 영구적으로 삭제합니다. 따라서 작업 전에 원본 데이터 보존 여부를 반드시 고려해 적절한 방법을 선택하시기 바랍니다. 이 글이 데이터셋에서 중복을 다루고 고유 값을 추출하는 데 도움이 되기를 바랍니다. 추가 질문이나 의견이 있다면 댓글로 남겨주세요. 다음 글에서 다시 만나겠습니다.
함께 읽으면 좋은 글
- Excel에서 사용자 지정 필터 수행하는 방법 (5가지)
- Excel에서 색상별로 필터링하는 방법 (2가지 예시)
- Excel에서 수식이 포함된 셀 필터링하는 방법 (2가지)
- Excel 필터에서 여러 항목 검색하는 방법 (2가지)