Excelで配列の完全一致回数を数える方法｜COUNTIF・EXACT・GROUPBYで重複行を集計する手順

Excelで配列や行データを扱っていると、「この配列とまったく同じ行が何回登場しているのか？」を知りたい場面がよくあります。この記事では、セルに「[3, 6, 8, 9, 10]」のような文字列として保存された配列を対象に、完全一致する配列の出現回数を正確にカウントする方法を、EXACT・COUNTIF・GROUPBYを中心にわかりやすく整理します。

Excelで「配列の完全一致回数」を数えたい典型シナリオ

例えば、次のようにセル1つに配列風の文字列が入っている表を考えます。

セル	内容（配列を表す文字列）
A2	[3, 6, 8, 9, 10]
A3	[3, 4, 6, 8, 9]
A4	[3, 6, 8, 9, 10]
A5	[1, 2, 3, 4, 5]
A6	[3, 6, 8, 9, 10]

このとき、

配列 [3, 6, 8, 9, 10] は何回出てくるか？
表の中に登場する配列とその出現回数を一覧にしたい

といった集計を行うことが目的です。

ここでは、セル内の配列を「文字列」として扱い、完全に一致する行の回数をカウントする方法に絞って解説します。

前提：セル内の配列は「テキスト」として比較する

Excel は、

[3, 6, 8, 9, 10]

のような値を配列オブジェクトとしてではなく、単なる文字列として扱います。

したがって、「完全一致」を判定するときも、

左の角かっこ [
右の角かっこ ]
カンマ , とその前後のスペース
数字の桁数（3 と 03 は別物）

などをすべてひっくるめて文字列として同じかどうかを比較することになります。

そのため、例えば次の2つは、見た目は同じでも文字列としては別物です。

[3, 6, 8, 9, 10]
[3,6,8,9,10]（カンマの後にスペースなし）

配列の完全一致回数を数える前に、

スペースの有無
全角・半角
0埋め（03 と 3）

などをあらかじめ揃えておくと、意図した通りの結果が得やすくなります。

数値が列ごとに分かれている場合は TEXTJOIN で1セルにまとめる

もともと次のように、「配列の要素」が列に分かれているケースも多いはずです。

セル	B列	C列	D列	E列	F列
2行目	3	6	8	9	10
3行目	3	4	6	8	9

この場合は、まず G 列などに次のような式で配列文字列を作っておくと、その後の比較がぐっと楽になります。

="[" & TEXTJOIN(", ", TRUE, B2:F2) & "]"

上記の式を G2 に入力して下方向へコピーすると、

G2: [3, 6, 8, 9, 10]
G3: [3, 4, 6, 8, 9]

といった形で、「配列を表す文字列」が1セルにまとまります。以降の説明では、配列文字列が A 列にあるものとして話を進めますが、実際には TEXTJOIN で作成した列を使えばOKです。

3つの基本アプローチの比較

配列（行データ）の完全一致回数をカウントする主な方法は次の3つです。

方法	数式例	特徴 / 注意点
EXACT + N + SUM	`=SUM(N(EXACT($A$2:$A$6,E2)))`	`EXACT` で文字列の完全一致を判定し、`N` で TRUE/FALSE を 1/0 に変換して合計。古い Excel では Ctrl+Shift+Enter で配列数式として確定。大文字小文字を区別したい場合に最も厳密。
COUNTIF	`=COUNTIF($A$2:$A$6,E2)`	シンプルで高速。文字列比較は大文字小文字を区別しないが、数字のみの配列では実質的な影響は少ない。
GROUPBY（動的配列対応）	`=GROUPBY(A2:A6,A2:A6,COUNTA)`	Microsoft 365 / Excel 2021 以降で使用可能。範囲内の重複行をまとめ、各行の出現回数を一度に集計できる。結果はスピルして一覧が自動生成される。

ここからは、それぞれの方法を具体的な例とともに詳しく見ていきます。

方法1：EXACT + N + SUM で厳密に完全一致を数える

想定データ

まず、次のようなデータを想定します。

セル	内容
A2	[3, 6, 8, 9, 10]
A3	[3, 4, 6, 8, 9]
A4	[3, 6, 8, 9, 10]
A5	[1, 2, 3, 4, 5]
A6	[3, 6, 8, 9, 10]

