【Excel】文字列内の文字・単語の出現回数をカウントする5つの方法

このチュートリアルでは、Excelで特定の文字列やデータ範囲内に含まれる文字や単語の出現回数をカウントする方法を解説します。大量のデータが入力されたセルや範囲の中から、特定の文字が何回登場するのかを調べたい場面はよくあります。本記事では、そんな作業を効率化できる実用的な数式を5つご紹介します。

練習用ワークブックのダウンロード

本記事で使用している練習用ワークブックは、こちらからダウンロードできます。

Excelで文字列内の文字の出現回数を数える5つの方法

1. SUMPRODUCT関数とLEN関数で文字数の合計を求める

1つのセル内の文字数を知りたい場合は、LEN関数だけで十分です。しかし、範囲全体の文字数の合計を求めたい場合は、SUMPRODUCT関数とLEN関数を組み合わせます。ここでは、書籍名のデータセットを例に、指定した範囲内の文字数の合計を求めてみましょう。

手順:

• まず、以下の数式を入力します。

=SUMPRODUCT(LEN(B5:B9))

LEN関数は文字列の文字数を返し、SUMPRODUCT関数は対応する範囲の積の合計を返します。

• これで、指定した範囲内の文字数の合計が表示されます。

ポイント:

ExcelのLEN関数は、各セル内のすべての文字をカウントします。これには英数字、スペース、記号、句読点なども含まれる点に注意してください。

2. SUBSTITUTE関数とLEN関数で特定の文字の出現回数を数える(大文字小文字を区別)

セル内に特定の文字が何回出現するかを知りたい場合は、LEN関数とSUBSTITUTE関数を同時に使います。SUBSTITUTE関数は大文字小文字を区別するため、小文字の「a」を検索すれば、小文字の「a」だけがカウントされます。例として、書籍名データセットの中から小文字の「a」の出現回数だけを数えてみましょう。

手順:

• まず、次の数式を入力します。

=LEN(B5)-LEN(SUBSTITUTE(B5,"a",""))

数式の解説:

➤ LEN(B5)

LEN関数がセルB5内の文字数をカウントします。

➤ SUBSTITUTE(B5,"a","")

SUBSTITUTE関数が、セルB5内のすべての「a」を空欄("")に置き換えます。

➤ LEN(SUBSTITUTE(B5,"a",""))

SUBSTITUTE関数の結果をLEN関数で囲むことで、「a」を除いた残りの文字数が求まります。

➤ LEN(B5)-LEN(SUBSTITUTE(B5,"a",""))

最後に、2つの文字数の差を計算することで、指定した文字「a」の合計出現回数が得られます。

• 最終的なカウント結果は以下のようになります。

3. SUBSTITUTE関数とLEN関数で特定の文字の出現回数を数える(大文字小文字を区別しない)

前の方法とは逆に、大文字小文字を問わず文字をカウントしたい場合もあります。その場合は、先ほどの数式にUPPER関数またはLOWER関数を追加します。ここでは、書籍名データセット内の「A」と「a」の両方をまとめてカウントしてみましょう。

手順:

• まず、以下の数式を入力します。

=LEN(B5)-LEN(SUBSTITUTE(UPPER(B5),"A",""))

UPPER関数はテキスト文字列をすべて大文字に変換します。これにより、すべての「a」が「A」に変換され、正しくカウントできるようになります。数式の残りの部分は、前の例と同じ仕組みで動作します。

• 数式が正しく入力されていれば、次のような結果が得られます。

4. 範囲内の特定の1文字の出現回数を計算する

文字列範囲全体における特定の文字の合計出現回数を求めたい場合もあります。例えば、データ範囲内に「A」または「a」が何回登場するかを知りたいといったケースです。

手順:

• まず、次の数式を入力します。

=SUM(LEN(B5:B11)-LEN(SUBSTITUTE(B5:B11,"a","")))

SUM関数はセル範囲内のすべての数値を合計します。数式の残りの部分は前述の方法と同じように動作し、最終的に範囲全体での「a」の合計出現回数を返します。

• その結果、合計カウントは以下のように表示されます。

5. 範囲内の文字列(テキストまたは部分文字列)の出現回数をカウントする

前の方法と同様に、データ範囲内に特定のテキストが何回出現するかを知りたい場合もあるでしょう。そのような場合は、複数の関数を組み合わせて使います。ここでは、SUM関数、LEN関数、SUBSTITUTE関数を組み合わせます。例えば、色名のデータ範囲から「Green」の出現回数を求めてみましょう。

手順:

• まず、以下の数式を入力します。

=SUM(LEN(B5:B11)-LEN(SUBSTITUTE(B5:B11,"Green","")))/LEN("Green")

上記の数式では、文字数を指定したテキスト(部分文字列)の長さで割る必要があります。割らないと、テキスト内の各文字が個別にカウントされてしまうためです。

• 最後に、指定した範囲内の「Green」の合計出現回数は以下のようになります。

注意:

上記の数式は配列数式として入力する必要があります。Windows版Excelで数式を配列として入力するには、Ctrl + Shift + Enterキーを押してください。

まとめ

今回は、Excelで文字列や文字列範囲内の文字数を調べるための、シンプルで使いやすい方法を5つ解説しました。どれもすぐに実践できるテクニックばかりなので、日々のデータ集計作業にぜひお役立てください。これらの方法についてご不明な点があれば、お気軽にお尋ねください。

関連記事

• Excelで文字列内の文字を検索する方法
• Excelで範囲内の値の最初の出現箇所を見つける3つの方法
• ExcelのFIND関数の使い方(7つの実例)
• ExcelでLEN関数を使う方法(数式とVBAコード)