年度末に近づくと自治会などの会計報告に関する相談が急増します。
特に多い相談が収支報告書に関するもの。収支報告書とは会計年度の収支の流れを科目ごとにまとめて報告するものですが、収支報告をまとめるためには現金出納帳や通帳でのやり取りを科目ごとに仕訳をしなければなりません。
わたしも何度か会計担当になったことがありますが、この仕訳が結構手間ですよね。
Excelを使って、現金出納帳や通帳でのやり取りを入力すれば自動的に仕訳を行った上に集計まで行うことができ、その集計結果を自治会などで決まっているフォーマット（文書の雛形）に転記すれば収支報告書を完成させることもできます。
Excelで現金出納帳を作成する方法
自動計算できる式を入力するため、データをいくつか入力しておく
下の画像のように、科目と概要、収入金額、支出金額、そして差引残高を入力していきます。
差引残高の部分に、収入があれば１つ上の行の差引残高に足して、支払いがあれば1つ上の差引残高から引くという計算式を入れて自動的に計算できるようにしていきたいと思います。
差引残高を計算するための計算式を入力する
1行目に繰越金を入力してあります。
そのひとつ下、取引が発生した行の差引残高を求めるセルを選択した状態で計算式を入力していきます。
初めにキーボードで「＝」（Shiftキーを押しながらひらがなの「ほ」）を入力します。次に、差引残高の列にある繰越金が入っているセルをクリックすると、＝G3（前月繰越金が入っているセル番号）と入力されます。
収入があれば差引残高に足せるようにする
次に、収入金額があれば差引残高にプラスしたいので、キーボードの「＋」キーを押して今入力している行の収入金額のセルをマウスを使って選択します。
と入力されたのを確認します。
支出があれば差引残高から引きたい
次に、支出取引が発生したらその金額を計算に含めるために、キーボードの「‐」を押して同じ行の支払金額を入力するセルをマウスで選択します。
と入力されたのを確認して、キーボードのEnterキーを押して計算式を確定させます。
計算式を下の行にコピーする
これで１つめの現金のやりとりが計算できました。あとは、この計算式を取引のあるたびにコピーしていけば日々の現金のやり取りは記帳できると思います。
あとは、月末になったら収入金額と支払金額を集計し、次月への繰越金額を求めた時点で現金と相違なければOKです。
セルの中で計算式を一度に表示した図を貼りつけておきます。
Excelで作った現金出納帳を科目ごとに集計する方法
現金出納帳の形が完成したら、次は科目ごとに集計します。科目ごとの集計には「関数」を使います。
関数を使う前に科目を別セルに入力します。自治会の会計でよく使う科目は
- 水道光熱費
- 通信費
- 福利厚生費
- 旅費交通費
- 接待交際費
- 消耗品費
- 雑費
くらいかなと。
下の画面でいうと、I列に上に挙げた科目を入力しておきます。
科目ごとに集計するには関数「SUMIF」を使う
ここからは「関数」の出番です。元の表から科目に該当するものが見つかったらその数値を合計することができる関数「SUMIF」を使います。
関数の中で頻繁に使うものは「ホーム」タブの「オートSUM」ボタン横にある▼をクリックすればすぐに使うことができますが、「SUMIF」は「その他の関数」をクリックして関数を呼び出す必要があります。
「その他の関数」をクリックすると「関数の挿入」画面が開きますので、「SUMIF」を探します。
「SUMIF」は関数の分類の▼をクリック→「数学/三角」をクリックして絞り込んでから、「関数名」の右側にあるスクロールバーを使って探しましょう。見つけたらクリックしてOKボタンをクリックします。
関数の引数画面が開くと、
- 範囲
- 検索条件
- 合計範囲
という3つの枠がありますので、それぞれの枠に以下のものを設定していきます。
- 範囲・・・科目と収支金額が含まれるように範囲選択する
- 検索条件・・・探し出したい科目
- 合計範囲・・・「範囲」の中で計算したい数値が入力されているところを範囲選択する
では、一番上に入力した科目「水道光熱費」を例に、SUMIFを使って集計する流れをご紹介していきます。
まず、１つめの枠「範囲」ですが、科目と収支金額が含まれるように範囲選択をします。見本だと以下の図のようになります。
範囲選択ができたら、キーボードのＦ4キーを押します。Excelで計算を行うときにキーボードのF4キーを使うことがありますが、これは「絶対参照」といって、範囲を固定したいときに使用します。
次に、2つ目の枠「検索条件」をクリック→さきほど入力した科目の「水道光熱費」と入力したセルをクリックします。
最後に、3つ目の枠「合計範囲」をクリック→現金出納帳の「支払金額」が入力されているセルを範囲選択してから、キーボードのF4キーを押します。ここも絶対参照です。
最後にOKボタンをクリックすると自動集計ができているはずです（といっても水道光熱費は現金出納帳に入力していないので結果は「0」です）。
あとの科目も上の操作を繰り返してもらえればいいのですが、Excelは計算式のコピーができましたね。水道光熱費をSUMIFで求めたセルをクリック→右下の■にマウスポインタを合わせて下方向へドラッグすれば、科目ごとの集計ができているはずです。
これで、関数「SUMIF」を使った科目ごとの支出が集計できました。
科目ごとの収入を集計するのも同じく「SUMIF」を使って行いますが、下の図のように収入の科目を別枠に入力し、SUMIFの「合計範囲」を収入金額が入力されているセルを選択すればOKです。
Excelで現金出納帳を作ろう、のまとめ
Excel2016で現金出納帳を作成する方法をまとめてみました。使ったのは足し算と引き算、関数の「SUMIF」でした。
計算式が苦手だという人は多いのですが、計算式を使う一番の利点は、値を入力すれば自動で計算結果を求めてくれるところです。
もし、値を入力し間違えても値を修正するだけで再計算してくれますので、作成するのは大変ですが、一度作ってしまえば使い回すこともできます。
使い回す方法が分かれば、毎月新たに作り直す必要もありませんね。
というわけで、月ごとに集計することができる現金出納帳に関してはまた別の記事でご紹介できればと思います。
今回は、計算式や関数を使って集計する方法をご紹介しましたが、計算式や関数を使わずに「テーブル」という機能を使って集計する方法もあります。
