데이터 중복 정리하기

데이터 정리하다 보면 중복이 정말 골치 아프죠. 같은 고객이 여러 번 등록되어 있거나, 비슷한 회사명이 다르게 입력되어 있거나... 하나하나 눈으로 찾아서 지우기엔 양이 너무 많아요.

게다가 완전히 똑같은 중복만 있는 게 아니에요. 공백이 다르거나, 대소문자만 다르거나, 띄어쓰기가 조금 다른 "유사 중복"도 많습니다. 이런 건 엑셀의 기본 중복 제거 기능으로는 찾을 수가 없어요.

AI를 활용하면 이런 복잡한 중복도 쉽게 찾고 정리할 수 있습니다. 완전 중복부터 유사 중복, 논리적 중복까지 모두 감지할 수 있어요. 심지어 유사도 분석까지 해서 "80% 이상 비슷하면 중복"이라는 기준도 적용할 수 있습니다.

이번 글에서는 AI로 데이터 중복을 찾고 정리하는 다양한 방법을 알아봅니다. 단순한 중복 제거부터 고급 퍼지 매칭까지, 실무에서 바로 쓸 수 있는 기법들을 살펴볼게요.

1. 중복 유형 파악하기

AI 프롬프트

"다음 상황별로 중복을 찾는 방법을 알려주세요:
1. 완전히 동일한 행
2. 특정 열만 중복된 경우
3. 유사하지만 약간 다른 데이터 (예: 공백, 대소문자)
4. 논리적 중복 (같은 사람의 다른 이메일)"

AI 답변

1. 완전 중복: 모든 열 값이 동일
   → 중복 항목 제거 기능 사용

2. 부분 중복: 주요 키 값만 확인
   → COUNTIFS로 특정 열 기준 카운트

3. 유사 중복: 공백/대소문자 차이
   → TRIM, UPPER 함수로 정규화 후 비교

4. 논리적 중복: 비즈니스 규칙 기반
   → 복합 조건으로 판별

2. 기본 중복 제거

엑셀 기본 기능으로 중복 제거

AI 프롬프트:

"A1:E100 범위에서 이메일(C열) 기준으로
중복을 제거하는 VBA 코드를 만들어주세요.
첫 번째 항목은 유지하고 나머지는 삭제하세요."

AI 생성 VBA:

Sub 이메일중복제거()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim deleteCount As Long

    Set ws = ThisWorkbook.Sheets("고객데이터")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' 작업 전 확인
    If MsgBox("중복된 이메일을 가진 행을 삭제하시겠습니까?" & vbCrLf & _
              "(첫 번째 항목은 유지됩니다)", _
              vbYesNo + vbQuestion, "중복 제거 확인") = vbNo Then
        Exit Sub
    End If

    ' 화면 업데이트 중지 (속도 향상)
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' 중복 제거 실행
    ws.Range("A1:E" & lastRow).RemoveDuplicates _
        Columns:=3, _  ' C열(이메일) 기준
        Header:=xlYes

    ' 삭제된 행 수 계산
    deleteCount = lastRow - ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' 화면 업데이트 재개
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    MsgBox deleteCount & "개의 중복 항목이 제거되었습니다.", vbInformation
End Sub

3. 중복 표시하기 (삭제하지 않고)

조건부 서식으로 중복 강조

AI 프롬프트:

"중복된 값을 삭제하지 않고 빨간색으로 표시해주세요.
전화번호(D열) 기준으로 2개 이상 나타나는 값을 찾아주세요."

AI 생성 VBA:

Sub 중복표시_조건부서식()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rng As Range

    Set ws = ThisWorkbook.Sheets("고객데이터")
    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
    Set rng = ws.Range("D2:D" & lastRow)

    ' 기존 조건부 서식 삭제
    rng.FormatConditions.Delete

    ' 중복 값 강조 조건 추가
    With rng.FormatConditions.Add( _
        Type:=xlExpression, _
        Formula1:="=COUNTIF($D$2:$D$" & lastRow & ",$D2)>1")

        .Interior.Color = RGB(255, 199, 206)  ' 연한 빨강
        .Font.Color = RGB(156, 0, 6)          ' 진한 빨강
        .Font.Bold = True
    End With

    MsgBox "중복된 전화번호가 강조 표시되었습니다.", vbInformation
