엑셀(Excel) 데이터 양이 많다면 중복 값을 일일이 확인하는 것은 매우 어렵습니다. 이럴 때 엑셀 중복 값을 자동으로 찾는 기능을 활용하면, 일을 빠르고 정확하게 처리할 수 있는데요.
이번에는 엑셀 중복 값을 처리하는 방법을 예제와 함께 정리해 보겠습니다.
중복 값 찾기(추출)
[조건부 서식] 기능을 사용해서 중복 값을 찾아보겠습니다.
▲ 위 예제의 목적은 품목(C열) 데이터의 중복 값을 찾아 색깔로 표시하는 것입니다.
▲ 중복 값을 찾는 셀을 선택해 주세요. 위 예제에서는 C3부터 C10을 선택했습니다.
▲ [홈] 탭의 메뉴 중에서 [조건부 서식] – [셀 강조 규칙] – [중복 값]을 눌러 주세요.
▲ [중복 값] 대화 상자에서 서식을 적용할 조건을 선택해 주세요.
첫 번째, 어떤 값(중복, 고유)에 서식을 적용할지 선택합니다.
두 번째, 어떤 서식을 적용할지 선택합니다.
▲ 지금은 중복 값을 추출해야 하므로, 첫 번째 조건은 ‘중복’을 선택했습니다.
두 번째로 중복 값에 적용할 서식은 ‘연한 빨강 채우기와 진한 빨강 텍스트’를 선택하고, [확인] 버튼을 눌렀습니다.
▲ ‘볼펜’과 ‘A4용지’가 있는 셀의 색깔이 변했습니다.
품목 데이터에 ‘볼펜’과 ‘A4용지’가 두 개씩 있어서 중복 값으로 선택된 것입니다.
✅ 구매일 중복 값 찾기
이번에는 구매일(표시 형식: 날짜)의 중복 값을 찾아보겠습니다.
▲ 먼저 구매일(B열)에 있는 8개 셀을 선택했습니다.
▲ 앞에서 했던 방법대로 [중복 값] 메뉴를 선택해 주세요.
▲ 이번에는 ‘진한 노랑 텍스트, 노랑 채우기’ 서식을 선택했습니다.
▲ 중복 값인 ‘2022-05-04’와 ‘2022-05-15’가 입력된 셀의 색깔이 변한 것을 보실 수 있습니다.
✅ 서식 지우기
이제 중복 값에 적용했던 서식을 모두 제거해 보겠습니다.
▲ [조건부 서식] – [규칙 지우기] – [시트 전체에서 규칙 지우기]를 눌러 주세요.
▲ 구매일과 품목 열에 적용된 조건부 서식이 모두 없어진 것을 볼 수 있습니다.
중복 값 합치기
[통합] 기능을 사용해서 중복 값이 있는 영역을 합쳐 보겠습니다.
▲ 먼저, 합쳐질 값이 입력될 셀을 선택해야 합니다. 원하는 셀을 클릭해서 선택할 수 있습니다.
예제에서는 E3셀을 클릭했습니다. 나중에 합쳐진 값들이 E3셀부터 오른쪽과 아래 방향으로 입력됩니다.
(참고) 예제에서 중복 값이 있는 셀을 분홍색으로 표시해 놓았습니다.
▲ [데이터] 탭에 있는 메뉴 중에서 [통합] 버튼을 눌러 주세요.
▲ [통합] 대화 상자에서 [참조] 입력 칸 오른쪽에 있는 ‘화살표(↑) 아이콘’을 눌러 주세요.
▲ 합치기(통합) 할 영역을 드래그해서 선택하고, 엔터(Enter) 키를 눌러 주세요.
왼쪽 ‘품목’ 열은 레이블이고, 오른쪽 ‘구매액’ 열은 합치기 할 값입니다.
▲ 왼쪽 레이블(품목)에 중복 값이 있는 경우 오른쪽 값(구매액)을 더하도록 설정해 보겠습니다.
사용할 레이블은 ‘왼쪽 열’을 선택하고, [확인] 버튼을 눌러 주세요.
▲ E3셀부터 합쳐진 값이 입력됐습니다.
‘볼펜’의 오른쪽에 있는 값인 9,600 두 개가 합쳐져서 19,200이 입력되고, ‘A4용지’의 오른쪽에 있는 18,400과 11,500이 합쳐져서 29,900이 입력된 것을 볼 수 있습니다.
중복 값 제거하기
[중복된 항목 제거] 기능을 사용해서 중복 값을 제거해 보겠습니다.
▲ 중복 값을 제거하기 전과 후를 비교하기 위해서, 원본 값(B2~B10)을 오른쪽에 복사해 놓았습니다.
▲ 중복 값을 제거할 영역(D3~D10)을 선택했습니다.
▲ [데이터] 탭의 메뉴 중에서 [중복된 항목 제거] 버튼을 눌러 주세요.
▲ 이렇게 경고 창이 나올 수 있습니다.
선택한 영역만 중복 값을 제거할 예정이므로 [현재 선택 영역으로 정렬]을 선택하고, [중복된 항목 제거] 버튼을 눌러 주세요.
▲ [중복 값 제거] 대화 상자에 있는 [확인] 버튼을 눌러 주세요.
▲ ‘제거된 중복 값’과 ‘제거되지 않고 남은 고유 값’ 개수를 알려 주는 창이 나옵니다. [확인] 버튼을 눌러 주세요.
▲ 이렇게 중복 값 2개가 제거됐습니다.
중복 값 개수 세기
중복 값이 제거된 데이터를 이용해서 최초 데이터에 중복 값이 몇 개 있는지 확인해 보겠습니다.
예제에서는 COUNTIF 함수를 사용하겠습니다.
▲ 먼저 중복 값이 제거된 데이터의 오른쪽에 ‘개수’를 입력해 보겠습니다.
함수를 입력하기 위해 E3셀을 눌렀습니다.
▲ E3셀에 ‘=countif($B$3:$B$10, D3)’을 입력했습니다.
지금 입력한 수식은 ‘참조 영역(B3~B10)에 D3셀(볼펜)과 같은 셀의 개수’를 구하는 수식입니다.
▲ E3셀에 2가 입력됐습니다. 참조 영역(B3~B10)에 ‘볼펜’이 2개 있다는 의미입니다.
아래 셀(E4~E8)에는 수식을 직접 입력하지 않고, 채우기 핸들을 이용해서 수식을 자동으로 채워 보겠습니다.
앞의 수식에서 B3과 B10에 $(달러)를 입력했기 때문에, 채우기를 해도 참조 영역이 변하지 않고 ‘B3:B10’으로 고정됩니다.
▲ E3부터 E8까지 개수가 모두 입력됐습니다.
여기서 개수가 ‘2’ 이상인 값이 중복 값입니다.
▲ 이렇게 ‘볼펜’과 ‘A4용지’의 개수가 ‘2’인 것을 확인할 수 있습니다.