【Excel】エクセルでユニーク数をカウントする方法（重複を除いた件数）

Excelで商品名、担当者名、顧客IDなどの一覧を扱っていると、同じ値が何回も入力されている中から、重複を除いた件数だけを数えたい場面があります。

このときに必要となるのが、データに含まれる異なる値の個数を求めるユニーク数のカウントです。

Microsoft 365やExcel 2021以降ならUNIQUE関数とCOUNTA関数を組み合わせる方法がわかりやすく、古いバージョンでもSUMPRODUCT関数やピボットテーブルを使って対応できます。

なお、ユニーク数はデータの件数そのものではなく、同じ内容を一つとして扱った件数です。

たとえば「東京、東京、大阪、名古屋」という4件のデータなら、ユニーク数は「東京、大阪、名古屋」の3件になります。

ここではサンプルデータの1行目に見出しがある前提で、重複を除いた件数を正確に求める数式と操作を詳しく確認していきましょう。

 

UNIQUE関数とCOUNTA関数によるユニーク数の集計

それではまず、Excel 365やExcel 2021で利用できる、UNIQUE関数とCOUNTA関数を使った基本的な数え方について解説していきます。

A	B
1	担当者
2	田中
3	佐藤
4	田中
5	鈴木

 

基本数式の入力

まずは、ユニーク数を表示したい空白セルを選択し、数式を入力します。

この数式では、最初にUNIQUE関数がA2からA10までの範囲から重複しない値を取り出します。

続いてCOUNTA関数が、取り出された値の個数を数える仕組みです。

たとえば範囲内に「田中、佐藤、田中、鈴木、佐藤」がある場合、UNIQUE関数の結果は「田中、佐藤、鈴木」となり、数式の結果は3です。

UNIQUE関数は重複した文字列や数値を一つにまとめて返す関数なので、名簿や商品リストの整理にも役立ちます。

数式を入力した後にEnterキーを押せば、対象範囲内のユニーク数がセルに表示されます。

 

スピルによる重複なし一覧

UNIQUE関数を単独で入力すると、ユニークな値の一覧を確認できます。

この数式を空いているセルに入力すると、結果が下方向へ自動的に展開されます。

この自動展開はスピルと呼ばれるExcelの機能です。

一覧として表示された値を目で確認できるため、件数だけでなく、どの担当者や商品が存在するかも同時に把握できます。

展開先のセルに文字や数式が入っていると、スピルエラーになる場合があります。

その場合は、結果が広がる予定のセルを空欄にしてから、もう一度数式を確定しましょう。

 

対象範囲の指定

数式でユニーク数を求める際は、見出し行を含めず、実際のデータが始まる2行目から範囲を指定します。

今回の例ではA1が「担当者」というヘッダーなので、対象範囲はA2:A10です。

もしA1を含めてしまうと、「担当者」という文字も一つのユニーク値として数えられ、結果が1件多くなります。

サンプルデータにヘッダーがある場合、数式の開始セルは必ず2行目にすることが基本です。

データ件数が増える可能性があるなら、A2:A1000のように余裕を持った範囲を設定する方法もあります。

ただし空白セルをどう扱うかによって結果が変わるため、その点は次の見出しで確認します。

 

空白セルを除外するユニーク数の計算

続いては、データ範囲に空白セルが含まれる場合に、空白をユニーク数へ含めない方法を確認していきます。

A	B
1	商品名
2	ノート
3
4	ペン
5	ノート

 

FILTER関数を組み合わせた数式

空白を除いたユニーク数を求めるには、FILTER関数を組み合わせます。

FILTER関数は、指定した条件に合うデータだけを取り出す関数です。

数式内のA2:A10<>””は、A2からA10までの各セルが空白ではないかを判定しています。

つまり、空欄以外のデータだけをFILTER関数で抽出し、その結果からUNIQUE関数が重複を除外し、最後にCOUNTA関数が個数を数えます。

空白を除外したいときは、UNIQUE関数の前にFILTER関数を置くと覚えると、数式の役割を整理しやすいでしょう。

 

空白が一件として数えられる原因

