PROGRAMMING WORKSHOP

자재관리 | DashBoard

STEP_9 ---------------------------------

실은 앞페이지의 UserForm상의 분석은 조건이 무척 많아 진다
기간별로 어떻게 할 것인지,
대분류별로는 어떻게 할 것인지,
현장별로는 어떻게 할 것인지,
현장별로 또 해당 현장의 담당자별로 어떻게 할 것인지,
납품업체별로는 또 어떻게 할 것인지,
분석대상은 예정가격으로 할 것인지 , 아니면 집행가격으로 할 것인지
아주 다양한 조합이 발생하게 될 것이다
분석력이 뛰어나다고 하는 것은 바로 어떤 조합을 분석내용을
보고자 하는 사람의 시각에 맞게 만들어 내느냐가 분석도구의 역할이다
이런 종합적인 분석을 쉽게 하는 방법이 없을까??
의 답이 현대사회에서...두 말 할 것없이 스프레드시트이고
스프레드시트중에서도 MS Excel 인것은 세계인 모두가 인정하는 도구이다
이 분석도구를 잘 활용한다고 하는 것은
두 말 할 것없이 분석력이 좋은 사람이 되는 셈이다
그런데 대개가 엑셀을 잘한다고 한다..
엑셀의 수많은 기능 중에서 과연 무엇을 잘 하길래 잘한다고 하는지
잘 모르겠다
원시테이블 하나 덜렁 주고 ..
분석을 이렇게, 저렇게 한 10가지 정도를 한시간만에
해와라 !!! 라고 할때 ..얼마나 잘 해올지..
그런데 문제는 시키는 놈이나, 해 오는 놈이나
엑셀실력이 도토리키재기라면 쓸데없는 짓들을 하는 것이고
그러니 높은 분들은 엑셀을 할 줄은 몰라도 엑셀로 무엇을
할수 있는지를 알면 시치미뚝떼고, 아랫것들을 열나 부려 먹을수 있는
실력이 있는 셈이다..^ ^

VBA를 열나 잘한다고 해도
위와 같은 종합적인 조합은 순환문으로 열나 돌리면 비효율적이고
좀 무식한 짓이 된다
그러면 무엇을 사용하여야 하는가??
엑셀개체를 활용하는 것이다
VBA로 엑셀개체에게 어떻게 일을 요령껏 잘 시킬 것인가??를
생각하는 단계가 되어야지
아는 것이라고는 순환문과 조건문밖에 없다면
컴퓨터를 고생시키는 것이고, 엑셀을 모독하는 셈이다
그럼 어떤 개체를 사용하여야 할까??
두말 하것없이 피벗테이블이다
피벗테이블을 시키면 피벗테이블개체가
숨어서 분석을 하는 요령은 DB를 분석하는 컨셉으로 처리하는 것이다
그래서 속도로 빠르고 효율적인 것이다
그냥 우리는 개체에게 개체가 원하는 매개변수들만
적절히 전달하면 되는 것이다

분석대상이 되는 원시테이블의 열머리가 기준이다
이 열머리를 피벗테이블상의 열쪽으로 보낼것이냐..
행쪽으로 보낼 것이냐..아니면 데이타쪽으로 보낼 것이냐??
등등의 여러분들의 머리속의 분석결과를 생각하면서
전달하면 되는 것이다

피벗테이블은 VBA에서만 사용하는 것이 아니고
이 개체의 적극적 활용이 몸에 베이면 VB.Net같은 곳에서도
엑셀의 파워를 그대로 옮기면 되는 것이니
잘 챙기시는 것이 좋을 것이다
피벗테이블은 피벗테이블을 만들기 전에 피벗테이블의 원시정보를
보관하고 있는 메모리상의 정보보관 개체를 만들어야 한다
이것을 PivotCache 라고 한다
캐시메모리,캐시정보등 많이 들어 보았을 것이다
그렇게 원본정보를 읽어서 피벗테이블용으로 캐쉬가 하나 만들어 지는 것이다
피벗캐쉬하나 만든후 이것을 이용하여 여러개의
피벗테이블이 만들어질수 있는 것이고
피벗캐쉬는 최소한의 하나의 피벗테이블을 갖고 있어야 할 것이다
아래와 같이 피벗케쉬하나 만든후
피벗케쉬의 CreatePivot메소드로 하나의 피벗테이블개체를 만들어 볼수 있다



