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

  • このエントリーをはてなブックマークに追加
  • LINEで送る
Excelリストの漏れ重複探し

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

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

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

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

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

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

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

漏れ・重複探し

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

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

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

1.COUNTIF関数でデータ数を数える

1-1.COUNTIF関数を入力する

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

COUNTIF関数

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

COUNTIF関数

上記の例の場合、

  1. 検索範囲 G3からG5の範囲
  2. 検索条件 C3(「東京商会」)

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

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

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

1-2.COUNTIF関数をコピーする

上記では「C3」セルの内容しかチェックしていませんが、C列のセルを全部チェックしなければ意味がありません。そこで、COUNTIF関数を入力したセルをコピーしていきましょう。

セルの内容を下のセルにコピーする方法は簡単です。コピーしたいセルを選択して、「フィルハンドル」(以下の赤丸で示した黒い四角)をダブルクリックしましょう

フィルハンドル

すると、以下のとおりデータの端まで自動でコピーされます。

フィル

上記のとおり、C6の「名古屋工業」の計算結果がゼロになりました。集計表から漏れており、集計されなかったことが確認できます。

上記の例はデータが少ないので、どこにゼロがあるか一目瞭然です。しかし、実務ではもっと膨大なデータの中から漏れや重複を探さなければならず、画面をスクロールしながら目で追って探すのは非効率です。そこで、次に紹介するフィルター機能を使って、効率的に「1以外のセル」をピックアップしましょう。

2.フィルター機能で例外を探す

フィルター機能は、表の中から条件に見合う行だけを表示する機能です。実際に使ってみましょう。

2-1.フィルターを設置する

まず、表のどこでもいいのでクリックし、選択します。

フィルター

次に、画面上部の「データ」タブをクリックし、「並べ替えとフィルター」内にある「フィルター」のアイコンをクリックします。

フィルター

すると、表の1行目(タイトル行)に、▼マークの入ったボタンが表示されます。

フィルター

 

これでフィルターの設置は完了です。次に、実際に使ってみましょう。

2-2.フィルターで1以外を絞り込む

さっそく、条件を絞り込みたい(余計な行を表示したくない)列の▼ボタンをクリックします。

フィルター

すると、以下のプルダウンメニューが表示されます。

フィルター

中ほどにチェックマークの入った四角形の欄があります。これはセルの内容を漏れ・ダブりなく表示しており、チェックマークを外すとその内容の行は表示されなくなります

今回は「1」は問題ない行であり、1以外の数字が入った行を知りたいため、「1」のチェックを外しましょう。

フィルター

終わったらOKをクリックします。すると、画面は以下の通りになります。フィルター

なお、再度「1」にチェックを入れると画面は元に戻ります。

 

この方法により、どんなに行数の多いデータであっても、漏れや重複が発生している項目をすぐに見つけることができるようになります。経理を自動化し、経理高速化を実現するためには、非常に重要なテクニックですので、必ず覚えておきましょう。

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

経理の業務効率が5倍になるExcel活用の全ノウハウ

  • このエントリーをはてなブックマークに追加
  • LINEで送る

他社さんと匿名で意見交換しませんか?

株式会社経理救援隊では、経理に携わる皆さまの「他社さんはどんなことをしているのか?」という疑問にお答えするため、匿名アンケートによる情報交換会を組織しております。

無料の匿名アンケート会員に登録して、生の現場情報を交換しましょう。ぜひご参加ください。

詳しく読む

SNSでもご購読できます。

スポンサードリンク

コメント

コメントを残す

*

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください