エクセルで住所か電話番号が重複していたら抽出したい

エクセルで住所か電話番号が重複していたら抽出したい

お世話になります。エクセル初心者です。
画面のような元データ（過去のデータ）があります。
今月分として、同じ氏名の人がでてきた場合、重複とチェックを入れたいのですが、
その人以外（同一家族）の場合も重複としたいと思います。
たとえば、氏名が別でも住所が同じとか、電話番号が同じ場合も「重複」のチェックが
したいです。
わかりにくくて申し訳ありませんが、よろしくお願いします。

No.7 ベストアンサー 回答者： tom04 回答日時： 2010/05/11 19:08

No.2・3です！

またまた・・・お邪魔します。

別Sheetバージョンをご希望のようなのですが、
No.6さんが仰っているように、「過去データ」の中に「今月分」のデータがある場合に
重複が判明すれば良いわけですよね？

一応そういうことだとしての方法です。
その場合は当然名前は「過去データ」にあるはずですので、名前は無視して
住所か電話番号があれば「重複」と表示するようにしてみました。

↓の画像でSheet1が「過去データ」・Sheet2が「今月分」としています。
Sheet2のD2セルに
=IF(COUNTBLANK(B2:C2),"",IF(OR(COUNTIF(Sheet1!$B$2:$B$1000,B2),COUNTIF(Sheet1!$C$2:$C$1000,C2)),"重複",""))
という数式を入れ、オートフィルで下へコピーしています。
（Sheet1の1000行目まで対応できるようにしています。）

以上、参考になれば良いのですが、
今回も外していたらごめんなさいね。m(__)m

この回答へのお礼

ご回答ありがとうございます。
返事が遅くなり申し訳ありません。
また、質問が分かりにくかったにもかかわらず、内容に沿ったご回答をいただきまして感謝いたします！（詳細はNo.6様に書かせていただきました）
こちらの方で試してみたところ、無事に解決いたしました！
本当にありがとうございました。
お手数をおかけいたしました。

お礼日時：2010/05/12 13:04

No.6 回答者： MackyNo1 回答日時： 2010/05/11 17:59

＞「過去データ」と「今月分」は別シートなのです。

。。

この意味がよくわかりません。

最初の質問とはまったく違いますが、同じシートには重複するデータはなく、別シートとの重複を調べたいということでしょうか？

この回答へのお礼

ご回答ありがとうございます。
返事が遅くなり申し訳ありません。
また、質問の内容が分かりにくい文章で申し訳ありませんでした。

>同じシートには重複するデータはなく、別シートとの重複を調べたいということでしょうか？

同じシート内（過去データ）にも重複データがあり、かつ、別シート（今月分）を作成する場合に過去データと照らし合わせて重複があるかを見たいといったことを説明できていませんでした。

内容としましては、「過去データ」が過去に粗品を送ったことのある人のデータで、「今月のデータ」は今月、粗品がほしいと言っておられる方・・・なのですが、過去に粗品を送ったことのある方（同一家族含む）には送らないことになったのです。
（いままで（過去）は何度でもＯＫでしたので、過去データには同じ方が重複しています）

質問が下手で、上記の内容でも伝え切れているのか不安ですが、説明不足で申し訳ありませんでした。

お礼日時：2010/05/12 13:03

No.5 回答者： keithin 回答日時： 2010/05/11 17:35

手始めはこんな方法でも。

手順：
ＤＥ列を列番号のＤとＥのとこで列選択する
Ｄ１セルがアクティブセル（選択範囲の中で逆選択セルになっている状態）になっているのを確認して
書式メニューの条件付き書式を開始する
セルの値が　→　数式が　に変えて
右の空欄に
=(COUNTIF($D:$D,$D1)>1)+(COUNTIF($E:$E,$E1)>1)
と記入して，書式ボタンで重複の書式を設定する。
住所または電話がダブってるデータが入力されたら色が付いて判る。

ちなみに，住所と電話の両方がダブっているのは，どっちかがダブってるのを調べれば必然的にカバーできます。



＞重複とチェックを入れたい

重複を調べる方法もさることながら，重複している事を具体的に「どうやって示す」のかにも，実に様々な方法があります。
どういう風に表示したらあなたが一番使いやすいのか，皆さんのアイデアを実際にあなたの手でエクセルにやってみてください。


＃それと。
実際にはデータを入れてみると，もっとちゃんと調べないといけないデータが沢山見つかると思います。
ご相談に書かれたような如何にもなデータや「判りやすくと思って説明用のサンプルを示しましたけど実際はもっと色々あります」では，本当にやらないといけない仕事には対応できません。大まかな所で練習が出来たら，今度は本当の状況を分析して改めて事実に基づいてご相談を投稿してみてください。

この回答へのお礼

ご回答ありがとうございます。
返事が遅くなり申し訳ありません。
実は最初は条件付き書式でやっていましたが、これで色のついた箇所を数えるというのは難しい（？）とのことでしたので断念しておりました。
こちらの不手際にもかかわらず、ご丁寧な回答をいただきありがとうございました。
今後の反省材料として肝に銘じます。（本当にその通りですよね・・・。）
ありがとうございました。

お礼日時：2010/05/12 13:01

No.4 回答者： KURUMITO 回答日時： 2010/05/11 16:53

