엑셀 오타 찾기 노가다 탈출, 데이터 클렌징 매크로로 정확도 높이기

업무를 하다 보면 수만 줄의 데이터 속에서 아주 미세한 오타 하나 때문에 보고서 전체 수치가 틀어지는 경우가 많습니다. 특히 텍스트 사이에 숨어 있는 공백이나, 숫자처럼 보이지만 텍스트로 저장된 데이터들은 눈으로 찾아내기가 거의 불가능합니다. 오늘은 클릭 한 번으로 데이터의 오타와 형식 오류를 찾아내어 색상을 칠해주는 클렌징 매크로를 소개합니다.

1. 데이터 클렌징이 실무에서 중요한 이유

데이터 분석의 결과는 입력된 데이터의 품질에 따라 결정됩니다. 아무리 훌륭한 수식과 피벗 테이블을 사용해도 원본 데이터에 오타가 있다면 결과값은 신뢰할 수 없습니다. 특히 원가 관리나 재무 업무에서는 단 하나의 오타가 큰 금액 차이로 이어질 수 있으므로, 데이터를 분석하기 전 반드시 정제 과정을 거쳐야 합니다.

2. 오타 및 형식 오류 감지 VBA 코드

이 코드는 사용자가 선택한 범위 내에서 비정상적인 데이터(불필요한 공백 포함, 숫자 형식 오류 등)를 찾아내어 노란색으로 표시해줍니다.

먼저 알트와 F11 키를 눌러 편집기를 열고 아래 코드를 모듈에 복사해 보세요.

 

Sub DetectDataErrors()
    Dim rTarget As Range
    Dim c As Range
    Dim errCount As Long
    
    ' 1. 검사할 데이터 범위를 사용자로부터 입력받습니다.
    On Error Resume Next
    Set rTarget = Application.InputBox("검사할 범위를 선택하세요", Type:=8)
    On Error GoTo 0
    
    If rTarget Is Nothing Then Exit Sub
    
    ' 2. 화면 업데이트를 잠시 멈춰 처리 속도를 높입니다.
    Application.ScreenUpdating = False
    errCount = 0
    
    ' 3. 각 셀을 순회하며 오류를 찾아냅니다.
    For Each c In rTarget
        If Not IsEmpty(c) Then
            ' 앞뒤에 공백이 포함되어 있는지 확인합니다.
            If Len(c.Value) <> Len(Trim(c.Value)) Then
                c.Interior.Color = vbYellow
                errCount = errCount + 1
            ' 숫자가 텍스트 형식으로 저장된 경우를 찾아냅니다.
            ElseIf IsNumeric(c.Value) And VarType(c.Value) = vbString Then
                c.Interior.Color = vbYellow
                errCount = errCount + 1
            ' 출력할 수 없는 특수 문자가 포함되어 있는지 확인합니다.
            ElseIf InStr(1, c.Value, Chr(10)) > 0 Or InStr(1, c.Value, Chr(13)) > 0 Then
                c.Interior.Color = vbYellow
                errCount = errCount + 1
            Else
                c.Interior.ColorIndex = xlNone
            End If
        End If
    Next c
    
    Application.ScreenUpdating = True
    
    If errCount > 0 Then
        MsgBox errCount & "개의 잠재적 오류를 발견했습니다. 노란색 셀을 확인하세요."
    Else
        MsgBox "오류가 발견되지 않았습니다. 깨끗한 데이터입니다."
    End If
End Sub

3. 실무 활용 팁

첫 번째로 공백 처리의 중요성입니다. 위 코드는 셀 앞뒤에 붙은 보이지 않는 공백을 잡아냅니다. 브이룩업 함수가 값을 찾지 못할 때 가장 흔한 원인이 바로 이 공백입니다. 매크로로 오류를 확인한 뒤에는 엑셀의 바꾸기 기능을 이용하거나 파워 쿼리의 다듬기 기능을 병행하면 더욱 완벽한 정제가 가능합니다.

두 번째로 형식 통일입니다. 숫자가 문자로 저장되어 있으면 합계가 계산되지 않거나 차트가 올바르게 그려지지 않습니다. 매크로가 찾아낸 노란색 셀들을 선택하고 엑셀에서 제공하는 숫자로 변환 기능을 클릭하면 데이터 형식을 일괄적으로 맞출 수 있습니다.

4. 마치며

데이터 클렌징은 지루하고 시간이 많이 걸리는 작업처럼 보일 수 있습니다. 하지만 이 과정을 자동화하면 업무의 안정성이 비약적으로 높아집니다. 오늘 공유한 매크로를 통해 여러분의 데이터 관리 수준을 전문가 급으로 끌어올려 보시기 바랍니다. 정확한 데이터에서 정확한 인사이트가 나옵니다.