VLOOKUPとSUMIFSで事業分析（第4回）P/Lを作ってみる
かーみーです。
今は中小企業がクラウドによって大手と渡り合えるようになった素晴らしい時代。
これからの方に参考になればとの備忘録。
前回はVLOOKUPとSUMIFSで売上を集計してみたが、次はP/L集計。
部門ごとP/Lを作ってみよう
karmie.hatenablog.com 前回、超簡単な売上集計表を作ってみたので、次はP/L分析表などを作ってみよう。今回使う元データは、会計パッケージから吐き出した部門毎の科目毎推移表だ。
縦軸に勘定科目、横軸に月が切られている。
（今回の記事では、当然ながらダミーデータも見せる事はできないので、マスクを入れている）
分析軸は縦軸が勘定科目、横軸は月。
で、（ここがミソだが）あくまでP/Lを分析したいのは事業なので、事業毎にシートを分けたい。元データを更新すれば一挙に全部門のデータが更新されるのが理想的だ。順番を追っていこう。
縦軸に勘定科目、横軸に月が切られている。
（今回の記事では、当然ながらダミーデータも見せる事はできないので、マスクを入れている）
分析軸は縦軸が勘定科目、横軸は月。
で、（ここがミソだが）あくまでP/Lを分析したいのは事業なので、事業毎にシートを分けたい。元データを更新すれば一挙に全部門のデータが更新されるのが理想的だ。順番を追っていこう。
まず、全社P/Lシートを作る
これは簡単。勘定科目の一覧は会計パッケージに規定されているので、縦軸に勘定科目一覧を並べる。科目名称は同じ科目名で原価・販管費が存在するようなケースもあると困るので科目コードも併記する。
推移表であれば単純にタテをSUMIFSで足しこむだけである。まあ、原価率なども知りたくなるので、そこだけ式を埋め込めば良い。
=SUMIFS（参照先の合計する列、参照先の科目コードが記載された列、集計表の科目コード）
日本語で言うと、こんな感じだ。
参照先の10月の列を足しあげろ。条件は1つ。
売上高のコードだ。よしなにやれ。
あとは、この式をよしなに全体展開すればよい。簡単。＄マークの絶対参照だけ気をつけよう。ここを間違えると、コピペの後にイチイチ式を修正しなければならず、超時間がかかる。
タイムイズマネーである。
全社が出来れば次は部門。
まずは部門コードをもとに部門マスタをつくろう。前回の記事を参考にしてもらえばよい。狙いは、集計用組織をVLOOKUPで読み出せるようにすることだ。
複数部門を統合しているような場合もあるので、丁寧に分析をかけたい軸が表現できるようにマスタを作る。あとで使うので、集計したい組織のプルダウン用リストも作っておく。
元のデータ表の左に1列加える。
で、VLOOKUPを埋め込み、先のマスタを参照させ、集計用の部門を表示させる。黄色い列だ。これで、SUMIFSを使えば集計部門で拾うことができるようになる。
材料が揃ったので、集計表に移ろう。
先程作った全社のPLをコピーし、部門集計用のシートを作る。今度は集計表の上の方に、部門を入れる箇所を作る。ついでに、この部門は先のプルダウン用リストを参照先にしておく。
そろそろ大詰め。コピーした全社P/L表のSUMIFS分に一文加える。追加箇所が赤。
=SUMIFS（参照先の合計する列、参照先の科目コードが記載された列、集計表の科目コード、参照先の集計用部門の列、集計表のプルダウンの集計部門）
日本語で言うと、こんな感じだ。
参照先の10月の列を足しあげろ。条件は2つ。
売上高のコードだ。よしなにやれ。
あ、忘れてた。今回は第一事業部の組織が対象だ
あらためて式の＄マークに気をつけながら、シート全体の関数に適用すると、
A1セルのプルダウンで組織を選ぶことで、自由に好きな組織のP/Lを表示することができる。このままでも使えるが、最後の仕上げに部門PLのシートをいくつか複製し、プルダウンのデフォルト値を各部門用に設定しておけば、そのまま各部門に展開が可能だ。
ちょっと駆け足で雑になったが、月次決算が〆ったら確定の推移表を更新するだけで、各部門が自分のP/Lを参照できる表の完成である。
