데이터 정리 예제

Step 1: 세로 방향 표를 가로 방향으로

다음과 같은 질문이었습니다. 표 1은 세로 방향으로 구성된 데이터이고, 표 2는 이것을 가로 방향으로 바꾼 모습입니다.

<표 1>과 같은 데이터와 같이 세로 방향으로 구성된 표가 있는데 이것을 <표 2>와 같은 형태로 바꿀 수 없겠느냐 하는 것이었습니다. 데이터 량이 좀 많아서 대략 2~3만 라인 정도 된다고 합니다.

1~200 행 정도만 되면 눈 딱감고 어찌해 보련만… 2~3만 행이면 한 2박 3일 동안 부지런히 하면 할 수도 있겠군요. ^^;

VBA를 이용하면, 마우스 클릭 한번으로 해결됩니다. 이번 강좌는 순서대로, 차례대로(둘다 같은 말이로군요…) 버튼을 눌러야 제대로 작동됩니다. 순서를 지키시지 않는데서 발생하는 오류는 책임지지 않습니다. ^^

다음 버튼을 누르세요!

자료의 양이 얼마나 되든지 간에 잘 되지요? 다음과 같은 몇 줄의 코드가 이 기능을 수행합니다.

Step 2: 코드 살펴보기

Sub ChangeForm()

    ' 늘 그렇듯, 필요한 변수를 선언해 주고…
    Dim shtSheet As Worksheet
    Dim rngCell As Range
    Dim rngResult As Range
    Dim rngTarget As Range
    Dim rngStart As Range
    Dim lngRow As Long
    Dim intCol As Integer

    Set shtSheet = Worksheets("Source")
    Set rngTarget = shtSheet.Columns(2).SpecialCells(xlTextValues)
    ' 작업 대상이 되는 영역을 rngTarget 변수에 할당합니다.

    MsgBox "화면에 표시할 영역의 내용을 모두 지웁니다", , "www.iExceller.com"
    Set rngStart = Range("Start")
    rngStart.CurrentRegion.Clear
    ' 표시하려는 공간에 이미 데이터가 들어있으면 지우고…

    For Each rngCell In rngTarget
    ' 여기서부터가 이번 강좌의 핵심입니다.

        If IsEmpty(rngCell.Offset(0, -1)) Then
        ' rngCell은 Source 시트의 B열 중에서 값이 들어있는 셀이 됩니다.
        ' rngCell.Offset(0, -1), 그러니까 B열에 있는 셀의 값이  Empty라는 것은 A열에 아무런
        ' 값이 없다는 의미가 됩니다. 따라서 그런 경우에는 rngCell의 값을 Cells(lngRow, intCol)
        ' 셀에 표시하고 intCol 변수의 값을 하나 늘려줍니다. Cells(lngRow, intCol) 셀이 어떻게
        '  바뀌는지는… 종이에 값을 대입해 가며 확인해 보세요.

           Cells(lngRow, intCol) = rngCell
           intCol = intCol + 1
        Else
        ' 그렇지 않다면, 즉 A열에 값이 들어있는 경우라면, lngRow 변수값을 하나 증가시키고
        ' A, B열에 있는 데이터 값을 E열과 F열로 가져갑니다.

           lngRow = lngRow + 1
           Cells(lngRow, 5) = rngCell.Offset(0, -1)
           Cells(lngRow, 6) = rngCell
           intCol = 7
        End If
    Next rngCell

    DrawLines
    ' 이렇게 해서 데이터 정리 작업이 끝났으면 외부 프로시저인 DrawLines를 호출합니다.
    ' 이 프로시저는 정리된 데이터의 내외부에 괘선을 그리기 위한 것입니다.

    MsgBox "작업을 완료하였습니다!", , "www.iExceller.com"

End Sub

DrawLines 프로시저는 괘선 그리기 과정을 매크로 기록기를 켜 놓고 작업해서 생성된 코드를 조금 고쳐준 것이니까 어려운 부분은 없을 것입니다.

Sub DrawLines(Optional rngTarget As Range)

    If rngTarget Is Nothing Then Set rngTarget = Range("Start").CurrentRegion

    With rngTarget
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
    End With

End Sub

Step 3: 반대로 가로 방향을 세로 방향으로

이번에는 <표 2>와 같은 형태로 되어있는 것을 세로 방향으로 바꾸어 볼까요?

코드는 앞서 소개해 드린 것과 크게 다르지 않습니다. 해서… 별도의 설명은 생략할까 합니다. 코드가 잘 이해가 안 되는 분은 질문주세요! ^^

사실 아래 코드에서는 For~Next 안쪽의 Do While 부분만 눈여겨 보시면 됩니다. 눈으로 잘 해결이 안되시면 종이를 한 장 꺼내 놓고 숫자를 차례로 대입시켜 가면서 살펴보시면 되겠습니다.

Sub ChangeForm2()

    Dim rngSource As Range
    Dim lngNum As Long
    Dim intNum As Long
    Dim lngRow As Long
    Dim intCol As Integer
    Set rngSource = Worksheets("Source").Range("E1").CurrentRegion

    rngSource.Copy
    Worksheets.Add
    ActiveSheet.Paste

    MsgBox "데이터를 새로운 시트에 복사하였습니다. 이제 배치 형태를 바꿉니다!", , "www.iExceller.com"

    intNum = Cells(Rows.Count, 1).End(xlUp).Row
    lngNum = intNum + 3

    For lngRow = 1 To intNum
        intCol = 2

        Do While Not IsEmpty(Cells(lngRow, intCol))
            Cells(lngNum, 1) = Cells(lngRow, 1)
            Cells(lngNum, 2) = Cells(lngRow, intCol)
            lngNum = lngNum + 1
            intCol = intCol + 1
        Loop

    Next lngRow

    DrawLines Range(Cells(intNum + 3, 1), Cells(lngNum - 1, 2))
    Range("A1").CurrentRegion.EntireRow.Delete
    Range("A1").Select
    MsgBox "작업을 완료하였습니다!", , "www.iExceller.com"

End Sub

오늘은 여기까지…

정리 — 데이터 정리 코드 핵심

자주 묻는 질문 (FAQ)

마치며

자료가 2~3만 행이라도 규칙만 정확히 파악하면 마우스 클릭 한 번으로 정리할 수 있습니다. 종이에 숫자를 직접 대입해 보며 로직을 이해해 보세요.