【Excel】エクセルで特定の文字をカウントする方法（セル内・複数列）

Excelでアンケート結果、商品コード、住所、管理番号などを扱っていると、特定の文字が何件あるのか、または1つのセル内に何文字含まれるのかを数えたい場面があります。

たとえば「完」という文字を含むセルの件数を調べる場合と、「東京都東京都」のようなセル内にある「都」の出現回数を調べる場合では、使用する関数が異なります。

さらに複数列を対象にする場合は、COUNTIF関数だけでは目的に合わないこともあります。

この記事では、Excelで特定の文字をカウントする代表的な数式、ワイルドカードの扱い、複数列で集計するときの注意点まで解説します。

サンプルデータは1行目を見出しとして、2行目以降にデータが入力されている前提です。

 

エクセルで特定文字をカウントする方法1【COUNTIF関数】

それではまず、複数のセルの中から特定の文字を含むセル数を数えるCOUNTIF関数について解説していきます。

A	B	C
商品名	状態	判定
りんご	完了	完を含む
みかん	処理中	完を含まない
ぶどう	完了予定	完を含む

 

特定文字を含むセル数の数式

最も基本的な方法は、検索範囲と条件をCOUNTIF関数に指定することです。

たとえばB2からB10に入力された状態のうち、「完」という文字を含むセルを数える場合は、次の数式を入力します。

この数式では、アスタリスクが任意の文字列を表します。

そのため「完了」「未完了」「完了予定」のように、セル内のどの位置に「完」があっても条件に一致します。

結果を表示するセルを選択して数式バーに入力し、Enterキーを押すだけで件数を確認できます。

文字列が完全に一致するセルだけを数えたい場合は、両側のアスタリスクを外して、=COUNTIF(B2:B10,”完了”)のように入力しましょう。

「完」を含むセルを調べるのか、「完了」とだけ入力されたセルを調べるのかを区別することが大切です。

対象範囲に空白セルが含まれていても、COUNTIF関数は空白を「完」を含む文字列として数えません。

データが途中で増える表では、必要に応じて範囲をB2:B1000のように少し広く設定しておく方法もあります。

 

検索文字を別セルで指定する方法

検索する文字を毎回数式内に書き換えるのが面倒なときは、条件を別セルに入力して参照します。

たとえばE2セルに「完」と入力し、B2からB10の範囲を検索する場合は、=COUNTIF(B2:B10,”*”&E2&”*”)と入力します。

アンパサンドは文字列をつなぐ記号であり、前後のアスタリスクとE2セルの内容を1つの検索条件にまとめる役割です。

検索文字をセル参照にすると、表の再利用性が大きく上がります。

ただしE2セルが空白の場合、数式はすべての文字列セルを数える可能性があります。

検索語を空白にできない運用にするか、IF関数を組み合わせて空白時は結果を空欄にする設計が安心です。

空白時に何も表示しない数式は、=IF(E2=””,””,COUNTIF(B2:B10,”*”&E2&”*”))です。

利用者に入力を促すセルには、入力規則やセルの塗りつぶしを設定しておくと、操作ミスの予防にもなります。

 

記号を含む文字列の検索条件

COUNTIF関数では、アスタリスクと疑問符に特別な意味があります。

アスタリスクは任意の文字数、疑問符は任意の1文字に一致するワイルドカードです。

たとえば「A?C」という条件では、「ABC」や「A1C」は一致しますが、「AC」は一致しません。

一方で、実際にアスタリスク記号そのものを含むセルを数えたいこともあるでしょう。

その場合は、チルダを前に付けて「~*」と指定します。

この数式は、セル内にアスタリスク記号が含まれるセル数を返します。

疑問符を文字として検索したいときは「~?」を使います。

記号を検索するときは、ワイルドカードとして解釈されないかを必ず確認しましょう。

【操作のポイント】

 

エクセルでセル内の文字数を数える方法【LEN関数とSUBSTITUTE関数】

続いては、1つのセルの中に特定の文字が何回登場するかを確認していきます。

A	B	C
住所	数える文字	出現回数
東京都千代田区東京都	都	2
大阪府大阪市	都	0

 

文字列の長さの差を利用する数式

セル内に含まれる特定文字の回数は、元の文字数と、対象文字を削除した後の文字数の差で求められます。

A2セルに「東京都千代田区東京都」が入っている場合、「都」の数を数える数式は次のとおりです。

LEN関数は文字列全体の文字数を返します。

SUBSTITUTE関数は、指定した文字を別の文字列に置き換える関数です。

ここでは「都」を空文字列に置き換えて削除し、削除前後の文字数を比較しています。

削除された文字数が、そのまま「都」の出現回数になります。

この方法は「完」「円」「-」など、1文字を数える場面で特に分かりやすい方法です。

