엑셀 중복된 항목 제거 사용 방법 (기능, 함수, Power Query)

엑셀은 다양한 산업에서 데이터 분석을 위해서 사용됩니다.

과거 클라우드가 없던 시절, 집, 회사 등 공용 폴더에 저장된 엑셀 데이터는 수 많은 사람들이 함께 사용하면서 데이터가 중복된 경우가 있었습니다.

최근에는 클라우드가 생겨 접근이 편리해진 만큼 이러한 문제가 발생하기 더 쉬워졌습니다.

중복된 항목 제거는 특히 방대한 데이터 셋에 대해서 편리한 기능입니다.

오늘은 엑셀 중복된 항목 제거 방법을 예제와 함께 알아보겠습니다.

지난 SUMIF 포스팅에서 사용한 노트북 가격의 예시를 한 번 더 활용해볼까 합니다.

엑셀 SUMIF 함수 사용법 배우기 (명칭, 비교 연산자, 날짜 예제)

---

---

위의 엑셀 파일을 첨부해두었으니 활용하시면 되겠습니다.

중복된 항목 제거.xlsx

0.01MB

 

---

중복된 항목  찾기 및 지우기

 

중복된 데이터는 방대한 자료를 이해하기 어렵게 할 수도 있으며, 부정확한 데이터 분석 결과를 초래할 수 있습니다.

하지만 필요에 의해, 혹은 우연에 의해 중복된 항목이 생길 수 있으므로 제거 전 중복된 항목을 찾고, 표시할 필요가 있습니다.

중복값은 조건부 서식와 데이터 필터를 이용하여 찾을 수 있습니다.

엑셀 ISBLANK 함수 사용법 (IF 함수 및 조건부 서식 활용)

위의 포스팅 내용 중 조건부 서식을 다룬 내용이 있으니 확인해보셔도 좋을 듯 합니다.

---

1. 조건부 서식을 활용하여 중복 값 찾기

위의 예제에서 제품 열 (C3:C18)을 선택하고 홈 - 조건부 서식 - 셀 강조 규칙 - 중복 값을 선택합니다.

아래의 붉은 배경은 중복 값을 의미합니다.

---

 

---

2. 데이터 필터를 활용하기 중복 값 제거

데이터 - 정렬 및 필터 - 고급을 클릭합니다.

---

---

• 다른 장소에 복사를 클릭합니다.
• 목록 범위를 데이터가 존재하는 셀 범위인 $B$2:$E$18로 설정합니다.
• 복사하고 싶은 위치를 선택합니다 ($G$2)
• 동일한 레코드는 하나만(R) 체크 박스를 선택하고 확인을 클릭합니다.

---

---

---

위의 그림과 같이 중복된 항복이 제거된 것을 확인할 수 있습니다.

3. 중복된 항목 제거 기능을 활용하기

• 모든 데이터 (B2:E18)를 선택합니다.
• 데이터 - 데이터 도구 - 중복된 항목 제거
• 중복 값 제거의 모든 항목을 선택하고 확인

---

---

---

다음의 알림 창이 뜨며 중복된 항목이 제거된 것을 확인할 수 있습니다.

---

엑셀 수식을 활용하여 중복된 항목 제거하기

 

1. 데이터 결합하기 ("&"과 CONCAT 함수)

동일한 예제 파일을 다시 불러오겠습니다.

---

---

이번에는 엑셀 함수를 사용하여 중복된 항목을 찾는 방법을 알아보겠습니다.

한 열에 동일한 데이터 (텍스트)가 몇개 존재하는지 확인하여 2개 이상일 경우 중복된 항목이라는 것을 알 수 있습니다.

이를 위해서 B, C, D, E 열의 데이터를 결합하겠습니다.

이를 위해서, 결합 연산자(Concatenation Operator) "&"를 사용합니다.

엑셀 VLOOKUP 사용법 배우기 3편 - 다중 조건

