幼稚園の経理システム
現金出納帳から毎月の収入の部・支出の部を計算して予算対比する簡単なシステムです。
それでもエクセルで集計して収入の部・支出の部に転記する作業は時間がかかります。
今回はその作業をボタンひとつで合理化するプログラムです。
B4のセルがアクティブな時に呼び出しボタンをクリックすると科目検索のHelp画面が
表示します。
該当する科目を選んでダブルクリックするとシートのセルにコピーされます。
フォームを作り科目シートのデータをリストボックスに表示させます。
リストボックスの科目名をダブルクリックしても実行ボタンを押しても同じプログラムを使います。
アクティブセルにリストボックスのデータを代入しフォームを閉じる操作です。
最後に作ったフォームをシートの呼び出しボタンにリンクするために標準モジュールに科目検索フォームを開くプログラムを書いておきます。
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub cmdOK_Click()
ActiveCell = lstKamoku.Text
Unload Me
End Sub
Private Sub lstKamoku_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
ActiveCell = lstKamoku.Text
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim i As Long
Dim lastrow As Long
lastrow = Worksheets("科目").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
lstKamoku.AddItem Worksheets("科目").Cells(i, 1)
Next
End Sub
標準モジュールに記入
Sub kensaku()
frmKamoku.Show
End Sub
ボタン一つで現金出納帳を科目ごとに集計して収入の部と支出の部に転記するプログラムです。
(1)収入の部から始めます。
(2)現金出納帳から入金項目を作業シートに取り出します。
入金の列に金額があるという条件で判断します。
Worksheets("現金出納帳").Cells(i, 3) <> 0
(3)取り出したデータを科目で合計し作業1に転記します。
(4)作業1と収入の部の2つのシートを使って収入の部の該当する科目
に転記します。
部品集(データベース)http://vba.asai.net/buhind.html
合計作業1の金額を科目シートに更新する(改良)を使ってください。
収入の部の科目を見やすくするために科目名の前に空白を入れていますので
検索のときに" " & Worksheets("作業1").Cells(i, 1)で検索しています。
(5)収入の部の合計の計算をします。
(6)支出の部は(2)から(5)を繰り返します。
Sub 収入支出()
Dim i As Long
Dim j As Long
Dim lastrow As Long
Dim lastrow1 As Long
Dim goukei As Long
'収入の部
Worksheets("作業").Cells.Clear
lastrow = Worksheets("現金出納帳").Cells(Rows.Count, 1).End(xlUp).Row
j = 1
'現金出納帳を作業シートにコピー
For i = 3 To lastrow
If Worksheets("現金出納帳").Cells(i, 3) <> 0 Then
Worksheets("作業").Cells(j, 1) = Worksheets("現金出納帳").Cells(i, 2)
Worksheets("作業").Cells(j, 2) = Worksheets("現金出納帳").Cells(i, 3)
j = j + 1
End If
Next
'作業シートの科目の合計を作業1に転記する
Worksheets("作業1").Cells.Clear
lastrow = Worksheets("作業").Cells(Rows.Count, 1).End(xlUp).Row
j = 1
For i = 1 To lastrow
goukei = goukei + Worksheets("作業").Cells(i, 2)
If Worksheets("作業").Cells(i, 1) <> Worksheets("作業").Cells(i + 1, 1) Then
Worksheets("作業1").Cells(j, 1) = Worksheets("作業").Cells(i, 1)
Worksheets("作業1").Cells(j, 2) = goukei
j = j + 1
goukei = 0
End If
Next
'作業1の金額を収入の部に転記する
'収入の部の金額をクリアする
For i = 2 To 11
Worksheets("収入の部").Cells(i, 2) = ""
Next
lastrow = Worksheets("作業1").Cells(Rows.Count, 1).End(xlUp).Row
lastrow1 = Worksheets("収入の部").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lastrow
For j = 2 To lastrow1
If Worksheets("収入の部").Cells(j, 1) = " " & Worksheets("作業1").Cells(i, 1) Then
Worksheets("収入の部").Cells(j, 2) = Worksheets("作業1").Cells(i, 2)
End If
Next
Next
'収入の部の合計の計算
'生徒収入=保育料+冷暖房費
Worksheets("収入の部").Cells(2, 2) = Worksheets("収入の部").Cells(3, 2) + Worksheets("収入の部").Cells(4, 2)
'寄付金・補助=寄付金+大阪市補助
Worksheets("収入の部").Cells(5, 2) = Worksheets("収入の部").Cells(6, 2) + Worksheets("収入の部").Cells(7, 2)
'雑収入・その他=おやつ代+遠足代
Worksheets("収入の部").Cells(8, 2) = Worksheets("収入の部").Cells(9, 2) + Worksheets("収入の部").Cells(10, 2)
'収入の計
Worksheets("収入の部").Cells(11, 2) = Worksheets("収入の部").Cells(2, 2) + Worksheets("収入の部").Cells(5, 2) + Worksheets("収入の部").Cells(8, 2)
'支出の部
Worksheets("作業").Cells.Clear
lastrow = Worksheets("現金出納帳").Cells(Rows.Count, 1).End(xlUp).Row
j = 1
'現金出納帳を作業シートにコピー
For i = 3 To lastrow
If Worksheets("現金出納帳").Cells(i, 4) <> 0 Then
Worksheets("作業").Cells(j, 1) = Worksheets("現金出納帳").Cells(i, 2)
Worksheets("作業").Cells(j, 2) = Worksheets("現金出納帳").Cells(i, 4)
j = j + 1
End If
Next
'作業シートの科目の合計を作業1に転記する
Worksheets("作業1").Cells.Clear
lastrow = Worksheets("作業").Cells(Rows.Count, 1).End(xlUp).Row
j = 1
For i = 1 To lastrow
goukei = goukei + Worksheets("作業").Cells(i, 2)
If Worksheets("作業").Cells(i, 1) <> Worksheets("作業").Cells(i + 1, 1) Then
Worksheets("作業1").Cells(j, 1) = Worksheets("作業").Cells(i, 1)
Worksheets("作業1").Cells(j, 2) = goukei
j = j + 1
goukei = 0
End If
Next
'作業1の金額を収入の部に転記する
'支出の部の金額をクリアする
For i = 2 To 17
Worksheets("支出の部").Cells(i, 2) = ""
Next
lastrow = Worksheets("作業1").Cells(Rows.Count, 1).End(xlUp).Row
lastrow1 = Worksheets("支出の部").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lastrow
For j = 2 To lastrow1
If Worksheets("支出の部").Cells(j, 1) = " " & Worksheets("作業1").Cells(i, 1) Then
Worksheets("支出の部").Cells(j, 2) = Worksheets("作業1").Cells(i, 2)
End If
Next
Next
'支出の部の合計の計算
'人件費
' 先生人件費 = 職員人件費 + その他人件費 + 法定福利費
Worksheets("支出の部").Cells(2, 2) = Worksheets("支出の部").Cells(3, 2) + Worksheets("支出の部").Cells(4, 2) + Worksheets("支出の部").Cells(5, 2) + Worksheets("支出の部").Cells(6, 2)
'教育費=図書費+用紙代
Worksheets("支出の部").Cells(7, 2) = Worksheets("支出の部").Cells(8, 2) + Worksheets("支出の部").Cells(9, 2)
'管理費・その他=事務用品+光熱費+交通費+消耗品
Worksheets("支出の部").Cells(10, 2) = Worksheets("支出の部").Cells(11, 2) + Worksheets("支出の部").Cells(12, 2) + Worksheets("支出の部").Cells(13, 2) + Worksheets("支出の部").Cells(14, 2)
'支出の計
Worksheets("支出の部").Cells(15, 2) = Worksheets("支出の部").Cells(2, 2) + Worksheets("支出の部").Cells(7, 2) + Worksheets("支出の部").Cells(10, 2)
MsgBox "作成できました"
End Sub