Excel関数で検索

質問

シート1に、会社名（(株)○○または○○(株)の表記）と住所が登録されています。
シート2に、会社名（○○の表記）が入力されています。

シート2に入力された会社名から、シート1の住所をシート2のB列に抽出したいのです。
普通ならVLOOKUPを使用すれば、簡単に出来ると思うのですが‥
(株)表記があるために、どうすれば良いのか分からなくなってしまいました。

さらに、一致するものが無かった場合に、エラーを表示せず、空欄にしたいと思います。
IF関数を使ってみましたが、エラーが出てしまいました‥

説明がうまく出来なくて申し訳ありませんが、ご指導お願いします。

回答No.1

nayuta_lot · ベストアンサー

こんにちは
方法としては、
１．シート１の会社名の（株）を取り除く
２．シート２の会社名に（株）をきちんとつける
のどちらかだと思います。

ちなみにＡ（株）、（株）Ａとでは別の会社になるので正確に行う
ためにはきちんと（株）をつけて検索すべきかと思います。
（本来なら取引先コードなどを検索キーにするのがよいのですが・・）
↓前株と後株の参考↓
http://oshiete1.goo.ne.jp/qa788142.html

１の方法であれば、メニューの編集から置き換えを使って

検索する文字列に、（株）
置換える文字列に、""
半角と全角を区別するのチェックをはずす（カッコがあるため）
"すべて置換"を押す

で（株）を取り除く

関数では
=SUBSTITUTE(B1,"（株）","")
とすれば、B1のＡ（株）はＡになります。

VLOOKUPは、VLOOKUP(検索値,範囲,列番号,検索の型)
ですが、検索値が該当しなかった場合は#N/Aを返してきます。
この#N/Aを調べるのがISNA(テストの対象)という関数です。
この関数はテストの対象が#N/Aだった場合にTRUE（真）を返してきます。
従って

=IF(ISNA(VLOOKUP(A1,Sheet1!$A$1:$C$10,2,0))=TRUE,"該当なし",VLOOKUP(A1,Sheet1!$A$1:$C$10,2,0))

などと書いてやれば、空白にすることも、"該当なし"と表示させること
もできます。

ちなみに IF文の中の"=TRUE"は省略可能です。　IF文は、
=IF(条件,TRUE(真)の時の処理,FALSE(偽)の時の処理)
ですから、ISNAがTRUEを返せば、必然的にTRUE(真)の時の処理を実行します。

このほかにISERRORという関数でも同様に処理できます。
メニューのところにある、fxみたいなボタンを押すと関数の貼り付けが
できますので、その中から情報→ISERRORやISNAを選択して説明を見てください。

何かありましたら補足してください。
ご参考まで

お礼

回答、ありがとうございます。
おっしゃるとおり、正式名称での登録が大前提だと思います。
最終的にシート2の会社名を正式名称での登録にしたいのですが、すでに登録してある会社は住所が入力されればすべてを調べ直す手間がないと考えました。
その上で、A1の表記を変更しようかと‥
いったん(株)表記を取ってシートにコピーし、そこから引っ張る形がベストかも知れませんね。。
一度にやってしまおうと考えたのが、ダメだったのかも‥
参考になりました。ありがとうございます。

回答No.4

mitarashi

#2です。
>Sheet1のA列は作業列との事ですが、現在A列に会社名が入力されているので、列を挿入すると言う事でしょうか？
仰る通りです。
どうしても元の体裁を変えたく無いときは、作業列は例えばSheet3に置く事もできます。
この場合、Sheet1のA列から元データが始まっているとして、
Sheet2のB1=IF(ISERROR(SMALL(Sheet3!$A:$A,ROW(A1))),"",INDEX(Sheet1!$A:$Z,SMALL(Sheet3!$A:$A,ROW(A1)),COLUMN(A1)))
以降、下方、右方に複写

作業列：Sheet3のA1=IF(ISERROR(SEARCH(Sheet2!$A$1,Sheet1!A1,1)),"",ROW())
以降、下方に複写
となります。
作業列の関数を入れた行が、Sheet1の行数より少ないと、当然検索漏れになりますので、ご注意下さい。

お礼

ありがとうございました。
試してみましたが、今回はうまく出来ませんでした‥
でも、私の発想には無い方法だったので、とても参考になりました。
次回、使えそうなので、覚えておいて試してみたいと思います。

回答No.3

mu2011

VLOOKUP関数はワイルドカードが使用出来ますので、次の方法をお試し下さい。
数式内のセル範囲等は仮の値の為、調整して下さい。
=IF(COUNTIF(sheet1!A:A,"*"&A1&"*"),VLOOKUP("*"&A1&"*",sheet1!A:B,2,FALSE),"")

お礼

回答ありがとうございます。
ワイルドカードはすでに試してみたのですが、ダメでした。
と言うのも、abc(株)という会社をワイルドカードで検索すると、(株)abcdという会社もヒットしてしまうのです‥
他に方法が無いか、もう少し考えてみます。

回答No.2

mitarashi

意図されているのとは違う、お節介な回答です。不要ならスルーしてください。
Sheet1
A列は作業列です。
................A...............B...............C
..1................1.......(株)A........住所1
..2...............2...（有）A.......住所2
..3........................(株)B.......住所3
..4........................C(株).......住所4
..5...............5.......A(株).......住所5
..6...............6.....AA(株).......住所6

A1の式=IF(ISERROR(SEARCH(Sheet2!$A$1,B1,1)),"",ROW())
以下下方にコピー

Sheet2
A1セルに検索する会社名（部分一致）を入れます
.................A...............B...............C
..1...............A.......(株)A........住所1
..2....................（有）A.......住所2
..3........................A(株).......住所5
..4......................AA(株).......住所6

B1の式=IF(ISERROR(SMALL(Sheet1!$A:$A,ROW(A1))),"",INDEX(Sheet1!$B:$Z,SMALL(Sheet1!$A:$A,ROW(A1)),COLUMN(A1)))
以下下方、右方にコピー

補足

回答ありがとうございます。
参考になりそうなんですが、ちょっと分からない部分がありますので、教えてください。
Sheet1のA列は作業列との事ですが、現在A列に会社名が入力されているので、列を挿入すると言う事でしょうか？
1.2._._.5.6.というA列が良く理解できません‥
教えていただければ嬉しいです。