そして、セル E2 に「調べたい配列」を入れておきます。

セル	内容
E2	[3, 6, 8, 9, 10]

使用する数式

配列 [3, 6, 8, 9, 10] が A2:A6 に何回現れるかを数えるには、次の式を使用します。

=SUM(N(EXACT($A$2:$A$6,E2)))

Microsoft 365 / Excel 2021 以降では、普通に Enter キーで確定すれば動作します。
Excel 2019 以前などの古いバージョンでは、

式入力後、Ctrl + Shift + Enter で確定する（配列数式として入力する）

必要がある点に注意してください。

式の中身を分解して理解する

部分	役割	イメージ（A2:A6・E2 の例）
`EXACT($A$2:$A$6,E2)`	各セルの文字列が E2 と完全一致かどうかを判定する	`{TRUE;FALSE;TRUE;FALSE;TRUE}` のように TRUE/FALSE の配列を返す
`N( ... )`	TRUE を 1、FALSE を 0 に変換する	`{1;0;1;0;1}`
`SUM( ... )`	1 と 0 を合計し、「一致したセルの数」に変換する	この例では 3 を返す

つまり、EXACT が「完全一致かどうか」を行ごとに判定し、その結果を数値化して合計しているだけです。

EXACT + N + SUM を使うべき場面

EXACT を使うと、次のような場合でもきちんと区別できます。

[3, 6, 8, 9, 10]
[3, 6, 8, 9, 10]（一見同じに見えるが、どこかに全角スペースが混じっている）
[3, 6, 8, 9, 10] と [3, 6, 8, 9, 10 ]（末尾にスペース）
[3, 6, 8, 9, 10] と [3, 6, 8, 9, 010]（「10」の部分が 2 桁表記）

また、文字列内にアルファベットが含まれる場合、EXACT は 大文字と小文字を区別します。

[A, B, C]
[a, b, c]

上記2つは EXACT では別物として扱われます。厳密なテキスト比較を行いたい場合は、COUNTIF よりも EXACT を優先するのがおすすめです。

EXACT + N + SUM のメリット・デメリット

観点	内容
メリット	文字列の「1文字違い」まで厳密に判定できる。大文字小文字を区別した比較が可能。配列の長さや記号も含めて完全一致を保証できる。
デメリット	古い Excel では配列数式（Ctrl+Shift+Enter）が必要で少しとっつきにくい。行数が非常に多いと、COUNTIF よりも重くなりやすい。

方法2：COUNTIF でシンプルに配列の出現回数を数える

「とにかく簡単に」「大量データでもサクサク動いてほしい」という場合は、COUNTIF が第一候補になります。

基本の考え方

COUNTIF の構文はとてもシンプルです。

=COUNTIF(範囲, 条件)

これを、次のように「完全一致させたい配列文字列」を条件として指定します。

=COUNTIF($A$2:$A$6, E2)

$A$2:$A$6：配列文字列が並んでいる範囲
E2：カウントしたい配列文字列

この式を E2 の右隣、たとえば F2 に入力すると、E2 の配列が範囲内に何回現れるかがそのまま数値として返ってきます。

複数の配列をまとめて集計する場合

もし、調べたい配列が E2:E6 に一覧で並んでいる場合は、F2 に

=COUNTIF($A$2:$A$6, E2)

と入力し、F2 を下方向へコピーすれば、各配列の出現回数がまとめて求められます。

COUNTIF で注意しておきたいポイント

大文字小文字は区別されない
例：[A, B, C] と [a, b, c] は同じものとしてカウントされます。
ワイルドカード文字が含まれる場合
文字列に * や ? を含むと、COUNTIF はワイルドカードとして解釈してしまいます。
配列文字列には通常含まれませんが、何らかの理由で含まれている場合は ~ を使ってエスケープが必要です。

COUNTIF のメリット・デメリット

観点	内容
メリット	普通の関数と同じように Enter で確定できる。式が短く、数式を見ただけで意味がわかりやすい。行数が多くても比較的軽く動作する。
デメリット	大文字小文字を区別できない。厳密なテキスト比較が必要なケース（ログ解析など）では少し不安。

とはいえ、配列の中身が数値だけなら、大文字小文字の区別は関係ありません。その場合は、まず COUNTIF を選び、必要なら EXACT に切り替えるくらいの感覚で問題ありません。

