クラブ会計簿の作成(2)—会計簿の作成—

エクセルは表計算ソフトです。パソコンならではの機能を使って人為ミスを減らすともによりクリエイティブな活動のために、エクセルを、パソコンを使いましょう。
暗算から筆算へ、そしてソロバン、計算尺などの計算道具、今はコンピュータとなりました。パソコンの機能を活かして使いましょう。

エクセルを使った会計簿を作ってみましょう。
人間は勘違いやうっかりによるミスから逃れることはできません。できるだけ間違いを犯さず、年度末には容易く会計報告書が作れる会計簿にしたいと思います。
内容は3回に分けて書きました。このブログでは会計簿作成について述べています
(1)作成する会計簿の概要(作成時のエクセル2003ファイル)
(2)会計簿作成(エクセル2013で作成します)
(3)年度末会計報告書作成(ピボット集計)

第二回 会計簿の作成

第一回でご説明した会計簿を実際に作りましょう。先ず完成した会計簿を実際に操作して完成品をイメージしてみましょう。

会計簿の入力操作

記述は実際の作成順にそって書きましたので、全体が見えにくいかも知れません。

1.表を作成する範囲に罫線を入れる(A8:H160)1-ekuseru2a

2.表タイトル項目など作成、見栄えよく整える
1)表タイトル項目記入(「年月日」「金額入力」・・・ピボット用の「年」まで)
2)表タイトルを見易くする(色塗り、太字化など)
3)入力範囲を色付け(A9:H160)、罫線も見易く
4)会計簿タイトルをワードアートで作成(「ABC会計簿)
2-ekuseru2

3.入力欄のセル書式を設定
1)年月日の表示書式        2)金額入力の書式設定
3-ekuseru24-ekuseru2

3)「年月日」と「金額入力」は右寄せとする
5-ekuseru2

4)A9とB9セルを「年月日」と「金額入力」欄全体にコピー6-ekuseru2

4.シート名を付ける
Sheet1を「会計簿」、シートを追加して「リスト」とする(リストは入力規則で使用)7-ekuseru2

5.リスト作成「リスト」
「リスト」シートに、「項目」「内訳」欄への入力に使うためのリスト項目表を作成8-ekuseru2

6.作成中のファイルを保存する(折角ここまで作ったので保存しておく)
デスクトップへ会計簿ファイルとして保存する。ファイル名は「会計簿作成練習」9-ekuseru2

7.リストに名前定義付けをする
1)「項目」リスト
B3からH3を選択し、リボンの「数式」→「名前定義」。
表示された「新しい名前」画面で名前を「項目」として「OK]
10-ekuseru2
2)「内訳」リスト
B4:B5を選択、リボンの「数式」→「名前定義」から「新しい名前」画面を表示し、名前を「繰越し」とする。
以下同様に、「同好会会費」・・・「その他の支出」まで名前定義を行う13-ekuseru2

8.「項目」「内訳」欄へデータ入力規則を設定
「項目」の設定
C9セルを選択し、リボンの「データ」→「データ入力規則」。
表示された「データ入力規則」画面で入力値の種類は「リスト」に、元の値は「=項目」と記入し「OK」
11-ekuseru2

「内訳」の設定
D9セルを選択し、リボンの「データ」→「データ入力規則」。
表示された「データ入力規則」画面で入力値の種類は「リスト」に、元の値は「=INDIRECT(C9)」と記入し「OK」
これで左隣のセルの値に等しい名前のリストが選択されることになる12-ekuseru2「項目」と「内訳」設定完了14-ekuseru2

9.「収入」「支出」欄の数式設定
「収入」欄には、「繰越し、同好会、その他の収入」のみが転記されるようにする。
数式は、「=IF(OR(C9=”繰越し”,C9=”同好会会費”,C9=”その他収入”),B9,””)」15-ekuseru2

「支出」欄も同様に
数式は、「=IF(OR(C9=”備品”,C9=”消耗品”,C9=”その他支出”),B9,””)」16-ekuseru2

10.「残高」欄の数式設定
「残高」の計算は誤記入などした行を削除しても、正しく計算できるようにしておく必要がある。
また未記入の欄の「残高」へも余計なものが表示されないようにする必要がある。
数式は、「=IF(AND(E9=””,F9=””),””,SUM(E$9:E9)-SUM(F$9:F9))」17-ekuseru2

11.ピボット集計用欄の設定
ピボット集計を単純に行うため「収支」「金額」「月」「年」の欄に数式を設定する「収支」欄の数式:
=IF(C9=””,””,(IF(OR(C9=”繰越し”,C9=”同好会会費”,C9=”その他収入”),”収入”,”支出”)))「金額」欄の数式: =IF(I9=”収入”,E9,IF(I9=”支出”,-F9,””))
「月」欄の数式: =MONTH(A9)
「年」欄の数式: =YEAR(A9)
18-ekuseru2

12.数式設定欄を全入力行にコピー
E9からL9を選択し、160行までコピー
19-ekuseru2

13.合計欄へ数式設定
常に総合計が見えるように表タイトル行の上に設定した合計欄へ数式を設定する
収入合計(E8セル):=SUBTOTAL(109,E9:E160) — =SUM(E9:E160)で良い
支出合計(F8セル):=SUBTOTAL(109,F9:F160)    — =SUM(F9:F160)で良い
支出件数(G8セル):=COUNTIF($I9:$I151,”支出”)
20-ekuseru2

14.合計欄の書式設定
見易い表示にするため、金額には「円」表示を付け、支出件数は支出件数であることがわかるようにする。「円」表示は「金額入力」で設定したユーザー定義を使い、件数は新たにユーザー定義で「”支出件数は”0″ 件”」と設定する。
21-ekuseru222-ekuseru2

書式も整えた
23-ekuseru2

15.入力域以外を入力・修正不能とする(シート保護)
保護領域設定
入力エリア(A9:D160とH9:H160)を選択し、
リボンから「ホーム」→「(セル)書式」→「セルの書式設定」、
表示された「セル書式設定」画面の
「保護」タブから「ロック」のチェックを外し「OK]24-ekuseru2

シート保護設定リボンから「校閲」→「シート保護」とし、
表示された「シートの保護」画面で「OK]
26-ekuseru2

これで完成です。上書き保存を忘れずに。
なお記入忘れのデータを追加する場合は最後尾に追加記入して下さい。そして全データ範囲を選択して、日付順に並びかえれば、追加データも年月日順となります。
整理すると
①誤記入のデータは入力域で修正
②なお誤記入データを削除する場合は該当行を削除
③後からデータを追加するときは行挿入はせず、最後尾に追加して年月日で並び替え(年月日順に並び変えることは、マクロを組み「整列ボタン」を作っておけば便利です。日常的にも日付に関係なくデータを入力し、「整列ボタン」で年月日順に並び変えができるので伝票類を日付順に並べることなく入力作業ができ、作業効率があがります)

マクロを登録しましたので、日付関係なく入力して並べ替える操作を動画にしてみました

マクロで並替え
カテゴリー: エクセル, Office(オフィス) タグ: パーマリンク

クラブ会計簿の作成(2)—会計簿の作成— への2件のフィードバック

  1. 西村 晃一 より:

    参考にして経理簿を作成していたのですが、8項目の項目から内訳が表示されないです。
    indirtect()がエラーになります。
    なんでだろう?

    いいね: 1人

  2. 井上哲朗 より:

    実際の西村さんのファイルを見てみないと、どうしてかの具体的な理由は分かりません。すみません。

    いいね

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト / 変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト / 変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト / 変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト / 変更 )

%s と連携中