会社を経営していると、毎日の取引を記録しなければいけません。法律上、決められていることなので、仕方ないのですが、せっかく記録したデータも決算書作成のためだけに使うのはもったいないです。会計記録は、経営上の課題を把握するために使う、経営分析にも役立てることもできます。ここでは、経費分析を例にして、効率的に経営分析を行うためのExcelファイルの作成を考えます。
おおまかな流れ
今回は、このようなファイルの作成を目指します。
手順は次の通りです。
１．その月の経費データのエクスポート
２．データの形式を整理
３．ピボットテーブルで項目ごとの金額を集計
４．SUMIF関数で、金額を分析シートに転記
５．増減額、増減率の式を入力
ただ、３．以降は最初だけで、2回目からはデータの更新をするだけで分析結果まで表示されるようになります。
ファイルを作ってみます。
具体的な作成手順
順番に見ていきましょう。
１．その月の経費データのエクスポート
まず、分析したい経費データを会計ソフトからエクスポートします。
なお、Excelで経費管理をされている方や、Excelから経費データを会計ソフトにインポートしている方は、この手続は不要ですので、「２．データの形式を整理」にお進み下さい。
ご利用になっている会計ソフトによって手順は異なりますが、通常は、勘定科目を指定してエクスポートすることになると思いますが、難しければ取引全体をエクスポートしても構いません。
Excel上で必要分析したいデータだけを取り出せば問題ありませんので、まずは、その月のデータを漏れなくExcelにエクスポートします。
２．データの形式を整理
今回のファイル作成で最も重要なのがこの作業です。
会計ソフトからエクスポートしたデータは、仕訳の形で出力されていると思いますが、
ポイントは、毎月同じレイアウトでデータを整理すること。
フォントやセルの幅などは、どのような形でも構わないのですが、
どの列に、何が入力されるか
については、必ず統一するようにします。
上の画面では、
A列：日付
B列：借方の勘定科目
C列：借方の摘要
D列：消費税
E列：借方の金額
といったことです。
会計ソフトからエクスポートすれば、自然に同じ形になるのであればそれでOKですし、
Excelで入力されている場合は、毎月同じ形式のシートを使って入力していけば問題ありません。
もし、会計データからのエクスポートで使っているレイアウトが一定でなかったり、見やすさなどの観点から、Excelシートのレイアウトを変えているような場合には、
どこかのタイミングでレイアウトを決めて、それ以降は変更しないようにします。
なぜ、データのレイアウトを統一しなければいけないかというと、この後ピボットテーブルで数値を集計していきますが、毎月ピボットテーブルの作業を行わなくても、データの更新だけで、分析シートを完成させられるからです。
毎月の作業を効率的に行うために、データのレイアウトを統一すると考えて下さい。
３．ピボットテーブルで項目ごとの金額を集計
２．で整理されたデータをピボットテーブルで集計します。
先ほど作成した経費のデータが入力されたシートを開いて、「挿入タブ」から「ピボットテーブル」をクリックすると、「ピボットテーブルの作成」画面が開きます。
まず、「テーブルまたは範囲を選択」ですが、これは、データが入力されている列全体を指定します。
たとえば、経費データがこのように入力されていたら、
「A列」から「I列」まで入力されているので、「$A:$I」のように指定します。
ピボットテーブルは、連続してデータが入力されている範囲を、自動で指定してくれるので便利ですが、どの範囲までデータが入力されるかは、月によって異なります。
先月よりも、取引の数が多く、より多くの行にデータが入力されていたら、前の月と同じ範囲を指定してしまうと、集計範囲に漏れが出ることになってしまいます。
そのような集計範囲の漏れを無くすために、データの範囲が広くなった時のことを想定して、列ごと指定しておくのです。
次に「ピボットテーブルを配置する場所を指定して下さい」ですが、これは「新規ワークシート」を選びます。
すると、このように表示されるので、
右側に出てくる「ピボットテーブルのフィールド」から、
「行」に勘定科目が入力されている項目（上のシートでは「借方」）
「値」に金額が入力されている項目（上のシートでは「借方金額」）
をドラッグ＆ドロップ。
このままだと、金額のところが「データの個数」になっているので、「▼」をクリックして「値フィールドの設定」をクリック。
「値フィールドの設定」画面が開くので、「値フィールドの集計」から「合計」を選んで「OK」をクリック。
すると、
このように、各科目の金額が集計されます。
「▼」をクリックするとフィルターが使えるので、
この中から、経費分析したい科目を選択すると、
このように整理できます。
同様に、「貸方」の科目についてもピボットテーブルで集計します。
やり方は「借方」の場合と同じですが、「ピボットテーブルを配置する場所を指定して下さい」にだけ注意して下さい。
「借方」の科目を集計したピボットテーブルと同じシートに並ぶように指定します。
「ピボットテーブルを配置する場所を指定して下さい」のところで、配置したいセルにカーソルを合わせてクリックすればOKです。
すると、「借方」「貸方」の集計結果が同じシートで並ぶことになります。
次は、この合計額を、前月の金額と比較できるように転記します。
４．SUMIF関数で、金額を分析シートに転記
分析シートで次のような表を作ります。
左端から、「勘定科目」「当月の金額」「前月の金額」「増減額」「増減率」、になっています。
まず、勘定科目は、分析したい経費項目を並べます。
全ての項目を並べたい場合は、先ほど作ったピボットテーブルから、項目をコピーしてはりつければOKです。
次に当月の金額です。これは、先ほど集計したピボットテーブルのシートからSUMIF関数を使って転記します。
SUMIF関数は次のような要素でできています。
①データを調査する範囲
②探したいデータ
③データが見つかった時に、どの範囲の数値を合計するか
この3点です。
たとえば、「リース料」を転記することを考えましょう。
「①データを調査する範囲」はピボットテーブルで集計したシートの、勘定科目が並んでいる列です。
この列（A列）を調査して、「リース料」があるかどうかを確かめることになるので、この列を指定します。
ピボットテーブルを作成したシートの名前を「201505」にしているので、「シート名＋列番号」で指定すると、
①‘2015015’!$A:$A
になります。
探すのは「リース料」ですが、これは、B列に入力されている、表の項目に合わせて指定するようにしたいので、
②B5
になります。
「どの範囲の数値を合計するか」は、ピボットテーブルのあるシートで金額が入力されている列（B列）を指定。
ピボットテーブルを作成したシートの名前を「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年6月分を作成する場合は、2015年5月のファイルをコピーして、ファイルの中身を6月分になるように、入力内容を更新します。
シート名を変え、
（「201505」から「201506」へ）
シート内の「月」の表示を5月から6月に変更し、
金額も、前月（5月）の金額を値で貼り付けます。
これで下準備は完了です。
先月（5月）のデータが入っていたシートに、当月のデータを貼り付けて、
ピボットテーブルのシートで、「データ」タブから「すべて更新」をクリックして、「すべて更新（A）」を選ぶと、
6月分のデータに更新されます。
その結果は、分析シートにも反映されますから、
自動的に、当月分の分析シートができあがることになります。
前月分から当月分への繰り越し作業と、データの更新は機械的に行うことができますから、一から分析シートを作る場合と比べると作業はかなりラクになります。
まとめ
ピボットテーブルとSUMIF関数を組み合わせて、経費の分析シートを作る事で、毎月データを更新するだけで、その月の経費分析シートを作る事ができます。
作業の効率化でできた時間は内容の分析に充てて、質の高い分析を行えるようにしましょう。
＜おまけ＞
土鍋を使って雑炊を作ったのですが、すぐに焦げ付いてダメですね。
火加減をもっと慎重にします。