excelの重複について

質問

顧客情報を管理しています。

A列～住所
B列～名前
C列～空白（必要に応じ○を入力）
D列～空白or重複

(1)、順に入力し同じ住所と名前の重複があればセルに色が付く方法ってありますか？
住所のみ一致や名前のみ一致の時は色は付けません。

(2)、さらに上記の条件で同じ住所と名前があればセルに色を付けC列のセルに○が入力されA列B列C列すべてが重複したらD列に「重複」を表示できますか？

条件付き書式や関数等の組み合わせいいですが、初心者なのでマクロは使用できません。
また抽出したりする工程は不要です。
よろしくお願いします。

例

A列 : B列 : C列 : D列
住所 : 名前:チェック:
1 : 1条東1丁目 : 田中: ○
2 : 2条東2丁目 : 鈴木: ○--------------2条東2丁目の鈴木は2つあるので色を付ける。
3 : 3条東3丁目 : 竹田: ○--------------3条東3丁目の竹田は2つあるので色を付ける。
4 : 1条東1丁目 : 小林:
5 : 2条東2丁目 : 鈴木 : ○ 重複-----2条東2丁目の鈴木に○が2つ付いたので「重複」と表示
6 : 3条東3丁目 : 竹田-----------------3条東3丁目の竹田は2つあるので色を付ける。

回答No.1

MackyNo1 · ベストアンサー

＞1)、順に入力し同じ住所と名前の重複があればセルに色が付く方法ってありますか？
住所のみ一致や名前のみ一致の時は色は付けません。

A2セルからB列のデータ範囲を選択し、数式を使った条件付き書式で以下の式を入力し、「書式」から塗りつぶしを指定します。

=SUMPRODUCT(($A$2:$A$100=$A2)*($B$2:$B$100=$B2))>1

(2)、さらに上記の条件で同じ住所と名前があればセルに色を付けC列のセルに○が入力されA列B列C列すべてが重複したらD列に「重複」を表示できますか？

D2セルに以下の式を入力してください。

=IF(SUMPRODUCT(($A$2:$A$100=$A2)*($B$2:$B$100=$B2)*($C$2:$C$100=$C2)*(C2="○"))>1,"重複","")

お礼

敏速な対応ありがとうございます。
早速試したところ希望通りに出来ました。

「=SUMPRODUCT(($A$2:$A$100=$A2)*($B$2:$B$100=$B2))>1」を入力したら空白のセルも塗りつぶしになりました。文字入力して重複時に色付けしたいのですが出来るのでしょうか？