Excelで一覧表を扱うと、同じ商品コードや氏名、受付番号が複数回入力されていることがあります。
ただし、単純に重複を数えるだけでは、空白セルや数値の0、フィルターで隠れている行まで集計に含まれ、欲しい結果と合わないことが少なくありません。
この記事では、関数を使って重複をカウントしない方法を中心に、空白を除外する式、0を除外する式、非表示セルを考慮する考え方を順番に解説します。
重複をカウントしない集計で押さえたいポイントは、集計対象の範囲を決めること、空白と0を条件から外すこと、表示中の行だけを数える必要があるかを区別することです。
数式は、1行目に見出しがあり、データが2行目から始まる表を前提にしています。
エクセルで重複をカウントしない基本の数式
| 行 | A列 商品コード | B列 判定 |
|---|---|---|
| 1 | 商品コード | 重複を除く件数 |
| 2 | A-101 | 1 |
| 3 | A-101 | 0 |
| 4 | B-205 | 1 |
それではまず、同じ値が複数あっても最初の1件だけを数える基本的な方法について解説していきます。
重複をカウントしないとは、同じ値が何回入力されていても、集計結果では1件として扱うことです。
たとえばA-101が10回現れても、商品コードの種類として知りたいなら数えるのは1件です。
重複行を削除せず、数式だけで集計できる点がこの方法の大きな利点です。
COUNTIFで最初の出現だけを判定する方法
最初の出現だけを判定するには、B2セルに次の数式を入力します。
=IF(COUNTIF($A$2:A2,A2)=1,1,0)
COUNTIF関数は、指定した範囲内に条件と同じ値が何個あるかを数える関数です。
数式内の$A$2:A2は、2行目から現在の行までを表します。
A2にあるA-101がこの範囲に初めて登場した場合はCOUNTIFの結果が1となり、B2には1が表示されます。
3行目でもA-101が入力されている場合、$A$2:A3の中にはA-101が2つ存在するため、結果は0になります。
この式を下方向へコピーすれば、各値の最初の行だけを1として判定できます。
範囲の先頭である$A$2を絶対参照にすることが重要です。
先頭を固定しないと、行ごとに判定の起点がずれてしまいます。
SUMで重複を除いた件数を求める方法
判定列に1と0を作成したら、その合計を求めるだけで重複を除いた件数になります。
=SUM(B2:B100)
この例では、A列の2行目から100行目までに含まれる商品コードの種類数を求められます。
判定列を使う方法は、数式の意味を確認しやすく、後からデータを点検しやすい構成です。
どの行が最初の出現なのかをB列で確認できるため、集計ミスの原因を探すときにも役立ちます。
元データを更新した場合も、計算範囲内であれば数式の結果が自動で更新されます。
集計用の補助列を残しておくと、実務では検算がしやすくなります。
UNIQUE関数で種類数を集計する方法
Microsoft 365やExcel 2021以降では、UNIQUE関数を使って重複を除いた一覧を取り出せます。
=COUNTA(UNIQUE(A2:A100))
UNIQUE関数は、指定範囲から重複しない値だけを抽出する関数です。
COUNTA関数でその抽出結果の件数を数えると、ユニークな値の数を取得できます。
ただし、このままでは空白セルも結果に含まれることがあります。
空白を除外したい場合は、次の見出しで紹介するFILTER関数を組み合わせる方法が便利です。
【操作のポイント】補助列で確認したい場合はCOUNTIF、一覧を別の場所へ出したい場合はUNIQUEを使い分けましょう。
空白セルを除外する重複なしカウント
| 行 | A列 担当者 | 集計対象 |
|---|---|---|
| 1 | 担当者 | 空白は除外 |
| 2 | 田中 | 対象 |
| 3 | 対象外 | |
| 4 | 田中 | 重複 |
続いては、空白セルを数えずに重複だけを除外する方法を確認していきます。
空白を含む名簿や入力途中の管理表では、空欄を1種類として数えてしまうと件数がずれます。
未入力を意味する空白は、集計条件で明示的に除外することが基本です。
COUNTIFSで空白を除く補助列を作る方法
B2セルには、空白を除外した次の数式を入力します。
=IF(AND(A2<>””,COUNTIF($A$2:A2,A2)=1),1,0)
AND関数は、指定した条件がすべて満たされた場合にTRUEを返します。
A2<>””は、A2が空白ではないという意味です。
空白でなければ、続くCOUNTIF関数で最初の出現かどうかを判定します。
空白セルでは最初の条件が満たされないため、たとえ空白が複数行にあってもB列は0となります。
この方法なら、田中、佐藤、空白、田中という並びでも、数えられるのは田中と佐藤の2件です。
数式内の空文字列””は、見た目が空白のセルを判定するために使います。
UNIQUEとFILTERで空白を取り除く方法
動的配列関数を使える環境では、空白以外の値をFILTER関数で絞り込んでからUNIQUE関数へ渡せます。
=COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>””)))
FILTER関数の第1引数は抽出する範囲です。
第2引数のA2:A100<>””は、空白ではないセルだけを残す条件になります。
FILTER関数で空白を除いた結果をUNIQUE関数が重複なしの一覧にし、最後にCOUNTA関数が件数を数えます。
数式を1つのセルにまとめられるため、補助列を作りたくない場合に適しています。
一方で、FILTER関数が使えないExcelではエラーになるため、古いバージョンではCOUNTIFを使う方法が確実です。
見かけの空白とスペースを区別する考え方
セルが空白に見えても、半角スペースや全角スペース、数式で返された空文字列が入っていることがあります。
スペースだけが入っているセルは、A2<>””だけでは空白ではない値として扱われます。
半角スペースを除外したい場合は、TRIM関数を使って前後の余分な空白を取り除く方法があります。
=IF(AND(TRIM(A2)<>””,COUNTIF($A$2:A2,A2)=1),1,0)
ただし、TRIM関数は主に半角スペースを対象とするため、全角スペースや特殊な空白文字が混ざるデータでは事前の整形が必要です。
外部システムから貼り付けたデータで件数が合わないときは、見えない文字が原因かもしれません。
【操作のポイント】空欄の扱いを決めてから数式を選び、スペースだけが入ったセルも必要に応じて点検しましょう。
数値の0を除外する重複なし集計
| 行 | A列 コード | 集計対象 |
|---|---|---|
| 1 | コード | 0は除外 |
| 2 | 100 | 対象 |
| 3 | 0 | 対象外 |
| 4 | 100 | 重複 |
続いては、数値の0を対象外にした重複なし集計を確認していきます。
未設定値として0を入れている表では、0を商品番号や社員番号の一種として数えない設定が必要です。
0を除外するかどうかは、0が実データなのか未入力の代替値なのかで判断します。
空白と0を両方除外する判定式
空白と0を同時に除外したい場合は、B2セルに次の数式を入力します。
=IF(AND(A2<>””,A2<>0,COUNTIF($A$2:A2,A2)=1),1,0)
この数式では、A2が空白ではないこと、A2が0ではないこと、現在までの範囲で初登場であることの3条件を確認しています。
どれか1つでも満たされなければ、結果は0です。
100、0、100、200、空白というデータであれば、100と200だけが1として判定されます。
数値として入力された0を除外したいケースに向く式です。
一方で、文字列として入力されている0は、セルの表示形式やデータの取り込み方によって挙動が異なることがあります。
先頭にアポストロフィが付いた文字列の0は、数値の0と別物として扱われる場合があります。
FILTER関数で0以外のユニーク件数を出す方法
Microsoft 365では、FILTER関数の条件に空白以外かつ0以外という条件を指定できます。
=COUNTA(UNIQUE(FILTER(A2:A100,(A2:A100<>””)*(A2:A100<>0))))
数式内の*は、複数条件を同時に満たすという意味で使われます。
A2:A100<>””が空白以外の条件、A2:A100<>0が0以外の条件です。
2つの条件を掛け合わせることで、両方に該当するセルだけがFILTER関数の対象になります。
抽出後の値に対してUNIQUE関数を実行するため、同じコードは何回あっても1件です。
補助列が不要な反面、数式を修正するときは条件の意味を見失わないように注意しましょう。
0とゼロ表示を混同しない確認方法
Excelでは、セルに0が入っている場合と、数式の結果が0になっている場合、表示形式によって0が表示されていない場合があります。
たとえばユーザー定義表示形式で0を非表示にしていると、見た目は空欄でもセルの値は0です。
数式バーを確認すると、そのセルが本当に空白なのか、0が入っているのかを判断できます。
また、IFERROR関数などでエラーを””に置き換えているセルは、数値0ではなく空文字列です。
表示だけで判断せず、数式バーとセルの値を確認する習慣を持つと、集計のズレを防げます。
【操作のポイント】0を除外する式は、0が欠損値として使われている場合だけに適用し、正しいコードの0まで消さないようにしましょう。
非表示セルを除外する重複なしカウント
| 行 | A列 商品コード | 表示状態 |
|---|---|---|
| 1 | 商品コード | 表示状態 |
| 2 | A-101 | 表示 |
| 3 | B-205 | フィルターで非表示 |
| 4 | C-310 | 表示 |
続いては、フィルターや行の非表示を使った表で、表示中のデータだけを集計する考え方を確認していきます。
通常のCOUNTIF関数やUNIQUE関数は、非表示になった行も含めて計算します。
絞り込み後の画面で見えている行だけを数えたい場合は、SUBTOTAL関数などを組み合わせる必要があります。
フィルターで隠れた行と、手動で非表示にした行では関数番号の選択が異なります。
SUBTOTAL関数で表示行を判定する方法
まず、B2セルに表示中かどうかを判定する数式を入れます。
=SUBTOTAL(103,A2)
SUBTOTAL関数の103は、フィルターで非表示の行と手動で非表示にした行を除外して、空白ではないセルを数える指定です。
A2が表示されていて空白でなければ1、非表示なら0が返ります。
この補助列を作ることで、どの行が現在の集計対象なのかを明確にできます。
フィルターだけを除外する場合は、103ではなく3を使う方法もあります。
ただし、行を手動で隠す運用がある表では、103を使うほうが意図に合いやすいでしょう。
表示中かつ初出の値を数える数式
C2セルには、表示中であり、空白と0ではなく、さらに最初の出現であることを確認する式を入力します。
=IF(AND(B2=1,A2<>””,A2<>0,COUNTIF($A$2:A2,A2)=1),1,0)
ただし、この式だけでは、最初の出現が非表示行にあるときに問題が起きます。
たとえばA-101が非表示の3行目にあり、表示中の10行目にもA-101がある場合、10行目は初出ではないと判定されます。
表示中の行だけで完全に重複なしカウントを行うには、より複雑な配列数式やPower Queryを検討する場面もあります。
実務では、フィルター前に重複なし件数を確定するのか、フィルター後の表示結果を集計するのかを先に決めることが大切です。
非表示セルを除く集計では、重複判定の基準が全データか表示データかを明確にします。
フィルター後の表示件数を確認する画面操作
フィルターで表示中のレコードを確認するときは、表の見出しにあるドロップダウン矢印をクリックします。
画面上で非表示の行は薄く表示されるか、行番号が連続しない状態になります。
SUBTOTAL関数の結果を補助列に出しておけば、フィルターの条件を変えた直後に集計対象を確認できます。
【操作のポイント】表示中の行だけを数える前に、フィルター後のデータで初出判定をどう扱うかを確認しましょう。
COUNTIFとUNIQUEを使い分ける集計設計
| 目的 | 向く方法 | 特徴 |
|---|---|---|
| 行ごとに確認 | COUNTIF | 補助列で判定 |
| 種類一覧を作成 | UNIQUE | 自動展開 |
| 条件付き集計 | FILTERとUNIQUE | 数式を集約 |
続いては、集計の目的に応じたCOUNTIF関数とUNIQUE関数の使い分けを確認していきます。
重複を除く件数という同じ目的でも、必要な結果が件数だけなのか、対象データの一覧なのかで選ぶ関数は変わります。
補助列を使う集計のメリット
COUNTIF関数を使った補助列方式は、古いバージョンのExcelでも利用しやすい方法です。
各行に1か0が表示されるため、どの値が重複として扱われたのかを目視で確認できます。
データの入力担当者と集計担当者が異なる場合にも、判定根拠を共有しやすいでしょう。
監査や確認が必要な業務表では、見える補助列が安心材料になります。
判定列をSUM関数で合計するほか、オートフィルターで1だけを表示すれば、重複を除いた一覧も作れます。
デメリットは、元データの右側に列を追加する必要がある点です。
動的配列を使う集計のメリット
UNIQUE関数は、元の表を変更せずに別の場所へ重複なしリストを出力できます。
データが追加される可能性がある場合は、表をテーブル化して構造化参照を使うと範囲の拡張にも対応しやすくなります。
=COUNTA(UNIQUE(FILTER(Table1[商品コード],(Table1[商品コード]<>””)*(Table1[商品コード]<>0))))
この式では、テーブル名がTable1、列名が商品コードであるケースを想定しています。
テーブルの末尾に新しい行を追加すると、参照範囲も自動で広がります。
毎月データが増える一覧では、固定範囲よりテーブル参照が便利です。
データ型をそろえる前処理
見た目が同じでも、数値の100と文字列の100は別の値として扱われることがあります。
CSVファイルを読み込んだ表や、他システムからコピーした表では、数値と文字列が混在しやすいため注意が必要です。
セルの左上に緑色の三角が表示されている場合は、数値が文字列として保存されている可能性があります。
数値として統一するには、エラー表示のメニューから数値に変換する方法や、VALUE関数を使う方法があります。
文字列の前後に空白がある場合には、TRIM関数やCLEAN関数で整えることも有効です。
【操作のポイント】集計式を複雑にする前に、値の形式と余分な空白を整えると結果が安定します。
重複なしカウントで起こりやすいエラー
| 症状 | 主な原因 | 確認方法 |
|---|---|---|
| 件数が1件多い | 空白を集計 | 空文字列を確認 |
| 同じ値が別件になる | スペースや型の違い | 数式バーを確認 |
| 絞り込みと合わない | 非表示行も計算 | SUBTOTALを利用 |
続いては、重複なしカウントで結果が合わないときに見直したい点を確認していきます。
関数自体が正しくても、参照範囲やデータの状態が想定と違うと集計値はずれます。
参照範囲のずれ
COUNTIF関数で最初の出現を判定する式では、開始セルを正しく固定する必要があります。
$A$2:A2のように先頭セルを固定せず、A2:A2のままコピーすると、すべての行が初出として扱われます。
また、合計式がB2:B100なのに、データが101行目以降へ追加されていると新しいデータは集計されません。
範囲を広めに取るか、Excelテーブルに変換して自動拡張させる方法を検討しましょう。
数式をコピーした後は、最終行の参照先を数式バーで確認することが大切です。
大文字小文字と表記ゆれ
COUNTIF関数は、通常は英字の大文字と小文字を区別しません。
ABCとabcを別のコードとして管理したい場合、標準的なCOUNTIFだけでは目的に合わないことがあります。
また、株式会社と（株）、東京支店と東京 支店のような表記ゆれも、別の文字列として数えられます。
集計前に入力規則で選択式にしたり、置換機能で表記を統一したりすると、重複なし件数の信頼性が高まります。
マスターデータを参照する仕組みにすることも、表記ゆれを減らす有効な方法です。
エラー値が含まれる場合の対処
対象範囲に#N/Aや#VALUE!などのエラーが含まれると、FILTER関数やUNIQUE関数の結果に影響することがあります。
元の計算式でエラーが起きる理由を確認し、必要に応じてIFERROR関数で処理します。
ただし、IFERROR関数でエラーを空文字列に変えると、空白除外の条件が必要になります。
エラーを0に変える場合も、0除外の条件と矛盾しないかを確認してください。
【操作のポイント】件数が合わないときは、空白、0、参照範囲、データ型、非表示行の順に確認すると原因を見つけやすくなります。
まとめ エクセルで重複をカウントしない方法
エクセルで重複をカウントしない集計では、同じ値の最初の出現だけを1として数えるCOUNTIF関数の方法が基本です。
空白を除外したい場合は、A2<>””という条件を加えます。
0も対象外にしたい場合は、A2<>0も組み合わせましょう。
Microsoft 365やExcel 2021以降では、UNIQUE関数とFILTER関数を組み合わせることで、補助列なしでも重複なし件数を求められます。
空白、0、非表示セルをどこまで数えるかを先に決めることが、正確な集計への近道です。
フィルター後に表示されている行だけを対象にする場合は、SUBTOTAL関数で表示判定を作り、全データを対象にした重複判定との違いを意識してください。
件数だけでなく、元データの形式、余分なスペース、数値と文字列の混在も確認すれば、実務で使える安定した重複なし集計になります。
