重複を除いた件数を数える

エクセルの列で重複を除いた件数を数える方法

1. ファイルを置く. シートが複数ある .xlsx ならシートを選べます。CSV は文字コードと区切り文字を自動で判定します。
2. 列を選ぶ. 見出しの名前が一覧に出ます。見出しが無いときは A・B・C の列記号で選びます。
3. 件数を読む. 最初の数字が重複を除いた値の数です。空のセルは数えず、件数を別に表示します。
4. 出現回数の表を読む. 値ごとの回数と割合が多い順に並びます。たいていの場合、知りたかったのはこちらです。

よく紹介される数式と、それぞれがつまずく所

• =COUNTA(UNIQUE(A2:A1000)) はいちばん新しい方法で、UNIQUE 関数が必要です。使えるのは Microsoft 365 と Excel 2021 以降で、Excel 2019 以前では #NAME? になります。
• =SUMPRODUCT(1/COUNTIF(A2:A1000,A2:A1000)) はどの版でも動きますが、範囲に空のセルが 1 つでもあると 0 で割ってエラーになります。そして範囲にはたいてい空のセルがあります。
• ピボットテーブルは値ごとの件数を出せますが、「重複しない値の個数」を出すにはデータモデルに追加する必要があり、ダイアログを何段も進みます。

どれもデータが増えるたびに範囲を直す必要があります。ファイルをここに置けばそのどれも要らず、出現回数の表も一度に手に入ります。

全角と半角は「別の値」として数えます

日本語の表で件数が思ったより多いとき、原因はたいてい表記の揺れです。このページは値を文字どおりに比べるので、 全角の ＡＢＣ と半角の ABC、半角カナの ｶﾌﾞｼｷｶﾞｲｼｬ と全角の カブシキガイシャ、株式会社 と (株) は、それぞれ別の値になります。 出現回数の表を値の順に並べると、こうした揺れが隣り合って見つけやすくなります。 全角と半角を同じものとして数えたいなら、重複を探すの「全角と半角を同じものとして扱う」を使ってください。重複した値ごとの出現回数が出ます。 株式会社 と (株) のような書き方の違いまで吸収したいなら、あいまい一致の仕事です。

「英字の大文字と小文字を区別しない」をオンにすると、Tokyo と TOKYO は 1 つの値になります。全角と半角の違いはこのチェックでは揃いません。

前後の空白と、見えない文字は落としてから数えます

値の前後の空白は、半角スペースも全角スペースも取り除いてから比べます。東京　 と 東京 は同じ値です。 コピーしたデータに紛れ込むノーブレークスペース(U+00A0)とゼロ幅スペース(U+200B)も同様です。見た目は 12 種類なのに 15 種類と出る列の原因は、たいていこれです。 値の途中にある空白はそのまま残すので、山田 太郎 と 山田太郎 は別の値です。

空のセルは数えずに、件数だけ報告します

空のセルは、重複を除いた件数にも出現回数の表にも入れず、何個あったかを別に表示します。 空を 1 つの値として黙って数えることが、同じ列について 2 つのツールの答えが食い違う典型的な原因だからです。

出現回数の表の使い道

表は CSV か .xlsx でダウンロードでき、Markdown としてコピーしてチケットに貼ることもできます(表の見出しは「件数」「割合」)。 回数ではなく、重複している行そのものを見たいなら別の問いです —— 重複を探すが行を一覧にし、重複に色を付けるがその場で色を付けます。