エクセルで「!」「#」「@」など複数の記号の数を集計したい場面は多いです。手動で数えるのは時間がかかり間違いも起こりやすいです。本記事では、複数の記号をまとめて数えるための関数の組み合わせ、配列数式、VBAなど複数の手法を詳しく解説します。セル単位、列単位、複数記号の指定など様々なパターンをカバーし、誰でも使いやすく理解できる内容になっています。最新情報に基づいた方法なので安心して使えます。
目次
エクセル 複数の記号の数を数える方法とは
複数の異なる記号の数を数えるには、まず何を「記号」と定義するかが重要です。例えば、文字の中の「#」「@」「!」などの特殊文字や、それ以外の記号類を対象にする場合、どのセル範囲を対象とするか決める必要があります。一般的には LEN 関数と SUBSTITUTE 関数の組み合わせで、一つの記号ならばセル内の全体長からその記号を削除した後の長さを引くことで個数を求めます。
では、複数の記号を同時に数えるときにはどうするか。記号ごとに上記の方法を使い、それらを合計するやり方や、配列数式や SUMPRODUCT を使って一発で済ませる方法もあります。これらの手法はエクセルのバージョン（Office365、Excel2021など）でも動作が異なることがありますので、最新情報を活かして適切に使い分けます。
LEN と SUBSTITUTE を使って一つの記号を数える
一つの記号「#」をセル A1 内で数えたい場合、以下の式が基本となります。
=LEN(A1) − LEN(SUBSTITUTE(A1, "#", ""))
このように、LEN(A1) で元の文字数、SUBSTITUTE で「#」を取り除いた文字数を計算し、その差分が「#」の数です。この方法は全角半角の記号、空白、改行などには対応しますが、大文字小文字や全角半角の違いには注意が必要です。
複数の記号を個別に数えて合計する方法
複数の記号（例：「#」「@」「!」）を対象にする場合、記号ごとに上記の式を繰り返し、それらを合計します。例えばセル A1 内でこれら三つの記号を数えるなら、次のようになります。
= (LEN(A1) − LEN(SUBSTITUTE(A1, "#", ""))) + (LEN(A1) − LEN(SUBSTITUTE(A1, "@", ""))) + (LEN(A1) − LEN(SUBSTITUTE(A1, "!", "")))
この方法はわかりやすいですが、記号の数が多いと式が長くなり見通しが悪くなる欠点があります。
SUMPRODUCT や配列数式を使って複数記号をまとめてカウント
Excel の Office365 や Excel2021 などでは、配列数式や SUMPRODUCT 関数を使って複数記号を一つの式で処理することができます。例えば、{ “#”, “@”, “!” } の配列を使ってそれぞれの記号を SUBSTITUTE で取り除き、元の長さとの差を合計する例があります。こうすることで、一つのセルあるいは列全体を対象に複数の記号をいっぺんに数えることが可能です。こうした手法はより効率的で、数式の管理もしやすくなります。
記号の種類や範囲に応じた具体的な数式例
記号の対象を「特定の一つ」「複数の決まった記号」「任意の記号カテゴリー」に分け、さらに対象範囲を「セル一つ」「列全体」「複数列や複数行」に広げることで多様なシナリオに対応できます。ここではそれぞれに応じた数式例を詳しく紹介し、どのように応用するかを解説します。
特定の一つの記号を数える例
先ほど紹介した通り、一つの記号を数える基本式は LEN と SUBSTITUTE の組み合わせです。たとえばセル B2 に「!」を数えるなら、
=LEN(B2) − LEN(SUBSTITUTE(B2, "!", ""))
この式は、「!」以外が含まれていても正確にその記号の数を返します。複数セルに適用するときはこの式をコピーすればよいです。また、全角記号と半角記号が混在する場合、それぞれに分けて処理するか正規化してから適用する方法が安全です。
複数の決まった記号を数える具体例
「#」「@」「!」など決まった記号を一度に数えるための式は以下のようになります。対象セルが A2 の場合：
= (LEN(A2) − LEN(SUBSTITUTE(A2, "#", ""))) + (LEN(A2) − LEN(SUBSTITUTE(A2, "@", ""))) + (LEN(A2) − LEN(SUBSTITUTE(A2, "!", "")))
範囲が複数セルあるなら SUMPRODUCT を使って列全体に適用できます。たとえば列 A2:A10 にあれば、それぞれの記号について SUMPRODUCT を使って集計し、合計すれば複数セル内の記号数が一発でわかります。
任意の記号カテゴリーを定義して数える応用テクニック
記号とは何かを幅広く捉えたい場合、記号カテゴリを定義して範囲を柔軟にするテクニックがあります。例えば正規表現風に記号をまとめて置き換える手法、TEXTSPLIT や FILTER、SEQUENCE、MID を組み合わせて、文字一つずつを記号かどうか判別し、数を集計する方法です。これは最新バージョンの Excel で使える動的配列機能を活かした方法で、空白やアルファベット数字を除外するなどの条件付きで記号のみをカウントできます。
複数セル・列全体で集計したい場合の手順と注意点
実務では一つのセルではなく複数のセルや列全体にわたって記号を集計するケースが多いです。そういった場合の手順や注意点をまとめます。対象範囲の選び方、数式のコピー、配列値の制御、計算速度への影響など、実践的なポイントを押さえておくことで効率的に処理できます。
範囲選択と数式のコピー
複数セルを対象にする際、数式をセル一つに書いてコピーまたはフィルハンドルで範囲に適用する方法があります。しかし範囲が非常に広いと計算に時間がかかることがあります。SUMPRODUCT を使うと一度に範囲を渡せるため効率的です。また Office365 など動的配列をサポートしているバージョンでは、配列数式を書くだけで複数セルをまとめて処理できるため管理が簡単になります。
計算速度とセル数・記号数のトレードオフ
記号をたくさん指定するほど SUBSTITUTE を繰り返す数だけ数式が複雑になります。その結果、計算が重くなりファイルサイズや処理時間に影響が出ることがあります。特に数千行以上の範囲を対象にする場合は、補助列を使って記号ごとの集計を分けたり、VBA やスクリプトを使った自動処理を併用することが有効です。また、ワークシートの再計算モードを手動にしておき、変更後のみ再計算するようにするとレスポンスが改善します。
複数列・複数行の集計例
例えば列 A と列 B の両方に記号が含まれており、どちらも集計対象とするなら、以下のような式を使えます。対象範囲 A2:B100、記号が「#」「@」「!」の場合：
=SUMPRODUCT((LEN(A2:B100) − LEN(SUBSTITUTE(A2:B100, "#", ""))) + (LEN(A2:B100) − LEN(SUBSTITUTE(A2:B100, "@", ""))) + (LEN(A2:B100) − LEN(SUBSTITUTE(A2:B100, "!", ""))))
この式は複数列にわたって指定した記号の合計数を返します。動的配列が使える環境では、このようなまとめ書きがとても役立ちます。
VBAマクロを使って大量データや動的処理を効率化する
記号の種類が多い、範囲がとても大きい、あるいは自動化したい場合には VBA（Visual Basic for Applications）を使ったマクロが有効です。特に変更後に自動で集計したいケースや、定期的に処理を実行する必要がある場合、マクロを組むことで手作業の数式管理の手間やエラーを減らせます。
基本的な VBA のサンプルコード
以下はセル A2:A100 にあるテキスト中で記号「#」「@」「!」をすべて数えて、結果を列 C に出力するマクロの例です。
Sub CountSymbols()
Dim ws As Worksheet
Dim rng As Range, cell As Range
Set ws = ThisWorkbook.ActiveSheet
Set rng = ws.Range("A2:A100")
For Each cell In rng
If Not IsEmpty(cell) Then
cell.Offset(0, 2).Value = _
(Len(cell.Value) − Len(Replace(cell.Value, "#", ""))) + _
(Len(cell.Value) − Len(Replace(cell.Value, "@", ""))) + _
(Len(cell.Value) − Len(Replace(cell.Value, "!", "")))
End If
Next cell
End Sub
このコードはシンプルですが、記号リストを配列にしたり、動的範囲を取得するように改良することでより汎用性が上がります。
VBAを使う際の設定と注意点
マクロを使用する際は、信頼された場所からファイルを開く、マクロのセキュリティ設定を確認するなど安全性に注意が必要です。また、大きなデータ範囲を VBA で処理すると応答が遅くなることがありますので、画面更新を停止する、計算モードを手動にするなどの工夫を導入すると良いです。
VBAと関数の組み合わせで自動更新を実現
たとえばシートにデータが追加されたときに自動で記号数を更新したい場合、Worksheet_Change イベントを使って、変更されたセルだけを対象に上記の記号計算を実行するマクロを組めます。これにより関数では対応しにくい「動的な追加」に対しても応じられます。必要に応じて補助列を使い、集計結果をまとめて別の場所に出す方法が一般的です。
関数選びの基準：目的・利用環境・可読性で判断する
どの方法を使うかは、目的、Excel のバージョン、扱うデータ量、数式のメンテナンス性などによって異なります。以下の基準で判断すると間違いが少ないです。
目的別の判断ポイント
単一セルの解析なら LEN/SUBSTITUTE の組み合わせが最もシンプルです。複数セルや複数列を一度に処理したいなら SUMPRODUCT を含む配列数式が有効です。自動化や定期的に更新する必要があるなら VBA を含む方法を選びましょう。まず「手軽さ」「スピード」「保守性」のバランスを見て検討することが肝要です。
Excel のバージョンによる互換性
Office365 や Excel2021 では動的配列機能やテキスト処理関数が強化されており、複数記号をまとめて扱う式や配列定数を使いやすくなっています。一方で古いバージョン（Excel2016 以前など）では配列数式を入力する際に Ctrl+Shift+Enter が必要なケースがあるなどの差異がありますので、その点をあらかじめ確認しておくことが望ましいです。
式の可読性と保守性
式が長くなりすぎると後で何を数えているか分からなくなります。記号の一覧をセルに並べて、それを参照する方法、補助列を使う方法、VBA を使ってリスト化する方法など、見通しをよくする工夫を施すことが重要です。ドキュメントに記号のリストを明示しておくこともおすすめです。
まとめ
複数の記号の数を数える作業は、LEN と SUBSTITUTE を使った基本式が出発点です。そこから複数記号の同時処理や複数セル対応、配列数式や SUMPRODUCT、さらに必要に応じて VBA を使うことで大幅に効率化できます。Excel のバージョンによる機能差や処理速度を考慮しながら、自分の環境に合った方法を選ぶことが重要です。今回紹介した手法を組み合わせて、記号の集計を正確かつ効率的に実行できるようになります。
コメント