odake's web page | パソコンで楽しむ | ゆっくり育てる会計簿マクロ編 | ゆっくり育てる会計簿

Excelでゆっくり育てる会計簿マクロ準備編

  1. 何をマクロで処理するか
  2. 用意するワークシート
  3. 郵便振替の出納簿を用意する。
  4. 入力シート

このテキストはExcelのマクロの解説書ではありませんが、次のような人に役に立つだろうと思うテキストです。

そして、出来たら次のように利用していただくと嬉しいです。

私はプログラムを組みたいと思っていますが、何回も挫折しています。Excelのマクロも詳しいという訳ではありません。

ただ、小さな団体の会計処理を会員の人にお願いするときに、処理を楽にするためにワークシートを作り、マクロをつくり使っていただきました。それがようやく使えそうなものになって来ました。

その小さな団体が出来てから10年になります。「Excelでゆっくり育てる会計簿」からこの「マクロ編」まで遠回りしてきた軌跡のようなものです。ゆっくりお付き合いいただける方は、実際にExcelを操作して進んでください。お急ぎの方はサンプル・ファイルを使ってみてください。

例えばWebに日経パソコンPC Online「「やさしく学ぶExcelVBA」(牧村あきこ)が連載されています。参考にしてください。http://blog.nikkeibp.co.jp/pconline/vba/2006/11/makimura1.html

(1)何をマクロで処理するか

真っ白いワークシートにマクロでそれぞれのワークシートを作るところから始めることも出来るでしょうがここは「データ入力だけをマクロで入力する」ことにします。出来そうなものを先ず実現して、その基礎の上に更に追加の機能を積み上げていくわけです。

詳しいことは判りませんが、Excelのマクロを理解するには「VBA」(Visual Basic for Application)というプログラム言語を知らなければなりませんので、敷居が凄く高く正面から取り掛かっては、私たち(高齢者)には歯が立ちません。

しかし、部分的に手作業を補う程度のものを作って、それが便利だと感じたら更に機能を高めていく、失敗したら手作業に戻り考え直す。ということを繰り返していくと次第に機能の高いもを作ることが出来ると思います。

他のことをやるときにも共通することですが、次のようなことを心がけたいと思っています。

このページのTopへ

(2)用意するワークシート

現金出納簿と収入・支出内訳書と振替(預金)口座、現在額の5つのワークシートをあらかじめ作っておきます。

「Excelでゆっくり育てる会計簿」の次の項でで作った、

現金出納簿・収入内訳書・支出内訳書・現在額内訳書を使うほか、現金以外の勘定のある場合の出納簿を用意します。現金以外の勘定のある場合も収・支内訳書については現金勘定と同じ収・支内訳書に入力します。

なお、次のプログラム編になってからもワークシートの内容は変更されることがあります。

シート名 現金出納
ABCDEF
1現金出納簿
2
3日付項目入金額出金額残高備考
4合計額
5繰越金
6
7
8
9
10
シート名 収入内訳
ABCDEFGHI
1収入内訳書
2予算額会費寄付金雑収入合計
3入金額入金額
4予算残額予算残額
5日付項目入金額項目入金額項目入金額
6
7
8
9
10
シート名 支出内訳
ABCDEFGHIJKLMNO
1支出内訳書
2予算額事業費通信費会議費事務費消耗品費雑費合計額
3出金額出金額
4予算残額予算残額
5日付項目出金額項目出金額項目出金額項目出金額項目出金額項目出金額
6
7
8
9
10
シート名 現在額
ABCDEFGH
1収支現在額
2収  入支  出
3項目予算額現在額対予算項目予算額現在額対予算
4繰越金事業費
5会費通信費
6寄付金会議費
7雑収入事務費
8消耗品費
9雑費
10合計合計
11現金残額照合
12合計
このページのTopへ

(3)郵便振替の出納簿を用意する

現金以外の勘定には、預金口座や振替口座があると思いますが、それぞれ使い方によって入力することも違いますから、ここでは主に会費を郵便振替で払い込んでもらうことを想定します。

郵便振替は払い込みなど口座に出入りがあると郵便振替通知票が郵送されてきますので、これにより経理できます。

シート名 振替
ABCDEFGH
1郵便振替口座出納簿
2
3年月日通知票
号数
受入項目受入金額払出項目払出金額現在高摘要
(人数)
4合計
5繰越金
6
7
8
9
10

