同じ値が上から数えて何回目かは、=COUNTIF($A$2:A2,A2) を入れて下へコピーすると出せます。範囲の始まり（$A$2）だけを固定するので、コピーするたびに範囲が 1 行ずつ広がり、「その行までに何回出たか」が数えられます。
この記事では、商品名の列を例に、何回目かを数える式、2 回目以降に「重複」と表示する式、同じ値ごとの連番、全体の出現回数との違いを、実際の Excel で確かめた結果で説明します。
何回目の出現かを数える式
=COUNTIF($A$2:A2, A2)| 部品 | 意味 |
|---|---|
| $A$2 | 範囲の始まり。$ を付けて固定する（コピーしても動かない） |
| A2（範囲の終わり） | $ を付けない。下へコピーすると A3・A4…と 1 行ずつ広がる |
| A2（数える値） | その行の値。範囲の中に同じ値がいくつあるかを数える |
3 行下へコピーした式は =COUNTIF($A$2:A5,A5) になります。「先頭からその行まで」の範囲で数えるので、1 回目は 1、2 回目は 2 になります。COUNTIF の基本は COUNTIF関数の使い方で説明しています。
1. 上から数えて何回目かを出す
I2 に式を入れて、下へコピーします。範囲の始まりだけ $ で固定するのがポイントです。
=COUNTIF($A$2:A2,A2)
上から 1 / 1 / 2 / 1 / 2 / 3 / 1 になりました。「りんご」は 2 行目が 1 回目、4 行目が 2 回目、7 行目が 3 回目です。
2. 2回目以降に「重複」と表示する
何回目かが 1 より大きい行は、同じ値が上にすでにあるということです。IF で「重複」と表示します。
=IF(COUNTIF($A$2:A2,A2)>1,"重複","")
4 行目・6 行目・7 行目に「重複」と出ました。1 回目の行は空白のままなので、「重複」の行だけを消せば 1 件ずつ残せます。重複の探し方と消し方は 重複を削除・チェックする方法でまとめています。
3. 同じ値ごとの連番を付ける（りんご-1・りんご-2）
何回目かの数字は、そのまま「グループごとの連番」になります。値と & でつなぐと、同じ値どうしを区別できる番号になります。
=A2&"-"&COUNTIF($A$2:A2,A2)
「りんご-1」「りんご-2」「りんご-3」のように、同じ商品ごとに 1 から番号が付きました。上から順に 1・2・3 と振るふつうの連番は 連番を振る方法で説明しています。
4. 全体の出現回数との違い
範囲の終わりにも $ を付けて $A$2:$A$8 と固定すると、どの行でも表全体から数えます。こちらは「全部で何回あるか」です。
=COUNTIF($A$2:$A$8,A2)
上から 3 / 2 / 3 / 1 / 2 / 3 / 1 になりました。「りんご」の行はどれも 3 です。1 の式は 1・2・3 と増えるのに対し、こちらは同じ値なら同じ数になります。「2 回以上ある値すべて」に印を付けたいときはこちら、「2 回目以降だけ」に印を付けたいときは 1 の式を使います。
【実機で検証】何回目かを数えるときにつまずくところ
| ためしたこと | 式 | 結果 |
|---|---|---|
| 大文字・小文字がちがう 3 つ（A12:A14）から「apple」を数える | =COUNTIF(A12:A14,"apple") | 3 |
| EXACT で大文字・小文字を区別して数える | =SUMPRODUCT(EXACT(A12:A14,"apple")*1) | 1 |
| 後ろに空白のある「apple 」（A15）も入れて数える | =COUNTIF(A12:A15,"apple") | 3 |
| TRIM で前後の空白を取ってから数える | =SUMPRODUCT((TRIM(A12:A15)="apple")*1) | 4 |
| $ なしの式を 4 行目までコピーした形（上の表） | =COUNTIF(何回目!A4:A4,何回目!A4) | 1 |
| $ ありの式の 4 行目（上の表） | =COUNTIF(何回目!$A$2:A4,何回目!A4) | 2 |
COUNTIF は大文字・小文字を区別しません。「apple」「Apple」「APPLE」の 3 つから「apple」を数えると 3 になりました。区別して数えたいときは EXACT 関数を使うと 1 になります。
後ろに空白のある「apple 」は別の値として扱われ、4 つの中から数えても 3 のままでした。見た目が同じなのに 2 回目にならないときは、前後の空白を疑います。TRIM で空白を取ってからくらべると 4 になりました。
範囲の始まりに $ を付けずにコピーすると、範囲ごと下へずれます。4 行目の式は =COUNTIF(A4:A4,A4) になり、2 回目の「りんご」なのに 1 でした。$ を付けた式は 2 です。
よくある質問
2回目以降の行に色を付けたいときは？
条件付き書式の数式に =COUNTIF($A$2:$A2,$A2)>1 を入れます。重複のチェックと色付けは 重複を削除・チェックする方法で説明しています。
2つの列の組み合わせで何回目かを数えたいときは？
COUNTIFS で条件を 2 つ並べます。=COUNTIFS($A$2:A2,A2,$B$2:B2,B2) のように、どちらの範囲も始まりだけ固定します。くわしくは 複数列の重複をチェックする方法をどうぞ。
重複した行をまとめて消したいときは？
「データ」タブの「重複の削除」を使います。重複を削除する方法で説明しています。
