VBA로 피벗 테이블 건들기3

Stage 시트와 컨트롤 도구 상자

이번 강좌에서는 피벗 테이블을 하나 만들어 놓고 사용자의 필요에 따라 늘었다 줄었다, 합계를 보이게 했다가 사라지게 했다가(무슨 약장사가 광고를 하는 듯 합니다 ^^) 하는 방법에 대해 설명을 드리겠습니다.

아래의 버튼을 눌러서 예제 시트로 가신 다음, 여러 버튼들을 눌러 보세요.

Step 1: 월별 데이터 보기

재미있지요? Stage 시트에서 사용된 옵션 버튼이나, 체크 박스는 양식 도구모음의 컨트롤이 아닌 컨트롤 도구 모음의 그것들을 사용한 것입니다. 따라서 디자인 편집 모드가 아닌 상태에서는 편집을 하실 수 없습니다. 양식 도구모음의 컨트롤을 사용하려다가 어떤 분의 질문 내용이 생각나서 일부러 컨트롤 도구 모음의 컨트롤을 사용해 보았습니다. 경각심을 환기시키는 차원에서… ^^

Stage 시트의 버튼이나 체크 박스에 대한 편집 작업을 하시려면, '보기-도구 모음-컨트롤 도구 상자'를 선택하시고 '디자인 모드' 아이콘을 한번 눌러 디자인 편집 모드로 들어간 상태라야 합니다. 양식 도구모음과 컨트롤 도구 모음은 아주 비슷하게 생겨 먹어서 많은 초보님들을 헷갈리게 하니까 혼동하지 않도록 잘 기억해 두시기 바랍니다.

워크시트에 옵션 버튼이나 체크 박스를 그리는 것은 아실 테니까 각 오브젝트에 연결된 코드를 살펴보도록 하겠습니다.

먼저 "월별 데이터 보기" 옵션 버튼에 연결된 코드입니다.

Private Sub optMonth_Click()
    With ActiveSheet.PivotTables(1).PivotFields("Month")
        .PivotItems("Jan").Visible = True
        '''코드는 아주 간단합니다. PivotItems 오브젝트의 Visible 속성을 사용하여
        '''특정 월의 값을 보이거나 숨기는 것입니다.

        .PivotItems("Feb").Visible = True
        .PivotItems("Mar").Visible = True
        .PivotItems("Apr").Visible = True
        .PivotItems("May").Visible = True
        .PivotItems("Jun").Visible = True
        .PivotItems("1/4분기").Visible = False
        .PivotItems("2/4분기").Visible = False
        .PivotItems("상반기").Visible = False
        ActiveWorkbook.ShowPivotTableFieldList = False
    End With
End Sub

Step 2: 분기 데이터 보기

아래는 "분기 데이터 보기" 버튼에 연결된 코드입니다. 위의 것과 마찬가지로 Visible 속성값의 조절을 통해 보여줄 필드와 숨길 필드를 결정합니다. 분기에 해당되는 데이터만 Visible 속성을 True, 즉 1로 지정합니다.

Private Sub optQuarter_Click()
    With ActiveSheet.PivotTables(1).PivotFields("Month")
        .PivotItems("1/4분기").Visible = True
        .PivotItems("2/4분기").Visible = True
        .PivotItems("상반기").Visible = False
        .PivotItems("Jan").Visible = False
        .PivotItems("Feb").Visible = False
        .PivotItems("Mar").Visible = False
        .PivotItems("Apr").Visible = False
        .PivotItems("May").Visible = False
        .PivotItems("Jun").Visible = False
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

Step 3: 전체 보기

이번에는 모든 값들을 다 보여주는 코드입니다. 당연히 모든 값의 Visible 속성을 True로 지정하면 되는 것이지요.

Private Sub optTotal_Click()
    With ActiveSheet.PivotTables(1).PivotFields("Month")
        .PivotItems("Jan").Visible = True
        .PivotItems("Feb").Visible = True
        .PivotItems("Mar").Visible = True
        .PivotItems("Apr").Visible = True
        .PivotItems("May").Visible = True
        .PivotItems("Jun").Visible = True
        .PivotItems("1/4분기").Visible = True
        .PivotItems("2/4분기").Visible = True
        .PivotItems("상반기").Visible = True
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

Step 4: 합계 보이기/숨기기

마지막으로, 피벗 테이블의 가로/세로 방향 합계를 보이거나 숨기기 위한 코드입니다.

Private Sub chkShowTotal_Click()
    With ActiveSheet.PivotTables(1)
        .ColumnGrand = chkShowTotal.Value
        .RowGrand = chkShowTotal.Value
        '''피벗 테이블의 합계 부분은 ColumnGrand 또는 RowGrand 속성의 조절을 통해 가능합니다.
        '''속성값이 1이면(즉 체크 표시가 되어있으면) 합계를 나타내고 속성값이 0이면 합계를
        '''숨깁니다.

    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
    If chkShowTotal.Value Then
        MsgBox "데이터의 합계를 표시하였습니다", , "Display Summing//By Exceller"
    End If
End Sub

마무리

코드 자체는 크게 복잡하지 않을 것입니다. 간단한 코드들을 어떻게 잘 조합해서 사용을 하느냐, 어떻게 응용을 하느냐의 문제일 따름입니다.

올 연말은 예년보다 훨씬 정신없이 지나가는 듯 합니다. 강좌 파일은 고사하고 홈 페이지에 답 글 달기에도 힘이 들 경우가 많이 있습니다. 무단 결강(?) 또는 답글이 제 때 올라오지 않더라도 노여워 마시기를 바랍니다.

오늘은 여기까지…

정리 — 피벗 표시 제어 핵심

자주 묻는 질문 (FAQ)

마치며

PivotItems.Visible과 ColumnGrand 속성만 알면 피벗 테이블을 사용자 선택에 따라 자유롭게 늘이고 줄일 수 있습니다.