今回の記事では
指定した範囲の中から探したい文字を含んだセルがいくつあるのか数える方法ってあるの？
探したいキーワードはその都度変わるので使い勝手が良い方法を知りたい
指定した範囲の中に複数のキーワードを含んだものがいくつあるか数えるにはどうしたら良いの？
キーワードの組み合わせは自由な方が使いやすいのでその方法が知りたい
こういった疑問に答えます
✔関数を使った任意の文字列の数え方のテーマ
①COUNTIF関数を使えばキーワードを含むセルの数を簡単に数えられます
②COUNTIFS関数を使えば複数のキーワードでもセルの数は簡単に数えられる
この記事を書いているのはOffice歴約20年
現在、エステサロン経営をしながらITを使った現場のマネジメントをしています
「関数を使った任意の文字列の数え方」に関して実体験に基づいて解説しています
今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい
目次を使って読みたい項目へ
COUNTIF関数を使えばキーワードを含むセルの数を簡単に数えられます
キーワードを含めるセルの数を数えたい場合はCOUNTIF関数を使うのが正解です
なぜなら、COUNTIF関数なら簡単に条件に合うセルの数を数えることが出来るからです
では実際にCOUNTIF関数を使ったセルの数え方について見ていきましょう
今回は、「日本にある人気の湖や沼の一覧」から、探したいキーワードを含む湖が何件あるのか自動で数えてくれる方法を例に設定方法を見ていきましょう
こんな風に「湖と池」の一覧表から「池」というキーワードを含んだ数を数える方法です
これなら「池」という名前が付く湖は2箇所あるということがパッと見で分かりますよね
では、具体的な設定方法を見ていきましょう
設定方法はこれだけです。思ったより簡単ですよね
では、式を詳しく見てみましょう「=COUNTIF(A5:A24,“*”&D5)」結構シンプルですがポイントはワイルドカードを組み合わせているところです
もう少し分かりやすいように式を日本語にしてみましょう
「=COUNTIF(A5～A24セルの範囲で,D5セルのキーワードを含む件数を表示してね)」
こんな感じです
COUNTIF関数は「指定の範囲内に調べたい文字がいくつあるか」数えてくれる関数です
この「調べたい文字」をワイルドカードを使って「あいまい検索」するのが今回のポイントです
ワイルドカードやあいまい検索について詳しくは、「IFとCOUNTIF関数のネストでキーワード検索」という記事で紹介しているので参考にしてみて下さい
今回の場合キーワードは「池」ですよね
つまり、一覧表から「〇〇池」というパターンの湖の名前が何件あるのか？ 数えれば言い訳です
そこで一番シンプルな検索方法がこんな感じになります
今回の式は「=COUNTIF(A5:A24,”*池”)」です
設定内容は最初とほぼ変わらないですよね
違いは「”*池”」か「”*”&\D5」だけです
COUNTIF関数は、検索したい文字を「””(ダブルクォーテーション)」で囲ってこれは文字ですよと分かるように設定します
そして「*(アスタリスク)」はワイルドカードの1種で要は「〇〇」と言う意味になります
例えば「*池」=「〇〇池」・「*池*」=「〇〇池〇〇」といった感じです
つまり、「”*池”」という表現は「〇〇池」つまり「池で終わる文字」を探してきてね
ということをCOUNTIF関数に教えているということになります
ここまでの話は整理出来ましたか
次はこのルールを応用しましょう。それが「”*”&」の部分になります
これは「〇〇」という文字のあとに何かくっつけたいので「&」を付けています
そして「”*”&D5」という設定で「*キーワード内容」つまり「*池」という文字を作っています
わざわざどうしてこんなことをするかというと、次の方法を見てみると分かりやすいです
こんな風にキーワードが「池から湖」に変わっても問題なく件数を表示してくれます
もし、直接キーワードを入力しているパターンの場合は「”*池”」を「”*湖”」と調べたい内容が変わるたびに再設定する必要が出てきちゃいますよね
この手間を無くすためにこんな風に工夫して汎用性を高く(使いみちを広く)しているという訳です
繰り返しになりますが、キーワードを含めるセルの数を数えたい場合はCOUNTIF関数を使うのが正解です
よくある質問：探したい文字が含まれている場合のセルの数え方は分かったけど、キーワードになる文字を含めない数え方はあるの？
キーワードを含めないセルの数を数えるなら「<>」記号を組み合わせるのが正解です
理由は、含めないということはつまり「ではない」ということなので同じ意味がある比較演算子を使えば、指定したキーワードを除外して数えることが出来るからです
実際に比較演算子を使ったキーワードを含めない方法について見ていきましょう
今回は「沼」というキーワードを含めない方法です
数式は「=COUNTIF(A5:A24,“<>*沼”)」です
これを日本語にしてみましょう
「=COUNTIF(A5～A24のセルの中で,「沼を含まない」数を数えてね)」
こんな感じですね
キーワードを除外するということは、〇〇を含まないということなので今回のポイントは「“<>*沼”」の部分です
これは「*沼」という「あいまい検索」に「<>(不等号)の比較演算子」を使って「〇〇沼ではない」という意味にしています
今回の検索範囲には「20の湖の名前」があってそのうち「沼」が付くのは「１つ」つまり「19件」が答えになります
では、「〇〇を含まない」を応用した使い方についても見ておきましょう
応用的な方法はこんな感じで「=COUNTIF(A5:A24,“<>*”&D9)」にすることです
キーワードを含める検索方法とほぼ一緒ですね
ポイントは「“<>*”&D9」の部分で「比較演算子とワイルドカードを使って一つの文字」にしています
それをセルの内容とくっつけることで応用が効く使い方にしています
キーワードを含める場合も含めない(除外する)場合も分かってしまえば、比較的簡単に使いこなせるので覚えておくとすごく便利ですよ
繰り返しになりますが、キーワードを含めないセルの数を数えるなら「<>」記号を組み合わせるのが正解です
COUNTIFS関数を使えば複数のキーワードでもセルの数は簡単に数えられる
複数のキーワードを含むセルを数えるにはCOUNTIFS関数を使うと簡単です
なぜなら、COUNTIFS関数は「複数の条件と複数の範囲をいっぺんに調べる」ことが出来る便利な関数だからです
では実際にCOUNTIFS関数を使った複数の条件に合うキーワードの数え方について見ていきましょう
今回は「湖」という文字を含む「北海道の湖の数」を知りたいというのが目的です
結果を見てもらうと分かる通りちゃんと2つの条件を満たした件数を数えてくれていますよね
では、実際の設定方法を見てみましょう
これが、COUNTIFS関数を使った複数の条件に一致したセルを数える方法です
難しそうに見えますが、すごく簡単なので設定内容を見てみましょう
式は「=COUNTIFS(A5:A24,“*”&D5,B5:B24,“*”&E5)」なのでコレを日本語にしてみます
「=COUNTIFS(A5～A24のセルの範囲で,「湖」に一致するモノを見つけて数えてね,B5～B24のセルの範囲で,「北海道」に一致するモノを見つけて数えてね)」これだけです
要はキーワード1の「湖」を数えてキーワード2の「北海道」の数をそれぞれのエリアで数えます
そして、1と2両方のキーワードが一致した件数を数えている訳ですね
設定方法はCOUNTIF関数とほぼ一緒です
違いは、検索範囲と条件を2回設定することだけなのでCOUNTIF関数の使い方を覚えてしまえば、COUNTIFS関数はめちゃくちゃ簡単に使えるという訳です
繰り返しになりますが、複数のキーワードを含むセルを数えるにはCOUNTIFS関数を使うと簡単です
オススメのExcel関数の解説書 5選
関数を本で学ぶ時は次に上げるものがオススメです
本を購入をオススメしている理由と各本の紹介に関して、下記の記事で詳しく解説しているので合わせて読んでみてください
コメントを残す