このテキストはExcelのマクロの解説書ではありませんが、次のような人に役に立つだろうと思うテキストです。
そして、出来たら次のように利用していただくと嬉しいです。
私はプログラムを組みたいと思っていますが、何回も挫折しています。Excelのマクロも詳しいという訳ではありません。
ただ、小さな団体の会計処理を会員の人にお願いするときに、処理を楽にするためにワークシートを作り、マクロをつくり使っていただきました。それがようやく使えそうなものになって来ました。
その小さな団体が出来てから10年になります。「Excelでゆっくり育てる会計簿」からこの「マクロ編」まで遠回りしてきた軌跡のようなものです。ゆっくりお付き合いいただける方は、実際にExcelを操作して進んでください。お急ぎの方はサンプル・ファイルを使ってみてください。
例えばWebに日経パソコンPC Online「「やさしく学ぶExcelVBA」(牧村あきこ)が連載されています。参考にしてください。http://blog.nikkeibp.co.jp/pconline/vba/2006/11/makimura1.html
真っ白いワークシートにマクロでそれぞれのワークシートを作るところから始めることも出来るでしょうがここは「データ入力だけをマクロで入力する」ことにします。出来そうなものを先ず実現して、その基礎の上に更に追加の機能を積み上げていくわけです。
詳しいことは判りませんが、Excelのマクロを理解するには「VBA」(Visual Basic for Application)というプログラム言語を知らなければなりませんので、敷居が凄く高く正面から取り掛かっては、私たち(高齢者)には歯が立ちません。
しかし、部分的に手作業を補う程度のものを作って、それが便利だと感じたら更に機能を高めていく、失敗したら手作業に戻り考え直す。ということを繰り返していくと次第に機能の高いもを作ることが出来ると思います。
他のことをやるときにも共通することですが、次のようなことを心がけたいと思っています。
現金出納簿と収入・支出内訳書と振替(預金)口座、現在額の5つのワークシートをあらかじめ作っておきます。
「Excelでゆっくり育てる会計簿」の次の項でで作った、
現金出納簿・収入内訳書・支出内訳書・現在額内訳書を使うほか、現金以外の勘定のある場合の出納簿を用意します。現金以外の勘定のある場合も収・支内訳書については現金勘定と同じ収・支内訳書に入力します。
なお、次のプログラム編になってからもワークシートの内容は変更されることがあります。
A | B | C | D | E | F | |
---|---|---|---|---|---|---|
1 | 現金出納簿 | |||||
2 | ||||||
3 | 日付 | 項目 | 入金額 | 出金額 | 残高 | 備考 |
4 | 合計額 | |||||
5 | 繰越金 | |||||
6 | ||||||
7 | ||||||
8 | ||||||
9 | ||||||
10 |
A | B | C | D | E | F | G | H | I | |
---|---|---|---|---|---|---|---|---|---|
1 | 収入内訳書 | ||||||||
2 | 予算額 | 会費 | 寄付金 | 雑収入 | 合計 | ||||
3 | 入金額 | 入金額 | |||||||
4 | 予算残額 | 予算残額 | |||||||
5 | 日付 | 項目 | 入金額 | 項目 | 入金額 | 項目 | 入金額 | ||
6 | |||||||||
7 | |||||||||
8 | |||||||||
9 | |||||||||
10 |
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 支出内訳書 | ||||||||||||||
2 | 予算額 | 事業費 | 通信費 | 会議費 | 事務費 | 消耗品費 | 雑費 | 合計額 | |||||||
3 | 出金額 | 出金額 | |||||||||||||
4 | 予算残額 | 予算残額 | |||||||||||||
5 | 日付 | 項目 | 出金額 | 項目 | 出金額 | 項目 | 出金額 | 項目 | 出金額 | 項目 | 出金額 | 項目 | 出金額 | ||
6 | |||||||||||||||
7 | |||||||||||||||
8 | |||||||||||||||
9 | |||||||||||||||
10 |
A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|
1 | 収支現在額 | |||||||
2 | 収 入 | 支 出 | ||||||
3 | 項目 | 予算額 | 現在額 | 対予算 | 項目 | 予算額 | 現在額 | 対予算 |
4 | 繰越金 | 事業費 | ||||||
5 | 会費 | 通信費 | ||||||
6 | 寄付金 | 会議費 | ||||||
7 | 雑収入 | 事務費 | ||||||
8 | 消耗品費 | |||||||
9 | 雑費 | |||||||
10 | 合計 | 合計 | ||||||
11 | 現金残額 | 照合 | ||||||
12 | 合計 |
現金以外の勘定には、預金口座や振替口座があると思いますが、それぞれ使い方によって入力することも違いますから、ここでは主に会費を郵便振替で払い込んでもらうことを想定します。
郵便振替は払い込みなど口座に出入りがあると郵便振替通知票が郵送されてきますので、これにより経理できます。
A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|
1 | 郵便振替口座出納簿 | |||||||
2 | ||||||||
3 | 年月日 | 通知票 号数 | 受入項目 | 受入金額 | 払出項目 | 払出金額 | 現在高 | 摘要 (人数) |
4 | 合計 | |||||||
5 | 繰越金 | |||||||
6 | ||||||||
7 | ||||||||
8 | ||||||||
9 | ||||||||
10 |
このシートが使用されると口座残額が出来ますから、現在額シートで出入り金額をチェックする照合に[振替残額]を設欄して算入しなければならなくなります。
ここまでに用意した現金出納簿・収入内訳書・支出内訳書・現在額・郵便振替口座出納簿の数式の入ったサンプルブックを参考にしてください。sampl.xls 31.0KB 圧縮ファイル sampl.lzh 7.0KB
ブックの最初に新しいシートを挿入して「入力」とシート名をつけます。
この入力シートでデータを入力して、ボタンをクリックすると必要なシートの該当のセルにデータが入力されるようにマクロを作ります。
データをマクロで入力する必要のある場面と、入力するデータを考えます。
出納簿 | 作業場面 | データを入力するシート | データ |
---|---|---|---|
現金 | 入金 | 現金出納・収入内訳 | 月日・収入科目・項目・入金額・備考 |
出金 | 現金出納・支出内訳 | 月日・支出科目・項目・出金額・備考 | |
振替 | 会費の払込 | 郵便振替口座出納簿・収入内訳・支出内訳 | 月日・通知票号数・項目・受入額・払出額・適用 |
一般の出入 | 郵便振替口座出納簿・収入内訳・支出内訳 | 月日・通知票号数・収支科目・項目・受入額・払出額・適用 | |
振替・現金間の移動 | 現金出納・郵便振替口座出納簿 | 月日・通知票号数・項目・移動金額・適用 |
この他に納入された会費の会員別管理などが必要になりますが、同じブック内に作るにしてもこのテキストの「会計簿とは別」と考えます。
また、一旦入力したデータが間違っていたときに、個別に手作業でシートを修正すると間違いの元になりますので、削除するシートの月日セルを全部選択した状態でマクロにより関係する行を削除する方法を考えます。
フォームを使って入力作業場面をタブて切り替える方法もありますが、ここでは単純に入力シートの中に入力作業場面の表を作り、その中のボタンをクリックするとマクロで入力する方法を取ります。入力作業場面の表が多くなりますから、メニューを作り入力作業場面の表に移動できるようにします。
A | B | C | D | E | F | G | H | I | J | K | |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | 入力メニュー | ||||||||||
2 | 入力作業をクリックしてください。入力作業の表へ移動します。 | ||||||||||
3 | |||||||||||
4 | 現金出納簿 | ||||||||||
5 | 1 入金 | 現金出納簿・支出内訳書に入力します。 | |||||||||
6 | 2 出金 | 現金出納簿・支出内訳書に入力します。 | |||||||||
7 | |||||||||||
8 | 振替口座 | ||||||||||
9 | 3 会費 | 会費について振替口座出納簿・収入内訳書・支出内訳書に入力します。 | |||||||||
10 | 4 一般 | 会費外の振替口座出納簿・収入内訳書・支出内訳書に入力します | |||||||||
11 | 5 現金・振替間移動 | 現金出納簿・振替口座出納簿間の移動を入力します。 | |||||||||
12 | |||||||||||
13 | 修正 | ||||||||||
14 | 6 入力済みデータの削除 | 間違ったデータを削除します。必要なら正しいデータを再入力してください。 |
背景色の入っているセル(B5,B4,B9,B10,B11,B14)には、ボタンをつくりマクロを起動してこのシート内の入力する表に移動するのですが、今はハイパーリンクで入力する表へ移動できるようにしてみます。設定の仕方は例えば
A | B | C | D | E | F | G | H | I | J | K | |
---|---|---|---|---|---|---|---|---|---|---|---|
19 | 現金出納簿 | ||||||||||
20 | 1 入金 | 入力したらTabキーで次のデータへ移動してください。 | |||||||||
21 | |||||||||||
22 | 処理月日 | 月日を mm/dd 形式で入力してください。 | 番号 | 収入科目 | |||||||
23 | 1 | 会費 | |||||||||
24 | 収入科目 | 収入科目は右の表の番号(1〜3)を入力してください。 | 2 | 寄付金 | |||||||
25 | 入金項目 | 3 | 雑収入 | ||||||||
26 | 入金額 | ||||||||||
27 | 備考 | ||||||||||
28 | |||||||||||
29 | 入金入力 | 入力取消 | メニューへ戻る |
「入力したらTabキーで次のデータへ移動してください。」と書いてありますが、このままではTabキーを押しても[処理月日] ⇒ [収入科目]という順にアクティブセルが動いてくれません。シートが出来上がってから、入力するセルのロックを外してシートに保護をかけると、Tabキーで移動できるようになります。
「メニューへ戻る」は、セルA1へ移動するハイパーリンクを設定します。「入金入力」と「入力取消」は、マクロを作ってからボタンを作ってマクロを起動するところです。
入力するセルには入力規則を設定します。C22[処理月日]は年数を入れると間違ったときに離れた違う行に入力されてしまいますので、年度毎に会計簿を作るのですから、年数の表示をしないよう、セルの表示形式を「月日」のみにします。C24「収入科目」には収入番号の1〜3のみ入力できるよう設定します。
A | B | C | D | E | F | G | H | I | J | K | |
---|---|---|---|---|---|---|---|---|---|---|---|
33 | 現金出納簿 | ||||||||||
34 | 2 出金 | 入力したらTabキーで次のデータへ移動してください。 | |||||||||
35 | 番号 | 支出 | |||||||||
36 | 処理月日 | 月日を mm/dd 形式で入力してください。 | 1 | 事業費 | |||||||
37 | 2 | 通信費 | |||||||||
38 | 支出科目 | 支出科目は右の表の番号(1〜6)を入力してください。 | 3 | 会議費 | |||||||
39 | 支出項目 | 4 | 事務費 | ||||||||
40 | 出金額 | 5 | 消耗品費 | ||||||||
41 | 備考 | 6 | 雑費 | ||||||||
42 | |||||||||||
43 | 出金入力 | 入力取消 | メニューへ戻る |
A | B | C | D | E | F | G | H | I | J | K | |
---|---|---|---|---|---|---|---|---|---|---|---|
48 | 振替口座出納簿 | ||||||||||
49 | 3 会費 | 入力したらTabキーで次のデータへ移動してください。 | |||||||||
50 | |||||||||||
51 | 処理月日 | 月日を mm/dd 形式で入力してください。 | |||||||||
52 | |||||||||||
53 | 通知票号数 | 通知票の号数を入力してください。 | |||||||||
54 | |||||||||||
55 | 受入金額 | ||||||||||
56 | 項目 | 継続会員 | 準会員 | 新規会員 | 会員の種類別の人数を入力してください。 | ||||||
57 | 0 | 0 | 0 | 継?準?新?と入力されます。 | |||||||
58 | 継0準0新0 | ||||||||||
59 | 受入金額 | ||||||||||
60 | 項目 | 振替手数料 | |||||||||
61 | 適用(人数) | 会費を納入した総人数などを入力 | |||||||||
62 | |||||||||||
63 | 入金入力 | 入力取消 | メニューへ戻る |
B列の幅を少し広くすれば「通知票号数」と「適用(人数)」はセルを結合しなくても済みます。
[項目]の会員の種別は個人別に確かめて入力しますが最初は[0]を入力しておきます。会員の種別別の把握が必要ない場合は単に項目の入力だけでも良いでしょう。
セルC58に次の数式を入力しておきます。 ="継"&C57&"準"&D57&"新"&E57 このセルのフォントは背景色と同じにして見えなくしておく方法もあります。
A | B | C | D | E | F | G | H | I | J | K | |
---|---|---|---|---|---|---|---|---|---|---|---|
67 | 振替口座出納簿 | ||||||||||
68 | 4 一般 | 入力したら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 | 会議費 | ||||||||
83 | 4 | 事務費 | |||||||||
84 | 5 | 消耗品費 | |||||||||
85 | 受払入力 | 入力取消 | メニューへ戻る | 6 | 雑費 |
A | B | C | D | E | F | G | H | I | J | K | |
---|---|---|---|---|---|---|---|---|---|---|---|
87 | 振替口座出納簿 5 現金・振替間移動 | ||||||||||
88 | 入力したらTabキーで次のデータへ移動してください。 | ||||||||||
89 | 処理月日 | 月日を mm/dd 形式で入力してください。 | |||||||||
90 | |||||||||||
91 | 通知票号数 | ||||||||||
92 | |||||||||||
93 | 移動金額 | ||||||||||
94 | 項目 | ||||||||||
95 | 備考 | ||||||||||
96 | |||||||||||
97 | 現金 ⇒ 振替口座 | 振替口座 ⇒ 現金 | 入力取消 | メニューへ戻る |
A | B | C | D | E | F | G | H | I | J | K | |
---|---|---|---|---|---|---|---|---|---|---|---|
100 | 修正 | 6 入力済みデータの削除 | |||||||||
101 | |||||||||||
102 | 入力作業ごとにデータを削除します。削除する作業番号を入力してください。 | ||||||||||
103 | 削除するデータのあるシートの「月日」セルを全部を選択して、下の「誤入力行の削除」をクリックしてください。 | ||||||||||
104 | |||||||||||
105 | 作業番号 | 削除行(月日セルを選択する箇所) | |||||||||
106 | 1 | 現金・収入 | 「現金出納」「収入内訳」シートの2箇所 | ||||||||
107 | 2 | 現金・支出 | 「現金出納」「支出内訳」シートの2箇所 | ||||||||
108 | 3 | 振替口座・会費 | 「振替口座」「収入内訳」「支出内訳」シートの3箇所 | ||||||||
109 | 4 | 振替口座・会費 | 「振替口座」「収入内訳」「支出内訳」シートの3箇所 | ||||||||
110 | 5 | 現金・振替口座間の移動 | 「現金出納」「振替口座」シートの2箇所 | ||||||||
111 | |||||||||||
112 | 削除する作業の番号 | ||||||||||
113 | |||||||||||
114 | 誤入力行の削除 | 入力取消 | メニューへ戻る |
以上で入力シートの形が一応出来ました。
それぞれの表の範囲を色分けして置き、入力するセルは白色にしておくと判り易いと思います。
ここまでのサンプルファイル sampl-2.xls 50.5KB 圧縮ファイル sampl-2.lzh 14.9KB
入力シートのメニューの表にある「1 入金」(セルB5)から「6 入力済みデータの削除」(セルB14)までにボタンを作ります。
アクティブセルを移動するときに、移動先が入力する最初のセルになっていると便利です。シートの上の方から入力する下のセル(例えばC36)へ直接ハイパーリンクで移動すると、ウインドウの下に36行目がかろうじて見えるように移動します。この場合[現金出納簿 2 出金]の表が全部見える43行まで見えるようにしたいものです。そのためには一旦A43へハイパーリンクで移動して、最初の入力セルC36を選択して入力することになります。
これをマクロで[現金出納簿 2 出金]の表全体を表示して最初の入力セルC36を選択することが出来ます。マクロを起動するためにボタンを作ります。(オートシェイプの図形でもマクロを起動することが出来ます。)
ハイパーリンクの削除の方法
セルを右クリックして、ショートカットメニューで[ハイパーリンクの削除]をクリックします。
セルB5からB14までにハイパーリンクが設定されてあるときは削除しておきます。
ボタンをシートに貼り付けるには色々な方法がありますが、一つの例です。
同様にしてセルB6,B9,B10,B11,B14にボタンを作ります。ボタンには作った順にCommandButton1 , CommandButton2 , CommandButton3 ,・・・・と言うように名前が付きます。失敗して削除して作り直すと数字は進みますから、なるべく作り直さないで順に作った方が後で混乱しなくて良いと思います。大きさなどは後でプロパティで設定できます。
セルに貼り付けたボタンは高さが短いのでよく見えませんが、CommandButton1と表示されています。これを[1 入金]などと変更し、大きさを整えます。
ボタンの高さとセルの高さが違うのでボタンが重なって見えます。ボタンはセルの大きさに関係なく作ることが出来ますが、この場合ボタンの説明との位置関係が揃っていないと具合が悪いです。
行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
これで準備が出来ました。次はいよいよ「プログラム編」です。