いきなり答える備忘録

Google Workspace・Microsoft 365・LibreOfficeなどに関するメモ

(Excel)重複しているデータを抽出する方法

 Excelで、データ中の重複している値を抽出したり、複数列の値が重複している行を抽出する方法についてです。
 条件とする(重複をチェックする)列以外の列を含めて抽出することができます。

  • FILTER関数とCOUNTIF(S)関数を併用することで重複している値を抽出したり、ある列(1つないし複数)の値が重複している行を抽出することができます。

値が重複するセルを抽出する場合

 次の画像ではD3セルに関数式を入力し、B列に記録されている商品名のうち重複しているもの(同じ値が2つ以上あるもの)だけを抽出しています。

 D3セル

=FILTER(B3:B9,COUNTIF(B3:B9,B3:B9)>1)

 COUNTIF関数を使って各行のB列の値がB3:B9の範囲内にいくつあるかカウントし、1より大きい場合にその行を抽出しています。
 COUNTIF関数の第1引数(条件範囲)と第2引数(条件)が同じになるので奇妙な感じがしますが、これにより各行において次のような式が実行され、抽出対象とするかどうかが判定されます。

COUNTIF(B3:B9,B3)>1
COUNTIF(B3:B9,B4)>1
・・・
COUNTIF(B3:B9,B9)>1

 


 同じ値を複数個表示しない(どの値が重複しているか確認できれば良い)、という場合はUNIQUE関数を併用します。

 D3セル

=UNIQUE(FILTER(B3:B9,COUNTIF(B3:B9,B3:B9)>1))

 


 次の画像は対象データが縦横のセルに並んでいるケースです。

 F3セル

=TOCOL(IF(COUNTIF(B3:D6,B3:D6)>1,B3:D6,NA()),2)

 上記例の延長で考えるなら「=LET(a,TOCOL(B3:D6),FILTER(a,COUNTIF(a,a)>1))」のようになるのですがCOUNTIFの第1引数で関数を使うことになるのでエラーになります。そこでIF関数を使って重複していない値をエラーに変換し、TOCOLをフィルタ代わりにしてエラーを削除しつつ縦に並べるということをしています。
 無理せず①TOCOL関数で縦に並べ直す、②上記と同様にして重複を抽出する、の2段階にした方が簡単かと思います。

1つの列の値が重複する行を抽出する場合

 質的には上記例と変わりませんが、より実用的な例です。
 次の画像では、各行の氏名の値をチェックし、重複する行の3つの列を抽出しています。同一人物の重複入力チェックを想定した例で、この抽出結果からは2つの「高橋 和夫」が同姓同名の別人であると推測されるのに対し、2つの「宮本 正樹」は同一人物であることが推測されます。

 F3セル

=FILTER(B3:D9,COUNTIF(B3:B9,B3:B9)>1)

 ここでもCOUNTIF関数を使って各行のB列の値がB3:B9の範囲内にいくつあるかカウントし、1より大きい場合にその行を抽出しています。
 FILTER関数による抽出範囲が複数列になっているだけで、式の内容としては最初の例と同様です。



 ちなみにここまでの結果でわかるとおり、行の順序は元の表と同じ(初出順)となりますので重複データ同士が並ぶとは限りません。ソートが必要な場合はSORT関数を併用します。


複数列の値が重複する行を抽出する場合

 2列以上の値をチェックし、いずれも重複している行を抽出するケースです。
 次の画像も上記と同様に重複入力チェックを想定したもので、B列の氏名とC列の生年月日が共に重複する行を抽出しています。

 F3セル

=FILTER(B3:D9,COUNTIFS(B3:B9,B3:B9,C3:C9,C3:C9)>1)

 COUNTIFS関数を使い複数条件でのカウントを行っています。つまり各行において「その行のB列の値とB列の値が同じで、かつ、その行のC列とC列の値が同じである行」がいくつあるかカウントし、結果が1より大きい行を抽出しています。
 これまたCOUNTIFS関数の引数が奇妙な感じですが、各行において次のように解釈されカウントが実行されます。

COUNTIFS(B3:B9,B3,C3:C9,C3)>1
COUNTIFS(B3:B9,B4,C3:C9,C4)>1
・・・
COUNTIFS(B3:B9,B9,C3:C9,C9)>1