데이터 배치를 자유자재로

Step 1: 수식으로 해결한 답글

자주 좋은 답변을 주시는 내가낸데님이 아래와 같이 답글을 주셨네요.

시트를 하나 삽입하신 다음 위의 수식을 적용시켜 보시기 바랍니다.

Step 2: VBA로 하면 더 융통성 있게

그런데 위와 같은 경우에는 VBA로 코딩을 하면 보다 융통성 있는 형태로 만들 수 있습니다. 버튼을 눌러 보세요.

데이터가 자유자재로 재배열이 되지요? 자… 여기에다가 한 가지를 더 추가해 보도록 하지요. 데이터를 재배열 하는 것은 물론, 생성된 데이터의 각종 통계 분석까지 자동으로 계산해 준다면 보다 편리하겠지요? 다시 버튼을 누르세요!

VBA는 어디 국 끓여 먹을려고 배우는 것이 아니라 바로 이런 데에 활용을 하기 위해 배우는 것입니다. ^^ 코드도 아주 간단합니다. 먼저 처음에 사용한 코드를 보면…

Sub DataFormatChange()
    Dim rngSource As Range
    Dim rngCell As Range
    Dim rngOut As Range
    Dim r As Long
    Dim c As Integer
    Dim intNum As Integer

    MakeTable
    '''데이터를 생성하는 외부 프로시저를 호출합니다.

    Set rngOut = [C1]
    Set rngSource = Columns(1).SpecialCells(xlCellTypeConstants)
    intNum = Val(InputBox("몇 개의 열로 재배치 할까요?", "http://www.iExceller.com", 3))
    intNum = intNum - 1
    '''작업 대상이 되는 소스 데이터 위치와 출력 기준이 되는 셀 위치를 잡습니다.
    '''그런 다음 몇 개의 열로 재배치를 할 것인지를 사용자로부터 입력 받습니다.
    '''그런데 왜 값을 입력받은 다음 -1을 해 주었을까요? 그것은… 숙제입니다. ^^

    If intNum < 0 Or intNum > rngSource.Cells.Count Then GoTo ET
    '''사용자가 엉뚱한 값을 입력하였을 경우에 대비하여 에러 트래핑 구문을 준비하고…

    For Each rngCell In rngSource
        If r > intNum Then
            r = 0
            c = c + 1
        End If
        rngOut.Offset(c, r) = rngCell.Value
        r = r + 1
    Next rngCell
    '''위의 부분이 오늘 강좌의 핵심입니다.
    '''rngSource 영역 내의 셀을 하나씩 순환하면서 사용자가 입력한 재배치 기준과
    '''같은지를 비교해 나갑니다. 재배치 기준을 초과하면 아래로 한 행 내려가고
    '''그렇지 않으면 오른쪽으로 한간 이동해서 셀 값을 채워나가는 것입니다.

    MsgBox "데이터를 재배열 하였습니다!", , "http://www.iExceller.com"
    Exit Sub
ET:
    MsgBox "재배열 기준을 잘못 입력하셨습니다", , "http://www.iExceller.com"
End Sub

위의 부분이 오늘 강좌의 핵심입니다. rngSource 영역 내의 셀을 하나씩 순환하면서 사용자가 입력한 재배치 기준과 같은지를 비교해 나갑니다. 재배치 기준을 초과하면 아래로 한 행 내려가고 그렇지 않으면 오른쪽으로 한간 이동해서 셀 값을 채워나가는 것입니다. 작업 대상이 되는 소스 데이터 위치와 출력 기준이 되는 셀 위치를 잡고 몇 개의 열로 재배치를 할 것인지를 사용자로부터 입력 받습니다. 그런데 왜 값을 입력받은 다음 -1을 해 주었을까요? 그것은… 숙제입니다. ^^ 사용자가 엉뚱한 값을 입력하였을 경우에 대비하여 에러 트래핑 구문도 준비해 두었습니다.

기초 데이터를 만드는 코드를 또 보도록 하지요. 어려운 부분이 하나도 없을 것입니다. For Each 문을 사용하여 임의의 숫자를 생성하였을 따름입니다.

Sub MakeTable()
    Dim rngCell As Range
    Dim rngTarget As Range
    Worksheets.Add after:=Sheets("Preface")
    Set rngTarget = ActiveSheet.[A1:A100]

    For Each rngCell In rngTarget
        rngCell = Int(Rnd * 1000)
    Next rngCell
End Sub

Step 3: 분석 데이터까지 자동으로

그러면 두번째 사용된 코드, 즉 분석 데이터를 자동으로 생성해 준 코드를 볼까요?

Sub DataFormatChange1()
    Dim rngSource As Range
    Dim rngCell As Range
    Dim rngOut As Range
    Dim r As Long
    Dim c As Integer
    Dim intNum As Integer

    MakeTable
    Set rngOut = [C1]
    Set rngSource = Columns(1).SpecialCells(xlCellTypeConstants)
    intNum = Val(InputBox("몇 개의 열로 재배치 할까요?", "http://www.iExceller.com", 3))
    intNum = intNum - 1
    If intNum < 0 Or intNum > rngSource.Cells.Count Then GoTo ET

    For Each rngCell In rngSource
        If r > intNum Then
            r = 0
            c = c + 1
        End If
        rngOut.Offset(c, r) = rngCell.Value
        r = r + 1
    Next rngCell
    StatReport rngSource, intNum
    Columns.AutoFit
    MsgBox "데이터를 재배열하고 분석 데이터를 만들었습니다!", , "http://www.iExceller.com"
    Exit Sub
ET:
    MsgBox "재배열 기준을 잘못 입력하셨습니다", , "http://www.iExceller.com"
End Sub

다른 부분은 다 똑같고 통계 자료 작성을 위한 외부 프로시저인 StatReport를 호출한 점이 다릅니다. 이 때 작업 대상이 되는 영역과 재배열 기준을 함께 인수로 넘겨준 점에 유의하시기 바랍니다. 그 밖의 사항에 대해서는 많이 다루었던 사항이므로 설명은 생략…

Sub StatReport(rngX As Range, intX As Integer)
    Dim rngTarget As Range
    Dim strTitle
    Set rngTarget = [C1]
    strTitle = Array("합계", "평균", "최대값", "최소값", "표준편차")

    With rngTarget
        With Range(.Offset(0, intX + 1), .Offset(4, intX + 1))
            .Value = Application.Transpose(strTitle)
            .Interior.ColorIndex = 3
            .Font.ColorIndex = 2
        End With
        .Offset(0, intX + 2) = Application.Sum(rngX)
        .Offset(1, intX + 2) = Application.Average(rngX)
        .Offset(2, intX + 2) = Application.Max(rngX)
        .Offset(3, intX + 2) = Application.Min(rngX)
        .Offset(4, intX + 2) = Application.StDev(rngX)
    End With
End Sub

의문 사항이 생기면 질문 하시기 바랍니다.

다음 시간에 또…

정리 — 데이터 재배열 핵심

자주 묻는 질문 (FAQ)

마치며

VBA는 어디 국 끓여 먹을려고 배우는 것이 아니라 바로 이런 데에 활용하기 위해 배우는 것입니다.