（読了時間＝約 3 分）
現場でいちいち教えてられない細かいテクニックシリーズ（笑）。今日は、受け取ったデータを効率よくチェックする方法です。
クライアントからデータを受け取って分析をしたりする時に、いつの間にかフォームが変わってたり、数字の縦計横計が合わなかったりすることがあります。
本日のお題
例えばこんな表を月次更新で貰っているとして、その最新版が来たとします。
説明用に単純化してますが、実際には縦軸・横軸ともにもっと項目が多い表がきたことを想定してください。
これをチェックするのが今回のお題です。
まずは縦軸・横軸をチェック
まずは表の形式が変わってないかチェックします。
こういう表でよくあるのが、横軸で言えば、例えば新しい店ができたので追加しましたとか。縦軸だと、科目のくくりを見直しました、とかです。
単純に追加だけであれば列数や行数が増えるので分かりやすいんですが、同時に閉店する店があったり、別の科目をその他にまとめたり、ってことが起こると行と列の数は変わらないので中身は変わっているということが起こります。
こういうのをしっかりチェックするわけです。
まずは横軸。21行目に先月の横軸を値貼り付けして、22行目で比較していきます。同じだったらゼロ、違ってたら1を表示するようにしています。
D22のセルで式を入力したら、それをG列までコピーすれば大丈夫です。相対参照で、自動的に参照セルが変わりますから。
で、次に縦軸。こちらも同じですね。先月の縦軸を貼って、D24に今月のと比較する算式を入れます。
縦軸はサブカテゴリーがあって2列表記になってるので比較も2列で行います。でもやることは一緒で、D24のセルに式を入れたらそれをD24:E35にコピーするだけ。
相対参照の機能をうまく使いたいので、横軸は横並び、縦軸は縦並びで構成していくわけです。
次に縦計・横計をチェック
次は、表の中の合計欄とかの計算が合ってるかのチェックです。
まずは縦計から。ここは、例えば売上の内訳が売上合計に合ってるかとか、売上ー売上原価＝売上総利益になっているかとか、検算すべき組み合わせを一通り考えてチェックしていきます。
もし違ってたらどれくらいの金額違っていたかも知りたいので、ここは「if文」でなく「誤差」そのものを算出しに行きます。
この例では販管費の明細と合計とで100千円の差があるということが検出されています。
次に横計のチェックを入れます。こちらは、店舗別の数字の合計が合計欄の数字と合っているかという検証です。
こちらも誤差の金額を出すように計算式を作ります。縦軸は科目名と一緒なので、D43のセルをD43:D54にコピーすればOKです。
こっちの方は間違いはないようで何よりでした。
誤差があった場合は修正しちゃえばいいや、という考え方はあると思います。実績数値の集計で、集計欄が各店の合計になる場合で合計が間違っていたらそりゃ計算ミスですよね。
でも例えば予算の場合はどうでしょう。予算では、全体を先に決めて、それを店別とか製品別にブレイクダウンするということがよく行われます。
こういう時に合計が合わなかったとしたら、修正すべきは合計数字ではなく、明細数字のどれかということになります。
なので、状況を考えて、必要な時には相手先（クライアント）に確認をする必要があります。
細かいんですが、こういう確認は結構重要です。
ここをいい加減にやってると、分析結果の信憑性がどんどん落ちてゆくことになってしまいます。
他の資料に同じチェックを展開する
さて、これで数値チェックは一通りできたわけですが、こういう作業が毎月発生します、という場合はどうでしょう。
あるいは、これが部門別の資料で、この会社にはこういった部門が20位あるとしたら。
いちいち作っても良いですが、同じ作業になりますし面倒ですよね。
そういう時は、この一連のチェックを他のシートにも適用するわけです。
まず、今回作ったチェックの全体像を見てみましょう。
上の表でグレーの部分がチェック結果です。
薄いグレーと濃いグレーがありますが、薄いグレーのセルは濃いグレーのセルをコピペして作ったセルであることを表しています。
何故こういう分け方をしているかというと、事後でチェック内容を変えたり検証したりする場合、濃いグレーのセルだけを見れば良いからなんです。
割と細かい技なんですが、こういう習慣をつけることで「混乱した時の」作業の効率性が抜群にアップして、生き延びる確率がかなり高くなります。
話を戻します。
チェックのモジュール化というのは、このチェック範囲全体を他の資料にもコピペするということです。
エクセルのコピペでは、数式は相対参照になりますので、同じ形式の表に対しては、同じ相対位置にコピペすれば、同じチェックが実行できるわけですね。
チェックモジュールを使いやすく
で、せっかくチェックをモジュール化するんだったら、もう一工夫して使いやすくしましょう。
例えば、チェックモジュールの一番上に「まとめ」を作ります。「まとめ」というのは、結局チェックの結果何件エラーがあったか、ということです。
基本的にはエラーはないはずですから、まずはここだけサクッとみて、もしエラーがあったら詳細を調べに行く、という形にした方が効率的です。
ここで使っている「COUNTIF」関数は、結構便利なのでまた別途詳しく説明しますが、ここではとりあえず呪文のようにこのまま使ってください。
D22:G54とあるのは対象範囲で、これが”>0″（つまりゼロ以上）のセルの数を数えて表示するという関数です。
モジュールをさらに使いやすく
さらに、チェックに使った範囲をグループ化してしまいます。
サマリは除いて、チェックに使った行を全て選択して、「データ ー グループ化」でグループ化します。
グループ化した行の上下は分かりやすいように色付けしておくと良いです。
今回は青っぽい色にセルを色付けしています。上はチェックサマリの行ですが、下は放置しておくと何もなくなるので、▲を一つおいてこの上の行までにチェックモジュールがあることが分かるようにしています。
グループ化した行は、左の「ー」を押すと畳めるので、下の画像のようにコンパクトになります。
こうなるとかなりモジュールっぽいですよね。要するに、ここで選択されている22〜55行目をコピーして対象となるシートに貼れば、エラーがあるか否かがすぐにわかるというわけです。
もしエラーがあったら畳んだ行を展開すればその内容がわかります。
モジュールを作る時は、貼り付ける先頭行（今回の例で言うと20行目）はキリの良い数字にしておくと良いです。
もらった表から何行か開けて、という形にしたくなりますが、結構数えるのが面倒ですし、エラーで表の行数が増えてる場合もあります。
チェックとしては、シートのどの行から表が始まってるかも重要なポイントになりますので、相対位置でなく絶対的な位置としてモジュールを仕込む行を決めておく方が良いです
常にやれってわけじゃないけど
こういうチェックは常に必要というわけではありません。貰った資料がある程度信頼できるなら、こうした手間をかけずに分析を進めた方が早いです。
ただし、
- 資料を貰う相手の数値管理能力に不安（疑念）がある
- 相手先がたくさんの場合（例えば各店舗別の数字をそれぞれの店舗からダイレクトに貰う場合＝エラーが紛れる可能性が高くなる）
といった場合には要注意ということで、こういったモジュールを仕込むのがプロジェクトを安定させる上で重要になったりします。
結構細かいテクニックですが、参考になれば嬉しいです。
それではまた！