피벗테이블개체를 만든후 아직 하나도 휠드를 배치하지 않은 상태다
원본테이블의 휠드명이 그대로 옮겨진 대화상자를 볼수 있다
이 휠드를 사용자가 원하는 쪽으로 마우스로 끌어다 놓으면
끌어다 놓는대로 엑셀이 알아서 분석하고 계산한다
이 피벗의 분석엔진을 우리는 VBA로 적절히 이리,저리 갖고 놀면
되는 것이다

중요한 것은 정보를 임시 메모리에 모두 갖고 있는 PivotCache개체를
PivotTable을 만들때마다 새로만들면 화일이 커져서 흉악해진다
하나의 PivotCache에서 여러개의 다양한 시각으로 분석하는
PivotTable을 수도 없이 만들어 낼수 있다는 생각을 잊어서는 안된다



그림과 같이 멍청한 방법과 현명한 방법의 개념을 알면 된다
물론 데이타쏘스가 다른 몇개의 PivotCache개체가 있을수도 있지만
같은 데이타쏘스를 사용하는 여러개의 PivotCache개체가 여러개가 있으면
멍청하다는 이야기다
이곳, 저곳에서 필요에 따라서 피벗테이블 이야기를 하였었다
이 코너에서도 차분하게 하나,하나,정복하는 기회가 되어보시기 바란다
다른 코너에서 이해를 못하였다면, 또 다른 측면으로
같은 내용을 다르게 이야기하는 기회가 되겠다

Sub createPivot_1()
Dim oPCache As PivotCache
Dim oPivot As PivotTable
Dim shtReport As Worksheet
Dim oPFld As PivotField
On Error Resume Next

Set shtReport = getSheet("피벗연습_1")
Set oPCache = getPivotCache("원본")
If oPCache Is Nothing Then
    Application.DisplayAlerts = False
    shtReport.Delete
    Application.DisplayAlerts = True
    Exit Sub
End If

Set oPivot = oPCache.createPivotTable(shtReport.Range("A1"))

MsgBox "피벗테이블의 구조만 만들어진곳이고 휠드배치를 하지 않은 상태"


Set oPivot = oPCache.createPivotTable(shtReport.Range("D1"))
Set oPFld = oPivot.PivotFields("대분류")
oPFld.Orientation = xlRowField
Set oPFld = oPivot.PivotFields("입고수량")
oPFld.Orientation = xlDataField
FormatTable oPivot

MsgBox "대분류를 행방향으로 입고수량을 데이타방향으로 배치" & vbNewLine & _
            "한장의 시트에 두개의 피벗테이블을 만들어 보자"

Set oPivot = oPCache.createPivotTable(shtReport.Range("G1"))
Set oPFld = oPivot.PivotFields("대분류")
oPFld.Orientation = xlRowField
Set oPFld = oPivot.PivotFields("입고수량")
oPFld.Orientation = xlDataField
oPFld.Function = xlCount
FormatTable oPivot


MsgBox "첫째것은 합계 두번째것은 갯수"

Set oPivot = oPCache.createPivotTable(shtReport.Range("J1"))
Set oPFld = oPivot.PivotFields("대분류")
oPFld.Orientation = xlRowField
Set oPFld = oPivot.PivotFields("현장명")
oPFld.Orientation = xlColumnField
Set oPFld = oPivot.PivotFields("입고수량")
oPFld.Orientation = xlDataField
oPFld.Function = xlAverage
FormatTable oPivot

MsgBox "열방향으로도 휠드를 배치하고 계산은 평균으로"

Application.CommandBars("PivotTable").Visible = False
ActiveWorkbook.ShowPivotTableFieldList = False

With shtReport.UsedRange.Rows(2).SpecialCells(xlCellTypeBlanks)
    .EntireColumn.ColumnWidth = 3
End With
End Sub

