Excelのある特定列の行に入力された値をマスタ表のある特定列から部分一致で検索、該当行の特定列の行を表示させる作りこみをしたい。


Excelで使ったことのある関数と言えばSUMしか使ったことがないようなビギナーです。

本題に入りますが、例えば「シート1」シートのB列のセルに入力された値を、「マスタ表」シートのB列にて部分一致で検索し、該当する行があればC列の該当行セルの内容を表示させる、なければ空白…ということをやりたいのです。
当初VLOOKUPを使用しようと思ったのですが、検索方法が完全一致のため、上手く行きませんでした。
IFやCOUNTなど、他の関数で出来るようなのですが、何しろ関数について不勉強すぎてどれをどう変えてよいのかわかりませんでした。
ご教示いただけますと幸いです。

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2012/08/31 13:13:32
  • 終了:2012/08/31 23:52:15

ベストアンサー

id:Silvanus No.2

Silvanus回答回数174ベストアンサー獲得回数672012/08/31 19:31:17

ポイント350pt

「シート1」のC列に以下の数式を貼れば良いのではないでしょうか。
[※セルC1に下式を貼って、セルC1をコピー、セルC2より下方へペイスト]
=INDEX(Master!C:C,MATCH("*"&B1&"*",Master!B:B,0))
列の先頭行から下方へ順に検索し、条件に一致する最初のセルの行にある
C列の値が返されるはずです。

他2件のコメントを見る
id:Marin_MTB

おぉ、これは良いかもしれませんね!!
来週ちょっと試してみます。

2012/08/31 23:51:55
id:Silvanus

BA賞ありがとうございます。
お試しになって上手く行かない点がありましたらお知らせ下さい。

2012/09/02 11:14:39

その他の回答(1件)

id:gong1971 No.1

gong1971回答回数443ベストアンサー獲得回数682012/08/31 18:34:56

ポイント150pt

かなり複雑になってしまいますが、配列数式を使用する事で解決出来るかと思います。
また、[シート1][マスタ表]のそれぞれのシートで処理用の列を1つ使用します。
以下、設定方法です。

仮に、[マスタ表]のデータ領域を、B2:C101とします。
C列に空列を挿入します。(結果、求めたい値はD列となります。)
C2=1, C3=2, C4=3 ... C101=100と挿入したC列に連番を振ります。

次に、[シート1]のB2に検索値が入っているとして、C2に下記の数式を入力し、
[Ctrl]+[Shift]+[Enter]で数式を確定します。(これが配列数式の入力方法です)
正しく入力されると数式バーの数式の両端に{}が表示されます。

=MIN(IF(ISERROR(FIND(B2,マスタ表!$B$2:$B$101)),0,マスタ表!$C$2:$C$101))

※合致する値が複数あった場合、上の値を返します。

また、D2に下記数式を入力します。この値が求めたい値となります。
(こちらは通常通り[Enter]のみで確定します)

=IF(C2=0,"",VLOOKUP(C2,マスタ表!$C$2:$D$101,2,0))


また、蛇足ですが、[マスタ表]に合致する値が複数ないかチェックするには、
[シート1]のE2に下記の数式を入力し、[Ctrl]+[Shift]+[Enter]で数式を確定します。

=SUM(IF(ISERROR(FIND(B2,マスタ表!$B$2:$B$101)),0,1))

※合致した件数を表示します。2以上の値がないか確認されると良いかと思います。


以上の通りとなりますが、配列数式はExcel上級者でも理解されている方が少ないように思います。
下記の詳細が分かれば、もう少し単純な方法があるかもしれません。

・[シート1]B列の文字数は最大何文字、最小何文字か?
・部分一致というのは、前方一致、後方一致ではないか?
 もしくは、何文字目からという決まった条件はないか?

※条件によっては、より単純な方法は無いかもしれません。
※どちらにしても、今回の回答以上に処理用の列は必要になるかと思います。

id:gong1971

補足
配列数式は通常の数式以上に処理に時間が掛かります。
最初は小さな範囲でテストされた方が良いかと思います。
また、上記の理由からデータの範囲は必ず必要最小限としてください。
→$B:$Bのように列全体は指定しないでください。

2012/08/31 18:38:29
id:Marin_MTB

ありがとうございます。

2012/08/31 23:51:01
id:Silvanus No.2

Silvanus回答回数174ベストアンサー獲得回数672012/08/31 19:31:17ここでベストアンサー

ポイント350pt

「シート1」のC列に以下の数式を貼れば良いのではないでしょうか。
[※セルC1に下式を貼って、セルC1をコピー、セルC2より下方へペイスト]
=INDEX(Master!C:C,MATCH("*"&B1&"*",Master!B:B,0))
列の先頭行から下方へ順に検索し、条件に一致する最初のセルの行にある
C列の値が返されるはずです。

他2件のコメントを見る
id:Marin_MTB

おぉ、これは良いかもしれませんね!!
来週ちょっと試してみます。

2012/08/31 23:51:55
id:Silvanus

BA賞ありがとうございます。
お試しになって上手く行かない点がありましたらお知らせ下さい。

2012/09/02 11:14:39

コメントはまだありません

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

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

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

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