会計システムの作成
実用的なプログラムとして、会計システムを作ってみます。伝票の仕分けデータをExcelに入力することで、元帳、試算表を作成します。
元帳、試算表とはどのような集計をするのか説明していきますので、簿記の知識のない方でも製作可能と思います。
計算式とVBA自動作成の7つのパターンを使用して作成します。手修正する部分はほとんどありません。
入力データについて
伝票シート、勘定科目シートが入力データとなります。このシートは伝票.xlsxというブックに保存してあります。プログラムを登録するブックとは別のブックです。
通常、プログラムの入ったブックは改定できないように読み込み専用となるよう設定します。
したがって、プログラムの入ったブックに入力データは登録しません。別のブックのデータを処理するように作ります。
勘定科目シート
使用する勘定科目が登録されています。
伝票シート
1月から10月までの取引が入力されています。
借方、貸方と1つの取引で2つの科目が記入されています。伝票no 2の例では、現金が減って、地代家賃を支払ったことを意味します。
伝票no 74 や 77のように、相手科目が複数となる取引もあります。
別ブックのデータを取り込む
まず、プログラムを作りやすくするため、伝票.xlsxから、伝票と勘定科目のデータをシステムブック会計.xlsmにコピーします。
下記手順で作成します。
1)伝票.xlsxを開く
2)勘定科目シートを会計.xlsmの科目シートにコピー
3)伝票シートを会計.xlsmの取引シートにコピー
4)伝票.xlsxを閉じる
1)伝票.xlsxを開く
伝票.xlsxを手操作で開きます。
VBA自動作成にて「ブック読込書込」を選択し、次のように操作します。
作成されたプログラムをモジュールに貼り付けます。
6行目の「pa =」の部分には 伝票.xlsxが保存されているパスが入力されます。
今回 ThisWorkbook.Path (会計.xlsmと同じホルダ)としておきます。
2)勘定科目シートを会計.xlsmの科目シートにコピー
伝票.xlsxを開き、勘定科目シートを選択します。
VBA自動作成にて「項目指定列コピー」を選択し、次のように操作します。
②で会計.xlsmブックを選択するには、ウィンドウボタン+tabですべてのウインドウを表示し切り替えます。
(win10ではフォーム表示中は[表示][ウインドウの切り替え]が使用できなくなったようです)
アクティブシートのA2:B2の項目を会計.xlsmの科目シートA2:B2の項目にコピーする意味です。
作成されたプログラムをモジュールに貼り付けます。
3)伝票シートを会計.xlsmの取引シートにコピー
同じように、伝票データをコピーするプログラムを作成します。
VBA自動作成にて「項目指定列コピー」を選択し、次のように操作します。
アクティブシートのA2:G2の項目を会計.xlsmの取引シートA2:B2の項目にコピーする意味です。
作成されたプログラムをモジュールに貼り付けます。
名前を伝票項目指定列コピーに修正します。
別ブックのデータを取り込むメインプログラム
作成したプログラムが順番に実行されるよう、メインプログラムを下記のように入力します。
3行から7行は、フィルタを解除するプログラムです。
念のため、会計.xlsm 伝票.xlsxの両方のブックのフィルタを解除します。
実行すると、取引シート、科目シートにデータが貼り付きます。このデータを使ってプログラムを作成していきます。
元帳の作成
元帳とは、1件のデータを、借方、貸方ごとに2件として登録するものです。たとえば、科目「現金」は
借方、貸方の両方にあります。それぞれを別のデータとして登録します。そのとき、付属する情報して、日付、伝票番号、コメント、相手科目も登録します。
科目「現金」では次のように作成します。借方にあれば借方金額、貸方にあれば貸方金額に記入します。
すこし面倒なのは、1つの取引の相手科目が複数になる場合の扱いです。
Excelで自動化を進めるとき、複数行がセットになっている場合は、工夫が必要です。
伝票no77の例では、「給与手当」の相手科目は「普通預金,預り金」とします。
下記の手順となります。順番にプログラムを作っていきます。
1) コメント、相手科目のデータを作成
2) 取引シートの借方のデータを元帳シートにコピー
3) 取引シートの貸方のデータを元帳シートに追加コピー
4) グループを科目シートより合成
5) グループ順科目順に分類
6) 残高の式をコピー
7) 科目ごとに行集計
1) コメント、相手科目のデータを作成
一つの伝票番号が複数行にわたるとき、コメント、相手科目が全ての行に表示できるよう、コメント1、借方科目2、貸方科目2を作成する計算式を記入します。
取引シートの先頭行に計算式を記入し、
VBA自動作成にて「計算式記入」を選択し、次のように操作します。
作成されたプログラムをモジュールに貼り付けます。
2) 取引シートの借方のデータを元帳シートにコピー
下記のように、取引シートの借方のデータを元帳シートにコピーします。
一つの伝票番号が複数行にわたるとき、借方科目が空欄となっている場合があります。
まず、始めに、借方科目が空欄の場合表示されないよう、フィルタを掛けます。
取引シートを表示し、
VBA自動作成にて「フィルタ」を選択し、次のように操作します。
作成されたプログラムをモジュールに貼り付けます。
フィルタの条件を修正し、空欄が表示されないようにします。
12行目を C.AutoFilter Field:=5, Criteria1:="<>" に変更します。"<>"は空欄を除く意味です。
取引シートから元帳シートにデータをコピーします。どの列をどの列にコピーするのかを自動設定するために、元帳シートに、コピーする取引シートの項目名を貼り付けておきます。
VBA自動作成にて「項目指定列コピー」を選択し、次のように操作します。
作成されたプログラムをモジュールに貼り付けます。
3) 取引シートの貸方のデータを元帳シートに追加コピー
貸方についても同じように、フィルタ、項目指定列コピーによりプログラムを作成します。
ただし、下記のように、データを追加するにチェックします。
作成されたプログラムをモジュールに貼り付けます。
これまで製作したプログラムを実行します。
科目が空欄の行は非表示となっているためコピーされません。
元帳シートに下記が作成されます。
4) グループを科目シートより合成
グループ(資産、負債、資本、収益、費用)を科目シートより取り込みます。
元帳シートを表示し、
VBA自動作成にて「合成」を選択し、次のように操作します。
作成されたプログラムをモジュールに貼り付け、実行します。グループが表示されます。
5) グループ順科目順に分類
元帳シートをグループ、勘定科目、日付、伝票no順に分類します。
元帳シートを表示し、
VBA自動作成にて「分類」を選択し、次のように操作します。
作成されたプログラムをモジュールに貼り付け、実行します。分類されます。
6) 残高の式をコピー
残高は、「借方金額-貸方金額」または、「貸方金額-借方金額」です。
グループの値により、どちらかを選びます。また、科目が同じ間、累計して表示します。
次の式で計算します。
=IF(B2=B3,N(G2),0)+IF(OR(A3="資産",A2="費用"),E3-F3,F3-E3)
式を記入し、
VBA自動作成にて「式コピー」を選択し、次のように操作します。
作成されたプログラムをモジュールに貼り付け、実行します。
7) 科目ごとに行集計
科目ごとに、借方金額、貸方金額の合計をもとめ、行挿入します。
元帳を表示し、
VBA自動作成にて「行集計」を選択し、次のように操作します。
作成されたプログラムをモジュールに貼り付け、実行します。
科目ごとの、借方金額、貸方金額の合計が青色で挿入されます。
元帳の作成 メインプログラム
作成したプログラムが順番に実行されるよう、メインプログラムを下記のように入力します。
何をしているかわかりやすくするため、プログラム名を修正しています。
また、正しく最後まで実行されたのかがわかるように、
9行、10行であらかじめ元帳シートのフォントカラーの初期化、消去を実施しておきます。
以上で元帳が完成しました。
試算表の作成
試算表とは、下記のようなものです。
科目ごとに借方合計、貸方合計を表示します。また、その差を借方残高または貸方残高として表示します。
元帳の残高計算と同じように、グループによって計算式を変えます。
元帳で借方合計、貸方合計を計算しておりますので、その値を取り込んで作成することにします。
下記の手順で作成します。
1)試算表シートに科目をコピー
2)元帳から借方合計、貸方合計を合成
3)残高計算
4)グループごとに集計
1)試算表シートに科目をコピー
科目シートの内容を試算表シートにコピーします。
科目シートを表示し、
VBA自動作成にて「項目指定列コピー」を選択し、次のように操作します。
作成されたプログラムをモジュールに貼り付け、実行します。
2)元帳から借方合計、貸方合計を合成
試算表の借方合計、貸方合計を元帳シートを使って記入します。
試算表シートを表示し、
VBA自動作成にて「合成」を選択し、次のように操作します。
作成されたプログラムをモジュールに貼り付けます。
元帳シートの合計の行は、勘定科目名に"計"がついています。
作成されたプログラムを修正して対応します。
改定前 KEYD = INS.Range("$D" & I).Value
改定後 KEYD = " " & INS.Range("$D" & I).Value & " 計"
修正後、実行します。
下記表示されます。
3)残高計算
グループが「資産」「負債」なら借方残高に「借方合計-貸方合計」を記入します。
グループが他なら、貸方残高に「貸方合計-借方合計」を記入します。
この式を先頭行に記入します。
VBA自動作成にて「計算式記入」を選択し、次のように操作します。
作成されたプログラムをモジュールに貼り付け、実行します。
4)グループごとに集計
VBA自動作成にて「行集計」を選択し、次のように操作します。
作成されたプログラムをモジュールに貼り付け、実行します。
グループごとの合計が挿入されました。
試算表の作成 メインプログラム
作成したプログラムが順番に実行されるよう、メインプログラムを下記のように入力します。
何をしているかわかりやすくするため、プログラム名を修正しています。
以上で試算表が完成しました。
計算式、および VBA自動作成のパターンを覚えると簡単に事務処理システムを製作できます。
ぜひトライしてみてください。