区分欄は収入支出移動の 3 つの何れかを選び集計項目を識別するのに使います特定の言葉しか入力しないのでドロップダウンリストから入力するようにしますセル E7 をクリックしておきデータタブにあるデータの入力規則を開きます右に示すような画面が開かれますから入力値の種類にリス

(1)

1 / 8

自治会会計簿の事例紹介

まえがき

ここで紹介する「自治会会計簿」は、筆者が自治会の会計担当者になったときに作成したものをベースに、シンプルでしかも使い易く改良したものです。金銭出入りのデータを集計するには、いろいろな方法がありますが、この会計簿の特徴は、入力と同時に自動集計されるようにしてあることです。この資料は Excel 2013 で説明しています。Excel 2007 などでも使えますが、メニュー名などは適宜読み替えてください。この「自治会会計簿」を他の自治会でも使えるように、改造するときの注意事項を最後にまとめていますが、改造によって如何なる問題や損害が発生しても、責任を負いかねますので、自己責任で改造してご使用くださるようお願いします。自治会の会計担当者は、予算書に基づく予算管理を行い、年度末には決算書を作成し、予算との対比を報告しなければなりません。そこで、予め集計を行うときの集計項目を適切に決めておき、会計簿に記入するたびにこれらの集計項目で自動集計される仕組みを考えてみましょう。このような仕組みを作れば、予算の執行状況が一目で分かり、決算書も簡単に作ることができるようになるでしょう。また、補助金の申請も会計担当者の重要な仕事です。これには、決められた様式で前年度の決算書と当年度の予算書を提出しなければなりませんが、多くの場合、自治会の会計簿とは会計費目が異なるため、それらの整合をとるのに苦労します。ここに紹介する「自治会会計簿」は、ドロップダウンリストから集計項目を入力でき、記入と同時に補助金申請用の集計表と自治会用の集計表が作られます。また、現金と預金の残高も記入と同時に確認できるようにしてあります。この自治会会計簿には、「会計簿」、「設定」、「集計表」、「補助金申請用集計表」、「自治会用集計表」という５つのシートがあります。

会計簿シートについて

タイトルは番号、月度、月日、区分、集計項目、摘要、伝票、取扱者、現金収入、現金支出、預金収入、預金支出、現金残高、預金残高という 14 個に分かれています。番号欄は、中央寄せに設定してから、1, 2, 3 と入力したのち、ドラッグして 300 までの数字を入れておきます。月日欄は、中央寄せに設定してから、セルのプロパティを「日付」に設定し、4 月 20 日なら 4/20 で入力できるようにしておきます。月度欄も、中央寄せに設定します。月日の入力で自動的に入力されるように、C6 には =IF(D6="", "", MONTH(D6)) という式を、C7 には =IF(D7="", "", MONTH(D7)) と書いておきます。IF 関数の書式は =IF(論理式, 真の場合, 偽の場合) です。最初の論理式とは、「若し～ならば」と読み替えれば分かり易いでしょう。MONTH 関数の書式は =MONTH(シリアル値) です。エクセルでは日付を扱うのに内部的には 1900/1/1 を 1 として、この日からの経過日数（シリアル値）を使って処理しています。次の区分欄と集計項目欄は、集計を行うときの項目を入力する箇所です。最初の行は、前年からの繰越金を記入する箇所なので、E6 には "収入" と入力し、F6 には次の設定シートで定義した繰越金に該当する言葉が入力されるようにしておきます。

(2)

