Excel の文字列、文字、単語の出現回数を数える数式について

[アーティクル]
06/06/2024
適用対象:

Excel 2016, Excel 2013

概要

この記事では、次のものを計算する数式について説明します。

セルの範囲内に文字列が出現する回数
1 つのセルに文字が出現する回数
セルの範囲内に文字が出現する回数
セル内の文字で区切られている単語 (または文字列) の数

詳細情報

範囲内の文字列の出現回数を数える数式

=SUM(LEN(範囲)-LEN(SUBSTITUTE(範囲,"文字列","")))/LEN("文字列")

ここで、範囲は、出現回数を数える対象となるセルの範囲です。"文字列" には、数えたい特定の文字列を指定します。

注:

上の数式は配列数式として入力する必要があります。 Excel for Windows で数式を配列として入力するには、Ctrl キーと Shift キーを押しながら Enter キーを押します。 Excel for Macintosh で数式を配列として入力するには、Command キーを押しながら Return キーを押します。

この数式の SUM は、指定した文字列を空文字列に置き換えることによって、範囲内にある文字の数がいくつ減ったかを求めます。したがって、この値を、指定した文字列の文字数で割ると、その出現回数になります。この数式は、セル内の単語数を数える場合を除き、この記事で説明しているすべての数式の代わりに使うことができます。

例 1: 範囲内の文字列の出現回数を数える

Excel を起動し、新しいブックを開きます。

Sheet1 に次のように入力します。

A1: Fruit
A2: apple,apple
A3: orange
A4: apple,orange
A5: grape
A6: orange,grape
A7: grape, apple
A8: =SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,"apple","")))/LEN("apple")

セル A8 は 4 です。つまり、"apple" という文字列は 4 回出現しています。

1 つのセル内に 1 つの文字が出現する回数を数える数式

=LEN(セル参照)-LEN(SUBSTITUTE(セル参照,"文字",""))

ここで、セル参照は、出現回数を数える対象のセル参照です。"文字" には、数えたい特定の文字を指定します。

注:

この数式を配列数式として入力する必要はありません。

例 2: 1 つのセル内に文字が出現する回数を数える

前の例と同じデータを使用し、セル A7 に "p" という文字が出現する回数を求めるとします。セル A9 に次の数式を入力します。

A9: =LEN(A7)-LEN(SUBSTITUTE(A7,"p",""))

セル A9 の値は 3 です。つまり、セル A7 には "p" という文字が 3 回出現しています。

範囲内の 1 つの文字の出現回数を数える数式

=SUM(LEN(範囲)-LEN(SUBSTITUTE(範囲,"文字","")))

ここで、範囲は、出現回数を数える対象となるセルの範囲です。"文字" には、数えたい特定の文字を指定します。

注:

上の数式は配列数式として入力する必要があります。 Excel で数式を配列として入力するには、Ctrl キーと Shift キーを押しながら Enter キーを押します。

例 3: 範囲内に文字が出現する回数を数える

前の例と同じデータを使用し、A2:A7 というセルの範囲に "p" という文字が出現する回数を求めるとします。セル A10 に次の数式を入力します。

A10: =SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,"p","")))

注:

セル A10 の値は 11 です。つまり、A2:A7 というセルの範囲には "p" という文字が 11 回出現しています。

セル内の文字で区切られている単語の数を数える数式

=IF(LEN(TRIM(セル参照))=0,0,LEN(セル参照)-LEN(SUBSTITUTE(セル参照,文字,""))+1)

ここで、セル参照は単語数を数える対象のセル参照、文字は単語を区切っている文字です。

注:

上の数式内に空白はまったくありません。数式が複数の行に分かれて表示される場合は、単にこの記事を表示する画面のスペースに合わせているだけです。この数式をセルに入力するときは、空白を一切含めないでください。この数式を配列数式として入力する必要はありません。

例 4: セル内の文字で区切られている単語の数を数える

セル内の空白文字で区切られている単語の数を数えるには、次の手順に従います。

Excel を起動し、新しいブックを開きます。

Sheet1 に次のように入力します。

A1: The car drove fast
A2: =IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)

セル A2 の数式は 4 という値を返します。これは、このセルには、空白文字で区切られた 4 つの単語から成る文字列があるという意味です。単語の間に空白文字が複数入っていたり、文字列の先頭や末尾に空白文字があっても、問題ありません。 TRIM 関数が、セル内の余分な空白文字と文字列の先頭と末尾にある空白文字を除きます。

Excel では、マクロを使って、1 つのセル内、またはセルの範囲に特定の文字が出現する回数を数えることもできます。

次の方法で共有