エクセル2003での照合について教えてください。

質問

エクセル2003での照合について教えてください。
こんにちは　いつもお世話になっています。

Sheet1とSheet2に名前、科目（政治、経済、社会）を項目とする表があります。

Sheet1
名前 政治 経済 社会
新井 　　A 　　B 　　C
伊藤 　　B 　　A 　　A
上田 　　A 　　A 　　B

Sheet2
名前 政治 経済 社会
新井 　　B 　　B 　　C
石井 　　C 　　A 　　A
上田 　　A 　　A 　　A

名前は読みがなの昇順にしてあります。
この状態でSheet2においてSheet1と変化したデータセルの背景色を赤色にしたいのです。
この例では、新井の「政治」のBと、上田の「社会」のA、それぞれのセル背景色を赤色にしたいです。
また、名前の伊藤が無くなり、石井が入っています。この場合は石井のデータ全て（C,A,A)の背景色を赤色にしたいです。
わかりにくい表現で恐縮です。
情報不足があったら教えてください。

回答No.8

kagakusuki · ベストアンサー

回答番号:ANo.2です。
　申し訳御座いません、条件付き書式を設定するセル番号と、コピー元のセル番号に、誤りが御座いました。
　説明文中において、B1と記されている箇所は、全てB2セルの間違いです。
　ですから、最初に条件付き書式を設定するセルは、B1セルではなくB2セルです。
　又、条件付き書式を設定した後にコピー元とするセルも、B1セルではなくB2セルです。

>データセルがすべて「政治」になりました。
私の操作ミスかもしれません。

　その不具合の原因はおそらく、貼り付けを行う際に、途中の操作を抜かして、形式を選択する事なく貼り付けを行われたか、或いは、形式を選択される際に間違えて、「書式」ではなく、「値」と記されている箇所にチェックを入れられたためだと思われます。

>名前行は追加、削除があります。

　すると、

>名前の伊藤が無くなり、石井が入っています。この場合は石井のデータ全て（C,A,A)の背景色を赤色にしたいです。

という様に、入力した名前が間違っていたり、名前の入力漏れがあった場合に対応するのではなく、単に、名前の並ぶ順序が変わっていてもSheet1に存在しない名前である場合に、セルのパターンを赤くするという事なのでしょうか？
　又、名前の入力漏れがあった場合には、対応しなくても良いという事なのでしょうか？

　それでしたら、B2セルの条件付き書式の設定において、「条件付き書式の設定」ウィンドウの右端の欄に入力する数式は、次の様になります。

=IF(COUNTIF(INDIRECT("Sheet1!A:A"),INDEX(2:2,1)),HLOOKUP(INDEX(B:B,1),INDIRECT("Sheet1!B:D"),MATC(INDEX(2:2,1),INDIRECT("Sheet1!A:A"),0),0)<>B2,INDEX(2:2,1)<>"")

お礼

kagakusuki　様　ありがとうございました。
説明が至らず申し訳ありませんでした。
御陰様で解決しました。
名前欄の人間の異動があるためデータ部も異動するという意味だったのです（この表現もどうかと思いますが）
至らぬ言葉の裏を読み取っていただいて助かりました。
（数式のmatchのHは加えさせていただきました）
簡単で恐縮ですが、お礼申し上げます。

回答No.7

imogasi

#6です。
お礼の部分に関して
設定が　Option Explisitとなっていて（宣言部、VBE画面の上部のはじめ）
定数定義(宣言) Dim d As Inreger
が無かったからと思います。プロシージャー内に入れてみてください。

お礼

imogasi 様　ありがとうございました。
新規の標準モジュールでoption explicitの無い状態で、作っていただいたコードを貼り付け、実行したところ、

実行時エラー’91：
オブジェクト変数またはwithブロック変数が設定されていません。

となりました。デバッグでは
y = sh1.Range("a1:A100").Find(x).Row '氏名が同じ行を見つける
の部分のようです。

ご教授いただけないでしょうか。

補足

imogasi 様　ありがとうございました。
取りあえず、条件付書式で解決しました。
VBAでも勉強したかったんですが
教えていただいたもので勉強してみます。
簡単で恐縮ですが、お礼申し上げます。

回答No.6

imogasi

条件付書式では式がやや複雑になるので、VBAでやると、
ロジックは素直なものになります。
氏名行が見つかるか
見つからなければB-E列色づけ
見つかったら、その行でB-D列について両シートデータ比較、同じでなければ色づけ
Sub test01()
Dim sh1, sh2 As Worksheet
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
d = sh2.Range("a65536").End(xlUp).Row 'Sheet2最下行
'MsgBox d
'--
For i = 3 To d '上から各行繰り返し
x = sh2.Cells(i, "a")
y = sh1.Range("a1:A100").Find(x).Row '氏名が同じ行を見つける
On Error GoTo err1
'MsgBox y
For j = 2 To 4 'B-D列について比較
If sh2.Cells(y, j) = sh1.Cells(i, j) Then
Else
sh2.Cells(y, j).Interior.ColorIndex = 6
End If
Next j
GoTo p2
err1: '見つからない行
'MsgBox i
sh2.Range("a" & i & ":d" & i).Interior.ColorIndex = 8
p2:
Next
End Sub
Sheet2を中心にして照合してます。
弱点はデータが変化するとやり直しに鳴ることです。

