엑셀 매크로 대량 데이터 정리

수백, 수천 개의 엑셀 데이터를 수동으로 정리하면 시간이 오래 걸릴 뿐만 아니라, 작은 실수 하나가 전체 데이터의 정확도를 떨어뜨릴 수 있습니다.

이럴 때 엑셀 매크로(VBA)는 고객 데이터의 이메일 형식 검증, 불필요한 공백 제거, 중복값 제거, 그리고 자동 정렬까지 한 번에 처리할 수 있습니다. 

 

왜 엑셀 매크로로 데이터 정리가 필요할까요?

데이터는 비즈니스의 핵심입니다. 

하지만 부정확하거나 일관성 없는 데이터는 오히려 혼란을 초래할 수 있습니다. 

특히 고객 정보와 같이 중요한 데이터를 다룰 때는 더욱 신중해야 합니다. 

엑셀 매크로는 이러한 데이터 정리 작업을 자동화하여 다음과 같은 이점을 제공합니다.

• 시간 절약: 반복적인 수동 작업을 없애고 단 몇 초 만에 대량의 데이터를 정리할 수 있습니다.
• 오류 감소: 사람의 실수로 발생할 수 있는 오타나 잘못된 형식의 데이터를 자동으로 수정하거나 걸러낼 수 있습니다.
• 일관성 유지: 모든 데이터가 동일한 규칙과 형식에 따라 정리되어 일관성을 유지할 수 있습니다.
• 업무 효율성 증대: 데이터 정리 시간을 줄여 다른 중요한 업무에 집중할 수 있도록 돕습니다.

 

실전 예제: 고객 데이터 정리 매크로

이제 고객 데이터를 효과적으로 정리할 수 있는 엑셀 매크로 코드를 살펴보겠습니다. 

이 매크로는 '고객 데이터'라는 이름의 시트에 있는 데이터를 대상으로 하며, 이메일 주소, 고객명 등 다양한 열의 데이터를 한 번에 정리해 줍니다.

1. VBA 편집기 열기

엑셀에서 Alt + F11을 눌러 VBA 편집기를 엽니다. 왼쪽 프로젝트 탐색기에서 해당 통합 문서(예: VBAProject (개인.xlsb) 또는 현재 작업 중인 통합 문서)를 선택한 후, 마우스 오른쪽 버튼을 클릭하여 삽입(I) > 모듈(M)을 선택합니다. 

새로 생성된 모듈에 아래 코드를 붙여넣으세요.

2. 매크로 코드

Sub CleanCustomerData()

    ' 매크로 실행 속도 향상
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim emailCol As Long ' 이메일 열 번호 (예: D열은 4)
    Dim nameCol As Long ' 고객명 열 번호 (예: B열은 2)

    ' 시트 이름 설정 (여기서는 '고객 데이터' 시트를 가정)
    Set ws = ThisWorkbook.Sheets("고객 데이터")

    ' 이메일 주소와 고객명이 있는 열을 지정합니다. (예: 이메일은 D열, 고객명은 B열)
    emailCol = 4
    nameCol = 2

    ' 마지막 행 찾기 (A열을 기준으로)
    lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row

    ' 1. 공백 제거 및 이메일 형식 검증
    For i = 2 To lastRow ' 2행부터 시작 (1행은 헤더)
        ' 이메일 열의 앞뒤 공백 제거
        ws.Cells(i, emailCol).Value = Trim(ws.Cells(i, emailCol).Value)

        ' 고객명 열의 앞뒤 공백 제거
        ws.Cells(i, nameCol).Value = Trim(ws.Cells(i, nameCol).Value)

        ' 이메일 형식 검증 (간단한 @ 및 . 포함 여부 확인)
        If InStr(ws.Cells(i, emailCol).Value, "@") = 0 Or InStr(ws.Cells(i, emailCol).Value, ".") = 0 Then
            ' 유효하지 않은 이메일인 경우 특정 셀에 경고 메시지 또는 셀 색상 변경
            ' 여기서는 이메일 셀 옆에 '형식 오류'라고 입력하도록 예시합니다.
            ws.Cells(i, emailCol + 1).Value = "이메일 형식 오류"
            ' 또는 ws.Cells(i, emailCol).Interior.Color = RGB(255, 255, 0) ' 노란색으로 변경
        End If
    Next i

    ' 2. 중복값 제거 (이메일 열을 기준으로)
    ' 엑셀의 RemoveDuplicates 기능을 사용합니다.
    ' 첫 번째 인수는 중복 제거할 열의 배열입니다. (여기서는 이메일 열인 emailCol)
    ' Header:=xlYes는 첫 행이 헤더임을 의미합니다.
    ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, ws.Columns.Count)).RemoveDuplicates Columns:=Array(emailCol), Header:=xlYes

    ' 중복 제거 후 마지막 행 다시 찾기
    lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row

    ' 3. 데이터 자동 정렬 (고객명을 기준으로 오름차순 정렬)
    ' 전체 데이터를 선택하고 정렬합니다.
    With ws.Sort
        .SortFields.Clear ' 기존 정렬 필드 초기화
        .SortFields.Add Key:=ws.Range(ws.Cells(1, nameCol), ws.Cells(lastRow, nameCol)), _
                         SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, ws.Columns.Count))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    ' 매크로 실행 속도 원상복구
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    MsgBox "고객 데이터 정리가 완료되었습니다!", vbInformation

