今回の記事では
セルの内容をキーワードにして一覧表から該当する件数がどれだけあるのか知りたい
この方法を関数を使って自動化出来ない？
複数のキーワードでもセルの内容を参照して一覧表に何件あるか知る方法は無いの？
こっちも関数を使って自動化出来ると便利なんだけどどうにかならない？
こういった疑問に答えます
✔セルの内容を使ったキーワード検索のテーマ
①自由度の高い検索するならCOUNTIF関数+ワイルドカードが鉄板です
②複数条件の検索ならCOUNTIFS関数を活用すれば手軽に実現出来ます
この記事を書いているのはOffice歴約20年
現在、エステサロン経営をしながらITを使った現場のマネジメントをしています
「セルの内容を使ったキーワード検索」に関して実体験に基づいて解説しています
今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい
目次を使って読みたい項目へ
自由度の高い検索するならCOUNTIF関数+ワイルドカードが鉄板です
セルの文字をキーワードにしてキーワードが含まれる内容を検索するには、COUNTIF関数＋ワイルドカードの組わせがベストです
理由はワイルドカードを使って数を数えるCOUNTIF関数が一番効率的だからです
以前にもCOUNTIF関数＋ワイルドカードで検索する方法についてお話しました
詳しくは「COUNTIF関数ならキーワードの数を簡単集計」という記事を見てみて下さい
ただ、ワイルドカードの使い方や種類・もう少し具体的な使い方についてもっと知りたいという要望が多くあります
そこで「*」と「?」を使ったワイルドカードの使い分けや設定方法について改めて見ていきましょう
今回は「全国星空スポット」という表から「セル内の文字」をキーワードにした件数の数え方について具体的に見ていきたいと思います
今回は左の表から右の表の「場所」を見つける方法を検証しています
まず、ワイルドカードでによく使われるのが「*(アスタリスク)」と「?(クエッション)」です
それぞれの使い方は
「北海道*」の場合「北海道〇〇」など文字数に関係なく「北海道で始まる」という意味になります
同じ使い方をする「?」の場合
「北海道?」とすると「北海道産」のように「?=何か1文字」という意味になります
つまり、ワイルドカードでは「*」が一番使いやすいと言えます
COUNTIF関数とワイルドカードの基本的な使い方は
こんな風に「=COUNTIF($B$5:$B$31,”*北海道*”)」と設定して「”*北海道*”」と言うように調べたいキーワードを直接入力して検索します
ただ、コレだと「”*北海道*”」を「沖縄」に変えたい時は「”*沖縄*”」と書き換えないいけなくなります
関数の設定内容をその都度書き換えるというのは、ちょっとした修正ミスで関数が全く使えなくなってしまったり、修正箇所が多いと混乱して訳が分からなくなってしまう可能性もあります
そんな時は、セルの内容を参照(検索キーワードに設定)して同じことが出来れば、参照先のいセルの内容を変更するだけで使い勝手がかなり広がります
そこで思いつくのが「=COUNTIF($B$5:$B$31,E6)」という方法です
ただ、この方法では残念ながら失敗してしまいます
なぜなら、この設定の場合「E6」の内容は「北海道」なので「北海道から始める」や「北海道で終わる」といった検索が出来ないからです
つまり、「B5のセル～B31のセル」の中には「北海道」のみのセルが無いので結果は「0」と表示されてしまう訳です
ではどうすればいいのか?
その答えがワイルドカードとくっつける方法になります
ワイルドカードとセルの内容をくっつけるためには「=COUNTIF($B$5:$B$31,”*”&E7)」のように設定します
ポイントは「”*”&E7」の部分で「*という文字に&を使ってE7セルの内容をくっつける」という使い方をしています
これは、「*北海道」という意味と同じになります
このルールは「*」でも「?」でも同じです
たま、それぞれの意味は次のようになります
| 設定内容 | イメージ | 意味 |
|---|---|---|
| *北海道 | ～北海道 | 北海道で終わる |
| 北海道* | 北海道～ | 北海道で始まる |
| *北海道* | ～北海道～ | 文字列内に北海道を含む |
| ?北海道 | ○北海道 | 1文字と北海道 |
| 北海道? | 北海道〇 | 北海道と1文字 |
| ?北海道? | ○北海道○ | 1文字北海道1文字 |
今回の場合は「?」だと検索対象が住所なのでいずれの場合も結果は「0」になってしまいます
例えば「北海道産」だったら「北海道?」をとすれば探し出すことが出来ます
「北海道*」と「*北海道*」の結果が一緒なのは住所の場合「北海道〇〇市」などのように北海道から始まるパターンになるからです
「*北海道*」の場合「北海道」の文字の前に何もなくても文字列の中に「北海道」が含まれているので、同じ結果になっているという訳です
今回の方法で一番違和感を感じるのは「”*”&E7」の「&E7」の部分だと思いますが、イメージにすると次のような感じになります
こんな風に工夫しないとワイルドカードを作ることが出来ないので、Excelの仕様に合わせて設定してあげることが重要になります
繰り返しになりますが、セルの文字をキーワードにしてキーワードが含まれる内容を検索するには、COUNTIF関数＋ワイルドカードの組わせがベストです
複数条件の検索ならCOUNTIFS関数を活用すれば手軽に実現出来ます
複数のセルの条件を利用して文字列を検索したい場合は、COUNTIFS関数を使うのが最適解です
なぜなら、COUNTIFS関数を使うことがやりたいことが実現出来る一番効率的な方法だからです
COUNTIF関数と同じように実際の例を参考にして複数のセルの条件を使った検索方法について見ていきましょう
COUNTIFS関数の詳しい使い方は「COUNTIF関数ならキーワードの数を簡単集計」でも紹介しているので参考にしてみて下さい
今回の場合もCOUNTIF関数とほぼ一緒です
違いは「キーワードと住所の両方の条件と一致する件数を数えていること」です
COUNTIF関数では、1つの条件しか設定出来ませんが、今回のように「沖縄にある島」を見つけたい時は「島というキーワード」と「沖縄という場所」の2つの条件に一致するモノが「全国星空スポットに何件あるか？」を知りたいという場合に活躍してくれます
まずは、直接ワイルドカードを設定する方法を見てみましょう
こんな風に複数の範囲と条件を指定しています
式は「=COUNTIFS($A$5:$A$31,”*島*”,$B$5:$B$31,”*沖縄*”)」でほぼCOUNTIF関数と同じ設定です
違いは、「$A$5:$A$31,”*島*”」と「$B$5:$B$31,”*沖縄*”」という2つの条件を設定しているところです
これもCOUNTIF関数と同じで直接入力しているため、条件が変われば直接修正する必要があります
これだと、イマイチ使い勝手が悪いのでセルを参照して条件を柔軟に変更できるようにしてみましょう
今回、正しい答えを出せたのは1パターンだけです
どうしてこうなったのか式を確認して見ましょう
「=COUNTIFS($A$5:$A$31,”*”&E9&”*”,$B$5:$B$31,”*”&F9&”*”)」これが唯一正解にたどり着けた設定です
「?」のパターンは文字数が必ず決まっていないといけないので、今回もどの組み合わせでも答えは「0」です
「?」の場合は、使い所が限られてくるということですね
今回の正解した設定内容のポイントは
「”*”&E9&”*”」と「”*”&F9&”*”」の組み合わせでした
つまり「文字列の中に「島」を含んでいる」+「文字列の中に「沖縄」を含んでいる」の両方の条件に一致しているから答えにたどり着けたということです
この組み合わせの他にも「島で終わって沖縄で始まる住所」ということは「*島」+「沖縄*」という「*」の場所が違えば見つけることが出来ます
複数の条件に一致する答えを見つけたい時は「”*”&E9&”*”」と「”*”&F9&”*”」の組み合わせであれば、多くの場合見つけ出してくれるので一番汎用性は高い(使い勝手がいい)ですね
ワイルドカードは使う目的によって使い分けることが重要です
関数との上手な組み合わせをマスターしてExcelを活用してみて下さい
繰り返しになりますが、複数のセルの条件を利用して文字列を検索したい場合は、COUNTIFS関数を使うのが最適解です
よくある質問：複数の条件でカウントする方法は分かったけど、もう少し実用的な使い方は出来ないの？
COUNTIFS関数の使い方を工夫すれば実用的な使い方をすることも簡単に出来ます
理由はCOUNTIFS関数を上手く使う方法を考えられれば、複数の条件設定もセルを使って簡単に設定できるからです
セルに入力された文字(キーワード)を基に一覧表を検索して条件に一致した結果を表示するのは意外と簡単です
そして、セルの文字をキーワードにできるメリットは対象のセルの内容を書き換えることで、検索方法を瞬間的に変更してその結果をすぐに表示できるところにあります
その設定方法についてみてみましょう
今回の目的はさっきと同じで「沖縄にある島」を検索することです
更に条件に一致したら「その島の名前を表示」する設定です
ポイントは、「検索対象の範囲を限定したこと」と「キーワードを固定すること」・「IFとネストさせること」です
キーワードについては、条件を１箇所にするだけなので上図のように設定すれば問題無いですよね
検索範囲は１行ずつ調べたいので「A30～A30」と設定してセルを１つずつ調べるように設定してあげればOKです
最後にIF関数を使って一致した場合(COUNTIFS関数の結果が「1」)ではない場合は、空白にしてそれ以外は「島の名前を表示させる」という設定をすればOKです
では、実際の式を見てみましょう
「=IF(COUNTIFS(A30:A30,“*”&$F$5,B30:B30,$G$5&”*”)=1,A30,“”)」コレを日本語にしてみます
「=もし(COUNTIFS関数の結果が「1」だったら,A30のセルを表示してね,そうでなければ「空白」を表示してね)」という意味になります
では、COUNTIFS関数の内容も日本語にしてみましょう
「COUNTIFS(A30～A30の範囲のセルで,最後が「島」で終わる場合と,B30～B30の範囲のセルで,沖縄から始まる住所だった場合)その数を表示してね」
という意味になります
こんな風に今までの方法を上手に応用することで複雑な条件に一致する場合でも、スムースに目的の結果を表示することが出来ます
IF関数とCOUNTIFS関数に慣れていると出てくる発想ですが、慣れないうちは難しいので少しずつ使い方をマスターしましょう
そうすれば、こんな風に実務でも活躍してくれるような使い方ができますよ
繰り返しになりますが、COUNTIFS関数の使い方を工夫すれば実用的な使い方をすることも簡単に出来ます
オススメのExcel関数の解説書 5選
関数を本で学ぶ時は次に上げるものがオススメです
本を購入をオススメしている理由と各本の紹介に関して、下記の記事で詳しく解説しているので合わせて読んでみてください
![VLOOKUP関数で下から検索する方法［XLOOKUP関数で簡単検索も可能]](https://i0.wp.com/tsurutoro.com/wp-content/uploads/2022/01/957697_m.jpg?fit=1200%2C797&ssl=1&resize=40%2C40){kind=small}
![VLOOKUP関数で０やエラー表示を避ける方法［IF・IFERROR関数との組み合わせ]](https://i0.wp.com/tsurutoro.com/wp-content/uploads/2022/01/4327107_m.jpg?fit=1200%2C900&ssl=1&resize=40%2C40){kind=small}
コメントを残す