エクセルで、２列以上合致する重複をチェックする

仮に顧客名簿を作っているとします。

岐阜県に「山田花子」さんがいて、長崎県にも「山田花子」さんがいる状態は、構いません。
が、同じ岐阜県に「山田花子」さんが２回出てくる場合はまずいです。
それは打ち間違いである可能性が高いので。
そういうデータだけ抽出して、目で生年月日などを確認して、別人かどうか確かめたいです。

そこでA列に「県名」、C列に「姓名」が入っているとして、
「A列の値と、C列の値が両方とも合致している場合だけ、背景に色を付ける」
というようなことをしてチェックしたいです。

あるいは、A列＝県名、C列＝姓名、G列＝生年月日の三つの列の値が、
すべて一致しているデータもチェックできるなら、その方法も知りたいです。


ネット上で検索したところ、２列以上の値で重複チェックをすると、
「どちらか片方が重複しているもの」すべてが色づけされてしまい、だめでした。

（この場合、県名だけで細かい住所までは入っていないと仮定してください。）
（また、生年月日をちょこっと打ち間違えたり、名前の漢字だけ打ち間違えたりしている可能性があるので、
A列とC列が同じものを出させて、G列を目で確認する、とか
A列とG列が同じものを出させて、C列を目で確認する、ということが必要です。
　例　　　岐阜県　山田花子　1980年5月10日
　　　　　岐阜県　山田花人　1980年5月10日
↑こういう場合、多分同一人物の名前だけちょこっと打ち間違えて、重複しているので、
A列とC列だけでまず出させてから、目でG列を確認するというようなことがしたいのです。）

No.9 回答者： tom04 回答日時： 2017/02/15 19:28

何度もごめんなさい。

No.8でせっかく作業列に「TRUE」または「FALSE」を表示させているので
条件付き書式の数式は
=$Z2
だけで大丈夫です。

どうも失礼しました。m(_ _)m

No.8 回答者： tom04 回答日時： 2017/02/15 14:06

No.2・3・6です。

＞しかし実際には3000列ぐらいあるので、すごく遅いです…
列ではなく行では？

行数が3000行位だという場合は
どこか使っていない列（仮にZ列とします）を作業用としてみてはどうでしょうか？
まずZ2セルに
=IF(A2="","",COUNTIFS(A:A,A2,C:C,C2)>1)
という数式を入れます。
これを3000行程度下へフィル＆コピーする訳ですが、マウスで下へコピーするのは大変なので
隣りの列Y列かAA列の2行目に
「1」を入力　→　メニューバーの右側にあるして向き矢印（↓のアイコン）をクリック　→　連続データの作成　→　「列」を選択　→　停止値の欄に3000とか5000とかこれ以上のデータはないというくらいの数値を入力しOK
そしてZ2セルのフィルハンドルでダブルクリック　→　連続データを消去

そして色を付けたいセル（A2以降）を範囲指定　→　条件付き書式　→　新しいルール　→　数式を使用して・・・　→　数式欄に
=$Z2=TRUE
という数式を入れ　→　書式から好みの塗りつぶしの色を選択しOK

※　作業列が目障りであれば非表示にするなりしてみてください。

未検証ですがこれではどうでしょうか？m(_ _)m

No.7 回答者： yuji3690 回答日時： 2017/02/15 10:56

AA列というのはZ列の右側にありますよ。

空いている列が分からなかったのでAA列と仮に決めて入力しましたが、
仮にE列があいているのであれば、AAをEと置き換えて（つまりE列に）入力してください。
E列は埋まっているけどH列なら空いてるというのであれば、AAをHと置き換えて（つまりH列に）入力してください。
式の中身はそのままでいいので、A,C,G以外の列ならどこでも構いませんよ。

No.6 回答者： tom04 回答日時： 2017/02/14 08:38

No.2・3です。

①簡単に、「A列とC列が一致している物を抽出する」だったら、どのような式になるでしょうか？
について
条件が一つになったので条件付き書式の数式を↓に変更してみてください。
=COUNTIFS($A$2:$A$1000,$A2,$C$2:$C$1000,$C2)>1

前回は列全体を範囲指定したいたので、動きがかなり遅かったと思います。
とりあえず1000行程度に範囲を限定してみました。

②式の中の「$A」というのは何を表しますか？
について
No.5さんが説明してくださっている通りです。
列だけを絶対参照させています。そうしないと（相対参照のままだと）A列しか色がつかないはずです。
範囲指定した行全体に色を付けたい場合はこのように絶対参照にします。
※　列全体の場合は行番号の前に「$」マークを付けます。

③余談ですが「＞１」というのは何を表しますか。
COUNTIFS関数の結果で、A・C列で重複がある場合は必ず2以上になります。
その判断をしています。
すなわち該当する行が「TRUE」となるので、条件付き書式が適用されます。m(_ _)m

この回答へのお礼

再度、ありがとうございます。
頂いた式を変えて使って見たら、うまくいきました！！

２つの列が同一であるのを反転させて、３つ目を目で確認、
これを３回繰り返せばいいわけです。

しかし実際には3000列ぐらいあるので、すごく遅いです…。
色を反転させたセルを下の方にずっと目で確認していくとき、とても遅くてスクロールしにくいぐらい。
仕方ないですが。

でも、とてもありがとうございます！

お礼日時：2017/02/15 09:42

No.5 回答者： yuji3690 回答日時： 2017/02/13 23:27

①同じ都道府県で同じ名前の人

