【excel2007で関数を使用した検索】

質問

初めて質問します。
不慣れな為、不手際もございますが宜しくお願いします。

VLOOKUPのあいまい検索で上手くいかなかったので、お知恵を貸して下さい。

※画像を確認下さい。

目的は、A列にある店舗の電話番号をリストと照合しB列に記載する事です。
照合先のリストではスペースが入っていたり、「店」や一部文字の抜け等がありVLOOKUPではほとんどがエラーとなりました。
あいまい検索では、全然違う値が返されたりとほぼ使えませんでした。
この様な場合、どの関数をどのように使用すればいいのでしょうか？
※excel勉強中ですが、まだまだ関数の理解に至っておりません。

皆様のお知恵を拝借出来ないでしょうか？
宜しくお願いします。

回答No.2

chonami

>検索対象範囲の値が昇順に並んでいる場合にどの値に近いかの判別です

最後の引数をFALSEにすると、完全一致で元データが昇順でなくても抜き出すことはできます。

文字列を使う場合はやはり元データも検索値も統一する方がいいでしょうね。
ネックになるのがスペースと店という文字だけでしたら、私もSUBSTITUTEを使って検索用の列を設けるのがいいと思います。
私は付け足すよりは削除する方が楽かなと思うので、まず、A列とB列の間に列を挿入し

=SUBSTITUTE(SUBSTITUTE(JIS(A2),"　",""),"店","")

とします。Aブランド○○とかになりますね？
アルファベットやスペースの全角半角に対応するため、一旦全部全角にしてから置換しています。

次にEとFの間に列を挿入し、F2に

=SUBSTITUTE(SUBSTITUTE(JIS(E2),"　",""),"店","")

と入力します。
検索の際はこの値を使って、G2に

=VLOOKUP(F2,$B2:$C5,2,FALSE)

などで如何でしょうか？

今回はすでに入力してあるデータに対応するために数式で修正データをつくるようにしましたが、根本的には参照元と検索値はできるだけ同じデータにするのが一番です。
検索値を入力する際に、元データから入力規則でリスト化するなどの方法でできるだけ違いが出ないような方法を考えてください。
特に、入力する人によって入れ方が違うとか…もう目が当てられない事になります。

お礼

早々の回答ありがとうございます！！
そうなんです。。。
参照元と検索値が同じならば、なにも問題は無かったのですが、二つのデータは別々の企業からの支給データで、それらを何とか一致させてデータを作成しなければならず、頭を痛めておりました。
数件なら、頑張れますが1000件以上となると・・・気が遠くなってしまって自動化出来ないかと頑張っておりました。

excelはまだまだ初心者で、分からない事が沢山ありますが教えて頂いた内容を元に頑張ってみます！
ありがとうございます！！

回答No.1

play_with_you

そもそもVLOOKUPはいわゆる「あいまい検索」はできません。「完全一致」か「近似値」です。近似値とは「なんか似てる」ではなく、検索対象範囲の値が昇順に並んでいる場合にどの値に近いかの判別です。
たとえば
A列------B列
あひる----1
あふろ----2
おふろ----3
とあってどこかに「=VLOOKUP("おひる",A1:B3,2,TRUE)」とした場合、文字が一番似ている「あひる」の「1」ではなく、「おひる」の1文字目が「お」なので2列目と3列目の間にあると判断し「2」と表示されます。

それはさておき。
スペースと「店」の有無だけなら、
1.A列の値からSUBSTITUTE関数でスペースを削除し、最後の一文字が「店」でなければ「店」を付けたものをVLOOKUPの検索値とする
2.検索対象範囲はスペースがなく「店」が付いている表記で統一する
とすれば、ブレを吸収して完全一致で検索できます。
大文字小文字の違いや長音符の有無なども問題になるなら同様に「置換で強制的に表記を一定のルールにそろえる」方法でできます。
ただしそこまでくるとVLOOKUPの中に入れたら数式が長くなりすぎるので、置換した値を置いておく専用列を用意したほうがいいかもしれません。

お礼

早々の回答ありがとうございます！！

VLOOKUPもその他の関数も、勉強中で良く分かっていませんでした。

参照元と検索値の二つのデータは別々の企業からの支給データで、それらを一致させてデータを作成をしています。
数件なら、頑張れますので、出来るだけ同じになるようにデータをなおして頑張ってみます。

ありがとうございます！！