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

注意

Office 365 用リソース は、 エンタープライズ向け Microsoft 365 アプリに名前変更されています。 この変更の詳細については、 このブログの投稿を参照してください。

概要

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

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

詳細情報

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

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

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

注意

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

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

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

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

  2. 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","")))

注意

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

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

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

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

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

注意

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

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

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

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

  2. 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 つのセル内、またはセルの範囲に特定の文字が出現する回数を数えることもできます。

関連情報

文字列の出現回数を数える方法の関連情報を参照するには、次のマイクロソフト サポート技術情報番号をクリックしてください。

89794 Excel で Visual Basic for Applications を使用して選択範囲内の文字の出現回数を数える方法