VLOOKUPとSUMIFSで事業分析（第2回）
かーみーです。
今は中小企業がクラウドによって大手と渡り合えるようになった素晴らしい時代。
これからの方に参考になればとの備忘録。
前回はVLOOKUPとSUMIFS、ピボットテーブルの特徴を説明したが、次は使い方だ。
事業系のデータを「なるべくエレガントに」集計するコツを。
さて、分析といえば売上やP/Lなどがルーチンで必要である。
特に、集計の現場で往々にしてあるのが、元のデータが汚いことである。でも多少のデータクレンジングはエクセルでできるのでその辺を補足しつつ。
さて、データ集計で大切なことは3つある。
①当たり前だがまず間違わないこと。
でも人間は必ず間違えるので検算をしやすくシートを作る事が大切。
②元のデータは極力変更しないこと。
第三者が見て、元データが違うのか、計算が違うのか分からなくなるからだ。後任から「かーみーさんのデータが違っていたので・・・」と責められることは無いようにしたい。
③毎日の集計に耐えられること。
ビジネスで使うデータは毎日増え続ける。いちいち式を直すのはミスの元。
原則一度シートを作ったら更新データをコピペすると自動計算する仕組みが望ましい。
VLOOKUPとSUMIFSで売上データを分析する
では具体的な手順に行こう。
こんな資料を作りたい。月ごとの組織毎、商品グループごとのサマリーだ。
で、使えそうなのはこんな営業マンごとの売上の記録だ。しかしイケてないかな、組織
も商品グループも記載されていない。
大きな手順は以下のとおり。
①VLOOKUPを使って、「集計しやすい」データにする
→集計軸をVLOOKUPで補足し、SUMIFSに耐えうる状態にする
→集計軸に抜け漏れがないようにピボットの機能を一部使う
②SUMIFSを使って、「シンプルに」集計する
→①の集計軸で一気に計算させる
VLOOKUPを使って「集計しやすい」データにする。
1：集計列を加える
まず、元の売上データに「集計用の列」を加えよう。集計用の列は元データの左に。右ではない。元データがいつもX列までとは限らない（勝手に備考とかの列を右に作るやつがいるからだ）。営業マンの気まぐれで列が増えても問題が起きないように、集計用の列は左が鉄則である。
2：集計列に、集計用の値を入れる
次に、集計するために必要な軸を整えよう。今回足りない要素は「組織」「商品グループ」の２つ。担当者から「組織」を逆引きさせ、「商品」から「商品グループ」を逆引きさせればよい。
逆引きするためのマスタを作る
逆引きをさせるためには、マスタを作るのが一番簡単だ。例えば以下のような組織マスタ。これがあれば、VLOOKUPを使って、「営業担当社が広岡さんなら営業1部、山田くんなら営業2部」を入れることができる。なお、マスタを作るのはもちろん手作業なのでそのへんは頑張るしかない。
＜組織マスタ＞
商品はこんな感じ。
＜商品マスタ＞
3：ピボットテーブルで抜け漏れの無いマスタを作る
マスタを作る場合、リストの抜け漏れは致命的だが、数百件を超えるリストから抜け漏れなく洗い出すのは結構面倒だ。 例えば組織の場合、対象の表からピボットテーブルを作成し、担当者を行に指定すれば、簡単にユニークな担当者一覧を作ることができる。商品も同じように一覧を作る
＜ピボットテーブルでユニークな一覧を抽出する＞
４：集計用の表に、VLOOKUPでマスタから引っ張った「集計用の値」を当て込む
ここまでできれば、あとは簡単。元のデータの値を元にマスタから必要な「組織」「商品グループ」の値を引っ張ってくれば良い。商品も同様。
式は以下のような感じになる。
= VLOOKUP（”元データの担当者”,”組織マスタの担当者と組織が入った表”,2,0）
これは日本語でいうと、以下のような命令になる。
B2のセルに、D2の担当者「田中」の組織を入れろ。「田中」の組織は、「組織マスタ」で「田中」を探し、見つかったら田中から数えて2番めの列に記載されている。検索条件は完全一致（0）であるので、ピッタリ合うものを探してこい
VLOOKUPの式が入ったら、全行にコピペする。基本的に同じ式なので、データが増えても同じ式をコピペするだけで良い
５：SUMIFSで一気に計算する
ここまでできれば、ほぼ完成だ。元の集計表から、SUMIFSで条件を設定すれば良い。
式は以下のような感じになる。
=SUMIFS（合計対象範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, ...）
あー、わかりにくい。
わかりやすく日本語でいうと、以下のような命令になる。以下のD4の式。
販売データの「売上（G列）」を全部足せ。条件は2つ。
1つ目の条件は販売データの「売上月（A列）」が「2月（D2）」のものだ。
2つ目の条件は販売データの「組織（B列）」が「営業一部（B4）」のものだ。
この2つの条件に合うように、よしなに足し上げろ
細かく式を見ると、箇所箇所に＄マークがついているのがわかると思う。
これは「絶対参照」というやつで、式をコピペしても固定になる。まあこのへんはどっかのタイミングで説明できればと。
商品も同様にSUMIFSをぶっこめば完成だ。以下はC10の式。
販売データの「売上（G列）」を全部足せ。条件は2つ。
1つ目の条件は販売データの「売上月（A列）」が「1月（D2）」のものだ。
2つ目の条件は販売データの「商品くくり（C列）」が「食品（B10）」のものだ。
この2つの条件に合うように、よしなに足し上げろ
６：まとめ
上記のような工程であれば、大体のデータの形は集計可能になる。P/Lなら勘定科目でマスタを作ればいいだろう。
今回は割愛するが、マスタも随時更新されるビジネスの場合は、IFERROR関数を使えばVLOOKUPでエラーになる値を「その他」にするなどの回避ができるので、いったん大枠外さない集計をかけ、後でエラー部を個別に精査するといった段取りも組めるようになる。
集計する元のデータを再度コピペし、ちょこちょことマスタの抜け漏れがないかの手続きを行えばいいので、毎日の集計にもそこそこ耐えうるレベルまで業務工数は減らすことが可能だ。
