2003/10/15作成 2006/7/9 htmlファイルUP
Excelを勉強しながら簡単な会計簿を試行錯誤を繰り返しながら作っていこうと言う独習用のテキストです。Excelの機能の勉強だけでは、面白くないし力がつきません。
こんな学び方をしていきます。途中で道草を食ったり、遠回りをしたり、無駄骨を折ったり、一直線ではありませんが、その間にいろいろな機能やExcel利用の考え方を習得できます。
私は情報処理・会計事務の専門家ではありませんし、文章を書くのも素人です。間違いがあるかも知れませんし、記述で判りにくいところや操作の理解できないところがありましたら、教えてください。
紙に書く現金出納簿は普通 日付、項目、入金、出金、残額、備考 という項目があります。これをExcelのワークシートで作ると
A | B | C | D | E | F | |
---|---|---|---|---|---|---|
1 | 日付 | 項目 | 入金 | 出金 | 残額 | 備考 |
2 | 5月8日 | 会費 | 3000 | 3000 | 継続 | |
3 | 5月8日 | 事務費 | 70 | 2930 | 振替手数料 | |
4 |
というようになりますが、残額欄のE列(E2,E3,〜)にどういう数式を入れたらよいか。
手書きの現金出納簿はこういう計算をします。
判りやすいのですが、途中に空白行があったり、どこかの行に間違った数値を入力したりするとそれ以降の数式が表示する数字が誤ったものになります。
C 列の合計からD 列の合計を引き算をします。少し Excel らしくなりました。
C2とD2の中に「$」が入っているのは、セルの絶対参照です。セルE2に数式を入力したら、E3以下はE2の数式をコピーするために、合計を出す範囲の最初のセルを絶対参照で固定します。
上の2例とも4行以下のC 列、D 列に値が入力されてない時も、前の行の数字が表示されます。
C 列と D 列にデータが入力されていない状態(Null)の時には E 列を空白にしたいものです。
オートフィルで下の方向にコピーしたときにデータのない行にも数字が表示されないようにするには、データを入力した行のみ数式をコピーする方法がありますが、少し手数がかかります。少し工夫してオートフィルでコピーできるようにします。
「""」はセルに何も入力されていない状態です。
書式 IF(論理式,真の場合実行する内容,偽の場合実行する内容)
これで実用上問題はないのですが、論理式に少し問題があります。出金欄に入金額と同じ数字でマイナスの値が入力された時にも表示しないことになります。
まだ問題があります。この方法はC列とD列のセルに数値でないもの(例えばスペース)が入力されているときにも、残額に数字が表示されます。
次ぎの項でやりますが入力規則でC列とD列のセルに整数だけ入力するように制限することが出来ます。
C列とD列のセルどれか1つでも数値が入力されているときに、E列に計算した値を入力し、そうでないとき(数値が入力されていないとき)は空白にする数式です。
数値データのないところを空白にするだけですが、3つの関数を使い数式が少し複雑になりました。セルE2に入力してオートフィルで下の方にコピーします。この程度の数式の演算速度は気にするほどでは無いと思います。可能性は低いのですが、例えばC列に「5/24」と日付を入力すると数値として扱い計算して数字が表示されます。やはり入力規則で制限を設ける必要があるようです。
E 列の数式が出来上がったら次のテーマは、タイトルを入力し、 A 列 〜 F 列に入力しやすいように入力規則の設定と、B 列の項目は決められた文字列を入力するようにするにはどうしたらよいか。
B 列の項目は、NET・陽だまり総会の会計報告の収入・支出の項目にしましょう。
表の上部に2行新しい行を挿入し、1行のA〜F列のセルを結合し文字の横配置を中央揃えに書式を設定します。フォントサイズを14ポイント、フォント種類をMSゴシックとして、「現金出納簿」と入力します。
3行目の項目の文字列を中央揃えに設定します。
データ部分の最初の行に「繰越金」の行を作るために、1行挿入し日付(セルA4)に「4/1」(4月1日)、項目(セルB4)に「繰越金」と入力し、入金(C4)に「20000」と入力します。
表の罫線はすぐ引きたくなりますがワークシートが完成する段階で入れる方が良いと思います。
A | B | C | D | E | F | |
---|---|---|---|---|---|---|
1 | 現金出納簿 | |||||
2 | ||||||
3 | 日付 | 項目 | 入金 | 出金 | 残額 | 備考 |
4 | 4月1日 | 繰越金 | 20,000 | |||
5 | 5月8日 | 会費 | 3,000 | 3,000 | 継続 | |
6 | 5月8日 | 事務費 | 70 | 2,930 | 振替手数料 | |
7 |
C列、D列、E列は金額が表示されますから、3桁目にコンマ「,」を表示するようにします。
行が挿入されましたが数式中の絶対セル参照の部分も行の移動に対応して変化しています。
セルE4には数式が入っていません。次のどちらかの数式を入力します。
キーで入力しても良いのですが、セルE5に数式が入っていますのでE5からE4にコピーして絶対セル参照の部分を修正する方が楽です。
A列には「5/8」と入力すれば、自動的に「5月8日」と日付の表示になりますから、表示の設定はする必要がありません。
B列には決められた項目を入力するようにします。
C列,とD列は数値を入力するようにします。
F列は全角文字を入力できるようにします。
この方法はExcelとして最も低いレベルと考えられますが、捨てたものではありません。
文字列を入力する場合、オートコンプリートという機能で、既に同じ列に入力してある文字列が入力の途中で候補として現れてきます。例えば既にセルB5に「会費」という文字列が入力してあるときに、B8に「か」と入力すると「か会費」と(会費が反転された状態で)表示され、このときにEnterキーを押せば「会費」が確定されます。しかし、同じ列に「会費」「会議費」が入力してあると「かい」まで入力しても「会費」は出てこないで、「かいひ」で「会費」、「かいぎ」まで入力したときに「会議費」が現れます。
この機能を有効にするには、メニューの[ツール][オプション]の「編集」タブで、「オートコンプリートを使用する」チェックボックスにチェックを入れておかなければなりません。(列に空白のセルがあるとそのセルより上の文字列は候補として現れません。)
もう一つ、入力規則を設定しなくても、同じ列に既に文字列が入力してある文字列をリストから選択する方法があります。上の例と同じに既にセルB5〜B7に文字列が入力してあるときに、B8で右クリックして出るショートカットメニューで「リストから選択(K)」をクリックすると、今まで入力してある文字列がリストに現れ選択すればセルに入力できます。
それを見ながら入力するか、そこからコピー貼り付けをします
H列に決められた項目を入力しておきます。
B列を選択してメニューの[データ][入力規則]の[設定]タブをクリックし、「入力値の種類」のリストボックスから「リスト」を選択します。
「空白を無視する」「ドロップダウンリストから選択する」にチェックが入っていることを確認して、「元の値」にH列の決められた項目入力してある範囲を設定し「OK」ボタンをクリックする。
これでB列のセルを選択するとリストの矢印が表示されるから、項目を選択して入力できます。
必要により「入力時メッセージ」と「エラーメッセージ」タブで設定をします。
C列とD列を選択してメニューの[データ][入力規則]の[設定]タブをクリックし、「入力値の種類」のリストボックスから「整数」を選択する。「空白を無視する」にチェックが入っていることを確認して、「データ」は「次の値より大きい」を選択、「最小値」は「0」として「OK」ボタンをクリックします。
必要により「入力時メッセージ」と「エラーメッセージ」タブで設定をします。
F列を選択してメニューの[データ][入力規則]の[日本語入力]タブをクリックし、日本語入力のリストボックスから「オン」を選択して、「OK」ボタンをクリックします。
必要により「入力時メッセージ」と「エラーメッセージ」タブで設定をします。
一先ず現金出納簿の形が出来ました。
現金出納簿が一応できましたら次のテーマは、元帳に相当する収入項目の表を作ります。収入ができれば支出も同じように作られます。現金出納簿と同じブックの中に収入内訳のワークシートを作ります。
「一度入力したデータは二度と入力しないようにする」のが間違いの少ない方法で手数もかからないので理想的です。そのためには
などが考えられいずれも一筋縄ではいきません。
ワンライティング方式はいずれ取り組むこととして、もう一度データ入力が必要ですが、会計報告を自動的に作るために、ワークシートを別に作ってみます。
会計報告書が自動的に作られるということは、年度の途中でも随時会計の現況がわかることになります。そのために収入全体の合計と項目ごとの合計が別のシートから参照できる(データの量によりデータの場所が動かない)一定の場所にあることが必要です。
この会計簿は出納のたびに入力するので、下の方に伸びていきます。列の合計を一定のセルに表示させるには、合計を一般の表のように表の下部ではなく上部の一定の場所に表示させる方が位置が固定されます。
項目ごとに予算があるときは予算との比較が自動的に表示できると良いので、現金出納簿の「残額」の数式を参考にして計算することが出来ますが、予算残額は過去の日ごとには必要なく「現在の残額がわかればよい」ということなら上部の合計のところに「予算額」と「予算残額」を設ければよいことになります。
こう考えると現金出納簿の「残額」(「行ごとの残額」)は手作業の出納簿では計算のために必要だったのですが、ExcelではSUM関数で簡単に合計が出せますから、「現在の残額」がわかればよいことになります。残額の数式で悩む必要がなかったことになります。
A | B | C | D | E | F | G | H | I | |
---|---|---|---|---|---|---|---|---|---|
1 | 収入内訳 | ||||||||
2 | 予算額 | 164,000 | 予算額 | 0 | 予算額 | 0 | 予算額 | 164,000 | |
3 | 合計額 | 160,000 | 合計額 | 35,000 | 合計額 | 28 | 合計額 | 195,028 | |
4 | 予算残額 | 4,000 | 予算残額 | -35,000 | 予算残額 | -28 | 予算残額 | -31,028 | |
5 | 日付 | 会費 | 寄付金 | 雑収入 | 合計 | ||||
6 | 入金額 | 備考 | 入金額 | 備考 | 入金額 | 備考 | 入金額 | 備考 | |
7 | 4月1日 | 28 | 預金利子 | 28 | |||||
8 | 4月5日 | 会費 | 3,000 | 3,000 | |||||
8 | 4月8日 | 会費 | 3,000 | 3,000 | |||||
9 | 4月9日 | 会費 | 3,000 | 3,000 |
表の上部の「予算額」は数値を入力します。
「合計額」は例えば セルC3 =SUM(C$7:C50) と数式が入ります。
表が50行より多くなるときは修正しなければなりません。
「予算残額」は セルC4 =C2-C3 と数式が入ります。
7行以下には、現金出納簿に入力したものをもう一度入力することになります。(実用にはここのところで間違いが発生します。)
収入より項目が多いのですが(3)の収入の場合と同様にシートを作ります。
実際に使用する時の使い勝手を試すためと、シートが正しく計算したり表示したりするか試すために模擬のデータを入力してみます。
各項目全部についてデータを入力します。
現金出納簿に入力したら、続いて同じデータについて項目を間違わないように収入・支出内訳書にも入力します。(2度同じデータを入力することは間違う確率を2倍にすることになります。)
収入内訳・支出内訳の項目の合計を参照して、予算・現金残額と比較して現在の収支状況を把握できるようにする。
A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|
1 | 収 入 | 支 出 | ||||||
2 | 項目 | 予算額 | 現在額 | 対予算 | 項目 | 予算額 | 現在額 | 対予算 |
3 | 繰越金 | 事業費 | ||||||
4 | 会費 | 通信費 | ||||||
5 | 寄付金 | 会議費 | ||||||
6 | 雑収入 | 事務費 | ||||||
7 | 消耗品費 | |||||||
8 | 雑費 | |||||||
9 | 合計 | 合計 |
1つのブックの中に現金出納・収入内訳・支出内訳のシートを作り、さらに新しいシート「現在額」のシートを上の表のように作ります。予算額と現在額は収入内訳と支出内訳の対応するセルを参照する数式を入力します。
例えばセルC4には、収入内訳シートの会費の合計額のセルを参照します。
入力手順 セルC4を選択して「=」を入力し、シート収入内訳のタブをクリックして、会費の合計額のセル(例えばC3)を選択してEnterキーを押す。
現在額シートのセルC4には「=収入内訳!C3」と入力されています。
D列とH列の「対予算」には、現在額-予算額の数式を入力します。
現金の現在額と収支の関係・ブック全体の整合性をチェックする仕掛けを作ります。
E11〜H12に次の表を加えます。
A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|
9 | 合計 | (収入合計) | 合計 | (支出合計) | ||||
10 | 現金残額 | (現金残額) | 照合 | |||||
11 | 合計 | (G9+G10) | (C9−G11) |
セルC9には収入の合計、セルG9には支出の合計の数式が入っています。
それぞれのセルに数式を入力します。
実際には現在額のシートのタイトルを表示したり空白行を作ったりすると行番号が変わってきます。
以上で同じデータを2箇所に入力する手間がかかりますが、どこかに間違った入力や入力漏れがあるとH11に不符合の金額が表示されます。【図(6)-2】
今回からデータ入力は現金出納簿だけで、内訳簿と現在額のシートが出来る方法を考えます。
A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|
1 | 現 金 出 納 簿 | |||||||
2 | ||||||||
3 | 合計 | 318,069 | 136,616 | 残額 | 181,453 | |||
4 | ||||||||
5 | 日付 | 項目 | 入金 | 出金 | 備考1 | 備考2 | ||
6 | 2002/4/1 | 繰越金 | 123,041 | 項目 | ||||
7 | 2002/4/1 | 雑収入 | 28 | 利子 | 会費 | |||
8 | 2002/4/4 | 通信費 | 3,840 | 郵便料金 | 第5回総会案内48通 | 寄付金 | ||
9 | 2002/4/5 | 会費 | 15,000 | 継続5 | 振替通知票1 | 雑収入 | ||
10 | 2002/4/5 | 事務費 | 350 | 振替手数料 | 振替通知票1 | 事業費 | ||
11 | 2002/4/8 | 会費 | 35,000 | 新規1継続10 | 振替通知票2 | 通信費 | ||
12 | 2002/4/8 | 事務費 | 770 | 振替手数料 | 振替通知票2 | 会議費 |
今まで作ってきたようにタイトル(現金出納簿)を入力し3行目に合計欄を作ります。
例えば セルC3 =SUM(C6:C100) 100行まで計算する
セルD3 =SUM(D6:D100)
セルF3 =C3-D3
H列に項目を入力するリストを入力しておき、B列「項目」を入力しやすくします。
A列からF列の入力規則を設定します。
これでシートの見出しを「出納簿」とシート名を変更して現金出納簿は出来ました。【図(7)-1】
フィルタで内訳のシートを作る場合は、例えば「会費内訳」「寄付金内訳」と項目ごとに作ります。
現金出納簿と同じシートを項目の数作り、項目ごとのフィルタをかけます。
A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|
1 | (会費) | 内訳書 | ||||||
2 | 予算額 | 入金合計額 | 予算残高 | |||||
3 | 164,000 | 160,000 | 4,000 | |||||
4 | ||||||||
5 | 日付 | 項目 | 入金 | 出金 | 備考1 | 備考2 |
D3の数式 SUBTOTAL関数の書式 SUBTOTAL(集計方法,範囲)
計算方法を指定して集計する関数です。集計方法「9」は「合計」です。他に平均・数値の個数・最大値・標準偏差などがあります。
範囲「C6:C100」はC列の100行までの範囲です。ここでSUM( )関数を使うとフィルタのかからない元のデータの合計が表示されます。
これで予算額をあらかじめ内訳に入力しておけば、現金出納簿のシートだけに入力すれば内訳と現在額まで表示されます。入力しないシートには保護をかけてありますから誤って変更することはありません。
現金出納簿と同じシートを項目の数作っているので、ファイルサイズが大きくなっているのが欠点です。
オートフィルタでは現金出納簿と同じシートを項目の数作りますので、ブックのファイルサイズが大きくなりますが、項目ごとに1つのシートで見ることが出来ます。
ピボットテーブルを使うと、(3)〜(6)で作ったような入金と出金の2つの内訳書に纏めることが出来ます。
今までの現金出納簿の列は入金と出金の金額だけ分けてありましたが、ピボットテーブルを使うときには日付以外を分けておいた方がよいようです。
日付 入金項目 入金額 入金備考 出金項目 出金額 出金備考
「備考」はピボットテーブルでどう扱ってよいか判りません。現金出納簿には書いておき、詳細を知りたいときはそちらを見る事にして、内訳書には載せてないことにする方法があります。
項目の先頭に表示する順に数字をつけておくと、内訳書を作るときに数字の順に並びます。
200行まで使うとして次ページの図の例で
収入合計 セルC3 =SUM(C6:C200) 支出合計 セルE3 =SUM(F6:F200)
残額 セル G3 =C3-E3
A | B | C | D | E | F | G | |
---|---|---|---|---|---|---|---|
1 | 現 金 出 納 簿 | ||||||
2 | |||||||
3 | 収入合計 | 138,069 | 支出合計 | 4,190 | 残額 | 133,879 4 | |
4 | 収 入 | 支 出 | |||||
5 | 日付 | 入金項目 | 入金額 | 入金備考1 | 出金項目 | 出金額 | 出金備考1 |
6 | 4月1日 | 繰越金 | 123,041 | ||||
7 | 4月1日 | 3雑収入 | 28 | 利子 | |||
8 | 4月4日 | 1通信費 | 3,840 | 郵便料金 | |||
9 | 4月5日 | 1会費 | 15,000 | 継続5 | |||
10 | 4月5日 | 3事務費 | 350 | 振替手数料 |
A | B | C | D | E | F | |
---|---|---|---|---|---|---|
1 | 入金内訳 | |||||
2 | 合計額 | 160,000 | 35,000 | 28 | 123,041 | 318,069 |
3 | 合計 : 入金額 | 入金項目 | ||||
4 | 日付 | 1会費 | 2寄付金 | 3雑収入 | 繰越金 | 総計 |
5 | 4月1日 | 28 | 123,041 | 123,069 | ||
6 | 4月5日 | 15,000 | 15,000 | |||
7 | 4月8日 | 35,000 | 35,000 | |||
8 | 4月9日 | 18,000 | 18,000 | |||
9 | 4月10日 | 20,000 | 20,000 | |||
10 | 4月11日 | 12,000 | 5,000 | 17,000 |
普通の会計では現金の出入りだけでなく、預金通帳や別会計の出入りがあります。これをExcelで扱うにはどうしたらよいかという問題があります。
紙の上での手作業の会計簿では、預金通帳のページを作って出入りを記入して、収支の内訳書にも記入することになります。
Excelの場合は、現金出納簿に入力したものを収支内訳にもう一度入力している場合は、預金通帳の出納簿に入力したデータを収支内訳にそれぞれ入力すればよいことになります。(1)〜(6)参照。現金出納簿と預貯金出納簿などの出納簿と、科目ごとに分けた内訳簿に入力することになります。複雑になり入力の間違いを探すのが難しくなります。
(7)オートフィルタや(8)ピボットテーブルのように1回の入力で済ませる方法となると、次に試してみるVBAを使うことになりますが、ここでは今まで作ってきた方法を使って出来ることを考えて見ます。
この方法の問題点は、現在額では2つの勘定を統合できますが、収入内訳・支出内訳では1本になっていない点です。
現金出納簿 ⇒ 収入・支出内訳書(現金) ⇒
預貯金出納簿 ⇒ 収入・支出内訳書(預貯金) ⇒
現在額
決算で会計関係書類をきちんと整備しなければならないときは、一つの項目があちこちのシートに分かれているのは変ですから、一つに纏める必要があります。
その方法は、それぞれの内訳を同じ構造に作っておき、コピー貼り付けで一つの表にしてから日付を基準にして並び替えれば出来ます。
例えば(7)オートフィルタで内訳を作る の現金出納簿の例で、会費は郵便振替で入ってきます。振り込まれると振替手数料が同時に支払われます。現金が動いていないで振替口座の中で出入りがあるのですから、現金出納簿には載せないで、別の振替口座の出納簿に載るのが自然です。振替口座の内訳も別にすると構造を同じにして内訳は次のようになります。
A | B | C | D | E | F | G | H | I | |
---|---|---|---|---|---|---|---|---|---|
1 | 収入内訳 |
5 | 日付 | 会費 | 寄付金 | 雑収入 | 合計 | ||||
---|---|---|---|---|---|---|---|---|---|
6 | 入金額 | 備考 | 入金額 | 備考 | 入金額 | 備考 | 入金額 | 備考 | |
7 | 4月1日 | 28 | 預金利子 | 28 | |||||
8 | 4月11日 | 5,000 | 寄付金 | 5,000 |
A | B | C | D | E | F | G | H | I | |
---|---|---|---|---|---|---|---|---|---|
1 | 収入内訳 |
5 | 日付 | 会費 | 寄付金 | 雑収入 | 合計 | ||||
---|---|---|---|---|---|---|---|---|---|
6 | 入金額 | 備考 | 入金額 | 備考 | 入金額 | 備考 | 入金額 | 備考 | |
7 | 4月5日 | 15,000 | 会費 | 15,000 | |||||
8 | 4月8日 | 35,000 | 会費 | 35,000 | |||||
9 | 4月9日 | 18,000 | 会費 | 18,000 | |||||
10 | 4月8日 | 20,000 | 会費 | 20,000 | |||||
11 | 4月11日 | 12,000 | 会費 | 12,000 | |||||
12 | 4月12日 | 9,000 | 会費 | 9,000 |
これを決算時に一つの内訳書に纏めるには、次のようにコピー貼り付けと並べ替えを行います。
それぞれの内訳に誤りが無いことを確認してからシートの統合をすることになりますが、余りスマートな方法とはいえません。そこで少し敷居が高いのですが、次はマクロを使った方法を考えて見ます。
出納簿と内訳、現在額の3つの構成で現金と預金通帳(振替口座)を統合した会計簿を作るために、それぞれのシートに数字を入力するのは必ず間違いや入力漏れが出ます。入力は入力シートだけに絞り、そこに入力したデータを必要なシートに漏れなく配置することが出来れば効率的です。
このテキストの「(6)現金出納簿・収入内訳・支出内訳をまとめた現在額のシートを作る。」までの4つのシート(基本会計簿という)に振替口座の出納簿を加えたものを作ります。
現金出納簿・収入内訳書・支出内訳書・現在額内訳書・郵便振替口座出納簿の数式の入ったサンプルブックを参考にしてください。sampl.xls 31.0KB 圧縮ファイル sampl.lzh 7.0KB
5つのシートに入力するデータは、「日付、収入項目・支出項目、金額(収入・支出)、備考」などですが、振替口座に関連して「振替通知票番号」と「振替口座 ←→ 現金出納簿」間の金額の移動も入力できるようにしておかないといけません。
入力シートでは現金出納と振替口座を分けて表を作り、1件ごとにデータを入力し終わったらボタンをクリックしてデータを必要なシートへ配置する仕組みを作ります。この仕組みのところをマクロ(VBA)で作ります。
マクロは特定のアプリケーション(この場合はExcel)の中で動くプログラムのようなものですから、敷居が高いです。Excelには「キー記録」という他のOfficeソフトにない機能があって、キー操作を自動的にマクロに記録してしまう機能があります。マクロとはどんなものか試し簡単なマクロを作って見ます。
マクロは使い方によっては大変便利なものです。キーボードで操作できない操作をマクロで実現できますが、コード(Moduleと言います)を直接書かなければなりません。これが大変なんですが、上手く出来ると登山で頂上に到着した気分になれます。
こんなことに興味をもたれる方は、マクロを使って「入力は入力シートだけにして、自動的に会計簿を作る」別の「ゆっくり育てる会計簿(マクロ偏)」に進んでみませんか?