「含む件数」と「出現回数」を数え分ける

LWP | 記事G003 「含む件数」と「出現回数」を数え分ける

図解1|4件と6回は、どちらも正しい

図1:メモの件数と言葉の出現回数を区別する

一行が一件のメモ。メモを数えるか、言葉を数えるかで答えが変わります。

解説1|最初に「一つ」の単位を決める

「至急」という言葉を数えたのに、答えが4と6になりました。この違いを説明するには、関数の前に、何を一つとして数えるのかを決めます。今回の表では、A列のメモIDとB列の本文が一組で、一行が一件の作業メモです。一つの本文に同じ言葉が二度出ても、メモが二件に増えたわけではありません。

二つの問いと、数える対象

知りたいこと

一つと数える対象

今回の答え

「至急」を含むメモは何件か

条件に合う本文セル

4件

「至急」は全部で何回出るか

各本文内の文字列の出現

6回

件数では、一つのセルを最大1として扱います。回数では、一つのセルから2以上を取り出すことがあります。「至急」を含むメモを探すとM01・M03・M05・M06の四つですが、その中の出現回数は順に1・2・1・2です。したがって、件数は4、回数の合計は6になります。

一つの明細で違いを確かめる

M03の本文は「至急:至急便の手配」です。最初の「至急」と「至急便」の中の「至急」が見つかります。それでも、この本文が入っているのはB4という一つのセルです。C4に「含めば1、なければ0」、D4に「何回出たか」を出すと、同じ行の結果は1と2になります。

見る場所

内容・結果

その値が表すもの

B4

至急:至急便の手配

一件のメモの本文

C4

1

このメモは検索語を含む

D4

2

この本文に検索語が二回ある

この講座で組み立てる表

B列の本文はそのまま残し、C列に含む判定、D列に出現回数を作ります。検索語はF2へまとめ、集計結果をG列へ出します。入力と計算を分けるので、検索語を変えても原文を失わず、合計から明細へ戻って理由を確かめられます。

まずCOUNTIFで条件に合うセルを数え、次にLENとSUBSTITUTEで本文内の回数を求めます。最後にSUMで明細を足し、検索語を「確認」に変えて結果を照合します。関数ごとに別の例へ飛ばず、同じ六つのメモを使い続ける構成です。

読むポイント|セル数をそのまま案件数にしない

「一行が一件」という前提があるから、本文セルの数をメモ件数と読めます。一案件が複数行に分かれている表や、一セルに複数案件を書いた表では、この対応が崩れます。自分の表へ使うときも、先に一行の意味を確かめてください。「何件か」と「何回か」を見出しで区別することが、計算結果を正しく伝える最初の一歩です。

図解2|検索語を一つのセルに置く

図2:六つの作業メモと検索語のセルを準備する

原文、計算欄、検索語を分け、セルの位置をそろえて準備します。

解説2|入力と計算の置き場を分ける

今回使うシート名は「メモ確認」です。A:Bは入力した事実、C:Dは数式で求める結果、F2は集計条件という役割を持たせます。同じ語を数式の中へ何度も直接書くより、条件を一セルに集めた方が、変更漏れを防ぎ、何を調べているかも確認しやすくなります。

入力例|メモ確認のA1:D7

行

A列

B列

C列

D列

1

メモID

本文

含む判定

出現回数

2

M01

至急:見積確認

3

M02

請求書の送付

4

M03

至急:至急便の手配

5

M04

納期回答待ち

6

M05

本日至急対応

7

M06

至急の確認後、至急連絡

A1:B7を入力し、C1とD1には見出しだけを置きます。C2:D7はまだ空欄です。C列の式は図解3・解説3、D列の式は図解4・解説4で入力します。先に1や2を手入力しないでください。表の上の「A列」などと左の行番号は位置を表す枠です。入力・コピーするデータには含めません。

検索条件|メモ確認のF1:F2

行

F列

1

検索語

2

至急

E列は空けて構いません。離れた表を紙面で並べても、検索語の位置はA2などへ振り直さずF2のままです。F2には二文字の「至急」だけを入力します。「至急 」のように空白が付くと、その空白まで含む別の文字列になります。

入力後に確かめること

見出しは1行目、データは2〜7行目の六行です。M03は4行目、M06は7行目にあることを確認します。以後の数式はB2から始めて7行目までコピーするため、行を一つずらして入力すると、式が正しくても意図したメモを調べられません。

A列のIDは照合の目印です。並べ替えや行追加をする際には、IDと本文、対応する結果が同じ行のまま動くようにします。今回はまず並べ替えず、掲載した順序のまま数式を試しましょう。

読むポイント|原文を消す操作ではない

後で使うSUBSTITUTEは、数式の中で比較用の文字列を作ります。B列から「至急」を実際に削除する操作ではありません。原文を保持すれば、数値の理由を読み直せます。全文の一括置換を先に行うと、調べたい言葉そのものが消え、この例の入力条件が変わってしまいます。

図解3|COUNTIFは一致するセルを数える

図3:COUNTIFで一つのセルを一回だけ数える

調べる範囲が一セルなら1か0、六セルなら該当セルの合計を返します。

解説3|範囲と条件を分けて式を読む

COUNTIFは、指定した範囲の中で条件に合うセルの数を返す関数です。書き方はCOUNTIF(範囲, 条件)。第1引数で調べるセルを決め、第2引数で数える条件を決めます。文字が何回あるかではなく、条件を満たすセルがいくつあるかを返す点が重要です。

C2に入力する式|C7までコピー

=COUNTIF(B2,"*"&$F$2&"*")

式の部分

今回の役割

読み方

B2

調べる範囲

まず一つの本文セルを調べる

$F$2

検索語

行も列も固定してF2を参照する

"*"&$F$2&"*"

条件の組み立て

検索語の前後に文字があってもよい

二重引用符内の"*"は文字列であり、&でF2の内容とつないでいます。F2が「至急」なら、できる条件は「*至急*」です。条件を一つの文字列へ組み立ててから、COUNTIFへ渡していると読みましょう。

関数の役割|部分一致を条件にする

条件内の*はゼロ文字以上の任意の文字を表します。そのため「至急:見積確認」だけでなく、途中にある「本日至急対応」も条件に合います。B2の一セルだけを調べる式なので、結果の候補は1か0です。B4に二回あってもC4は1になります。

C2を下へコピーすると、調べるセルはB2→B3→B4と動きます。一方、$F$2は動きません。たとえばC4の式はCOUNTIF(B4,"*"&$F$2&"*")です。検索語の参照をF2のまま固定しないと、コピー先で空のF3などを調べる式になってしまいます。

G4に入力する式|六つの本文をまとめて数える

=COUNTIF(B2:B7,"*"&$F$2&"*")

F4へ「含むメモ件数」と入力しておきます。今度は第1引数をB2:B7に広げ、六つのセルを一度に調べます。条件は同じですが、範囲が一セルから六セルへ変わったため、答えは4です。C列を作る式とG4の式は、用途に応じて範囲を変えているだけです。

結果・読むポイント

C2:C7は1・0・1・0・1・1、G4は4になります。COUNTIFの後ろに「>0」を付けると真偽値の判定になりますが、この例では付けません。C列を数値の1/0にしておくことで、後からSUMで足して、G4と同じ4になるか照合できるからです。仕様:COUNTIF

図解4|減った文字数を、回数へ変える

図4:検索語を取り除いた文字数の差を語の長さで割る

文字数の差を、そのまま出現回数にせず、検索語の長さで割ります。

解説4|「取り除く前後の差」から回数を求める

同じ本文の中の回数を知るには、検索語をすべて取り除いた文字列を作り、元の文字列との長さを比べます。ただし「至急」は二文字です。一回取り除くごとに二文字減るため、減った文字数を検索語の長さで割って、回数へ変換します。

D2に入力する式|D7までコピー

=(LEN(B2)-LEN(SUBSTITUTE(B2,$F$2,"")))/LEN($F$2)

式の外側は「(元の文字数−置換後の文字数)÷検索語の文字数」です。引き算全体を括弧で囲んでから割ります。分母を固定値2にせずLEN($F$2)にしたのは、検索語の文字数が変わっても、その長さを使って計算するためです。

関数の役割|内側の結果を外側へ渡す

関数・指定

受け取るもの

今回返すもの

LEN(文字列)

元の本文や置換後の文字列

その文字数

SUBSTITUTE(本文, 検索語, "")

本文、探す語、置換後の空文字列

