[가계부 제작기] 엑셀 VBA 복식부기 가계부 만들기 도전 1 (계정설정)
가계부 작성을 시작할 때 가장 먼저 하는 일은 바로 계정항목 만들기일 것이다.
계정이라는 것은 계정과목, 계정항목, 항목, 세부항목, 카테고리, 분류, 대분류, 중분류, 소분류 등등 의미와 쓰임새에 따라 여러가지가 있지만, 나는 계정이라는 단어가 뭔가 전문적인 용어 느낌이 나서 주로 계정이라는 단어를 좋아한다.
계정은 각 가정마다, 사람마다 천차만별이다.
100만명이 가계부를 쓴다고 한다면, 약 100만개의 계정들의 조합이 있다고 해도 과언이 아니다.
자산관리는 기업처럼
가계부 만들기 준비편에서 언급했던 것 중에서
“자산관리는 기업처럼“
이라는 내용이 있었다.
기업의 가계부 역할을 하는 손익계산서에는 주로 아래와 같은 계정으로 이루어져 있다.
- 매출
- 매출원가
- 매출총이익
- 판매비 및 관리비
- 영업이익
- 영업외수익
- 영업외비용
- 법인세차감전순이익
- 법인세비용
- 당기순이익
기업마다 표기하는 방법이 다양하기는 하지만, 기본적인 틀은 위와 별반 다르지 않다.
왜?
굳이 복잡하게 위처럼 기업의 손익계산서 계정을 언급한 이유는 내가 벌어들이고 있는 수입의 종류를 알고 싶기 때문이다.
나는 현재 본업 수입이 있고, (미미하지만) 부업 수입도 있고, 가끔 꽁돈이 생기기도 한다.
지출도 마찬가지다.
이렇게 수입이나 지출의 종류를 나눠놓으면 조금 더 자세한 분석이 가능해지고, 목표를 세울 수 있고, 그 목표를 향해 도전해볼 기회가 생길 수도 있다.
가능형으로 표현한 것은 아직 나에게도 경험이 없기 때문이다.
그냥 '그럴 수 있지 않을까’하는 뇌피셜이다.
엑셀에서 계정설정 시트 만들기
매출총이익, 영업이익, 법인세차감전순이익은 그냥 덧셈과 뺄셈으로 계산하는 것이라 따로 계정을 만들 필요는 없다.
그래서 일단 나한테 필요한 계정은
- 매출
- 매출원가
- 판관비
- 영업외수익
- 영업외비용
- 특별이익
- 특별손실
- 유동자산
- 유동부채
- 자기자본
따라서, 총 10개의 카테고리가 필요했고, 각각 엑셀의 테이블(표)로 지정했다.
액셀에서 테이블은 은근히 인기가 없는 기능인데, 나는 데이터의 갯수에 변동성이 많은 것들은 주로 테이블을 이용한다.
그냥 나만의 엑셀 취향이라고 보면 된다.
왜 10개씩이나 테이블을 작성해야 했는지 설명하기가 쉽지 않은데, 계정을 4단계로 나누다보니 “그냥 보거나 작업하기에 왠지 편안할 것같은 느낌이 들어서"라고 해두자.
카테고리나 분류 등은 일반적으로 구성의 깊이에 따라 대분류, 중분류, 소분류로 나누는 경우가 많다.
대개는 이렇게 3가지로 나누어서 이용하는데, 어떤 사람들은 여기에 한 번 더 깊게 구성하기 위해 세부항목을 추가해서 4가지로 나눠쓰는 경우도 있다.
나의 경우에는 이렇다.
3단계가 지출 – 식비 – 외식으로 나눈다고 하면, 내가 쓰려는 4단계는 지출 – 판관비 – 식비 – 외식으로 나누게 된다.
보통 4단계를 쓰는 사람들은 판관비라는 계정 대신에 고정비나 변동비라는 항목으로 많이 쓰는 것 같다.
자산과 부채의 경우, 비유동자산과 비유동부채는 제외했다.
이 항목들을 포함시키면 금액이 너무 커져버려서 비교하기가 어려울 것 같기 때문이다.
우선 이대로 사용해보고 필요성을 느끼게 되면 그때가서 추가하면 된다.
실제 사용 예시
나는 위와 같이 계정들을 분류했다.
테이블 위에 있는 단어들은 테이블의 이름이다.
엑셀에서는 테이블을 생성하면 기본적으로 '표1’, '표2’와 같은 이름이 붙는다.
이 부분을 각각의 테이블에 해당하는 계정이름으로 바꾼다.
각 테이블의 용도
좌측에 아무런 값이 없는 (계정이라는) 빈 테이블이 하나 있는데, 방금 전에 작성했던 10개의 테이블의 모든 계정들이, 이 글 아래에서 작성하는 매크로로 인해 자동으로 4단계로 정리되어서 이 계정테이블에 들어가게 된다.
앞으로 계정에 관한 코드를 작성할 때에는 이 테이블을 참조하게 된다.
기업용 손익계산서를, 내가 작성하려는 가계부에서 아래와 같이 적용해 볼 수 있다.
매출 : 본업수입
매출원가 : 본업수입을 얻기 위해 직접적으로 연관된 지출
판관비 : 일상생활에서 기본적으로 먹고사는데 드는 지출
영업외수익 : 비정기적으로 들어오는 수입
영업외비용 : 비정기적으로 나가는 지출
여기에 조금 더 추가해서
특별이익 : 정기적이지도 않고, 비정기적이지도 않은 수입. 1년에 몇 번 안되거나 진짜 뜬금없이 생기는 수입
특별손실 : 위와 마찬가지인데 수입이 아닌 지출
그냥 뻘짓하는 것처럼 보일 수도 있는데, 이런 시스템으로 구축해놓아도 딱히 불이익이나 불편함은 없을 것으로 보이기에 그냥 진행해본다.
시간이 흐른 뒤에 실제로 도움이 되었는지, 안 되었는지 기회가 되면 다시 리뷰를 작성해보도록 하겠다.
VBA 코드 작성
왜 이런 코드를 작성해야하는가에 대한 설명이 생각보다 길어졌다.
실제 코드는 다음과 같다.
Option Explicit
Sub UpdateTableAccounts()
' 계정테이블 변수 설정
Dim tblAccounts As ListObject: Set tblAccounts = wsAccounts.ListObjects("계정") ' 계정테이블 변수
Dim cntColsAcc As Long: cntColsAcc = tblAccounts.Range.Columns.Count ' = 4개 = 계정테이블 컬럼 갯수 ( 1. Lv1 ~ Lv4 )
' 모든 테이블 순환
Dim tblTemp As ListObject ' 모든 테이블을 순환할 임시 테이블변수
For Each tblTemp In wsAccounts.ListObjects
' 계정테이블은 제외
If tblTemp.Name <> "계정" Then
Dim cntColsTemp As Integer: cntColsTemp = tblTemp.HeaderRowRange.Columns.Count ' 각 테이블의 column 수
' 테이블에 항목이 있을 경우에만 실행 ( 없으면 skip )
If tblTemp.DataBodyRange Is Nothing = False Then
Dim i As Integer ' 반복구문에 사용할 변수
For i = 1 To cntColsTemp ' 각 테이블의 컬럼 개수만큼 반복
' 각 테이블의 컬럼 데이터를 한개씩 순환
Dim rngAll As Range ' 각 테이블의 컬럼 데이터를 저장할 개체변수
Set rngAll = tblTemp.ListColumns(i).DataBodyRange
Dim rngC As Range
For Each rngC In rngAll ' 컬럼 데이터를 하나씩 순환할 개체변수
' 계정항목이 있는 경우만 실행 ( 없으면 skip )
If Not IsEmpty(rngC) Then
Dim cnt As Long ' 값이 있는 셀의 갯수를 카운팅 해 나갈 변수
cnt = cnt + 1
Dim arrAccounts() As String ' 각 테이블의 데이터를 병합하여 누적해 나갈 배열변수 ( 최종결과값 ) // 계속해서 증가하기때문에 동적배열 지정
ReDim Preserve arrAccounts(1 To cntColsAcc, 1 To cnt) ' 배열의 갯수를 하나씩 늘려가기
Dim tblName As String: tblName = tblTemp.Name '각 테이블의 이름 ( 매출, 매출원가, 판관비......등 )
' Lv2 (테이블 이름) 기준으로 회계의 5요소를 정하기
Dim Lv1 As String
Select Case tblName
Case "매출", "영업외수익", "특별이익"
Lv1 = "수입"
Case "매출원가", "판관비", "영업외비용", "특별손실"
Lv1 = "지출"
Case "유동자산"
Lv1 = "자산"
Case "유동부채"
Lv1 = "부채"
Case "자기자본"
Lv1 = "자본"
Case Else
Debug.Print Chr(39) & rngC.Value & Chr(39) & "가 포함되어 있는 테이블의 이름 " & Chr(39) & tblName & Chr(39) & "은 올바른 이름이 아닙니다."
End Select
' Lv1 ~ Lv4까지의 값을 배열에 저장하기
arrAccounts(1, cnt) = Lv1
arrAccounts(2, cnt) = tblName
arrAccounts(3, cnt) = tblTemp.HeaderRowRange(i)
arrAccounts(4, cnt) = rngC.value
End If
Next rngC
Next i
End If
End If
Next tblTemp
' 출력
If cnt > 0 Then
With tblAccounts
' 기존의 계정테이블 내용 삭제
If .DataBodyRange Is Nothing = False Then
.DataBodyRange.Delete
End If
' 최종결과값 출력
.ListRows.Add
.DataBodyRange(1).Resize(cnt, cntColsAcc).Value = Application.Transpose(arrAccounts)
End With
End If
End Sub음… 그런데 이걸 어떻게 설명해야 하지? 엄두가 안 나네.
VBA의 지식이 있는 분들에게는 조금이나마 도움이 되길 바랄 뿐이다.
주의사항
혹시나 이 코드를 따라하려는 분들을 위해 (어느 정도 VBA 지식이 있다고 가정하고) 주의사항만 적어본다.
코드 네임
Dim tblAccounts As ListObject: Set tblAccounts = wsAccounts.ListObjects("계정") ' 계정테이블 변수위의 코드에서는 시트를 지정할 때 wsAccounts이라는 코드 네임을 사용하고 있다.
나와 똑같이 코드 네임을 바꿔주던가 아니면 평소에 본인이 쓰던 방식으로 시트를 지정하면 된다.
바꾸는 방법은 아래와 같다.
테이블의 이름
If tblTemp.Name <> "계정" Then앞서 언급했다시피 테이블의 이름은 수동으로 직접 지정해주어야한다.
위 코드의 “계정"은 결과값을 출력할 계정테이블을 뜻한다.
' Lv2 (테이블 이름) 기준으로 회계의 5요소를 정하기
Dim Lv1 As String
Select Case tblName
Case "매출", "영업외수익", "특별이익"
Lv1 = "수입"
Case "매출원가", "판관비", "영업외비용", "특별손실"
Lv1 = "지출"
Case "유동자산"
Lv1 = "자산"
Case "유동부채"
Lv1 = "부채"
Case "자기자본"
Lv1 = "자본"
Case Else
Debug.Print Chr(39) & rngC.Value & Chr(39) & "가 포함되어 있는 테이블의 이름 " & Chr(39) & tblName & Chr(39) & "은 올바른 이름이 아닙니다."
End Select계정테이블뿐만 아니라 모든 테이블을 각각 매출, 매출원가, 판관비, 영업외수익, 영업외비용, 특별이익, 특별손실, 유동자산, 유동부채, 자본이라고 직접 변경해줘야 한다.
변경하는 방법은, 테이블을 선택하면 엑셀 화면 위에 '테이블 디자인’이라는 메뉴가 나타나는데 그걸 클릭하면 위 이미지처럼 나온다. 저기서 바꾸면 된다.
이 블로그는 코딩을 목적으로 만든 블로그가 아니고, 나 또한 개발자도 아니며 혼자 독학으로 습득한 수준이기 때문에 더 이상의 자세한 설명은 생략한다.
출력 결과
좌측의 계정테이블에, 매출부터 시작해서 내가 원했던대로 4단계로 분류된 데이터들이 아주 잘 들어갔다.
엔딩
인터넷상에서 배포되고 있는 엑셀 가계부 중에는 계정이나 항목 등의 갯수 제한을 두고 있는 것도 있다.
대부분 수식이 깨진다는 이유이다.
엑셀 수식은 필요한 곳에 미리 넣어놓아야 작동을 하는데 수식과 관련된 셀이, 수식이 참조하는 범위를 벗어나 버리면 오류가 발생하기 때문이다.
제작자는 넉넉하게 적당한 갯수를 정했을 테지만, 사실 모든 사람들을 고려해서 만든다는 것 자체가 불가능하다.
그렇다면 갯수를 제한하지 않도록 만들면 되지 않을까하고 고른 것이 이 방법이다.
어리석게도 나는 어려운 길을 선택했다.
그냥 개취라고 해 두자.