=COUNTA(UNIQUE(A2:A10))だけを使うと、空白セルがある場合に空白が一つの種類として扱われることがあります。

このため、画面上では商品が「ノート」と「ペン」の2種類に見えるのに、結果が3になるケースがあります。

原因は、空白そのものも重複しない値の一つとしてUNIQUE関数が返すためです。

データの途中に空欄がある表では、数式の結果と実際の種類数が一致しているかを確認する習慣が重要です。

空白の扱いを決めずに集計すると、ユニーク数がずれる可能性があります。

空欄を数に含めたい特別な運用でなければ、FILTER関数を使う方法が実務では扱いやすいでしょう。

 

見えない空白文字の確認

セルが空欄に見えても、半角スペース、全角スペース、改行などが入力されていると、Excelでは空白ではない値として認識されます。

このようなセルがあると、FILTER関数の条件A2:A10<>””では除外されず、ユニーク値として数えられます。

データを外部システムから貼り付けた場合は、目に見えない文字が混ざりやすいため注意が必要です。

不要なスペースを削除するには、TRIM関数や置換機能を使う方法があります。

TRIM関数は、文字列の前後にある余分な半角スペースを整える際に役立ちます。

ユニーク数が合わないときは、表示内容だけでなくセル内の文字も確認することが大切です。

 

条件付きでユニーク数を求める数式

続いては、特定の部署、月、担当者などの条件に一致するデータだけから、重複を除いた件数を求める方法を確認していきます。

A	B	C
1	部署	顧客名
2	営業	A社
3	営業	B社
4	総務	A社

 

条件に一致するデータの抽出

営業部だけの顧客数のように、条件を付けてユニーク数を数える場合は、FILTER関数の条件部分を変更します。

この数式では、A列が「営業」である行だけから、B列の顧客名を取り出します。

その後、UNIQUE関数で同じ顧客名を一つにまとめ、COUNTA関数で件数を表示します。

営業部でA社に複数回訪問していても、顧客としてはA社を1件として数えられます。

抽出する列と条件を判定する列は別々に指定できるため、売上表や問い合わせ管理表でも応用可能です。

赤枠で示したように、条件列と集計列の行数は同じにそろえる必要があります。

 

セル参照を利用した条件指定

数式に「営業」と直接入力する代わりに、条件を入力するセルを用意して参照する方法も便利です。

この例では、E2セルに「営業」と入力しておけば、E2の内容に応じたユニーク数が表示されます。

E2を「総務」に書き換えるだけで、総務部の顧客ユニーク数へ切り替わります。

条件をセル参照にすると、数式を書き換えずに集計条件を変更できます。

部署名、担当者名、年月などを選択する集計表を作る場合にも適した方法です。

 

複数条件による集計

部署と月の両方など、複数の条件を満たすデータからユニーク数を出すこともできます。

この数式では、A列がF2セルの条件と一致し、かつB列がG2セルの条件と一致する行だけを抽出します。

数式中のアスタリスクは、複数条件を同時に満たすという意味で使われています。

たとえば部署別かつ月別の購入顧客数、担当者別かつ商品別の取引先数を調べる場合に活用できます。

複数条件のユニーク数は、FILTER関数の条件を掛け合わせて作成します。

該当データが一件もない場合のエラー表示を避けたいときは、IFERROR関数で数式全体を囲む方法もあります。

 

旧バージョンで使えるSUMPRODUCT関数

続いては、UNIQUE関数やFILTER関数が使えないExcel 2019以前などで、重複を除いた件数を数える方法を確認していきます。

A	B
1	商品コード
2	P001
3	P002
4	P001

 

基本となるSUMPRODUCT数式

旧バージョンで空白を含まないデータのユニーク数を求める場合は、次の数式を使用します。

COUNTIF関数は、それぞれの値が範囲内に何回出現するかを数えます。

たとえばP001が2回ある場合、各P001に対する計算結果は2分の1です。

P001の2セル分を合計すると1となり、重複している値は全体として1件だけ数えられます。

SUMPRODUCT関数がそれぞれの計算結果を合計することで、ユニーク数が求められる仕組みです。

