【Excel】現金出納帳を作って、自治会の現金のやり取りを自動計算できるようにしよう

高齢者のためのエクセル教室
jarmoluk / Pixabay
この記事は約6分で読めます。

年度末に近づくと自治会などの会計報告に関する相談が急増します。

特に多い相談が収支報告書に関するもの。収支報告書とは会計年度の収支の流れを科目ごとにまとめて報告するものですが、収支報告をまとめるためには現金出納帳や通帳でのやり取りを科目ごとに仕訳をしなければなりません。

わたしも何度か会計担当になったことがありますが、この仕訳が結構手間ですよね。

Excelを使って、現金出納帳や通帳でのやり取りを入力すれば自動的に仕訳を行った上に集計まで行うことができ、その集計結果を自治会などで決まっているフォーマット(文書の雛形)に転記すれば収支報告書を完成させることもできます。

スポンサーリンク

Excel2016で現金出納帳を作成する方法

下の画像のように、科目と概要、収入金額、支出金額、そして差引残高を入力していきます。

Excelでの現金出納帳の作り方

差引残高の部分に、収入があれば1つ上の行の差引残高に足して、支払いがあれば1つ上の差引残高から引くという計算式を入れて自動的に計算できるようにしていきたいと思います。

差引残高を計算するための計算式を入力する

1行目には前月繰越金(いま、現金で持っている金額)を入力してあります。そのひとつ下、取引が発生した行の差引残高を求めるセルを選択した状態で計算式を入力していきます。

Excelで計算式を始めるときは、キーボードの「=」を入力し、1行上の残高(前月繰越金)を入力したセルをマウスを使って選択します。すると、セルには

=G3(前月繰越金が入っているセル番号)

と入力されます。

Excelでの現金出納帳の差引残高の計算式

次に、収入金額があれば差引残高にプラスしたいので、キーボードの「+」キーを押して今入力している行の収入金額のセルをマウスを使って選択します。

Excelでの現金出納帳の計算式

=G3(1行上の差引残高のセル番号)+E4(取引が発生した行の収入金額のセル番号)

と入力されたのを確認します。

次に、支出取引が発生したらその金額を計算に含めるために、キーボードの「‐」を押して同じ行の支払金額を入力するセルをマウスで選択します。

Excelで現金出納帳の計算式

=G3(1行上の差引残高のセル番号)+E4(取引が発生した行の収入金額のセル番号)‐F4(取引が発生した支払金額のセル番号)

と入力されたのを確認して、キーボードのEnterキーを押して計算式を確定させます。

計算式を下の行にコピーする

これで1つめの現金のやりとりが計算できました。あとは、この計算式を取引のあるたびにコピーしていけば日々の現金のやり取りは記帳できると思います。

Excelでの計算式のコピー

あとは、月末になったら収入金額と支払金額を集計し、次月への繰越金額を求めた時点で現金と相違なければOKです。

セルの中で計算式を一度に表示した図を貼りつけておきます。

Excelでの現金出納帳の計算式を細かく見た図

Excelで作った現金出納帳を科目ごとに集計する方法

現金出納帳の形が完成したら、次は科目ごとに集計します。科目ごとの集計には「関数」を使います。

関数を使う前に科目を別セルに入力します。自治会の会計でよく使う科目は

  • 水道光熱費
  • 通信費
  • 福利厚生費
  • 旅費交通費
  • 接待交際費
  • 消耗品費
  • 雑費

くらいかなと。

下の画面でいうと、I列に上に挙げた科目を入力しておきます。

Excelで科目ごとに自動的に集計の計算をする方法

科目ごとに集計するには関数「SUMIF」を使う

ここからは「関数」の出番です。元の表から科目に該当するものが見つかったらその数値を合計することができる関数「SUMIF」を使います。

関数の中で頻繁に使うものは「ホーム」タブの「オートSUM」ボタン横にある▼をクリックすればすぐに使うことができますが、「SUMIF」は「その他の関数」をクリックして関数を呼び出す必要があります。

Excelでの関数の呼び出し方

「その他の関数」をクリックすると「関数の挿入」画面が開きますので、「SUMIF」を探します。

「SUMIF」は関数の分類の▼をクリック→「数学/三角」をクリックして絞り込んでから、「関数名」の右側にあるスクロールバーを使って探しましょう。見つけたらクリックしてOKボタンをクリックします。

Excel関数のSUMIFの呼び出し方

関数の引数画面が開くと、

  • 範囲
  • 検索条件
  • 合計範囲

という3つの枠がありますので、それぞれの枠に以下のものを設定していきます。

  • 範囲・・・科目と収支金額が含まれるように範囲選択する
  • 検索条件・・・探し出したい科目
  • 合計範囲・・・「範囲」の中で計算したい数値が入力されているところを範囲選択する

では、一番上に入力した科目「水道光熱費」を例に、SUMIFを使って集計する流れをご紹介していきます。

まず、1つめの枠「範囲」ですが、科目と収支金額が含まれるように範囲選択をします。見本だと以下の図のようになります。

範囲選択ができたら、キーボードのF4キーを押します。Excelで計算を行うときにキーボードのF4キーを使うことがありますが、これは「絶対参照」といって、範囲を固定したいときに使用します。

次に、2つ目の枠「検索条件」をクリック→さきほど入力した科目の「水道光熱費」と入力したセルをクリックします。

最後に、3つ目の枠「合計範囲」をクリック→現金出納帳の「支払金額」が入力されているセルを範囲選択してから、キーボードのF4キーを押します。ここも絶対参照です。

最後にOKボタンをクリックすると自動集計ができているはずです(といっても水道光熱費は現金出納帳に入力していないので結果は「0」です)。

あとの科目も上の操作を繰り返してもらえればいいのですが、Excelは計算式のコピーができましたね。水道光熱費をSUMIFで求めたセルをクリック→右下の■にマウスポインタを合わせて下方向へドラッグすれば、科目ごとの集計ができているはずです。

計算式のコピー

Excelで作成した現金出納帳の科目ごとの集計結果

これで、関数「SUMIF」を使った科目ごとの支出が集計できました。

科目ごとの収入を集計するのも同じく「SUMIF」を使って行いますが、下の図のように収入の科目を別枠に入力し、SUMIFの「合計範囲」を収入金額が入力されているセルを選択すればOKです。

スポンサーリンク

Excel2016で現金出納帳を作ろう、のまとめ

Excel2016で現金出納帳を作成する方法をまとめてみました。使ったのは足し算と引き算、関数の「SUMIF」でした。

計算式が苦手だという人は多いのですが、計算式を使う一番の利点は、値を入力すれば自動で計算結果を求めてくれるところです。

もし、値を入力し間違えても値を修正するだけで再計算してくれますので、作成するのは大変ですが、一度作ってしまえば使い回すこともできます。

使い回す方法が分かれば、毎月新たに作り直す必要もありませんね。

というわけで、月ごとに集計することができる現金出納帳に関してはまた別の記事でご紹介できればと思います。

スポンサーリンク
この記事を書いている人
パソコン教室の先生

市内の60歳以上の方を対象としたパソコン教室でインストラクターをしています。授業の中でお話している内容やパソコン相談会でお受けするご相談などについて書いています。
高齢者のためのICT教室については本サイト「高齢者のためのICT教室」についてお読みください。お問い合わせには随時対応させていただいておりますが、時間がかかりますことをあらかじめご了承くださいませ。

パソコン教室の先生をフォローする
高齢者のためのエクセル教室
スポンサーリンク
パソコン教室の先生をフォローする
高齢者のためのICT教室
タイトルとURLをコピーしました