End Sub

중복 개수 표시

AI 프롬프트:

"각 행 옆에 해당 이메일이 몇 번 나타나는지 표시해주세요."

AI 생성 수식:

// F열에 중복 횟수 표시
=COUNTIF($C$2:$C$1000, C2)

// 중복 여부만 표시
=IF(COUNTIF($C$2:$C$1000, C2)>1, "중복", "")

// 몇 번째 중복인지 표시
=IF(COUNTIF($C$2:$C$1000, C2)>1,
   COUNTIF($C$2:C2, C2) & "번째",
   "유일")

4. 고급 중복 처리

여러 열 조합으로 중복 확인

AI 프롬프트:

"이름(A열)과 생년월일(B열)이 동시에 같은 경우만
중복으로 판단하고 표시해주세요."

AI 생성 수식:

// 헬퍼 열(E열) 생성: 이름+생년월일 결합
=A2&"|"&TEXT(B2,"YYYY-MM-DD")

// F열에 중복 여부 표시
=IF(COUNTIF($E$2:$E$1000, E2)>1, "중복", "")

AI 생성 VBA (헬퍼 열 없이):

Sub 복합키중복확인()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long, j As Long
    Dim dict As Object
    Dim key As String
    Dim dupCount As Long

    Set ws = ThisWorkbook.Sheets("직원데이터")
    Set dict = CreateObject("Scripting.Dictionary")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Application.ScreenUpdating = False

    ' 첫 번째 패스: 중복 찾기
    For i = 2 To lastRow
        key = ws.Cells(i, 1).Value & "|" & _
              Format(ws.Cells(i, 2).Value, "YYYY-MM-DD")

        If dict.Exists(key) Then
            dict(key) = dict(key) + 1
        Else
            dict.Add key, 1
        End If
    Next i

    ' 두 번째 패스: 중복 표시
    For i = 2 To lastRow
        key = ws.Cells(i, 1).Value & "|" & _
              Format(ws.Cells(i, 2).Value, "YYYY-MM-DD")

        If dict(key) > 1 Then
            ws.Cells(i, 6).Value = "중복 (" & dict(key) & "건)"
            ws.Rows(i).Interior.Color = RGB(255, 235, 156)
            dupCount = dupCount + 1
        Else
            ws.Cells(i, 6).Value = ""
            ws.Rows(i).Interior.ColorIndex = xlNone
        End If
    Next i

    Application.ScreenUpdating = True

    MsgBox dupCount & "개의 중복 항목이 발견되었습니다.", vbInformation
End Sub

5. 유사 중복 찾기

공백/대소문자 무시

AI 프롬프트:

"회사명에 공백과 대소문자 차이만 있는 중복을 찾아주세요.
예: 'Samsung', 'SAMSUNG', ' Samsung '은 모두 같은 것으로 처리"

AI 생성 VBA:

Sub 유사중복찾기()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim normalizedDict As Object
    Dim originalValue As String
    Dim normalizedValue As String

    Set ws = ThisWorkbook.Sheets("회사목록")
    Set normalizedDict = CreateObject("Scripting.Dictionary")
    normalizedDict.CompareMode = vbTextCompare ' 대소문자 무시
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' E열에 헤더 추가
    ws.Cells(1, 5).Value = "정규화된 값"
    ws.Cells(1, 6).Value = "중복 상태"

    For i = 2 To lastRow
        originalValue = ws.Cells(i, 1).Value

        ' 정규화: 공백 제거 + 대문자 변환
        normalizedValue = UCase(Trim(Replace(originalValue, " ", "")))
        ws.Cells(i, 5).Value = normalizedValue

        ' 중복 확인
        If normalizedDict.Exists(normalizedValue) Then
            ws.Cells(i, 6).Value = "중복 (원본: " & _
                normalizedDict(normalizedValue) & ")"
            ws.Rows(i).Interior.Color = RGB(255, 199, 206)
        Else
            normalizedDict.Add normalizedValue, originalValue
            ws.Cells(i, 6).Value = "최초"
        End If
    Next i

    MsgBox "유사 중복 검사가 완료되었습니다.", vbInformation
