엑셀 데이터 중복값 제거, 왜 필요할까요?

엑셀을 사용하다 보면 똑같은 데이터가 여러 번 반복되어 나타나는 경우가 많습니다. 예를 들어, 고객 명단에서 이름이 중복되거나, 판매 기록에서 동일한 제품이 여러 번 기록되는 식이죠. 이렇게 중복된 데이터는 다음과 같은 문제를 일으킬 수 있습니다.

• 데이터 분석의 오류: 중복된 데이터는 통계나 분석 결과에 왜곡을 가져올 수 있습니다. 예를 들어, 실제 고객 수가 100명인데 중복 데이터 때문에 120명으로 잘못 계산될 수 있습니다.

• 불필요한 저장 공간 낭비: 데이터 양이 많아질수록 중복된 데이터는 저장 공간을 차지하며, 파일 용량을 불필요하게 늘립니다.

• 업무 효율성 저하: 중복된 데이터를 일일이 찾아 수정하거나 관리하는 것은 시간과 노력을 많이 소모하는 비효율적인 작업입니다.

• 정보의 혼란 야기: 똑같은 정보가 여러 곳에 존재하면 어떤 정보가 최신이고 정확한 정보인지 혼란을 겪을 수 있습니다.

따라서 엑셀 데이터를 효율적으로 관리하고 정확한 분석을 위해서는 중복값을 제거하고 고유값만 남기는 작업이 필수적입니다.

엑셀 기본 기능: 필터로 중복값 확인 및 제거하기

엑셀의 기본 기능인 ‘필터’를 활용하면 중복값을 손쉽게 확인하고 제거할 수 있습니다. 이 방법은 간단하지만, 데이터의 양이 많거나 복잡한 경우에는 다소 시간이 걸릴 수 있습니다.

1단계: 데이터 준비 및 필터 적용

먼저, 중복값을 제거하려는 엑셀 시트를 엽니다. 데이터 범위 전체를 선택하거나, 중복값을 확인할 특정 열을 선택합니다.

1. 데이터 선택: 중복값을 확인할 열(예: ‘이름’ 열)의 첫 번째 데이터 셀을 클릭합니다.

2. 필터 적용: 엑셀 상단 메뉴에서 [데이터] 탭으로 이동하여 [필터]를 클릭합니다. 선택한 열 머리글에 드롭다운 화살표가 나타나는 것을 확인할 수 있습니다.

2단계: 중복값 표시 필터 사용 (Excel 2013 이상)

Excel 2013 버전 이상을 사용하신다면, ‘중복값’ 필터 기능을 바로 활용할 수 있어 더욱 편리합니다.

1. 열 머리글 클릭: 중복값을 확인할 열의 머리글에 있는 드롭다운 화살표를 클릭합니다.

2. ‘텍스트 필터’ 또는 ‘숫자 필터’ 선택: 해당 열의 데이터 형식에 따라 ‘텍스트 필터’ 또는 ‘숫자 필터’를 선택합니다.

3. ‘같음’ 선택: 나타나는 하위 메뉴에서 ‘같음’을 선택합니다. (이는 특정 값과 ‘같은’ 항목을 찾는 기본 필터입니다.)

4. ‘중복값’ 입력: ‘같음’ 옆의 입력란에 “중복값”이라고 직접 입력합니다. (정확히 “중복값”이라는 텍스트를 입력하는 것이 아니라, 엑셀이 자체적으로 중복된 항목을 찾아주는 기능입니다. 이 부분에서 혼동이 있을 수 있습니다. 정정: 이 방법은 잘못된 정보이며, 엑셀 기본 필터에는 직접적으로 “중복값”을 찾아주는 기능이 없습니다. 아래에서 정확한 방법을 안내해 드리겠습니다.)

[정확한 중복값 필터링 방법 (Excel 2013 이상)]

Excel 2013부터는 ‘조건부 서식’을 활용하여 중복값을 시각적으로 표시하고, 이를 바탕으로 필터링하는 것이 일반적입니다.

1. 데이터 범위 선택: 중복값을 확인할 열 또는 전체 데이터 범위를 선택합니다.

2. 조건부 서식 적용: [홈] 탭에서 [조건부 서식] > [셀 강조 규칙] > [중복값]을 선택합니다.

3. 서식 지정: ‘중복값’ 대화 상자에서 ‘다음으로 서식이 지정된 셀’ 옆에 원하는 서식(예: 연한 빨강 채우기)을 선택하고 ‘확인’을 클릭합니다.

4. 중복값 시각화: 이제 중복된 값이 있는 셀들이 지정한 서식으로 표시됩니다.

