Excelファイルを使った経費分析シートの作成 毎月のデータを更新するだけで効率よく分析ができるシートを目指します

シェアする

  • このエントリーをはてなブックマークに追加

会社を経営していると、毎日の取引を記録しなければいけません。法律上、決められていることなので、仕方ないのですが、せっかく記録したデータも決算書作成のためだけに使うのはもったいないです。会計記録は、経営上の課題を把握するために使う、経営分析にも役立てることもできます。ここでは、経費分析を例にして、効率的に経営分析を行うためのExcelファイルの作成を考えます。

スポンサーリンク
自動サイズレスボンシブ

おおまかな流れ

今回は、このようなファイルの作成を目指します。

スクリーンショット 2015 03 13 10 21 16


手順は次の通りです。


1.その月の経費データのエクスポート

2.データの形式を整理

3.ピボットテーブルで項目ごとの金額を集計 


4.SUMIF関数で、金額を分析シートに転記


5.増減額、増減率の式を入力


ただ、3.以降は最初だけで、2回目からはデータの更新をするだけで分析結果まで表示されるようになります。


ファイルを作ってみます。

具体的な作成手順

順番に見ていきましょう。

1.その月の経費データのエクスポート

まず、分析したい経費データを会計ソフトからエクスポートします。


なお、Excelで経費管理をされている方や、Excelから経費データを会計ソフトにインポートしている方は、この手続は不要ですので、「2.データの形式を整理」にお進み下さい。


ご利用になっている会計ソフトによって手順は異なりますが、通常は、勘定科目を指定してエクスポートすることになると思いますが、難しければ取引全体をエクスポートしても構いません。


Excel上で必要分析したいデータだけを取り出せば問題ありませんので、まずは、その月のデータを漏れなくExcelにエクスポートします。

2.データの形式を整理

今回のファイル作成で最も重要なのがこの作業です。


会計ソフトからエクスポートしたデータは、仕訳の形で出力されていると思いますが、

スクリーンショット 2015 03 13 10 53 45


ポイントは、毎月同じレイアウトでデータを整理すること。


フォントやセルの幅などは、どのような形でも構わないのですが、


どの列に、何が入力されるか


については、必ず統一するようにします。


上の画面では、
A列:日付
B列:借方の勘定科目
C列:借方の摘要
D列:消費税
E列:借方の金額


といったことです。


会計ソフトからエクスポートすれば、自然に同じ形になるのであればそれでOKですし、


Excelで入力されている場合は、毎月同じ形式のシートを使って入力していけば問題ありません。


もし、会計データからのエクスポートで使っているレイアウトが一定でなかったり、見やすさなどの観点から、Excelシートのレイアウトを変えているような場合には、


どこかのタイミングでレイアウトを決めて、それ以降は変更しないようにします。


なぜ、データのレイアウトを統一しなければいけないかというと、この後ピボットテーブルで数値を集計していきますが、毎月ピボットテーブルの作業を行わなくても、データの更新だけで、分析シートを完成させられるからです。


毎月の作業を効率的に行うために、データのレイアウトを統一すると考えて下さい。

3.ピボットテーブルで項目ごとの金額を集計

2.で整理されたデータをピボットテーブルで集計します。


先ほど作成した経費のデータが入力されたシートを開いて、「挿入タブ」から「ピボットテーブル」をクリックすると、「ピボットテーブルの作成」画面が開きます。

スクリーンショット 2015 03 13 11 07 06


まず、「テーブルまたは範囲を選択」ですが、これは、データが入力されている列全体を指定します。

スクリーンショット 2015 03 13 11 09 38


たとえば、経費データがこのように入力されていたら、

スクリーンショット 2015 03 13 11 11 08


「A列」から「I列」まで入力されているので、「$A:$I」のように指定します。


ピボットテーブルは、連続してデータが入力されている範囲を、自動で指定してくれるので便利ですが、どの範囲までデータが入力されるかは、月によって異なります。


先月よりも、取引の数が多く、より多くの行にデータが入力されていたら、前の月と同じ範囲を指定してしまうと、集計範囲に漏れが出ることになってしまいます。


そのような集計範囲の漏れを無くすために、データの範囲が広くなった時のことを想定して、列ごと指定しておくのです。


次に「ピボットテーブルを配置する場所を指定して下さい」ですが、これは「新規ワークシート」を選びます。


すると、このように表示されるので、

スクリーンショット 2015 03 13 11 26 21


右側に出てくる「ピボットテーブルのフィールド」から、

スクリーンショット 2015 03 13 11 26 27

「行」に勘定科目が入力されている項目(上のシートでは「借方」)


「値」に金額が入力されている項目(上のシートでは「借方金額」)

をドラッグ&ドロップ。

スクリーンショット 2015 03 13 11 26 47


このままだと、金額のところが「データの個数」になっているので、「▼」をクリックして「値フィールドの設定」をクリック。

スクリーンショット 2015 03 13 11 30 30


「値フィールドの設定」画面が開くので、「値フィールドの集計」から「合計」を選んで「OK」をクリック。

スクリーンショット 2015 03 13 11 27 07


すると、

スクリーンショット 2015 03 13 11 34 37


このように、各科目の金額が集計されます。


「▼」をクリックするとフィルターが使えるので、

スクリーンショット 2015 03 13 11 37 08


この中から、経費分析したい科目を選択すると、

スクリーンショット 2015 03 13 11 39 15


このように整理できます。


同様に、「貸方」の科目についてもピボットテーブルで集計します。


やり方は「借方」の場合と同じですが、「ピボットテーブルを配置する場所を指定して下さい」にだけ注意して下さい。


「借方」の科目を集計したピボットテーブルと同じシートに並ぶように指定します。

スクリーンショット 2015 03 13 11 42 37


「ピボットテーブルを配置する場所を指定して下さい」のところで、配置したいセルにカーソルを合わせてクリックすればOKです。


すると、「借方」「貸方」の集計結果が同じシートで並ぶことになります。

スクリーンショット 2015 03 13 11 47 20


次は、この合計額を、前月の金額と比較できるように転記します。

4.SUMIF関数で、金額を分析シートに転記

分析シートで次のような表を作ります。

スクリーンショット 2015 03 13 11 52 59


左端から、「勘定科目」「当月の金額」「前月の金額」「増減額」「増減率」、になっています。


まず、勘定科目は、分析したい経費項目を並べます。 
全ての項目を並べたい場合は、先ほど作ったピボットテーブルから、項目をコピーしてはりつければOKです。

スクリーンショット 2015 03 13 11 54 42


次に当月の金額です。これは、先ほど集計したピボットテーブルのシートからSUMIF関数を使って転記します。


SUMIF関数は次のような要素でできています。


①データを調査する範囲

②探したいデータ

③データが見つかった時に、どの範囲の数値を合計するか


この3点です。


たとえば、「リース料」を転記することを考えましょう。


「①データを調査する範囲」はピボットテーブルで集計したシートの、勘定科目が並んでいる列です。

スクリーンショット 2015 03 13 12 00 12


この列(A列)を調査して、「リース料」があるかどうかを確かめることになるので、この列を指定します。


ピボットテーブルを作成したシートの名前を「201505」にしているので、「シート名+列番号」で指定すると、


‘2015015’!$A:$A


になります。


探すのは「リース料」ですが、これは、B列に入力されている、表の項目に合わせて指定するようにしたいので、

スクリーンショット 2015 03 13 12 05 32


B5


になります。


「どの範囲の数値を合計するか」は、ピボットテーブルのあるシートで金額が入力されている列(B列)を指定。

スクリーンショット 2015 03 13 12 09 26


ピボットテーブルを作成したシートの名前を「201505」にしているので、「シート名+列番号」で指定すると、

‘2015015’!$B:$B


となります。

まとめると、


=SUMIF(①’201505′!$A:$A, ②B5, ③’201505′!$B:$B)


このようになります。


ただし、これは借方を合計しただけです。


場合によっては、修正仕訳が入るなどの事情によって、貸方にも経費項目が入力されていることがあります。


そのため、貸方についても同様に転記して、借方の金額からマイナスする必要があります。


SUMIF関数の作り方については全く同じなので、


=SUMIF(①’201505′!$A:$A, ②B5, ③’201505′!$B:$B)
nbsp;
-SUMIF(①’2015015′!$D:$D, ②B5, ③’2015015′!$E:$E)
(”ー”以下が貸方)


として転記します。


これは、前月分も同様なので、同じようにして前月分も合わせて集計すると、

スクリーンショット 2015 03 13 12 18 16


このようになります。


あとは、増減額と増減率の式を入力するだけです。

5.増減額、増減率の式を入力

最後に、増減額、増減率の式を入力します。

スクリーンショット 2015 03 13 12 20 48


増減額は、引き算


増減率は、前月の金額をベースにして考えるので、増減額を分子、前月の額を分母、にした割り算。

スクリーンショット 2015 03 13 12 22 07


表示をパーセンテージにすると、

スクリーンショット 2015 03 13 12 23 30


こうなります。


あとは、この式の入ったセルをコピーして、数式を貼り付け。

スクリーンショット 2015 03 13 12 25 08


すると、

スクリーンショット 2015 03 13 12 27 11


経費の分析表が完成しました。

経費の分析表のキモは、データを更新するだけで今月分のファイルが完成すること

ようやく分析表が完成しました。


ただし、これだけ時間がかかるのは最初だけ。 
翌月以降は、データを更新するだけで、自動的に分析シートができあがります。


具体的には、2015年6月分を作成する場合は、2015年5月のファイルをコピーして、ファイルの中身を6月分になるように、入力内容を更新します。


シート名を変え、

スクリーンショット 2015 03 13 12 34 17
(「201505」から「201506」へ)


シート内の「月」の表示を5月から6月に変更し、 
金額も、前月(5月)の金額を値で貼り付けます。

スクリーンショット 2015 03 13 12 35 14


これで下準備は完了です。


先月(5月)のデータが入っていたシートに、当月のデータを貼り付けて、

スクリーンショット 2015 03 13 12 42 23


ピボットテーブルのシートで、「データ」タブから「すべて更新」をクリックして、「すべて更新(A)」を選ぶと、

スクリーンショット 2015 03 13 12 47 56


6月分のデータに更新されます。


その結果は、分析シートにも反映されますから、

スクリーンショット 2015 03 13 12 49 03


自動的に、当月分の分析シートができあがることになります。


前月分から当月分への繰り越し作業と、データの更新は機械的に行うことができますから、一から分析シートを作る場合と比べると作業はかなりラクになります。

まとめ

ピボットテーブルとSUMIF関数を組み合わせて、経費の分析シートを作る事で、毎月データを更新するだけで、その月の経費分析シートを作る事ができます。 
作業の効率化でできた時間は内容の分析に充てて、質の高い分析を行えるようにしましょう。

<おまけ>
土鍋を使って雑炊を作ったのですが、すぐに焦げ付いてダメですね。 
火加減をもっと慎重にします。

スポンサーリンク
自動サイズレスボンシブ
自動サイズレスボンシブ

シェアする

  • このエントリーをはてなブックマークに追加

フォローする