PROGRAMMING WORKSHOP

Financial Planning | 새 코드별 과거정보 집계시트만들기

아래의 그림과 같이 코드입력도구를 활용하여
과거정보를 나름대로 재분류 계정을 모두 입력한후
이것을 새로운 과거정보집계시트를 만든다



필요한 작업은

1)테이블만들고
2)새로운 계정별 찾아서 옮기고
3)코드의 상하관계를 구분하여 들여쓰기를 해주고
4)정렬을 하고
5)수정범위를 만들어서 실무자들이 과거정보의 오류를 찾아서 입력한다
6)이때 과거정보수정을 하는 근거 시트를 하나 별도로 준비한다(다음 페이지)
7)각 입력정보는 관련근거를 보여주기 위한 관련수식을 입력한다
8)최종과거년도별계정별금액열을 만들고 이곳도 최종결산수식입력
9)마지막행에는 대변과 차변을 비교하는 수식이 작성된 검증행이 필요할 것이다
모두 입력되고 계산된 내용이 맞으면 True,아니면 False가 떨어지게 하여
수정할수 있는 시각적 단서를 줘야 안심하고 다음 작업을 한다

위의 정도의 작업이 필요하게 되겠다


계정과목의 계정의 레벨에 따라서 들여쓰기를 하게 표현하고 싶을때는
Range개체의 IndentLevel속성을 사용하는 것이 좋다
들여쓰기를 한다고 String(3," ") & 계정과목명 을 하면 문자의 개수가 늘어나
버려 다음 다른 작업할때 혼란스러워진다
진정한 값은 변하지 않고 시각적 표현만 띄워쓰기를 엑셀에게 부탁하는 것이 좋다
될수 있는한 진정한 고유값은 변하지 않게 유지하는 것이 모든 면에서 좋다

앞 페이지에서 modTable모듈시트에서 테이블 만드는 것을 작성했었다
이때는 표준테이블이였고, 지금 만들려고 하는 것은 변종테이블이다
그러니 이미 만들어 놓은 표준테이블을 적절히 수정하여
두개의 테이블을 모두 소화시키게 하자
이럴때 필수적으로 필요한 것이 열거상수(Enum)이라고 하는 것이고
이런 열거상수를 단순한 프로시져작업할때는 별로 사용할 기회가
없지만 이렇게 다양한 형태가 섞일때는 자연스럽게 사용하게 되는 것이
열거 상수이다
테이블을 두가지만드는 것을 하나의 프로시져에서 처리하는 것만
우선 아래화일에서 처리해 보자

***[LOG-IN]***

이제 위에 만든 테이블 열머리밑에 새로운 계정별로
분류한 내용을 하나,하나 옮기면서 수식도 만들어 넣고, 수선을
떨어야 하겠다

그림과 같이 계정별 들여쓰기도 하여야 하고
계정별 단계에 따라 다른 형식의 번호도 붙여 주고
00000 다섯 자리에서 뒤의 두개자리가 번호나
알파벳이 있을때는 [입력계정]이고
나머지는 [합계계정]이다
즉 [합계계정]에서는 자식이 되는 [입력계정]을 찾아서
합산을 하는 수식을 작성하여 넣어주어야 한다
번호를 붙일때 로마문자숫자로 붙여주기를 바란다고 하면
숫자를 로마문자로 바꿔주는
엑셀의 워크시트함수는 ROMAN()를 사용해도 되고
그냥 ASC번호를 사용하여 부여 하여 주어도 좋을 것이다

코드와 계정명을 전달하고 들여쓰기 결과와 번호를
붙이는 것을 아래와 같이 하는 함수를 하나 만들면 좋겠다