お礼

imogasi 様　ありがとうございました。
　よろしければ御回答下さい。
実行したところ「コンパイルエラー　変数が定義されていません」となりました。
d = sh2.Range("a65536").End(xlUp).Row 'Sheet2最下行
の部分のようです。
　操作ミスかもしれませんが、ご教授いただけないでしょうか。
書き忘れましたが、名前行はSheet1で追加や削除を経て、Sheet２に移行しています。
他のご回答者様のような条件書式では対応できないようです。
よろしくお願いします。

回答No.5

MackyNo1

たびたび間違えて申し訳ありません。

お分かりのことと思いますが、訂正で示した数式のOR関数の後にかっこが抜けていましたので追加してください。

=OR(INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN()))=B2,INDIRECT("Sheet1!"&ADDRESS(ROW(),1))=$A2)

ちなみに、私の提示した数式は、同じレイアウトの2つのシートの同じセル位置のデータ同士を比較するものですから、行の削除や追加を行った場合は対応できませんので注意して下さい。

お礼

MackyNo1　様　ありがとうございました。
　名前行の追加、削除があると対応できないとのご指摘を頂き、書き忘れていたことに気付きました。
申し訳ありませんでした。その場合は条件書式は使えないと理解しました。
この書式で勉強させていただきます。
簡単で恐縮ですが、お礼申し上げます。

回答No.4

MackyNo1

No3の回答がANDとORが間違えていました。

正しくは、B2セル以下のデータ範囲を選択して以下の数式を設定してください。

=ORINDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN()))=B2,INDIRECT("Sheet1!"&ADDRESS(ROW(),1))=$A2)

回答No.3

MackyNo1

Sheet2のB2セル以下を選択し、「書式」「条件付き書式」で「数式が」にして背景色を赤に設定して下さい。

=AND(INDIRECT("Sheet1!"&ADDRESS(ROW(),COLUMN()))=B2,INDIRECT("Sheet1!"&ADDRESS(ROW(),1))=$A2)

回答No.2

kagakusuki

以下の操作を行って、Sheet2のB1～D4の範囲にあるセルに条件付き書式を設定されると良いと思います。

Sheet2のB1セルをクリックして選択
　　↓
メニューの[書式]をクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ウィンドウの左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
「条件付き書式の設定」ウィンドウの右端の欄に次の数式を入力

=OR(HLOOKUP(B$1,INDIRECT("Sheet1!B:D"),ROWS($1:2),0)<>B2,$A2<>INDIRECT("Sheet1!A"&ROWS($1:2)))

　　↓

「条件付き書式の設定」ウィンドウの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ウィンドウの[パターン]タグをクリック
　　↓
赤色の四角形をクリック
　　↓
「セルの書式設定」ウィンドウの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ウィンドウの[OK]ボタンをクリック
　　↓
Sheet2のB1セルにカーソルを合わせてマウスを右クリック
　　↓
現れた選択肢の中にある[コピー]をクリック
　　↓
Sheet2のB2にカーソルを合わせて、マウスの左ボタンを押した後、左ボタンを押したまま、D4セルにカーソルを移動させてから、ボタンを放す事により、Sheet2のB2～D4の範囲を選択
　　↓
選択範囲を表す黒い太枠の内側にカーソルを合わせてマウスを右クリック
　　↓
現れた選択肢の中にある[形式を選択して貼り付け]をクリック
　　↓
現れた「形式を選択して貼り付け」ウィンドウの「貼り付け」欄の[書式]と記されている箇所をクリックしてチェックを入れる
　　↓
「形式を選択して貼り付け」ウィンドウの[OK]ボタンをクリック

お礼

kagakusuki　様　ありがとうございました。
丁寧に教えていただいて助かりました。
B1セルは「政治」の項目のせいか、データセルがすべて「政治」になりました。
私の操作ミスかもしれません。
私の記入漏れがありました。名前行は追加、削除があります。
ご迷惑をおかけしましてすみません。
簡単で恐縮ですが、お礼申し上げます。

回答No.1

mu2011

一例です。
ご例示のセル範囲で回答します。
（１）Sheet1のデータ範囲を選択→ctrl＋F3押下→新規作成→仮に名前をaaaとします。
（２）Sheet2のA2:D4を選択→条件付き書式設定→数式がを選択、=ISERROR(VLOOKUP($A2,aaa,1,FALSE)→書式→パターンタブで赤色を選択
（３）追加→Sheet2のB2:D4を選択→条件付き書式設定→数式がを選択=B2<>VLOOKUP($A2,aaa,COLUMN(),FALSE)→書式→パターンタブで赤色を選択

お礼

mu2011　様　ありがとうございました。
（２）の作業で「数式中に対応する括弧がありません」とエラー表示されました。
私の操作ミスかもしれません。
教えていただいた書式で勉強させていただきます。
こちらの記入漏れがありました。名前行は追加、削除されて、Sheet2になります。
すみませんでした。
簡単で恐縮ですが、お礼申し上げます。