End Sub

 

코드 설명:

1. 초기 설정:
   - Application.ScreenUpdating = False, Application.Calculation = xlCalculationManual: 매크로 실행 중 화면 업데이트와 자동 계산을 중지하여 속도를 향상시킵니다.
   - ws: '고객 데이터' 시트를 지정합니다. 시트 이름을 실제 사용하는 이름으로 변경해 주세요.
   - emailCol, nameCol: 이메일과 고객명이 있는 열의 번호를 지정합니다. 예를 들어 D열이면 4, B열이면 2로 설정합니다. 실제 데이터가 있는 열 번호에 맞춰 수정해야 합니다.
   - lastRow: 데이터의 마지막 행을 자동으로 찾습니다.
2. 공백 제거 및 이메일 형식 검증:
   - Trim() 함수를 사용하여 이메일 주소와 고객명의 앞뒤 공백을 제거합니다.
   - InStr() 함수를 사용하여 이메일 주소에 @와 .이 포함되어 있는지 간단하게 검증합니다. 만약 유효하지 않은 형식이라면 해당 셀 옆(예: emailCol + 1 열)에 "이메일 형식 오류"라는 메시지를 입력하거나 셀 색상을 변경할 수 있습니다. 더 정교한 이메일 검증이 필요하다면 정규식(Regular Expression)을 사용할 수 있습니다.
3. 중복값 제거:
   - RemoveDuplicates 메소드를 사용하여 이메일 열(Columns:=Array(emailCol))을 기준으로 중복된 행을 제거합니다. Header:=xlYes는 첫 행이 헤더임을 나타냅니다.
4. 데이터 자동 정렬:
   - ws.Sort 개체를 사용하여 데이터를 정렬합니다.
   - SortFields.Add: 고객명 열(nameCol)을 기준으로 오름차순(xlAscending)으로 정렬하도록 설정합니다.
   - SetRange: 정렬할 데이터 범위를 지정합니다.
   - Header = xlYes: 첫 행이 헤더이므로 정렬에서 제외합니다.
   - Apply: 정렬을 적용합니다.

• Application.ScreenUpdating = True, Application.Calculation = xlCalculationAutomatic: 매크로 실행 후 화면 업데이트와 자동 계산을 다시 활성화합니다.
• MsgBox: 데이터 정리가 완료되었음을 알리는 메시지 상자를 띄웁니다.

매크로 실행 방법

• VBA 편집기에서 실행: VBA 편집기에서 CleanCustomerData 매크로 안에 커서를 두고 F5 키를 누르거나, 상단 도구 모음에서 실행 버튼(초록색 삼각형)을 클릭합니다.
• 엑셀에서 실행: 엑셀로 돌아와 개발 도구 탭 > 매크로를 클릭합니다. CleanCustomerData를 선택하고 실행 버튼을 클릭합니다.
• 버튼에 연결: 자주 사용하는 매크로라면 엑셀 시트에 버튼을 만들고 해당 버튼에 CleanCustomerData 매크로를 연결하여 편리하게 사용할 수 있습니다.

 

이제 더 이상 수백, 수천 개의 엑셀 데이터를 일일이 손으로 정리하는 데 시간을 낭비하지 마세요! 

엑셀 매크로를 활용하면 이메일 검증, 공백 제거, 중복값 제거, 자동 정렬까지 모든 과정을 단 한 번의 클릭으로 자동화할 수 있습니다. 오늘 소개된 실전 예제를 통해 당신의 데이터 관리 업무가 훨씬 더 빠르고 정확해지길 바랍니다.