顧客コードや取引先名の列から、2回以上出てくる値だけを取り出したいことがあります。重複の削除やUNIQUE関数で作る「重複を除いた一覧」では、この目的には合いません。この記事では、まずCOUNTIFで値ごとの出現回数を数えます。次に、2件以上ある値だけを1件ずつ並べ、その横に出現回数を出します。元の表は消したり並べ替えたりしないので、確認用の一覧として安全に使えます。
先に決めること：「重複件数」は何の数か
「重複件数」は、次の2つのどちらを指すかで数字が変わります。
| 数え方 | T001が3行あるときの数 | 使う場面 |
|---|---|---|
| 出現回数 | 3 | 同じ値が何行に出てくるかを見る |
| 余分な行数（出現回数−1） | 2 | 1件だけ残すと何行減るかを見る |
この記事の一覧には「出現回数」を出します。余分な行数は最後の確認で計算します。
もう1つ決めておくのが「同じ」とみなす基準です。1列（取引先コードなど）で判定するのか、複数列の組み合わせ（日付＋伝票番号など）で判定するのかを先に決めます。
例に使う表
A列に取引先コードが入っている表を使います。A9は空白です。
| セル | 取引先コード |
|---|---|
| A2 | T001 |
| A3 | T002 |
| A4 | T003 |
| A5 | T001 |
| A6 | T004 |
| A7 | T002 |
| A8 | T001 |
| A9 | （空白） |
| A10 | T005 |
ほしい結果は次のとおりです。
| 重複している値 | 出現回数 |
|---|---|
| T001 | 3 |
| T002 | 2 |
方法1：FILTER関数が使える場合
FILTERとUNIQUEは、Microsoft 365、Excel 2021以降、Web版Excelなどで使えます。入力して #NAME? になる場合は使えないバージョンなので、方法2へ進んでください。
重複している値を1件ずつ出す
C2に次の式を入れます。結果は下方向へ自動で広がる（スピルする）ので、C3以降は空けておきます。
=UNIQUE(FILTER(A2:A10,(COUNTIF(A2:A10,A2:A10)>=2)*(A2:A10<>""),"重複なし"))
式は次の順に動きます。
COUNTIF(A2:A10,A2:A10)が、各行の値が範囲内に何回あるかを行ごとに返します。T001の行は3、T002の行は2、T003の行は1です。>=2で、2回以上の行だけがTRUEになります。(A2:A10<>"")を掛けて、空白セルを対象から外します。- FILTERが条件に合う行の値を取り出します。この時点では、T001が3回、T002が2回並んでいます。
- UNIQUEで、同じ値を1件にまとめます。
該当する値が1つもないときは「重複なし」と表示されます。FILTERの第3引数を省くと、この場合は #CALC! になります。
出現回数を横に並べる
D2に次の式を入れます。C2# は、C2から広がった範囲全体を指します。
=COUNTIF(A2:A10,C2#)
C列の値が増減しても、D列は自動で同じ行数に合わせて広がります。C2が「重複なし」のときは、D2は0になります。
回数の多い順に並べる（任意）
HSTACK関数が使える環境なら、値と回数を1つの式で作り、回数の多い順に並べられます。
=LET(r,A2:A10,k,UNIQUE(FILTER(r,(COUNTIF(r,r)>=2)*(r<>""))),SORT(HSTACK(k,COUNTIF(r,k)),2,-1))
この式は、重複が1件もないと #CALC! になります。
範囲を固定しない
行が増えていく表では、A2:A10 のような固定範囲だと新しい行が対象から漏れます。元の表をテーブルにして、テーブル1[取引先コード] のような列の参照に置き換えると、行を追加しても対象に含まれます。
なお、範囲全体を条件としてCOUNTIFに渡すこの式は、行数が増えるほど計算量が急に増えます。数万行を超えて重く感じる場合は、ピボットテーブルで件数を数える方法なども検討してください。
方法2：FILTER関数が使えない場合（補助列を使う）
Excel 2019以前などでは、補助列で「出現回数」と「重複している値が最初に出てきた行」に印を付け、別の列に一覧を作ります。
| 列 | 見出し | 2行目の式 | 役割 |
|---|---|---|---|
| B | 出現回数 | =IF(A2="","",COUNTIF($A$2:$A$10,A2)) |
各行の値が何回出てくるか |
| C | 連番 | =IF(AND(A2<>"",B2>=2,COUNTIF($A$2:A2,A2)=1),MAX($C$1:C1)+1,"") |
重複している値が初めて出てきた行に1、2、3…と番号を振る |
B2とC2に入力したら、A列のデータがある最終行までコピーします。C列の COUNTIF($A$2:A2,A2) は下の行へ行くほど範囲が広がるので、同じ値の2回目以降の行には番号が付きません。C1には見出しの文字を入れておきます。MAXは文字を無視するので、最初の番号は1になります。
例の表では、C2（T001）に1、C3（T002）に2が付き、ほかの行は空白になります。
続いて、一覧を作る列に式を入れます。
| 列 | 見出し | 2行目の式 |
|---|---|---|
| E | 重複している値 | =IFERROR(INDEX($A$2:$A$10,MATCH(ROWS($E$2:E2),$C$2:$C$10,0)),"") |
| F | 出現回数 | =IF(E2="","",COUNTIF($A$2:$A$10,E2)) |
E2とF2は、重複している値の数より多めに下へコピーします。ROWS($E$2:E2) はコピー先で1、2、3…と増えていき、INDEXがその番号の付いた行の値を取り出します。番号が尽きた行は空白になります。
一覧を関数で作る必要がなければ、B列とC列を作ったところでオートフィルターをかけ、C列が空白でない行だけを表示してA列とB列をコピーしても同じ結果になります。
複数列の組み合わせで重複を探す場合
「日付と伝票番号の両方が同じ行」のように複数列で判定するときは、COUNTIFの代わりにCOUNTIFSを使います。ここではA列に日付、B列に伝票番号があるとします。
FILTERが使える場合は、D2に次の式を入れると、重複している組み合わせが2列で出ます。
=UNIQUE(FILTER(A2:B10,(COUNTIFS(A2:A10,A2:A10,B2:B10,B2:B10)>=2)*(B2:B10<>""),"重複なし"))
出現回数はF2に次の式で出します。重複が1件もなく、D2が「重複なし」のときはエラーになります。
=COUNTIFS(A2:A10,INDEX(D2#,0,1),B2:B10,INDEX(D2#,0,2))
補助列を使う場合は、方法2の式のCOUNTIFをCOUNTIFSに置き換え、列ごとに条件を並べます。たとえばC列の「初めて出てきた行」の判定は、COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1 になります。
COUNTIFで数え間違えやすい値
COUNTIFは、見た目どおりに比べない場面があります。出現回数がおかしいと感じたら、次の点を確認してください。
| ケース | COUNTIFの扱い | 対処の方向 |
|---|---|---|
| 大文字と小文字（abc と ABC） | 同じ値として数える | 区別したい場合はEXACTを使う（下記） |
| 前後の空白、全角と半角 | 別の値として数える | 見た目が同じでも別扱いになる。元データの表記をそろえてから数える |
* ? ~ を含む値 |
ワイルドカードとして解釈され、ほかの値まで数えることがある | = で比べる式に切り替える（下記） |
| 数字だけの文字列（001 と 1） | 同じ値として数えることがある | = で比べる式に切り替える |
| 16桁以上の数字の文字列（カード番号など） | 先頭15桁が同じなら同じ値として数えることがある | = またはEXACTで比べる |
| 255文字を超える文字列 | 正しく数えられず #VALUE! になる | = で比べる式に切り替える |
= で比べると、ワイルドカードや数値への読み替えをせずに比較できます。方法2のB列を次のように書き換えます。
=IF(A2="","",SUMPRODUCT(--($A$2:$A$10=A2)))
大文字と小文字も区別したい場合は、EXACTを使います。
=IF(A2="","",SUMPRODUCT(--EXACT($A$2:$A$10,A2)))
C列の COUNTIF($A$2:A2,A2)=1 やF列のCOUNTIFも、同じ考え方で SUMPRODUCT(--($A$2:A2=A2))=1、SUMPRODUCT(--($A$2:$A$10=E2)) に置き換えて、比べ方をそろえます。UNIQUEも大文字と小文字を区別しないので、区別が必要なときは補助列の方法を使ってください。
結果を確かめる
一覧ができたら、次の3点で数が合っているか確かめます。
- 出現回数の合計：D列（方法2ではF列）の合計が、重複に関わる行数と一致するかを見ます。FILTER版なら、次の2つの式が同じ値になります。例の表ではどちらも5です。
=SUM(D2#)=SUMPRODUCT((COUNTIF(A2:A10,A2:A10)>=2)*(A2:A10<>""))
- 余分な行数：
=SUM(D2#)-ROWS(C2#)で、1件ずつ残したときに減る行数が出ます。例の表では 5−2=3 です。シートのコピーで「重複の削除」を実行し、表示される削除件数と比べれば突き合わせになります。空白行があったり、上の表のような表記の違いがあったりすると、差が出ることがあります。 - 抜き取り確認：一覧から値を1つ選び、元の表をその値でフィルターします。表示された行数が出現回数と同じかを確かめます。
エラーが出たときの目安は次のとおりです。
- #SPILL!：結果が広がる先のセルに何か入っています。C列・D列の下を空けてください。
- #CALC!：FILTERの結果が0件で、第3引数を指定していません。
- #NAME?：FILTER・UNIQUE・HSTACKなどが使えないバージョンです。方法2を使ってください。
