Excelで「ある文字を含む」場合の判断を自動化したい……
IF関数を使ってなんとかしたいけど、どうしたら良いのか分からない
「ある文字を含まない」場合の判断はどうしたら良いの?
同じように関数を使って特定出来たら簡単なのに
こういった疑問に答えます
✔IF関数＋COUNTIF関数で特定の文字をピックアップするためのテーマ
①IF関数にCOUNTIF関数を含めれば解決出来ます
②条件式を工夫すれば除外したい場合も判定出来る
この記事を書いているのはOffice歴約20年
現在、エステサロン経営をしながらITを使った現場のマネジメントをしています
「IF関数＋COUNTIF関数で特定の文字をピックアップ」に関して実体験に基づいて解説しています
目次を使って読みたい項目へ
IF関数にCOUNTIF関数を含めれば解決出来ます
調べたい文字が含まれているかどうか調べるには「IF関数+COUNTIF関数」を組合せるのが正解です
理由はIF関数だけでは含まれる文字を探すことが出来ないからです
それでは次の表から「ある文字」が含まれているかどうか？ 調べる方法について順を追って見ていきましょう
練習したい方は▼からファイルをダウンロードして下さい(今回の記事で使った全ての内容が含まれています)
こんな風に設定することで「指定の文字を含む」場合その山の名前を表示することが出来ます
今回は「岳」という文字を含んだ場合「山の名前を表示してね」という設定をしています
その他にも例えば、市町村名から「町」の付く名前をピックアップすることも出来ちゃいます
では、今回の式を分かりやすくしてみましょう
「=IF(COUNTIF(B5,“*岳*”)=1,B5,””)」ざっくり色分けするとこんな感じです
これを日本語にすると
「もし、COUNTIF関数の結果が1だったら、B5のセルを表示してね、そうでなければ空白を表示してね」となります
ポイントはCOUNTIF関数の使い方です
ではここでCOUNTIF関数を式から取り出してみましょう「COUNTIF(B5,“*岳*”)」この結果は「1」になります
COUNTIF関数の使い方はすごく簡単で「対象の範囲の中に調べたい文字がいくつ含まれているか？」とう結果を数字で表してくれます
つまり、今回の場合「B5セルに「岳」という文字を含んでいるか？」ということを調べてくれています
B5セルには「利尻岳」という文字が入力されていて「岳」という文字が含まれているので「COUNTIF(B5,”*岳*”)」の答えは「１」になります
言葉だけでは分かりづらいので実際に「COUNTIF関数だけ」での場合の結果を見てみましょう
こんな風に「１」を返してくれます
今回のポイントは、どうして「１」を返すことが出来たのか？ という部分です
COUNTIF関数の普通の使い方の場合は「COUNTIF(B5,”岳”)」のように使います
違いは「岳の文字」を挟んでいる「＊(アスタリスク)」記号が有るか無いかです
ココが重要で「＊(アスタリスク)」マークは「ワイルドカード」と呼ばれ「条件に合う文字を特定するための記号」です
今回の場合「”*岳*”」の意味は文字の中に「岳」という文字が含まれているか？ という設定になります
つまり、「利尻岳」という３文字の中に「岳」の文字があるかどうか？ を調べている事になります
「利尻岳」という文字は「岳の文字を含んでいる」ので今回の答えは「１」という訳です
「*A*」と指定した場合「10ABC38A498」のように「A」の前後に他の文字などが含まれていても「A」が含まれているか分かります
「＊A」と指定した場合「12345A」のようにAで終わる文字を特定してくれます
「A＊」と指定した場合「A12345」のようにAで始まる文字を特定してくれます
※但し「*」は必ず半角で設定する必要があります
こんな風にIF関数を使って「ある文字を含む」場合に表示される結果を変えたい時はCOUNTIF関数との組合せ技を使って実現しましょう
繰り返しになりますが、調べたい文字が含まれているかどうか調べるには「IF関数+COUNTIF関数」を組合せるのが正解です
よくある質問：関数のネストは活用方法が分からない。今回みたいな時はどうやってアイディアを出せばいいの？
関数のネストは慣れないとなかなか難しいのでよくこういった質問を受けます
関数をネストする時はそれぞれの関数の結果を出して組合せるのが正解です
なぜなら、関数をいきなりネストさせて正しく動かすにはベテランでも大変だからです
今回のパターンを基にどうやって答えにたどり着いたのか？ 考えてみましょう
前提として「IF関数だけ」で今回の機能を実現することは出来ません
なぜなら、IF関数は「条件に一致する場合としない場合の結果を表示するための関数」だからです
今回の目的は「文字に〇〇を含む」場合「△△という文字を表示したい」ということでしたね
注目すべきは「文字に〇〇を含む」という条件をどうやって「特定するか？」ですよね
これは「ワイルドカード」という機能を知っていれば実現できそうです
次に「〇〇を含む」といキーワードをどうやって関数で表現すれば良いのか？ ということになります
「含む」ということは、含まない場合は「何も無い」つまり「０」という結果を表現出来れば解決出来そうですよね
ということは「対象の文字を１字目から見ていって」その結果が「０」になるか「１以上」になれば「含む」「含まない」は判断出来そうです
「０」か「1」以上を数えるためには「探している文字」が「対象の文字」に含まれているかどうか、「条件付きで数える」ことができれば良さそうです
条件付きで数える→「COUNTIF関数」が使えて「〇〇を含む」→「ワイルドカード」が使える→この2つを組み合わせれば出来そうですよね
ココまで当たりが付けられたら実際に設定してみましょう
但し、ココで慌てると頭がごちゃごちゃになるので段階的に設定していきます
まずはCOUNTIF関数だけで結果が出せるか見てみましょう
「=COUNTIF(B5,”岳”)」と設定しましたが「B5セルには「岳」という文字だけ」では無いので結果は「0」です
そこでワイルドカードが使えないか設定し直してみましょう
こんな風に「=COUNTIF(B5,”*岳*”)」として設定すると「利尻岳」という3文字の中に「岳」という文字が含まれるので「１」になりました
この結果を「IF関数の条件として設定」できれば、今回の問題を解決出来そうですよね
最後の仕上げに2つの関数をネストを使って判断出来るように設定してみましょう
これで目的は達成出来ましたが、ポイントはIF関数の条件式にさっき作った「=COUNTIF(B5,”*岳*”)」をコピペすることです
道のりは長いですがこうやって1ステップずつ設定していくことがネストを使いこなすためのコツです
ヒントも思い浮かばない時はネットで調べてみることも正解です
今の時代検索してみれば、大抵の答えは載っているので活用しましょう
もしくは、詳しい人がいればその人に聞いて自分なりに理解するのが最も早い方法です
繰り返しになりますが、関数をネストする時はそれぞれの関数の結果を出して組合せるのが正解です
条件式を工夫すれば除外したい場合も判定出来る
指定した条件を含まないモノをピックアップする時は「比較演算子+ワイルドカード」の組合せをするのが正解です
つまり、「〇〇を含まない」ということは「含むの反対」である「ではない」を表す演算子「<>」を組合せることで除外したい結果を表示することが出来ます
演算子はこんな風に「>」の反対は「<」など反対の意味を持つモノがあるのでセットで覚えておくと便利です
言葉だけでは分かりづらいので実際に図解で見てみましょう
今回の目的は「田舎を探すために「市」以外の地区に当たりをつけてそこに向かう」です
つまり、最後が市で終わらない「町村」を見つけることが出来れば成功です
設定する関数はこんな感じです「=IF(COUNTIF(A5,”<>*市”)=0,””,A5)」
最初に紹介した「岳」を含む山の名前を表示する方法を少しだけ応用すれば実現出来ちゃいます
ポイントは「COUNTIF(A5,”<>*市”)=0」の部分で「”<>*市”」という条件部分です
意味は「市で終わらない」という意味になります
「ではない」+「〇〇市」＝「市で終わらない」という意味になるので一見難しそうですが、ワイルドカードの前に除外する演算子をくっつけただけなので基本が分かっていれば簡単ですね
ちなみに「市町村は人口数で名前が変わる」という基本的なルールがあるのでこの設定であれば「人口が少ない」＝「あまり人がいない」→「自然が多い田舎」を簡単にピックアップ出来るという訳です
この基本的な考え方を抑えておけば、実務でも意外なところで活躍してくれると思います
意外と簡単な方法なのでぜひ押さえておきましょう
繰り返しになりますが、指定した条件を含まないモノをピックアップする時は「比較演算子+ワイルドカード」の組合せをするのが正解です
オススメのExcel関数の解説書 5選
関数を本で学ぶ時は次に上げるものがオススメです
本を購入をオススメしている理由と各本の紹介に関して、下記の記事で詳しく解説しているので合わせて読んでみてください
コメントを残す