Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7全般 : Excel 2010)
名簿を比較して重複している場合、レコードを抽出したい
投稿日時: 17/05/28 19:39:04
投稿者: ちいまま

二つの法人名簿を比較して重複している場合、コードを抽出したいのですが
関数を組み合せる等にて可能でしょうか?
 
シート2のA列のデータ(顧客名のみあり)とシート1のA列を比較して重複がある場合、
シート1からB・Cを抽出し、シート2のB・Cへ表記したい。
ただし、同一名でも株式会社入力と(株)入力の場合があり一致していない。
シート1・2ともデータ数はそれぞれ1000件程度です。
 
シート1
  A       B    C
1 名       番号   コード     
2 安部株式会社  818181  100
3 (株)小池    202020  200
4 森 株式会社  301349  400
 
シート2 (シート1より BとCを抽出)
  A       B    C
1 名       番号   コード     
2 株式会社 小池 202020  200
3 株式会社 麻生
4 安部株式会社  818181  100
5 (有)不破

回答
投稿日時: 17/05/28 20:27:30
投稿者: WinArrow
投稿者のウェブサイトに移動

>重複
とは、シート内の複数存在していた場合に使う表現で
  
別シートの〱物がある
 という場合は、重複とは言いません。
  
と、私は考えます。
  
・・・で、今回の場合は、シート2にある顧客名がシート1にあるかということですか?
(株)株式会社の件は別として。
  
それともシート2の中にも重複はあるのですか?
  
 (株)の件ですが、
 1文字で「梶vもあれば「(株)」もあれば「(株)」もあると思います。
 2番目は、全角文字のカッコ、3番目は半角文字のカッコ
  
シート1のA列とB列の間に列を挿入して、シート2と合致するような文字列に変換することを
 お勧めします。
  
新しいB列セルの数式例
=SUBSTITUTE(A1,"(株)","株式会社")
  

投稿日時: 17/05/28 22:09:26
投稿者: ちいまま

WinArrowさん 回答ありがとうございます。
シート1・シート2のそれぞれのA列には、重複するレコードはありません。
シート1とシート2を比較してで同じ名前があるか?ないか?を調べたい。
あればシート2へシート1のB・C列を返したいのです。
 
↓↓こちらは、形式を同型にした上で比較すると言うことでしょうか?
  ならば、「株式会社」とか「(株)」とかを全て削除した方が良いのでしょうか?
新しいB列セルの数式例
=SUBSTITUTE(A1,"(株)","株式会社")
 
すみません、あまり詳しくないので・・・
何か方法がお教え願いたいです。

回答
投稿日時: 17/05/28 22:42:28
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
ならば、「株式会社」とか「(株)」とかを全て削除した方が良いのでしょうか?

 
それでも構いませんが、シート2も同じように削除することになりますから
シート2の形にシート1の顧客名を合わせる(片方だけで済みます)式の例が↓です。
 
 新しいB列セルの数式例
=SUBSTITUTE(A1,"(株)","株式会社")
 
他に途中にスペースがあったりすると、不一致となりますから
シート1の顧客名をシート2の形に併せるパターンを先に調べて、そのパターンを
全て詰め込むことができるかも検証したほうがよいと思います。
 
少しだけの例示ですので、パターンがどれだけあるのかは不明です。
 
前レスでも書きましたが「梶vと「(株)」の両方のパターンがあるとすれば、
両方を組み込んだ数式は
=SUBSTITUTE(SUBSTITUTE(A1,"(株)","株式会社"),"","株式会社")
 のようになります。
 
  

投稿日時: 17/05/28 22:56:26
投稿者: ちいまま

WinArrowさん またまた回答ありがとうございます。
SUBSTITUTEは、パターンを同等にしておくと捉えて良いですか?
その上で、比較して結果を返すことは、可能でしょうか?

回答
投稿日時: 17/05/28 23:36:02
投稿者: WinArrow
投稿者のウェブサイトに移動

ちいまま さんの引用:
WinArrowさん またまた回答ありがとうございます。
SUBSTITUTEは、パターンを同等にしておくと捉えて良いですか?
その上で、比較して結果を返すことは、可能でしょうか?

 
シート2のA列セルの値を検索キーにして、
シート1の検索範囲(B:D)からを検索して、シート1の内容を取得する
関数んは、VLOOKUP関数を使います。
 
シート2のB列セルに
=VLOOKUP(A2,Sheet1!$B:$E,2,FALSE)
シート2のC列セルに
=VLOOKUP(A2,Sheet1!$B:$E,3,FALSE)
で対応できますが、
不一致の場合「#N/A」エラーになります。
取り敢えず、エラーを目視で検証して
本当にアンマッチなのか?
整合しなければいけないパターンがあるのかが判明します。
 
※私見ですが、顧客名が統一的に入力されていないような運用は、大変です。
 例えば、顧客マスタのようなものを作成し、顧客番号から顧客名を索引するような運用に
した方がよいと思いますが・・・
 

投稿日時: 17/05/29 00:00:36
投稿者: ちいまま

本当に遅くまで再々すみません。
=VLOOKUP が一番なのですね。それなら分かります。
数値でないので文字が均一でない場合、可能なのか?不安でしたので。
試してみます。
 
ありがとうございました。

回答
投稿日時: 17/05/29 22:22:26
投稿者: んなっと

●Sheet1
 
        A    B    C D E F     G
1       名  番号 コード          
2  安部株式会社 818181   100      安部(株)
3    (株)小池 202020   200      (株)小池
4  森 株式会社 301349   400       森(株)
5 有限会社 不和 123456   600      (有)不和
 
G2
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",),"株式会社","(株)"),"有限会社","(有)"),"合同会社","(同)")
下方向・↓
 
●Sheet2
 
        A    B    C D E F     G
1       名  番号 コード          
2 株式会社 小池 202020   200      (株)小池
3 株式会社 麻生              (株)麻生
4  安部株式会社 818181   100      安部(株)
5    (有)不破              (有)不破
 
B2
=IFERROR(INDEX(Sheet1!B:B,MATCH($G2,Sheet1!$G:$G,0)),"")
右方向・→[C2まで] 下方向・↓
 
G2
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",),"株式会社","(株)"),"有限会社","(有)"),"合同会社","(同)")
下方向・↓

投稿日時: 17/06/02 21:55:21
投稿者: ちいまま

WinArrowさん、んなっとさん ありがとうございました。
結果としは、やはり一致する文字にばらつきがありました。スペースの関係でしょうか?
でも全くではなかったので助かりました。