人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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

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

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

●質問者: Marin_MTB
●カテゴリ:コンピュータ
○ 状態 :終了
└ 回答数 : 2/2件

▽最新の回答へ

1 ● gong1971
●150ポイント

かなり複雑になってしまいますが、配列数式を使用する事で解決出来るかと思います。
また、[シート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列の文字数は最大何文字、最小何文字か?
・部分一致というのは、前方一致、後方一致ではないか?
もしくは、何文字目からという決まった条件はないか?

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


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

Marin_MTBさんのコメント
ありがとうございます。

2 ● Silvanus
●350ポイント ベストアンサー

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


Silvanusさんのコメント
済みません。見付けられない時の処理を忘れてました。 =IF(ISERROR(INDEX(Master!C:C,MATCH("*"&B1&"*",Master!B:B,0))),"Not found",INDEX(Master!C:C,MATCH("*"&B1&"*",Master!B:B,0)))

Silvanusさんのコメント
度々済みません。もっと簡略化できますね。 =IF(ISERROR(MATCH("*"&B1&"*",Master!B:B,0)),"Not found",INDEX(Master!C:C,MATCH("*"&B1&"*",Master!B:B,0)))

Marin_MTBさんのコメント
おぉ、これは良いかもしれませんね!! 来週ちょっと試してみます。

Silvanusさんのコメント
BA賞ありがとうございます。 お試しになって上手く行かない点がありましたらお知らせ下さい。
関連質問

●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