ご質問にある表のようにＣ列に氏名、Ｄ列に住所、Ｅ列に電話番号があるとして、同一家族が並んで入力されていれば問題はないのでしょうが、一般にはデータがばらばらで入力されますね。

その場合にはどの方とどの方が同一家族なのかを区別していく必要がありますね。
Ｆ列は作業列としてＦ2セルには次の式を入力して下方にオートフィルドラッグします。

=D2&E2

長い表示になりますので列を非表示にしても良いでしょう。

そこで住所と電話番号が一致する方をＧ列に表示させるとしてＧ1セルには住所＋電話番号とでも入力し、Ｇ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(F2="","",IF(AND(COUNTIF(F$2:F2,F2)=1,COUNTIF(F:F,F2)>1),INT(MAX(G$1:G1))+1,IF(COUNTIF(F$2:F2,F2)>1,INDEX(G:G,MATCH(F2,F:F,0))+0.01*(COUNTIF(F$2:F2,F2)-1),"")))

これで整数の部分が同じものは同一家族とみなされるでしょう。

次にＨ1セルには住所同じとでも入力し、Ｉ1セルには電話番号同じとでも入力します。その後にＨ2セルには次の式を入力してＩ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(AND(COUNTIF(D$2:D2,D2)=1,COUNTIF(D:D,D2)>1),INT(MAX(H$1:H1))+1,IF(COUNTIF(D$2:D2,D2)>1,INDEX(H:H,MATCH(D2,D:D,0))+0.01*(COUNTIF(D$2:D2,D2)-1),""))

整数部分の同じ者同士が同一家族とみなされるでしょう。

なお、同一家族同士を連続した行に寄せるには「並べ替えとフィルタ」で「ユーザー設定の並べ替え」などを行うことになるでしょう。

入力していくデータがこれまでに入力したデータと重複があるかどうかはＧ，Ｈ，Ｉ列に表示されるかどうかで判定することができますね。また、それはどの家族にあるかは整数部分がどの家族と一致しているかで判断できますね。

この回答へのお礼

早速のご回答、ありがとうございます
＞同一家族が並んで入力されていれば問題はないのでしょうが、一般にはデータがばらばらで入力されますね。

まったくその通りでございます。その事も念頭になかったです。
（本当に質問が下手ですみません）

こちらも試しているのですが、エクセル初心者のためよくわかってません。
もう少し勉強してみたいと思います。

ありがとうございました。

お礼日時：2010/05/11 17:19

No.3 回答者： tom04 回答日時： 2010/05/11 16:51

No.2です！

たびたびごめんなさい。

余計なお世話かもしれませんが・・・
前回の方法では誰と重複しているのか判らないと思いますので、

もう一度数式を考えてみました。

F2セルを
=IF(COUNTIF($D$2:D2,D2)>1,INDEX($C$2:$C$1000,MATCH(D2,$D$2:$D$1000,0))&"さんと同一家族",IF(COUNTIF($E$2:E2,E2)>1,INDEX($C$2:$C$1000,MATCH(E2,$E$2:$E$1000,0))&"さんと同一家族",""))

としてオートフィルで下へコピーしてみてください。

これで誰と同一家族かが表示されると思います。

以上、何度も失礼しました。m(__)m

この回答へのお礼

こちらも試してみたらできました！
誰と重複しているのかまでわかるなんて素敵です（笑）
以下にも書いていますが、こちらの記入ミスで
別シートの事を書き忘れていました。。。
あつかましくて申し訳ありませんが、
別シートバージョンもご指導いただけましたら
よろしくお願いします。

お礼日時：2010/05/11 17:14

No.2 回答者： tom04 回答日時： 2010/05/11 16:29

こんにちは！

外していたらごめんなさい。

F2セルに
=IF(OR(COUNTIF($D$2:D2,D2)>1,COUNTIF($E$2:E2,E2)>1),"重複","")
という数式を入れ、オートフィルで下へコピーしてはどうでしょうか？

これで、住所か電話番号、または両方が重複していても、「重複」と表示されると思います。

以上、参考になれば良いのですが、
的外れならごめんなさいね。m(__)m

この回答へのお礼

早速のご回答、ありがとうございます。
試してみたらできました！
本当にありがとうございました。

ただ、こちらのミスで言い忘れていました
（すみません・・・）
「過去データ」と「今月分」は別シートなのです。。。
こちらでも格闘してみようと思っていますが（汗）
あつかましくて申し訳ありませんが、
別シートバージョンもご指導いただけましたら
よろしくお願いします。

お礼日時：2010/05/11 17:12

No.1 回答者： MackyNo1 回答日時： 2010/05/11 16:28

F2セルに以下の式を入力して下方向にオートフィルしてください。

=IF(COUNTIF($D$2:D2,D2)+COUNTIF($E$2:E2,E2)>2,"同一家族","")

この回答へのお礼

早速のご回答、ありがとうございます。
試してみたらできました！
本当にありがとうございました。

ただ、こちらのミスで言い忘れていました
（すみません・・・）
「過去データ」と「今月分」は別シートなのです。。。
こちらでも格闘してみようと思っていますが（汗）
あつかましくて申し訳ありませんが、
別シートバージョンもご指導いただけましたら
よろしくお願いします。

お礼日時：2010/05/11 17:11