スプレッドシートでPLを作る方法 - 実績集計編
スプレッドシートでPLを作る方法 - 予算作成編でPLの予算シートを作成したので、今回は実績値を集計するシートの作成方法についてお伝えします。
今回作る実績PLを先にお見せします。
※手入力の数字は紫色、同一シート内の計算式が入っている数字は黒色、別シートを参照している数字は茶色で表示しています
上記のPLの作成手順をステップバイステップでお伝えしていきます。
1.元データを貼り付けるシートを作成する
データを集計する前に、元データを貼り付けるシートを作成しましょう。
シートの項目名はデータの中身によって異なりますが、フォーマットは基本的に同じです。
ポイントは3つです。
- 元データのURLを記載する
- 更新日を記載する
- 貼り付けする箇所と計算式が入っている箇所を色分けする
- 元データのURLを記載する
データの入力・更新をする際に、どこからデータを取ってきているかを分かるようにしておきましょう。そうすることで、自分以外の人も元データのありかが把握でき、更新作業を別の人にお願いする際もスムーズです。
- 更新日を記載する
データは一定期間（毎日、週ごと、月ごと）に更新していきますが、いつ更新したかをすぐ把握できるようにしておくと良いです。
- 貼り付けする箇所と計算式が入っている箇所を色分けする
貼り付けする箇所（つまりベタ打ちの数字）と、計算式の箇所を色分けしておくと、どこが貼り付け箇所なのかが一目瞭然になり便利です。
2.実績データを集計する
先ほど作成した元データ貼り付け用のシートが完成したら、実績データを集計してみます。
ポイントは2つです。
- 年（Year）、月（Month）を参照できるようにする
- SUMIFS関数で集計する
- 年（Year）、月（Month）を参照できるようにする
集計する期間を決めるために、何年何月かを指定できるようにします。そのため、シートのどこかの対象年（例えば2023）と、対象月（1月なら1）という数字を引っ張ってこれるようにしておきます。対象年はどこか見やすいところに入力しておくと良いです。対象月は「1月」「2月」のすぐ上に記載すると分かりやすいです。
- SUMIFS関数で集計する
では実際の計算式を見てみましょう。
2023年1月のユーザー数の計算式はこちら。
=sumifs('UU,CV,CVR'!$E:$E,'UU,CV,CVR'!$A:$A,$H$3,'UU,CV,CVR'!$B:$B,I$3)
元データのシート「UU,CV,CVR」のE列をSUM（足し合わせる）、条件はシート「UU,CV,CVR」のA列（つまりYear）がH3（つまり2023）、シート「UU,CV,CVR」のB列（つまりMonth）がI3（つまり1）、という意味になります。これで、2023年1月のユーザー数を集計できました。
基本的にはこのように集計すれば良いのですが、元データの項目の粒度と、集計したい項目の粒度が異なる場合はどうすればよいでしょうか。このような場合は、項目名の対応表を用意すると解決するかもしれません。
例えば販管費の項目で、元データの項目名が細かいので少し集約した粒度にしたい場合を考えてみます。その際は、下記のような対応表を作成してみましょう。
そして、元データ貼り付け用のシートに1列追加し、集計で使いたい項目名を引っ張ってきます。
「項目名-集計用」の項目名は、販管費対応表からvlookupで引っ張ってきます。
=vlookup(C4,'販管費対応表'!C:D,2,false)
以上を踏まえて、他の項目も集計するとこのような形になります。
3.対予算、対前年の数字を計算する
実績値だけでは、良かったか悪かったが評価しづらいですよね。そのため、次に予算に対して実績がどうだったかを把握できるようにします。
計算方法は2パターンあり、
- 実績 / 予算 - 1
- 実績 / 予算
前者は、予算通りにいった場合数字が0%になり、予算未達の場合はマイナス表示、予算を超えた場合はプラス表示になります。
一方後者は、予算通りにいった場合数字が100%になり、予算未達の場合は90%や80%といった数字に、予算を超えた場合は110%や120%といった数字になります。
個人的には前者を使うことが多いですが、統一さえしていればどちらも使っても良いと思います。（本シートでは前者の表記にしています）
また、実績値を比較する数字として、対予算だけでなく対前年比（YoY）や対前月比（MoM）も見てみると良いです。
最後に
PLの形は事業内容やKPIなどによって変わりますが、大枠はこの記事でご説明した内容になります。ぜひ参考にしてみてください。
また、当社のダッシュボード構築支援については下記をご覧ください。