Function getPivotCache(sOriShtName As String) As PivotCache
Dim rOri As Range
On Error Resume Next
Set rOri = Worksheets(sOriShtName).Range("A1").CurrentRegion
If rOri Is Nothing Then
    MsgBox "원본데이타 확인후 다시하세요"
    Set getPivotCache = Nothing
Else
    If ThisWorkbook.PivotCaches.Count > 0 Then
        Dim oP As PivotCache
        For Each oP In ThisWorkbook.PivotCaches
            Dim sAddress As String
            sAddress = Split(oP.SourceData, ":")(1)
            If Replace(Split(sAddress, "C")(0), "R", "") = rOri.Rows.Count And Split(sAddress, "C")(1) = rOri.Columns.Count Then
                Set getPivotCache = oP
                Exit Function
            End If
        Next
        GoTo MakeNew
    Else
MakeNew:
        Set getPivotCache = ThisWorkbook.PivotCaches.Add(xlDatabase, rOri)
    End If
End If
End Function

Function getSheet(sShtName As String)
On Error Resume Next
Dim shtX As Worksheet
Application.DisplayAlerts = False
Worksheets(sShtName).Delete
Application.DisplayAlerts = True
Set shtX = Worksheets.Add
With shtX
    .Name = sShtName
    .Cells.Font.Name = "맑은 고딕"
    .Cells.Font.Size = 10
End With
ActiveWindow.DisplayGridlines = False
Set getSheet = shtX
End Function

Sub FormatTable(oP As PivotTable)
With oP.TableRange1
    .Font.Name = "맑은 고딕"
    .Font.Size = 10
    .Borders.LineStyle = xlSolid
End With
End Sub

위의 코드는 아래와 같이 하나의 데이타원본에서 하나의 피벗케시를
만들어서 여러개의 피벗테이블을 아래의 그림과 같이 만드는 것의 데모였다



하나의 피벗케시에서 아래와 같이 여러개의 피벗테이블을
CreatePivotTable 메소드로 빵찍어내듯이 만들어 내면 되는 것이다

Set oPivot=oPCache.createPivotTable("피벗테이블이 만들어질 범위의 위치정보")

이렇게 만든후..oPivot개체(피벗테이블)의 구성요소인 다양한 개체에게
일을 시켜서 테이블에 서식도 하고 , 계산방법도 바꾸고..
문제는 PivotTable에게 일을 시키려면
PivotTable을 구성하는 개체를 알아야 한다는 결론이 나는 것이다
이런 개체와 친해지면 번거롭게 계산할일도, 순환을 할일도
별로 없게 되는 것이다
일꾼들이 그냥 일만 시켜주세요..열심히 할께요..
하는데 대화가 안되니 깝깝해지는 것이다
이 대화방법을 숙달시키는 것이 VBA언어와 더불어 엑셀개체와
부단히 대화를 하는 일인 것이다

피벗테이블을 처음 보는 초보님들은 ..이것을 무엇을 어떻게 하라는 거야?? 황당하다..
그런데 실은 일의 순서가 이렇다
어떤 정보가 담긴 테이블을 보았을때, 문제의식을 먼저갖은후
그 문제를 풀 도구를 찾는 것이 순서다
그러나 대개가 정보에 대한 개념들이 없어서
(아쉽게도 우리의 교육방식이 정보분석에 대한 개념이 그들 보다 못하다)
엑셀이 좋다는 소리는 덜렁 들었는데..
그래서 엑셀을 열었는데 , 그리고 피벗테이블만 할줄 알면 된다는
소리도 들었는데..그래서 피벗테이블을 열었는데 황당하다..
당연하다 정보에 대한 문제의식없이
무엇을 보아도 황당하다, 휠터 정도는 이해가 가는 듯하다
하나씩, 차근 차근 문제의식을 만들어 가보자
아래의 구문을 엑셀에서 실행시켜서 테이블을 하나 만들어 놓고
문제를 찾아보자