②同じ都道府県で同じ誕生日の人
③同じ都道府県で同じ名前で同じ誕生日の人
をそれぞれチェックしたいわけですね。

※$を付けているのは、他のセルにコピーした時にその部分が自動で変更されないようにです。
　A1は相対参照、＄A$1は絶対参照といいます。A$1や＄A1という風に行/列だけ固定させることも可能です。

仮に1～100行目にデータがあるとして

同じ都道府県で同じ名前の人のチェックをAA列に表示させる場合の例
AA1=IF(SUMPRODUCT((A$1:A$100=A1)*1,(C$1:C$100=C1)*1)=1,"","同名あり")
A列がA1と同じでC列がC1と同じデータが1つしかないなら空白を、複数あるなら同名ありと表示する。

同じ都道府県で同じ生年月日の人のチェックをAB列に表示させる場合の例
AB1=IF(SUMPRODUCT((A$1:A$100=A1)*1,(G$1:G$100=G1)*1)=1,"","同生年月日あり")
A列がA1と同じでG列がG1と同じデータが1つしかないなら空白を、複数あるなら同生年月日ありと表示する。

同じ都道府県で同じ名前で同じ生年月日の人のチェックをAC列に表示させる場合の例
AC1=IF(SUMPRODUCT((A$1:A$100=A1)*1,(C$1:C$100=C1)*1,(G$1:G$100=G1)*1)=1,"","同名同生年月日あり")
A列がA1と同じでC列がC1と同じでG列がG1と同じデータが1つしかないなら空白を、複数あるなら同名同生年月日ありと表示する。

チェック用の列を複数必要としますが、確認したい内容に応じてフィルタを切りかえれるので良いと思いますよ。
データの量が多い場合、色付きだけで比較しようとすると見比べるのにスクロールが大変になる可能性があるので、チェックの時には要チェックデータのみの表示にできる方が比べ易いかと。

使う場合はデータの範囲に応じてA$1:A$100等の行番号を調整してくださいね。
（列全体にすると重くなると思います）
同名同生年月日あり等の表示が長くて嫌であれば、○でも△でも好きな表示に変えてください。

この回答へのお礼

ありがとうございます！
$について、そういえばフィルハンドルで下にずるっとコピーしたときに、
勝手に数字が動いて困ることがありました。スカラをつければいいんですね。
ただ、すみませんAA列というのがどの列のことだか分からず、試せませんでした。すみません。

お礼日時：2017/02/15 09:27

No.4 回答者： mike_g 回答日時： 2017/02/13 22:41

》 こういう場合、多分同一人物の名前だけちょこっと

》 打ち間違えて、重複しているので、
「こういう場合」が、「一文字だけ異なる場合」なら
山田花子/山田花人
だけでなく、
山田花亜、山田花以、山田花呂、山田花葉、山田花似、…
山田亜子、山田以子、山田呂子、山田葉子、山田似子、…
のように、膨大になるはずだけど、ソコントコどう考えますか？

この回答へのお礼

それが質問文の以下の部分デス
>A列とG列が同じものを出させて、C列を目で確認する、ということが必要です。

お礼日時：2017/02/14 23:58

No.3 回答者： tom04 回答日時： 2017/02/13 22:17

No.2です。

もう少し短い数式で大丈夫だと思います。
=OR(COUNTIFS($A:$A,$A2,$C:$C,$C2)>1,COUNTIFS($A:$A,$A2,$G:$G,$G2)>1)

に変更してください。m(_ _)m

この回答へのお礼

わあ！わざわざエクセルの見本をつくって頂いてまで、ありがとうございます！！

が、自分の質問が未整理過ぎて、質問している部分と数式のどこが対応しているかつかめなくなってしまいました。
（すみません、これは完全に私のせいです。）

①簡単に、「A列とC列が一致している物を抽出する」だったら、どのような式になるでしょうか？

②式の中の「$A」というのは何を表しますか？「$」の部分に、私が実際に使っているシートの、対象の行の数字を代入して使う、ということではないですよね。
③余談ですが「＞１」というのは何を表しますか。

お礼日時：2017/02/13 22:45

No.2 回答者： tom04 回答日時： 2017/02/13 22:10

こんばんは！

A列都道府県が重複している場合
C列が一致、またはG列が一致している行を赤く塗りつぶしてみました。

↓の画像ではA2以降色を付けたいセルを選択　→　条件付き書式　→　新しいルール　→　数式を使用して・・・　→　数式欄に
=IF(COUNTIF($A:$A,$A2)>1,OR(COUNTIFS($A:$A,$A2,$C:$C,$C2)>1,COUNTIFS($A:$A,$A2,$G:$G,$G2)>1))

という数式を入れ　→　書式　→　塗りつぶしから「赤」を選択しています。

こんな感じではどうでしょうか？m(_ _)m

No.1 回答者： yuji3690 回答日時： 2017/02/13 21:59

その例だと名前一致してないですよね？

一致しているものを抽出したいのでは？

SUMIFやSUMPRODUCTによって判定し、該当するものに☓なり○なりを表示させる列を作り、
その列にその記号が表示された場合に色を塗る。
とするのはどうでしょう？

色を塗らなくても、フィルタでそのマークが付いてる行だけを表示させればいい気はしますが。

この回答へのお礼

すみません、補足で書いた最後の部分については、間違えました。

×　A列とC列だけでまず出させてから、目でG列を確認するというようなことがしたいのです。）
〇　A列とG列だけでまず出させてから、目でC列を確認するというようなこともしたいのです。）

お礼日時：2017/02/13 22:34