Excelを扱ってイライラする場面の一つに、「集計値が合わない」があります。
数値をどう変えてみてもうまく合わない。
そもそも、行が多すぎてどこから見れば良いのか検討もつかない……。
原因がどこにあるかわからず、無駄に時間が流れる感覚だけが増していくあの感じは、イヤなものです。
Excelで集計値が合わない場合、そこには様々な原因が考えられます。
小数点以下の処理を適正にしていないとか、そもそも入力値が間違っているといったことがありますが、「データに重複がある」というのもよくあるミスです。
集計値が合わない原因を調べるときのコツは、一つ一つ切り分けて確認していくことです。
その中でも「重複を調べる」のはテクニックを使えば比較的手っ取り早くできるため、最初に試してみるのに適しています。
そこで今回は、Excelで重複を調べるときに使えるテクニックを紹介します。
もしあなたが集計値が合わないことで悩んでいるのでしたら、この記事を読んで重複を調べてみることで、問題を解決できるかもしれません。
ちなみに、数値が桁違いにまちがっている場合はデータの重複が原因である可能性がありますが、1円単位で集計値が合わない場合は、小数点以下の処理が間違っている可能性が高いです。
小数点以下の処理が間違っていないか調べるときにやることは、以前ブログ記事を書いていますので、そちらを参照してください。
重複を見分ける「条件付き書式の設定」
手っ取り早く重複を見つけるのに有効なのが、「条件付き書式の設定」です。
条件付き書式を設定することで、重複しているセルをひと目で確認することできます。
条件付き書式はホームタブ→条件付き書式→セルの強調表示ルール→「重複する値」から設定します。
この方法のメリットは、重複のあるセルに色が設定されるので、色でフィルタをかけることができることです。また、条件付き書式なので、データの変更に即時反応します。
重複しているデータを見ながら修正をするのに向いている方法です。
重複をかぞえるCOUNTIF関数
重複を除いたデータ数や、重複しているデータ数を確認したい場合は、関数を使います。
重複を除いたデータ数を数えたい場合は、SUMPRODUCT関数とCOUNTIF関数を組み合わせて、以下のように書きます。
「=SUMPRODUCT(1/COUNTIF(調べたいデータの範囲,調べたいデータの範囲))」複雑な関数に見えますが、COUNTIF関数で一行ずつの「1÷重複しているデータ数」を出し、SUMPRODUCT関数で合計を出しているだけです。関数の検証をした画面がこちら。
本来出力されるべき「正しいデータ件数」がわかっている場合は、こちらの方法で重複を除いた件数を出すと有効です。
また、重複している件数を調べるにはCOUNTIF関数を使います。
重複している件数は一つのセルで集計することができないので、１列追加して以下のように入力します。すると、指定したセルのデータが、その列の中でいくつあるのか分かるようになります。
「=COUNTIF(調べたい列の範囲,隣のセル」この上で、「=COUNTIF(Sheet1!$B$2:$B$11,”>=2″)」といったように2以上のセルだけ数える関数を書けば、重複しているデータだけを数えることができます。
重複しているデータを数えなくてはならない場面は多くはありませんが、例えば、もとのデータの作りがまずくて、データにダブりがある中で集計をしなくてはならないような時には必要になります。（こういうデータは集計よりも先に整形をすべきですが、それはまた別の機会に……）
関数を使うことのメリットは、常に結果を見ることができるという点です。結果を常に確認する必要があるときに、この方法を使うと良いです。
重複を取り除く方法
Excelでデータの重複を取り除く方法はいくつかありますが、僕が一番おすすめしたいのは、先に書いた”重複を見分ける「条件付き書式の設定」”で紹介した方法で重複に色をつけ、フィルタした状態で1行ずつチェックしていく方法です。
地味だし効率がいい方法ではありませんが、大事なデータを失う可能性は低いです。
CTRL+D（上のセルをコピー）やCTRL+R（左のセルをコピー）などのショートカットキーを使うことで、少しは高速化することも可能です。
また、relaxtoolsというアドインを使うことによって、半角と全角を統一したり、両端のスペースやセル内改行を取り除くことができます。このアドインはデータ整形には欠かせないツールです。
重複を取り除く方法として、Excelの標準機能で「データ」タブに「重複の削除」があることを知っている人もいるかと思います。
この機能は便利なのですが「常に１行目の重複のみを残す」という特徴があります。
「必ず１行目のデータが正しい」とわかっていれば良いのですが、そうしたケースは少なく、「重複の削除」をすることで、本来残すべきだったデータを削除してしまう可能性があります。
一度データを消してしまい、その状態を保存してしまうと、復元作業に倍以上の時間がかかることになります。
重複を取り除くのは楽な作業ではありませんが、地道に丁寧にやるのが近道です。
おわりに
今回は、Excelで重複を見分ける、かぞえる、取り除くテクニックを紹介しました。
Excelを扱う上で、重複は非常にやっかいでイライラする存在です。
集計の数値が合わない原因になるほか、例えばVLOOKUPなどのルックアップ系関数で数値を引っ張ってくるときにも、最初の重複に反応してしまい、目的のデータをルックアップできない原因になります。
今回ご紹介した方法で、あなたのイライラが少しでも解決できれば嬉しいです。