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

Excelで顧客名、商品名、担当者名などの一覧を集計するとき、同じデータが何度も入力されていることがあります。

単純な件数ではなく、重複を除いたユニーク数を数えたい場面では、COUNTIF関数だけでは対応しにくいケースもあります。

本記事では、Excelで重複を除いてカウントする基本的な数式から、複数条件を指定する方法、Microsoft 365で使えるUNIQUE関数までを解説します。

サンプルデータは1行目を見出し行として、2行目からデータが入力されている前提で説明します。

 

エクセルで重複を除いてカウントする方法1【UNIQUE関数とCOUNTA関数】

それではまず、Microsoft 365やExcel 2021以降で使いやすいUNIQUE関数によるユニーク数の数え方について解説していきます。

行	A列　顧客名	B列　商品	C列　担当者
1	顧客名	商品	担当者
2	青木商事	ノート	田中
3	藤田工業	ペン	佐藤
4	青木商事	ノート	田中
5	森田商店	ファイル	田中
6	藤田工業	ペン	鈴木

この表ではA2からA6に5件の入力がありますが、顧客名の重複を除くと青木商事、藤田工業、森田商店の3社です。

 

UNIQUE関数で重複しない値を取り出す手順

UNIQUE関数は、指定した範囲から重複した値を取り除き、異なる値だけを一覧として返す関数です。

たとえばE2セルに次の式を入力すると、A2からA6の顧客名から重複しない会社名がE列へ自動的に展開されます。

結果は青木商事、藤田工業、森田商店の3件になります。

UNIQUE関数の結果はスピル機能で複数セルに広がるため、数式を入力するセルの下や右には空白を確保します。

データを追加したときに対象範囲も自動で広げたい場合は、元データをテーブル化して列名を参照する方法が便利です。

テーブル名を売上一覧、顧客名の列名を顧客名にした場合は、=UNIQUE(売上一覧[顧客名])のように指定できます。

この方法なら、表の最終行に新しい顧客を追加したときも、重複しない顧客の一覧が更新されます。

 

COUNTA関数でユニーク数を数える数式

重複を除いた一覧を別の場所に表示する必要がなく、件数だけを求めたい場合はCOUNTA関数とUNIQUE関数を組み合わせます。

たとえば、顧客名のユニーク数を求める式は次のとおりです。

UNIQUE(A2:A6)が重複しない3つの顧客名を返し、COUNTA関数がその個数を数えるため、結果は3になります。

COUNTA関数は空白ではないセルを数えるため、空文字列を返す数式が含まれる範囲では結果を確認する必要があります。

完全に空白のセルが混じるだけなら、通常はこの式で問題なくユニーク数を集計できます。

ただし、空白そのものを1種類の値として数えたくないときは、次の見出しで紹介するFILTER関数との組み合わせが適しています。

 

空白を除外してユニーク数を求める式

入力途中の表では、A列に空白行が含まれることがあります。

この場合、空白をユニークな1件として数えないようにするには、FILTER関数で空白以外の値だけを取り出してからUNIQUE関数へ渡します。

FILTER(A2:A100,A2:A100<>””)は、A2からA100のうち空白ではないセルだけを抽出する部分です。

その抽出結果からUNIQUE関数が重複を除き、COUNTA関数が件数を返します。

範囲をA2:A100のように余裕を持たせる場合でも、空白除外の条件を入れておけば未入力行が集計に影響しません。

数式を結果表示用のセルに入力し、元データと重ならない位置に置くことも大切です。

 

COUNTIF関数を使った重複なしカウント

続いては、UNIQUE関数が使えないExcelでも利用できるCOUNTIF関数を使った重複なしカウントを確認していきます。

行	A列　顧客名	B列　初回判定
1	顧客名	初回判定
2	青木商事	1
3	藤田工業	1
4	青木商事	0
5	森田商店	1
6	藤田工業	0

COUNTIF関数では、各データが範囲内で何回目に登場したかを判定し、初めて出現した行だけを数える考え方を使います。

 

先頭行からの累積範囲を指定する考え方

B2セルに次の数式を入力すると、A2の顧客名が最初に出現したデータかどうかを判定できます。

$A$2:A2は、先頭のA2セルを固定しながら、数式を下へコピーするごとに終点だけを広げる累積範囲です。

A2ではCOUNTIF($A$2:A2,A2)が1となるため、B2には1が表示されます。

A4の青木商事では、$A$2:A4の中に青木商事が2件あるため、結果は0です。

絶対参照と相対参照を混ぜた$A$2:A2という指定が、初回だけを判定するための要点です。

B2の右下にあるフィルハンドルを下方向へドラッグすると、各行で初回判定を作成できます。

 

SUM関数で初回判定の合計を集計する方法

初回のデータに1、重複データに0が入ったら、B列を合計することでユニーク数を求められます。

たとえばB2からB6までに初回判定を作成した場合、任意のセルに次の式を入力します。

青木商事、藤田工業、森田商店がそれぞれ最初に登場した3行だけが1になるため、合計結果は3です。

この方法は途中経過が目で確認できるため、数式に慣れていない場合でも集計ミスを発見しやすいでしょう。

重複の有無を可視化したい表では、ユニーク数だけを求めるより補助列を使う方法が実務向きです。

データを追加したら、B列の式も新しい行までコピーして、SUM関数の範囲を見直します。

元データをテーブルに変換しておくと、数式を新規行へ自動コピーしやすくなります。

 

空白セルを数えないための条件追加

A列に空白がある場合、基本式では最初の空白セルが1と判定されることがあります。

空白をユニーク数に含めない場合は、IF関数の先頭に空白確認を加えます。

日本語版Excelの設定によっては引数の区切り記号がカンマの場合もあるため、その環境ではセミコロンをカンマへ置き換えます。

一般的なカンマ区切りの式では、=IF(A2=””,0,IF(COUNTIF($A$2:A2,A2)=1,1,0))です。

空白を除外する条件は、データの件数が増えるほど集計の信頼性を高めます。

 

複数条件でユニーク数を数える数式

続いては、担当者や商品などの複数条件を指定して、重複を除いた件数を数える数式を確認していきます。

行	A列　顧客名	B列　商品	C列　担当者
1	顧客名	商品	担当者
2	青木商事	ノート	田中
3	藤田工業	ペン	佐藤
4	青木商事	ノート	田中
5	森田商店	ファイル	田中
6	藤田工業	ペン	田中

ここでは、C列が田中である行だけを対象として、A列の顧客名の重複を除いた件数を数えます。

 

FILTER関数とUNIQUE関数を組み合わせる方法

Microsoft 365またはExcel 2021以降では、FILTER関数で条件に一致する顧客名を絞り込み、その結果をUNIQUE関数へ渡す方法が分かりやすいです。

たとえばE2セルに担当者名として田中を入力している場合、次の数式を使用します。

FILTER関数はC2からC6がE2と一致する行の顧客名を抽出します。

抽出対象は青木商事、青木商事、森田商店、藤田工業となり、UNIQUE関数で青木商事、森田商店、藤田工業の3社に整理されます。

条件セルをE2のように別セルへ置けば、担当者名を書き換えるだけでユニーク数を切り替えられます。

数式の入力後は、E2の担当者名を佐藤や鈴木へ変更して、結果が正しく変わるか確認しましょう。

 

二つ以上の条件を同時に指定する方法

担当者が田中で、かつ商品がノートである顧客のユニーク数を調べる場合は、FILTER関数の条件を掛け算で結合します。

ここでE2には担当者名、F2には商品名を入力します。

数式内のアスタリスクは計算上のAND条件を表し、担当者と商品がどちらも一致する行だけが抽出されます。

田中かつノートという条件では、青木商事が重複していてもユニーク数は1です。

複数条件の各範囲は、A2:A100、B2:B100、C2:C100のように必ず同じ開始行と終了行にそろえます。

範囲の大きさが異なると、#VALUE!エラーになることがあるため注意が必要です。

 

該当データがない場合のエラー対策

指定した条件に一致するデータが1件もない場合、FILTER関数は#CALC!エラーを表示します。

