業務が捗るExcel術。重複データを削除する

自分で入力した案件一覧表など、Excelに入力しているデータは、誤入力によって同じ内容のデータが複数できてしまう時があり、重複しているデータは削除する必要があります。また、クライアントから提供されたデータをアイテムごとに仕分けして集計する時は一覧表の中に複数回存在しているアイテムで重複しているものを消さなくてはなりません。目視で重複しているデータを削除するとミスが怖いですし、何よりも手間がかかります。

そのための方法として代表的なのは、Excelのデータタブにある重複の削除機能です。この機能は、対象となる表をクリックし、データタブの重複の削除をクリックすることで実行できます。

しかし、元のデータが追加されたらまた重複の削除をしていかなければならず、操作が面倒だったり、操作し忘れが起きたりする可能性もあります。

そこで、Excel計算式であれば、で元データの入力と同時に重複データを削除してくれますので、その方法を紹介します。

計算式で重複を削除する手順

計算式で重複を削除するには、COUNTIF関数で何回目の登場かを検出し、その結果をIF関数ではじめて登場したときだけインデックスが+1するようにし、そのインデックスをMATCH関数で見つけて、そのインデックスに対応する値をINDEX関数で見つけるという4段階で作業します。といっても言葉ではなかなかわかりません。次の手順で実際にやってみましょう。今回は次の表で重複を削除していきます。

COUNTIF関数で何回目の登場かを調べる

COUNTIF関数でそのアイテムが登場したのは上から数えて何回目なのかということをCOUNTIF関数で求めます。COUNTIF関数の書式は次の通りです。

=COUNTIF(範囲,値)

今回の表で重複を削除する場合、B列にA列のその行の値が何回目に登場したものを求めます。セルB2にはセルA1からセルA2の中でセルA1が何回目に登場したかを求めることになります。セルB5ではA1からセルA5の中でセルA1が何回目に登場したかになります。この場合、コピーすることを考えると、セルA1は数え始める範囲として固定されるので絶対参照、他のセルは相対参照になります。そうするとセルB2には次のような計算式になり、その式を下までコピーすることになります。

=COUNTIF($A$1:A2,A2)

今回の範囲のところにある「$A$1:A2」のように範囲の片方だけが絶対参照になるという使い方もできるのがポイントです。

IF関数でインデックス番号を作る

それぞれのアイテムが1回目の登場だった場合、上から順番に番号を振っていきますが、そのために使うのがIF関数です。IF関数の書式は次のとおりです。

=IF(評価式,評価式が真の場合,評価式が偽の場合)

今回は、B列が1回目を表す場合かどうかを調べるのが評価式です。その評価式が成立していて真の場合は上のセル+1、成立していなくて偽の場合は上のセルそのものを出しますので、セルC2には次の計算式が入り、下方向にコピーされます。

=IF(B2=1,C1+1,C1)

はじめて登場した行でカウントアップされるインデックス番号を求めることができました。

MATCH関数で一番初めに見つかったインデックス番号の位置を調べる

今度はC列に求めたカウントアップされるものの中ではじめてその数字が登場した行番号を、その数値ごとに調べます。そのために使うのがMATCH関数です。MATCH関数の書式は次のとおりです。

=MACTH(検索値,検索範囲,一致方法)

今回の検索値は1から始まる番号です。1から始まる連続番号の列を用意してもいいのですが、今回は行番号を求めるROW関数を使うことにします。検索範囲は行の範囲選択ではなく、C列丸ごとを対象にします。一致方法は根気アは完全一致なので完全一致の場合は0と指定します。セルE1に次のように計算式を作成し、セルE14までコピーしておくとよいでしょう。

=MATCH(ROW(),C:C,0)

1ならC列の2行目、2ならC列の3行目、3ならC列の4行目、4ならC列の8行目と求まりました。MATCH関数はこのように探したいものが初めて登場したのが何番目かを求めることができます。セルE5からの結果が#N/Aとなっていますが、これはそのデータは見つからなかったという意味です。今回はそのままにしていてもよいでしょう。

INDEX関数で調べた何番目かのデータを求める

いよいよ最後のステップです。E列に求めた上から何番目という値を元に、A列の中でその何番目にあたるデータを取り出します。INDEX関数は範囲の中で指定した番目のデータを取り出す関数で、今回の処理にはぴったりなのです。INDEX関数の書式は次のとおりです。

=INDEX（範囲,番目）

今回の範囲はA列全体、何番目を求めるかというのはE列に求まっているので、セルF1に計算式を作るのであればセルE1となり、次の計算式を作成し、セルF14までコピーすればよいのです。

これでF列の中で#N/Aよりも上のセルに重複がないデータを作り出すことができました。

計算式で複数列の重複を削除する

上記の例では元データは1列しかありませんでした。しかし、実際には複数列で重複していないかチェックすることが多いと思います。その場合ははじめのCOUNTIF関数をCOUNTIFS関数に変えるだけでいいのです。COUNTIFS関数の書式は次のとおりです。

=COUNTIF(範囲1,値1,範囲2,値2.範囲3,値3・・・)

範囲と値の組み合わせは最大で127個までできますが、そこまで必要になるケースはないでしょう。

次のようなケースでは、セルE2に入るはじめのCOUNTIFS関数を次の計算式とします。

=COUNTIFS($A$1:A2,A2,$B$1:B2,B2,$C$1:C2,C2)

重複していない行が1になっています。

新しい関数で重複を削除する

Microsoft365またはExcel2021では新しい関数が使え、その中に重複を削除するUNIQUE関数があります。UNIQUE関数の書式は次のとおりです。

=UNIQUE(範囲)

次の場合でセルE2には次の関数が入っています。コピーする必要もなく、セルE3だけに計算式が入っていればいいのです。

=UNIQUE(A2:C14)

もちろん計算式ですので、元データの値を変更したら即座に結果も再計算されます。新しい関数が使える場合には積極的に使うとよいでしょう。

まとめ

今回はExcelで一覧表にある重複を削除する方法について解説しました。

Excelの機能で行う場合はデータタブの中の重複の削除機能を使うと良いのですが、一回一回操作する必要があるので元のデータに変更があるごとに操作しなければならない面倒さがあるので、元データが変わったら即座に重複が削除されたデータを作るには計算式である必要があるということを説明しました。

重複を削除する計算式は、最新のExcelを使えばUNIQUE関数を使うことができますが、そうではない場合はCOUNTIF関数、IF関数、MATCH関数、INDEX関数を組み合わせていきます。なかなかこの組み合わせをどうしたらいいかが思いつかないケースもありますが、INDEXとMATCH関数の組み合わせで、ある番号が範囲の中ではじめて登場した行の値を返すことができること、COUNTIF関数で範囲の片方だけを絶対参照にすることで上から順に範囲を広げることができるという、一つ一つは小さなテクニックの積み重ねです。

そのためにはいろいろな関数の様々な使い方を見た時に、そのままにせずに、記憶にとどめる為にメモを取ったり、実際に同じような動作をしてみることが大事です。