【Excel関数活用】セル内の文字に複数キーワードのうちいずれかが含まれている時にのみ実行する関数の書き方
このブログではアフィリエイト・アドセンス広告を利用しています
当ブログでは、アドセンス・アフィリエイト広告を掲載しています。
消費者庁が発表しているルールに沿って記事を作成していますが、問題のある表現を見つけた際にはご連絡ください。
Excelを使って大量のデータを入力したり加工することが多い事務職のお仲間のみなさま、ちょっとしたことでつまづいて上手くいかないことが多々ありますよね。
今回は、「ある列のセル内に複数のキーワードのいずれかを含む場合に何かを実行する関数」の書き方をご紹介します。
目次（読みたいところへジャンプ！）
セル内の文字が1つのキーワードを含む判定は簡単にできるけれど…
Excelの関数を使って、あるセル内に1つのキーワードを含んでいるなら…という条件式なら、いくつもの解決方法が見つかります。
例えば、こんな表があったとしましょう。
「病院なら1、薬局なら2」という判定フラグを自動化したい関数を設定するなら、あなたはどのように書きますか？
| 施設名 | 判定 |
|---|---|
| 伊藤病院 | |
| 佐藤病院 | |
| 鈴木病院 | |
| 山田病院 | |
| 伊藤内科 | |
| 佐藤整形外科 | |
| 鈴木皮膚科 | |
| 山田眼科 | |
| 伊藤整骨院 | |
| 佐藤クリニック | |
| 鈴木ファーマシー | |
| 山田薬局 |
上の表で「病院なら1、薬局なら2」という判定フラグを関数で書くなら、私ならこのように関数を組みます。
IF文でワイルドカードが使えたらいいのですが、ワイルドカードを使用できないので、COUNTIF文でワイルドカードを使って指定した文字列である「病院」「薬局」の数を数えてから、IF文でフラグを立てるようにしてみました。
=IF(COUNTIF($A2,"*"&"病院"&"*"),1,IF(COUNTIF($A2,"*"&"薬局"&"*"),2,0))
全ての病院の名前に「病院」と含まれているとしたら、この関数で無事解決なのですが、世の中の病院はそうじゃありませんよね…
「山田内科」「佐藤レディースクリニック」「山下皮膚科」「近藤皮フ科」…病院によって、カタカナ表記だったり、診療科目名だったり様々。
そんな時に、全ての病院に関するキーワードをこの関数に追加しますか？
通常は、しませんよね。関数の文字数が凄いことになってしまうから。
=IF(COUNTIF($A2,"*"&"病院"&"*")+COUNTIF($A2,"*"&"内科"&"*")+COUNTIF($A2,"*"&"皮膚科"&"*")+COUNTIF($A2,"*"&"皮フ科"&"*"),1,0)
セル内の文字が複数のキーワードを含む判定をする関数の書き方
検索したい複数キーワードを簡単に追加したり修正したりできるように、エクセルの別シートに病院名キーワードを保存する場所を作りましょう。シート名は「医療区分1」としました。
薬局に該当するキーワードは、「医療区分3」シートにまとめていきます。
最後に、判定フラグを実行する列のセルに、次のコードを配列数式で入力します。
=IF(OR(SUBSTITUTE(A2,医療区分1!$A$1:$A$15,"",1)<>A2),1,IF(OR(SUBSTITUTE(A2,医療区分3!$A$1:$A$2,"",1)<>A2),3))
判定セルに、上の関数を入力後、Ctrlキー+Shiftキー+Enterキーを同時に押すと、関数が{}（中括弧）で囲まれて配列数式になります。
手入力でかっこ｛｝で囲んでも配列数式にはなりません。Ctrl+Shift+Enterキーを押しましょう。この3つのキーボードの頭文字を取って、配列数式のことをCSE数式と言ったりもします。
配列数式になると、クロスした複数条件の関数の実行が可能になります。
今回の例ですと、A2セルに医療区分1シート内のキーワードのいずれかが含まれているなら1、医療区分3シート内のキーワードのいずれかが含まれているなら3というフラグを立ててくれます。
配列数式になっていないと、関数が正しく実行されずに「False」と表示されてしまいます。
一番上のセルの関数内で、参照先の医療区分1!$A$1:$A$15と医療区分3!$A$1:$A$2の範囲を絶対値にしておけば、2行目以降のセルはオートフィルで自動でセットされちゃいますよね。
オートフィルなら、セルの右下を選択した状態でカーソルが「＋」の形になった時にダブルクリックすると、テーブルの最終行まで自動で関数を設定してくれます。データが何万件もあったとしても、ダブルクリックするだけの簡単作業です。
今まで複雑で長ったらしい関数でしか実現できないと思っていた条件式も、配列数式を使えばスッキリさせることができるかもしれません。
Excelの関数はなるべくシンプルな構造が望ましい
作成した関数やVBAの含まれたシートを入力する人のExcelレベルには差があります。どんな人でも問題なく作業が進むよう、なるべく関数の仕組みはシンプルでわかりやすくしておきましょう。
さらに、関数が書かれているセルをロックしておくと、「すみません、壊してしまったみたいです…」なんて、新人さんに振り回されることも無くなります。触られたくない部分は、ロックと保護を。
医療費控除の区分を自動で判定するフラグ
勘のいい方はお気づきかもしれません。今回の関数でできることの例は、確定申告で医療費区分1～4を施設名から自動判別してくれる関数になります。
病院だったら1、介護施設だったら2、薬局だったら3、その他は4と入力するのが面倒なので、この関数とVBAを使って自動化してみました。大量の医療費を入力しなきゃいけない会計事務所の方にならお役に立つかも。
配列数式、活用してみてくださいね。