集計表でエラーを見せたくない場合は、IFERROR関数で数式全体を囲み、該当なしなら0を返すように設定します。

この式では、E2に存在しない担当者名を入力しても、エラーではなく0が表示されます。

条件を入力するセルに余分な空白が入ると一致しないため、見た目が同じ文字列でも結果が0になることがあります。

入力規則のリストを使って担当者名や商品名を選択できるようにすると、表記ゆれの防止に役立ちます。

 

UNIQUE関数がないExcelで使う配列数式

続いては、Excel 2019やExcel 2016などでUNIQUE関数が利用できない場合の配列数式を確認していきます。

行	A列　顧客名	B列　商品
1	顧客名	商品
2	青木商事	ノート
3	藤田工業	ペン
4	青木商事	ノート
5	森田商店	ファイル
6	藤田工業	ペン

旧バージョンでは、SUMPRODUCT関数とCOUNTIF関数を組み合わせると、補助列を作らずにユニーク数を求められます。

 

SUMPRODUCT関数による基本の数式

A2からA6の空白以外の顧客名について、重複を除いた件数を数える代表的な式は次のとおりです。

COUNTIF(A2:A6,A2:A6&””)の部分は、各顧客名がA2:A6に何件存在するかを配列として返します。

青木商事は2件なのでそれぞれ2分の1、藤田工業も2件なのでそれぞれ2分の1、森田商店は1件なので1として計算されます。

同じ値に対応する分数を合計すると1になるため、重複があっても各顧客名は1件として数えられる仕組みです。

SUMPRODUCT関数は通常のEnterキーだけで確定できるため、古いExcelでのユニーク集計に便利です。

 

複数条件を加えたSUMPRODUCT関数

担当者が田中である行だけから、顧客名の重複を除いて数える場合は、条件式を先頭へ追加します。

COUNTIFS関数は、顧客名と担当者の組み合わせが何回出現するかを数えます。

これにより、同じ顧客でも担当者が異なる場合は別の組み合わせとして扱うことができます。

顧客名だけのユニーク数を担当者条件で絞りたい場合は、集計の目的に合わせて数式の設計を確認しましょう。

ユニークと判断する単位が顧客名なのか、顧客名と担当者の組み合わせなのかで、正しい数式は変わります。

 

配列数式で発生しやすいエラー

COUNTIF関数の範囲と条件に指定する範囲がずれていると、意図しない集計結果になります。

たとえばCOUNTIF(A2:A100,A2:A99)のように行数が異なる指定は避け、すべてA2:A100に統一します。

数値と文字列の見た目が同じでも、片方が文字列として保存されている場合は別の値と判定されることがあります。

セルの左上に緑色の三角が表示されているときは、数値が文字列になっていないか確認する場面です。

重複除外の結果が想定より多いときは、先頭や末尾の空白、全角と半角、数値の文字列化を疑いましょう。

 

ピボットテーブルによる重複を除く集計

続いては、数式をセルへ入力せず、ピボットテーブルでユニーク数を集計する方法を確認していきます。

行	A列　顧客名	B列　商品	C列　担当者
1	顧客名	商品	担当者
2	青木商事	ノート	田中
3	藤田工業	ペン	佐藤
4	青木商事	ノート	田中
5	森田商店	ファイル	田中

ピボットテーブルでは、集計対象を更新しながら担当者別や商品別の状況を確認したいときに便利です。

 

データモデルを追加してピボットテーブルを作成する手順

ユニーク数を集計するには、ピボットテーブル作成時にデータモデルへ追加する必要があります。

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

表示されるダイアログでテーブルまたは範囲を確認し、データモデルにこのデータを追加する項目へチェックを入れます。

その後、新規ワークシートまたは既存ワークシートを選択して作成します。

データモデルへ追加せずに作成したピボットテーブルでは、集計方法に重複しない数が表示されない場合があります。

 

値フィールドの設定で重複しない数を選ぶ方法

作成したピボットテーブルのフィールド一覧で、顧客名を値エリアへドラッグします。

値エリアに追加された顧客名をクリックし、値フィールドの設定を開きます。

集計方法の一覧から重複しない数を選択してOKをクリックすると、同一顧客を1件として数える結果になります。

担当者を行エリアやフィルターエリアに配置すれば、担当者別のユニーク顧客数も確認できます。

ピボットテーブルでは、元データの列見出しに空白や重複がない状態で作成することが基本です。

 

元データを更新した後の注意点

ピボットテーブルは、元データに新しい行を追加しても自動で表示内容が更新されるわけではありません。

ピボットテーブル内を右クリックして更新を選択するか、データタブのすべて更新を実行します。

元データをテーブル形式にしておくと、追加行をピボットテーブルの対象範囲へ含めやすくなります。

データ量が多い場合は、数式を大量の行へコピーするよりも、ピボットテーブルのほうが管理しやすいことがあります。

 

ユニーク数が合わないときの確認項目

続いては、重複を除いたカウント結果が想定と合わないときの確認項目を解説していきます。

確認対象	よくある状態	確認方法
空白セル	空白が1件として数えられる	空白除外条件を加える
余分な空白	同じ名称が別データになる	LEN関数で文字数を確認する
表記ゆれ	全角半角や法人表記が異なる	入力規則や置換を利用する
数値形式	数値と文字列が混在する	表示形式と左上のエラーを確認する

ユニーク数の数式が正しくても、元データの表記が統一されていなければ、集計結果は期待どおりになりません。

 

前後の空白と不可視文字の確認

青木商事と青木商事の後ろに空白が入った文字列は、見た目がほぼ同じでもExcelでは別の値です。

文字数を調べるには、隣のセルへ=LEN(A2)と入力して確認できます。

不要な前後空白を取り除く場合は、TRIM関数を利用します。

Webサイトや他システムからコピーしたデータでは、通常のスペース以外の文字が混入することもあります。

元データを整形してからユニーク数を数えると、後から集計条件を見直す手間を減らせます。

 

文字列の数値と数値の混在

001と1を同じコードとして扱うか、別のコードとして扱うかは、業務上のルールによって異なります。

商品コードや社員番号の先頭ゼロに意味がある場合は、対象列を文字列として統一する必要があります。

一方で金額や数量のように数値として扱う項目では、文字列形式の数値を数値に変換しておくと、並べ替えや計算でも問題が起こりにくくなります。

セルの表示形式だけではデータ型が変わらないため、必要に応じて値の変換を行いましょう。

ユニーク数の違いは数式の誤りではなく、同じように見えるデータの型違いが原因であることも少なくありません。

 

対象範囲と見出し行の確認

数式の範囲に1行目の見出しを含めると、見出し文字も1件として数えられます。

サンプルのように1行目がヘッダーの場合、集計範囲はA2:A100のように2行目から始めます。

また、新しいデータ行を追加したにもかかわらず範囲がA2:A6のままであれば、追加分は集計されません。

固定範囲を使う場合は定期的に終点を確認し、可能ならExcelテーブル化して構造化参照を利用します。

 

まとめ　エクセルでユニーク数を複数条件でカウントする方法

Excelで重複を除いてカウントする場合、Microsoft 365やExcel 2021以降であれば、UNIQUE関数とCOUNTA関数の組み合わせが分かりやすい方法です。

=COUNTA(UNIQUE(A2:A6))を使うと、指定範囲にある重複しない値の件数を求められます。

空白セルを含めたくない場合は、FILTER関数を組み合わせて空白以外を抽出してから数えます。

担当者や商品などの条件を指定する場合は、FILTER関数の条件に一致するデータだけを抽出し、その結果をUNIQUE関数で整理します。

UNIQUE関数が使えない旧バージョンのExcelでは、COUNTIF関数で初回出現を判定する補助列を作る方法や、SUMPRODUCT関数を使う方法が役立ちます。

大量データを切り替えながら分析するなら、データモデルを追加したピボットテーブルで重複しない数を集計する選択肢もあります。

最後に、ユニーク数が合わないときは、数式だけでなく空白、表記ゆれ、数値と文字列の混在、対象範囲を確認することが重要です。

目的に合う方法を選び、正確な重複除外集計へつなげていきましょう。