エクセルで、連続10文字以上がかぶっているデータを抽出する、なんてことはできませんか?



名前だったり住所だったりメールアドレスが含まれる5000人分の個人情報があります。
かなり重複していて、1名1名の情報が完全に同じ形式であるとは限りません。
例えば、同じ人だけど住所が区から始まっている人、東京都から始まっている人。同様に、同じ人だけど郵便番号が3桁の人と7桁の人。


一人一人にあるご案内をするので、重複しないようにデータを整理したいのですが、目で追っていくのはかなりの時間が掛かるというか不可能だと思うので、難儀しています。


良い方法はありませんでしょうか?

回答の条件
  • URL必須
  • 1人5回まで
  • 登録:2008/02/16 15:34:08
  • 終了:2008/02/23 15:35:07

回答(5件)

id:koji88 No.1

koji88回答回数56ベストアンサー獲得回数12008/02/16 16:02:13

ポイント20pt

貴方のエクセル習熟度がわかりませんが、まったくの素人ではないけどもそれほど詳しい機能はご存知でないという前提で、一番理解しやすそうな方法を説明します。

名簿が縦のデータ(例えばA列が名前でB列が住所のように)になっているとして、データーエリアをドラグで選択し、メニューの「データ(D)」、「フィルタ(F)」、「オートフィルタ(F)」とします。列の最上部にプルダウンのマークが出ますからここをクリックして、(トップテン)を選ぶとダブっている数の多い順にトップテンが出ますので、これで絞込みをしてダブっている部分を消す(各ダブりデータ毎に1行は残さないといけません)、またトップテンで絞る、ということを繰り返していくのが簡単と思います。

Excel(エクセル)基本講座:オートフィルタ(データ抽出)

id:curry21

ありがとうございます。

肝心の、「連続10文字以上がかぶっているデータを抽出する」方法はいかがでしょうか。

2008/02/17 03:29:15
id:nekonekochan No.2

nekonekochan回答回数72ベストアンサー獲得回数42008/02/16 17:28:24

ポイント20pt

住所よりも名前で並べ替えるのが一番効率的ではないでしょうか?

名前だけはどの個人情報にも必ず含まれていると思いますので・・・。


まず名前の列を基準にしてデータを並べ替えて、

同じシート内のどこか邪魔にならない列に、名前のある列を参照しながら

=if(A1=A2,"重複","")

とでも入れ、その5000件の行全部にコピーします。

既にデータを並べ替えてあるので、同じ名前のデータは連続していますから、

このように前後を比較するだけで重複が分かります。


住所で比較したいのなら、まずデータの形式を揃えるために、"東京都"を

置換機能を使って全部消してしまうとか。


http://www.yahoo.co.jp/(ダミーです)

id:curry21

ありがとうございます。

肝心の、「連続10文字以上がかぶっているデータを抽出する」方法はいかがでしょうか。

2008/02/17 03:29:14
id:ardarim No.3

ardarim回答回数897ベストアンサー獲得回数1452008/02/17 12:46:49

ポイント20pt

まず、任意の位置から始まる「連続10文字以上がかぶっているデータを抽出する」簡単な方法はありません。エクセルだろうと、専用のアプリケーションを作ったとしても、膨大な処理時間がかかる作業になります。


そこで、エクセルでできるレベルの代替策を紹介します。

(1)まず作業用のシートに元データを全コピーします。(以降の作業は作業用シートで行います)

(2)住所から都道府県の部分を削除し、先頭10文字分を切り出します。A列(A2,A3,…)に住所が入っているとし、適当な列(例えばB列)に切り出すとすると、B2に以下の式を入れ、全データ分(全行)コピーします。

=IF(ISERROR(FIND(LEFT(A2,3),"北海道青森県岩手県宮城県秋田県山形県福島県茨城県栃木県群馬県埼玉県千葉県東京都新潟県富山県石川県福井県山梨県長野県岐阜県静岡県愛知県三重県滋賀県京都府大阪府兵庫県奈良県鳥取県島根県岡山県広島県山口県徳島県香川県愛媛県高知県福岡県佐賀県長崎県熊本県大分県宮崎県沖縄県")),IF(ISERROR(FIND(LEFT(A2,3),"神奈川県和歌山県鹿児島県")),LEFT(A2,10),MID(A2,5,10)),MID(A2,4,10))

