Excelで列の重複値をカウントする方法｜COUNTIF関数の使い方

Excelで列全体にわたる重複データの数を把握しておくと、さまざまな場面で役立ちます。例えば、意図しない重複入力がないかをチェックしたい場合や、重複自体は想定内でも、それぞれの値が何回出現しているのかを正確に知りたい場合などです。この記事では、Excelである列内の重複値をカウントする方法を、具体例とともにわかりやすく解説します。

Excelで列内の重複値をカウントする基本

この作業にはCOUNTIF関数を使用します。アプローチは大きく2つあり、1つは「各値が合計で何回重複しているか(出現頻度)」を数える方法、もう1つは「その値が何回目の出現か(出現順序)」を数える方法です。

方法1:重複値の出現頻度をカウントする

選択した範囲の中で、特定の値が合計何回繰り返されているかを知りたいケースを考えてみましょう。単純に各値の重複回数だけを求めたい場合は、次の数式を使用します。

=COUNTIF($<先頭セルの列>$<先頭セルの行>:$<先頭セルの列>$<末尾セルの行>,<先頭セル番号>)

各要素の意味は以下の通りです。

• <先頭セルの列>:値のリストが入力されている列
• <先頭セルの行>:値が入力されている最初のセルの行番号
• <末尾セルの行>:値が入力されている最後のセルの行番号
• <先頭セル番号>:列内で値が入力されている最初のセルのセル番号

例えば、B列のB3からB11までにデータのリストがあり、C列のC3からC11にそれぞれの値の重複回数を表示したい場合、数式は次のようになります。

=COUNTIF($B$3:$B$11,B3)

この数式をセルC3に入力します。入力後、一度別のセルをクリックしてからC3を再び選択すると、セルの右下に小さな点(フィルハンドル)が表示されます。これをクリックしたままC11までドラッグすると、数式が自動的にコピーされ、各行の値の重複回数が一括で計算されます。

方法2:重複の出現順序をカウントする

方法1とは異なり、同じ値が出現するたびにカウントが増えていく形式(1回目なら「1」、2回目なら「2」…)が必要な場合は、次の数式を使います。

=COUNTIF($<先頭セルの列>$<先頭セルの行>:$<先頭セル番号>,<先頭セル番号>)

各要素の意味は以下の通りです。

• <先頭セルの列>:値のリストが入力されている列
• <先頭セルの行>:値が入力されている最初のセルの行番号
• <先頭セル番号>:列内で値が入力されている最初のセルのセル番号

先ほどの例と同様に、B3からB11にデータがあり、C列に出現回数を表示する場合、数式は以下のようになります。

=COUNTIF($B$3:$B3,B3)

この数式をセルC3に入力し、フィルハンドルを使ってC11まで数式をコピーします。

ポイントは、範囲指定の終点(B3部分)を絶対参照にしないことです。こうすることで、数式を下にコピーするたびに集計範囲が1行ずつ拡張され、最初の出現は「1」、2回目の出現は「2」というように、出現のたびにカウントが増加していきます。

まとめ

COUNTIF関数と絶対参照・相対参照を組み合わせれば、重複データの「出現頻度」も「出現順序」も簡単にカウントできます。データのクレンジングや重複チェック、集計作業の効率化にぜひ活用してみてください。

あわせて読みたい:Excelでセルを結合・結合解除する方法