リストの単語が含まれるセルを自動で塗りつぶす方法

営業員の日々の活動記録(文章)のうち、特定の単語(複数)が一つでも含まれている場合にセルを塗りつぶししたいと考えています。
完全一致した場合に塗りつぶす方法は、他の質問等で解決できたのですが、部分一致？の設定方法が見つからなかったため、質問させて頂きます。

【前提】
・シート1のA列の各セルに文章が記入されており、1000行程度（A1〜A1000）あります。
・シート2のB列の各セルに単語が記入されており、100行（B1〜B100）程度あります。
【出来上がりイメージ】
・シート1、A列の1000セルに入力されている各文章において、シート2の100単語のうちいずれかが含まれていれば、そのセルを自動で塗りつぶしたい。
・文章のどの位置（文頭・文中・文末）に対象の単語が含まれているかは問いません。
・文章に対象の単語が複数含まれていても問わない（ひとつでも含まれていれば塗りつぶす）
※シート2のB8に『C社』とあり、シート1の各セルが以下の場合、全て塗りつぶしの対象になるイメージです。
A列
1行目 C社
2行目 C社社長の来歴
3行目 先週のC社面談について
4行目 取引先引き合わせ D社及びC社

【試したこと】
①条件付き書式で「指定の単語を含む」を選択し、セル参照（上の※であれば、B8を参照させる）により一単語ずつ設定

→100単語分を登録する必要があり維持管理が困難。
→ブランクのセルを参照してしまうと、全ての文章が塗りつぶされてしまう。

②単語（シート2のB列）を「リスト」と名前の定義付けの上、条件付き書式で「数式を使用して、書式設定するセルを決定」を選択。数式に =countif(リスト,B1） と設定。以下URLに記載されている方法を使いました。
http://www.ne.jp/asahi/juku/excel/Jouken2907.htm

→文章と単語が完全一致でないと塗りつぶされず、「●●を含む」の条件を作れていない。（上の※であれば、A1だけ塗りつぶされます。）

会社のPCから投稿できず、携帯から投稿しており、意図が伝わりづらい箇所も有ると思いますが、ご容赦いただければ有難いです。

No.1 ベストアンサー 回答者： tom04 回答日時： 2016/11/29 08:37

こんにちは！

条件付き書式での処理をご希望なのですね？

一案です。
作業用の列を使用し、そのセルを参照する方法はどうでしょうか？

↓の画像のようにSheet2のB列に対象単語を入力しておきます。
B1～B100セルまでを参照するようにしていますので、
空白セルが存在する場合は、文字列に含まれることはないであろうと思われるアスタリクス「*」で埋めます。
（もちろん他の文字「#」などでも構いません。空白セルがなければ大丈夫です）

Sheet1のC1セルに
=IF(OR(ISNUMBER(FIND(Sheet2!B$1:B$100,A1))),1,"")
配列数式ですのでCtrl+Shift＋Enterで確定し、フィルハンドルでずぃ～～～！っと下へコピーしておきます。

そしてA列すべてを範囲指定　→　条件付き書式　→　新しいルール　→　数式を使用して・・・　→　数式欄に
=C1=1
という数式を入れ　→　書式　→　塗りつぶしで「赤」を選択しています。

※　作業列が目障りであれば遠く離れた列にするか
非表示にしてください。m(_ _)m

この回答へのお礼

tom04様

早速ご回答頂き有難うございます。
まさに希望していた作業が一発でできました。
キーワードを追加するたびに条件付き書式を一つ追加…。。。と労力をかけてしまっていたので、非常に助かりました。

お礼日時：2016/11/29 09:42

No.2 回答者： CoalTar 回答日時： 2016/11/29 19:54

A1:A1000セル範囲をセルA1から選択して

条件付き書式 数式が、
=COUNT(FIND(リスト,$A1))-COUNTBLANK(リスト)