아이엑셀러 닷컴

완성 예

여러 시트에 있는 데이터를 하나로 합쳐야 하는 경우가 흔히 있습니다. [완성 예]와 같이 1월 ~ 6월까지 6개의 시트 내용을 'Report' 시트에 통합하고자 합니다. 이 때 단순히 시트를 붙여넣기 하는 것이 아니고 제품코드 별로 수량을 합산해야 합니다. 어떻게 하면 될까요?

완성 예

'데이터 통합'이나 '파워 쿼리'를 이용하는 방법도 있습니다. 하지만 시트가 추가/삭제되거나 데이터 범위가 변경되면 번거로운 수작업을 반복해야 합니다. 챗GPT로 코딩을 할 수도 있는데 여러 번의 시행착오를 각오해야 하며, 어느 정도의 VBA 지식이 필요할 수도 있습니다. 처음부터 체계적으로 VBA 코딩을 해보도록 하죠.

전체 코드 둘러보기

워크시트에서 ALT + F11 키를 눌러서 Visual Basic Editor(VB Editor) 창을 엽니다. VB Editor에서 [삽입] - [모듈] 메뉴를 선택하여 모듈Module 시트를 하나 삽입하고 코드를 작성합니다. 코드에 대한 설명은 이어서 나오니까 전체 구조와 흐름(순서)을 살펴보시기 바랍니다.

Sub summaryData()
    ''' 변수 선언
    Dim shtRpt As Worksheet
    Dim shtX As Worksheet
    Dim rMonth() As Range
    Dim rX As Range
    Dim oItem As New Collection
    Dim vX As Variant
    Dim lTotal As Long
    Dim lSum As Long
    Dim iMonth As Integer
    Dim iRow As Integer
    Dim iX As Integer
    
    ''' Report 시트 삭제 후 재작성
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Report").Delete
    Application.DisplayAlerts = True
    Set shtRpt = Worksheets.Add
    shtRpt.Name = "Report"
    shtRpt.Move after:=Worksheets(Worksheets.Count)

    ''' 집계할 대상 시트 숫자 파악
    For Each shtX In Worksheets
        If Right(shtX.Name, 1) = "월" Then
            iMonth = iMonth + 1
        End If
    Next
    ReDim rMonth(1 To iMonth)
    
    ''' 대상 시트 숫자만큼 변수에 값 할당(지정), 유일한 값 추출
    For Each shtX In Worksheets
        If Right(shtX.Name, 1) = "월" Then
            iX = iX + 1
            Set rMonth(iX) = shtX.Range("A1").CurrentRegion
            Set rMonth(iX) = rMonth(iX).Offset(1).Resize(rMonth(iX).Rows.Count - 1)
            lTotal = lTotal + Application.Sum(rMonth(iX).Columns(2))
            For Each rX In rMonth(iX).Columns(1).Cells
                oItem.Add rX, rX
            Next
        End If
    Next

    ''' Report 시트에 결과 표시
    With shtRpt.Range("A1")
        For Each vX In oItem
            .Resize(, 3) = Array("제품코드", "수량", "비율")
            For iX = 1 To iMonth
                lSum = lSum + Application.SumIf(rMonth(iX).Columns(1), vX, rMonth(iX).Columns(2))
            Next
            iRow = iRow + 1
            .Offset(iRow).Resize(, 3) = Array(vX, lSum, lSum / lTotal)
            lSum = 0
        Next        
        With .CurrentRegion.Rows(.CurrentRegion.Rows.Count + 1)
            .Resize(, 2) = Array("합계", lTotal)
        End With
        
        ''' 데이터 정렬, 서식 지정
        With .CurrentRegion
            .Sort key1:=.Cells(3), order1:=xlDescending, Header:=xlYes
            .Borders.LineStyle = xlSolid
            .Rows(1).Interior.ColorIndex = 6
            .Rows(.Rows.Count).Interior.ColorIndex = 15
            .Columns(1).HorizontalAlignment = xlCenter
            .Columns(2).NumberFormat = "#,###"
            .Columns(3).NumberFormat = "0.0%"
        End With
    End With
End Sub

CODE