方法3：GROUPBY（Microsoft 365）で「配列 + 出現回数」の一覧を一発作成

Microsoft 365 / Excel 2021 以降をお使いであれば、GROUPBY 関数を使うことで、

各配列の種類
それぞれの出現回数

を1つの数式で一覧化できます。

基本的な数式

配列文字列が A2:A6 にあるとした場合、C2 に次の式を入力します。

=GROUPBY(A2:A6, A2:A6, COUNTA)

すると、C2 を起点に、縦方向と横方向に結果がスピルします。イメージとしては、

C列（グループ）	D列（カウント）
[3, 6, 8, 9, 10]	3
[3, 4, 6, 8, 9]	1
[1, 2, 3, 4, 5]	1

のような結果が、自動的に生成されます。

GROUPBY の引数の意味

引数	例	役割
array	A2:A6	集計対象となる元データの範囲（ここでは配列文字列そのもの）
by_array	A2:A6	どの単位でグループ化するかを指定（ここでは各配列文字列ごと）
group_function	COUNTA	グループごとにどのような集計を行うか（ここでは個数を数える）

「A2:A6 を、同じ A2:A6 の値ごとにグループ化し、その個数（COUNTA）を集計してね」という指定になっています。

特定の配列の回数だけ知りたい場合

GROUPBY で得られた結果から、特定の配列の回数だけ知りたい場合は、XLOOKUP や VLOOKUP と組み合わせると便利です。

例えば、上記の GROUPBY の結果が C2:D4 にスピルしているとき、セル E2 に調べたい配列を入れておき、F2 に

=XLOOKUP(E2, C2:C4, D2:D4, 0)

とすれば、GROUPBY の結果から該当配列の出現回数だけを取り出せます。

GROUPBY のメリット・デメリット

観点	内容
メリット	配列ごとの出現回数一覧を、一発の数式で作成できる。「ピボットテーブル + 更新」のような操作をしなくてもよい。スピル範囲なので、元データの行数が増減しても自動で追従しやすい。
デメリット	Microsoft 365 / Excel 2021 以降でないと使えない。古いバージョンの Excel ユーザーとファイルを共有する場合には不向き。

状況別のおすすめの選び方

状況	おすすめの方法	理由
小規模データ（数百行程度）で、厳密な一致判定が必要	EXACT + N + SUM	スペースや大文字小文字の違いなども含め、完全に同じものだけを数えたい場合に向いている。
行数が多い（数万行）で、とにかくサクサク集計したい	COUNTIF	1セル1式で済み、高速に動作しやすい。配列の中身が数値だけなら十分。
配列の種類と出現回数を一覧で見たい（ユニーク集計）	GROUPBY（Microsoft 365）	1つの数式で「配列 + 回数」の一覧表を生成できるため、集計レポート作成が楽になる。
Microsoft 365 以外で一覧を作りたい	COUNTIF + UNIQUE（365 が使える環境）またはピボットテーブル	GROUPBY が使えない場合の代替として、UNIQUE と COUNTIF の組み合わせやピボットテーブルが有効。

UNIQUE + COUNTIF で GROUPBY の代替を作る（365 環境向け）

GROUPBY がない環境でも、Microsoft 365 であれば、UNIQUE + COUNTIF の組み合わせでほぼ同じことができます。

基本の流れ

A2:A6 からユニークな配列文字列の一覧を作る。
その一覧に対して COUNTIF で出現回数を数える。

具体例：LET を使った書き方

配列文字列が A2:A6 にあるとき、C2 に次の式を入力します。

=LET(
  arr, A2:A6,
  u, UNIQUE(arr),
  HSTACK(
    u,
    COUNTIF(arr, u)
  )
)

この式でも、

C列にユニークな配列
D列にその出現回数

がスピルして表示されます。GROUPBY と似た結果が得られるため、GROUPBY が使えない場合の代替手段として覚えておくと便利です。

よくあるつまずきとチェックリスト

「同じ配列なのにカウントされない」「思ったより件数が多い/少ない」といったトラブルは、ほとんどがデータ側の細かな違いに起因します。以下のチェックリストを一つずつ確認してみてください。

1. スペースの有無が違っていないか？

例えば、次の2つは文字列としては別物です。

[3, 6, 8, 9, 10]
[3, 6, 8, 9, 10 ]（末尾にスペース）

末尾のスペースや、カンマ前後のスペースの有無を揃えるには、SUBSTITUTE 関数や TRIM 関数が便利です。

目的	例
すべての半角スペースを削除	`=SUBSTITUTE(A2, " ", "")`
前後のスペースや連続スペースを整える	`=TRIM(A2)`（日本語混在だと全角スペースはそのまま残る点に注意）

一度別列に「クリーンな配列文字列」を作成し、その列を基準に COUNTIF や EXACT を適用するのが安全です。

2. 全角・半角が混じっていないか？

次の2つも、見た目は似ていますが文字列としては別物です。

[3, 6, 8, 9, 10]（半角カンマ）
[3，6，8，9，10]（全角カンマ）

全角記号を半角に揃えたい場合は、関数だけで完全に変換するのはやや面倒ですが、

事前に「検索と置換」で置換する
または VBA / Power Query で整形する

といった方法も検討するとよいでしょう。

3. 0埋めや書式の違いがないか？

配列の要素に 0 埋めの数値が混じっていると、次のように解釈かいしゃくが変わります。

[03, 06, 08]
[3, 6, 8]

Excel の書式設定で表示を 2 桁にしているだけなら、TEXTJOIN で文字列化するときに数値として扱われますが、最初から「03」という文字列として入力されていると、文字列の比較としては一致しません。

「数値としては同じものを同じとみなしたい」場合は、

数値として B列〜F列に保持する
TEXTJOIN で文字列化するときのみ、同じルールで書式化する

といった方針で揃えるとよいでしょう。

前処理で配列文字列を整える実践例

ここまでの話を踏まえ、実際のシートでは次のような手順にするとトラブルが減ります。

ステップ1：各行の配列を TEXTJOIN で1セルにまとめる

数値が B2:F2 にあるとき、G2 に次の式を入れます。

="[" & TEXTJOIN(", ", TRUE, B2:F2) & "]"

この列（G列）を、その後の集計の「元データ」として扱います。

ステップ2：不要なスペースを除去する列を作る（必要に応じて）

G列の末尾に余計なスペースが紛れている疑いがある場合、H2 に次のような式を入れておきます。

=SUBSTITUTE(G2, " ", "")

これにより、すべての半角スペースが取り除かれた配列文字列が生成されます。

以降は、

EXACT + N + SUM を H列に対して適用
COUNTIF の「範囲」に H列を指定
GROUPBY の引数 array/by_array に H列を指定

とすることで、「余計なスペースの違い」に悩まされることなく完全一致判定が行えます。

COUNTIFS や FREQUENCY がうまくいかなかった理由

質問の中で、「COUNTIFS や FREQUENCY を試したがエラーや不正確な結果になった」とありました。これらの関数は便利ですが、配列文字列の完全一致カウントには少し不向きな面があります。

COUNTIFS の落とし穴

COUNTIFS は「複数条件」を同時に満たす件数を数えるのには最適ですが、

行全体を1つの文字列として比較したい

という用途にはあまり向いていません。COUNTIFS で行データを比較しようとすると、

列ごとに条件を書かないといけない
列数が変わると式の修正が大変

といった問題が出てきます。セル1つに配列文字列をまとめてしまえば、COUNTIF のような単純な関数で済むため、結果として扱いやすくなります。

FREQUENCY の落とし穴

FREQUENCY は本来、「数値データを階級に分けて頻度分布を求める」ための関数です。テキストの完全一致回数を求める用途には設計されていないため、

文字列をうまく扱えない
配列数式としての設定がシビア

といった理由から、今回のような「配列文字列の完全一致カウント」にはあまり向きません。EXACT + N + SUM や COUNTIF / GROUPBY に乗り換える方がシンプルです。

まとめ：配列の完全一致カウントは「文字列として揃える」ことがカギ

Excel で配列（行データ）の完全一致回数を数えるときのポイントを整理すると、次のようになります。

セル内の配列は、Excel にとっては ただの文字列 である。
比較の前に、TEXTJOIN で1セルにまとめる と、その後の処理が楽になる。
スペース・全角半角・0埋めなどをできるだけ揃えてから比較する。
厳密な比較には EXACT + N + SUM、高速で簡単に済ませたい場合は COUNTIF を使う。
Microsoft 365 環境なら GROUPBY で「配列 + 出現回数」の一覧を一発で作れる。