5. 필터 적용: 데이터 범위를 다시 선택한 상태에서 [데이터] 탭의 [필터]를 클릭합니다.

6. 색상으로 필터링: 중복값이 표시된 열의 머리글 드롭다운 화살표를 클릭하고, [색으로 필터링] > (조건부 서식으로 지정한 색상)을 선택합니다.

7. 결과 확인: 선택한 색상으로 표시된 (즉, 중복된) 데이터만 화면에 나타납니다. 이 상태에서 해당 행들을 선택하여 삭제하거나 다른 시트로 복사할 수 있습니다.

3단계: 중복값 제거 기능 활용 (더 빠르고 정확하게)

엑셀에는 중복값을 직접 제거해주는 편리한 기능이 있습니다. 이 기능은 중복된 행 전체를 삭제하므로, 사용 전에 반드시 원본 데이터를 백업하거나 주의 깊게 사용해야 합니다.

1. 데이터 범위 선택: 중복값을 제거하려는 데이터 범위를 선택합니다. 특정 열만 기준으로 중복을 제거하고 싶다면 해당 열을 포함한 전체 범위를 선택해야 합니다.

2. ‘중복된 항목 제거’ 실행:

3. Excel 2013 이상: [데이터] 탭 > [데이터 도구] 그룹 > [중복된 항목 제거]를 클릭합니다.

4. Excel 2010 이하: [데이터] 탭 > [데이터 도구] 그룹 > [중복된 항목 제거]를 클릭합니다.

5. 기준 열 선택: ‘중복된 항목 제거’ 대화 상자가 나타납니다. 여기서 어떤 열을 기준으로 중복값을 판단할지 선택합니다. 예를 들어, ‘이름’과 ‘이메일’이 모두 같아야 중복으로 간주하려면 두 열 모두 체크합니다. 특정 열(예: ‘주문 번호’)만 중복을 제거하고 싶다면 해당 열만 체크합니다.

6. 팁: ‘모두 선택’ 또는 ‘모두 선택 취소’ 버튼을 활용하면 편리합니다.

7. 확인: ‘확인’을 클릭하면 엑셀이 중복된 값을 찾아 삭제하고, 몇 개의 값이 제거되었는지, 몇 개의 고유한 값이 남았는지 알려줍니다.

주의사항:

• 이 기능은 중복된 행 전체를 삭제합니다. 따라서 특정 열만 중복을 제거하고 싶더라도, 해당 열의 값이 중복되는 모든 행이 삭제될 수 있습니다.

• 실행 전 원본 데이터 백업은 필수입니다.

엑셀 고급 기능: 고유값만 추출하기

중복값을 제거하는 것 외에, 특정 열에서 고유한 값들만 따로 모아보고 싶을 때가 있습니다. 예를 들어, 전체 고객 목록에서 어떤 지역의 고객들이 있는지 고유한 지역 목록만 추출하고 싶을 때 유용합니다.

1. ‘고급 필터’ 기능 활용

엑셀의 ‘고급 필터’ 기능은 특정 조건을 만족하는 데이터를 추출하거나, 중복된 값을 제외하고 고유한 값만 다른 위치로 복사하는 데 강력한 기능을 제공합니다.

1. 데이터 준비:

2. 고유값을 추출할 열의 제목(헤더)이 반드시 있어야 합니다.

3. 추출된 고유값을 붙여넣을 빈 셀 영역을 준비합니다.

4. 고급 필터 실행:

5. [데이터] 탭 > [정렬 및 필터] 그룹 > [고급]을 클릭합니다.

6. 옵션 설정: ‘고급 필터’ 대화 상자가 나타납니다.

7. 동작: ‘다른 장소에 복사’를 선택합니다.

8. 목록 범위: 고유값을 추출할 원본 데이터 범위를 선택합니다. (열 제목 포함)

9. 조건 범위: 비워둡니다. (고유값 추출은 조건이 필요 없습니다.)

10. 복사 위치: 고유값이 추출될 시작 셀을 지정합니다.

11. ‘고유 레코드만’ 체크: 이 부분이 가장 중요합니다. 이 옵션을 선택해야 중복된 값이 제외되고 고유한 값만 추출됩니다.

12. 확인: ‘확인’을 클릭하면 지정한 ‘복사 위치’에 원본 데이터에서 중복을 제외한 고유한 값들만 추출되어 나타납니다.

2. ‘고유값’ 버튼 활용 (Excel 2013 이상)

Excel 2013 버전부터는 ‘고유값’ 추출 기능이 더욱 간편해졌습니다.

1. 데이터 범위 선택: 고유값을 추출할 열의 데이터를 선택합니다. (열 제목 제외)