このシートが使用されると口座残額が出来ますから、現在額シートで出入り金額をチェックする照合に[振替残額]を設欄して算入しなければならなくなります。

ここまでに用意した現金出納簿・収入内訳書・支出内訳書・現在額・郵便振替口座出納簿の数式の入ったサンプルブックを参考にしてください。sampl.xls 31.0KB  圧縮ファイル sampl.lzh 7.0KB

(4)入力シート

ブックの最初に新しいシートを挿入して「入力」とシート名をつけます。

この入力シートでデータを入力して、ボタンをクリックすると必要なシートの該当のセルにデータが入力されるようにマクロを作ります。

データをマクロで入力する必要のある場面と、入力するデータを考えます。

出納簿作業場面データを入力するシートデータ
現金入金現金出納・収入内訳月日・収入科目・項目・入金額・備考
出金現金出納・支出内訳月日・支出科目・項目・出金額・備考
振替会費の払込郵便振替口座出納簿・収入内訳・支出内訳月日・通知票号数・項目・受入額・払出額・適用
一般の出入郵便振替口座出納簿・収入内訳・支出内訳月日・通知票号数・収支科目・項目・受入額・払出額・適用
振替・現金間の移動現金出納・郵便振替口座出納簿月日・通知票号数・項目・移動金額・適用

この他に納入された会費の会員別管理などが必要になりますが、同じブック内に作るにしてもこのテキストの「会計簿とは別」と考えます。

また、一旦入力したデータが間違っていたときに、個別に手作業でシートを修正すると間違いの元になりますので、削除するシートの月日セルを全部選択した状態でマクロにより関係する行を削除する方法を考えます。

フォームを使って入力作業場面をタブて切り替える方法もありますが、ここでは単純に入力シートの中に入力作業場面の表を作り、その中のボタンをクリックするとマクロで入力する方法を取ります。入力作業場面の表が多くなりますから、メニューを作り入力作業場面の表に移動できるようにします。

このページのTopへ

メニュー

シート名 入力
ABCDEFGHIJK
1入力メニュー
2入力作業をクリックしてください。入力作業の表へ移動します。
3
4現金出納簿
51 入金現金出納簿・支出内訳書に入力します。
62 出金現金出納簿・支出内訳書に入力します。
7
8振替口座
93 会費会費について振替口座出納簿・収入内訳書・支出内訳書に入力します。
104 一般会費外の振替口座出納簿・収入内訳書・支出内訳書に入力します
115 現金・振替間移動現金出納簿・振替口座出納簿間の移動を入力します。
12
13修正
146 入力済みデータの削除間違ったデータを削除します。必要なら正しいデータを再入力してください。

背景色の入っているセル(B5,B4,B9,B10,B11,B14)には、ボタンをつくりマクロを起動してこのシート内の入力する表に移動するのですが、今はハイパーリンクで入力する表へ移動できるようにしてみます。設定の仕方は例えば

  1. B4[1 入金]を右クリックする。
  2. ショートカットメニューの[ハイパーリンク]をクリック。
  3. [ハイパーリンクの挿入]ダイアログボックスの[リンク先]で、[このドキュメント内]を選択。
  4. [またはドキュメント内の場所]が[入力]セルを選択されていいることを確認する。
  5. [セル参照を入力してください]のテキストボックス(A1と入力されている)に移動するセル番地(入力する表の最も下のセルが良い)を入力する。
このページのTopへ

現金出納簿 1 入金

シート名 入力
ABCDEFGHIJK
19現金出納簿
201 入金入力したらTabキーで次のデータへ移動してください。
21
22処理月日月日を mm/dd 形式で入力してください。番号収入科目
231会費
24収入科目収入科目は右の表の番号(1〜3)を入力してください。2寄付金
25入金項目3雑収入
26入金額
27備考
28
29入金入力入力取消メニューへ戻る

「入力したらTabキーで次のデータへ移動してください。」と書いてありますが、このままではTabキーを押しても[処理月日] ⇒ [収入科目]という順にアクティブセルが動いてくれません。シートが出来上がってから、入力するセルのロックを外してシートに保護をかけると、Tabキーで移動できるようになります。

「メニューへ戻る」は、セルA1へ移動するハイパーリンクを設定します。「入金入力」と「入力取消」は、マクロを作ってからボタンを作ってマクロを起動するところです。

入力するセルには入力規則を設定します。C22[処理月日]は年数を入れると間違ったときに離れた違う行に入力されてしまいますので、年度毎に会計簿を作るのですから、年数の表示をしないよう、セルの表示形式を「月日」のみにします。C24「収入科目」には収入番号の1〜3のみ入力できるよう設定します。