(3)全データ範囲を選択し、メニューから[データ]-[ソート]を選びます。先頭10文字を切り出した列を選んで[OK]を押してください。

(4)全データ範囲を選択したまま、メニューから[データ]-[集計]を選びます。設定画面が表示されますので、「集計の方法」は「データの個数」を選びます。「集計するフィールド」は、先頭10文字を切り出した列を選びます。その他の設定はそのままにして[OK]を押してください。

(5)画面左側にグループ表示を切り替えるツリーと、左上に[1][2][3]のような数字のボタンが表示されます。ボタンを押すと集計結果だけを表示するか、データも表示するかを変更できます。左側の[+][-]ボタンで個別にデータの表示/非表示を切り替えられます。

これで、集計結果(データの個数)が1以上であれば重複していることになります。


URLはダミーです

http://q.hatena.ne.jp/1203143647

id:haruo-31 No.4

haruo-31回答回数80ベストアンサー獲得回数102008/02/18 12:57:14

ポイント20pt

10文字以上が被っているデータを抽出するという処理を実現するにはプログラムを作るしか方法はありませんが、プログラム作成の労力、処理にも膨大な時間が掛かるので、現実的ではないです。

最終的には手作業になるのですが、下の方法で、大分楽になると思います。

1. 郵便番号を全て7桁化する。(データはここから取れます http://www.post.japanpost.jp/zipcode/download.html)

2. 郵便番号でデータをソートし、同じ郵便番号で、且つ宛名が同じデータを抽出する

3. その内同一宛先と判断できるものを削除

3桁しかない郵便番号データを7桁化するのも、労力が掛かりますが、こちらのプログラム化はそれほど難しくありません。

id:kaasuke No.5

kaasuke回答回数6ベストアンサー獲得回数02008/02/19 02:25:26

ポイント20pt

「エクセルで、連続10文字以上」という回答ではないのでポイントは結構です。

抽出の判断基準がないと、そうとう大変と思います。

456,X市ZZZ町1-1,二宮尊徳兄,aaa@abc.com

456-1111,X市ZZZ町1-1,二宮尊徳兄,zzz@abc.com

456-1111,X市ZZZ町1-1,二宮尊徳弟,zzz@abc.com

上記のようなデータがあったとき、どのフィールドを優先するかで

答えが変わってきます。

(たとえば456-1111で抜けば二宮尊徳兄と二宮尊徳弟はどう判断する?)

妥協案の一つとしてはメールで案内するのであれば、メールが重複しないように、

名前であれば名前で抜き出して案内するというのはどうでしょう?

まずは1つの基準を決めて抜き出してそこから手作業、というのが一番いいのかもしれません。

http://d

  • id:taku0208
    ちゃんとした回答になっていないので、コメントにします。

    まず、どのように情報が入っているのでしょうか?

    一つのセルに、住所、名前、電話番号、メールアドレス等が無秩序に書かれているとすれば、まずそれらを分離する必要がありますね。

    上記情報が別々のセルに分かれて入っているのでしたら、名前や電話番号でソートして重複する部分をピックアップするのが単純です。(手間は重複度合いによりますが)

    しかし、まず住所等の形式をそろえることをするのが効率的かもしれません。都道府県の部分を別セルに抽出する、3桁の郵便番号を7桁に変更するなど。

  • id:ritumikuma
    以前、質問者さんと似たような状況に陥り、大変な目にあったことがあります。
    私はこの方法で解決しました。何か役にたてないかな、と思いコメント書かせていただきます。
    (我が家のエクセルのバージョンは2000です)

    A列が氏名、B列が住所とします。
    氏名が重複しているデータを除く場合。
    A列の、タイトルとデータが入っているセルを選択。
    「データ」→「フィルタ」→「フィルタオプションの設定」で、
    「重複するレコードは無視する」にチェックを入れる。
    実行すると、氏名が重複している行を除いたデータが表示されます。
    これだけでは重複している行は隠れているだけなので、
    表示されている表を別シートにコピーして完了。

    (私の場合はその後、できあがった重複していないはずのデータで、
    住所でも同様の作業をしてみたら表示される件数が減ったので、
    1件1件チェック。氏名の入力ミスでした.....)

    1件1件確認・修正をしながら不要な行を削除する時、
    私は見落とし防止のため、並び替え後(この時は住所をキーにしました)、
    全データのセルを黄色にし、フィルタオプション→重複レコード無視で出たデータを塗りつぶしなしにして、
    「データ」→「フィルタ」→「すべて表示」にした後、作業しました。
    確認・修正後黄色の行を削除して、シートに黄色いセルがなくなれば完了。

    (住所で重複チェックをする前に「置換」を使って住所の「丁目」という文字をハイフンに統一するなどしてデータを整理しています)
  • id:rikuzai
    いわゆるデータクリーニングがしたいのでしょうか。
    それともあくまで「連続10文字以上がかぶっているデータを抽出」することが必要なのでしょうか。
    どちらにしてもデータが正規化されていないと求める結果は得られないように思うのですが。
    そういう意味ではtaku0208さんのご意見に賛成です。
    正規化をされたいということならアドバイスできることは多少あるのですが・・・。
  • id:curry21
    > それともあくまで「連続10文字以上がかぶっているデータを抽出」することが必要

    です。

    今回は住所録でしたが、このケースはこれに限らずあると思うので、もしやり方があるなら・・と
    思った次第です。

    例えば2chのニュースで、被ってるのを削除するとか。
    http://headline.2ch.net/bbynews/

  • id:rikuzai
    了解です。
    それでは私は離脱します。

    ちなみに質問文には「エクセルで」とありますが、
    >例えば2chのニュースで、被ってるのを削除するとか。
    といったことを考えておられるのなら方向性がまったく違うと思いますよ。
  • id:haruo-31
    curry21さん

    すみません、期待に沿えない回答をしてしまいましたが、
    普通データを比較する場合、そのデータを何らかのルールに従って整理する必要があります。

    例えば、2chのスレを整理する場合なら、辞書に載っているデータで文節を区切ったりして、
    スレタイに入っているキーワードを抽出してから、「キーワードが重複しているスレを検出する」ような方法を取るのが現実的です。
    もし希望の内容で5000件を比較する場合、5001*2500*(キーのデータの文字数-10)*(一回の検索時間)という時間が掛かります。恐らく1回実行するのに1時間近く掛かり、しかも期待するほど精度の良い結果は得られません。

    ご参考まで。
  • id:airplant
    nekonekochanさんやtaku0208さんの回答に類似しています。

    まず、列がばらばらなのか、そうでないのかですね。
    ばらばらならば、列をそろえるところから始める必要があります。
    正規表現である程度のキーワードは抜き出せますが、道のりは長そうです。
    例えば郵便番号は数字3桁か7桁連続とすれば、正規表現で次のように取り出しできます。
    検索:(^|[^0-9]+)([0-9]{7}|[0-9]{3})([^0-9]+|$)
    置換:\2\n (処理系により$2)

    今回は、ばらばらではなさそうな感触なので、表記のゆれを直してから、一番簡単な氏名列でソートして重複を消すというのが短時間でできる作業と思います。
    重複割合と、ゆれの割合がどのくらいあるのかによりますが、、、
    → 重複割合=50%だと、2500件が対象になるので、少し大変です。反対に5%程度なら25件なので、何もツールを使わずにあっという間にできてしまいますね。

    ゆれが大きいと10文字マッチでもマッチしなくなりますので、まずはゆれをなくすることが最初のステップかと。
    ゆれを直す方法:
    Excelマクロを使った正規表現(*1)でもできます。又単純にJIS関数で全部全角にしてから、スペースを削除したり「丁目」を「-」に変えれば、相当マッチするのでは?
    ゆれの具合はどんなものなのでしょう?
      東京都千代田区丸の内1-1-1
      千代田区 丸ノ内1丁目1番地1号
    上記の揺れだとすると、少し無理かも。郵便番号を元にするしかなさそうですね。

    (*1)
    http://q.hatena.ne.jp/1187227086
    http://q.hatena.ne.jp/1191417739#a765907

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません