PROGRAMMING WORKSHOP

Access와 Excel의 연동_13|영업분석
분석...(엑셀의 피벗테이블로...)

이미 작성하였던 코드를 수정하여 옮겨붙이기

실은 앞페이지에서 만든 DB의 구축과 인터페이스의 구성은 별문제가 없다
몇개월동안 발생한 정보가 DB에 저장이 되어있으니..
이것을 이렇게도 보고, 저렇게도 보면서
애완동물의 진료상품명별로 매출현황등을 관찰하면서 새로운 상품을 구성하기도 하고
동물중에서도 어떤 동물이 진료행위를 더 많이 했는지도 관찰하고
고객별로 판촉을 위한 활동의 기준이 되는 데이타를 찾아 볼수도 있고
정보분석가의 특별한 눈을 요구하는 부분이 엑셀과 SQL언어의 화려한 응용부분이 되는 것이다

최근 UNO_Weekly의 PivotTable관련 화일에서 만들었던 내용을 그대로 옮기고
휠드명과 변수등만 좀 고치면 될 것이다..
한번작성하였던 기능을 다른 곳에 옮기는 요령도 필요하겠지..



응용하여 옮기는 작업을 경험하여야 할 것은..
어떤 문제를 풀기위한 소루션을 잘 만들었다면
다음에 어떤 유사한 문제를 풀기 위한 소루션에 부분 활용하고 싶을때..
처음 만드는 소루션의 코드에 대한 주석을 많이 자세하게 달아 두었다면 무척 도움이 된다
어떤 문제해법의 과정을 잘 정리하고 주석을 잘 달아주는 습관... 좋은 습관이다!!

아래의 그림과 같이 MultiPage콘트롤에 Page를 하나 추가하고
추가된 Page콘트롤에 필요한 콘트롤들을 Runtime으로 만들어 붙인다..


''홈이 로딩될때 하지 말고, 
''MultiPage 콘트롤의 Change 이벤트에서 피벗에 사용할 콘트롤을 생성하여 붙인다
Private Sub MultiPage1_Change()
If MultiPage1.Value = 0 Then
    Me.MultiPage1.Pages(1).Enabled = False
    Me.Height = FORM_BIG_HEIGHT
ElseIf MultiPage1.Value = 2 Then
    Me.Height = FORM_SMALL_HEIGHT
    
    '' 추가된 3번째 탭상에 아직 콘트롤이 만들어지지 않았다면..
    If oOption1 Is Nothing Then
        createControls
        setControls
    End If
End If
End Sub

'' 피벗테이블의 조건을 입력하기 위한 콘트롤 Run-Time생성..
Sub createControls()
On Error Resume Next

''UNO_Weekly에서 사용하던 데이타시트를 이 프로젝트에 맞는 시트명으로 바꾸고..
''피벗테이블을 그렸으나, 여기에서는 별도의 시트없이
'' DB에서 가져온 Recordset을 피벗테이블의 쏘스로 사용하면 된다
'' 피벗에 필요한 휠드만으로 구성된 쿼리테이블을 가져와서 Recordset에 담는다
'' Recordset은 전역변수로 선언하여 다른 콘트롤에서 사용하게 한다

Set oRst = getRecordset("SELECT 진료진행테이블.WorkDate," & _
          "진료타입테이블.TreatName, 진료타입테이블.Price, 애완동물테이블.PetName," & _
          "애완동물테이블.State, 애완동물테이블.type, 고객테이블.CustomerName" & _
          " FROM ((고객테이블 INNER JOIN 애완동물테이블 ON " & _
          "고객테이블.CustomerID = 애완동물테이블.CustomerID) INNER JOIN 진료진행테이블 ON " & _
          "애완동물테이블.PetID = 진료진행테이블.PetID) INNER JOIN 진료타입테이블 ON " & _
          "진료진행테이블.TreatID = 진료타입테이블.TreatID ")
          
