Excelでこのようなときはどんな関数にすればよいでしょうか

質問

同じブック内で以下のような２シートがあるときに、

（シートＡ）
　　　Ａ　　　Ｂ　　　　Ｃ　　　
１　　あい　　123　　りんご
２　　うえ　　456　　みかん　　
３　　おか　　789　　イチゴ

（シートＢ）
　　　Ａ　　　Ｂ　　　　Ｃ　　　
１　　あい　　123　　めろん
２　　えお　　456　　みかん　　
３　　おか　　789　　イチゴ

横に並んだ３項目が同じものにだけ、（シートＢ）のセルに色を付けたいのです。
（上記の例だと、３行目が３項目とも一致しているということです）
色は横３つのセルにつけられなければ、Ａ列のセルだけでも構いません。
（上記の場合、色をつけたいのはシートＢ「おか」のセル）

列を増やすなど、処理用のデータが必要でも非表示にすればいいので
その点は構いません。
関数や条件付書式などで、なんとかなりますでしょうか？

回答No.1

maruru01 · ベストアンサー

こんにちは。maruru01です。

Sheet2のD列を作業列として使用します。
まず、D1に、

「=AND(IF(ISERROR(VLOOKUP($A1,Sheet1!$A$1:$C$3,2,FALSE)),"",VLOOKUP($A1,Sheet1!$A$1:$C$3,2,FALSE))=B1,IF(ISERROR(VLOOKUP($A1,Sheet1!$A$1:$C$3,3,FALSE)),"",VLOOKUP($A1,Sheet1!$A$1:$C$3,3,FALSE))=C1)」

と入力します。
（D列には、"TRUE"か"FALSE"が表示されます。）
なお、VLOOKUP関数の第2引数の範囲は、Sheet1のデータ範囲を絶対参照（$付き）で指定して下さい。
そしたら、Sheet2のA1からC3（データの一番右下のセル）までドラッグします。
（"A1から"というのが重要です。）
[条件付き書式]で、条件を、

「数式が」
「=$D1」

として、書式を設定します。

お礼

すみません！！m(_ _)m
また違う質問になってしまってますね。
新たな質問として投稿することにしました。
ありがとうございました！！

補足

かんっぺきです！！
まさにしたい事がちゃんとできました。
ありがとうございました！！！

ごめんなさい、ちょこっとだけ補足なのですが、
もし以下のようにシートＡのＡ列に、1か空欄かのデータがあった場合に

（シートＡ）
　　 Ａ　　　Ｂ　　　Ｃ　　 Ｄ　　　　
１　　1　　　あい　　123　　りんご
２　　　　　 うえ　　456　　みかん　　
３　　1　　　おか　　789　　イチゴ

シートＢにも同じようにＡの列を作って、
そこに1か空欄かのデータを入れることもできますか？
横３つが同じならばそれは複数行出てきても、１か空欄かのどちらかです。
１の場合と空欄の場合両方があることはありません。

列B,C,Dのそれぞれのデータが一致するものを判定して
それのA列にあるデータをシートＢにも持ってくる、という感じです。
そんなこともできますでしょうか。

回答No.6

imogasi

既に出ているご回答が相当理解するのに手間が掛かりそう
に思います。それで質問者の希望に無いのですが、VBAを
参考までに載せます。ロジックも初歩的な、総なめ法を使っているので、やっていることは単純で判りやすいと思います。そこがVBAの良いところだと思います。
ぜひ見てください。
Sub test02()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Set sh1 = Worksheets("sheet1")
Set sh2 = Worksheets("sheet2")
'Sheet1とShｅet2の最下行を知る
d1 = sh1.Range("A1").CurrentRegion.Rows.Count
d2 = sh2.Range("A1").CurrentRegion.Rows.Count
'------Ｓｈｅｅｔ１の各々全行について、Sheet2の
'全行に渡って同じ物があるかどうか総なめで比較する
For i = 1 To d1
s1 = sh1.Cells(i, "A") & sh1.Cells(i, "B") & sh1.Cells(i, "C")
For j = 1 To d2
s2 = sh2.Cells(j, "A") & sh2.Cells(j, "B") & sh2.Cells(j, "C")
If s1 = s2 Then
'見つかればSheet1のA列に黄色を付ける
sh1.Cells(i, "A").Interior.ColorIndex = 6
End If
Next j
Next i
End Sub

お礼