End Sub

퍼지 매칭 (유사도 기반)

AI 프롬프트:

"레벤슈타인 거리를 사용해서
80% 이상 유사한 회사명을 찾아주세요."

AI 생성 VBA:

' 레벤슈타인 거리 함수
Function LevenshteinDistance(str1 As String, str2 As String) As Integer
    Dim i As Integer, j As Integer
    Dim cost As Integer
    Dim d() As Integer
    Dim min1 As Integer, min2 As Integer, min3 As Integer

    ReDim d(Len(str1), Len(str2))

    For i = 0 To Len(str1)
        d(i, 0) = i
    Next

    For j = 0 To Len(str2)
        d(0, j) = j
    Next

    For i = 1 To Len(str1)
        For j = 1 To Len(str2)
            If Mid(str1, i, 1) = Mid(str2, j, 1) Then
                cost = 0
            Else
                cost = 1
            End If

            min1 = d(i - 1, j) + 1
            min2 = d(i, j - 1) + 1
            min3 = d(i - 1, j - 1) + cost

            d(i, j) = WorksheetFunction.Min(min1, min2, min3)
        Next
    Next

    LevenshteinDistance = d(Len(str1), Len(str2))
End Function

' 유사도 계산 (0~100%)
Function SimilarityPercent(str1 As String, str2 As String) As Double
    Dim maxLen As Integer
    Dim distance As Integer

    maxLen = WorksheetFunction.Max(Len(str1), Len(str2))
    If maxLen = 0 Then
        SimilarityPercent = 100
        Exit Function
    End If

    distance = LevenshteinDistance(str1, str2)
    SimilarityPercent = (1 - distance / maxLen) * 100
End Function

Sub 유사회사명찾기()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long, j As Long
    Dim similarity As Double
    Dim threshold As Double

    Set ws = ThisWorkbook.Sheets("회사목록")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    threshold = 80 ' 유사도 임계값 80%

    ' 결과 시트 생성
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("유사항목").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    Dim resultWs As Worksheet
    Set resultWs = Sheets.Add(After:=ws)
    resultWs.Name = "유사항목"
    resultWs.Cells(1, 1).Value = "항목1"
    resultWs.Cells(1, 2).Value = "항목2"
    resultWs.Cells(1, 3).Value = "유사도(%)"

    Dim resultRow As Long
    resultRow = 2

    Application.ScreenUpdating = False

    ' 모든 쌍 비교
    For i = 2 To lastRow - 1
        For j = i + 1 To lastRow
            similarity = SimilarityPercent( _
                ws.Cells(i, 1).Value, _
                ws.Cells(j, 1).Value)

            If similarity >= threshold Then
                resultWs.Cells(resultRow, 1).Value = ws.Cells(i, 1).Value
                resultWs.Cells(resultRow, 2).Value = ws.Cells(j, 1).Value
                resultWs.Cells(resultRow, 3).Value = Round(similarity, 2)
                resultRow = resultRow + 1
            End If
        Next j

        ' 진행 상황 표시
        If i Mod 10 = 0 Then
            Application.StatusBar = "처리 중... " & _
                Format(i / lastRow, "0%")
        End If
    Next i

    Application.StatusBar = False
    Application.ScreenUpdating = True

    MsgBox (resultRow - 2) & "개의 유사 항목이 발견되었습니다.", vbInformation
End Sub

6. 중복 병합하기

중복된 행의 데이터 통합

AI 프롬프트:

"같은 고객의 여러 주문을 하나로 병합하고,
주문 금액은 합산, 주문 날짜는 최신 것으로 업데이트하세요."

AI 생성 VBA:

Sub 중복고객병합()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dict As Object
    Dim i As Long
    Dim customerID As String

    Set ws = ThisWorkbook.Sheets("주문데이터")
    Set dict = CreateObject("Scripting.Dictionary")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Application.ScreenUpdating = False

    ' Dictionary에 고객별 데이터 수집
    For i = 2 To lastRow
        customerID = ws.Cells(i, 1).Value ' A열: 고객ID

        If dict.Exists(customerID) Then
            ' 기존 고객: 금액 합산, 날짜 업데이트
            Dim existingData As Variant
            existingData = dict(customerID)

            existingData(0) = existingData(0) + ws.Cells(i, 3).Value ' 금액 합산

            ' 더 최신 날짜로 업데이트
            If ws.Cells(i, 4).Value > existingData(1) Then
                existingData(1) = ws.Cells(i, 4).Value
            End If

            dict(customerID) = existingData
        Else
            ' 신규 고객
            Dim newData(1) As Variant
            newData(0) = ws.Cells(i, 3).Value ' 금액
            newData(1) = ws.Cells(i, 4).Value ' 날짜
            dict.Add customerID, newData
        End If
    Next i

    ' 결과 시트에 쓰기
    Dim resultWs As Worksheet
    On Error Resume Next
    Set resultWs = Sheets("병합결과")
    If resultWs Is Nothing Then
        Set resultWs = Sheets.Add(After:=ws)
        resultWs.Name = "병합결과"
    Else
        resultWs.Cells.Clear
    End If
    On Error GoTo 0

    ' 헤더 작성
    resultWs.Cells(1, 1).Value = "고객ID"
    resultWs.Cells(1, 2).Value = "고객명"
    resultWs.Cells(1, 3).Value = "총 주문금액"
    resultWs.Cells(1, 4).Value = "최종 주문일"

    ' 데이터 작성
    Dim rowNum As Long
    rowNum = 2

    Dim key As Variant
    For Each key In dict.Keys
        resultWs.Cells(rowNum, 1).Value = key

        ' 원본에서 고객명 찾기
        Dim customerName As String
        customerName = Application.WorksheetFunction.Index( _
            ws.Columns(2), _
            Application.WorksheetFunction.Match(key, ws.Columns(1), 0))
        resultWs.Cells(rowNum, 2).Value = customerName

        resultWs.Cells(rowNum, 3).Value = dict(key)(0)
        resultWs.Cells(rowNum, 4).Value = dict(key)(1)

        rowNum = rowNum + 1
    Next key

    ' 서식 적용
    With resultWs.Range("C2:C" & rowNum - 1)
        .NumberFormat = "#,##0"
    End With

    With resultWs.Range("D2:D" & rowNum - 1)
        .NumberFormat = "yyyy-mm-dd"
    End With

    Application.ScreenUpdating = True

    MsgBox "중복 고객이 병합되었습니다." & vbCrLf & _
           "원본: " & lastRow - 1 & "건 → 병합 후: " & _
           dict.Count & "건", vbInformation
End Sub

7. 중복 검사 리포트

AI 프롬프트:

"데이터 품질 보고서를 생성해주세요:
- 총 레코드 수
- 완전 중복 수
- 부분 중복 수
- 중복률(%)
- 열별 중복 통계"

AI 생성 VBA:

Sub 중복검사리포트()
    Dim ws As Worksheet
    Dim reportWs As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, col As Long
    Dim totalRows As Long
    Dim fullDupCount As Long

    Set ws = ThisWorkbook.Sheets("원본데이터")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    totalRows = lastRow - 1

    ' 보고서 시트 생성
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("중복검사리포트").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    Set reportWs = Sheets.Add(After:=Sheets(Sheets.Count))
    reportWs.Name = "중복검사리포트"

    ' 보고서 작성
    With reportWs
        .Cells(1, 1).Value = "데이터 품질 검사 보고서"
        .Cells(1, 1).Font.Size = 14
        .Cells(1, 1).Font.Bold = True

        .Cells(3, 1).Value = "검사일시:"
        .Cells(3, 2).Value = Now

        .Cells(5, 1).Value = "총 레코드 수:"
        .Cells(5, 2).Value = totalRows

        ' 완전 중복 확인
        Dim tempRange As Range
        Set tempRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))

        Dim originalCount As Long
        originalCount = tempRange.Rows.Count - 1

        tempRange.Copy
        tempRange.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), Header:=xlYes

        Dim uniqueCount As Long
        uniqueCount = tempRange.Rows.Count - 1
        fullDupCount = originalCount - uniqueCount

        .Cells(6, 1).Value = "완전 중복:"
        .Cells(6, 2).Value = fullDupCount

        .Cells(7, 1).Value = "중복률:"
        .Cells(7, 2).Value = Format(fullDupCount / totalRows, "0.0%")

        Application.Undo ' 중복 제거 취소

        ' 열별 중복 통계
        .Cells(9, 1).Value = "열별 중복 통계"
        .Cells(9, 1).Font.Bold = True

        .Cells(10, 1).Value = "열 이름"
        .Cells(10, 2).Value = "고유값 수"
        .Cells(10, 3).Value = "중복값 수"
        .Cells(10, 4).Value = "중복률"

        Dim rowNum As Long
        rowNum = 11

        For col = 1 To lastCol
            Dim uniqueValues As Long
            Dim colName As String

            colName = ws.Cells(1, col).Value
            uniqueValues = ws.Range(ws.Cells(2, col), ws.Cells(lastRow, col)).Rows.Count - _
                           ws.Range(ws.Cells(2, col), ws.Cells(lastRow, col)).RemoveDuplicates( _
                           Columns:=1, Header:=xlNo).Rows.Count

            Application.Undo

            uniqueValues = Application.WorksheetFunction.SumProduct( _
                1 / Application.WorksheetFunction.CountIf( _
                ws.Range(ws.Cells(2, col), ws.Cells(lastRow, col)), _
                ws.Range(ws.Cells(2, col), ws.Cells(lastRow, col))))

            .Cells(rowNum, 1).Value = colName
            .Cells(rowNum, 2).Value = uniqueValues
            .Cells(rowNum, 3).Value = totalRows - uniqueValues
            .Cells(rowNum, 4).Value = Format((totalRows - uniqueValues) / totalRows, "0.0%")

            rowNum = rowNum + 1
        Next col

        ' 서식 적용
        .Columns("A:D").AutoFit
        .Range("A10:D10").Font.Bold = True
        .Range("A10:D" & rowNum - 1).Borders.LineStyle = xlContinuous
    End With

    MsgBox "중복 검사 리포트가 생성되었습니다.", vbInformation
End Sub

8. 실전 활용: 고객 데이터 정제

통합 시나리오:

Sub 고객데이터정제_통합()
    ' 1단계: 백업 생성
    Call 데이터백업

    ' 2단계: 유사 중복 찾기
    Call 유사중복찾기

    ' 3단계: 중복 병합
    Call 중복고객병합

    ' 4단계: 리포트 생성
    Call 중복검사리포트

    MsgBox "고객 데이터 정제가 완료되었습니다.", vbInformation
End Sub

Sub 데이터백업()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("고객데이터")

    ws.Copy After:=ws
    ActiveSheet.Name = "백업_" & Format(Now, "yyyymmdd_hhmmss")
End Sub

마무리

AI로 데이터 중복 정리:

• ✅ 다양한 유형의 중복 감지
• ✅ 유사도 기반 퍼지 매칭
• ✅ 중복 병합 및 통합
• ✅ 데이터 품질 리포트

다음 글에서는 IF문과 VLOOKUP을 자동으로 작성하는 방법을 알아봅니다.