If oRst Is Nothing Then MsgBox "DB에서 정보를 가져오지 못했습니다, 확인하시고 다시하세요!!": Exit Sub

Dim iStartX As Integer
Dim iStartY As Integer
Dim iGap As Integer
Dim iHeight As Integer
Dim iX As Integer
Dim iZ As Integer
Dim oLocation As Variant
Dim oLbl As MSForms.Label
Dim oCbo As MSForms.ComboBox
Dim oChk As MSForms.CheckBox
Dim oFrm As MSForms.Frame
Dim iFld As Integer

iGap = 3
iHeight = 20
iStartX = 6
iStartY = 6

oLocation = Array("열방향", "행방향", "데이타방향", "페이지방향")
''위에서 DB에서 가져온 oRst의 휠드들을 순환하면서 휠드명을 읽어서 콘트롤을 만든다
For iFld = 0 To oRst.Fields.Count - 1
''휠드명을 표시할 라벨콘트롤 만들기
    Set oLbl = Me.MultiPage1.Pages(2).Controls.Add("Forms.Label.1", "lbl" & oRst.Fields(iFld).Name)
    oLbl.Caption = oRst.Fields(iFld).Name
    oLbl.TextAlign = fmTextAlignRight
    oLbl.Top = (iHeight + iGap) * iX + iStartX
    oLbl.Left = iStartY
    oLbl.Width = 80
''휠드갯수에 맞게 콤보상자를 만들고, oLoaction배열에 담긴 목록을 넣어준다
    Set oCbo = Me.MultiPage1.Pages(2).Controls.Add("Forms.ComboBox.1", "cbo" & oRst.Fields(iFld).Name)
    For iZ = 0 To UBound(oLocation)
        oCbo.AddItem oLocation(iZ)
    Next
    
    '' 선택취소를 위하여 빈값을 하나 추가
    oCbo.AddItem ""
    
    oCbo.Top = oLbl.Top
    oCbo.Left = oLbl.Left + oLbl.Width + iGap
    oCbo.Width = 80

    '' 콘트롤에 이름 지어주기 참조를 쉽게
    oCbo.Name = "cbo_" & oRst.Fields(iFld).Name
    ''콤보상자의 이벤트를 활용하기 위하여 크래스모듈(clsCombo)를 만들고 개체를 생성한다
    '' 사용자정의개체 만들어서 현재만든 콤보상자를
    '' 사용자정의 개체상의 콤보상자와 맵핑시킨다
    Dim objCombo As New clsCombo
    ReDim Preserve oControls(iX)
    Set oControls(iX) = New clsCombo
    Set oControls(iX).oMyCombo = oCbo
    
    iX = iX + 1
Next
''기간의 그룹핑정보를 입력할 체크박스를 담아줄 그룹박스 만들기
Set oFrm = Me.MultiPage1.Pages(2).Controls.Add("Forms.Frame.1", "frm" & "기간그룹")
oFrm.Left = oCbo.Left + oCbo.Width + iGap
oFrm.Height = 35
oFrm.Top = iStartY
oFrm.Caption = "기간그룹핑선택"
oFrm.Enabled = False

'' 쏘스의 정보가 날짜정보일때 날짜의 그룹핑에 사용할 체크박스만들기
For iX = 1 To 4
    Set oChk = oFrm.Controls.Add("Forms.CheckBox.1", "chk" & Choose(iX, "일", "월", "분기", "년"))
    oChk.Caption = Choose(iX, "일", "월", "분기", "년")
    oChk.Left = iStartX + (50 + iGap) * (iX - 1)
    oChk.Top = iStartY
    oChk.Width = 50
Next

'' 피벗보고서 만들기 버튼만들기
Set oBtn = Me.MultiPage1.Pages(2).Controls.Add("Forms.Commandbutton.1", "cmd" & "보고서")
oBtn.Caption = "피벗보고서만들기"
oBtn.Width = 100
oBtn.Height = 20
oBtn.Top = oFrm.Top + oFrm.Height + iGap
oBtn.Left = oFrm.Left