2 / 8 区分欄は、収入、支出、移動の３つの何れかを選び、集計項目を識別するのに使います。特定の言葉しか入力しないので、ドロップダウンリストから入力するようにします。セル E7 をクリックしておき、「データ」タブにある「データの入力規則」を開きます。右に示すような画面が開かれますから、「入力値の種類」に「リスト」を選び、「元の値」に「収入, 支出, 移動」と入力しておきます。これで、セル E7 はドロップダウンリストから、"収入"、"支出"、"移動" の何れかを選ぶことができるようになります。セル E7 も中央寄せにしておきましょう。集計項目欄は、次の設定シートで定義した「収入」、「支出」、「移動」の３つのリストを利用します。セル F7 をクリックしておき、前述と同様にして「データの入力規則」を開きます。右に示すような画面が開かれますから、「入力値の種類」に「リスト」を選び、「元の値」に =INDIRECT(E7) と書いておきます。 INDIRECT 関数は、領域名に変換する関数です。すなわち、セル E7 が "収入" であれば、ドロップダウンリストの内容は「収入」と名前が付けられたリストになります。次の摘要、伝票、取扱者は、会計簿の覚書の項目です。摘要欄には集計項目に関する補足メモを自由に書いてください。伝票欄には、収入伝票と支出伝票の番号を、N-1, N-2, …、S-1, S-2, … などと、収入と支出を区別するための記号を付けて記入します。タイトル名を「領収書」と変更して領収書の番号を書くようにしても構いません。取扱者欄には現金を誰に渡したか、誰から受け取ったかなどを記入しておきます。摘要、伝票、取扱者の 3 つの欄は、集計には関係しないので、タイトル名を変更しても問題はなく、また列を挿入して新しいメモ欄を作っても構いません。実際に金額を記入するのは、現金収入、現金支出、預金収入、預金支出の４つの項目の何れかです。現金収入、預金収入の最初の行は、前年度からの繰越金を記入する箇所なので、セル J6, L6 にはコメントを付けておきます。

(3)

3 / 8 セル J7 には、区分が "支出" の場合には入力されるのを防ぐ必要があります。このために「入力規則」を使います。セル J7 をクリックしておいて、「データの入力規則」を開きます。右に示すような画面が開かれますから、「入力値の種類」に「ユーザ設定」を選びます。「数式」の右にあるをクリックすると、右下図のような細長い画面に切り替わりますから、 =OR(E7="収入", F7="預金引き出し", F7="仮払金戻り") と記入します。また、右にあるをクリックと元の画面に戻ります。すなわち、この「数式」の条件を満たすときのみ入力が許され、区分 E7 が "支出" の時はエラーを表示します。エラーの場合の表示は、「エラーメッセージ」タブを開き、「区分または集計項目で入力が制限されています。」としておきます。上述のような処理をセル K7, L7, M7 にも施しておきます。入力を許容する「数式」には、それぞれ右図のように記入しておきます。預金の「預け入れ」の場合は、「現金支出」と「預金収入」に同じ金額を記入してください。「引き出し」の場合は、「現金収入」と「預金支出」に同じ金額を記入します。「仮払金」を支出したときには、清算時に「仮払金戻り」の収入処理をしてから、実際に必要とした金額を、該当する「集計項目」の支出として処理するようにします。仮払金と実際に必要とした金額の差額を「仮払金戻り」として処理するのは間違いです。残金の計算をしているのが、現金残高と預金残高の２つの欄です。現金残高 N7 の式を考えてみましょう。普通に考えれば =N6+J7-K7 とすればよさそうですが、これだけでは入力のない行も残高が計算されてしまいます。これを防ぐため、=IF(F7="", "", N6+J7-K7) とします。すなわち、セル F7 の入力が済んでいることを条件に表示するようにします。ただし、これだけでは十分ではありません。後で述べるソートや並べ替えを行うと、エラーが発生してしまうからです。このために使っているのが、IS 関数です。IS 関数と呼ばれるものには、ISTEXT、ISNUMBER などがありますが、指定した対象の種類をチェックし、TRUE または FALSE を返します。ISNUMBER 関数を使いセル N7 は、=IF(ISNUMBER(N6), IF(F7="", "", N6+J7-K7), "") とし、一行上に残高があることを確かめて表示するようにしています。同様に、最初の N6 は、=IF(ISTEXT(N5), IF(J6="", "", J6), "") とし、上にタイトルが書いてあることを確かめて表示しています。同様な操作を、預金残高に関するセル O6, O7 についても行っておきます。

(4)

