Excelで文字の出現回数を数える方法｜LEN・SUBSTITUTE・COUNTIFを使い分け

Excelで文字の出現回数を数える基本式

ExcelでA2に「東京の市場を調べ、東京の人口も確認する」、B1に「東京」と入力します。次の式なら結果は2です。

A2は検索対象、B1は探す文字です。B1を変えれば、式を直さず別の文字列を調べられます。1文字なら文字数の差が回数です。

文字か語句かを先に決める

この式を使う前に、結果として欲しい数を決めます。「セルの中に東京という語句が何回あるか」なら出現回数を数えます。「東京を含む行が何行あるか」ならセル数を数えます。同じデータでも集計単位が異なるため、式を選ぶ前に一行だけ手で数え、どの結果が正解かを確認しておくと後の照合が簡単です。

検索語が1文字なら、置換で減った文字数がそのまま回数です。検索語が複数文字なら、減った文字数を検索語の長さで割ります。たとえば「東京都東京」に「東京」は2回あり、削除される文字数は4です。語句の一部だけを数えたいときは、検索語を短くして同じ式を使います。

式は検索語と完全に一致する連続した文字列を対象にします。表記の違いを同一視したい場合は、先に元データと検索語の表記を整えます。結果が整数にならない場合は、検索語が空欄でないか、式の検索語参照と割る文字数が同じセルを見ているかを確認してください。

ExcelのLENとSUBSTITUTEで数える仕組み

LEN は文字数を返し、SUBSTITUTE は指定した文字列を置き換えます。置換後を "" にすれば対象が消えるため、元との文字数の差で削除した長さが分かります。

複数文字の語句は、減った文字数を検索語の長さで割ります。たとえば「青い青い箱」に「青い」は2回です。

SUBSTITUTE式は重ならない一致を数えます。重なりも数える場合は別の照合方法を選びます。

Excelで文字の出現回数を複数セルで合計する

Excelで文字の出現回数を複数セルから集計する例では、A2:A100の各行を数える式をC2に入れて下へコピーします。

語句なら式に /LEN($B$1) を加え、結果列を =SUM(C2:C100) で合計します。$B$1 はコピー後も検索語の参照を固定します。

Excelで文字の出現回数を全行分合計する

Excelで文字の出現回数を行ごとに出さず合計だけ求めたいときは、範囲に対して SUMPRODUCT を使えます。B1が空欄なら0を返し、語句が複数文字でもLENで割る式です。

この式はA2:A100の各セルで非重複一致を数えてから合計します。途中のセルにエラー値があると集計結果にも影響するため、式を使う前にエラー行を確認してください。Excelの版や表のサイズによっては、補助列を使うほうが計算結果を追いやすくなります。

監査や月別比較で行ごとの内訳が必要なら、補助列を残してください。合計だけでは入力ミスのある行を特定しにくく、データ追加時にどのセルが結果へ寄与したかも分かりません。明細を残す方法と合計だけを返す方法は、目的に応じて使い分けます。

ExcelのCOUNTIFは出現数ではなく該当セル数を数える

COUNTIF は条件に合うセル数を数えます。A2:A100のうち「東京」を含むセル数は次の式です。

同じセルに「東京」が3回あってもCOUNTIFの結果は1です。セル内の出現総数はLENとSUBSTITUTEで数えます。条件の * は任意の文字列、? は任意の1文字なので、記号自体を探す場合は公式仕様を確認してください。

大小文字・空欄・重複一致の注意点

検索する文字が空欄のとき

Excelで検索語が空欄なら、IF(B1="",0,...) で0を返します。入力漏れを見つけたい表では、0の代わりにメッセージを表示します。

英字の大文字・小文字を区別するか

SUBSTITUTE は英字の大小文字を区別し、COUNTIFは区別しません。区別しない場合は文章と検索語を同じケースに変換します。

日本語のみの文字列では大小文字の違いはありません。

一部が重なる語句を数えるか

「banana」の「ana」は、置換式では1回ですが、開始位置ごとなら2回です。重なりを含める場合は各位置から照合し、集計ルールを明記します。

全角・半角、空白、改行

「Ａ」と「A」、「カ」と「ｶ」、末尾の空白や改行は同じ扱いとは限りません。0件なら元セルを確認し、全角・半角や空白の表記をそろえます。

COUNTIFで記号を検索する場合

COUNTIFの条件では、アスタリスクは任意の文字列、疑問符は任意の1文字を表すワイルドカードです。記号そのものを条件にしたいときは、直前にチルダを付けます。たとえば、セル内にアスタリスクを含む行は =COUNTIF(A2:A100,"*~**") のように指定できます。条件文字列にワイルドカードが含まれるデータでは、意図しない行まで数えていないか、抽出結果を数件見て確認します。

