経理を高速化するExcelリストの漏れ・重複箇所の探し方

経理業務にもっとも便利な関数であるSUMIF関数の弱点は、検索条件リストに漏れがあった場合に、その検索条件での集計が丸々抜けてしまうことです。これは集計する項目を限定したいときには便利ですが、完全な集計表を作るときは抜けが生じないよう重々注意する必要があります。

SUMIFと漏れや重複のないリストの作成方法、そしてミスの有無の確認方法は、以下の記事をご参照ください。

▶経理を高速化するSUMIF関数の基本と５つの超便利機能
▶経理を高速化する漏れや重複のない【リスト作成】のExcel技
▶経理高速化とミス防止を同時達成するExcelの【ポカヨケ】設置法

さて、では今回は、集計結果の合計が元データと一致せず、「どこかに漏れや重複があるのはわかっているけど、具体的にどこにあるのかわからない」という状況で、どのように漏れ・重複を探せばよいかをご紹介します。

その方法としては、COUNTIF関数とフィルター機能の２つを使います。経理ではそれぞれ様々な場面で活躍する機能ですので、しっかりとマスターしましょう。

リストの漏れ・重複を効率的に見つける方法

今回は、以下の「SUMIFによる得意先別の売上集計」を例にします。

赤くなっているのはポカヨケですので、エラーが発生していることを教えてくれています。具体的には左側の売上データから、右側の得意先別売上集計を、SUMIF関数で作ってみたところ、集計結果の合計(29,900)が元データの合計(32,360)と一致していません。これは集計表の得意先リストに漏れがあることを意味します。

上記のように「エラーの存在」を確認する方法は、経理高速化とミス防止を同時達成するExcelの【ポカヨケ】設置法で詳しく解説していますので、今回は省略します。

「漏れの存在」がわかったら、これから説明する方法で「具体的に何が漏れているのか」を確認しましょう。

１．COUNTIF関数でデータ数を数える

１－１．COUNTIF関数を入力する

まず、元データの一行めのすぐ隣のセルに、以下の青吹き出しのCOUNTIF関数を入力します。

COUNTIF関数とは、検索範囲の中で検索条件と合致するセルの数を数えるという関数で、以下の数式を入力します。

上記の例の場合、

検索範囲　G3からG5の範囲
検索条件　C3（「東京商会」）

ですので、G3からG5の範囲でC3セルと一致するセルの数を数えるという計算式になります。

検索範囲について「G3:G5」ではなく「$G$3:$G$5」と「$」（ドルマーク）を加えています。これは「絶対参照」といって、通常のセル参照ではコピーした際に範囲がずれ動いてしまうところ、$を加えることでどこにコピーしても同じセルを参照してくれるようになります。一方で「C3」は絶対参照ではない（「相対参照」）ため、コピーと同じように参照セルが動いてくれます。

なぜこのようにCOUNTIF関数で該当するセルの数を数えるかというと、検索条件の内容が検索範囲から漏れていたらゼロが、重複していたら２以上の数値が計算結果として表示されるからです。上記の「東京商会」の計算結果は「１」となりましたが、これは「リストの中に１つしかない」という状態で、本来あるべき形です。それぞれの行の得意先が、リストの中にいくつあるかを数えてあげることで、漏れや重複が焙りだされるのです。