[VBA] 완전 쉬운 엑셀 피벗테이블 VBA로 자동 생성하는 법, 피벗테이블 부분 합 없애기

안녕하세요 케이네입니다^^

복직후 바로 강도높은 업무가 시작되었어요. 반복된 업무를 조금이라도 줄여보고 싶어서 연구하다가 엑셀에서 사용되는 VBA라는 기능을 활용해 보기로 했어요. 인터넷에서 찾아보면서 케이네에게 맞게 커스터마이즈 했답니다. VBA를 모르는 케이네도 인터넷 짜집기로 만들수 있었으니 다른 워킹맘 분들도 분명 만드실 수 있을거라 생각해요.

 

먼저 케이네가 자동으로 만들고 싶었던 피벗테이블을 보여드릴게요.

월말 마감때가 되면 하루에도 어러번 집계데이터를 추출하는데요 하루 한번 만드는거면 그냥 엑셀의 피벗테이블 메뉴에서 드래그&드롭으로 하겠는데 매 시간마다 새로 데이터를 추출해서 피벗테이블 항목을 드래그& 드롭으로 만들려고 하니 번거로웠어요.

 

케이네가 쓰는 ERP시스템이 SAP인데 거기에서 추출된 데이터의 항목이 방대해서, 드래그&드롭으로 항목 찾는데만 세월아네월아 하거든요.

대략 항목만 58개 ㅠㅠ

처음에 케이네가 자동화를 위해 사용한 방식은 엑셀의 매크로 기록 방식을 이용하는 것이었어요.

 

하지만 매크로 방식을 이용하니 SAP에서 추출된 데이터에서 한자나 일본어등의 항목이 깨져서 표기가 되기도 하고, 다른 파일에서 매크로를 적용하려고 하면 만들어진 VBA가 항목을 불러올때 필드명이 깨져서 제대로 항목을 가져오지 못했어요.

일본어가 VBA에서 깨져서 나오기 때문에 필드를 불러오지 못했어요

VBA에서 피벗테이블을 만들때 필드를 추가 하려고 하면 PivotFields("항목이름") 의 형태로 불러오는데 일본어로 된 데이터를 불러오려고 하니 잘 안되더라고요. 대신 PivotFields(5) 와 같이 항목의 번호를 입력하면 잘 불러와요. 항목의 번호를 확인 하는 방법은 엑셀의 데이터셀 에서 =Column() 을 입력 하시면 확인 가능해요.

 

이런 식으로 항목의 번호를 확인해서 일본어가 깨진 항목을 일일이 전부 수정했는데, 매크로 기록을 사용하니 아래와 같이 엄청 긴 코드가 생성되었어요. 이중에 진짜 필요한 코드는 뭐인지 VBA초짜인 케이네는 알수가 없죠. 이 방법으로도 충분히 잘 움직이기는 했는데 매번 복사해서 사용하기에는 코드가 넘 긴것 같아서 좀 더 간단하게 하는 방법을 찾아보기로 했어요. 

매크로 기록으로 생성한 VBA 피벗테이블 코드

 

그리고 인터넷을 뒤져 짜집기해서 완성한 VBA코드입니다.

 

Option Explicit
Sub PivotSales()

Dim pt As PivotTable
Dim pc As PivotCache

Dim sd As Worksheet, ss As Worksheet
Dim cs As Range, cd As Range

Set ss = Sheets("raw")
Set sd = Sheets("Sheet1")

Set cs = ss.Range("a1")
Set cd = sd.Range("a1")

sd.Activate
sd.Cells.Clear

Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, cs.CurrentRegion)
Set pt = pc.CreatePivotTable(cd, "SalesSummary")


With pt
.RowAxisLayout xlTabularRow
.PivotFields(18).Orientation = xlRowField
.PivotFields(27).Orientation = xlRowField
End With


With pt.PivotFields(21)
.Orientation = xlDataField
.Name = "Qty(M)"
End With


With pt.PivotFields(26)
.Orientation = xlDataField
.Name = "Amount(JPY,USD,KRW)"
End With


With pt.PivotFields(28)
.Orientation = xlDataField
.Name = "Amount(KRW)"
End With

Call PvtNoSubTtl
 
End Sub

Sub PvtNoSubTtl()
         
            Dim pt As PivotTable
            Dim pf As PivotField
            On Error Resume Next
         
            For Each pt In ActiveSheet.PivotTables
                pt.RowAxisLayout xlTabularRow
             
            Next pt
         
            For Each pt In ActiveSheet.PivotTables
                For Each pf In pt.PivotFields
                    pf.RepeatLabels = False
                    pf.Subtotals(1) = True
                    pf.Subtotals(1) = False
                Next pf
            Next pt

End Sub
   

1) 파란색으로 기재되어 있는 부분은 피벗테이블을 생성하기 위한 사전작업이라고 해요. 일단 raw 데이터를 모두 읽어와서 저장한 다음에 피벗을 만들기 위한 준비라고 합니다. 일단 무조건 적고 들어가야 하는것이라고 생각하면 될것같아요.

 

2) 빨간색으로 기재된 부분이 커스터마이즈를 할 부분이에요. 

.PivotFields(18).Orientation = xlRowField 이런식으로 어떤 필드에 대하여 보고싶은지 나열 하면 됩니다. 예를들어 고객사별로 보고싶거나, 지역별 등 어떤것에 대한 정보를 보고싶을때 이 부분을 수정하시면 되요. raw데이터에서 항목의 번호를 찾는 법은 =COLUMN() 을 셀에 넣으시면 번호가 나와요.

 

어떤'것'에 대한 정보를 보고싶은지 정했으면, '어떤' 데이터를 보고싶은지 적어야 해요. 케이네가 보고싶은 정보는 고객사별 주문한 미터'수량'을 집계내고 싶었어요. 똑같이 =COLUMN() 로 셀 번호를 찾아서 아래와 같이 기입해줍니다. 그리고 완성될 피벗테이블의 항목 이름을 Qty(M)으로 지정하고 싶어요. 

 

With pt.PivotFields(21)
.Orientation = xlDataField
.Name = "Qty(M)"
End With

 

이런식으로 여러개의 집계할 정보를 추가 할 수 있어요.

 

마지막으로 Call PvtNoSubTtl를 불러오는데요, 이게 무엇인가 하면 가장 마지막에 추가한 Sub PvtNoSubTtl()라는 프로시저라는 녀석을 불러와서 실행시키는거라고 해요. 케이네가 PvtNoSubTtl()라는 프로시져를 추가한 이유는 안하면 아래의 그림처럼 나오거든요.

 

피벗테이블 부분합

피벗테이블을 그냥 생성만 하면 자동으로 부분합이 같이 나와서 지저분해 보여요. 물론 디자인항목에서 수동으로 없애는 방법이 있지만, 이런 부분까지도 귀찮아서 인터넷을 뒤적거려 코드를 찾았답니다. 친절하신 능력자 분들께서 인터넷에 올려놔 주셨더라고요. 케이네는 뚝딱뚝딱 떼어다가 사용할뿐...능력자 분들 감사합니다.

 

다른 능력자분들이 만드신 코드들도 많은데 스스로 어느정도 이해하고 있는 편이 코드를 수정하기도 편하고 나중에 다른 raw데이터를 피벗으로 만들고 싶을때 활용이 가능할것 같아요. 더 간략하고 깨끗하고 빠른 코드를 만드시는 분들도 계실것 같은데 케이네의 능력치로는 아직 이정도가 최선이네요. 11월의 마감처리때 잘 활용해 보려고해요!