4 / 8 金額に関する現金収入、現金支出、預金収入、預金支出、現金残高と預金残高の６つの欄の 6 行目、7 行目を選んでおいてプロパティ画面を開き、右図のように「通貨」に設定し「記号」を「なし」にしておきましょう。また、月日、現金収入、現金支出、預金収入、預金支出の５つの欄の 6 行目、7 行目には、「データ入力規則」の「日本語入力」タブを開いて、「日本語入力」を「オフ（英語モード）」に設定しておきましょう。これらが済んだら、セル C7 から O7 までの 13 のセルを選んでおき、下方の番号 300 までドラッグして全体を完成させます。

合計について

会計簿シートで、集計に必要となる領域を定義しておきましょう。集計項目欄の書き込み領域 F6:F305 を選んで、名前ボックスに「集計項目」と書き込みます。これでこの領域が「集計項目」と名付けられたことになります。現金収入、現金支出、預金収入、預金支出の書き込み領域 J6:J305, K6:K305, L6:L305, M6:M305 についても、それぞれ「現金収入」、「現金支出」、「預金収入」、「預金支出」と名付けます。会計簿シートで記入できるのは 300 行にしてありますが、行数を増やすには最終行を下方にドラッグしてください。ただし、上で定義した領域の大きさは元のままですから、「数式」タブにある「名前の管理」を開いて、「集計項目」、「現金収入」、「現金支出」、「預金収入」、「預金支出」の領域を修正する必要があります。右に「名前の管理」を開いた画面を示します。「集計項目」の欄を見ると、=会計簿!$F$6:$F$305 と定義されています。セルを F6

(5)

5 / 8 と参照する方法を「相対参照」、ドル記号を付けて $F$6 と参照する方法を「絶対参照」と呼びます。「相対参照」ではセルをコピーすれば参照先が変化しますが、「絶対参照」ではコピーしても参照先に変化はありません。この画面から分かるように、名前を付けられた領域は絶対参照で指定されているわけです。会計簿シートの行数を増やした場合は、図の赤丸で囲んだ部分を変更します。会計簿シートに「合計」欄を作っておきましょう。セル J3, K3, L3, M3 にそれぞれ現金収入、現金支出、預金収入、預金支出の合計を求める式を書きます。合計を求めるには、SUM 関数を使います。セル J3 に =SUM() と書きカーソルを () の中において、J6 をクリックし J305 までドラッグします。セル J3 は =SUM(J6:J305) となりますが、直ぐ =SUM(現金収入) と書き換わります。すなわち、領域名で合計が求められます。同様にして、K3, L3, M3 に =SUM(現金支出)、=SUM(預金収入)、=SUM(預金支出) という式を入れておきます。セル N3, O3 には残高を求める式 =J3-K3, =L3-M3 を入れておきます。

設定シートについて

自治会の決算報告に使う会計費目と、補助金申請に使う会計費目とは、通常異なっています。ここに示したのはあくまでサンプルですが、補助金申請用集計表シートと自治会集計表シートで使われている会計費目は大分異なっています。例えば、補助金申請での「防犯灯維持管理費」という項目は、この例の自治会ではこれまで「電灯料」と「修繕費」に分けて会計処理を行ってきたという経緯があります。また、補助金申請では会館借上料、会館光熱水費、会館修繕費などを事務費に計上していますが、この自治会では一括して別項目として計上しています。これらの違いを吸収するために、設定シートで集計項目を決めています。設定シートの左側には、補助金申請用の会計費目を並べています。これと対比する形で、真ん中の「集計項目」の列に自治会の会計費目を割り付けてみます。補助金申請の「防犯灯維持管理費」は、2 行に分けられ、「防犯灯・電灯料」と「防犯灯・修繕費」としています。このようにして、左側の各会計費目に対応する言葉を真ん中の「集計項目」の列に入力し、すべて埋まるようにしてください。色の付いた領域を、「収入」、「支出」、「移動」の３つのリストとして定義しています。会計簿シートで集計項目を入力するときは、区分で選んだ内容に従ってドロップダウンリストの内容が、「収入」、「支出」、「移動」の何れかに切り替わります。「移動」のリストに使っている言葉は、会計簿シートで金額欄の入力制限に使っているため、変更しないでください。

