Excel使いこなしの基礎と応用

ワイルドカードが使えない条件付き書式で部分的な文字列が一致する行全体に色を付ける

使用関数:COUNTIF、AND

文字列の特定の部分が一致するという条件で、行全体に書式を設定する方法です。条件付き書式ではアスタリスクを任意の文字列に代用する指定が使えないので、COUNTIF関数の救けを借ります。状況によってはAND関数にネストした複数の数式を条件に使います。

目次


文字列の部分一致で条件付き書式を適用するには…

▶条件付き書式で部分的な文字列が一致するセルに色を付けるでは、商品名の1部分だけの一致を条件に「強調表示ルール」でセルに書式を適用しました。
ここでは、同じ条件でセルだけでなく、全体に書式を適用させます。

スポンサーリンク

オフィス家具の売上表(下図)で、「」の商品の行にだけ背景色とフォント色を付けたいと思います。色の種類表記は商品名の中に含まれているので、「*白*」というようにアスタリスクを付けたくなりますが、条件付き書式ではワイルドカードは使えません。半角アスタリスクも半角疑問符も文字列の一部としか認識されないのです。
商品名に含まれる「白」を条件に行に書式を付けたい
そこで、ワイルドカードが使えるCOUNTIF関数を利用することにします。

COUNTIF関数で指定文字列の個数を求める

COUNTIF関数は範囲の中に条件を満たすセルが幾つあるかを返します。
関数の引数ダイアログを開いて、数式を組み立ててみましょう。

スポンサーリンク

引数は2つ。「範囲」と「検索条件」です。
範囲」に指定するのは商品名ですから、「E2」セルをクリック。F4キーを2度押して「$E2」と絶対列参照にしておきます。E列を固定させて、行を可変させたということです。
検索条件」には「"*白*"」とダブルクォーテーションで括ったアスタリスク付きの文字列を指定します。
COUNTIF関数の引数を指定
数式は「=COUNTIF($E2,"*白*")」となります。文章に置換えると「商品の文字列の中に白が含まれていたら、その個数を返す」。この数式をコピーしておきます。

「範囲」に指定したのは1つのセルですから、「白」が含まれていれば「1」と返り、含まれていなければ「0と返ります。
これを利用した数式を条件にして、条件付き書式を設定していくわけです。

組み立てた数式を条件に指定する

データ表の1行目を選択して、「ホーム」→「条件付き書式」から「新しいルール」をクリック。
条件付き書式の新しいルールをクリック
新しい書式のルール」のルールの種類で、「▶数式を使用して、書式を設定するセルを決定」を選択。ルールの内容のボックスに先ほどコピーしたCOUNTIFの数式をペーストするか、手打ちで入力します。最後に右下の「書式」をクリック。
新しい書式のルールで種類を「数式を使用して、書式を設定するセルを決定」にしてCOUNTIFの数式をペースト
セルの書式設定」で背景色の塗りつぶしやフォント色など、好みの書式を設定します。
セルの書式設定で背景色・フォント色を指定
全て「OK」を押してダイアログを閉じ、表の1行目の右端セルのフィルハンドル(右下角の小さな突起)をダブルクリックしてフィルコピーします。
最初の行範囲をフィルコピー
単純なコピーになっても慌てずに、オートフィルオプションボタン(右下に表示されているアイコン)をクリックして、「書式のみコピー(フィル)」を選択して下さい。
オートフィルオプションで書式のみコピーを選択
「白」の商品の行全体に書式が適用されました。
「白」の商品の行全体に書式が適用された

AND関数で複数の条件を指定する

行に背景色やフォント色が付いたので、ひとまず成功ですが、よく見ると「白木」という商品の行まで色が付いています。「白木」は材質名で、色の「白」とは別物ですので、これを除外する必要があります。
書式の適用から除外すべき行がある

「白木」の商品を条件付き書式から除外するための数式を組み立てるのは簡単です。最初の数式を少しアレンジするだけです。
COUNTIF($E2,"*白*")」 を「COUNTIF($E2,"*白*")=0」 と修正。「白木という文字列がE2セルにあるか数え、その答えが0=(セルに白木という文字列がない)」
後は、この2つの数式をAND関数にネストするだけです。

「ホーム」タブ→「条件付き書式」から「ルールの管理」をクリック。
条件付き書式の「ルールの管理」をクリック
ルールの編集」をクリック。
ルールの編集をクリック
数式を「=AND(COUNTIF($E2,"*白*"),COUNTIF($E2,"*白木*")=0)」と修正。ピンクの部分を書き足します。
数式を修正

AND関数は指定した全ての条件が満たされた時にTRUEを返します。
「=AND(COUNTIF($E2,"*白*"),COUNTIF($E2,"*白木*")=0)」と条件指定したということは、「セルに白という文字列があり白木という文字列が無いという2条件が満たされれば」書式が実行されるということです。

全て「OK」して、ダイアログを閉じ、結果を確認します。
条件付き書式の適用結果を確認
▶条件付き書式とCOUNTIF関数の別例|重複するデータの洗い出し方法
▶条件付き書式とWEEKDAY関数・MATCH関数で土・日・祝日を塗り分け


高機能の表計算ソフトExcelを活用すれば、仕事や私生活で、今あなたが必要とするいろいろなモノが作れます。その作成手順とデザイン、アイデアのヒントまでを可能な限り詳細にわかりやすくナビします。基本操作や関数、グラフに関する解説記事とともにご利用下さい。