Introduction
Ver. all 2013 2016 2019 365
エクセルの条件付き書式でVLOOKUP関数を使用するやり方です。数式を作成する際にいくつかポイントが存在します。
数値を一致させる場合と、文字列を一致させる場合で作成する式が異なるので分けて説明していきます。
また、最後に２つの表で氏名を一致させつつ数値を比較する例を紹介します。
ちなみに、複数の条件に色を付けたい場合は、状況によってはVLOOKUP関数を使用するよりもCOUNTIF関数を使用した方が簡単な場合があります。
下のリンク先をご覧ください。
幾つか知らないといけない事があるにゃ
数値を一致させる場合のやり方
最初は、下のように右に用意した複数の数値データの中から、一致するデータを塗りつぶしてみます。
ちなみに、今回の例のように参照先のデータが３つしかない場合は、VLOOKUP関数を使用する必要はありません。
下のように一件のデータと数値が同じになった条件を設定してから、塗りつぶし色を設定し、
それを件数分だけ追加すれば終了です。
実際に使用する時は、一件ずつ条件追加するのが面倒になるほど数が多いデータを参照する場合に有効です。
では操作です。
まず条件付き書式を設定するデータ範囲を選択して、
［ホーム］タブ → ［条件付き書式］ →
一覧から［新しいルール］を選びます。
表示された画面で、［数式を使用して、書式設定するセルを決定］を選びます。
その後、②の場所で数式を入力し、③の［書式］をクリックして検索結果のセルを塗る色を指定します。
ポイントは設定する数式ですね。今回の場合は下のようになります。
=VLOOKUP(C2,$G$2:$G$4,1,FALSE)
VLOOKUP関数・・・他の表から対応データを探し出し、一致した行中のデータを求める関数
VLOOKUP（検索値,範囲,列番号,検索の型［省略可］）
- 検索値・・・検索する値（一致させたい場所のセル参照や値、文字列）を指定
- 範囲・・・検索する（一致させる）相手のデータ全体を指定
- 列番号・・・「範囲」で指定したデータの、左から何列目と「検索値」を一致させるかを指定
- 検索の型・・・完全一致させたい場合は「FALSE」、近似値で一致させたい場合は「TRUE」を入力。省略した場合は「TRUE」になる。（FALSEは「0」、TRUEは「1」でも可）
実際に入力する時は、［関数の挿入］ダイアログボックスは使用できないので、直接入力して式を作成します。
では、数式のポイントです。下の画像をご覧ください。
［検索値］の引数には、最初に選択したデータ範囲の左上端を指定します。
この場所は「$」記号を付けて絶対参照にしてはいけません。
※マウスで選択すると、自動で絶対参照になってしまうので、手入力しましょう。
次の［範囲］の引数の場所は、参照先データ範囲を絶対参照で指定します。
※この場所はマウスで選択するのが楽です。
［列番号］には「1」、［検索の型］は完全一致させたいので「false」か「0」を入力します。
数式の作成が終わったら、右下の［書式］ボタンをクリックして色などを設定します。
今回は、［塗りつぶし］タブに移動して「オレンジ」色にしてみました。
［OK］を押し、前の画面に戻って繰り返し［OK］。
これで設定完了です。
右側で指定したデータに色が塗られました。
文字列を一致させる場合のやり方
条件付き書式で文字列を一致させるには、やり方を数値の場合と変更する必要があります。
下の表で説明します。左の表で、右の３名と一致するデータを塗りつぶしてみます。
条件付き書式を設定するデータ範囲を選択して、
先ほどと同じ手順で、条件付き書式の数式を入力する画面を表示します。
今回入力する式は下のようになります。
=NOT(ISNA(VLOOKUP(B2,$D$2:$D$4,1,FALSE)))
「数値」を使用する時よりも、関数が多くなります。
VLOOKUP関数のポイントに関しては先ほどの数値と一致させる場合と同じなので、割愛します。
VLOOKUPを囲むようにに使用する関数について簡単に説明しておきます。
条件付き書式は、数式の結果が「TRUE」の時に書式が適用されます。
「文字列」が結果として返された場合は「TRUE」と認識してくれません。
ということで、「文字列」として認識してくれないのであれば、「文字列」でない場合を認識して、その結果を逆にすれば良いのでは・・・。
今回の場合は検索結果が異なっていた場合は「#N/A 工ラー」が返されることになります。
そこで「#N/A 工ラー」を「TRUE」にするISNA関数を使用します。
参照先が「#N/A 工ラー」の場合は「TRUE」を返し、そうでない場合は「FALSE」を返す
これでデータが一致していない場所に「TRUE」が返されることになります。その結果をNOT関数で反転させます。
結果を反転する
これによって、一致した文字列を「TRUE」として条件付き書式に認識させることができます。
数式ができたら、右下の［書式］ボタンで書式を設定。今回はオレンジで塗っています。
今回の結果は下のようになります。
２つの表で氏名を一致させつつ数値を比較する例
最後に、下の２つの表で、１学期より２学期の方が点数が高かったデータを塗りつぶしてみます。
２つの表の氏名のデータや順番がバラバラになっていますね。VLOOKUPが活躍しそうです。
まず、色を塗りたいデータ部分を選択して、
［ホーム］タブ → ［条件付き書式］ →
一覧から［新しいルール］を選びます。
表示された画面で、［数式を使用して、書式設定するセルを決定］を選びます。
その後、②の場所で数式を入力し、③の［書式］をクリックして検索結果のセルを塗る色を指定します。
今回設定する数式は下のようになります。
=VLOOKUP($E3,$B$3:$C$12,2,FALSE)<$F3
実際に入力する時は、［関数の挿入］ダイアログボックスは使用できないので、直接入力して式を作成します。
VLOOKUP関数で、２学期と１学期の氏名を一致させ、１学期の点数を引っ張ってきます。
その点数と2学期の点数を演算子「＜」で比較するという感じ。
注意すべきなのは、［検索地］の「E3」が「$E3」となり、比較する点数セルの「F3」も「$F3」と列番号が固定されているところです。
これにより、選択したデータ行全体に色を付けることができます。
もし、氏名だけに色が付けば良いのであれば、「E3」「F3」のように「$」記号は付けずに通常のセル参照にします。絶対参照にしてはいけません。
行全体に色を付ける方法の詳しい説明は、下のリンク先にありますので、興味のある方はご覧ください。
数式の作成が終わったら、右下の［書式］ボタンをクリックして色などを設定します。
今回は、［塗りつぶし］タブに移動して「オレンジ」色にしてみます。
［OK］を押し、前の画面に戻って繰り返し［OK］。
これで設定完了です。
どうやら、３人の成績が良かったようですね。
まとめ
いくつかポイントがありました。最後にまとめてみます。
VLOOKUP関数のポイント
- ［検索値］の引数は、最初に選択したデータ範囲の左上端を指定して、絶対参照にしない
- ［範囲］の引数は、参照先データ範囲を絶対参照で指定する
文字列を使用する場合
- VLOOKUP関数を、ISNA関数とNOT関数で囲む
その他にも
- ［関数の挿入］画面が使用できないので、「=」から手入力する
- 式の中に比較演算子が使用できる
- 行全体に色を付けたい場合は、列番の前だけに「$」記号を付ける
この辺を押さえておきましょう。
なんだか細かい注意点が多くて明日には忘れるにゃ・・・。