''보고서 타입의 결정을 위한 옵션버튼만들기..
Dim oGroupbox As MSForms.Frame
Dim oOption As MSForms.OptionButton
Set oGroupbox = Me.MultiPage1.Pages(2).Controls.Add("Forms.Frame.1", "frm_reportType")
oGroupbox.Width = 110
oGroupbox.Height = 80
oGroupbox.Left = oBtn.Left + oBtn.Width + 6
oGroupbox.Top = oBtn.Top
oGroupbox.Caption = "보고서타입"
For iX = 1 To 3
    Set oOption = oGroupbox.Controls.Add("Forms.OptionButton.1", "opt_" & Choose(iX, "압축", "개요", "테이블"))
    oOption.Caption = Choose(iX, "압축 형식", "개요 형식", "테이블 형식")
    oOption.Left = 6
    oOption.Top = (iX - 1) * 20 + 6
    oOption.Enabled = False
    Select Case iX
        Case 1
            Set oOption1 = oOption
        Case 2
            Set oOption2 = oOption
        Case 3
           Set oOption3 = oOption
    End Select
Next
oOption1.Value = True

End Sub

아래와 같이 필요한 콘트롤이 질서있게 잘 만들어졌다



DB의 Recordset를 피벗의 쏘스로 사용하기

아무튼 주간강좌 961회 UNO_Weekly의 피벗시리즈중의 하나인 화일의 내용을 복사하여
옮기고, 좀 뜯어 고치자
뜯어 고칠부분은...
콘트롤을 만드는 부분은..
여기에서는 UserForm.Multipage.Controls.Add.....와 같이 콘트롤이 만들어질
상위콘트롤로 경로룰 수정하여야 할것이고
또 다른 중요한 것 하나는..
엑셀의 Range 개체를 피벗테이블의 쏘스로 사용하였던것을 여기에서는
DB의 Recordset를 사용하여야 하는 것..아마도 새로운 경험일 것이다..
그런데 아주 단순하다...

엑셀의 범위를 쏘스로 사용할 경우는...

Set oPCache = ThisWorkbook.PivotCaches.Add(xlDatabase, rSourceTable)
DB의 Recordset을 사용할 경우는 ..

Set oPCache = ThisWorkbook.PivotCaches.Add(xlExternal)
Set oPCache.Recordset = UserForm2.oRst

DB에서 필요한 정보만들 끌어다가(SQL) Recordset에 담아서
피벗테이블을 그린다면, 원본시트같은 것도 필요없으니 씸플하고 단순해지는 셈이다

피벗만들기 버튼을 크릭하여 아래 구문이 실행되면

Private Sub oBtn_Click()
''피벗테이블의 피벗열의 열방향,행방향,페이지방향,데이타방향을
''사용자가 지정한 내용을 수집하기 위한 집합체
Dim oPageCol As New Collection
Dim oRowCol As New Collection
Dim oColCol As New Collection
Dim oDataCol As New Collection
Dim oCtl As Control
Dim sFld As String
Dim bPeriodChecked As Boolean
''날짜열이 있을때 날짜의 그룹핑정보 수집
If Me.MultiPage1.Pages(2).Controls("frm" & "기간그룹").Enabled = True Then
    For Each oCtl In Me.Controls
        If TypeName(oCtl) = "CheckBox" Then
            If oCtl.Value = True Then
                GoTo DO_NEXT
            End If
        End If
    Next
    MsgBox "날짜휠드가 지정되면, 기간별그룹핑을 하나이상 체크하여야 합니다": Exit Sub
End If


