VLOOKUPとSUMIFSで事業分析（第3回）検算とIFERRORを使った回避処理
かーみーです。
今は中小企業がクラウドによって大手と渡り合えるようになった素晴らしい時代。
これからの方に参考になればとの備忘録。
前回はVLOOKUPとSUMIFS、ピボットテーブルを使った集計のコツを紹介したが、今回は日々の運用について
前回、SUMIFSとVLOOKUPを使った簡単なデータの集計の方法を書いた。
乱暴に言えば、不備のあるデータを、不備を補足する逆引きマスタで集計しやすいデータにし、SUMIFSで一気に集計するというものだ。
ざっくりふり返ると以下のような方法。
①VLOOKUPを使って、「集計しやすい」データにする→集計軸をVLOOKUPで補足し、SUMIFSに耐えうる状態にする→集計軸に抜け漏れがないようにピボットの機能を一部使う→上記を行うためにマスタを作る②SUMIFSを使って、「シンプルに」集計する→①の集計軸で一気に計算させる
SUMIFSの弱点をカバーする方法
さて、上記の方法には、実は致命的な弱点がある。「マスタが正しくないと、データが狂う」ということだ。例えば以下のようなデータ。マスタに存在しない「乾君」と「じゃがいも」が入ったことで、組織と商品グループがエラーになる。
あくまでSUMIFSは条件で足し上げるだけである。マスタに存在しないデータが入ってくると、認識ができないため、結果が「#N/A」になり、そのままSUMIFSしても合計値が合わない。
こういった問題が回避するために、運用上はいくつかヘッジを入れる必要がある。
１：SUMIFSを使った、検算式をこっそり仕込む
しごくアタリマエだが、検算を仕込む方法だ。
SUMIFSを使った集計の場合、「個々の要素の合計」と「全体の合計」を比較すれば良い。例えば組織毎の売上集計の場合、「個々の部門の合計」と「単純な総計」を比較することだ。
SUMIFSの「組織」条件を外したものを検算式に埋め込み、その上で集計結果と比較すれば、必ず差分が出てくる。
例えば6月「乾君」の売った「じゃがいも」はマスタに含まれていないので「営業1部」「営業2部」のいずれにも含まれていないため、個々の合計と総計を比較すると差分が出る。異なる方法で「総計」を出すことで、必ず漏れに気がつくことができる。
ただし、毎日のようにデータが増えていく事業や、そもそもマスタが汚い事業の場合、毎日バグ探しに追われることになる。で、以下の方法もあわせて検討する。
２：IFERRORを使って、マスタに新しい値が入ってきても一旦処理させる方法
上記検算をちょっと緩くした感じの方法だ。マスタに無い新しい値が入ってきても、一旦「その他」で集計させるので、合計値は合う。一旦時間稼ぎをしつつ、「その他」の中身を調べる、といった方法だ。
VLOOKUPにIFERRORを組み合わせエラー値を「その他」にする
IFERRORというのは、「エラーがなかったら計算しなさい。エラーの場合は○○を返しなさい」という関数である。これに元々のVLOOKUPを組み合わせると、イケてる関数になる。青い箇所は、前回作ったVLOOKUPのままである。
= IFERROR(VLOOKUP（”元データの担当者”,”組織マスタの担当者と組織が入った表”,2,0）,"その他")
これは日本語でいうと、以下のような命令になる。
B2のセルに、D2の担当者「田中」の組織を入れろ。「田中」の組織は、「組織マスタ」で「田中」を探し、見つかったら田中から数えて2番めの列に記載されている。検索条件は完全一致（0）であるので、ピッタリ合うものを探してこい
で、ピッタリ合うものが見つからなかった場合は「その他」にしておけ
この関数を埋め込むと、にっくき＃N/Aが「その他」に早変わり。
で、ついでに元の集計表にも「その他」を入れる。式はそのままコピペすれば良い。
あら不思議、小計と合計は必ず一致し、マスタに入っていない項目は「その他」に現れるようになった。
多少汚いデータでマスタに新たな「商品」や「営業マン」が突如登場しても、とりあえずその日は「その他」で集計させておいて後で洗う事ができる。
これで、毎日朝イチの営業会議で報告が必要な担当者も、「○○君、小計足しても5万円足りなくないか？」といった指摘にビビる必要はない。
「本日の売上は100万円です。一部組織が未設定の売上5万円が含まれていますが、明日の会議までに確認して報告いたします」とサラッとかわすことができる。
ビジネスに大切なのは情報精度とスピードだ。スピードを維持しつつ、全体としては精度を担保し、どうでもいい重箱隅ハナシはよしなに受け流しながら回すのが良いのではないかと思う。
