자료변환 예제Ⅱ

Step 1: 질문의 내용

기존의 자료는 회사명 한 줄에 부장과 과장의 인원수, 기본급, 상여금이 가로로 나열된 형태입니다.

이것을 아래와 같이 회사명과 직위별로 한 줄씩 나열하고 싶다는 질문입니다.

Step 2: 실행해 보기

아래의 버튼을 누르시면 워크시트가 한장 삽입되고 소스 테이블이 복사됩니다. 그런 다음에 다시 버튼을 누르세요.

잘 보셨지요? 지난 강좌 시간에도 언급을 했었지만 자료를 정리할 때에는 나중을 위해서 DB 구조를 잘 생각하셔야 합니다. 그래야 자료를 관리하고 응용을 하기가 수월해 집니다.

Step 3: ArrangeData 프로시저

Sub ArrangeData()
    Dim rngStart As Range
    Dim r As Long
    Set rngStart = ActiveSheet.Range("a2")
    rngStart.Sort key1:=Range("a2"), order1:=xlAscending, header:=xlGuess
    '//변수를 선언합니다. 그리고 나서 현재 시트(여기서는 새로 삽입한 시트)의 A2 셀을
    '//시작셀로 지정합니다.
    '//혹시 회사명이 혼재되어 있을 경우를 대비하여 Sorting을 해 줍니다.

    Do While rngStart.Offset(r, 0) <> ""
        If rngStart.Offset(r, 0) = "" Then
        '//A열의 값이 공란이면 Do Loop 문을 빠져나가고,

            Exit Do
        Else
        '//A열의 값이 공란이 아니면 행을 하나 삽입한 다음 오른쪽으로 4간 이동한 위치에
        '//있는 자료(과장 인원수)부터 오른쪽 편에 있는 모든 자료들을 잘라내기/붙여넣기
        '//합니다. 즉 가로 방향으로 배열되어 있는 자료를 세로로 재배치하는 것입니다.

            rngStart.Offset(r + 1).EntireRow.Insert
            rngStart.Offset(r, 4).Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Cut Destination:=rngStart.Offset(r + 1, 1)
        End If
        r = r + 2
    Loop

    '//회사명 부분이 공란으로 남겨져 있으면 안되니까 빈 셀인 경우, 바로 위 셀의 값을
    '//끌어 옵니다.
    Range("b2").EntireColumn.Insert
    Columns(1).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=r[-1]c"

    '//직책 부분의 타이틀을 적어줍니다.
    rngStart.Offset(0, 1) = "부장"
    rngStart.Offset(1, 1) = "과장"
    Range("b2:b3").Select
    Selection.Copy
    Range("b" & 4 & ":" & "b" & r + 1).Select
    ActiveSheet.Paste
    Rows(1).ClearContents

    '//자료의 헤더 부분을 다시 적어주고 서식을 재정의합니다.
    Range("a1:e1") = Array("회사명", "직책", "인원수", "기본급", "상여금")
    ActiveSheet.UsedRange.ClearFormats
    Range("a2").Select
    Selection.AutoFormat Format:=xlRangeAutoFormatList1, Number:=True, Font:= _
        True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
End Sub

Step 4: 시트를 삽입하고 버튼 만들기

아래의 코드는 워크시트를 한장 삽입하고 버튼을 만든 다음 버튼에 프로시저를 연결하는 코드입니다. 이것은 어려운 부분이 하나도 없으므로 해설은 생략합니다.

Sub GoToWorkplace()
    Range("Source").Copy
    Worksheets.Add after:=Sheets("Preface")
    ActiveSheet.Paste
    ActiveWindow.DisplayGridlines = False
    Range("i1").Select

    With ActiveCell
        ActiveSheet.Buttons.Add(.Left, .Top, .Width * 2, .Height).Select
    End With
    With Selection
        .Caption = "Click Me!!!"
        .Font.Name = "arial"
        .Font.Size = 10
        .Font.Bold = True
        .OnAction = "ArrangeData"
    End With
    Range(Selection.TopLeftCell.Address).Select
End Sub

마무리

다음 시간에 또…

정리 — 표 구조 변환 핵심

자주 묻는 질문 (FAQ)

마치며

자료를 관리하기 쉬운 DB 구조로 바꾸는 작업은 VBA로 자동화할 수 있습니다.