現金出納簿 2 出金

シート名 入力
ABCDEFGHIJK
33現金出納簿
342 出金入力したらTabキーで次のデータへ移動してください。
35番号支出
36処理月日月日を mm/dd 形式で入力してください。1事業費
372通信費
38支出科目支出科目は右の表の番号(1〜6)を入力してください。3会議費
39支出項目4事務費
40出金額5消耗品費
41備考6雑費
42
43出金入力入力取消メニューへ戻る

振替口座出納簿 3 会費

シート名 入力
ABCDEFGHIJK
48振替口座出納簿
493 会費入力したらTabキーで次のデータへ移動してください。
50
51処理月日月日を mm/dd 形式で入力してください。
52
53通知票号数通知票の号数を入力してください。
54
55受入金額
56項目継続会員準会員新規会員会員の種類別の人数を入力してください。
57000継?準?新?と入力されます。
58継0準0新0
59受入金額
60項目振替手数料
61適用(人数)会費を納入した総人数などを入力
62
63入金入力入力取消メニューへ戻る

B列の幅を少し広くすれば「通知票号数」と「適用(人数)」はセルを結合しなくても済みます。

[項目]の会員の種別は個人別に確かめて入力しますが最初は[0]を入力しておきます。会員の種別別の把握が必要ない場合は単に項目の入力だけでも良いでしょう。

セルC58に次の数式を入力しておきます。 ="継"&C57&"準"&D57&"新"&E57 このセルのフォントは背景色と同じにして見えなくしておく方法もあります。

このページのTopへ

振替口座出納簿 4 一般

シート名 入力
ABCDEFGHIJK
67振替口座出納簿
684 一般入力したらTabキーで次のデータへ移動してください。
69
70処理月日月日を mm/dd 形式で入力してください。
71
72通知票号数通知票の号数を入力してください。
73
74受入金額番号収入科目
75収入科目収入科目は右の表の番号(1〜3)を入力してください。1会費
76入金項目2寄付金
77備考3雑収入
78
79払出金額番号支出科目
80支出科目支出科目は右の表の番号(1〜6)を入力してください。1事業費
81支出項目2通信費
82備考3会議費
834事務費
845消耗品費
85受払入力入力取消メニューへ戻る6雑費

振替口座出納簿 5 現金・振替間移動

シート名 入力
ABCDEFGHIJK
87振替口座出納簿 5 現金・振替間移動
88入力したらTabキーで次のデータへ移動してください。
89処理月日月日を mm/dd 形式で入力してください。
90
91通知票号数
92
93移動金額
94項目
95備考
96
97現金 ⇒ 振替口座振替口座 ⇒ 現金入力取消メニューへ戻る
このページのTopへ

修正 6 入力済みデータの削除

シート名 入力
ABCDEFGHIJK
100修正6 入力済みデータの削除
101
102入力作業ごとにデータを削除します。削除する作業番号を入力してください。
103削除するデータのあるシートの「月日」セルを全部を選択して、下の「誤入力行の削除」をクリックしてください。
104
105作業番号削除行(月日セルを選択する箇所)
1061現金・収入「現金出納」「収入内訳」シートの2箇所
1072現金・支出「現金出納」「支出内訳」シートの2箇所
1083振替口座・会費「振替口座」「収入内訳」「支出内訳」シートの3箇所
1094振替口座・会費「振替口座」「収入内訳」「支出内訳」シートの3箇所
1105現金・振替口座間の移動「現金出納」「振替口座」シートの2箇所
111
112削除する作業の番号
113
114誤入力行の削除入力取消メニューへ戻る

以上で入力シートの形が一応出来ました。

それぞれの表の範囲を色分けして置き、入力するセルは白色にしておくと判り易いと思います。

ここまでのサンプルファイル sampl-2.xls 50.5KB 圧縮ファイル sampl-2.lzh 14.9KB

このページのTopへ

ボタンを作る

メニュー

入力シートのメニューの表にある「1 入金」(セルB5)から「6 入力済みデータの削除」(セルB14)までにボタンを作ります。

アクティブセルを移動するときに、移動先が入力する最初のセルになっていると便利です。シートの上の方から入力する下のセル(例えばC36)へ直接ハイパーリンクで移動すると、ウインドウの下に36行目がかろうじて見えるように移動します。この場合[現金出納簿 2 出金]の表が全部見える43行まで見えるようにしたいものです。そのためには一旦A43へハイパーリンクで移動して、最初の入力セルC36を選択して入力することになります。

