아이엑셀러 닷컴

엑셀에서 사용하는 테이블 종류

실무에서는 아래와 같은 모양의 테이블(표)을 많이 사용합니다. 이렇게 생긴 표를 크로스탭 테이블 ^{Cross-tab Table}이라고 부릅니다.

크로스탭 테이블

테이블은 크게 세 가지 종류로 구분할 수 있습니다. 테이블 1과 같은 형태는 로데이터^Raw-data라고도 부르는 표준 테이블입니다. 테이블 2는 '변형된 테이블', 테이블 3은 '크로스탭 테이블'이라고 합니다. 테이블 2나 테이블 3과 같은 형태로 만들더라도 항상 테이블 1과 같은 표준 테이블을 가지고 있는 것이 좋습니다. '테이블 1'과 같은 표가 아니라면 피벗 테이블이나 정렬, 부분합 같은 엑셀의 강력한 분석 도구들을 사용할 수 없습니다.

테이블의 3가지 종류

이번 강의에서는 '테이블 만들기' 버튼을 클릭하면 왼쪽의 표를 오른쪽과 같은 형태로 변환하는 방법에 대해 알아봅니다.

완성 예

크로스탭 테이블을 표준 테이블로 바꾸는 방법은 여러 가지가 있습니다. 엑셀의 수식을 사용할 수도 있고 피벗 테이블이나 파워 쿼리를 이용할 수도 있습니다. 하지만 VBA로 코딩하는 방법을 익혀두면 실무에서 맞닥뜨리는 다양한 환경에서 융통성 있게 처리할 수 있습니다.

코드 작성

워크시트 상태에서 ALT + F11 키를 눌러서 Visual Basic Editor 창을 엽니다. [삽입] - [모듈] 메뉴를 이용하여 모듈^Module을 하나 삽입하고 코드를 작성합니다. 코드에 대한 설명은 아래에 나오니까 전체적인 구조와 흐름을 주욱 훑어보고 넘어가시기 바랍니다.

Sub arrangeData()
    Dim rTbl As Range
    Dim rRow As Range
    Dim rCol As Range
    Dim rX As Range
    Dim rY As Range
    Dim rStart As Range
    Dim iRow As Integer
    
    On Error Resume Next
    Set rTbl = Worksheets("예제").Range("A2").CurrentRegion
    Set rCol = rTbl.Columns(1)
    Set rCol = rCol.Offset(1).Resize(rCol.Rows.Count - 1)
    Set rRow = rTbl.Rows(1)
    Set rRow = rRow.Offset(, 1).Resize(, rRow.Columns.Count - 1)
    Set rStart = rTbl.Cells(1).Offset(, rTbl.Columns.Count + 4)
    
    With rStart
        .CurrentRegion.Clear
        .Resize(, 3) = Array("품목", "월", "실적")
        For Each rX In rCol.Cells
            For Each rY In rRow.Cells
                iRow = iRow + 1
                .Offset(iRow).Resize(, 3) = Array(rX, rY, Intersect(rX.EntireRow, rY.EntireColumn))
            Next
        Next
        With .CurrentRegion
            .Borders.LineStyle = xlSolid
            .Columns(1).AutoFit
            .Columns(3).NumberFormat = "#,###"
            .Rows(1).Interior.ColorIndex = 6
            .Rows(1).HorizontalAlignment = xlCenter
        End With
    End With
End Sub

CODE

코드 해설

1. 코드를 4개 부분으로 나누어 살펴보겠습니다. 맨 먼저 할 일은 등장인물을 지정하는 겁니다. 코딩에서 등장인물의 역할은 변수가 수행합니다.