検索語を取り除いた文字列

置換位置の第4引数を省略

置換する出現位置を限定しない

該当箇所をすべて置換した結果

""は空白一文字ではなく、長さ0の空文字列です。SUBSTITUTEが返すのは数値ではなく文字列で、その結果をLENへ渡して初めて文字数になります。B列の元のセル内容は変わりません。

B4の例|途中の値を順に確かめる

計算する部分

得られる値

意味

元の「至急:至急便の手配」の長さ

9

コロンも一文字に含む

「至急」を取り除いた文字列

:便の手配

置換後は5文字

元の長さ−置換後の長さ

9−5=4

取り除いた文字の総数

検索語「至急」の長さ

2

一回で減る文字数

文字数の差÷検索語の長さ

4÷2=2

D4に出る出現回数

結果・読むポイント

検索語を含まない本文では、置換前後の長さが同じなので差は0です。一度含むと2文字減って1回、二度含むと4文字減って2回になります。この順に短い例で追うと、関数を重ねた式の各部分に意味を付けられます。

F2が空欄だとLEN($F$2)は0になり、割り算が成立しません。入力忘れを出現回数0と混同しないよう、まず検索語を確認します。エラーを一律に0へ置き換えて済ませないでください。仕様:LEN/SUBSTITUTE

図解5|各行の結果を出して合計する

図5:六行の判定と回数を別々に集計する

合計だけでなく、差を生んだM03・M06の明細へ戻って確かめます。

解説5|作業列と集計セルを照合する

C列とD列を7行目までコピーしたら、最初に各行の結果を見ます。C列は一つのメモを最大1として数え、D列は本文内の回数を返します。同じ検索語を使っていても、列の意味が違うため、同じ値になるとは限りません。

計算結果|メモ確認のA列・C列・D列(B列を省略)

行

A列

C列

D列

1

メモID

含む判定

出現回数

2

M01

1

1

3

M02

0

0

4

M03

1

2

5

M04

0

0

6

M05

1

1

7

M06

1

2

C列を足すと1+0+1+0+1+1=4、D列を足すと1+0+2+0+1+2=6です。差の2は、M03とM06がそれぞれ「一件の中に二回」を持つことから生まれます。本文を一つずつ読み返し、合計と明細の説明がつながることを確かめます。

G5に入力する式|出現回数を合計する

=SUM(D2:D7)

SUMは引数で指定した数値や範囲を合計します。今回はD2:D7の六つの計算結果を足します。F5には「出現回数合計」と入力し、G5の6がメモ件数ではなく回数の合計だと分かるようにします。

G6に入力する式|件数を別の計算経路で確かめる

=SUM(C2:C7)

F6を「件数の照合」とします。G4はCOUNTIFで本文全体を調べた結果、G6は行ごとの1/0をSUMで足した結果です。どちらも同じ六行を対象にしているので、両方が4になることを確認します。

集計結果|メモ確認のF4:G6

行

F列

G列

4

含むメモ件数

4

5

出現回数合計

6

6

件数の照合

4

読むポイント|合計一致だけで検査を終えない

G4とG6が一致しても、両方が同じ誤った範囲を見ていれば間違いを見逃します。六行すべてを対象にしていること、M02・M04は0、M03・M06のD列は2という内訳まで照合します。数式をコピーしたつもりで最後の一行が空欄のままになっていないかも確認してください。仕様:SUM

図解6|文字の一致と、仕事の意味を分ける

図6:文字列を含むことと業務上の意味を分ける

文字列の部分一致と、実際の優先度・対応状態を区別します。

解説6|この式が判断している範囲を知る

今回の数式は、指定した文字の並びが本文にあるかを調べています。日本語の単語を切り分けたり、文章の意図を読んだりする処理ではありません。計算が正しくても、数えた値へ違う意味の名前を付けると、業務上の結論を誤ります。

同じ「至急」でも、数値が表す範囲は違う

調べる本文

含む判定

出現回数

数えた理由

至急:至急便の手配

1

2

語の一部分も含め、二箇所に一致

至急ではない

1

1

否定されていても文字列は存在

二行目は条件を理解するための別例です。練習用の六行へ追加すると集計の期待値が変わるため、ここでは追加しません。M03の「至急便」も検索語を含みます。置換も語の一部分へ働くので、COUNTIF側と出現回数側の両方で対象になります。