これをマクロで[現金出納簿 2 出金]の表全体を表示して最初の入力セルC36を選択することが出来ます。マクロを起動するためにボタンを作ります。(オートシェイプの図形でもマクロを起動することが出来ます。)

ハイパーリンクの削除の方法
セルを右クリックして、ショートカットメニューで[ハイパーリンクの削除]をクリックします。

セルB5からB14までにハイパーリンクが設定されてあるときは削除しておきます。

ボタンをシートに貼り付けるには色々な方法がありますが、一つの例です。

  1. 入力シートで、メニューバーの[表示][ツールバー][コントロールツールボックス]をクリック。ツールのアイコンが並んだ小さいウインドウ(コントロールツールボックス)が開きます。
  2. ボタンの形をした[コマンドボタン]をクリック。マウスポインタが[+]になります。
  3. ボタンを作るセルB5をマウスで左上から右下へドラッグして四角を作ります。ボタンが出来ます。大きさは今は気にしないでセル内にボタンを1個作ります。

同様にしてセルB6,B9,B10,B11,B14にボタンを作ります。ボタンには作った順にCommandButton1 , CommandButton2 , CommandButton3 ,・・・・と言うように名前が付きます。失敗して削除して作り直すと数字は進みますから、なるべく作り直さないで順に作った方が後で混乱しなくて良いと思います。大きさなどは後でプロパティで設定できます。

セルに貼り付けたボタンは高さが短いのでよく見えませんが、CommandButton1と表示されています。これを[1 入金]などと変更し、大きさを整えます。

  1. セルB5のボタンをクリックするとボタンの四隅と上下の中央に[ º ]印が見えます。選択された状態です。
  2. 右クリックしてショートカットメニューの[プロパティ]をクリックします。
  3. プロパティウインドウで左側[(オブジェクト名)]が右側[CommandButton1]ことを確認。
  4. [Caption]が[CommandButton1]となっていますが、これをドラッグして選択して[1 入金]と入力。(Deleteキーで消してから入力しても同じ。)
  5. [Font]が[MSPゴシック]になっているところをクリックして、右側の[ … ]をクリックして、フォントサイズを[10]に変更。
  6. [Height]を[19.5] ボタンの高さを決めます。
  7. [Left]を[54.75] ボタンのシートの左からの距離を決めます。
  8. [Width]を[60]  ボタンの幅を決めます。

ボタンの高さとセルの高さが違うのでボタンが重なって見えます。ボタンはセルの大きさに関係なく作ることが出来ますが、この場合ボタンの説明との位置関係が揃っていないと具合が悪いです。

行5,6,9,10,11,14を選択して(Ctrlキーを押しながらそれぞれの行番号をクリックします。)メニューの[書式][行][高さ]をクリックして、行の高さのテキストボックスに[18.75]と入力します。

ボタンをクリックして選択しボタンの中にマウスポインタを置くと、マウスポインタが十字の矢印になりドラッグするとボタンを移動できます。横の位置はプロパティで他のボタンと揃えます。縦の位置をドラッグしてセルの線に合わせます。

同じようにして、2 出金、3 会費、4 一般のボタンの大きさ位置などをプロパティで決めます。ボタンをクリックすると、プロパティがそのボタンに変わります。

モニタの解像度により違うかもしれませんが、[5 現金・振替間移動]の[Width]は[126]、[6 入力済みデータの削除]]の[Width]は[126]にすると、ボタンの[Caption]が揃うと思います。ボタンを選択したときの右横のハンドル[ º ]印にマウスポインタを当てると、マウスポインタが左右の矢印に変わるから、ドラッグすると横幅[Width]を変更できます。

その他の入力する表

それぞれの入力する表の[メニューへ戻る]はハイパーリンクのままでも問題ありません。

その他の[XX入力][入力取消][現金 ⇒ 振替口座][振替口座 ⇒ 現金][誤入力行の削除]は、マクロを起動するボタンになります。[メニュー]に準じてボタンを作ります。ボタンは並んで揃っていれば良いので、行の高さは気にすることは無いでしょう。

ここまでのサンプルファイル sampl-3.xls 84.5KB 圧縮ファイル sampl-3.lzh 34.1KB

これで準備が出来ました。次はいよいよ「プログラム編」です。


このページのTopへ
連絡:E-mail