Excelで、2シートの突合せ（社名抽出）について

質問

Excelファイルが2つあります。
1つ目のファイルには、顧客情報が2万行ほどあります。
（氏名・会社名・部署名・役職・メールアドレス・・・・など）
2つ目のファイルには、社名のみ200行ほどあります。

1つ目のデータの社名欄で、2つ目のファイルの200社に含まれている行をピックアップしたいのですが、社名のカナや英語が全角だったり半角だったり、株式会社だったり（株）だったりと、表記が揃っておらず、調べてはみたのですが自力で抽出する方法がわかりません。。。

すみませんが、教えていただけないでしょうか。
よろしくお願いいたします。

回答No.4

kagakusuki · ベストアンサー

下記の方法は

>株式会社だったり（株）だったり

という点に対しては、

株式会社
(株)
(株)
KK
(株)
(株)
（株）
(カ)
カ)
(カ)
カ)
有限会社
(有)
(有)
(有)
(有)
（有）
(ユ)
ユ)
(ユ)
ユ)

の場合にしか対応してはおらず、合名会社や合資会社、合同会社等の他のパターンに関しては会社ごとに(正式名称か同一の略称を用いる様に)統一する必要がありますが、

>社名のカナや英語が全角だったり半角だったり

という点にも対応する方法です。
　尚、「氏名」、「会社名」、「部署名」の3項目が同じ内容になっているデータがあった場合には、重複しているデータと見做して1番最初に現れたデータのみが表示される様になっております。
　又、抽出したデータは会社ごと、部署ごとにまとまっている様に並べ替えられます。

　処で各表のレイアウトに関してですが、質問者様が書かれた御質問文の内容には、どのデータが何というファイルの何というシートのどこに入力されているのかという事に関する情報が何も説明されておりませんので、取り敢えず仮の話として1つ目のファイルのファイル名がbook1、2つ目のファイルのファイル名がbook2であり、

>顧客情報が2万行ほど

が並んでいるシートがbook1のSheet1で、その中のA1セルに「氏名」という項目名が入力されていて、B1セルに「会社名」という項目名が入力されていて、C1セルに「部署名」という項目名が入力されていて、D1セルに「役職」という項目名が入力されていて、E1セルに「メールアドレス」という項目名が入力されていて、実際のデータは2行目以下に入力されているものとします。
　又、

>2社名のみ200行ほど

が並んでいるシートがbook2のSheet1で、その中のA1セルには「会社名」という項目名が入力されていて、実際の会社名のデータはA2以下に入力されているものとします。
　又、抽出した情報はbook2のSheet2のA列～E列に表示するものとし、その際、A1セルに「氏名」、B1セルに「会社名」、C1セルに「部署名」、D1セルに「役職」、E1セルに「メールアドレス」という具合に、book1のSheet1に入力されているものと同じ項目名が入力されていて、抽出したデータは2行目以下に表示するものとします。
　又、データを抽出するために必要となる処理を行う都合から、book2のSheet3のA列～D列を作業列として使用するのとします。
　又、book2を開いて編集や保存等を行うのは、必ずbook1が開かれている状態で行うものとします。

　まず、book2のSheet3のA2セルに次の関数を入力して下さい。

=IF(INDEX(Sheet1!$A:$A,ROW())="","",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(JIS(INDEX(Sheet1!$A:$A,ROW()))),"　",),"（ユ）","有限会社"),"（ユ","有限会社"),"ユ）","有限会社"),"(有)","有限会社"),"（有）","有限会社"),"（カ）","株式会社"),"（カ","株式会社"),"カ）","株式会社"),"(株)","株式会社"),"ＫＫ","株式会社"),"（株）","株式会社"),"ウ゛","ヴ"))

