Microsoft 365とExcel 2021以降で使える UNIQUE関数やFILTER関数が、重複チェックに超便利!

投稿日 2024年4月15日
著者 root

せるワザ

Excelを使う代表的タスクの1つに、データ重複チェックがあります。

社員名簿や顧客名簿、売上日報や在庫目録などを、長時間かけて情報蓄積すると、高い確率で”情報のダブり”が生じます。

”昨日の自分は赤の他人!?”で、すでに完了した作業をくりかえすことも、よくありますからね。

そのようなダブりを発見し、効率よく削除するためにExcelはよく活用されていますが、今回はこの強力な助っ人となる新関数、UNIQUEとFILTERを使ってみましょう。

UNIQUE関数を使うと、元データをのこしながら重複データを削除できます

Excel 2019以前のバージョンで重複データを削除する場合、［重複の削除］ウィザードを使うことが多いのではないでしょうか?

［データ］→［重複の削除］ボタンをクリックして、表示されるダイアログボックスから、重複を削除する列を指定して［OK］します。

たしかに、この方法で手軽に重複データを削除できますが、元データの集合体に手を加えるので、あとから「データをどのように変化させたか」追うことができません(ワークシートそのものを複製して、バックアップする必要があります)。

業務上重要なデータや大量レコードは、トレーサビリティ(追跡可能性)を確保するためにも、元データをのこしながら、関数で重複削除した方が安心です。そこで、今回ご紹介する新関数UNIQUEの登場です。

UNIQUE 関数は、一覧または範囲内の一意の値の一覧を返す関数です(※ Microsoft公式サポートページ転載)。2024年現在、Microsoft 365、およびExcel 2021以降のバージョンで使用可能です。

構文は、

= UNIQUE（範囲, ［検索方向］,［回数］）

となり、［検索方向］と［回数］は省略可能なため、今回は詳述しないこととします。

ためしに、以下のような日別/顧客名別の売上記録から、重複する顧客名をUNIQUE関数で削除してみましょう(今回はMicrosoft 365版Excelを使用しています)。

ワークシートのA:D列に、約10日間分の顧客別購入金額記録が100件あります(ダミーです)。同一人物が複数回購入するケースもあり、100件の中には氏名の重複があります。

わずか100件でも目視重複チェックをするのはゾッとしますから、実務データで数千件、数万件あるレコードの重複チェックは、とても手動ではできませんね。

そこで、セルG2へ

=UNIQUE(C2:C101)

※C2:C101が重複データを検索する範囲

と入力します。これだけで「スピル」機能が働き、それ以下の行には、関数式をドラッグしたのと同じ結果が自動表示され、ユニーク(一意)なお名前は100件中92件だったという結果が即座にえられました。

FILTER関数とCOUNTIF関数を使って“重複するデータだけ”を抜き出すこともできます

Excel2019以前の［重複の削除］ボタンも、上記UNIQUE関数による検索も、いずれも「重複をはぶいて、ユニーク(一意)のデータのみを残す」作業でしたが、「重複するデータだけを抽出したい」場合はありませんか?

たとえば、売上記録なら「購入記録が重複するリピーター顧客にVIPフラグを立てたい」ような場合です。このような作業には［重複の削除］ボタンは使えません。

どうしたらよいでしょう?

こんな時には、新関数FILTERを使ってみてください。

FILTER関数は、定義した条件に基づいてデータの範囲をフィルター処理する関数です(※ Microsoft公式サポートページ転載)。

ごく簡単に言えば、FILTER関数は、従来からのExcel標準機能である「フィルター」を関数化したもので、構文は、

= FILTER（配列, 含む(条件)）

となります。

従来のフィルター機能も、元データに手を加える表示となりますから、トレーサビリティを確保するためには、元データはのこしておいて、FILTER関数で結果を抽出する方が安全です。

今回は、まずE列に氏名の重複回数をカウントするCOUNTIF関数( =COUNTIF($C$2:$C$101,C2) )を入れ、最終行までドラッグします。

氏名の重複件数が1〜4までカウントされますから、このE列数値をキーにフィルターをかけることができます。

しかし、今回は元データをのこし、I:J列へ、求める結果を関数で抽出するようにします。

まずセルI2へ、

=FILTER(C:C,E:E>1)

※C列全体を検索し、E列でカウントした数が1より大きい(=氏名が重複する)データのみフィルターする

と入力すると、ここでもスピル機能がはたらいて、一発で全重複レコード12件が自動抽出されます。

ここでふたたびI列へUNIQUE関数を用いて、

=UNIQUE(I:I)

と入力すれば、複数の購入履歴がある上客は、4名だったことがわかりました。 100レコード中、前章で［重複の削除］をしたユニーク(一意)な氏名は92件でしたから、ここから上客4名をのぞいた88名が「1回のみの購入」。そして、上客4名で12回の購入履歴をのこし、合計100件の購入記録であったことが、この作業で判明しました。