VBA로 피벗테이블 건들기4

VBA 코드 숨기기

VBA 코드를 숨기는 방법은 아주 간단합니다. VB Editor 상태에서 숨기시려는 프로젝트를 오른쪽 마우스 버튼으로 클릭합니다. 그러면 그림과 같이 단축 메뉴가 나타나는데 'VBProject 속성' 메뉴를 선택하고 '보호' 탭을 선택하신 다음 암호를 지정해 주시면 됩니다.

아이엑셀러

모쪼록 깊이 상심하시는 일이 없기를 바랍니다.

설문 조사 결과를 피벗 테이블로 분석하기

요즘 주변에서 On-line을 이용하여 설문 조사를 하는 경우를 자주 봅니다. 대개의 경우, 설문 조사 결과를 어떤 식으로 분석을 하시는지 잘 모르겠지만 피벗 테이블을 사용하면 아주 간편하게 결과를 요약, 분석할 수 있습니다. (오늘 예제는 J.Walkenbach님의 데이터를 약간 수정한 것입니다) 버튼을 눌러 예제를 살펴보고 오세요.

말 그대로 순식간에 처리가 되지요? 똑같은 일을 두 사람에게 시켰는데 한 사람은 5분만에 뚝딱해 치우고 놀고 있는데(논다기 보다 다른 사람과 교류 ^^) 다른 사람은 2시간이 지났는데도 땀 뻘뻘 흘려가며 부지런히 계산기와 키보드를 두들기고 있다면 여러분은 어떤 사람한테 점수를 더 주시겠습니까?

그야 당연히... 후자라구요? 당연히 그렇게 되어야 하나 책상머리에 앉아있는 시간만을 가지고 평가를 하는 곳이 아직은 많이 있는 듯 합니다. 신문 지상을 덮고있는 수많은 조직들을 보면 그런 생각이 절로 듭니다. 엑셀과 VBA(사실은 컴퓨터 그 자체가 그렇습니다만)의 가장 큰 특성이 효율성 추구, 중복의 배제입니다. 엑셀과 VBA를 통해 이런 기본 원칙을 조금만이라도 습득을 한다면 불미스런 일로 신문을 덮게 되지는 않을텐데…

그건 그렇고… 코드를 보도록 하지요.

MakeSummarySheet 프로시저