[注]このサイトの回答欄の仕様により、勝手に別の文字に変換されてしまっておりますが、上記の関数の中の"(株)"や"(有)"の部分(漢字1文字を囲んでいる小括弧が半角文字となっている部分)は、「(」+「株」+「)」や「(」+「有」+「)」の3文字ではなく、株式会社や有限会社の略称として使われる「括弧も含めて1文字となっている『括弧付き文字記号』」ですので、関数を入力する際に元の「括弧も含めて1文字となっている『括弧付き文字記号』」に戻して下さい。
　それに対し、"（株）"や"（有）"などの小括弧が全角文字となっている部分は、回答欄に表示されている通りの文字列ですので、間違えないよう注意して下さい。

　次に、book2のSheet3のA2セルをコピーして、A3以下に貼り付けて下さい。(book2のSheet1のA列においてデータが入力されている行を上回るのに十分な行数となるまで)
　次に、book2のSheet3のB2セルに次の関数を入力して下さい。

=IF(OR(INDEX([Book1.xlsx]Sheet1!$A:$A,ROW())="",INDEX([Book1.xlsx]Sheet1!$B:$B,ROW())=""),"",IFERROR(VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(JIS(INDEX([Book1.xlsx]Sheet1!$B:$B,ROW()))),"　",),"（ユ）","有限会社"),"（ユ","有限会社"),"ユ）","有限会社"),"(有)","有限会社"),"（有）","有限会社"),"（カ）","株式会社"),"（カ","株式会社"),"カ）","株式会社"),"(株)","株式会社"),"ＫＫ","株式会社"),"（株）","株式会社"),"ウ゛","ヴ"),$A:$A,1,FALSE),""))

[注]こちらの関数の場合も"(株)"や"(有)"の部分を「括弧付き文字記号」に戻して下さい。

　次に、book2のSheet3のC2セルに次の関数を入力して下さい。

=IF($B2="","",IF(COUNTIF($C$1:$C1,$B2&"◆"&SUBSTITUTE(SUBSTITUTE(UPPER(JIS(INDEX([Book1.xlsx]Sheet1!$C:$C,ROW()))),"　",),"ウ゛","ヴ")&"◆"&SUBSTITUTE(SUBSTITUTE(UPPER(JIS(INDEX([Book1.xlsx]Sheet1!$A:$A,ROW()))),"　",),"ウ゛","ヴ")&CHAR(7)&"*"),"",$B2&"◆"&SUBSTITUTE(SUBSTITUTE(UPPER(JIS(INDEX([Book1.xlsx]Sheet1!$C:$C,ROW()))),"　",),"ウ゛","ヴ")&"◆"&SUBSTITUTE(SUBSTITUTE(UPPER(JIS(INDEX([Book1.xlsx]Sheet1!$A:$A,ROW()))),"　",),"ウ゛","ヴ")&CHAR(7)&COUNTIF($B$1:$B2,$B2)))

　次に、book2のSheet3のD2セルに次の関数を入力して下さい。

=IF($C2="","",IF(COUNTIF($C$1:$C1,$C2),"",IFERROR(MATCH(LEFT($C2,FIND("◆",$C2)-1),$A:$A,0)*10000000+COUNTIF($C:$C,"<"&$C2),"")))

　次に、book2のSheet3のB2～D2のセル範囲をコピーして、book2のSheet3のB列～D列の3行目以下に貼り付けて下さい。(book1のリストの行数を上回るのに十分な行数となるまで)

　次に、book2のSheet2のB2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet3!$D:$D),"",INDEX(Sheet1!$A:$A,INT(SMALL(Sheet3!$D:$D,ROWS($2:2))/10000000)))

　次に、book2のSheet2のA2セルに次の関数を入力して下さい。

=IF($B2="","",IF(ISERROR(1/(INDEX([Book1.xlsx]Sheet1!$A:$E,MATCH(INDEX(Sheet3!$A:$A,MATCH($B2,Sheet1!$A:$A,0))&"◆*?"&CHAR(7)&COUNTIF($B$1:$B2,$B2),Sheet3!$C:$C,0),MATCH(A$1,[Book1.xlsx]Sheet1!$A$1:$E$1,0))<>"")),"",INDEX([Book1.xlsx]Sheet1!$A:$E,MATCH(INDEX(Sheet3!$A:$A,MATCH($B2,Sheet1!$A:$A,0))&"◆*?"&CHAR(7)&COUNTIF($B$1:$B2,$B2),Sheet3!$C:$C,0),MATCH(A$1,[Book1.xlsx]Sheet1!$A$1:$E$1,0))))

