콘텐츠 목차(Table of Contents)
안녕하세요! 직장인과 학생들의 영원한 야근 방지 동반자, 엑셀(Excel)입니다. 수백, 수천 줄이 넘어가는 방대한 설문조사 데이터나 부서별 매출 장부, 고객 명단을 취합하다 보면 필연적으로 동일한 이름이나 주문번호가 ‘중복’으로 기입되는 불상사가 발생합니다. 원본 데이터를 정제할 때 첫 단추를 잘못 끼우면 통계 수치 전체가 꼬여버리기 일쑤죠.
혹시 아직도 마우스 휠을 굴리며 눈이 빠져라 똑같은 단어를 찾고 계시진 않나요? 실무에서 데이터 클렌징(Data Cleansing)을 할 때 가장 기본적이면서도 막강한 무기가 바로 ‘엑셀 중복값 찾기 및 제거’ 기능입니다. 클릭 단 세 번이면 데이터의 옥석을 가려낼 수 있습니다.
오늘은 마이크로소프트 오피스의 공식 지원 매뉴얼을 바탕으로 투박한 설명서는 치워버리고, 정말 실무에서 바로 써먹을 수 있는 엑셀 중복값 찾기와 두 열 데이터 비교 영구 제거 방법, 그리고 함정 대처법을 핵심 요약으로 완벽하게 전수해 드리겠습니다.
▲ 노가다는 이제 그만! 클릭 몇 번이면 칼퇴가 가능합니다.
- 시각적 조건부 서식: 데이터를 섣불리 지우지 않고 특정 색상(빨간색 등)으로 하이라이트 칠을 하여 위치를 육안으로 식별합니다.
- 버튼 원클릭 삭제: 데이터 탭에 있는 기능으로 필요 없는 백업 찌꺼기를 세밀한 카테고리 필터별로 영구 삭제 합니다.
- 여러 열 심화 스크리닝 비교: 단일 열이 아닌 작년도 vs 올해도 등 독립된 ‘두 개의 열(Column) 데이터 교집합’을 찾아내는 함수 기술식을 지원합니다.
1. 엑셀 중복값 찾기 기초: 조건부 서식 하이라이트 표시 🔍
중복 데이터는 유용할 때도 있지만, 큰 데이터를 이해하기 더 어렵게 만들 수도 있습니다. 무작정 지우기 전에 조건부 서식을 사용하여 중복 데이터를 찾고 강조 표시해 보세요. 그러면 중복 값을 눈으로 먼저 검토하고 제거 여부를 현명하게 결정할 수 있습니다.
▲ 중복 값을 확인할 셀을 지정한 후, 상단 패널에서 홈 > 조건부 서식 > 셀 규칙 > 중복 값 강조 표시를 선택합니다.
▲ 팝업이 뜨면 값 옆의 상자에서 중복 값에 적용할 서식(예: 진한 빨강 텍스트가 있는 연한 빨강 채우기)을 선택한 다음 [확인]을 선택합니다. 한눈에 중복값이 구분됩니다.
2. 클릭 세 번에 끝나는 엑셀 중복값 찾기 및 영구 제거 🗑️
[중복 제거 기능]을 사용하면 중복 데이터가 영구적으로 삭제됩니다. 따라서 중복 항목을 삭제하기 전에는 실수로 필수 정보를 잃지 않도록 반드시 원본 데이터를 다른 워크시트로 이동하거나 복사(백업)해 두는 것이 좋습니다.
▲ 상단의 [데이터] > [중복 제거] 버튼을 선택한 다음 열 목록 창이 뜨면, 고유값을 보존해야 할 독립적인 열은 검사에서 선택 취소(해제)할 수 있습니다.
▲ 예를 들어, 이 워크시트에서 ‘1월’ 열에는 절대 삭제하지 않고 유지해야 하는 중요 가격 정보가 있습니다.
▲ 이렇게 1월 칸을 살리려면, 중복 제거 상자 항목 목록에서 V표시 되어있던 ‘1월’을 클릭하여 선택 취소(빈칸) 한 뒤 하단의 확인을 선택하시면 됩니다.
3. 심화: 두 개의 열을 비교하여 엑셀 중복값 찾기 스크리닝 💡
실무 현장에서는 종종 하나의 열(A열) 내부에서의 중복 찾기가 아니라, 상사가 “작년도 구매 명단(A열)과 금년도 명단(B열) 시트 사이에 겹쳐있는 고객(교집합)만 발라와”라는 고난도 지시를 내릴 때가 있습니다. 이 경우엔 수식을 써서 비교해야 합니다.
🌐 두 열(Column) 데이터 병합 및 비교 로직
• 나란히 있는 단순 비교 (IF 함수): 각 행의 순서가 정확히 대응할 땐 C열 1번 칸에 =IF(A1=B1, "중복", "") 라고 입력 후 쭈욱 드래그 하시면 됩니다.
• 순서가 섞인 스크리닝 (COUNTIF 함수): B열의 각 명단이 A열 전체 범위 중 1번이라도 등장하는지 뒤지려면 =IF(COUNTIF($A:$A, B1)>0, "중복", "") 포맷식을 활용합니다.
※ 좀 더 방대한 배열 수식과 난이도 높은 병합 과정은 하단의 마이크로소프트 고객지원부서의 공식 솔루션을 참조바랍니다!
4. 고수가 쓰는 엑셀 원본 보존 중복값 필터: UNIQUE 함수 💻
원본 데이터를 물리적으로 파괴(삭제 버튼)하는 것을 극도로 꺼림칙하게 여기는 보수적인 프로젝트의 경우, “원본은 건드리지 말고 옆에 새 시트에다 유니크한 리스트만 짜와”라는 명령이 떨어집니다. 이럴 땐 UNIQUE 내장 함수가 완벽한 해결책입니다.
🌐 엑셀 최신 배열 함수: UNIQUE 활용법
• 빈 셀을 하나 선택하고 =UNIQUE(A2:A100) 와 같이 원본 데이터 덩어리를 괄호 안에 넣고 엔터를 칩니다.
• 데이터 내에서 겹치던 것을 단 1회 씩만 등장하도록 자체 정제시킨 ‘순수 고유값(Unique Values)’ 배열 명단이 아래쪽 셀에 자동으로 쏟아집니다.
• 원본(A열)을 추가 타이핑해서 바꿀 때마다, 명단 데이터도 ‘실시간(Real-Time)’으로 증식되므로 관리자 대시보드 제작에 꽃입니다.
※ 주의: UNIQUE 배열 함수는 Excel 2021 및 Microsoft 365 버전만 지원합니다. 사무실 옛날 엑셀에서는 ‘고급 필터’를 임시 방편으로 쓰셔야 합니다.
5. 엑셀 중복값 찾기 안됨 오류 총정리! 실전 FAQ 답변 💡
기계를 전적으로 믿고 무지성 삭제를 진행했는데, 막상 안 지워진 오염 데이터가 있거나 멀쩡한 수백만 원짜리 명단이 망가지는 등 십년감수하게 만드는 환장할 오류 Q&A 10가지를 발췌했습니다.
Q1 육안으로 보면 완전히 똑같은 텍스트인데, 기능이 인식을 못하고 버벅거립니다.
“홍길동”과 “홍길동 “(스페이스 한 칸)은 컴퓨터 IT 체계상 남남인 텍스트로 인식됩니다. 이럴 땐 빈 곳에 =TRIM(셀 지정 칸) 함수를 먼저 씌워 잉여 공백을 싹 살균 제거해주고 복사-값 붙여넣기를 체인징 한 뒤 엑셀 스캔을 재가동해 보세요.
Q2 테이블 표면을 전체 씌웠는데 일렬 덩어리가 통째로 날아가지 않고 일부분만 잘려 나갔습니다.
만약 [아이디 열, 이름 열, 휴대전화 열] 총 세 개 파츠가 완전히 100% 토씨 하나 안 틀리게 일치할 때 한 세트의 행(Row)으로 식별하고 싶으시다면, 체크박스 목록에 그 세 가지가 반드시 모두 V 활성화되어 있어야 합니다.
Q3 옛날 엑셀 버전이라 UNIQUE 함수가 쳐지지가 않습니다. 원본 유지하는 필터 꼼수는 없을까요?
데이터 탭 ➜ ‘고급(고급 필터)’을 누른 직후, 상단의 [다른 장소에 복사] 라디오 버튼을 딸깍 켜주세요. 그 다음 하단에 웅크리고 있는 [동일한 레코드는 하나만] 이라는 핵심 항목에 V 표시를 한 뒤 OK 사인을 내리면 UNIQUE 함수 포맷과 질량 보존이 완벽히 똑같은 산출물을 뱉어냅니다.
Q4 특정 데이터값 항목이 내 시트에 과연 ‘도합 몇 번이나 등장했는지(몇 번 중복인지)’ 카운트 세고 싶습니다.
B열에 조사 명단이 깔려있다면, 우측 빈칸에 `=COUNTIF($B:$B, B2)` 수식을 채워 쭉 아래로 파종하세요. 결과 숫자가 2 이상으로 뜨는 행(Row)들이 바로 이번 리서치에서 겹치기로 적발된 불량 단골손님들입니다.
Q5 알파벳 영어에서 대문자와 소문자를 다르게 인식하진 않던가요? (ex. Korea와 korea)
Korea, korea를 똑같은 애들로 인식해서 하나로 묶어 지워버리죠. 이를 보안 비밀번호 찾기처럼 아주 엄격하게 구별하고 싶다면, 옆에다 =EXACT(셀1, 셀2) 함수 구문을 거쳐 TRUE/FALSE 감별사 보조 열을 뽑아내시는 심화 과정을 거쳐야 합니다.
Q6 항목을 지우긴 했는데, 위에 1번으로 등록된 데이터값과 맨 밑에 등록된 2번 값 중에 대체 어느 게 최후에 살아남은 겁니까?
따라서 최신 갱신된 최근 매출 내역 정보를 우선적으로 살리고 싶다면, 섣불리 지우개 버튼부터 들이대지 마시고 반드시 날짜 열을 기준으로 ‘최신순 정렬(내림차순)’을 거쳐 리뉴얼 데이터를 옥상으로 끌어올리신 후 스캔을 작동하시기 바랍니다.
Q7. 조건부 서식으로 빨갛게 핏빛(?)이 밴 중복값들만 따로 헤쳐 모여 맨 위로 정렬시킬 수 있나요?
정렬 추가 기준에 진입하셔서 ‘셀 값’이 아닌 ‘셀 색’ 항목을 선택, 위치를 [위에 표시(Top)]로 기어 변경해주시고 확인을 때리면 우리가 서식으로 빨갛게 색칠해둔 범죄(?) 데이터들만 최상단으로 촤르륵 대열을 재정비하게 됩니다.
Q8. 워크시트(Sheet1, Sheet2…) 여러 개 탭들에 이산가족처럼 흩어져 분리된 방대한 정보들을 클릭 한 큐에 전체 다 통합 검사할 방법은 없나요?
번거로우시더라도 [통합(Consolidate)] 기능을 띄우시거나 물리적 수동 복사-붙여넣기를 거듭하여 결국 ‘1개의 마스터 대형 시트(Master Sheet)’ 판을 짜셔서 모조리 합친 다음 스캐닝 엔진을 돌리시는 것이 치명적인 누락 참사를 예방하는 정석적인 워크플로우입니다.
Q9. 선생님 영구 삭제라면서요? 제거 버튼을 잘못 눌러 데이터베이스가 엉켰는데 원상복구 안 되나요? 제발 도와주세요!
다만 이미 식은땀 흘리면서 저장(Ctrl+S 버튼) 누른 뒤에 우측 X 버튼으로 엑셀 프로그램을 닫아버리셨다면 억겁의 시간이 흘러도 소실된 찌꺼기는 다시 현세로 불러들일 수 없습니다. (데이터 증발) 따라서 파괴적인 이 기능을 수행하기 직전에는 반드시 원본 시트 탭 바탕 공간을 마우스 우클릭하여 [복사본(Copy) 만들기] 체크란을 통과해 보험용 백업 스페어타이어를 상비해 두는 프로의 습관을 가지세요!
Q10. 고유 회원번호가 “003” 과 “3”으로 기입돼 있는데 둘 다 육안으론 같은 사람 3번인데 컴퓨터 기계가 동일인 1인으로 겹치게 취급을 안 해서 지워지지가 않고 살만 찌고 있어요.
[텍스트 나누기] 기법이나 강력한 =VALUE(특정셀) 포터블 함수를 투입하셔서 시스템상 ‘순수한 동일 숫자(Number)’ 포맷형 속성으로 통일시키거나 아예 싹 다 텍스트화를 구워 버린 후 다시금 자비 없는 지우개 스캐닝을 작동시켜주시면 되겠습니다.