직장인 실무에서 유용한 고급 엑셀 매크로 기능 예제 모음 (심화편)

 

🔧 실무에서 유용한 고급 엑셀 매크로 기능 모음 (심화편)

반복 작업 줄이기, 보고서 자동화, 데이터 가공까지 한 번에!

👩‍💻 엑셀 매크로란?

**엑셀 매크로(Macro)**는 반복되는 작업을 자동화해주는 기능입니다.
단축키나 VBA 코드를 이용해 “한 번의 클릭”으로
💡 수작업을 줄이고, 업무 시간을 확 줄일 수 있어요.

 

🧠 매크로를 써야 하는 이유

✔️ 반복적인 복사 붙여넣기 작업
✔️ 매달 정기적인 보고서 양식 채우기
✔️ 여러 시트를 합치거나, 나누거나, 정렬해야 할 때
✔️ 대량 데이터를 필터링하거나 가공할 때

 

🛠️ 실무에 바로 쓰는 고급 매크로 기능 

 

1. 여러 엑셀 파일을 하나로 통합하는 매크로

📌 업무 예시: “부서별 실적 파일을 하나의 파일로 모아서 보고해야 할 때”

Sub 통합하기()
    Dim 파일 As String
    Dim 폴더 As String
    Dim 통합시트 As Worksheet
    Set 통합시트 = ThisWorkbook.Sheets(1)
    폴더 = "C:\보고서\" ' 경로 수정
    파일 = Dir(폴더 & "*.xlsx")
    
    Do While 파일 <> ""
        Workbooks.Open 폴더 & 파일
        ActiveSheet.UsedRange.Copy 통합시트.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        Workbooks(파일).Close False
        파일 = Dir
    Loop
End Sub

---

2. 시트별로 자동 분리하여 저장하는 매크로

📌 업무 예시: “부서별 시트를 각각의 파일로 저장해야 할 때”

Sub 시트별_파일저장()
    Dim sht As Worksheet
    For Each sht In ThisWorkbook.Sheets
        sht.Copy
        ActiveWorkbook.SaveAs "C:\분리파일\" & sht.Name & ".xlsx"
        ActiveWorkbook.Close False
    Next sht
End Sub

---

3. 반복 필터 & 정렬 자동화

📌 업무 예시: “매주 특정 조건으로 필터 후 정렬하는 작업 반복 시”

Sub 자동필터정렬()
    With ActiveSheet
        .Range("A1").AutoFilter Field:=3, Criteria1:="완료"
        .Range("A1").CurrentRegion.Sort Key1:=Range("B2"), Order1:=xlDescending
    End With
End Sub

---

4. 날짜 기준으로 자동 시트 분리

📌 업무 예시: “월별 데이터를 시트별로 자동 나누고 싶은 경우”

Sub 월별분리()
    Dim rng As Range, cell As Range
    Set rng = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)

    For Each cell In rng
        If Not WorksheetExists(Format(cell.Value, "yyyy-mm")) Then
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = Format(cell.Value, "yyyy-mm")
        End If
        cell.EntireRow.Copy Sheets(Format(cell.Value, "yyyy-mm")).Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Next
End Sub

Function WorksheetExists(wsName As String) As Boolean
    On Error Resume Next
    WorksheetExists = Not Worksheets(wsName) Is Nothing
    On Error GoTo 0
End Function

---

5. 자동 보고서 이메일 첨부 전송 (Outlook 연동)

📌 업무 예시: “주기적으로 보고서를 메일로 자동 전송할 때”

Sub 메일전송()
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = "someone@email.com"
        .Subject = "자동 보고서 전송"
        .Body = "첨부된 보고서를 확인해 주세요."
        .Attachments.Add "C:\보고서\이번주보고서.xlsx"
        .Send
    End With
End Sub

---

6. 데이터 중복 제거 & 정렬 자동화

📍 예시: 매일 고객명단 중복 제거 후 이름순 정렬하는 경우