SUMPRODUCTとCOUNTIFの組み合わせは、古いExcelでの代表的な重複除外集計です。

 

空白を除外する旧バージョンの数式

空白セルも含まれるデータでは、次のように条件を追加します。

A2:A10<>””の部分が、空欄ではないセルだけを計算対象にする条件です。

COUNTIF関数内でA2:A10&””とすることで、文字列として安定して判定しやすくなります。

数式が長く見える場合でも、前半が空白除外、後半が重複回数による割り算と考えると整理できます。

旧バージョンでは空白除外の条件を明示することが特に重要です。

 

数値と文字列の表記ゆれ

商品コードや社員番号では、数字に見えても文字列として保存されているデータが混在する場合があります。

たとえば「001」と数値の1は、表示が似ていてもExcelでは別の値として扱われることがあります。

また「東京」と「東京 」のように末尾へスペースが付いたデータも、別のユニーク値です。

集計前に表示形式、スペース、全角と半角の混在を確認すると、想定外の件数を防げます。

ユニーク数の正確さは、数式だけでなく元データの統一にも左右されます。

データを整える作業は少し手間でも、後の集計ミスを減らす大切な工程です。

 

ピボットテーブルによる重複なしデータの確認

続いては、数式を使わずに重複しない項目の種類を確認したい場合に便利な、ピボットテーブルの活用方法を確認していきます。

A	B
1	都道府県
2	東京都
3	大阪府
4	東京都

 

行エリアへの項目配置

元データの範囲を選択してから、挿入タブのピボットテーブルを選択します。

作成先を指定した後、フィールド一覧から集計したい項目を行エリアへドラッグします。

すると、同じ都道府県や担当者名は一つにまとめられ、重複なしの一覧が表示されます。

ピボットテーブルの行ラベルは、ユニークな値の確認に使いやすい機能です。

一覧の行数を見れば、おおよそのユニーク数も把握できます。

 

値エリアでの件数集計

項目を値エリアにも配置すると、それぞれの値が何回出現しているかを確認できます。

たとえば都道府県を行エリアと値エリアの両方に置けば、東京都が何件、大阪府が何件という内訳が表示されます。

単なるユニーク数だけでなく、重複の頻度も見たいときに便利です。

項目ごとの出現回数を確認すると、入力ミスや特定データへの偏りにも気付きやすくなります。

一覧と件数を同時に見たい場合は、ピボットテーブルが有効です。

 

更新操作と元データの追加

ピボットテーブルは、元データに行を追加しても自動では更新されない場合があります。

新しいデータを反映したいときは、ピボットテーブル内を右クリックして更新を実行します。

ただし、最初に指定した元データ範囲の外へ追加した行は、更新しても含まれないことがあります。

データをテーブル形式に変換しておくと、行の追加に対応しやすくなります。

ピボットテーブルの集計結果は、更新と元データ範囲の両方を確認することが重要です。

 

まとめ エクセルで重複を除いたユニーク数をカウントする方法

Excelで重複を除いた件数を求めるには、利用しているExcelのバージョンと、空白を含めるかどうかを確認して方法を選ぶことが大切です。

Excel 365やExcel 2021では、=COUNTA(UNIQUE(A2:A10))を使うと、基本的なユニーク数を簡単に求められます。

空白セルを数に含めたくない場合は、=COUNTA(UNIQUE(FILTER(A2:A10,A2:A10<>””)))を使うことで、空欄を除外した正確な件数を表示できます。

条件付きの集計では、FILTER関数の条件部分を活用し、部署別や期間別のユニーク数を計算できます。

UNIQUE関数が利用できない旧バージョンでは、SUMPRODUCT関数とCOUNTIF関数の組み合わせが選択肢になります。

また、ユニークな項目の一覧と各項目の出現回数を確認したいときは、ピボットテーブルも便利です。

ユニーク数の計算では、見出し行を含めないこと、空白やスペースを確認すること、表記を統一することが正確な集計の基本になります。

目的に合った数式や集計機能を使い分けて、重複の多いExcelデータを効率よく整理していきましょう。