Sub MakeSummarySheet()
    Dim pvtPCache As PivotCache
    Dim pvtPTable As PivotTable
    Dim shtPivot As Worksheet
    Dim strItem As String, Msg As String
    Dim r As Integer, i As Integer
    '''등장 인물 및 배역을 설정하는 부분입니다.

    For Each shtPivot In Worksheets
        If shtPivot.Name = "결과분석" Then
            Msg = Msg & "같은 시트가 이미 존재합니다" & vbCr
            Msg = Msg & "지우고 다시 만들까요?"
            If MsgBox(Msg, vbYesNo, "동일 시트 발견//By Exceller") = vbYes Then
                Application.DisplayAlerts = False
                shtPivot.Delete
                Application.DisplayAlerts = True
            Else
                MsgBox """아니오""를 선택하셨으므로 아무 작업도 하지 않았습니다"
                Exit Sub
            End If
        '''설문 결과를 분석할 시트를 만듭니다. 이 때 기존에 만들어 진 것이 있으면
        '''사용자에게 알려주고 시트를 다시 만들 것인지의 여부를 결정토록 합니다.

        End If
    Next shtPivot
    Set shtPivot = Worksheets.Add
    With shtPivot
        .Name = "결과분석"
        .Move after:=Sheets(Sheets.Count)
        '''시트를 새로 한장 삽입하고 시트 이름을 "결과분석"이라고 줍니다.
        '''시트를 삽입할 위치는 현재 워크시트의 마지막 부분이 됩니다.

    End With

    Set pvtPCache = ActiveWorkbook.PivotCaches.Add _
        (SourceType:=xlDatabase, _
        SourceData:=Sheets("설문결과").Range("A1"). _
        CurrentRegion.Address)
    '''피벗 캐시 오브젝트를 생성하는 것에 대해서는 몇 강좌 전에 설명을 드리고
    '''넘어왔나 그냥 넘어왔나? 기억이 가물가물한데… 형식이 거의 정해져 있습니다.
    '''SourceType이라는 것은 피벗 테이블의 원본 데이터 형식을 의미하는 것이고,
    '''SourceData는 말 그대로 피벗 테이블을 만들 원본 데이터의 위치를 알려주는
    '''것입니다.

    r = 1
    For i = 1 To 10
        strItem = Sheets("설문결과").Cells(1, i + 2)
        Set pvtPTable = pvtPCache.CreatePivotTable _
            (TableDestination:=shtPivot.Cells(r, 1), _
             TableName:=strItem)
        r = r + 12
        '''이 부분이 중요합니다. 피벗 테이블을 생성하는데 어디에다 생성을 하느냐 하면
        '''shtPivot 시트, 그러니까 새로 삽입한 워크시트에다 생성을 하는 것입니다.
        '''이 때 각 문항마다 별도의 피벗 테이블로 분석을 하기 위해 순환문을 사용하여
        '''반복 작업을 합니다. r = r+12 에서 12를 더해 준 이유는 그냥 피벗 테이블이 겹치지
        '''않도록 하기 위한 것입니다.

        '''나머지 아래 부분은 지난 강좌에서 많이 다루어 본 내용이지요?
        '''Orientation 속성을 사용하여 데이터를 어느 필드에 넣을 것인지를 결정하는
        '''것입니다. 그러면 이 모든 속성값을 다 외워야 하느냐? 절대로 그렇지가 않습니다.
        '''매크로 기록기를 켜 놓고 수작업을 한 다음 코드를 조금씩 수정해 나가면 되는
        '''것입니다.

        With pvtPTable.PivotFields(strItem)
            .Orientation = xlDataField
            .Name = "응답 빈도"
        End With

        With pvtPTable.PivotFields(strItem)
            .Orientation = xlDataField
            .Name = "비율(%)"
            .Calculation = xlPercentOfTotal
        End With

        With pvtPTable
            .AddFields RowFields:=Array(strItem, "데이터")
            .PivotFields("성별").Orientation = xlColumnField
            .PivotFields("데이터").Orientation = xlColumnField
            .Format xlTable6
        End With
     Next i

    shtPivot.Select
    With Columns(1)
        .Replace What:="1", Replacement:="매우 그렇다", LookAt:=xlWhole
        .Replace What:="2", Replacement:="그런 편이다", LookAt:=xlWhole
        .Replace What:="3", Replacement:="보통이다", LookAt:=xlWhole
        .Replace What:="4", Replacement:="그렇지 않다", LookAt:=xlWhole
        .Replace What:="5", Replacement:="매우 그렇지 않다", LookAt:=xlWhole
        '''보통 설문지를 작성할 때 5점 척도법을 많이 사용합니다. 숫자로 1, 2, 3, 4, 5로
        '''표기한 것을 문자로 바꾸어 줍니다.
        '''아래의 코드는 생성된 피벗 테이블 시트의 서식을 꾸미고 메시지 박스를 띄우기
        '''위한 것이므로 설명은 생략합니다.

    End With

    With Columns
        .EntireColumn.AutoFit
        .Font.Name = "굴림"
        .Font.Size = 11
    End With
    On Error Resume Next
    Application.CommandBars("PivotTable").Visible = False
    On Error GoTo 0
    ActiveWorkbook.ShowPivotTableFieldList = False
    Application.DisplayAlerts = True
    Msg = Space(0)
    Msg = Msg & "설문 항목별 분석을 완료하였습니다" & vbCr
    Msg = Msg & "말 그대로 순식간에 해치워 버렸지요?"
    MsgBox Msg, , "문항별 분석 완료//By Exceller"
End Sub

마무리

이제 VBA로 피벗 테이블은 고만 건들까요? ^^ 오늘은 여기까지…

정리 — 설문 분석 피벗 핵심

자주 묻는 질문 (FAQ)

마치며

순환문과 피벗 테이블을 결합하면 반복적인 설문 분석을 순식간에 처리할 수 있습니다.