数式を入力したC2セル右下のフィルハンドルを下へドラッグすれば、3行目以降にも同じ計算をコピーできます。

セルごとの文字数を一覧化したいときに便利な手順です。

 

複数文字の語句を数える計算式

「東京」のように2文字以上の語句がセル内に何回あるかを数えることもできます。

A2セルに含まれる「東京」の出現回数を数える場合は、文字数の差を検索語の文字数で割ります。

「東京」を削除すると、出現した回数ごとに2文字ずつ減少します。

したがって、減少した文字数をLEN(“東京”)で割れば、語句の出現回数を求められます。

検索語をE2セルに入れる場合は、=(LEN(A2)-LEN(SUBSTITUTE(A2,E2,””)))/LEN(E2)に変更します。

複数文字を数える式では、最後に検索語の文字数で割ることが重要です。

ただし、検索語が空白だとLEN(E2)が0になり、割り算エラーになります。

実務では、=IF(E2=””,””,(LEN(A2)-LEN(SUBSTITUTE(A2,E2,””)))/LEN(E2))のように空欄対策を加えるとよいでしょう。

 

全角半角と大文字小文字の違い

文字数を数える数式では、見た目が似ている文字でも別の文字として扱われることがあります。

たとえば全角の「Ａ」と半角の「A」、全角スペースと半角スペースは別物です。

入力データに表記ゆれがあると、想定より少ない結果になるかもしれません。

英字については、SUBSTITUTE関数は通常、大文字と小文字を区別しません。

たとえば「Excel」という文字列で「e」を置換すると、先頭の「E」も対象として扱われます。

大文字小文字を区別して数えたい場合は、後述するEXACT関数とSUMPRODUCT関数を使います。

スペースを数えるときは、数式内の引用符の間に半角スペースまたは全角スペースを正確に入力してください。

判別しにくい文字は、対象セルを数式バーで確認してから条件に設定するのが確実です。

【操作のポイント】

 

エクセルで複数列の文字をカウントする方法【SUMPRODUCT関数】

続いては、複数列にまたがる表から特定文字をまとめて数える方法を確認していきます。

A	B	C
氏名	備考	連絡事項
田中	確認済	済
佐藤	未確認	連絡済

 

複数列で文字を含むセルを合計する数式

B列とC列のように離れた複数列を対象に、「済」を含むセル数を一度に集計したい場合があります。

連続した範囲であれば、SUMPRODUCT関数とSEARCH関数を組み合わせる方法が便利です。

SEARCH関数は、指定した文字が見つかった位置を数値で返します。

見つからない場合はエラーになるため、ISNUMBER関数で数値かどうかを判定します。

ダブルマイナス記号はTRUEとFALSEを1と0へ変換し、SUMPRODUCT関数が合計できる形に変えるためのものです。

この数式は、B2からC10にある「済」を含むセルをすべて数えます。

上のイメージのように、集計結果を表示するセルに数式を入力すると、選択範囲内の一致セル数が表示されます。

範囲内に数式エラーがある場合はSEARCH関数の結果に影響することがあるため、元データも確認してください。

 

列ごとにCOUNTIF関数を加算する方法

対象列が少なく、数式を見やすく保ちたい場合は、COUNTIF関数を列ごとに足す方法もあります。

この式はB列の件数とC列の件数を別々に求めてから合計します。

集計範囲がB列、D列、G列のように飛び飛びの場合にも、必要なCOUNTIF関数を加算するだけで対応できます。

離れた列を少数だけ集計するなら、COUNTIF関数の加算は理解しやすい方法です。

ただし対象列が多くなると数式が長くなり、列の追加時に修正漏れが起こりやすくなります。

表の構造に合わせて、SUMPRODUCT関数とCOUNTIF関数の加算を使い分けましょう。

 

検索語をセル参照にした複数列集計

検索語をE2セルに入力して、B列からC列を対象に集計する場合は、SEARCH関数の検索文字にE2を指定します。

検索語を変更すると、複数列の結果も連動して更新されます。

「済」「確認」「要連絡」などを切り替えて調査する管理表で使いやすい数式です。

検索語が空欄のときに結果を非表示にしたい場合は、=IF(E2=””,””,SUMPRODUCT(–ISNUMBER(SEARCH(E2,B2:C10))))とします。

検索条件セルは、集計対象範囲の外に置くと数式の意味を確認しやすくなります。

【操作のポイント】

 

エクセルで複数セルの出現回数を合計する方法【文字数差の集計】

続いては、複数セルにある特定文字そのものの総出現回数を確認していきます。

A	B
メモ	「！」の回数
至急！確認！	2
完了！	1

 

SUMPRODUCT関数で出現数を合計する数式

