엑셀 매크로는 사용자의 반복 작업을 덜어줄 수 있는 아주 유용한 기능입니다. 화면에서 셀의 디자인을 동일한 방식으로 변경하거나 데이터 작업을 반복한다면 매크로로 동작을 녹화해서 재사용해 보세요. 또는 특정 작업에 대해 VBA 코드를 모르는 경우 매크로에 의해 기록된 내용으로 어떤 코드를 사용했는지 파악할 수 있습니다. 왜냐하면 사용자의 동작을 기록한 매크로는 VBA 코드이며, 다음에 실행할 때 해당 코드가 실행되기 때문입니다. Show ▼ 매크로 관련 리본 메뉴는 개발 도구탭에 있습니다. 개발 도구 탭의 왼쪽 끝의 코드 그룹 안에 매크로와 매크로 녹화 리본 메뉴입니다. 만약 개발 도구탭이 보이지 않는다면 옵션에서 리본 사용자 지정 값을 변경해야 합니다.  ▼ 파일 탭 > 옵션버튼을 눌러 엑셀 옵션 창을 띄웁니다. 왼쪽 사이드에 리본 사용자 지정 메뉴를 선택한 뒤 오른쪽으로 이동해서 리본 메뉴 사용자 지정 목록에 개발 도구를 체크합니다. ▼ 매크로 기록을 위한 첫 단계는 이름을 만드는 것입니다. 개발 도구 > 매크로 기록리본 메뉴를 눌러 대화상자를 띄우고 이름을 입력합니다. 확인버튼을 누르는 순간 기록을 시작합니다. ▼ 매크로 기록 시작과 함께 셀에 편집 작업을 진행합니다. 리본 메뉴 아이콘도 그림처럼 바뀝니다. 작업이 끝나면 셀 편집을 중지하고 개발 도구 > 코드 그룹으로 가서 매크로 기록 중지 버튼을 클릭합니다. 그럼 자동으로 코드 기록이 멈춥니다. ▼ 반복 작업을 덜어줄 매크로가 만들어 졌습니다. 매크로 함수에 어떤 코드들이 들어가 있는지 확인해 보겠습니다. 매크로 대화 상자를 띄우고 새로 만든 매크로를 선택합니다. 그리고 오른쪽 버튼 목록에서 편집을 클릭합니다. ▼ Visual Basic 편집창이 열리면서 매크로가 기록한 코드로 이동합니다. 매크로 명은 함수명과 동일합니다. 사용자가 직접 매크로 함수를 만들고 싶다면 Sub + 함수명() ~ End Sub 를 입력하고 사이에 코드를 삽입합니다. ▼ 사용법은 간단합니다. 매크로 대화장자를 띄우는 단축키는 Alt + F8입니다. 기존에 저장한 매크로 이름을 선택하고 오른쪽에 실행 버튼을 누르면 화면에 작업을 진행합니다. ▼ 그림처럼 이전에 작업했던 것과 동일하게 자동으로 편집을 완료했습니다. 엑셀은 스프레드 시트라는 프로그램 특성상 대게 많은 데이터를 다루게 됩니다. 이 엑셀 파일의 “데이터 자동화”를 해낼 수만 있다면, 실제 엑셀을 이용한 업무 효율을 크게 늘일 수 있습니다. 이번 글에서는 엑셀 매크로를 활용하여 데이터 관리를 자동화 할 수 있는 방법에 대하여 예시 위주로 알아보도록 하겠습니다. 이번에 소개 드릴 예시는 다음의 3가지 입니다.
※ 엑셀 매크로 활용법과 관련하여서는 아래의 글을 먼저 보시면 이해가 용이합니다. 엑셀 매크로 (VBA) 시작하기 엑셀 VBA를 시작하는 사람들을 위한 팁을 공개합니다. 금번 글에서 완성한 매크로 파일은 다음의 네가지 파일입니다. 1. 같은 양식의 데이터 자동화 취합 하기회사 업무를 할 때 자주 하는 업무 중 하나는 자료의 취합입니다. 특히나 같은 양식의 자료에 대하여 여러 유관 부서가 각각의 부서원에 대한 정보를 추가하는 경우가 왕왕 발생하곤 합니다. 보통 이와 같은 업무를 할 때 여러개의 파일을 하나의 폴더에 모아두고 하나하나 열면서 추가된 내용을 취합용 파일에 붙여 넣는 방식으로 작업을 합니다. 실제 하는 작업을 하나 하나 나열하면 다음과 같이 나열할 수 있겠네요.
이와같이 데이터 자동화 진행할 방향이 잡히고, 업무 순서가 나오면 그 다음 구현은 하나씩 진행해 가면 오히려 쉬울 수도 있습니다. 여기서는 3~4번 작업은 매크로 기록을 활용하여 기본 작업을 만들고, 필요한 부분을 수정한 다음 내용을 추가하는 방식으로 자동화 매크로를 만들어 보도록 하겠습니다. 1) 1차 내 작업 기록 후 수정 (기본 기능 구현)다음과 같이 특정 이벤트 참석자 정보를 회신 받은 파일이 있다고 가정하겠습니다. ※ 참석자 정보 회신용 파일은 Email 버튼을 누르면 Outlook을 통해 자동 회신하게 만들어져 있습니다. 자료 회신 메일 자동 생성기(엑셀 VBA) : 네이버 블로그 그리고, 취합용 파일이 다음과 같이 있다고 가정하겠습니다. 먼저 취합용 파일과 Sample 데이터 파일을 하나 엽니다. 개발도구 → 상대 참조 기록을 클릭하여 활성화 시킨 후 → 매크로 기록 버튼을 누릅니다. 매크로 이름을 정하고 확인 버튼을 누르면 매크로 기록이 시작됩니다. 매크로 기록이 시작되면 기존의 매크로 기록 버튼 위치의 아이콘이 “기록 중지”로 바뀌게 됩니다. 매크로 기록이 된 상태에서 필요한 작업을 하겠습니다. 기록된 매크로를 바로 쉽게 사용하기 위해서는 범위를 설정할때 마우스로 선택하기 보다는 데이터 끝으로 이동(Ctrl+화살표 등)과 같은 조작으로 선택을 해주는 것이 좋습니다. 두개의 파일을 열고 매크로 기록을 시작한 상태에서 저는 작업을 다음과 같이 했습니다.
이상의 작업을 한다음 매크로를 확인하면 아래의 소스와 같이 자동으로 소스파일이 만들어 졌습니다. Sub 매크로1()
'
' 매크로1 매크로
'
'
Windows("참석자 정보 회신용(DC).xlsm").Activate
ActiveCell.Offset(14, -2).Range("A1").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Windows("회신 정보 취합용.xlsm").Activate
ActiveCell.Offset(19, 0).Range("A1").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Rows("1:1").EntireRow.Select
ActiveCell.Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub여기서 다른 소스는 그대로 두고, 다음의 4가지 동작에 대한 소스만 바꿔보도록 하겠습니다.
추가되어야 할 부분은
여기까지 하면 소스는 다음과 같이 구성됩니다. Sub 매크로1()
Dim MyWB As Workbook
Dim OPWB As Workbook
Dim F_dlg As FileDialog
Dim F_Path, F_Name, F_Format As String
Set MyWB = ActiveWorkbook
Set F_dlg = Application.FileDialog(msoFileDialogFolderPicker)
F_dlg.Show
F_Path = F_dlg.SelectedItems(1)
F_Name = Dir(F_Path & "\*.*")
Do While F_Name <> ""
F_Format = Right(F_Name, 4)
If F_Format = "xlsm" Or F_Format = "xlsx" Or F_Format = ".xls" Then
Set OPWB = Workbooks.Open(F_Path & "\" & F_Name)
OPWB.Sheets(1).Range("B5").Select
If Selection.Value <> "" And OPWB.Comments = "201912_참석자" Then
Range("B9999").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
MyWB.Activate
Range("B9999").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Rows("1:1").EntireRow.Select
ActiveCell.Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
ActiveCell.Offset(1, 0).Range("A1").Select
End If
End If
F_Name = Dir()
Loop
End Sub여기까지하면 취합하는 작업은 완료 되었습니다. 2) 2차 내 작업 기록 후 수정 (중복 정보 삭제)그런데, 취합하다보면 같은 사람의 정보가 2번 들어가는 경우도 있을수 있습니다. 중복된 정보를 삭제하기 위한 매크로를 추가해 보겠습니다. 이 부분 또한 고민해서 만들지 말고 다시 매크로 기록을 이용해 작업해 보겠습니다. 매크로 기록을 시작한 상태에서 다시 다음과 같이 작업을 했습니다.
그러면 자동으로 다음과 같은 매크로를 만들어 줍니다. Sub 매크로2()
'
' 매크로2 매크로
'
'
ActiveCell.Offset(2, 0).Range("A1").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Range("$B$5:$E$18").RemoveDuplicates Columns:=Array(1, 2, 3), _
Header:=xlYes
End Sub
다음의 두가지 부분만 소스를 바꾸면 되겠습니다.
변경된 소스를 기존의 소스의 마지막에 추가하고 마무리하면 다음과 같습니다. ub Summarize()
Dim MyWB As Workbook
Dim OPWB As Workbook
Dim F_dlg As FileDialog
Dim F_Path, F_Name, F_Format As String
'현재 Workbook의 변수 선언
Set MyWB = ActiveWorkbook
'취합 폴더 설정
Set F_dlg = Application.FileDialog(msoFileDialogFolderPicker)
F_dlg.Show
F_Path = F_dlg.SelectedItems(1)
F_Name = Dir(F_Path & "\*.*")
'폴더내에서 파일이름읽어 반복하기
Do While F_Name <> ""
F_Format = Right(F_Name, 4)
'확장자 명이 엑셀 파일일때만 실행
If F_Format = "xlsm" Or F_Format = "xlsx" Or F_Format = ".xls" Then
'데이터 Workbook 변수 선언
Set OPWB = Workbooks.Open(F_Path & "\" & F_Name)
OPWB.Sheets(1).Range("B6").Select
'조건에 맞을 경우 취합 작업
If Selection.Value <> "" And OPWB.Comments = "201912_참석자" Then
Range("B9999").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
MyWB.Activate
Range("B9999").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Rows("1:1").EntireRow.Select
ActiveCell.Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
End If
End If
OPWB.Close
F_Name = Dir()
Loop
'중복된 내용 삭제
Range("B9999").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.RemoveDuplicates Columns:=Array(1, 2, 3), _
Header:=xlYes
End Sub여기까지 만들어졌으니 단추를 추가하여 만들어진 매크로를 연결하겠습니다. 개발도구 → 삽입 → 단추를 선택한다음 단추를 그려줍니다. 매크로를 방금 만든 매크로이름을 고른다음 확인을 눌러줍니다. 단추의 이름을 바꿔줍니다. 이제 완성되었습니다. 추가된 단추위에 마우스 커서를 두면 손가락 모양으로 변경되고, 버튼을 누르면 폴더의 엑셀 파일 내용들을 가져와 취합된 자료를 자동으로 만들어 줍니다. 만들어진 매크로 파일은 다음과 같습니다. 2. 다른 양식의 데이터 자동화 취합하기거래처가 다양할 경우 핵심적으로 담고 있어야 하는 내용은 다르지만, 각 거래처별로 미묘하게 양식이 다르게 만들어지는 경우가 있습니다. 그리고, 이러한 값들을 모아서, 취합 정리가 필요할 때가 있습니다. 이때 우리가 취할 수 있는 방법은 하나하나의 양식을 일일히 대조하면서 필요한 위치의 필요한 값을 복사해 오는 것일텐데, 이 또한 규칙성을 찾을 수만 있다면 데이터 자동화 하여 필요 정보를 취합할 수 있습니다. 1) 사례 정리예를 들어 다음과 같이 A, B, C, D의 4군데 마켓이 있고 각각의 마켓에서 다루는 양식이 미묘하게 다른 경우를 생각해 보겠습니다. 이번 예제 파일에서는 한가지 전제 조건은 두겠습니다. 첫번째 줄이 비어있지 않고, 첫번째 줄에 머릿글, 즉 각 항목에 대한 이름이 들어 있다는 가정 하에서 진행하겠습니다. ※ 최소한의 규칙성이 없으면, 실제 문제를 해결해 나가는 것이 쉽지는 않습니다. 비슷하지만 다른 양식을 가지고 있는 각 마켓의 양식에서 내가 사용할 정보가 아래와 같은 정보로 통일시킬 수 있다고 가정하겠습니다. 각각의 양식이 다른 용어로 사용하고 있는 각 항목의 이름을 다음과 같이 정리하여 통합 자료에 Sheet를 하나 추가하여 Sheet 이름은 “Ref”로 지정하여 자료를 정리해 둡니다. 2) 매크로 작성이번 매크로에서는 Data를 Array 형태로 정리하여 풀어볼까 합니다. Array형태로 정의되는 Data Set는 2개가 되겠습니다. 1. Ref Data를 정리하여 필요한 정보를 찾기 위한 Array Data 2. 필요한 파일을 읽었을 때의 데이터 처리를 위한 Array Data 우선 필요한 정보를 가져오는 소스를 정리하면 다음과 같습니다. Sub Total_Order()
Dim MyWB, OPWB As Workbook
Dim Order_Array() As String
Dim Ref_Array(8, 10) As String
Dim F_dir As FileDialog
Dim F_Name As String
Dim E_R, E_C, i, j, k, l, W_R As Integer
'Select active workbook and find out start row of empty range
Set MyWB = ActiveWorkbook
'Select and open File for read data
Set F_dir = Application.FileDialog(msoFileDialogFilePicker)
F_dir.AllowMultiSelect = False
F_dir.Show
F_Name = F_dir.SelectedItems(1)
Set OPWB = Workbooks.Open(F_Name)
'Check Data Range
OPWB.Sheets(1).Range("A60000").Select
Selection.End(xlUp).Select
Selection.End(xlToRight).Select
E_R = Selection.Row
E_C = Selection.Column
'Resize Array veriable
ReDim Order_Array(E_R, E_C) As String
'Read Order veriables
For i = 1 To E_R
For j = 1 To E_C
Order_Array(i, j) = OPWB.Sheets(1).Cells(i, j).Text
Next j
Next i
'Read Reference veriables
For k = 1 To 8
For l = 1 To 10
Ref_Array(k, l) = MyWB.Sheets("Ref").Cells(k, l).Text
Next l
Next k
OPWB.Close
End Sub
데이터를 가져오는 부분에서 Data의 사이즈는 파일을 불러왔을때 불러온 파일의 Data 크기에 따라서 Array 크기를 달라지게 ReDim을 활용하여 사이즈를 재조정하였습니다. Ref Data는 8개 항목에 대하여 정리되어 있으므로, 열방향으로는 8칸, 행방향으로는 10칸으로 임의로 정하여 정리했습니다. 이 영역은 Ref Data가 변경되면 거기에 따라서 맞춰서 조정하면 되겠습니다. 여기까지 하면 필요한 정보는 다 수집이 되었으니, 통합 시트의 첫번째 빈칸을 찾아가서 읽은 데이터들을 비교하여 값을 넣으면 되겠습니다. 첫번째 빈칸은 통합시트의 첫번째 시트에서 첫번째열의 아주 큰 아래쪽의 칸(본 소스에서는 60000번째 칸으로 설정했습니다)에서 xlUp으로 이동하여 Data가 있는 가장 마지막 위치에 커서를 이동시킨 후 한칸 내려오면 첫번째 빈칸 위치가 되겠습니다. 첫번째 빈칸에서 부터 읽어온 Data의 두번째 줄부터 데이터를 가져와 하나하나 비교하여 값을 입력해야 합니다. 각 데이터의 첫번째 줄 값이 Ref의 값중 같은 값이 있는지를 찾아보고 같은 값이 있으면 해당 값이 있는 열에 값을 입력하는 방식을 반복해야 합니다. 여기까지 해서 소스를 정리하면 다음과 같이 정리됩니다. Sub Total_Order()
Dim MyWB, OPWB As Workbook
Dim Order_Array() As String
Dim Ref_Array(8, 10) As String
Dim F_dir As FileDialog
Dim F_Name As String
Dim E_R, E_C, i, j, k, l, W_R As Integer
Dim R_R, R_C As Integer
'Select active workbook and find out start row of empty range
Set MyWB = ActiveWorkbook
MyWB.Sheets(1).Range("A60000").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
W_R = Selection.Row
'Select and open File for read data
Set F_dir = Application.FileDialog(msoFileDialogFilePicker)
F_dir.AllowMultiSelect = False
F_dir.Show
F_Name = F_dir.SelectedItems(1)
Set OPWB = Workbooks.Open(F_Name)
'Check Data Range
OPWB.Sheets(1).Range("A60000").Select
Selection.End(xlUp).Select
Selection.End(xlToRight).Select
E_R = Selection.Row
E_C = Selection.Column
'Resize Array veriable
ReDim Order_Array(E_R, E_C) As String
'Read Order veriables
For i = 1 To E_R
For j = 1 To E_C
Order_Array(i, j) = OPWB.Sheets(1).Cells(i, j).Text
Next j
Next i
'Read Reference veriables
For k = 1 To 8
For l = 1 To 10
Ref_Array(k, l) = MyWB.Sheets("Ref").Cells(k, l).Text
Next l
Next k
'Check order veriables name and fill up right data
For i = 1 To E_R
For k = 1 To 8
For j = 1 To E_C
For l = 1 To 10
If Order_Array(1, j) = Ref_Array(k, l) Then
MyWB.Sheets(1).Cells(W_R, l).Value = Order_Array(i, j)
Exit For
End If
Next l
Next j
Next k
W_R = W_R + 1
Next i
OPWB.Close
End Sub내용을 보시면 아시겠지만… 정말 무식하게 하나하나 비교하면서 대조작업을 합니다. 만들어진 파일은 다음과 같습니다. 3) 데이터 자동화 추가 보완여기까지 하면 기본적인 기능은 구현이 되었는데, 몇가지 보완이 필요하여 추가 보완 하였습니다.
만들어진 소스파일은 다음과 같습니다. Sub Total_Order()
Dim MyWB, OPWB As Workbook
Dim Order_Array() As String
Dim Ref_Array() As String
Dim F_dir As FileDialog
Dim Fold_Name, F_Name As String
Dim E_R, E_C, i, j, k, l, W_R As Integer
Dim R_R, R_C As Integer
'Select active workbook and find out start row of empty range
Set MyWB = ActiveWorkbook
MyWB.Sheets(1).Select
Range("A60000").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
W_R = Selection.Row
'Ref Table 정보 범위 설정
MyWB.Sheets("Ref").Select
Range("A1").Select
Selection.End(xlToRight).Select
R_C = Selection.Column
R_R = 1
For l = 1 To R_C
k = 1
Do While Cells(k, l).Value <> ""
If k > R_R Then R_R = k
k = k + 1
Loop
Next l
'Ref Array 정보 수집
ReDim Ref_Array(R_R, R_C) As String
'Read Reference veriables
For k = 1 To R_R
For l = 1 To R_C
Ref_Array(k, l) = MyWB.Sheets("Ref").Cells(k, l).Text
Next l
Next k
'Select and open Folder
Set F_dir = Application.FileDialog(msoFileDialogFolderPicker)
F_dir.AllowMultiSelect = False
F_dir.Show
Fold_Name = F_dir.SelectedItems(1)
'Open File and Repeat
F_Name = Dir(Fold_Name & "\*.xl*")
Do While F_Name <> ""
Set OPWB = Workbooks.Open(Fold_Name & "\" & F_Name)
'Check Data Range
OPWB.Sheets(1).Range("A60000").Select
Selection.End(xlUp).Select
E_R = Selection.Row
OPWB.Sheets(1).Range("A1").Select
Selection.End(xlToRight).Select
E_C = Selection.Column
'Resize Array veriable
ReDim Order_Array(E_R, E_C) As String
'Read Order veriables
For i = 1 To E_R
For j = 1 To E_C
Order_Array(i, j) = OPWB.Sheets(1).Cells(i, j).Text
Next j
Next i
'Check order veriables name and fill up right data
For i = 2 To E_R
For k = 1 To R_R
For j = 1 To E_C
For l = 1 To R_C
If Order_Array(1, j) = Ref_Array(k, l) Then
MyWB.Sheets(1).Cells(W_R, l).Value = Order_Array(i, j)
Exit For
End If
Next l
Next j
Next k
W_R = W_R + 1
Next i
OPWB.Close
F_Name = Dir()
Loop
End Sub
3. 중복 데이터 자동화 정리하기이력 관리 데이터를 관리하다 보면 시작과 끝 시점은 관리 해야 하지만, 중간의 중복 정보는 걸러내야 하는 경우가 있습니다. 이와 같은 작업 또한 사람이 하나 하나 정리하게 되면 꽤 지루한 반복 작업의 연속일 수 있습니다. 루틴하게 반복되는 데이터 정리 작업을 매크로 기록 기능과 수정을 활용하여 해당 기능을 구현해 보겠습니다. 1) 사례 분석아래 이미지와 같이 어떤 기업체의 정보들이 있을때, 같은 평가이지만, 기간에 따라 다른 평가가 들어갈 경우 가장 먼저 일어난 평가와 가장 마지막에 있었던 평가만 볼 수 있게 정리하는 케이스를 생각해 보겠습니다.. 아래 유첨 파일과 같은 가상의 정보에 대하여 이와 같은 작업을 실행한다고 가정하겠습니다. 이 작업을 수작업으로 매번 반복한다고 생각해 보면, 저는 아마 다음과 같은 순서로 작업을 하게 될 것 같습니다.
위와 같은 작업을 할때 가장 고민을 해야 하는 부분은 어떤 줄을 삭제 할 것인가, 우선 1, 2번 작업을 통하여 이미 법인번호와 발행일에 대해서 정렬되었기 때문에, 연속된 같은 법인 번호의 경우 데이터중 가장 처음과 가장 마지막값만을 남기면 되겠습니다. 사람이 작업한다면 이와 같은 기준만으로도 명확히 작업 할 수 있는데, 컴퓨터에게 일을 시키기 위해서는 한단게 더 일을 잘게 쪼개서 여러번 작업해야 하지만, 기준이 명확하게 일을 재정의하여 시킬 필요가 있습니다. 제가 생각한 로직은 다음과 같습니다.
여기서 반복 작업을 해야 하는 부분은 For 문 또는 Do While 문으로 감싸고, 실질적인 작업을 하는 부분은 매크로 기록을 통해 처리하면 우리가 원하는 작업을 매크로로 만들 수 있을 것 같습니다. 2) 매크로 기록하기먼저, 내가 작업하고자 하는 부분을 매크로 기록을 통하여 기록으로 남겨 보겠습니다. 개발도구 → 매크로 기록을 선택하고, 적절한 이름을 정한 후 확인 버튼을 누릅니다. ※ 개발도구 메뉴가 안보이실 경우에는 파일 → 옵션 → 리본 사용자 지정에서 개발도구의 체크박스를 선택해 주셔야 합니다. 이제부터는 하고자하는 동작을 순차적으로 입력하면 되겠습니다. 데이터가 있는 Sheet를 선택하여 마우스 우클릭 → 이동/복사 → 복사본 만들기 → 확인 데이터가 있는 행을 모두 선택한다음 데이터 → 정렬을 선택합니다. 법인번호와 발행일을 기준으로 정렬할 것이기 때문에 열C와 열K를 정렬 기준으로 선택한 다음 확인을 누릅니다. 정렬 경고가 뜨면 모두 숫자로 정렬을 선택하여 정렬합니다. ※ 이때 정렬 기준은 데이터 형태에 따라서 맞춰서 구분하면 되겠습니다. 이번 예시 자료의 경우 모두 숫자로 인식해도 문제가 없기 때문에 숫자로 정렬 기준으로 하였습니다. 정렬이 완료되면, 두번째 줄을 마우스 우클릭한다음 삭제 버튼을 누르고 개발도구 → 기록 중지를 누릅니다. 여기까지 마무리하면 자동으로 소스가 만들어져 있습니다. Sub 매크로1()
'
' 매크로1 매크로
'
'
Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Sheets(1)
Rows("3:29").Select
ActiveWorkbook.Worksheets("Sheet1 (2)").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1 (2)").Sort.SortFields.Add2 Key:=Range( _
"C3:C29"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
ActiveWorkbook.Worksheets("Sheet1 (2)").Sort.SortFields.Add2 Key:=Range( _
"K3:K29"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Sheet1 (2)").Sort
.SetRange Range("A3:AD29")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Rows("4:4").Select
Selection.Delete Shift:=xlUp
End Sub
3) 데이터 자동화 수정하기이제 이 소스에서 우리가 손봐야 하는 부분을 확인해 보겠습니다. 먼저 Sheets(“Sheet1”)이라고 표시된 값은 Sheet 이름에 따라 바뀔수 있으니, Sheets(1)라는 형태로 첫번째 Sheet로 바꿔 주겠습니다. 이후의 Sheets(“Sheet1 (2)”) 라고 표시된 부분 역시 Sheets(1)로 형태를 바꾸겠습니다. 정렬을 정확히 하기 위해서는 데이터가 있는 줄을 모두 선택해야 하는데, 데이터가 있는 첫번째 줄은 3번째줄로 고정되어 있으니, 이값은 그대로 쓰면 되는데, 문제는 마지막 줄입니다. 마지막 줄이 현재 위의 코드에서는 29번째 줄로 되어 있으니, 29라고 표시된 값은 모두 데이터 크기에 따라 가변적으로 움직일 수 있게 만들어 주면 되겠습니다. C3 칸을 선택한 상태에서 데이터가 있는 가장 아래칸 까지 이동, 현재의 행 위치 정보를 가져와서 그 값을 행의 마지막 값으로 기억하게 하면 되겠습니다. 여기까지를 수정하면 다음과 같습니다. ※ 매크로 이름도 Automation이라고 바꾸겠습니다. Sub Automation()
'
' 매크로1 매크로
'
'
Dim E_Row As Integer
Sheets(1).Select
Sheets(1).Copy Before:=Sheets(1)
E_Row = Range("C3").End(xlDown).Row
Rows("3:" & E_Row).Select
ActiveWorkbook.Worksheets(1).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(1).Sort.SortFields.Add2 Key:=Range( _
"C3:C" & E_Row), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
ActiveWorkbook.Worksheets(1).Sort.SortFields.Add2 Key:=Range( _
"K3:K" & E_Row), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets(1).Sort
.SetRange Range("A3:AD" & E_Row)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Rows("4:4").Select
Selection.Delete Shift:=xlUp
End Sub
이제, 데이터를 삭제하는 부분을 로직으로 감싸주면 되겠습니다. 첫번째 조건으로 현재행의 위치를 하나의 변수로 두고 해당 행의 법인 번호가 아무값이 없을때 까지 반복하는 Do 문을 만들고, 그 안에 조건문으로 같은 법인 번호로 값이 3개 이상인 경우에만 현재 행바로 앞의 행 값을 삭제하는 로직을 넣어주면 되겠습니다. 여기까지 소스로 만들어주면 다음과 같습니다. Sub Automation()
'
' 매크로1 매크로
'
'
Dim E_Row As Integer
Dim C_Row As Integer
Sheets(1).Select
Sheets(1).Copy Before:=Sheets(1)
E_Row = Range("C3").End(xlDown).Row
Rows("3:" & E_Row).Select
ActiveWorkbook.Worksheets(1).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(1).Sort.SortFields.Add2 Key:=Range( _
"C3:C" & E_Row), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
ActiveWorkbook.Worksheets(1).Sort.SortFields.Add2 Key:=Range( _
"K3:K" & E_Row), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets(1).Sort
.SetRange Range("A3:AD" & E_Row)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
C_Row = 5
Do While Range("C" & C_Row).Value <> ""
If Range("C" & C_Row).Value = Range("C" & C_Row - 1).Value And Range("C" & C_Row).Value = Range("C" & C_Row - 2).Value Then
Rows(C_Row - 1 & ":" & C_Row - 1).Select
Selection.Delete Shift:=xlUp
Else
C_Row = C_Row + 1
End If
Loop
End Sub만들어진 소스 파일은 다음과 같습니다. 이미지 일괄 삽입, 일괄 저장 (엑셀 매크로) 엑셀 매크로를 이용하여 엑셀내에 이미지를 일괄 삽입하거나, 엑셀내의 이미지를 일괄 저장하는 매크로를 만들어 봤습니다. |
엑셀 자동입력 매크로 - egsel jadong-iblyeog maekeulo
관련 게시물
저작권 © 2024 toptenid.com Inc.