結果の意味|「含むメモ」と「急ぐ案件」を分ける

G4へ「緊急案件数」と書くと、この式が緊急度を判断したように見えます。実際に分かるのは「至急という文字列を含むメモ件数」です。優先度を集計したいなら、本文とは別に「至急・通常」などの優先度欄を設け、その列の値を数える方が目的に対応します。

ただし、既存本文から正しい優先度を自動的に確定できるとは限りません。誰が、どの基準で、いつ値を更新するかも決める必要があります。数式の選び方だけでなく、入力項目と運用の設計が集計結果の意味を支えます。

今回の式をそのまま使う条件

検索語は「至急」「確認」のような漢字の文字列を使います。COUNTIFでは*と?がワイルドカードとして働き、~はその扱いを変えるための記号です。記号そのものを検索語に含めたい場合は、条件のエスケープを検討します。今回の条件連結式が、任意の記号列にもそのまま使えるとは考えないでください。

また、COUNTIFは英字の大文字・小文字を区別しません。英字について厳密な区別が必要なら、数えたい条件に合う方法を別に選びます。検索語を自由入力する仕組みへ広げるときほど、「何を同じものと扱うか」を先に決めることが大切です。

読むポイント|重なりを数える検索とは違う

文字数の差で求めるのは、置換された重ならない出現です。たとえば「aaa」から「aa」を空文字列に置換する考え方では、二文字を一回取り除いて一文字が残ります。開始位置を一文字ずつずらして二箇所を探す検索とは目的が違います。単語の境界、否定、重なりまで判断したい場合は、今回の式の対象範囲を越えると分けて考えてください。

図解7|検索語を変え、予想と照合する

図7:検索語・範囲・単位・結果を自分の表で照合する

予想できる小さな結果で試し、参照と集計範囲を確かめてから広げます。

解説7|別の検索語で確かめ、自分の表へ移す

最後にF2を「至急」から「確認」へ変えます。M01の「見積確認」とM06の「確認後」に一回ずつあるので、計算する前に2件・2回と予想できます。条件を一セルへ集めた効果と、C:D・G列の数式が同じ条件を参照していることを、一度に確かめられます。

検索語を変えた結果|メモ確認のF4:G6

行

F列

G列

4

含むメモ件数

2

5

出現回数合計

2

6

件数の照合

2

このときC2とC7、D2とD7が1、他のデータ行は0です。A:Bの原文とIDが変わらず、計算結果だけが変わることを確認します。照合が終わったらF2を「至急」へ戻し、G4・G5・G6も4・6・4へ戻ることを確かめます。

自分の表へ対応させる三つの場所

対応させるもの

今回の位置

変更するときの確認

調べる本文

B2:B7

先頭行と最終行、本文の列

検索語

F2

COUNTIF、SUBSTITUTE、分母LENを同じ参照へ

行ごとの結果

C2:D7

コピー先と集計範囲をそろえる

検索語を別のセルへ置くなら、そのセルを固定参照にして三箇所をそろえます。本文は各行を調べるので、コピーに応じて行番号が変わる参照を使います。両方を同じように固定してしまうと、全行が同じ本文を調べる原因になります。

行を追加したときの確認

8行目にメモを追加した場合は、C8とD8へ数式をコピーし、G4のB2:B7、G5のD2:D7、G6のC2:C7も8行目まで広げます。作業列だけ、あるいは集計式だけを延ばすと、明細と合計の対象がずれます。行の途中へ挿入する場合も、変更後の式がどこを参照しているかを見て確認してください。

読むポイント|0回・1回・2回の例を残す

自分の表へ移した直後は、結果を予想できる短い本文で試します。含まない行、一回だけの行、同じセルに二回ある行を含めると、範囲のずれや、件数と回数の取り違えを見つけやすくなります。一行が何を表すか、どのセルを調べるか、結果を何と呼ぶかまでそろって、初めて数値を業務で説明できます。

出典

『ちゃんとわかる Googleスプレッドシート』第9章9.3.5・9.3.6をもとに、六つのメモと二文字の検索語の独立例へ再構成しました。関数仕様:COUNTIF/SUBSTITUTE/LEN/SUM

利用について

Copyright © 2026 LWP 山中 一弘

本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。