*위 포스팅에서 결합 연산자에 대한 간략한 정보가 나타나 있습니다.

F2 셀에 "결합"을 입력하고 F3 셀에 다음의 수식을 입력합니다.

=B3&C3&D3

이후 F3:F18 셀을 선택하고 Ctrl + D를 입력하여 자동 완성합니다.

*동일한 결과를 나타내는 결합 연산자로 CONCAT 함수가 있으며, 구문은 다음과 같습니다.

=CONCAT(B3:D3)

---

---

 

2. COUNTIF 함수를 통해 중복된 값 찾기

F열 (결합) 옆인 E열에 개수를 COUNTIF 함수를 활용하여 나타내고자 합니다.

• E2 셀에 "개수"를 입력합니다.
• E3 셀에 다음의 구문을 입력합니다.

=COUNTIF($F$3:F3,F3)

---

---

"1"의 의미: 해당 결합 열의 데이터가 지금까지 한 번 나왔다 = 중복된 적이 없다.

"2"의 의미: 해당 결합 열의 동일한 데이터가 지금까지 두 번 나왔다 = 중복된 적이 있다..

동일하게 "n" 값이 개수에 나온 경우 지금까지 동일한 데이터가 n번 나왔다라고 해석할 수 있습니다.

• 이제 G2:G18 셀을 선택하여 개수 열에 필터를 적용합니다.
• 숫자 필터 중 "1"을 제외하고, "2"를 클릭하여 체크 해제합니다.

---

---

---

이제 위와 같이 중복된 항목이 제거된 것을 알 수 있습니다.

---

Power Query 편집기를 활용하여 중복된 항목 제거하기

 

마지막 방법은 엑셀의 Power Query 편집기를 이용하는 방법입니다.

엑셀의 Power Query는 경우에 따라 위에서 소개된 방법보다 간편하지만 강력한 성능을 보입니다.

보이는 것보다 어렵지 않으니 차근차근 따라오시면 충분히 하실 수 있을 것이라 생각합니다.

다시 처음의 엑셀 파일을 불러옵니다.

---

• 모든 데이터 (B2:E18)를 선택합니다.
• 데이터 - 데이터 가져오기 및 변환 - 테이블/범위에서를 클릭합니다.

---

---

---

이후 확인을 클릭합니다.

이제 다음과 같은 Power Query 편집기가 나타납니다.

---

---

두 가지 옵션을 통해서 중복된 항목 제거가 가능합니다.

• 하나 혹은 여러 개의 열
• 전체 테이블

하나 혹은 여러 개의 열 옵션을 통해서 중복된 항목을 제거하기 위해서는 특정 열 헤더를 우클릭합니다.

예제에서는 제품 열을 통해서 중복된 항목 제거가 가능합니다.

1. 제품 열 우클릭
2. 중복된 항목 제거 클릭

 

---

---

---

그 결과 다음과 같이 중복된 항목이 제거되어 있고, 쿼리 설정의 적용된 단계에 "제거된 중복 항목 수"가 나타나 있습니다.

만약, 여러 개의 열을 선택하여 앞서 데이터 결합 이후 중복된 항목 제거와 동일한 방식으로 진행하고 싶다면 Ctrl을 누른 상태에서 열을 클릭하여 선택하면 됩니다.

또한, 전체 테이블에서 중복된 항목을 제거하기 위해서는 왼쪽 코너의 테이블 아이콘을 클릭하고 중복된 항목 제거를 선택하면 됩니다.

---

---

이번 포스팅에서는 중복된 항목을 찾는 방법 그리고 제거하는 다양한 방법에 대해서 알아보았습니다.

가장 효율적인 방법은 무엇이다라고 단정하는 것보다는 다양한 기능을 적시적소에 사용하는 것이 가장 좋다고 개인적으로 생각합니다.

엑셀 관련하여 실용적인 내용 다룬 포스팅으로 다시 찾아뵙겠습니다.