Function GetItemTouched(sCode As String, sItem As String) Dim sHead As String Dim iLevel As Integer '별도로 특별히 처리할 계정명이 있으면 Case를 추가하면서 '계속 만들면 될것이고.. Select Case True Case Right(sCode, 4) = "0000" iLevel = 0 Case Right(sCode, 3) = "000" iLevel = 1 sHead = Application.Roman(Val(Mid(sCode, 2, 1))) & "." Case Right(sCode, 2) = "00" iLevel = 2 sHead = Val(Mid(sCode, 3, 1)) & ")" Case Else iLevel = 3 End Select

위와 같은 경우
If ..Then..ElseIf..Then..Else..End If
는 가독성이 떨어지고 많은 것을 분석할때는 헷갈린다
그럴때는 아래와 같이 응용하면 좋다

Select True
..
..
End Select

테이블을 만들면서 합계계정과 입력계정에 있어서
입력계정은 과거년도의 시트의 해당셀을 참조하는 수식을 넣으면
되지만 합계계정은 자신의 코드의 하위코드를 찾아서 수식을 조합
하여야 한다
이때 문제는 프로그래밍적으로 계정코드의 마지막 두자리문자를 판단하여
합계계정인가, 입력계정인가 판단하면 좋은데
실무에서는 그렇게 프로그래밍 편하게 상황을 만들지 않는다
항상 예외, 툭 튀어나온것이 있게 마련이다
그래서 이것을 아래의 그림과 같이 표준코드목록에 열을 하나 더 추가하여
사용자가 합계계정은 육안으로 판단하여 마우스를 크릭하여
체크표시를 하는 사용자지정형식으로 하고
프로그래밍적으로는 표준코드테이블의 마지막열을 읽어서
합계계정과 입력계정을 판단하게 하는 것이 사용자나 프로그래머나
문제의 해법이 된다



더블크릭하면 체크가 되고 지우고 하는 것은 아래와 같이 하면 편리할 것이다

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Range("sumcode"), Target) Is Nothing Then
   Target.Font.Name = "Marlett"
   If Target = "a" Then Target = "" Else Target = "a"
End If
Cancel = True
End Sub


특히 엑셀프로그래밍의 특징은 사용자의 상황의 변화에
즉각즉각 대응 할 수 있는 것이 장점이다

시간이 충분하다면,좀더 노력하여 잘 만든다고 한다면
각 계정별로 프로그래밍적으로
합계를 낼 하위계정이 있는지 찾아서 없으면 입력계정으로 하고
합계를 할 하위계정이 있다면 합계계정으로 할 수도 있을 것이다
실은 이렇게 하는 것이 좀더 섬세한 자동화라고 할 수 있을 것이다
아무튼 소루션은 상황에 따른 도구의 선택이다

이번 화일의 작업내용은 아래 그림과 같다



[새로운 시트]를 만들고 테이블열머리를 만든후
[COA_CODE] 표준 코드시트의 내용을 순환하면서
[새로운 시트]의 열머리밑 첫째셀을 기준으로 뿌려주면서
COA_CD 코드로 [회사과거재무제표(수정후)]시트에서 찾아서
과거년도값을 수식으로 [새로운 시트]의 해당년도를 찾아서 연결한다
코드번호 첫자리가 1에서 3(자산,부채,자본)까지만 한다

중간 차변,대벼년도별 수정열이 완료된후 마지막 완료과거년도열은
왼쪽의 과거년도와 중간의 각 년도별 차대변수정을 감안한
결과값을 표현하는 수식을 작성입력한다

위의 작업이 완료된후 합계계정, 위의 값을 직접갖여온 셀 이외의 것
부분을 각각 순환하면서 해당계정의 하위계정을 코드를 기준으로
찾아서 연결하는 수식을 작성한다

테이블의 맨밑에 검증행을 만든다..
대차관계가 0가 되는 검증을 하여 맞으면 TRUE, 틀리면 FALSE로
표현하는 수식을 입력한다

화일을 실행해 보시고
입력된 수식들을 잘 관찰해 보세요
자동화를 하면서 수식을 입력하여야 하는 경우가 많으니
잘 응용하시기 바랍니다

***[LOG-IN]***