2. ‘제거’ 탭 이동: [데이터] 탭으로 이동합니다.

3. ‘고유값’ 버튼 클릭: [데이터 도구] 그룹에 있는 ‘중복된 항목 제거’ 바로 옆에 ‘고유값’ 버튼이 있다면 이를 클릭합니다. (이 버튼은 Excel 버전에 따라 약간 다를 수 있습니다. 만약 없다면 ‘중복된 항목 제거’ 기능을 활용 후, 제거된 중복 항목을 다시 붙여넣는 방식으로도 가능합니다.)

4. [정정] 엑셀에는 ‘고유값’이라는 독립적인 버튼이 직접적으로 존재하지 않습니다. ‘중복된 항목 제거’ 기능을 사용한 후, 남은 고유한 값들을 활용하는 것이 일반적입니다.

5. ‘중복된 항목 제거’ 기능 활용:

6. 고유값을 추출할 열의 데이터를 선택합니다.

7. [데이터] 탭 > [데이터 도구] 그룹 > [중복된 항목 제거]를 클릭합니다.

8. ‘중복된 항목 제거’ 대화 상자에서 중복을 판단할 기준 열을 모두 선택 취소하고, 고유값을 추출할 해당 열만 선택합니다.

9. ‘확인’을 클릭하면 해당 열에서 중복된 값들이 제거되고 고유한 값만 남게 됩니다.

3. 수식 활용: UNIQUE 함수 (Microsoft 365 및 Excel 2021 이상)

Microsoft 365 구독자나 Excel 2021 이상 버전을 사용하신다면, UNIQUE 함수를 사용하여 동적으로 고유값을 추출할 수 있습니다. 이 함수는 원본 데이터가 변경될 때마다 자동으로 업데이트되는 장점이 있습니다.

1. 함수 입력: 고유값을 표시할 셀에 다음과 같은 형식으로 함수를 입력합니다.

=UNIQUE(배열, [열], [처음_한_번])

• 배열: 고유값을 추출할 원본 데이터 범위입니다. (예: A2:A100)

• [열]: 선택 사항입니다. TRUE로 설정하면 열별 고유값을 반환하고, FALSE 또는 생략하면 행별 고유값을 반환합니다. (일반적으로 행별 고유값 추출 시에는 생략합니다.)

• [처음_한_번]: 선택 사항입니다. TRUE로 설정하면 처음 나타나는 값만 반환하고, FALSE 또는 생략하면 마지막에 나타나는 값만 반환합니다.

예시: A2부터 A100까지의 데이터에서 고유값만 추출하고 싶다면, 원하는 셀에 =UNIQUE(A2:A100)이라고 입력합니다.

1. 결과 확인: 함수를 입력하면 해당 셀부터 아래로 고유한 값들이 자동으로 채워집니다. 이 영역을 ‘동적 배열’이라고 하며, 다른 셀에 영향을 주지 않습니다.

UNIQUE 함수 활용 팁:

• 원본 데이터 범위를 A2:A100과 같이 고정하기보다는, A2:A와 같이 열 전체를 지정하면 나중에 데이터가 추가되어도 자동으로 범위가 확장됩니다.

• UNIQUE 함수는 동적 배열 함수이므로, 결과가 표시되는 셀 아래나 옆에 다른 데이터가 있으면 오류가 발생할 수 있습니다. 충분히 비어있는 공간에 함수를 입력하세요.

데이터 정리를 위한 추가 팁 및 주의사항

엑셀에서 중복값을 제거하고 고유값을 추출하는 작업은 데이터를 깔끔하게 유지하는 데 매우 중요합니다. 몇 가지 추가 팁과 주의사항을 숙지하시면 더욱 효율적으로 작업할 수 있습니다.

1. 데이터 정렬의 중요성

중복값 제거 기능이나 필터링을 사용하기 전에 데이터를 정렬하는 것이 좋습니다. 비슷한 값들이 함께 모여 있으면 중복값을 시각적으로 확인하기 쉬울 뿐만 아니라, 중복값 제거 기능이 예상대로 작동하는지 확인하는 데도 도움이 됩니다.

• 정렬 방법:

• 데이터 범위를 선택합니다.

• [데이터] 탭 > [정렬]을 클릭합니다.

• 정렬 기준이 될 열을 선택하고 오름차순 또는 내림차순으로 정렬합니다.

2. 공백 및 오탈자 확인

중복값 제거 기능은 정확히 일치하는 값에 대해서만 작동합니다. 따라서 다음과 같은 경우 중복으로 인식되지 않을 수 있습니다.

• 앞뒤 공백: “