集計表シートについて

集計表シートでは、会計簿シートでの集計項目ごとの現金収入、現金支出、預金収入、預金支出の集計を計算します。先ず、これらの項目に収入計、支出計を加えて７列の表を作ります。集計項目欄の最初のセル B6 には、= と書いておき、設定シートを開いてセル F5 をクリックして Enter を押します。集計表シートのセル B6 には =設定!F5 と入り、「会費」と表示されます。このセル B6 をクリックして下方にドラッグすれば、他の項目名も自動的に入ります。このようにして、項目名の入った表を完成させます。

集計の計算に使うのは SUMIF 関数です。SUMIF 関数の書式は =SUMIF(範囲, 検索条件, 合計範囲) です。最初の「範囲」の中から、2 番目の「検索条件」に一致するものを探し出し、3 番目の「合計範囲」にある夫々の数値を合計した値を返します。

この関数を使って、会計簿シートの集計項目欄の中から、例えば "会費" と書いてあるものだけを探し出し、現金収入欄にある数値を合計するには、次のようにします。

(6)

6 / 8 範囲：集計項目欄にあるデータ範囲（「集計項目」と名付けられた領域）検索条件： "会費" 合計範囲：現金収入欄にあるデータ範囲（「現金収入」と名付けられた領域）すなわち、=SUMIF(集計項目, "会費", 現金収入) という式になります。従って、セル C6 には =SUMIF(集計項目, B6, 現金収入) という式を入れればよいことになります。このセル C6 をクリックして下方にドラッグすれば、現金収入の列が完成します。同様にして、現金支出、預金収入、預金支出、収入計、支出計の表を完成させます。最後に、現預金移動の表を作り、収入合計、支出合計を加えて、現在の残高を計算しておきます。ここに示す残高は、会計簿シート上部の残高と同じ値でなければなりません。時折チェックに使ってください。

補助金申請用集計表、自治会用集計表シートについて

補助金申請用集計表、自治会用集計表シートには、集計表シートで計算した収入合計および支出合計の数字を写します。補助金申請用集計表、自治会用集計表シートのそれぞれには、タイトルとして項目、予算、現在、該当集計項目の４つを作り、収入の部と支出の部の項目名を決められた様式によって書き込みます。該当集計項目というのは、集計表シートで使った集計項目です。現在欄に集計表シートの計算結果を写します。補助金申請用集計表シートの会費のセル F5 には、このセルに = を入れておいて、集計表シートを開き、収入計欄の会費のセル G6 をクリックし Enter を押します。すると、補助金申請用集計表シートの会費のセル F5 には、=集計表!G6 という式が入ります。集計表シートの２つの行をまとめるには、+ 記号で結びます。このようにしてそれぞれの表を完成させます。予算欄は、この会計簿を使い始めるときに書き込んでください。

シートの保護について

セルのプロパティを開き、「保護」タブをクリックすると、「ロック」と「表示しない」の２つの設定項目があります。通常は、「ロック」にチェックが入っています。「校閲」タブにある「シートの保護」をクリックすると、小さな条件設定の画面が開かれますが、そのまま「OK」ボタンを押すと、「ロック」にチェックの入っているセルはロックされ、書き込みが禁止されます。会計簿シートの薄いピンクの色が付いた箇所は、入力を行う箇所ですから、セルの「ロック」を外しておきます。通常は誤操作防止のため、シートを保護した状態で使ってください。「シート保護の解除」をクリックすると、ロックされていたセルの編集が可能になります。なお、セルを「表示しない」に設定しておくと、「シートの保護」にした場合、数式は非表示になるようになっています

フィルター機能を活用しよう