Sub table_1()
Dim iRow As Integer
With Worksheets.Add
    .Range("A1").Resize(, 2) = Array("데이타A", "데이타B")
    For iRow = 2 To 101
        .Range("A" & iRow).Resize(, 2).Value = _
        Array(Mid("ABCDEFGHIJKLM", Int(Rnd() * 10) + 1, 3), _
        Int(Rnd() * 1000) + 500)
    Next
    With .UsedRange
        .Font.Name = "맑은 고딕"
        .Font.Size = 10
        .Columns.AutoFit
    End With
   With .Cells
        .Font.Name = "맑은 고딕"
        .Font.Size = 10
    End With
End With
End Sub

아주 최소한 간단한 테이블이 하나 만들어졌다
첫째열은 어떤 항목인데..각 항목에 발생한 숫자가 2번째열에 있다
이런 테이블을 보면 이게 뭐지???라는 문제의식이 발동해야 한다
가장 기본적인 생각이 항목별로 합계가 얼마일까???
라는 문제의식이 생긴다
항목별 발생회수는 몇번이지???
항목별 평균발생값은 얼마지??
이런 것이 기본적인 문제일 것이고
좀더 깊이 들어가면 항목별 표준편차는 얼마지?
항목별 분산값은 얼마지??
라는 생각들까지 가면 더욱 좋은 것이고..아무튼 이런 문제가
발생한다
그럼 이것을 종이에 써넣고 전자계산기를 두들기면 바보라는 것은
이제 엑셀때문에 다 안다!!!
간단한 것이니..정렬을 하고 데이타/부분합을 하면 되겠다!!!라는 생각을
하면 그래도 다행이다..
그러나 정보가 복합적으로 엮일때는 부분합, 휠터등으로는 흥미가 없다
뭐 강력한 것이 없을까??
이때 나타나는 것이 바로 피벗테이블인 것이다
한번 만들어 보자
위의 프로시져에 뭔가 좀 추가하여..아래와 같이 실행해보시기 바란다

Sub table_1()
Dim iRow As Integer
Dim rData As Range
With Worksheets.Add
    .Range("A1").Resize(, 2) = Array("데이타A", "데이타B")
    For iRow = 2 To 101
        .Range("A" & iRow).Resize(, 2).Value = _
        Array(Mid("ABCDEFGHIJKLM", Int(Rnd() * 10) + 1, 3), _
        Int(Rnd() * 1000) + 500)
    Next
    With .UsedRange
        .Font.Name = "맑은 고딕"
        .Font.Size = 10
        .Columns.AutoFit
    End With
   With .Cells
        .Font.Name = "맑은 고딕"
        .Font.Size = 10
    End With
    Set rData = .Range("A1").CurrentRegion
    
End With
MsgBox "간단하게 피벗으로 분석해 보자"
createPivot rData
End Sub

Sub createPivot(rData As Range)
Dim shtX As Worksheet
Dim oPivot As PivotTable
Dim oPivotCache As PivotCache
Dim oPivotFld As PivotField
Dim iX As Integer
Set shtX = rData.Worksheet
Set oPivotCache = ThisWorkbook.PivotCaches.Add(xlDatabase, rData)
Set oPivot = oPivotCache.CreatePivotTable(rData.Cells(2).Offset(, 4))
For iX = 1 To 5
Set oPivotFld = oPivot.PivotFields(rData.Cells(2).Value)
oPivotFld.Orientation = xlDataField
With oPivotFld
Select Case iX
    Case 1
        .Function = xlSum
    Case 2
        .Function = xlAverage
    Case 3
        .Function = xlCount
    Case 4
        .Function = xlMax
    Case 5
        .Function = xlMin
End Select
End With
Next
Set oPivotFld = oPivot.PivotFields(rData.Cells(1).Value)
oPivotFld.Orientation = xlRowField
oPivotFld.Position = 2
End Sub

고것참...!!!
그럴듯하게 만들어 진다
서식도 좀 보기좋게 하면 끝내주겠는데..소리가 나와야 한다
별로 의미 없어 보이는 하나의 테이블을 의미 없는 정보로
재생산한 것이다
VBA와 엑셀프로그래밍을 하는 이유는 별 의미없어 보이는 정보를
의미있는 정보로 재가공하는 작업이라고 할수 있고
이런 의미있는 정보재생산을 위하여 엑셀의 다양한 도구가 제공되는 것이다
그중에 가장 강력한 처방이 피벗테이블인 것이다