Dim rTbl As Range       ''' 작업 대상이 되는 테이블(원본 테이블)
Dim rRow As Range       ''' 표의 각 행에 접근하기 위한 변수
Dim rCol As Range       ''' 표의 각 열에 접근하기 위한 변수
Dim rX As Range         ''' 열 방향으로 접근하기 위한 변수(순환 변수)
Dim rY As Range         ''' 행 방향으로 접근하기 위한 변수(순환 변수)
Dim rStart As Range     ''' 원본 테이블을 출력할 첫 번째 셀
Dim iRow As Integer     ''' 한 행씩 아래로 이동하기 위한 변수

해설

2. 등장인물을 불러냈으니 어떤 역할을 하도록 할 것인지 배역도 설정해 주어야겠죠? 각 변수가 담당할 역할을 지정합니다.


Set rTbl = Worksheets("예제").Range("A2").CurrentRegion       ''' 예제 시트의 A2 셀 인접 영역을 rTbl 변수에 저장
Set rCol = rTbl.Columns(1)                                    ''' rTbl 영역의 첫 번째 열에 접근
Set rCol = rCol.Offset(1).Resize(rCol.Rows.Count - 1)         ''' 첫 번째 열 중에서 제목(여기서는 A2 셀)을 제외한 영역에 접근
Set rRow = rTbl.Rows(1)                                       ''' rTbl 영역의 첫 번째 행에 접근
Set rRow = rRow.Offset(, 1).Resize(, rRow.Columns.Count - 1)  ''' 첫 번째 행 중에서 제목(여기서는 A2 셀)을 제외한 영역에 접근
Set rStart = rTbl.Cells(1).Offset(, rTbl.Columns.Count + 4)   ''' 새로운 표를 표시할 첫 번째 셀 위치 지정(여기서는 L2 셀)

해설

3. 등장인물과 배역을 설정했으니 이제부터 본격적인 코딩에 들어갑니다.


With rStart                                     ''' 새롭게 표를 출력할 첫 번째 셀(L2 셀)을 기준으로
    .CurrentRegion.Clear                        ''' 주변에 데이터가 있으면 삭제
    .Resize(, 3) = Array("품목", "월", "실적")  ''' L2:N2 셀에 표의 제목 출력
    For Each rX In rCol.Cells                   ''' A열의 각 셀을 대상으로 반복 실행
        For Each rY In rRow.Cells               ''' A열의 각 셀마다 열 방향(1월, 2월,...)으로 접근하며 반복 실행
            iRow = iRow + 1                     ''' 한 행씩 내려가면서 데이터를 삽입하기 위해 변수 값 1씩 증가
            .Offset(iRow).Resize(, 3) = Array(rX, rY, Intersect(rX.EntireRow, rY.EntireColumn))   ''' L:N 열의 지정한 위치에 품목, 월, 값 삽입
        Next
    Next

해설

위 코드에서 가장 중요한 부분은 행(품목)과 열(월)이 만나는 부분에 접근하여 값을 얻어내는 과정입니다. 두 영역이 중첩하는 영역에 접근하는 역할을 수행하는 것이 Application 개체가 가진 Intersect 속성입니다. 위 코드에서 For Each ~ In ~ Next 문이 두 번 중첩해서 사용되었습니다. 바깥에 있는 For Each 문은 A열의 품목에 접근합니다. 각 품목에 대해 월별로 접근하기 위해 For Each 문을 한 번 더 사용한 구조입니다. 결과적으로 하나의 품목에 대해 6번을 반복하게 되죠. 그림으로 나타내 보면 이렇게 됩니다.

Intersect 메서드 작동 원리

희안(?)하게도 Range가 아닌 Application 개체에 속해 있습니다. 참고로 Application 개체에 딸려 있으면서 Range 개체를 만들어 주는 메서드로는 Intersect 말고도 Union이 있습니다. 이 두 메서드는 앞에 Application을 생략해도 됩니다.Intersect 메서드에 대한 자세한 설명은 아래 강의를 참고하세요.

레인지 개체 생성 (4) ㅡ Union, Intersect 메서드

여기까지 진행하면 L:N열에 새로운 포맷으로 데이터가 표시됩니다.

4. 표에 테두리 선을 삽입하고 제목 영역에 색상을 지정하는 등 표를 좀 더 시각적으로 보이도록 데코레이션 하여 완성합니다.


    With .CurrentRegion                           ''' rStart(L2 셀)의 주변 영역을 대상으로
        .Borders.LineStyle = xlSolid              ''' 테두리선 작성
        .Columns(1).AutoFit                       ''' 첫 번째 컬럼의 열 너비 자동 맞춤
        .Columns(3).NumberFormat = "#,###"        ''' 세 번째 컬럼의 숫자 표시를 천 단위마다 쉼표 스타일 설정
        .Rows(1).Interior.ColorIndex = 6          ''' 첫 번째 행의 셀 색상을 노란색으로 지정
        .Rows(1).HorizontalAlignment = xlCenter   ''' 첫 번째 행을 가운데 맞춤 설정
    End With
End With

해설

Intersect를 비롯하여 Range 개체에 접근하는 몇 가지 속성이나 메서드들에 대해 잘 익혀두시기 바랍니다. 함수와 마찬가지로 피상적으로 알고 있어서는 안 되고 속속들이 알고 있어야 합니다. Range 개체만 잘 이해하면 나머지 개체는 크게 어렵지 않습니다.

추석이 일주일 앞 둔 시점에 '매미'나 '사라'에 비견할 만한 역대급 태풍이 남부 지방에 상륙할 것이라는 예보가 있어 걱정입니다. 기상청 예보가 기우였으면 좋겠습니다. 다들 태풍 피해 없도록 조심하시고 즐거운 한가위 보내세요.

#엑셀테이블 #Intersect #크로스탭

들어가기 전에 ㅡ 데자뷰와 뷰자데

엑셀에서 사용하는 테이블 종류

코드 작성

코드 해설