この機能を使うには、最初にシートの保護を解除しておきます。会計簿シートのタイトル行にある任意のセルをクリックしておき、「ホーム」タブにある「並べ替えとフィルター」から、または「データ」タブから「フィルター」をクリックすると、全てのタイトル項目にドロップダウンリストを開くボタンが付き、このボタンをクリックしてリストの中で指定したデータを抽出することができます。例えば、集計項目のドロップダウンリストを開くと、（すべて選択）にチェックが入っていますが、これを外し「会費」のみにチェックを入れて「OK」を押せば、会費のデータのみが抽出されます。また、月度のドロップダウンリストを開き、例えば 6 月のデータのみを抽出することができます。これをコピーして新しいシートに張り付け、少し加工すれば 6 月度の収支明細表などとして使えます。同様にして、4 月～9 月を指定してデータを抽出し、上期収支明細表なども作れます。

(7)

7 / 8 抽出された状態を元に戻すには、再度「フィルター」メニューをクリックします。

並べ替えを行うときの注意

預金の出入りは、銀行に出かけた日に記入するのが普通でしょうから、会計簿シートの月日は日付順になっているとは限りません。このため日付順に並べ替えてみたくなります。また、区分や集計項目で並べ替えてみたいこともあるでしょう。並べ替えを行うには、最初にシートの保護を解除しておきます。並べ替えを行いたい列のどこかのセルをクリックしておいて、「ホーム」タブにある「並べ替えとフィルター」から、または「データ」タブから「昇順」または「降順」をクリックすると、その列の並べ替えを行うことができます。並べ替えをおこなうと、「前年度繰越金」が１行目にあるとは限らなくなり、現金残高、預金残高が正しく計算されず、意味を持たなくなります。このため、ISNUMBER 関数を使い、１行上の行が数字かどうかを調べています。これにより、「前年度繰越金」が最初の行から別の行に移ったときは、残高の表示が消えるようになっています。並べ替えを行ったら、元に戻す操作を忘れないでください。番号の列を昇順に並べ替えれば元に戻ります。

別シートにコピーするときの注意

集計表シートなどをコピーして別シートに貼り付けるとき、そのシートの中に書かれている数式をコピーしたいのか、それとも表示されている数値をコピーしたいのかによって、張り付け方が異なることに注意してください。コピー操作をした後で別シートを開き、張り付けたいセルを指定して、「ホーム」タブにある「貼り付け」の下の ▼ をクリックすると、「貼り付け」、「値の貼り付け」などに分類されたいろいろなオプションのボタンが表示され、それらの上にマウスを移すと、どのように張り付けられるかを表示してくれます。数式も含めてそっくりコピーするには、「貼り付け」→「元の列幅を保持」を使えばいいでしょう。数式ではなく表示されている数値をコピーするには、「値の貼り付け」→「値と数値の書式」を選ぶといいでしょう。

この自治会会計簿を改造するときの注意

1. 設定シートには、「補助金申請用会計費目」に対応する形で「集計項目」欄が設けてあります。この集計項目に自治会の会計費目を割り付けてみて、集計項目の変更、削除、追加などを行ってください。 2. 設定シートの集計項目名を変更すれば、会計簿シートの集計項目のドロップダウンリストが変わります。また、集計表シートの集計項目名が自動的に変更されます。会計簿シートの記入済みデータは自動的には書き換わりませんから、使い始めてから集計項目名を変更するときは、変更に関係する集計項目は入力し直すなどの処理が必要になります。 3. 不要な集計項目を削除するには、先ず集計表シートを見て、その項目に対して集計値が 0 であることを確認してください。未使用の項目であることを確認したら、設定シートで該当する行を削除します。集計表シートを見ると、#REF! というエラー表示が出ていますから、その行を削除します。 4. 新しい集計項目を追加するには、設定シートで追加したい場所に新しい行を挿入して項目名を記入します。次に集計表シートで該当する場所に新しい行を挿入し、数式の書いてある上の行のすべ

(8)

8 / 8 てのセルを下方にドラッグしてコピーします。上下の行のセルで使われている数式を調べ、式の中で使われている行番号が連続していることを確認してください。 5. 補助金申請用集計表シートは、設定シートで集計項目の削除や追加を行った場合、該当項目の「現在」欄のセルに書いてある数式を修正する必要があります。 6. 自治会用集計表シートは、ここに示した例を参考にして、自分の自治会の様式で全面的に書き直してください。以上 2016/01/20