단계별 코드 설명

1. 변수를 선언합니다. 연극에 비유하자면 등장 인물을 지정하는 겁니다. rMonth 변수는 전체 시트 중에서 작업 대상 시트가 몇 개가 될 지 유동적이므로 동적 배열 변수로 선언한 점과, '제품코드' 중에서 중복되지 않는 것을 저장하기 위해 oItem이라는 컬렉션 개체 변수를 선언한 것에 유의하세요.

Dim shtRpt As Worksheet        ''' 결과를 표시할 워크시트
Dim shtX As Worksheet        ''' 워크북 내의 모든 시트에 접근하기 위한 순환 변수
Dim rMonth() As Range        ''' 집계 대상 시트의 범위에 접근하기 위한 변수(동적 배열)
Dim rX As Range              ''' 각 범위의 셀 영역에 접근하기 위한 순환 변수
Dim oItem As New Collection  ''' [제품코드]를 중복되지 않도록 추출해서 담아둘 컬렉션 개체 변수
Dim vX As Variant            ''' 컬렉션 개체 변수에 접근하기 위한 순환 변수
Dim lTotal As Long           ''' 전체 수량 저장
Dim lSum As Long             ''' 각 품목별 수량 저장
Dim iMonth As Integer        ''' 집계 대상 시트 수 저장
Dim iRow As Integer          ''' 행 수 지정 변수
Dim iX As Integer            ''' For ~ Next 문에서 사용할 순환 변수

해설

2. 결과를 표시할 시트(Report)가 이미 있으면 지우고 다시 만듭니다. Add 메서드를 사용할 때, before나 after 인수를 이용하여 삽입할 시트의 위치를 지정할 수 있습니다.

On Error Resume Next                 ''' 실행 도중 오류가 발생하더라도 다음 과정 진행
Application.DisplayAlerts = False    ''' 시트 삭제 시 나타나는 경고 메시지 비활성화
Worksheets("Report").Delete          ''' [Report] 시트 삭제
Application.DisplayAlerts = True     ''' 경고 메시지 표시 상태 초기화
Set shtRpt = Worksheets.Add          ''' 시트를 삽입하고 이름 지정
shtRpt.Name = "Report"
shtRpt.Move after:=Worksheets(Worksheets.Count)    ''' 삽입한 시트를 맨 오른쪽으로 이동

해설

3. 집계할 시트 수를 파악합니다. 대상이 되는 시트는 시트 이름 뒤에 '월'이라는 단어가 공통적으로 들어 있으므로 이 특성을 이용합니다. 하려는 작업에 어떤 규칙이나 패턴이 있는 지 파악하는 것이 중요합니다.

For Each shtX In Worksheets    ''' 문서 내의 모든 워크시트에 대해 반복 실행
    If Right(shtX.Name, 1) = "월" Then    ''' 만약 시트 이름 뒤의 마지막 글자가 '월'이면
        iMonth = iMonth + 1    ''' iMonth 변수 값 1씩 증가
    End If
Next
ReDim rMonth(1 To iMonth)      ''' 변수 크기 재지정

해설

4. 여기서는 두 가지 작업이 이루어집니다.

집계할 시트 숫자만큼 변수에 값을 지정합니다. 작업할 대상이 되는 워크시트 내 범위를 동적 배열 변수에 지정하는 것이지요.
'제품코드' 중에서 중복되지 않는 유일한 값들만 추출하여 oItem 변수에 담습니다. 딱 한 줄로 처리할 수 있으며, 실무에서 빈번하게 활용되므로 잘 기억해 두시기 바랍니다(oItem.Add rX, rX).

For Each shtX In Worksheets    ''' 문서 내의 모든 워크시트에 대해 반복 실행
  If Right(shtX.Name, 1) = "월" Then    ''' 작업 대상이 되는 시트에 선별 접근
      iX = iX + 1
      Set rMonth(iX) = shtX.Range("A1").CurrentRegion    ''' A1 셀 인접 영역을 rMonth(iX) 변수에 차례로 할당(지정)
      Set rMonth(iX) = rMonth(iX).Offset(1).Resize(rMonth(iX).Rows.Count - 1)    ''' 표 제목을 제외한 부분으로 범위 크기 변경
      lTotal = lTotal + Application.Sum(rMonth(iX).Columns(2))    ''' 영역의 두 번째 열의 합계를 lTotal 변수(전체 합계)에 저장
      For Each rX In rMonth(iX).Columns(1).Cells    ''' 대상 영역의 첫 번째 컬럼(제품코드)의 셀을 대상으로 반복 실행
          oItem.Add rX, rX    ''' 컬렉션 개체 변수 oItem에 제품코드 추가. 이 때 중복되는 값이 나오면 자동으로 다음 진행
      Next
  End If
Next

해설

5. 'Report' 시트에 결과를 표시합니다. 워크시트 함수를 VBA에서도 사용할 수 있습니다. 함수 앞에 Application이나 WorksheetFunction이라는 키워드를 적어주기만 하면 됩니다.

With shtRpt.Range("A1")
    .Resize(, 3) = Array("제품코드", "수량", "비율")    ''' A1:C1 영역에 표의 제목 입력
    For Each vX In oItem        ''' oItem 변수에 들어 있는 모든 항목('제품코드')에 대해 반복 실행
        For iX = 1 To iMonth    ''' 시트 이름에 '월'이 들어 있는 수만큼 반복 실행
            lSum = lSum + Application.SumIf(rMonth(iX).Columns(1), vX, _
                rMonth(iX).CurrentRegion.Columns(2))
                            ''' SUMIF 함수를 이용하여 제품코드 별로 합계를 구해 lSum 변수에 저장
            End If
        Next
        .Offset(iRow).Resize(, 3) = Array(vX, lSum, lSum / lTotal)    
                            ''' 한 행씩 아래로 이동하면서 결과 표시(제품코드, 수량, 비율)
        lSum = 0            ''' 다음 제품코드의 합계를 위해 변수 초기화
    Next
End With

해설

6. '비율'을 기준으로 내림차순 정렬하고 각종 서식을 지정하여 완성합니다. 서식 지정에 사용되는 다양한 속성이나 메서드를 굳이 외울 필요는 없습니다. 매크로 기록기를 이용하거나 이제는 챗GPT를 활용하면 쉽게 해결할 수 있습니다.

With shtRpt.Range("A1").CurrentRegion    ''' Report 시트의 데이터 마지막에 '합계' 표시
    .Rows(.Rows.Count + 1).Resize(, 2) = Array("합계", lTotal)
End With
With shtRpt.Range("A1").CurrentRegion
    .Sort key1:=.Cells(3), order1:=xlDescending, Header:=xlYes    ''' 비율을 기준으로 내림차순 정렬
    .Borders.LineStyle = xlSolid        ''' 집계표에 실선 지정
    .Rows(1).Interior.ColorIndex = 6    ''' 집계표 제목 노란색 배경 지정
    .Rows(1).HorizontalAlignment = xlCenter
    .Columns(1).HorizontalAlignment = xlCenter
    .Columns(2).NumberFormat = "#,###"
    .Columns(3).NumberFormat = "0.0%"
    .Rows(.Rows.Count).Interior.ColorIndex = 15    ''' 합계 부분에 회색 음영 처리
End With

해설

생소하고 복잡해 보일 수 있지만 중요한 개념들이 몇 가지 들어 있습니다. 코드 내용을 자세히 모르더라도 영역 주소를 조금 수정할 정도만 되어도 '복/붙'을 통해 업무에 활용할 수 있으리라 생각합니다. 잘 활용해 보세요.

얼마 전에 유엔 사무총장이 "지구 온난화^{global warming} 시대가 끝나고, 이제는 열대화 시대^{global boiling}가 시작됐다"고 말했습니다. 앞으로는 '역대 최고'라는 수식어가 계속 등장하겠군요. 제동 장치 없는 지구 온난화가 걱정입니다. 일단은 올 여름을 다들 무탈하게 잘 보내시기 바랍니다.

들어가기 전에 ㅡ 부모 마음이란 그런 것

완성 예

전체 코드 둘러보기

단계별 코드 설명