아이엑셀러 닷컴

완성 예

테이블(표)은 크게 세 가지 종류가 있다는 것은 이제 아시리라 생각합니다. "그게 뭔데요?" 하는 분은 아래 강의 초반부를 잽싸게 보고 오시기 바랍니다.

위 강의에서는 크로스탭 테이블^{Cross-tab Table}을 표준 테이블^{혹은 일반 테이블, Raw-data}로 바꾸는 방법에 대해 알아보았습니다. 이번 시간에는 그것과는 반대로 크로스탭 테이블을 표준 테이블로 바꾸는 방법에 대해 살펴보겠습니다.

예제 파일을 열고 [테이블 만들기] 버튼을 클릭하면 오른쪽과 같은 크로스탭 테이블이 순식간에 만들어집니다.

완성 예

VBA의 도움 없이 엑셀 고유 기능만으로 이렇게 하려면 어떻게 해야 할까요? Sumproduct 함수를 이용하거나 배열 수식을 이용하면 됩니다. 수식에 알러지가 있는 분이라면 피벗 테이블을 사용할 수도 있습니다. 피벗 테이블을 삽입한 다음 행 영역에는 [품목], 열 영역에는 [지역], 값 영역에는 [실적] 필드를 끌어다 놓기 하면 됩니다.

피벗 테이블로 크로스탭 테이블 만들기

이번 강의에서는 이 원리에 착안하여 크로스탭 테이블을 만드는 방법에 대해 소개합니다.

코드 작성

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

Sub makeCrossTabTable()
    Dim shtX As Worksheet
    Dim oPC As PivotCache
    Dim oPT As PivotTable
    Dim rWrite As Range
    
    On Error Resume Next
    Application.DisplayAlerts = False
    Set shtX = Worksheets("예제")
    If shtX Is Nothing Then
        MsgBox "작업 대상 시트가 없으므로 실행을 중단합니다"
        Exit Sub
    End If
    Set rWrite = shtX.Range("H1")
    rWrite.CurrentRegion.Clear
    Set oPC = ThisWorkbook.PivotCaches.Create(xlDatabase, shtX.Range("A1").CurrentRegion)
    Set oPT = oPC.CreatePivotTable(rWrite)
    
    With oPT
        .PivotFields("품목").Orientation = xlRowField
        .PivotFields("지역").Orientation = xlColumnField
        .PivotFields("실적").Orientation = xlDataField
        .TableRange1.Copy
    End With
    With rWrite
        .PasteSpecial xlPasteValues
        With .CurrentRegion
            .Rows(1).Clear
            With .Offset(1)
                .Cells(1) = "품목"
                .Resize(.Rows.Count - 1).Borders.LineStyle = xlSolid
                .Rows(1).Interior.ColorIndex = 6
                .Rows(1).HorizontalAlignment = xlCenter
                .Cells(1).Select
            End With
            Range("J:N").NumberFormat = "#,###"
            .Columns.AutoFit
        End With
    End With
    Application.DisplayAlerts = True
End Sub

CODE

코드 해설

1. 코드를 4개 부분으로 나누어 살펴보겠습니다. 맨 먼저 할 일은 늘 그렇듯 등장인물을 지정하는 겁니다. 변수를 선언하고 값을 지정하는 작업이지요.


Dim shtX As Worksheet        ''' 워크시트 개체에 접근할 개체 변수
Dim oPC As PivotCaches       ''' 피벗 캐시 개체에 접근할 변수
Dim oPT As PivotTable        ''' 피벗 테이블 개체에 접근할 변수
Dim rWrite As Range          ''' 피벗 테이블을 표시할 위치에 접근할 변수

해설

2. 본격적인 프로그램 실행 전에 간단한 유효성 검사^Validation를 진행하고 변수에게 구체적인 역할을 맡깁니다.


On Error Resume Next                ''' 실행 도중 오류가 발생하더라도 다음 구문 진행
Application.DisplayAlerts = False   ''' 오류 발생 시 메시지 표시를 일시 중단
Set shtX = Worksheets("예제")
If shtX Is Nothing Then             ''' 예제 시트가 없으면 프로시저 실행 중단
    MsgBox "작업 대상 시트가 없으므로 실행을 중단합니다"
    Exit Sub
End If
Set rWrite = shtX.Range("H1")       ''' 피벗 테이블의 결과를 표시할 위치를 rWrite 변수에 할당
rWrite.CurrentRegion.Clear          ''' rWrite 주변 영역 지우기
Set oPC = ThisWorkbook.PivotCaches.Create(xlDatabase, shtX.Range("A1").CurrentRegion)
                              ''' shtX(예제) 시트의 A1 셀 인접 영역을 이용하여 피벗 캐시 생성
Set oPT = oPC.CreatePivotTable(rWrite)
                              ''' 만들어진 피벗 캐시를 이용하여 피벗 테이블 생성

해설

피벗 테이블을 만들 때 사용할 수 있는 소스는 5가지가 있습니다. 당분간은 xlDatabase만 알면 되니 너무 고민할 필요 없습니다.

xlDatabase: 엑셀 표준 데이터베이스(테이블)
xlConsolidation: 다중 통합 범위
xlExternal: 외부 데이터
xlPivotTable: 다른 피벗 테이블
xlScenario: 엑셀 시나리오

3. 등장인물과 배역을 설정했으니 이제부터 본격적인 코딩에 들어갑니다. 워크시트에서 피벗 테이블을 만들 때 각 필드를 해당 영역으로 끌어다 놓기 했던 것을 엑셀이 알아듣도록 코딩 하면 이렇게 됩니다.


With oPT
  .PivotFields("품목").Orientation = xlRowField        ''' 품목 필드를 행 영역으로
  .PivotFields("지역").Orientation = xlColumnField     ''' 지역 필드를 열 영역으로
  .PivotFields("실적").Orientation = xlDataField       ''' 실적 필드를 데이터 영역으로
  .TableRange1.Copy                                    ''' 피벗 테이블 복사
End With

해설

여기까지 작성된 코드를 실행하면 아래와 같이 피벗 테이블이 지정한 위치에 만들어집니다.

4. 피벗 테이블을 일반 범위로 변환하고 서식을 지정하여 좀 더 융통성 있게 사용할 수 있도록 코드를 추가합니다.


With rWrite                     
  .PasteSpecial xlPasteValues    ''' 앞에서 복사한 피벗 테이블을 rWrite(H1 셀) 영역에 값 붙여넣기
  With .CurrentRegion            ''' rWrite 인접 영역에 대하여
      .Rows(1).Clear             ''' 첫 번째 행의 값과 서식 지우기
      With .Offset(1)            ''' 아래로 한 행 이동하여
          .Cells(1) = "품목"     ''' 첫 번째 셀에 '품목' 입력
          .Resize(.Rows.Count - 1).Borders.LineStyle = xlSolid    
                                 ''' rWrite 인접 영역에서 아래로 1행 이동, 크기를 1행 축소한 영역에 괘선 작성
          .Rows(1).Interior.ColorIndex = 6    
          .Rows(1).HorizontalAlignment = xlCenter
          .Cells(1).Select
      End With
      Range("J:N").NumberFormat = "#,###"    ''' J:N열에 쉼표 서식 지정
      .Columns.AutoFit
  End With
End With
Application.DisplayAlerts = True    ''' 오류 메시지 표시 상태 초기화

해설

어느덧 11월 마지막 주입니다. 계절은 겨울을 향해 성큼성큼 접어들고 있습니다. 조금 이른감이 없지 않지만 한해 마무리 잘들 하시기 바랍니다.

들어가기 전에 ㅡ 시간을 길게 쓰는 법

완성 예

코드 작성

코드 해설