　次に、book2のSheet2のA2セルをコピーして、C2～E2のセル範囲に貼り付けて下さい。
　次に、book2のSheet2のA2～E2のセル範囲をコピーして、book2のSheet2のA列～E列の3行目以下に貼り付けて下さい。(抽出されるデータの行数を上回るのに十分な行数となるまで)

　以上です。

お礼

非常に細かく回答していただいて、ご丁寧にありがとうございました。
想定いただいたとおりでしたので、その通り記入してみました。
最初はエラーが出たのですが、苦戦した結果なんとかやりたいことができました！
ありがとうございました。

回答No.3

bunjii

>社名のカナや英語が全角だったり半角だったり、株式会社だったり（株）だったりと、表記が揃っておらず、
1つ目のファイルがマスターと考えて良いでしょうか？
先ずは前処理として社名の修復を行って表記を統一してください。
（株）、(株)、(株)等を株式会社に置換する。
半角カタカナ、英数字および英記号は全角に置換する。
その後の追記では統一した規則で入力するように担当者を教育します。
検索についてはMATCH関数で2つ目のシートの社名を検索値として1つ目のシートの社名の列を範囲として検索すれば一致した会社名の行番号が返りますので、必要な項目の値はINDEX関数で取り出せます。
具体的には模擬データの提示が無いので提言できません。
1つ目のシートに相当する模擬データを20行程度提示頂ければ検証して処理するための数式を提言できます。
尚、Excelのバージョンによって組み込まれている関数が異なりますので念のためにバージョン番号も提示してください。

お礼

ご親切にありがとうございます。
行数が多くて重たいので苦戦しましたが、いただいた案をもとにがんばってみました。
ご回答いただきありがとうございました。

回答No.2

passers-by

2万件もあるようならば、本来はデータベースなどで管理する方がよいかとは思います。
また、痛感されているとは思いますが、マスターとなるデータは全半角や(株)or(株)などは統一しておくのがデータ管理の基本です。

とにかく今回はExcelで関数式を使って1つめのマスターになるシートから探すということですが、
全角や半角が入り混じって一律でないとのことですから、シンプルな１つの式だけではなかなか難しいかもしれません。

ASCかJISのどちらかを使って、半角か全角かに統一する
　ASC関数　　全角のカナや英数字を半角にする　　=ASC("カナ") →　　カナ
　JIS関数　　　半角のカナや英数字を全角にする　　=JIS("カナ")　→　カナ

SEARCHとREPLACEを組み合わせて、A1セルの社名中にある(株)　を　(株)　に置き換える
　　=REPLACE(A1,SEARCH("(株)",A1),1,"(株)")
　SEARCH関数　　指定した文字が何文字目に出てくるか調べる　　=SEARCH("(株)",A1)
　REPLACE関数　指定した位置の文字列を置き換える
　　=REPLACE("対象のセル",開始位置,置き換え文字数,"置き換える文字列")

複数のセルからその文字列を探して、その行数を調べるMATCH関数を使って検索
　　MATCH(探す文字列、探す範囲,0)　　　※ 最後の0　は完全一致した場合だけ結果を返す
　　C1セルに　(株)OKWave　と入力されていたとして、Sheet1（社名のシート）のA列から探す場合
　　=MATCH(C1,Sheet1!A1:A200,0)
Sheet1の中に　(株)OKWave　があれば、その行数が関数の結果として返ってきます。
(見つからなければ、エラーの文字(#N/A)が表示されますが…)

これらの関数を状況に合わせて組み合わせることでできませんか？

お礼

おっしゃるとおりで、きちんと名寄せができていればいいのですが。。。
また、DBといっても特定用途でクラウドサービスを使用しており、Accessなどではないので難しく。。。
なんとかやってみました。ありがとうございました。

回答No.1

aokii

VLOOKUP関数を使ってみてください。