엑셀에서 고유값(unique value)을 추출하는 방법을 찾고 계신다면 정확히 맞는 곳에 오셨습니다. 엑셀에서 값의 중복은 하나의 열 안에서만 발생할 수도 있고, 여러 열과 행에 걸쳐 나타날 수도 있습니다. 정확한 데이터 분석과 데이터 통합을 위해서는 중복을 제거해야 하는 경우가 많고, 반대로 중복값을 삭제하지 않고 단순히 확인하거나 세어야 하는 상황도 있습니다.
이 글에서는 엑셀에서 고유값을 손쉽게 추출하고 중복값을 걸러낼 수 있는 5가지 대표적인 방법을 단계별로 소개합니다.
엑셀에서 고유값을 추출하는 5가지 쉬운 방법
이번 튜토리얼에서는 고성능 액체 크로마토그래피(HPLC) 분석 데이터를 예제로 사용합니다. HPLC는 혼합물 속 화합물을 분리·식별·정량하는 데 널리 쓰이는 분석화학 기법으로, 연구 조교마다 서로 다른 HPLC 컬럼을 담당하고 있다고 가정해 보겠습니다. 각 열의 중복 여부와 고유값을 다양한 방식으로 분석해 볼 것입니다.
아래는 예제로 사용되는 원본 데이터입니다. 총 200행 5열 구성의 데이터이며, 공간상 일부 행만 표시했습니다.
그럼 지금부터 아주 간단한 단계로 고유값을 추출할 수 있는 다섯 가지 방법을 하나씩 살펴보겠습니다.
1. 고급 필터(Advanced Filter) 사용하기
고급 필터를 사용하면 지정한 조건에 맞는 데이터를 손쉽게 추출할 수 있으며, 고유값만 골라내는 데도 매우 유용합니다.
진행 순서:
- 먼저 빈 시트를 만들고 이름을 “Using Advanced Filter”로 지정합니다.
- 리본 메뉴에서 데이터 > 정렬 및 필터 > 고급을 클릭합니다.
- 고급 필터 대화상자가 나타나면 “다른 위치에 복사”를 선택합니다.
- “복사 위치”에는 같은 시트의 $B$4 셀을 지정하고, 아래 그림처럼 “동일한 레코드는 하나만” 옵션에 체크합니다.
- “목록 범위”에는 원본 데이터 시트(Dataset)로 이동해 $B$4:$B$204, 즉 대학·연구소 이름 열 전체를 선택합니다.
- 확인을 누릅니다.
- 그러면 Using Advanced Filter 시트에 대학·연구소 이름의 고유값 목록이 추출됩니다.
- 같은 방식으로 원본 데이터의 나머지 열들에도 동일한 절차를 적용합니다.
- 예를 들어 HPLC 컬럼 열의 고유값을 구하려면 아래 그림처럼 고급 필터 설정을 수정하면 됩니다.
최종 결과물은 다음과 같습니다.
2. 중복 항목 제거 기능으로 고유값 찾기
데이터 탭의 중복된 항목 제거(Remove Duplicates) 기능을 이용해 중복값을 삭제하고 고유값만 남길 수도 있습니다. 단, 이 기능은 데이터를 영구적으로 삭제한다는 점을 꼭 기억하세요.
진행 순서:
- 먼저 원본 데이터 시트의 사본을 만듭니다. 원본은 항상 그대로 보관하는 것이 안전합니다. 사본 시트 이름은 “Removing Duplicates to Get Unique Values”로 지정하겠습니다.
- 범위를 선택한 뒤 데이터 > 데이터 도구 > 중복된 항목 제거를 클릭합니다.
- 중복된 항목 제거 대화상자가 열리면 “데이터에 머리글 포함” 체크박스가 선택되어 있는지 확인하고, 중복을 제거할 열을 지정하거나 모든 열을 선택한 상태로 둡니다.
- 특정 열 하나만 기준으로 중복을 제거하면, 앞서 고급 필터 예제와 동일한 결과를 얻을 수 있습니다.
- 예를 들어 아래와 같이 대학·연구소 이름 열만 선택해 보겠습니다.
- 실행하면 대화상자가 뜨면서 대학·연구소 이름 열 기준으로 중복값 171개가 제거되고 고유값 6개가 남았다고 알려줍니다.
- 이는 고급 필터의 결과를 그대로 확인해 주며, 해당 열에 중복값이 얼마나 많았는지까지 알려준다는 점에서 더 유용합니다.
- 이번에는 모든 열에 체크한 상태로 중복을 제거해 보겠습니다.
- 그러면 Excel은 중복값 3개, 고유값 197개라는 결과를 반환합니다. 즉, 대학·연구소 이름, HPLC 컬럼, HPLC 컬럼이 감지 가능한 화합물(공급업체), HPLC 컬럼이 실제로 감지하는 화합물(특정 실험실), 연구 조교 다섯 열의 값이 모두 동일한 행이 데이터셋에 3개 있었다는 의미입니다.
- 추가로, 연구 조교 열을 제외한 나머지 네 열을 기준으로 중복이 몇 건인지도 확인해 보겠습니다.
- 그 결과 아래 그림처럼 중복값 22개가 제거되고 고유값 178개가 남았습니다.
- 마지막으로 확인을 누르면 고유값만 남은 데이터를 얻을 수 있습니다.
3. 엑셀 수식 활용하기
수식을 이용해서도 고유값을 찾을 수 있습니다. 특히 EXACT 함수와 UNIQUE 함수가 매우 유용합니다.
3.1. EXACT 함수와 조건부 서식 함께 사용하기
EXACT 함수를 조건부 서식과 결합하면, 특정 값이 열 안의 어느 위치에 나타나는지 한눈에 파악할 수 있습니다.
진행 순서:
- 먼저 아래 그림처럼 “HPLC 컬럼이 실제로 감지하는 화합물(특정 실험실)” 열을 선택합니다.
- 리본 메뉴에서 홈 > 스타일 > 조건부 서식으로 이동한 뒤 드롭다운 메뉴에서 새 규칙을 선택합니다.
- “다음 수식이 참인 값의 서식 지정” 입력란에 아래 수식을 입력합니다.
=EXACT("C8 compounds",$E5)
확인을 누르기 전에 서식을 먼저 지정해야 하는 점에 유의하세요.
- 옵션에서 진한 파란색 배경과 흰색 굵은 글씨를 선택합니다.
- 모두 마쳤으면 확인을 눌러 미리 보기를 확인합니다.
마지막으로 확인을 누르면 아래와 같은 결과를 얻습니다.
3.2. UNIQUE 함수 활용하기
UNIQUE 함수를 사용하면 지정한 범위 안에서 고유값만 깔끔하게 추출할 수 있습니다. Microsoft 365 및 Excel 2021 이상 버전에서 사용할 수 있는 동적 배열 함수입니다.
여기서는 연구 조교 열의 고유값을 구해 보겠습니다.
- 먼저 H5 셀에 아래 수식을 입력합니다.
=UNIQUE(F5:F204)여기서 F5:F204는 연구 조교 열의 셀 범위입니다.
- ENTER 키를 누르면 중복이 제거된 연구 조교 목록이 바로 나타납니다.
4. 엑셀 테이블의 내장 기능 활용하기
엑셀 테이블(Table)의 내장 필터 기능을 활용해도 중복값을 걸러내거나 특정 값을 분리해 볼 수 있습니다.
진행 순서:
- 먼저 원본 데이터가 담긴 시트의 사본을 만듭니다.
- 데이터 범위 안의 셀을 하나 선택한 상태에서 키보드로 CTRL + T를 누르거나, 삽입 > 표 > 표를 클릭해 테이블로 변환합니다.
- 테이블의 필터 옵션을 이용하면 특정 값만 분리해 볼 수 있습니다. 대학·연구소 이름 열 옆의 드롭다운 화살표를 클릭하고 University ABC만 체크하면, 해당 대학의 기록만 데이터셋에서 분리되어 표시됩니다.
- 그러면 아래와 같이 고유한 대학 이름만 걸러진 결과를 얻을 수 있습니다.
- 테이블은 두 개 이상의 열로도 필터링할 수 있습니다. 예를 들어 University ABC 기록 중에서 연구 조교 Jennifer Smith가 작성한 데이터만 보고 싶다면, 먼저 위 예시처럼 University ABC를 체크한 뒤 연구 조교 열의 드롭다운 화살표를 눌러 Jennifer Smith를 선택하면 됩니다.
- 결과적으로 고유한 연구 조교 데이터를 확인할 수 있습니다.
5. 테이블과 슬라이서(Slicer) 활용하기
슬라이서를 사용하면 테이블을 시각적 버튼으로 빠르게 필터링할 수 있습니다. 슬라이서로 고유값을 추출하는 절차는 다음과 같습니다.
진행 순서:
- 슬라이서를 삽입하려면 먼저 테이블 안의 셀을 선택한 뒤, 테이블 도구 상황별 탭의 테이블 디자인 > 도구 > 슬라이서 삽입을 클릭합니다.
- 슬라이서 삽입 대화상자에서 아래 그림처럼 “HPLC 컬럼이 감지 가능한 화합물(공급업체)” 열과 “HPLC 컬럼이 실제로 감지하는 화합물(특정 실험실)” 열을 체크합니다.
- 그러면 아래와 같이 슬라이서 두 개가 삽입됩니다.
- 세 번째로, 공급업체 슬라이서에서 C8 compounds를 선택하면 테이블이 즉시 필터링되어 해당 열에 C8 compounds가 기재된 행만 표시됩니다.
- 네 번째로, 아래 그림처럼 특정 실험실 슬라이서에서도 C8 compounds를 다시 선택합니다. 어떤 실험실이 공급업체가 명시한 사양을 실제로 준수하고 있는지 확인하기 위해 값의 반복 위치를 살펴보는 것입니다.
여러 열에서 고유값 찾기
UNIQUE 함수를 사용하면 여러 열에 걸친 고유값도 아주 쉽게 구할 수 있습니다.
- 예를 들어 B열과 C열에서 고유값을 추출해야 한다면, 먼저 E5 셀에 아래 수식을 입력합니다.
=UNIQUE(B5:C204,FALSE,TRUE)여기서 B5:C204는 대학·연구소 이름과 HPLC 컬럼 두 열의 셀 범위입니다.
- ENTER 키를 누릅니다.
그러면 아래와 같은 결과를 얻을 수 있습니다.
엑셀에서 고유값 개수 세기
COUNTIF 함수를 응용하면 열이나 행의 고유값을 직접 나열하지 않고도 그 개수를 구할 수 있습니다. COUNTIF 함수 자체는 고유값을 보여 주지 않지만, 각 값의 출현 횟수를 세는 데 활용할 수 있습니다.
- 예를 들어 C열의 고유값 개수를 알고 싶다면, G5 셀에 아래 수식을 입력합니다.
=SUMPRODUCT(1/COUNTIF(C5:C204,C5:C204))
- ENTER 키를 누르면 결과값으로 14가 표시됩니다. Microsoft 365 사용자라면 =COUNTA(UNIQUE(C5:C204)) 수식으로도 동일한 결과를 얻을 수 있습니다.
마무리
엑셀은 반복되는 값을 감지하고 중복값을 제거할 수 있는 다양한 도구를 제공합니다. 향후 데이터베이스 통합을 위해 데이터셋을 정제할 때(중복과 잉여 데이터 제거가 필요한 경우)는 여러 도구를 함께 사용해야 할 때도 있습니다. 또한 상황에 따라서는 반복값을 지우기보다 개수를 세거나 표시해 두는 것이 더 유용하기도 합니다. 이 글이 엑셀에서 고유값을 찾는 데 도움이 되기를 바랍니다. 첨부 파일에 대한 의견과 여러분만의 중복값 제거 팁, 그리고 실험실에서 HPLC 분석을 활용하고 계신다면 그 경험도 댓글로 자유롭게 공유해 주세요.
함께 보면 좋은 관련 글
- 엑셀에서 두 열을 기준으로 중복 행 제거하기 [4가지 방법]
- VLOOKUP으로 엑셀에서 중복값 찾는 방법