ありがとうございます！
また違った方法をお教えくださって勉強になります。
関数の方が使い慣れているため、理解は簡単でした。
でもVBAもチャレンジしてみようとは思っておりますので
その時にはぜひこの例で勉強してみようと思います！

回答No.5

NIWAKA_0

#3です。

私の方法だと、例えば

　　　Ａ　　　Ｂ　　　　Ｃ　　　
１　　あい　 うえ　　おか
２　　あ　 いうえお　　か

みたいな区切り位置が違うだけの時もＨＩＴしちゃいますね。
そういうのが無いデータの場合に使用してくださいｍ（＿＿）ｍ

お礼

あ、そうですね！
でも区切り位置が違うだけのデータは無いので
やってみてもうまくいって感動しただけで、気付きませんでした。。。
どうもありがとうございました！！

回答No.4

comv

こんにちは

例記の範囲で説明します

------シートＡ---------
・Ａ１：Ａ３を選択　挿入　名前　定義　DATA1
・Ｂ１：Ｂ３を選択　挿入　名前　定義　DATA2
・Ｃ１：Ｃ３を選択　挿入　名前　定義　DATA3

------シートＢ---------
・Ａ１：Ｃ１を選択
・書式　条件付き書式　数式が▼
　数式欄に
　=MAX((DATA1=$A1)*(DATA2=$B1)*(DATA3=$C1))
　書式ボタン　パターンタブ　好みの色を設定
・Ａ１：Ｃ１を選択した状態で必要範囲の下行へ
　右ボタンを押したフィルドラッグ　書式のコピー

式で不特定行３列で全ての比較演算が　TRUE　なら
　１＝TRUE　となり
１つでも合致しない列があれば
　0＝FALSE　となります。
その中でＭＡＸ（最大）を抽出する
→　結果　合致する行がある場合１(TRUE)　全ての行で
合致しない場合０(FALSE)となります。

お礼

ありがとうございましたー
やってみましたが、完璧です！
ですが、例では３行しかないのでそれで十分なのですが、
実は実際に使いたかったシートにはデータが2000行くらいあるのです。。。
全部に範囲名をつけていくのは、ちょっと大変・・・

でもそのやりかたは全く考え付きませんでした
勉強になりました。ありがとうございました！

回答No.3

NIWAKA_0

まずはシートＢのＤ列・Ｅ列にチェック用のデータを作りましょう。
（条件付書式では他のシートを参照できないため）
Ｄ列（シートＡのチェック用データ）
=Sheet1!A1&Sheet1!B1&Sheet1!C1
Ｅ列（シートＢのチェック用データ）
=A1&B1&C1

こうなります。
（シートＡ）
　　　Ａ　　　Ｂ　　　　Ｃ　　　　
１　　あい　　123　　りんご
２　　うえ　　456　　みかん　　
３　　おか　　789　　イチゴ

（シートＢ）
　　　Ａ　　　Ｂ　　　　Ｃ　　　　　Ｄ　　　　　　　　Ｅ　　　
１　　あい　　123　　めろん 　　あい123りんご　　あい123めろん
２　　えお　　456　　みかん　　 うえ456みかん　　えお456みかん　　
３　　おか　　789　　イチゴ　　 おか789イチゴ　　おか789イチゴ

あとは条件付書式です。
シートＢの１行目で参照するのはシートＡの１行目だけでいいですか?
それならば[数式が][=$D1=$E1]でお好みの書式を設定してあげればＯＫですね。
カーソルでセル指定すると絶対参照で出てくるので、列番号の前の$マークは消してあげてください。
これを下方向にコピーすればＯＫです。

シートＡの「どこか」と一致する場合、というなら、条件数式を
=MATCH($E1,$D$1:$D$3,0)
としてください。

回答No.2

bucchi-

私の知識では
表外にマークを付ける以外方法はみつかりませんでした。
たぶんそんなことだったらしっとるわと思われるでしょうが…

別のシートか別の欄に

IF関数でIF(A3=A3,0,1)
とういう具合にして
D3に合計を出し　『0』の時は、表外　D3に(列を挿入してA3にでも)IF関数で0の時は☆とかでも…

質問のとおり出来る方法があれば私も知りたいです♪

お礼

それですと、シート間で同じ行にあるものしか
判定できない・・ですよね。たぶん。

でも0か1かで判定して合計を出すというのは、いろんなことに
使えそうです！どうもありがとうございました！！