[VBA] 엑셀 Raw Data 자동 편집 프로그램 만들기 (업무 효율↑)

 

# 목표
 - 매크로를 통해서 Raw Data 자동으로 편집하는 프로그램 구축 (VBA 코드 수정)
 - 한번 편집된 Sheet의 경우, 다시 실행되지 않도록 VBA 코드 추가 (조건문)
 - 하나의 문서가 아닌 엑셀의 모든 문서에서 매크로 사용하도록 설정 (Excel 추가 기능)

 

[ Raw Data 자동 편집 프로그램 구현 (예시) ]

 

---

 

1. Raw Data 자동 편집 프로그램 만들기

Excel 내 많은 데이터들을 일정한 규칙으로 편집하는 프로그램을 자동 매크로를 통해 만들고, VBA 코드를 수정 및 보완하면서 완성도를 높여보자. 

1-1. 셀 데이터 이동과 행 삭제 매크로 기록 (상대참조)

  위의 최종 구현 영상에서 사용된 데이터는 가상의 고객사 정보로, 불규칙하게 흩어져있는 '주소', '우편번호', '담당자' 데이터를 '상대참조 매크로'를 이용하여 셀 데이터 이동 및 불필요한 행 삭제하는 VBA코드를 생성한 후, 코드를 수정해보자.

 

 → 상대참조 관련 내용은 아래 링크에서 참고! 복습!

2022.07.06 - [Data Analyst/VBA] - [VBA] 엑셀 매크로와 VBA에 꼭 필요한 기초 자동화 (초급)

[VBA] 엑셀 매크로와 VBA에 꼭 필요한 기초 자동화 (초급)

 

[ Raw Data 자동 편집(한줄씩) 코드 ]

 

위의 코드는 데이터 정리를 한 줄씩 원하는 카테고리에 맞게 정리한 코드이다. 여기서 중요한 것은 매크로를 반복 실행할 때마다 실행될 셀의 위치가 변경됨으로 마지막에는 다시 실행될 셀의 위치를 지정해줘야 한다는 것이다! (상대 참조)

 

- ActiveCell.Offset(-1,1).Range("A1") 
 : 현재 선택된 셀에서부터 지정한 행 & 열 수만큼 이동하는 셀을 지칭 - Offset(이동 행 수, 이동 열 수)
 → Offset(이동 행 수, 이동 열 수). 해당 코드에서는 행방향은 위로 한 칸, 열방향은 오른쪽으로 한 칸.
 → Range("A1")은 가상 셀을 지정한 것이므로 삭제해도 무방하다.

- Selection.Cut Destination:=ActiveCell.Offset(-1,1).Range("A1")
 : Selection 개체에서 Cut 메서드에 의해서 지정한 Offset 만큼 데이터를 이동시킨다. 
 → 위와 동일하게 Range("A1")은 자동 매크로로 인해 생성된 가상 셀로 삭제하면 된다.

- ActiveCell.Offset(-2,0).Rows("1:3").EntireRow.Select 
 : 불필요한 행을 삭제하기 위한 행 범위 선택(1~3행)

- Selection.Delete Shift:=xlUp  : 선택한 범위의 불필요한 행 삭제

1-2. 반복문과 조건문을 추가하여 프로그램 완성도 높이기

Do ~ Loop 반복문을 사용하여 셀에 데이터가 있는 동안 작업이 반복될 수 있도록 설정하고, If 조건문을 통해 이미 편집된 Sheet의 경우 더이상 추가적인 편집이 되지 않도록 설정해보자.

 위의 코드는 Raw data를 한 줄씩 정리한 것이므로, 모든 데이터에 대해 매크로를 매번 실행해야 하는 번거로움이 있기 때문에 Do ~ Loop 반복문을 통해 'One-Click'으로 한번에 정리될 수 있도록 해보자. (아래 코드 참고)

 

 

위 사진은 Do ~ Loop 반복문을 적용한 코드이며, 셀에 빈셀이 아닐 동안 수행하도록 설정한다.
추가적으로 ScreenUpdating 속성을 추가해서 더 깔끔하고 빠르게 실행될 수 있도록 하자.

- Application.ScreenUpdating = False
 : 'Application' 라는 개체는 액셀 프로그램을 뜻하며, 'ScreenUpdating' 속성은 데이터 편집이 이루어지면서 데이터가 업데이트되는 것을 표시할 것인지, 표시하지 않을 것인지 설정하는 역할을 한다.
 → False : 표시하지 않기,  True : 표시하기
 → 작업을 수행할 때는 표시하지 않지만, 모두 완료되었을 때는 편집된 데이터를 바로 업데이트하기!

 

[ 데이터 자동 편집 최종 코드 (예시) ]

 

위 코드처럼, 편집 완료된 Sheet에 한에서 더이상 편집되지 않도록 조건문을 추가하고 메세지 박스를 추가함으로써 프로그램의 완성도를 높이자. (만약, 2번 이상의 편집이 진행되면 데이터 손상될 수 있음)

- If Range("C2").Value <> "" Then
 MsgBox "편집 완료 시트입니다."
 Exit Sub
 End If 
 : 이미 편집이 된 것인지 아닌지를 판단하기 위한 조건문으로, 실습에 사용된 데이터에는 C2 셀 내 데이터값의 유무에 따라서 판단할 수 있도록 했다. (즉, C2셀에 데이터가 있을 시 : 편집 완료,  없을 시 : 편집 전)

# If 조건문의 3가지 형식 ***

1) If  (조건)  Then  참일 때  (실행문)
 : 하나의 조건과 하나의 실행문일 경우                                       
                                                                                                    
2) If  (조건)  Then
  참일 때  (실행문1)
  참일 때  (실행문2)  ...
  End If
 : 조건은 하나지만, 조건을 만족할 때 실행문이 2개 이상일 경우

3) If  (조건)  Then
  참일 때  (실행문)
  Else
  거짓일 때 (실행문)
  End If
 : 조건은 하나지만, True/False에 따른 실행문이 다를 경우

 

2. Excel 추가 기능을 통한 매크로 실행하기

만약 생성한 매크로가 특정 엑셀문서에서만 사용하는 것이 아니라 다른 엑셀문서에서도 공통으로 사용할 기능이라면, 매크로를 Excel 추가 기능에 저장해두고 사용하면 매우 용이하다.

 

 

 '파일 - 다른 이름으로 저장'에 들어가서 파일 형식 = "Excel 추가 기능(*.xlam)"으로 변경/저장!
 → 이렇게 저장하면 시트의 내용이 아닌 매크로가 저장됨으로, 
 매크로가 없는 다른 엑셀파일(xlsx)에서 추가 기능에 저장했던 매크로를 실행할 수 있게 된다.

 이렇게 생성한 엑셀 추가기능으로 저장된 매크로 기능은,
'개발도구 - Excel 추가 기능'을 클릭하여 추가 기능에 저장했던 매크로를 활성화해서 실행하면 된다!

 더불어 자주 쓰는 매크로의 경우에는 '빠른 실행 도구 모음'에 추가해서 더 편리하게 사용할 수도 있다.
위에 사진처럼 매크로를 엑셀 화면 상단에 추가해서 아이콘을 클릭만 한다면, 매크로가 바로 실행된다.