PROGRAMMING WORKSHOP

Data Table | 워크시트데이타도구..부분합도구만들기

질문중의 가장 많은 것은
워크시트상의 데이타를 보고 싶은대로 집계를 내고 싶은 것이 가장 많다
분석을 위한 집계,보고서용집계를 위하여 피봇테이블도 있고
휠터,부분합등의 메뉴상의 기능으로 수행할 수 있다
이 부분(데이타메뉴상의 기능들)를 VBA를 사용한 좀 더 자동화하여 보도록 하자

아래와 같이 데이타를 만들고

Sub makeDummy() Dim iRow As Integer With Worksheets.Add .Range("A1").Resize(, 2) = Array("구분", "데이타") For iRow = 2 To 1000 .Range("A" & iRow).Resize(, 2) = _ Array(Choose(Int(Rnd() * 5) + 1, "AA", "BB", "CC", "DD", "EE"), _ Int(Rnd() * 1000) + 500) Next End With End Sub

와 같이 하면 AA,BB,CC,, 라는 것에 대한 값이 다양하게
발생한 시트가 만들어 진다
이때 AA별로 합계를 내고 싶고, 평균을 내고 싶고,
갯수를 세고 싶고, 또 통계적으로 필요한 다양한 함수를 적용한..
예를 들면 표준편차를 얻고 싶기도 하고 분산값을 얻고도 싶고
아주 다양해 진다
오래된 화일을 뒤져 보니 이런 것을 다루었던 소루션이 하나 있다
워크시트의 데이타관련 메뉴기능에 매료되었을때 만든 것이라
워크시트함수를 많이 사용하였고 이것이 또한 좋은 프로그래밍방법이다

VBA에 흠뻑빠지면 대개가 순환하면서 값을 찾아서 처리한다
예를 들면 위의 데이타에서 AA가 갖고 있는 값을 합계를 내고 싶다면

Sub doDummyJob() Dim rX As Range Dim iRow As Integer Dim rDatas As Range Dim dblSum As Double Dim shtX As Worksheet Set shtX = ActiveSheet Set rDatas = shtX.Range("A1").CurrentRegion For Each rX In rDatas.Columns(1).Cells If rX = "AA" Then dblSum = dblSum + rX.Offset(, 1) End If Next MsgBox dblSum End Sub

와 같이 하여 처리하면서 흡족해 한다
그러나 워크시트함수를 요령껏 잘 활용하면 좀더 효율적을
값을 얻어낼수 있다
위의 내용을 A열을 기준으로 정렬을 한후
AA값이 되는 부분의 두번째열의 범위를 찾아서
이 부분을

[얻고 싶은 값]=Application.WorksheetFunction.Sum(해당범위)

라고 하면 쉽게 얻을 수 있을 것이다
이와 같은 컨셉으로 좀더 많은 통계함수를 선택하게 하는 요약작업을
몇개의 화일을 통하여 해보도록 하자

STEP_1 ---------------------------------

아래의 그림과 같이 폼이 로딩될때 3개의 콤보상자를..
주어진 테이블의 휠드명을 두개의 콤보상자에 넣고
함수목록을 또 다른 콤보상자에 넣는 작업을 하자



이때 어떤 휠드에 대한 어떤 것을 요약계산할 것이지를 선택하게되는 것
예를 들면
분석할 테이블은 항상 휠드가 다를수 있다
어떤 테이블을 사용자가 분석하고 싶을지 모른다
그림에서 [담당자] 휠드별 [판매액]의 합계를 낼수 있을 것이다
또 [고객회사] 휠드별 [판매액]의 평균을 알고 싶을 수 있을 것이다
그러니 첫번째 콤보상자에는 휠드명의 내용이 숫자라면
[판매액] 휠드별 [고객회사]를 합계를 내거나 평균을 내거나 할수는
없을 것이다
그래서 휠드명을 테이블에서 찾아서 넣을때 만약 해당휠드가 갖고 있는
정보가 숫자값이라면 제외시키는 작업을 하여야 한다

만약 [담당자] 별 계산 휠드를 [고객회사]라고 선택이 된다면
이럴때는 그냥 Count함수로서 처리하도록 한다..즉 어떤 [담당자]가
처리한 [고객회사]가 몇개인지는 분석이 될 수 있으니까..
이런 전제하에 폼을 열때 활성화된 시트상에 유효한 테이블이 있는지
확인 절차와 확인이 되면 유효한 휠드명과 함수명을 콤보상자에
채우는 작업까지 해본다

  • UserForm의 콘트롤의 속성은 런타임에 한꺼번에 처리하는 것이 효율적이다
  • 테이블이 유효한 테이블인지 검증을 한다
  • 유효한 그룹핑할 휠드인지 검사하고 콤보상자에 채운다
  • 함수목록을 배열화하여 콤보상자에 쉽게 직접 전달한다
  • 계산휠드가 될 휠드의 유효성을 체크하고 채운다
  • 매크로이름이 매크로실행대화상자에 나타나지 않게 제어하자

***[LOG-IN]***

STEP_2 ---------------------------------

이제 콤보상자옆에 있는 추가버튼을 크릭하면 사용자가 콤보상자를
통해 선택한 작업휠드와 작업계산함수등을 목록상자에 전달하는 작업을 하고
또 목록상자에 작업하기위한 목록을 더블크릭하면 해당 목록은
취소되게 하는 작업을 하자
작업을 할때는 항상 취소를 할 것이라는것을 감안하는 습관이 좋다
장사하면서 반품을 고려하지 않으면 망하듯이..프로그래밍도
그런 습관을 키워야한다
계산을 하면서 0값과 마이너스값도 발생할수 있다는 것을 항상
염두에 두는 것도 마찬가지다



두개의 성격이 다른 정보를 하나의 목록상자에서 처리하겠다는 것이
중요한 요소이다
첫번째 버튼이 몇개라도 계속등록하는 것은 목록상자의 아랫쪽에
모아 놓도록 하고

두번째 버튼또한 몇개라도 계속 등록하는 것은 목록상자의
윗쪽에 모이도록 하는 것이 좋을 것이다
또 중복되는 것을 또 추가하려고 하면 제어를 해주어야 할 것이다
그리고 추가한후 다음 휠드목록으로 이동하게 자동화시키주는 것이
사용자가 마우스질을 한번이라도 덜 하게 하는 좋은 써비스다

콤보상자의 목록중 현재것을 목록상자에 선택했다
그리고 다음 목록을 선택하게 하려고 한다면
목록상자.AddItem 콤보상자.Value
라고 한다
콤보상자의 목록을 다음 것으로 갱신하려면
콤보상자.ListIndex=콤보상자.ListIndex+1
라고 하면 되겠지만..마지막 목록이였다면 위의 구문은 에러가 날 것이다
에러가 안나게 하려면 당연히 IF문을 써가면서 따져야 할 것이다
간단한 방법이 없을까..?
콤보상자.ListIndex=(콤보상자.ListIndex+1) Mod 콤보상자.ListCount
라고 한다면 몇줄이 필요한 구문을 한줄로 처리하고 말게 된다
왜냐면 5 Mod 5=0 이 되니까..당연히 콤보상자의 최초목록으로
그냥 돌아가게 되는 셈이다..
요런것이 테크닉인 셈이다
프로그래밍은 몇줄을 어떻게 한줄로 딱 끊어서 정리할수 없을까..
궁리하는 재미이다

***[LOG-IN]***

STEP_3 ---------------------------------

좀 기능이 많은 모듈시트를 작성하다 보면
매크로상자에 수많은 프로시져이름이 나타난다
이렇게 만들어서 사용자에게 준다면 사용자들은 공연히 이것 저것
선택하여 실행해보려고 한다



그러다 보면 에러도 나고..사용자들을 헷갈리게 한다
위의 그림과 같이 매크로상자에 프로시져이름들이 나타나지 않게 하는 것이 좋다
또한 아주 중요한 실행을 시작하는 이름만 나타나게 하는 것이 좀더 프로다운
소루션이 된다
그런 테크닉도 살펴보도록 하고..
예를 들면...

'일반 모듈시트에 아래와 같이 입력하고..
Sub insertButtons(Optional iX As Integer)
On Error Resume Next
CommandBars("myButtons").Delete
Dim oBar As CommandBar
Set oBar = CommandBars.Add
Dim shtX As Worksheet
oBar.Position = msoBarFloating
oBar.Visible = True
oBar.Name = "myButtons"
For Each shtX In Worksheets
Dim oCtl As Office.CommandBarButton
Set oCtl = oBar.Controls.Add(Office.msoControlButton)
With oCtl
    .Style = msoButtonCaption
    .Caption = shtX.Name
    .OnAction = "gotoSheet"
End With
Next
End Sub
Private Sub gotoSheet()
Dim sWhichSheet As String
sWhichSheet = CommandBars.ActionControl.Caption
Application.Goto Worksheets(sWhichSheet).Range("A1")
End Sub
Sub deleteButtons(Optional iX As Integer)
On Error Resume Next
CommandBars("myButtons").Delete
End Sub
'ThisWorkBook모듈시트에는 아래와 같이 넣고..
Private Sub Workbook_BeforeClose(Cancel As Boolean)
deleteButtons
End Sub
Private Sub Workbook_Open()
insertButtons
End Sub

매크로상자에는 아무것도 나타나지 않으면서 작업을 한다

이제 목록상자에 무엇을 해 달라고 올라온대로 작업을 하여
시트에 무언가 만들어 줄 단계다
크릭하면 작업을 하기 위한 것중에..
배열로 선언된 함수목록을 나중에 배열인덱스번호에 따라 어떤 함수를
의미하는지 쉽게 식별할수 있도록 하여야 하고
이럴때 필요한 것이 상수목록의 작성이다
짧은 프로시져를 작성할때는 이런 작업이 별로 필요없지만
실전에서는 상수목록작성의 요령이 뛰어나야 한다
아래의 그림과 같이 배열의 각요소에 해당하는 상수를 풀어서
작성한다



상수명을 Public Const SUM As Integer=2 라고 변수나 상수명, 프로시져명들을
이미 엑셀에 지정된(Reserved Words)단어로
사용하면 에러가 나거나 헷갈린다
이럴때는 항상 _ 언더바같은 것을 추가하여 지정된 단어와 구분을 하도록 한다

목록상자에 등록된 정보를 분석하고 배열에 재 정리한다
이것은 본작업을 위한 준비작업이다
아래의 그림과 같이 하나의 목록은 3개의 정보를 갖고 있다



위와 같이 하나의 목록은 3개의 다른 정보가 문자열로 조합된것
이런 떼거리 정보를 체계적으로 수집하는 작업이 본 작업을
시작하기전의 기본작업이 된다
그래서 하나의 목록을 구성하는 각각다른 성격의 3개의 정보를
사용자정의 변수를 선언하고 이것을 배열에 담는다

Type ToDo
   fieldColNum As Integer--------해당휠드의 열번호
   functionID As Integer---------원하는 계산함수번호
   caption As String-------------그룹핑하거나 계산대상 휠드명
End Type

이 소루션의 주된 목적은 엑셀시트에서 사용하는 워크시트함수의
VBA에서의 적절한 활용이 많다는 점을 염두에 두시면 좋을 것이다

***[LOG-IN]***

STEP_4 ---------------------------------

이제 준비작업이 다 끝났고
준비된 정보들을 갖고 원하는 정보를 뽑아낼 차례이다
위에서 이야기한대로 Worksheet함수의 VBA속의 활용의 흥미로움을 보게 된다

