エクセルで「ある記号」がセル内にどれだけ含まれているかを調べたい場面は意外と多いです。メールの件名で「＠」の数を数える、ログデータで「#」や「%」など特定の記号を集計する、あるいはテキスト処理で記号の頻度を把握するなどです。記号の数を数えるには専用関数は無いものの、組み合わせで簡単に実現できます。ここでは初心者から上級者まで役立つ記号を数える関数の使い方を丁寧に解説します。最新情報を基にした方法を学び、業務効率をぐっと上げましょう。
目次
エクセル 記号の数を数える 関数を使う基本公式と仕組み
このセクションではエクセルで特定の記号の数を数える関数の基本的な仕組みと、なぜLEN関数とSUBSTITUTE関数の組み合わせが鍵となるかを解説します。文字列操作の基礎を押さえることで応用力が身に付きますのでしっかり理解してください。公式の意味、構成要素、処理の流れを段階的に見ていきます。各関数の特性に注意すると、数え漏れや誤差を防げます。
LEN関数とは何か
LEN関数はセル内の文字数を数える関数で、アルファベットや数字、空白、記号すべてを含みます。たとえばセルに「abc#＠123 」と入力されていたら、空白も記号も含めて全体の文字数を返します。これにより「記号を含んだ文字列の全体長さ」が把握できます。最新のエクセルでもLENは基本機能として安定しており、テキスト処理の基準として欠かせません。
SUBSTITUTE関数で記号を除去する
SUBSTITUTE関数は文字列から指定した文字や記号を別の文字列に置換できる関数です。「記号」を空文字（何もない文字列）に置換して文字列から除去することで、その記号が含まれていた数だけ長さが短くなります。記号が複数あってもすべて置換可能で、除去後の長さと元の長さの差で記号の数が算出できます。
LEN−SUBSTITUTEの組み合わせで記号の数を数える公式
記号の数を数えるには以下のような公式を使います。
=LEN(対象セル) − LEN(SUBSTITUTE(対象セル, “記号”, “”))
この公式の流れは、まず文字列全体の文字数をLENで取得し、次に記号をすべて削除した文字列の長さを取得し、その差を計算します。この差が記号の数になります。この方法は記号だけでなく特定の文字や数字でも使えます。大文字小文字の区別に注意が必要です。
範囲指定と複数セルでの集計
セル1つだけでなく複数セルにまたがる範囲で記号の合計数を求めたい場合は、SUMPRODUCT関数を使うのが効果的です。一般的には以下のような式になります。
=SUMPRODUCT(LEN(範囲) − LEN(SUBSTITUTE(範囲, “記号”, “”)))
この式は範囲内の各セルについて差を取り、それらを足し合わせることで全体の記号数を算出します。Excelのバージョンによって動き方が異なる場合がありますが、最新のExcelでは自動配列機能により簡単に使えるようになっています。
エクセルで特殊記号やワイルドカード文字などを数える方法
記号の中には “*”, “?” などワイルドカードとして扱われるものや、エクセル上で特殊に処理される記号があります。このセクションでは、それらの扱い方と、ワイルドカード文字を記号として数えるための対処法を紹介します。通常の記号の場合と異なる注意点や回避策も含めて解説します。
ワイルドカード文字を文字として扱う必要性
* や ? は検索やCOUNTIFなどの関数でワイルドカードとして特別な意味を持ちます。記号として数える場合、そのままでは文字列検索などで意図せずマッチしたり、除外されたりします。記号を正しく数えるためには、エスケープ処理や代替の関数を使う必要があります。ワイルドカードを文字列内で扱うか、関数の引数で特別指定するかが重要です。
SUBSTITUTEでエスケープが必要な記号の取り扱い
通常のSUBSTITUTE関数ではワイルドカードのエスケープ処理は不要です。SUBSTITUTEは文字列の置換を文字列ベースで行うため、ワイルドカードを特別に解釈しません。たとえば記号がアスタリスク（*）であっても、SUBSTITUTE(セル, “*”, “” ) とすれば空文字に置換可能です。COUNTIFなどワイルドカードを含む関数を使う際にワイルドカードとしての意味を無効化したい場合、その前にエスケープ文字を使うなど対応が必要です。
複数種類の記号を一度に数えるテクニック
もし “#”, “＠”, “％” など複数の記号をまとめて数えたい場合は、SUM関数やSUMPRODUCT関数を組み合わせる方法が便利です。具体的には、各記号に対して LEN−SUBSTITUTE の公式を使い、それを SUM で合計します。
例：= (LEN(A1)−LEN(SUBSTITUTE(A1,“#”,“”))) + (LEN(A1)−LEN(SUBSTITUTE(A1,“＠”,“”))) + …
また、配列定数や名前定義を用いて記号リストを作っておくとメンテナンスもしやすくなります。
ケース別：大文字小文字を無視して記号や文字数を数える方法
英文字など文字の大小が意味を持つケースでは、同じ記号でも異なる表記になることがあります。大文字／小文字の区別を無視したい場合や、日本語の全角半角の混在などがある場合に備えて応用できる方法を解説します。これにより誤差を減らし、期待通りの集計が可能になります。
UPPER関数やLOWER関数で統一してから処理
SUBSTITUTE関数は大文字小文字を区別するため、元の文字列をすべて同じケースに統一することで記号・文字の一致を拡張できます。たとえば全て大文字に変換する UPPER 関数を使うか、LOWER を使って小文字に統一してから SUBSTITUTE 処理を行います。これにより “A” と “a” を区別せずに数えたい場合に正しく集計されます。
全角半角記号の混在時の注意点
日本語環境では全角記号と半角記号が混在することが多く、それぞれコードが異なります。全角「＠」と半角「@」は別文字と見なされるため、それぞれを個別に置換・数える必要があります。全角半角の差を意識して置換対象として含めるか、前処理で統一するかを考えると良いでしょう。
記号以外の文字種も対象にする例（英数字・記号など）
文字種を問わず記号・英数字など特定の文字群をまとめて数えたい場合、文字クラス的に処理するには VBA を使う方法や配列式を用いる方法があります。Excel 本体の関数だけでは一部制限がありますが、テキストが表形式で規則的であれば SUBSTITUTE を組み合わせて複数の文字種を個別に除去し、差をとる方法で対応可能です。
応用編：セル内の部分文字列や複数記号、フィルタリング時の集計方法
基本を理解したら、さらに応用的なケースにも挑戦してみましょう。部分文字列（2文字以上の記号列）を数える、複数記号をリスト化して一括処理、フィルタがかかっているデータのみ集計するなど実務で役に立つ場面です。それらのテクニックを使いこなせば業務効率が大幅に向上します。
部分文字列（サブストリング）の出現回数を数える方法
たとえば「##」や「@@」といった2文字以上の記号列を数えたい場合は、LEN−SUBSTITUTE の差をその部分文字列の長さで割る方法が使えます。具体的には (LEN(セル)−LEN(SUBSTITUTE(セル, 部分文字列, “”))) ÷ LEN(部分文字列) という形です。これによりサブストリングが重なっていないと仮定した場合の出現回数が得られます。
複数記号をリストを使って一括処理する方法
複数の記号を同時に数えるために名前付き範囲や補助列を使う方法があります。記号リストを別のセル範囲に列挙し、各記号について LEN−SUBSTITUTE を計算し、それを SUMPRODUCT などで合計します。処理の重複を避けつつ、記号が多い場合でも整理して管理できます。
フィルタまたはテーブルで絞り込んだ表示データのみ対象にする方法
フィルタ処理がかかったデータだけで記号数を集計したいなら、SUBTOTAL 関数やテーブル機能が利用できます。SUMPRODUCT ではフィルタの影響を受けず全体を処理することがあるため、可視セルだけを対象とするには VISIBLECELLS を対象にした関数やヘルパー列を設けて可視かどうかを判断させ処理する方法が実用的です。
マクロ／VBAで記号の数を数える関数を自作する方法
Excel 標準の関数だけでは対応できないケース（大量データ、高速処理、複雑なパターンなど）では VBA による自作関数が有効です。このセクションでは VBA を使って記号出現回数をカスタマイズする関数の書き方と実際の使い方、注意点を説明します。マクロを使える環境かどうか、セキュリティ設定なども確認して使いましょう。
単一記号出現カウントのユーザ定義関数作成
VBA で関数を自作する場合、以下のような構造になります。まず対象文字列とカウントしたい記号を引数として受け取り、文字列を一文字ずつ走査して記号と一致するたびにカウンタを増やすロジックを書きます。単一記号であれば比較が単純なので処理速度も速く、Excel関数で処理が難しいワイルドカードや全角半角混在などにも柔軟に対応できます。
部分文字列や複数記号に対応する VBA 関数の例
複数文字からなる部分文字列や複数記号をリストで受け取って一括で処理する関数を作ることも可能です。引数で文字列と記号リストを受け取り、記号リストをループ処理しながらそれぞれの部分文字列を対象文字列から検索し、出現回数を加算する形式です。置換による方法か InStr 関数を使った検索ループかが実装手段としてあります。
速度とメモリ効率を意識した設計上の注意点
大量のセルや長い文字列を処理する場合、VBA のループ構造は遅くなることがあります。できるだけセル範囲をまとめて処理する、配列を使ってメモリ上で一括処理する、Excel 関数部分で可能な限り処理させて VBA は最小限にするなど工夫が必要です。マクロの利用許可やセキュリティも忘れずに確認してください。
よくあるトラブルと解決策：記号数カウントの誤差を防ぐチェックポイント
公式や応用方法を覚えても、実際の業務で想定外の誤差が出ることがあります。このセクションでは、記号のカウントが合わない・重複・抜けが出るといったトラブルを防ぐためのチェックポイントと対応策を具体的に紹介します。実務での失敗を未然に防ぎ、信頼できる集計結果を得るためのノウハウとして活用してください。
全角と半角の混在による差異
日本語環境では全角と半角が混在しやすく、それぞれが別の文字として扱われます。たとえば全角「＠」と半角「@」は別文字です。それゆえ片方のみを置換・除去の対象にしていると、もう片方は無視されて誤ったカウントになります。事前に全角半角を統一するか、両方を対象に公式を組む必要があります。
記号の重なりやサブストリング同士の干渉
部分文字列を数える際、重なって出現するケース（例：”@@@” の中の “@@”）では、LEN−SUBSTITUTE 方式ではオーバーラップを正しく扱えないことがあります。重なりを許さない場合、部分文字列を順に検出するループを使ったVBAが有効です。エクセルの標準関数でカバーできない場合の対応として理解しておくと安心です。
フィルタや非表示セルを含む範囲の制御
データがフィルタされたり非表示になっていたりする場合、SUMPRODUCT などで範囲を指定すると見えていない行も処理対象になることがあります。可視セルだけを計算対象にしたいときは、SUBTOTAL を応用するまたはテーブル機能を利用して「可視セルの合計」を取得できる設定を行うと誤差を減らせます。
ワイルドカード文字や特殊文字のエスケープ忘れ
* や ? や ~ などワイルドカードを法律的に扱う関数を使う際には、それらを文字としてそのまま扱うかどうかを意識する必要があります。COUNTIF などではワイルドカードとして解釈されるため、文字列とみなして正しく数えたい場合は、ワイルドカード文字をその関数内で無効化する手段（通常 ~ を前に置くなど）を使うか、関数を切り替えるとよいです。
実際の例で学ぶ記号数カウントの活用シーン
ここでは業務で使われる具体的な例を通じて、記号の数を数える関数がどのように役立つかを紹介します。メールデータやログ、アンケート回答など実践的なデータでの手順や工夫を示します。自分のデータに応用するためのヒントが得られますので、例を見て真似してください。
メール件名データで＠の数を集計する
メール件名に含まれる “＠” の数を集計したい場合、セルに件名が入力されているとして、先述の公式を用います。例：=LEN(A2) − LEN(SUBSTITUTE(A2, “＠”, “”)) でセル A2 の件名に含まれる “＠” の数が出ます。複数件を集計するなら SUMPRODUCT を使って範囲 A2:A100 の全件をまとめて計算できます。こうした集計はメーラーやマーケティングで頻出します。
ログファイルで_PERCENTや#を含む記号の頻度を調べる
ログやトラッキングデータに “%” や “#” の記号が含まれていることがあります。例えば、URL やパラメータ部分で使用される記号数を集計して分析したいとき、SUBSTITUTE を使ってそれらを取り除き、LEN との差で数えます。ログ行が複数ある範囲に対して SUMPRODUCT を使うことで、ファイル全体での記号使用量の傾向を把握できます。
アンケート結果のコメント欄で絵文字や特殊記号の数をカウントする
自由記述欄に絵文字や特殊記号が含まれていることがあります。これをカウントして文字数制限に近づいていないかチェックしたり、記号率を分析したいとき、記号リストを用意して複数の SUBSTITUTE を組み合わせたり、VBA で一括処理するのが便利です。絵文字は複数のコードポイントになるケースがあるため、Excel の仕様を理解して取り扱うことが重要です。
まとめ
エクセルで特定の記号の数を数えるには、LEN関数とSUBSTITUTE関数の組み合わせが基本かつ強力な方法です。記号を除去した文字列の長さとの差を取ることで数えられますし、SUMPRODUCT を使えば範囲全体で合計できます。ワイルドカードや大文字小文字、全角半角の混在などに注意することで誤差を避けられます。
必要であれば VBA を使って複雑な処理を自作することも可能です。ビジネスの現場ではメール件名やログ、アンケートなどでよく使われるテクニックなので、今回紹介した方法を習得していれば作業効率が大幅に向上します。是非、手元のデータで試して実践的なノウハウを身につけてください。
コメント