A2からA10にある「！」の総数を数えるには、各セルで減少した文字数をSUMPRODUCT関数で合計します。

各セルの「！」を削除した前後の文字数差を配列として計算し、その結果をSUMPRODUCT関数が合計します。

「至急！確認！」のように同じセル内に記号が2個ある場合も、2回として正しく反映されます。

COUNTIF関数がセル数を数えるのに対し、この数式は文字の総数を数えます。

集計したい対象が1文字なら、そのまま使用できます。

複数文字の語句を集計する場合は、文字数差を検索語の長さで割る考え方が必要です。

 

複数文字の語句を範囲全体で数える数式

A2からA10の範囲にある「確認」という語句の総出現回数は、次の数式で求められます。

検索語が2文字なら、1回出現するごとに文字数差は2になります。

このためLEN(“確認”)で割る処理を加えます。

「再確認」「確認済」「確認確認」のような文字列も、含まれる回数に応じて集計されます。

語句の重なり方によっては数え方が変わるため、特殊な表記は実データで検証しましょう。

たとえば同じ文字列が重なるケースでは、SUBSTITUTE関数は通常、置換できる箇所を順に扱います。

 

複数列を対象にした総出現回数

B列からD列までのすべてのセルにある「円」の総数を数えたい場合は、範囲をB2:D10のように指定します。

=SUMPRODUCT(LEN(B2:D10)-LEN(SUBSTITUTE(B2:D10,”円”,””)))という数式で集計できます。

金額欄、備考欄、単位欄などに同じ文字が混在している表の確認に向いています。

範囲を広げるときは、見出し行を含めないように2行目から指定します。

データ量が非常に多い場合、列全体参照は計算負荷を増やすことがあります。

B:Bのような指定より、実際のデータ行に合わせたB2:D1000のような範囲指定がおすすめです。

【操作のポイント】

 

エクセルで条件付き文字カウントを行う方法【IF関数と検索関数】

続いては、特定の条件を満たす行だけに絞って文字を数える方法を確認していきます。

A	B	C
担当	状態	備考
東京	対応中	要確認
大阪	完了	確認済

 

別列の条件に一致する行だけを数える数式

担当が「東京」で、かつ備考に「確認」を含む行だけを数える場合はCOUNTIFS関数を使います。

COUNTIFS関数では、条件範囲と条件を組み合わせて複数指定できます。

この数式はA列が「東京」であり、さらにC列に「確認」が含まれる行を数えます。

複数条件の集計では、すべての条件範囲の行数をそろえる必要があります。

A2:A10とC2:C20のように範囲の大きさが違うと、意図した結果になりません。

 

条件に合う行の文字出現数を合計する数式

「東京」が担当する行だけに絞り、C列内の「確」の総出現回数を数える場合はSUMPRODUCT関数を使用します。

A列が東京であるかどうかを1または0に変換し、C列の文字数差に掛け合わせています。

東京以外の行は0倍になるため、集計結果に含まれません。

条件付きで文字の総数を調べるときは、SUMPRODUCT関数が柔軟です。

条件文字をE2セル、検索文字をF2セルに分けて入力すれば、集計条件を変更しやすい表になります。

 

大文字小文字を区別する文字カウント

英字コードなどでは、「a」と「A」を区別して数えたいケースがあります。

SEARCH関数は大文字小文字を区別しないため、そのままでは使えません。

単一セルで大文字のAだけを数える場合は、EXACT関数を利用したより複雑な式が必要になることがあります。

実務では、補助列で文字列を分割する、Power Queryで整形するなどの方法も選択肢です。

英字の大小を区別する要件では、簡単なCOUNTIF関数だけで判断しないことが大切です。

まずはデータの表記ルールを統一できないかを確認すると、集計式を大幅に簡潔にできます。

【操作のポイント】

 

まとめ エクセルで特定の文字をカウントする方法

Excelで特定の文字をカウントする方法は、何を1件として数えたいかで選ぶ関数が変わります。

特定文字を含むセルの件数を数えたい場合は、=COUNTIF(範囲,”*検索文字*”)を使います。

1つのセル内にある文字の出現回数は、=LEN(セル)-LEN(SUBSTITUTE(セル,”検索文字”,””))で求められます。

複数セルにある文字の総数は、SUMPRODUCT関数で文字数差を合計すると効率的です。

セル数と文字の総出現回数は異なるため、最初に集計目的を整理することが正確な結果への近道です。

複数列を検索する場合はSUMPRODUCT関数、少数の離れた列を対象にする場合はCOUNTIF関数の加算も使いやすいでしょう。

検索文字を別セルに入力する仕組みにすれば、数式を直接編集しなくても条件を切り替えられます。

全角半角、空白、ワイルドカード記号の扱いにも注意しながら、用途に合った数式で文字カウントを行いましょう。