はじめに
エクセルを使った業務では特定の文字列を含むデータが入ったセルを探す機会に何度も遭遇します。
エクセルのデータから指定した文字列を探す場合、検索機能を使って文字列検索をかけることができます。
ただ検索機能では検索対象の文字列を含むセルを選択状態にする、カーソルを合わせることはできますが色をつけることまではできません。
この記事では複数の文字列を指定、文字列を含むセルを強調表示させる条件付き書式の設定方法を紹介します！
指定した文字列を含むセルを強調表示する条件付き書式（OR条件）
まずはイメージを掴んでもらうために動画を見てみましょう！
続いて画像でも見てみましょう
<画像で使用している数式>
=SUM(IFERROR(FIND(検索リスト!$A$1:$A$10,INDIRECT("RC",FALSE)),0)*(検索リスト!$A$1:$A$10<>""))>0
検索文字列を入力すると
次々と強調した書式に変化していますね！
検索条件はOR条件になっています
複数の文字列を探したい時に便利ですよ！
便利そうですが数式が長くて難しそうです…
紹介した数式は貼り付けるだけで使えますよ
検索用シートの作成から順番に手順を説明しますね！
まずは『検索リスト』という名前のシートを作成します
検索したい文字を入力するセルに枠線を設定しても良いですね！
画像の例ですと文字列を10個設定するために10セル準備しています
セルA1：A10ということですね！
次に強調したいセルがあるシートに移動します
セルを選択した状態でリボンの『ホーム』タブにある
『条件付き書式』をクリックして『新しいルール』を選択します
『新しい書式ルール』ウインドウが表示されるので
『数式を使用して、書式設定するセルを決定』を選択、
『書式』から強調表示の書式を設定します
図の例はセルの背景色をオレンジに設定しています
好きな強調方法を設定できるんですね！
<画像で使用している数式>
=SUM(IFERROR(FIND(検索リスト!$A$1:$A$10,INDIRECT("RC",FALSE)),0)*(検索リスト!$A$1:$A$10<>""))>0
『次の数式を満たす場合に値を書式設定』の下の欄に
上記の=SUMから始まる数式をコピーして貼り付けます！
あとは『OK』ボタンを押すだけです！
おつかれさまでした～
確認します！
きっとできているはず…
コピーした数式をそのまま使う場合、
『検索リスト』シートのセルA1:A10に検索文字列を入れます
それ以外のセルは検索文字列として認識しないので注意が必要です
あっ…うごきました！！
面倒な文字列探し業務に遭遇したときに使ってみて下さい
きっと役立ちますよ！
指定した文字列を含むセルを強調表示する条件付き書式（AND条件）
先に紹介した数式はOR条件
（指定文字列のいずれかを含むと強調表示）
でしたが、AND条件
（指定文字列の全てを含むと強調表示）
の数式もあわせて紹介しますね！
動画からどうぞ～！
<例で使用している数式>
=AND(SUM(N((IFERROR(FIND(検索リスト!$A$1:$A$10,INDIRECT("RC",FALSE)),0)*(検索リスト!$A$1:$A$10<>""))<>0))=SUM(N(検索リスト!$A$1:$A$10<>"")),SUM(N(検索リスト!$A$1:$A$10<>""))<>0)
設定の方法はOR条件の時と同じです
『検索リスト』シートを作成して
上記の数式をコピーして貼り付けて下さい！
先ほどと違って指定した文字列全てを含むセルが強調表示されますね！
文字列を追加する度に強調表示されるセルが絞られる感じです
状況によっては便利かもしれませんね
お役に立てば良いのですが…
おわりに
今回は条件付き書式を使用して文字列を検索、強調表示をする方法と必要な数式を紹介しました。
条件付き書式の設定に使用する数式はセルに入力する数式と若干勝手が違うのでなかなか手間取りました…。
検証もちょっと面倒なので上手く動かすところまででやっとです。
私は経理という仕事柄、データの中から欲しいデータを探すことが多いです。
そして、その大半が文字列による検索です。
この記事では手順に従って設定すれば狙った動きが実現できるよう数式を工夫しました。
読んでくださった方のお役に立てると幸いです！
最後までお付き合い頂き、ありがとうございました！
当ブログはエクセル関数の擬人化キャラクター記事をメインコンテンツとしています！
よろしければこちらの記事もどうぞ～！
コメント
[…] 指定した複数の文字列を含むセルを強調表示する条件付き書式設定（OR条件・AND条件）【エクセルTips】エクセルを使った業務では特定の文字列を含むデータが入ったセルを探す機会に何 […]
突然失礼します。
条件付き書式について悩んでいて、このブログをみつけました。
記述の数式についてですが、どうしても理解できない部分があるので
質問させてください！
SUM関数なのに「*」が入っているのがわかりません。
数式後半の、”A1-A10が空白でない時”との関連性を教えて頂きたいです。
よろしくお願いします。
ネット等で調べてもどうしても出てこず、気になってしまって…
すみません。
コメントありがとうございます！
=SUM(IFERROR(FIND(検索リスト!$A$1:$A$10,INDIRECT(“RC”,FALSE)),0)*(検索リスト!$A$1:$A$10<>“”))>0
この数式では「SUMの結果が1以上であれば、対象セルが検索リストのいずれかを含む」ということが言えます。
つまり、SUM関数の引数は対象セルが検索リストのいずれかを含むかどうかを数値で表現したものが入っています。
「*」について前と後ろにわけて説明しますね。
前の部分（IFERROR～）は、対象セルの文字列からリストの文字列を探して、何文字目にあるのか？
というFIND関数の結果を配列で返しています。
IFERRORで、文字列がない場合にFINDがエラーを返したら0になる仕組みにしています。
リストが全部埋められていない場合、FINDの検索値が「空白」になります。
FINDが空白を探すと1を返します。
これは困る動きです。リストが空白でもSUMの結果が1以上になってしまうからです。
【例】
リストにa,b,cの3つだけ入力されている（のこり7つは空白）
対象セルにはabcという文字列が入力されている
FIND関数は
aを探して1を返し、
bを探して2を返し、
cを探して3を返し。
それ以降は空白を探して1を返します
前半が返す配列
{1,2,3,1,1,1,1,1,1,1}
欲しい配列
{1,2,3,0,0,0,0,0,0,0}
配列を欲しい形にするために後半の式が必要です。
リストに値が入っていればTRUE（1）、空白ならFALSE(0）の配列が得られます。
後半が返す配列
{1,1,1,0,0,0,0,0,0,0}
前半が返す配列と後半が返す配列を掛け算「*」します
{1,2,3,1,1,1,1,1,1,1}*{1,2,3,0,0,0,0,0,0,0}
すると、結果が欲しい配列の形になります。
{1,2,3,0,0,0,0,0,0,0}
SUMで配列内の数値を足し算すると6になり、1以上なので条件付き書式が適用されます。
配列数式なので、なじみがないと理解するのが難しいと思います。
ネットで出てきた数式に疑問を持ち、ご質問頂けたことを嬉しく思います。
まだ足りないようでしたらおっしゃって下さい！
筒井様
さっそく返信くださり、ありがとうございます！！！
今まで自分が使ったことのない数式でしたので、
難しかったですが丁寧にご説明下さったので理解できました(*^^*)
今後、自分のExcelファイルでも使ってみたいと思います。
ありがとうございました！
質問失礼致します。
”RC”は何でしょうか。
ぜひ応用させて頂きたいのですが、なぜRCが必要なのか理解できませんでした。
よろしくお願いいたします。
コメントありがとうございます！
セルの番地にはA1のような「A1形式」とR1C1のような「R1C1形式」があります
後者の「R1C1形式」で自セルを表現すると”RC”となります。
A1セルに”A1”に書くのと同じ意味になります。
私のブログではコピー＆ペーストすればそのまま使えることを重視しているので、
この”RC”の書き方を採用しています。