rand(life)
[vba] 통합기능을 VBA로 본문
핵심코드의 출처는 여기
이런 세 개의 파일이 있다고 하자. 그리고 이 세 개의 파일에 있는 데이터를 통합하여 아래와 같은 표를 완성해야한다고 하자.
왼쪽의 item이나 상단의 title이 파일마다 순서가 다르다. 그리고 모두 다 있는 것도 아니다.
이런 경우, 어떤 식으로 데이터를 모으겠는가?
sumif, sumifs같은 것을 사용할 수도 있을 것이다. 그러나, 방금 말한 것처럼, item과 title의 구성 및 순서가 달라서 쉽지 않아보인다.
이런 경우 사용할 수 있는 것이 엑셀의 "통합" 기능이다.
데이터 탭에 있다. 이것을 누르면
이런 창이 뜨고, 여기서 참조 영역과 함수를 적절히 지정해주고 확인하면 된다.
그런데, 위의 표에서 보듯이 참조 영역이 불규칙적이어서, 일일이 파일을 열어서 확인을 해야한다. 게다가, 어느날은 4행까지있고, 어느날은 7행까지 있을 수도 있다.
이럴때 VBA(매크로)의 힘을 빌려야한다.
단, 조건이 있다.
1. 통합할 것들의 이름은 같아야한다. 예를 들어, title1 과 title 1은 가운데 공백이 한칸 들어간 것만 다르다. 사람은 이 두가지를 같은 것이라고 생각할 수 있지만 엑셀은 서로 다른 것으로 취급한다.
2. 각 파일에서 통합할 데이터는 첫번째 시트에 있다고 가정했다.
set ws = wb.Sheets(1)
만약, 시트의 순서가 달라질 수 있지만 이름은 동일하다면
set ws = wb.Sheets("데이터")
이런식으로 주어야할 것이다.
3. 통합이 끝날때까지는 파일을 열어두어야한다는 점. 물론 통합이 끝나면 현재파일을 제외한 나머지 엑셀파일은 닫도록 코딩했지만, 통합해야할 파일이 많아지면 메모리 문제가 생길 수 있다.
4. 통합할 데이터를 사용된 모든 셀(UsedRange)으로 설정했으므로, 시트안에 통합할 대상 데이터만 있어야한다. 만약 그렇지 않고 통합되어서는 안되는 데이터까지 시트안에 있다면 결과가 이상하게 나온다.
Option Explicit
Sub Consolidate_Totals()
Dim ws As Worksheet, wb As Workbook, wsT As Worksheet, wbT As Workbook
Dim sArray, i&, sPath$, sFname$
ReDim sArray(1 To 1)
Set wbT = ActiveWorkbook
Set wsT = ActiveSheet
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "폴더를 고르시오"
.Show
If .SelectedItems.Count = 0 Then
MsgBox "Canceled"
Exit Sub
Else
sPath = .SelectedItems(1) & "\"
End If
End With
sFname = Dir(sPath & "*.xls*")
If Len(sFname) = 0 Then Exit Sub
Do
Set wb = Workbooks.Open(Filename:=sPath & sFname)
Set ws = wb.Sheets(1)
i = i + 1
ReDim Preserve sArray(1 To i)
sArray(i) = ws.UsedRange.Address(ReferenceStyle:=XlReferenceStyle.xlR1C1, external:=True)
sFname = Dir
Loop Until Len(sFname) = 0
If i = 0 Then Exit Sub
wsT.Range("A1").Consolidate Sources:=(sArray), _
Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
For Each wb In Application.Workbooks
If wb.Name <> wbT.Name Then
wb.Close False
End If
Next
End Sub