Sub 중복제거_정렬()
    With ActiveSheet
        .Range("A1:A" & .Cells(Rows.Count, 1).End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlYes
        .Range("A1").CurrentRegion.Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlYes
    End With
End Sub

---

7. 빈 셀 자동 채우기 (위 값으로)

📍 예시: 보고서에 누락된 값이 많을 때 한 번에 보완

Sub 빈셀_위값채우기()
    Dim rng As Range
    For Each rng In Selection
        If rng.Value = "" Then
            rng.Value = rng.Offset(-1, 0).Value
        End If
    Next rng
End Sub

✔️ 선택 영역에서만 동작하므로, A열 전체 선택 후 실행하세요.

---

8. 동적 차트 자동 업데이트 매크로

📍 예시: 차트 범위를 매번 수동으로 바꾸지 않도록

Sub 차트_자동범위업데이트()
    Dim sht As Worksheet
    Set sht = Sheets("Sheet1")
    
    sht.ChartObjects("Chart 1").Chart.SetSourceData _
        Source:=sht.Range("A1:B" & sht.Cells(Rows.Count, 1).End(xlUp).Row)
End Sub

---

9. 행 단위 조건부 서식 자동 적용

📍 예시: 특정 조건이 맞는 행만 강조 (예: 상태 = “지연”)

Sub 지연_강조()
    Dim i As Long
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        If Cells(i, 3).Value = "지연" Then
            Rows(i).Interior.Color = RGB(255, 200, 200)
        End If
    Next i
End Sub

---

10. 이메일 주소 자동 추출 매크로 (텍스트 중에서)

📍 예시: 텍스트 문장에서 이메일 주소만 뽑아야 할 때

Function 이메일추출(문장 As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    
    With regex
        .Global = True
        .Pattern = "\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}\b"
        .IgnoreCase = True
    End With
    
    If regex.Test(문장) Then
        이메일추출 = regex.Execute(문장)(0)
    Else
        이메일추출 = ""
    End If
End Function

👉 셀에서 =이메일추출(A2)처럼 함수로 사용 가능!

---

11. 파일 자동 저장 백업 매크로

📍 예시: 작업 중 수시로 자동 저장되는 기능 구현

Sub 자동저장()
    Dim 경로 As String
    경로 = "C:\엑셀백업\Backup_" & Format(Now, "yyyymmdd_hhmmss") & ".xlsm"
    ThisWorkbook.SaveCopyAs 경로
    MsgBox "백업 완료!", vbInformation
End Sub

---

12. 워크시트 보호/해제 매크로

📍 예시: 특정 셀만 수정 가능하도록 자동 보호 적용

Sub 시트보호()
    ActiveSheet.Protect Password:="1234", UserInterfaceOnly:=True
End Sub

Sub 시트보호해제()
    ActiveSheet.Unprotect Password:="1234"
End Sub

✔️ 버튼에 연결해서 사용하면 편리!

---

🧠 실무 팁 & 정리

💡 매크로는 작은 루틴부터 시작	매일 반복하는 작업 1개만 자동화해도 효율성 급상승
🧾 명확한 범위지정	UsedRange, Cells(Rows.Count, 1).End(xlUp) 등 자주 활용
⚠️ 오류 대비	On Error Resume Next 구문 활용으로 예상 오류 처리 가능
💬 설명 주석 필수	실무 매크로는 다른 사람과 공유도 고려해야 함!

 

🔒 매크로 실행 전	보안 설정에서 ‘매크로 사용 허용’ 체크 필수!
💡 실습은 복사본으로	원본 파일은 백업하고 테스트하세요
🧠 단축키 지정	자주 쓰는 매크로는 단축키로 연결해서 바로 실행 가능
⌨️ 개발도구 탭	리본 메뉴에서 ‘개발도구’ 탭을 활성화해야 VBA 편집 가능

 

 

✨ 정리

매크로는 ‘엑셀 고수’만 쓰는 기능이 아니에요.

자주 하는 일이라면 매크로로 바꿔버리는 것,
그게 곧 ‘일 잘하는 직장인’의 핵심 스킬입니다 💼