Excelの条件付書式とVlookupを組み合わせて

質問

いつもお世話になっております。

『残高報告3月末』と『残高報告9月末』というフォーマットを同じくするシートがあります。
いずれも一行一レコードで、「案件名」「残高１」「残高２」「残高３」という4項目についてデータが存在します。
この中の「残高３」という項目について両シート間で比較を行い、値が違っている場合は『残高報告9月末』のセルの文字色を青に変える、ということを条件付書式機能を用いて設定しようとしています。

レコードの順番が変わらなければセルの位置をして指定して比較を行えばよいものと思うのですが、順番は変わる可能性があるため、
『「案件名」が同じものを探して、そのレコードの「残高３」の値同士を比較する』ということが必要になっています。
Vlookupを使えばできるように思うのですが、具体的にどう記述すればいいのかまでたどり着けず…教えて頂けますと助かります。

宜しくお願い致します。

回答No.3

abe_onesel · ベストアンサー

A列～D列に案件名～残高３があるものとして、
残高報告9月末シートのE2に
=VLOOKUP(A2,残高報告3月末!$A:$D,4,FALSE)
とすれば、同じ案件名の３月末の残高がでると思います。

それを参考にして、D列に条件付き書式を数式で下記のようにします。
=D2<>VLOOKUP(A2,INDIRECT("残高報告3月末!$A:$D"),4,FALSE)

※条件付き書式だと別シートを参照できないので、INDIRECT関数を使っています。

条件付き書式について知っているものとして回答していますので、
そもそも条件付き書式がわからないのであれば、別途調べて見てください。

お礼

少ない手間で実現できました！ありがとうございました。

回答No.5

myRange

他のシートを参照する場合、そのセル範囲に”名前”を付けると参照できますので、作業列は不要になります。

回答２の表を使わせていただくとして、、

残高報告3月末（Sheet１)
残高報告9月末（Sheet２)

●Sheet1（3月末）の表の全て選択する
案件がこれからも増えるなら、余分な行まで選択する
例えば、Ａ１～Ｄ５６７　（５６７行選択）

●左上の名前ボックスに、検索範囲　と入力する
これで、Ａ１～Ｄ５６７に、"検索範囲"という名前が付く

●Sheet2（９月末）のシートを選択する
条件付書式を設定したセルを選択する
例えば、Ｄ２～Ｄ９９９

●条件付書式で、
式、
式ボックスに

　=D2=VLOOKUP(A2,検索範囲,4,0)

を入力して、色を設定する

尚、名前を付けるときは
挿入メニュー　＞　名前　＞　定義
と進んでいっても付けられます。

以上です。

お礼

丁寧なご解説、ありがとうございました！ポイント付与できず、申し訳ありません。

回答No.4

135ok

sheet1の表を選択して、名前ボックスで名前をつければ参照できます。

下記添付は、表を表１として作業しています。

お礼

わざわざ添付図までありがとうございました。ポイント付与できず、申し訳ありません。

回答No.2

tom04

こんにちは！
一例です。

条件付書式は別Sheetを参照できませんので
↓の画像で説明させていただくと

Sheet2（今回は9月末分とします）に作業用の列を設けて対処するようにしてみました。

Sheet1（3月末分）のA列とD列を全て選択します
コピー　→　Sheet2のE1セルを選択　→　形式を選択して貼り付け
→　リンク貼り付け　にチェックを入れ、OK

この場合空白セルがあると「0」と表示されると思いますので
メニュー　→　ツール　→　オプション　→　「表示」　→　「ゼロ値」のチェックを外しておきます。

そして、Sheet2のD2セルからD列を適当なセルまで範囲指定します。

メニュー　→　書式　→　条件付書式　→　「数式が」を選択し
数式欄に
=D2<>VLOOKUP(A2,$E$2:$F$1000,2,0)
という数式を入れ、→　書式　→　好みの色を選択　→　OK

これで数値が違うセルだけに色が付くはずです。

尚、条件付書式の数式は1000行まで対応できるようにしていますので、
データ量によって範囲はアレンジしてみてください。

以上、当方使用のExcel2003での回答です。
参考になれば幸いですが、
他に良い方法があれば読み流してくださいね。m(__)m

お礼

丁寧なご解説、ありがとうございました！

回答No.1

DIooggooID

VLOOKUP 関数を使用するには、検索対象（案件名）が、昇順に並べられていないと正しく検索できませんが、それは大丈夫ですか？

補足

そうでした、VLookupにはその制限があるんでしたね…。

関係ないかと思い記述が抜けておりましたが、実は案件名以外に「グループ１」「グループ２」という項目もあり、資料としての表示上、そちらを第一キー、第二キーとして優先的にソートさせているのです。
よって、案件名はソート対象ではあるものの、第三キーなので厳密に照準にはなっておりません…。

そうすると、Vlookupは使えないということですよね。
他に方法はないものでしょうか…。