DO_NEXT:
'' 콤보상자에서 열별로 배치방향지정내용 수집
For Each oCtl In Me.MultiPage1.Pages(2).Controls
    If InStr(oCtl.Name, "cbo_") = 1 Then
        sFld = Replace(oCtl.Name, "cbo_", "")
        Select Case oCtl.Text
            Case "페이지방향"
                oPageCol.Add sFld
            Case "열방향"
                oColCol.Add sFld
            Case "행방향"
                oRowCol.Add sFld
            Case "데이타방향"
                oDataCol.Add sFld
        End Select
    End If
Next
'' 기간그룹정보 수집
If Me.MultiPage1.Pages(2).Controls("frm기간그룹").Enabled = True Then
    Dim arrGroup(1 To 7) As Variant
    Dim varX As Variant
    Dim iX As Integer
    For iX = 1 To 7
        Select Case iX
            Case 1 To 3
                arrGroup(iX) = False
            Case Else 
'                arrGroup(iX) = Choose(iX - 3, Me.Controls("chk일").Value, Me.Controls("chk월").Value, Me.Controls("chk분기").Value, Me.Controls("chk년")) ' rGroupCriteria.Cells(iX - 3).Interior.ColorIndex = 6
                arrGroup(iX) = Choose(iX - 3, Me.MultiPage1.Pages(2).Controls("chk일").Value, _
                                                            Me.MultiPage1.Pages(2).Controls("chk월").Value, _
                                                            Me.MultiPage1.Pages(2).Controls("chk분기").Value, _
                                                            Me.MultiPage1.Pages(2).Controls("chk년")) ' rGroupCriteria.Cells(iX - 3).Interior.ColorIndex = 6
        End Select
    Next
End If

''수집된 정보의 검증
If Not modPivot.checkValidCol(oColCol, "열방향") Then Exit Sub
If Not modPivot.checkValidCol(oRowCol, "행방향") Then Exit Sub
If Not modPivot.checkValidCol(oPageCol, "페이지방향") Then Exit Sub
If Not modPivot.checkValidCol(oDataCol, "데이타방향") Then Exit Sub
''피벗그리기
modPivot.drawPivotTable oPageCol, oRowCol, oColCol, oDataCol, "myReport", arrGroup, sOri

oOption1.Enabled = True
oOption2.Enabled = True
oOption3.Enabled = True

MsgBox "옵션버튼을 선택하여 보고 싶은 형식의 레이아웃으로 바꿀수 있습니다"
End Sub

아래의 그림과 같이 간단하게 그려진다



엑셀에서 데이타를 분석하는 것은 휠터, 정렬,부분합,요약하기등이 있으나
이것은 모두 피벗테이블을 잘 모르면 하는 하위 분석도구이다
최종적인 분석의 꽃은 피벗테이블인것이니..
어떤 정보이던 피벗테이블로 분석을 하면 그것이 엑셀을 제대로 잘 활용하는 것이 된다
위에서 좀더 처리해야 할 문제는
다른 DB를 사용할때 DB의 휠드갯수가 폼에 콤보상자와 라벨을 만드는 위치를
좀더 계산하는 구문을 넣어야 할 것이다
휠드가 많아지면 현재의 로직으로는 폼의 높이를 넘어서 처리하게 되니까..눈에 안보일 것이다
이것은 만들면서 계산하여 다음 열을 만들어 주어야 할것이다
다음기회에 처리하도록 하고, 다음 작업으로 가자

다음 페이지에서는 피벗테이블이 아닌
DB에서 직접 SQL언어로 분석을 하는 내용을 해보도록 하자
어떤 면에서 피벗은 틀에 딱짜여있어서 보기 어려워 하는 사람도 많으니..
보고서를 보는 사람들의 입맛에 맞추어 이렇게도 분석하고, 저렇게도 분석하여
보여주는 것이 능력있는 정보개발자일 것이다

나머지 콤보상자의 이벤트를 활용하기 위한 크래스모듈
그리고 버튼의 WithEvents 를 활용한 이벤트의 활용등을 관심을 갖고
다음 화일을 보시면 좋겠다





***[LOG-IN]***