긴작업에 들어가면 항상 습관적으로
다른 시트에 수식이 많이 들어 있을경우에는

Dim lCal As Long
lCal=Application.Calculation

Application.ScreenUpdating=False
Application.Calculation=xlCalculationManual
...하고 싶은 작업...
...
...
...
Application.Calculation=xlCal
Application.ScreenUpdating=True

라고 해주는 습관이 좋다
특별히 어떤 작업내용을 관찰하게 하고 싶지 않다면 위와 같이
제어를 해주는 것이 좋다
특히 이런 습관은 VSTO로 엑셀소루션을 만들때
Application.ScreenUpdating=True로 되돌리지 않으면
치명적 오류를 내고 화일이 다운된다
엑셀자체에서 할때는 별문제가 없지만 딴 동네가서
엑셀내에서의 습관대로
Application.ScreenUpdating=False 로 해두고
작업후 다시 True로 돌려주지 않으면 엑셀이 영원히
나타나지 않는다!!
어떤 문헌에도 없는 하다 보니까 발견된 현상들이니..잘 챙기시면 좋을것이다

여러개의 휠드를 차례대로 그룹핑을 하려면 필수적인 것은
정렬작업일 것이다
아래의 그림과 같이 차례대로 그룹핑을 하려고 한다면
당연히 차례대로 정렬을 하여야 할 것이다



지난회화일을 열고 데이타테이블시트를 활성화시킨후 아래의 구문을
실행시켜 보시기 바란다

Sub testTableSort() Dim rData As Range Dim iX As Integer Set rData = ActiveSheet.Range("A1").CurrentRegion For iX = 2 To 5 rData.Sort rData.Columns(iX), xlAscending, , , , , , xlYes MsgBox "x" Next End Sub

차례대로 정렬이 되는 것을 관찰할수 있을 것이다
이것이 된 후 다음 작업이 들어가게 된다
이런 정렬작업을 선행적으로 하여야 하기때문에 임시시트에
데이타를 옮겨서 진행하게 되는 것이다

위의 요령으로
실제 소루션에서는 배열에 그룹핑할 휠드의 열번호만 모아서
이 배열을 순환하면서 정렬을 하면 원하는 것만 되게 된다

이제 마지막 중요한 로직은 아래의 그림과 같이
분류명,제품명,담당자별 그룹핑을 한다고 할때



3개의 휠드의 내용이 모두 같은 것을 찾아서 합계를 내던
평균을 내던 지정된 함수로 계산을 하면 되는 것이다
정렬후 그룹핑하고자 하는 열의 값을 각 행별로 줄줄이
비교하면서 같은 경우를 찾고, 또 다른 값으로 변하는 경우를
찾아서 처리하여야 하는 프로그래밍이 필요하게 된다
이럴때 필요하게 되는 것이 역시 집합체를 사용하던
배열을 사용하던..떼거리 정보의 처리를 하는 테크닉을 갖추어야
풀수 있는 문제가 된다

실은 이런 문제는 피봇테이블을 이용하면 간단하게 처리되지만
코딩의 테크닉을 쌓는 페이지이니..
피벗같은 것은 잃어버리고
하도록 하는 것이다
이런 골때리는 것을 한 후..피벗개체를 활용하게 되면..
피벗개체의 뛰어남과 유용함을 절실히 느끼게 될 것이니까!!!

아무튼 위와 같은 문제는 떼거리정보를 다루는 테크닉의 절정이라고
볼 수 있으니 꼭 직접 만들어 보시는 시간을 할애 하시기 바란다
그래야 피벗개체를 활용을 하던 어떤 개체를 사용하던
모든 응용의 기초가 쌓이는 것이다

아래에 4번째 마지막화일로 완료되었다
많이 확장 응용하시기를..

***[LOG-IN]***