一度、「配列を文字列として正しく揃える」仕組みを作ってしまえば、あとは COUNTIF や GROUPBY などの標準関数だけで柔軟な集計が可能になります。配列データが増えても流用しやすい形に作っておくことで、今後の分析やレポート作成の手間を大きく減らすことができます。

この記事を書いた人

佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

Excelで配列の完全一致回数を数える方法｜COUNTIF・EXACT・GROUPBYで重複行を集計する手順

Excelで「配列の完全一致回数」を数えたい典型シナリオ

前提：セル内の配列は「テキスト」として比較する

数値が列ごとに分かれている場合は TEXTJOIN で1セルにまとめる

3つの基本アプローチの比較

方法1：EXACT + N + SUM で厳密に完全一致を数える

想定データ

使用する数式

式の中身を分解して理解する

EXACT + N + SUM を使うべき場面

EXACT + N + SUM のメリット・デメリット

方法2：COUNTIF でシンプルに配列の出現回数を数える

基本の考え方

複数の配列をまとめて集計する場合

COUNTIF で注意しておきたいポイント

COUNTIF のメリット・デメリット

方法3：GROUPBY（Microsoft 365）で「配列 + 出現回数」の一覧を一発作成

基本的な数式

GROUPBY の引数の意味

特定の配列の回数だけ知りたい場合

GROUPBY のメリット・デメリット

状況別のおすすめの選び方

UNIQUE + COUNTIF で GROUPBY の代替を作る（365 環境向け）

基本の流れ

具体例：LET を使った書き方

よくあるつまずきとチェックリスト

1. スペースの有無が違っていないか？

2. 全角・半角が混じっていないか？

3. 0埋めや書式の違いがないか？

前処理で配列文字列を整える実践例

ステップ1：各行の配列を TEXTJOIN で1セルにまとめる

ステップ2：不要なスペースを除去する列を作る（必要に応じて）

COUNTIFS や FREQUENCY がうまくいかなかった理由

COUNTIFS の落とし穴

FREQUENCY の落とし穴

まとめ：配列の完全一致カウントは「文字列として揃える」ことがカギ

この記事を書いた人

コメント

コメントするコメントをキャンセル

Excelで配列の完全一致回数を数える方法｜COUNTIF・EXACT・GROUPBYで重複行を集計する手順

Excelで「配列の完全一致回数」を数えたい典型シナリオ

前提：セル内の配列は「テキスト」として比較する

数値が列ごとに分かれている場合は TEXTJOIN で1セルにまとめる

3つの基本アプローチの比較

方法1：EXACT + N + SUM で厳密に完全一致を数える

想定データ

使用する数式

式の中身を分解して理解する

EXACT + N + SUM を使うべき場面

EXACT + N + SUM のメリット・デメリット

方法2：COUNTIF でシンプルに配列の出現回数を数える

基本の考え方

複数の配列をまとめて集計する場合

COUNTIF で注意しておきたいポイント

COUNTIF のメリット・デメリット

方法3：GROUPBY（Microsoft 365）で「配列 + 出現回数」の一覧を一発作成

基本的な数式

GROUPBY の引数の意味

特定の配列の回数だけ知りたい場合

GROUPBY のメリット・デメリット

状況別のおすすめの選び方

UNIQUE + COUNTIF で GROUPBY の代替を作る（365 環境向け）

基本の流れ

具体例：LET を使った書き方

よくあるつまずきとチェックリスト

1. スペースの有無が違っていないか？

2. 全角・半角が混じっていないか？

3. 0埋めや書式の違いがないか？

前処理で配列文字列を整える実践例

ステップ1：各行の配列を TEXTJOIN で1セルにまとめる

ステップ2：不要なスペースを除去する列を作る（必要に応じて）

COUNTIFS や FREQUENCY がうまくいかなかった理由

COUNTIFS の落とし穴

FREQUENCY の落とし穴

まとめ：配列の完全一致カウントは「文字列として揃える」ことがカギ

この記事を書いた人

関連記事

コメント

コメントする コメントをキャンセル

コメントするコメントをキャンセル