Googleスプレッドシート・FILTER × COUNTIF

重複データだけを別の一覧に抽出する方法

商品を登録した一覧で、同じ商品名の行が何度も出てくる。重複している商品だけを別の一覧で確認したいけれど、元の行は消したくない。そんなときは FILTER関数とCOUNTIF関数を組み合わせると、元の表を残したまま重複する行だけを別タブに表示できます。

元の商品一覧を残し、同じ商品名の行を取り出す

例えば「商品一覧」には6行の商品があり、「りんご」と「食パン」がそれぞれ2回登録されています。抽出するのはその4行です。「牛乳」と「ヨーグルト」は1行ずつなので含みません。

判定に使うのはA列の「商品名」です。同じ名前の行をまとめて確認するため、ほかの列まで完全に一致するかどうかは、この式では判定しません。

練習シートの「重複候補」タブを開いてください。見出しは入力済みなので、空欄のA2セルを選びます。

「重複候補」A2に貼り付ける式

=FILTER('商品一覧'!A2:D100,ARRAYFORMULA(COUNTIF('商品一覧'!A2:A100,'商品一覧'!A2:A100))>1,'商品一覧'!A2:A100<>"")

この式は、「商品一覧」のA列で同じ商品名が2回以上ある行を、A～D列ごと取り出します。空欄の商品名は除外します。

'商品一覧'!A2:D100：持ってくる元データの範囲
元の「商品一覧」のA～D列、2～100行目。商品名だけでなく、分類・単価・在庫数も一緒に取り出します。
COUNTIF('商品一覧'!A2:A100,'商品一覧'!A2:A100)：同じ商品名が何回あるか
元のA列の各商品名について、A2～A100の中に同じ名前が何回あるか数えます。ARRAYFORMULAが各行分をまとめて計算します。
>1：2回以上の行だけ
出現回数が1より多い、つまり同じ商品名が2回以上ある行が対象です。1回目の行も含みます。
'商品一覧'!A2:A100<>""：空欄の商品名は除外
商品名が空欄の行を出さない条件です。判定列のA2～A100を指定しています。

参照元の範囲 A2:D100 と、条件で使う A2:A100 は、どちらも2行目から100行目までにそろえています。ずれると、条件と取り出す行が対応しなくなります。

Enterキーを押すと、「りんご」が2行、「食パン」が2行の合計4行がA2:D5に表示されます。牛乳とヨーグルトは表示されず、元の「商品一覧」の6行は残ったままです。

別の表に使う場合は、まず何をもって重複とするかを決めます。別の表で取引先名の重複を調べるなら、取引先名の列をCOUNTIFの中の2つの範囲と空欄チェックの範囲に指定してください。抽出したい列が違う場合は、FILTERの最初の範囲を変えます。

この練習例は100行目までを対象にしています。101行目以降にも追加するなら、式の 100 を必要な最終行にすべて変更してください。行数をむやみに増やすと計算が重くなる場合があります。

「#N/A」と表示された: 指定した範囲に同じ商品名が2回以上ある行がない場合、FILTERは #N/A を返します。範囲内に本当に重複があるか、判定する列を間違えていないか確認してください。
「#REF!」や「配列結果は展開されません」と表示された: 結果はA2から右と下へ広がります。取り込み先に値や結合セルが残っていると表示できません。必要な値を別の場所に退避してから、表示先を空けてください。