Excel2016で現金出納帳を作ろう

2月も終わり、明日から3月です。となると、そろそろ年度末の忙しさが聞こえてきそうです。が、この時期に増える相談というのが、年度末に向けた総会や報告会で使用する会計書類。

毎年、もっと楽に手間をかけずに…と思いながらなかなか手を付けられないという人も多いのではないでしょうか。

今回は次年度に向けて、現金出納帳をExcel2016で作成する方法をご紹介しておきます。この現金出納帳を作成する流れは、家計簿作成にも応用できます。

スポンサーリンク
レクタングル広告大

現金出納帳の元となるデータを入れながら現金出納帳のフォーマットを作成しよう

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

例として上の図のような出納があったとします。出納帳を作成するには差引残高の部分を自動的に計算できるような仕組み(計算式)を入れる必要がありますね。

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

まず、1つめの取引である差引残高を求めるセル(上の例だとするとG4のセル)を選択しておきます。

次に、計算式を始めるのでを入力します。マウスを使って元になる1つ上の差引残高をクリックして選択します。すると、セルには「=G3」と入力されます。

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

次に、収入があれば差引残高に足してもらいたいので、+を入力し、4行目の収入金額(上の例だとするとE4セル)をクリックします。

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

次に、支出があれば差引残高から引いてもらいたいので、-を入力し、4行目の支払金額(上の例だとするとセル)をクリックし、Enterキーを押して計算式を確定させます。

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

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

Excelでの計算式のコピー
Excelでの計算式のコピー

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

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

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

Excelでは桁区切りは手入力しない。桁区切りスタイルを使おう

Excel2016では桁区切りはスタイルで設定すればいいので、キーボードを使っての入力はしません。ちなみに桁区切りスタイルはどこから設定するのかというと、<ホーム>タブの中にある数値のところに桁区切りスタイルのボタンがあります。

Excelで数値に桁区切りを入れる
Excelで数値に桁区切りを入れる

科目ごとに集計をしたいなら関数を使おう

関数を使えば科目ごとに金額を集計することができます。上の例を使って集計表を作ってみようと思います。

まずは、科目を抜き出します。といっても、今回は科目が少ないのでよく使う科目を例に挙げておきます。

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

では関数を使って科目ごとに集計できるよう、計算式を入れていきましょう。まずは、水道光熱費の横のセル(例だとJ2セル)をクリックした状態にしておきます。

範囲の中に該当するものがあったら合計する関数「SUMIF」

関数を呼び出します。<ホーム>タブの中にあるオートSUMボタン横にある▼ボタンをクリックし、「その他の関数」をクリックします。

Excelでの関数の呼び出し方
Excelでの関数の呼び出し方

関数を選択する画面が開いたら、関数の分類を「数学/三角」に切り替え、関数名の中からSUMIFを見つけ出し、OKボタンをクリックします。

Excel関数のSUMIFの呼び出し方
Excel関数のSUMIFの呼び出し方

SUMIF関数の引数を入力するための画面が開いてきます。

Excel関数SUMIFの設定画面
Excel関数SUMIFの設定画面

まずは集計対象となるセルの範囲をドラッグで指定します。今回の例だと、科目が入っているセルから収入金額、支出金額が入っているセルまでドラッグします。

SUMIFの範囲の設定方法
SUMIFの範囲の設定方法

ドラッグが完了したら、F4キーを押します。これは絶対参照という指定方法です。この絶対参照で対象範囲を指定しておくことで、他の科目の計算は計算式をコピーするだけで済むようになります。

今回の例だとB3:B7となっているどちらにも$マークが付いていなければならないので、もしF4キーを押しても$B$3:$F$7とならない場合は付いていないセルのほうをクリックしてF4キーを押すようにしてください。

次に、検索条件を指定します。2つめのマス目をクリックし、今回の場合は右側に抜き出した費目一覧の1番上にある水道光熱費をクリックして選択します。

SUMIFの検索条件の設定方法
SUMIFの検索条件の設定方法

最後に合計範囲を指定します。これは、もしも検索条件で指定したものが範囲の中にあったら合計するのはどの範囲にある数値かということを指定するところです。今回の例だと支払金額が入っているセル範囲を選択し、先ほどと同じように絶対参照をかけます。

SUMIFの関数の引数の設定方法
SUMIFの関数の引数の設定方法
SUMIF
SUMIF

すべての入力が完了したらOKボタンをクリックします。

あとは科目分だけ計算式をコピーしよう

今回の例だと、水道光熱費はありませんでしたので計算結果は0となっているはずです。では、最後に科目分だけ計算式をコピーしてみましょう。

計算式のコピー
計算式のコピー

どうでしょうか?計算結果が下の図のようになったでしょうか?

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

1ヶ月分の出納帳が完成したら、シートをコピーして使い回すことで取引を入力するだけで残高計算も科目の集計もできます。

パソコン教室の先生
パソコン教室の先生

市内の60歳以上の方を対象としたパソコン教室でインストラクターをしています。授業の中でお話している内容やパソコン相談会でお受けするご相談などについて書いています。

高齢者のためのICT教室については本サイト「高齢者のためのICT教室」についてお読みください。お問い合わせには随時対応させていただいておりますが、時間がかかりますことをあらかじめご了承くださいませ。

お問い合わせ