見えない空白と改行を調べる

画面上では同じに見えても、文字列の先頭や末尾に半角空白、全角空白、改行、コピー元の特殊な空白があると完全一致になりません。まず対象セルを数式バーで開き、前後に不要な空白がないか確認します。複数セルに同じ修正をする場合は、元データを別列へ残し、整形後の列だけを集計に使うと修正前後を比較できます。

TRIMは通常の半角スペースを整えますが、全角スペースやすべての制御文字を一括で消す関数ではありません。必要な場合は、置換対象を明示した SUBSTITUTE や不要文字を取り除く CLEAN を組み合わせ、変換後の文字列を標本で確認してください。無条件に文字を削除すると、商品コードや住所などの意味のある区切りまで消すおそれがあります。

エラー値と絵文字の扱い

検索対象のセルに #VALUE! などのエラーが含まれると、LENやSUBSTITUTEの結果もエラーになる場合があります。エラーを0として扱うか、元データを修正するかを決めてから集計します。また、一部の絵文字や合成文字はExcelのバージョンや互換性設定によってLENの数え方が異なることがあります。記号を正確に数える表では、使用中のExcelで既知の短い例を試し、期待値と照合してください。

Excelで集計表を作る手順と確認方法

1. 元データを上書きせず、検索語、出現回数、含むセル数の列を分ける。
2. 2回と分かっている短文で式を試し、式の結果と手作業の数を照合する。
3. 数える範囲の先頭行と最終行を固定し、検索語の参照だけを絶対参照にする。
4. 空欄、大小文字、全角半角、空白、重複一致の扱いを表の近くに記録する。

結果列を使う表では、見出しに「セル内の出現回数」と書き、COUNTIFで求める「該当セル数」と区別します。単位を明記しておけば、別の担当者が表を引き継いだときにも、数値が行数なのか文字列の繰り返し数なのかを判断できます。検索語を変えて再利用する場合は、検索語セルを一か所にまとめ、数式内へ直接文字を何度も入力しないようにします。

行を追加する予定がある場合は、対象データをExcelのテーブルにすると、テーブル末尾に行を追加した際に数式が引き継がれやすくなります。固定範囲を使う場合は、データがA101以降に増えたのに式の対象がA2:A100のままになっていないかを定期的に点検してください。

数が合わないときのチェックリスト

0件なら全角半角・空白・参照先、COUNTIFと違うならセル数と出現数の違い、英字だけ違うなら大小文字を確認します。語句ではLENで割ったか、重なりを含むかも見直します。

Excelで文字の出現回数が1だけ少ない場合は、同じ語句が隣接している場所や、検索語が重なって現れる箇所を調べます。LENとSUBSTITUTEは置換後に残る文字列を基準にするので、互いに重なる一致を別々の出現として数えません。反対に数が多い場合は、集計範囲に見出しやメモ行が含まれていないか、COUNTIFのワイルドカードが意図した条件かを確認します。

数式をコピーした後に行ごとの結果がすべて同じなら、検索語だけでなく検索対象の行参照まで固定されていないかを見ます。結果が空欄になるときは、数式を表示する設定、セルの表示形式、参照先の空欄を順に確認します。変更前の数式と元データを残しておけば、誤った置換や参照の修正にも戻りやすくなります。

Excelの文字カウントでよくある質問

COUNTIFで文字の回数を数えられますか？

ExcelのCOUNTIFは条件に一致したセルの個数を数えます。セル内で同じ文字列が複数回出ても、そのセルは1件です。セル内の出現数そのものを求めるときは、LENとSUBSTITUTEを使います。

範囲にある全セルの出現回数を一度に合計するには？

各行に出現回数の式を入れて下までコピーし、結果列をSUMで合計する方法が確認しやすいです。各行の値を残せるので、誤入力や表記揺れの確認にも役立ちます。

文字を含むセルの個数と出現回数は同じですか？

異なります。COUNTIFは条件に合うセルを数え、LENとSUBSTITUTEはセル内で一致した文字列を数えます。1つのセルに同じ語句が3回あれば、該当セル数は1、出現回数は3になります。

「ana」のように一致部分が重なる語句も数えられますか？

基本式は重なりを含めません。「banana」の「ana」は、基本式では1回として扱います。重なりを数える必要があるデータでは、開始位置ごとに照合する別の計算方法を用意し、どの位置を1件とみなすかを先に決めてください。

Microsoft公式リファレンス

関数の引数や条件記号の詳細は、使用中のExcelの仕様に合わせて公式ヘルプを確認してください。

• Microsoft: LEN function
• Microsoft: SUBSTITUTE function
• Microsoft: COUNTIF function