Excelの質問です。VLOOKUP関数とOR検索を、組み合わせて用いるようなことはできないでしょうか？

質問

Excelの質問です。今、以下のようなデータがA列とB列に、1000行ぐらいずらっと並んでいます。

sp1|春の季語[あしび(馬酔木)]
sp2|春の季語[かげろふ(陽炎)]
sp3|春の季語[きじ(雉子)]
sp4|春の季語[ざんせつ(残雪)]
sp5|春の季語[ふぢのはな(藤の花)]
sp6|春の季語[やまぶき(山吹)]
・
・
・
su1|夏の季語[あぢさい(紫陽花)]
su2|夏の季語[かたつむり(蝸牛)]
su3|夏の季語[くものみね(雲の峰)]
su4|夏の季語[ころもがへ(衣替)]
su5|夏の季語[さみだれ(五月雨)]
su6|夏の季語[ばんりょく(万緑)]
su7|夏の季語[ひまはり(向日葵)]
su8|夏の季語[ほたる(蛍)]
su9|夏の季語[ほととぎす(時鳥)]
・
・
・
au1|秋の季語[あかとんぼ(赤蜻蛉)]
au2|秋の季語[あまのがわ(天の川)]
au3|秋の季語[いわしぐも(鰯雲)]
au4|秋の季語[かまきり(蟷螂)]
au5|秋の季語[きり(霧)]
au6|秋の季語[すすき(薄)]
au7|秋の季語[まんじゅしゃげ(曼珠沙華)]
au8|秋の季語[めいげつ(明月)]
・
・
・
wi1|冬の季語[おちば(落ち葉)]
wi2|冬の季語[かも(鴨)]
wi3|冬の季語[かれの(枯れ野)]
wi4|冬の季語[こがらし(木枯し)]
wi5|冬の季語[ゆきのこる(雪残る)]
・
・
・

シート名は＜季語・枕詞＞、ブック名は『俳句.xls』です。
A列には「sp1、sp2・・・su1、su2・・・au1、au2、・・・wi1、wi2・・・」といったIDのようなデータ、B列には「春の季語・・・」「夏の季語・・・」「秋の季語・・・」「冬の季語・・・」といったデータが入っています。
皆さまのお助け↓
http://okwave.jp/qa5096109.html
により、このデータの中から「春の季語」「夏の季語」「秋の季語」「冬の季語」といった、“種類のデータ”のみを抽出する方法は、理解することはできました。
その節はありがとうございました(>_<)

ただ、実は隣の列も、抽出しなければならない事態が生じまして(ToT)

皆様に教えていただいた方法により、別シート＜季語・枕詞の種類＞のA列に、「春の季語」「夏の季語」「秋の季語」「冬の季語」・・・といった“種類のデータ”は、並べることができました。

ここから、＜季語・枕詞＞シートのB列(季語のデータ)と、＜季語・枕詞の種類＞シートのA列(種類のデータ)を比較し、データが一致(完全一致ではなく部分一致でも構いません)すれば、＜季語・枕詞の種類＞シートのB列に、＜季語・枕詞＞シートのA列(ID)を、転記することはできないでしょうか？

例えば種類のデータとして＜季語・枕詞の種類＞シートのA列にある

「夏の季語」

に、部分一致する＜季語・枕詞＞シートのB列のデータは、

su1|夏の季語[あぢさい(紫陽花)]
su2|夏の季語[かたつむり(蝸牛)]
su3|夏の季語[くものみね(雲の峰)]
・
・
・

と、膨大にあります。
その膨大なデータ中の、一つだけでもいいのです。例えば、検索を開始して、最初に部分一致したデータが「su3|夏の季語[くものみね(雲の峰)]」であったならば、＜季語・枕詞の種類＞シートのA列とB列には、

夏の季語|su3

というように、転記したいのです。
“su”という情報が欲しいだけなので、三文字目の番号は、特に何でも構わないのです。

VLOOKUP関数を使おうと思ったのですが、部分一致検索で、VLOOKUPを使おうとしてもうまくいきませんでして・・・(ToT)

どうすれば、＜季語・枕詞の種類＞シートのB列に、＜季語・枕詞＞シートのA列(ID)を、転記できるでしょうか？
皆様のお力をお借しいただければ幸いです<m(__)m>

回答No.7

KURUMITO · ベストアンサー

回答5です。
=IF(OR(A1="",COUNTIF(季語・枕詞!B:B,A1&"*")=0),"",LEFT(INDIRECT("季語・枕詞!A"&MATCH(A1&"*",季語・枕詞!B:B,0)),2))
LEFT関数はＬＥＦＴ（文字列、文字数）のように使われますね。
上式ではＩＮＤＩＲＥＣＴ関数を使ってその文字列を季語・枕詞のシートから取り出しています。そこでＡとあるのはＡ列を意味しており、ＭＡＴＣＨ関数でＡ1セルに書かれた文字を先頭に含む文字を季語・枕詞のシートのＢ列で検索し、その行を取り出しています。
表示したＩＮＤＩＲＥＣＴ関数の全体では季語・枕詞シートのＡ列でのその行を求め、そこにある文字列を取り出していることになります。
ＩＮＤＩＲＥＣＴ関数を使うことでＡ列の例えば3行、4行を取り出す場合には単に＝Ａ3や＝Ａ4とすればよいのですが3や4が定まっていない場合、例えばB1セルに入力された数値の行でA列でのデータを取り出したい場合には普通の書き方ではできません。その場合には、例えば=INDIRECT("A"&B1)のような式を使うことで解決することができます。この場合に""を省略することはできません。

お礼

なんとなくわかりました！
"季語・枕詞!A"と、MATCH(A1&"*",季語・枕詞!B:B,0)は、“＆”でペアになっており、「MATCH(A1&"*",季語・枕詞!B:B,0)を満たすA列のデータを返す。」という意味ですね(^_^;)
ありがとうございます<m(__)m>

回答No.6

imogasi

#4です。
式
=IF(AND(A2="夏の季語",COUNTIF(C2,"*かたつむり*")>=1),MAX($E$1:E1)+1,"")
は（最初に式を入れる行は）
E2セルに入れるのです。E1に入れているので循環参照になるのです。

お礼

私の勘違いでした、すいません(>_<)
うまくいきました、ありがとうございます<m(__)m>

回答No.5

KURUMITO

回答Ｎｏ3です。春、夏、秋、冬だけが表示されていたので、簡単な式で済むような気がしました。
たくさんの種類があるのでしたら季語・枕詞の種類のシートでＢ1セルには次の式を入力し、下方にオートフィルドラッグします。
=IF(OR(A1="",COUNTIF(季語・枕詞!B:B,A1&"*")=0),"",LEFT(INDIRECT("季語・枕詞!A"&MATCH(A1&"*",季語・枕詞!B:B,0)),2))
この式ではＩＤの頭の２文字が表示されるようになっています。
3文字のケースがあるのでしたらそれを考慮した式にすることが必要です。

お礼

うまくいきました(^_^;)
ありがとうございます<m(__)m>

A1セルが空白、もしくは＜季語・枕詞＞シートに“A1＆*”の文字列が一つもない(＝0)場合、空白を返して、偽の場合は、LEFT関数で左から2文字切り取るみたいですね・・・でも、INDIRECT関数の使い方が、よくわかりませんでして・・・「"季語・枕詞!A"」とは、どういう意味なのでしょうか？
よくわからないので、

LEFT(INDIRECT(MATCH(A1&"*",季語・枕詞!B:B,0)),2))

と、削除してみたら、エラーになってしまいました(ToT)
普通に＜季語・枕詞＞シートのA列全部を選択しようとしたら、「季語・枕詞!A:A」となり、これまたエラーになってしまいました・・・「"季語・枕詞!A"」における、一つだけポツンとある“A”は、一体何なのでしょうか？

すいません、お暇な時で構いませんので、再度ご回答いただれば幸いです(>_<)

回答No.4

imogasi

#1です補足して貰っても、質問の内容（何がやりたいか）のことは十分書かず、自分の場合のことばかり（こんなの不要）書いている。
ーー
A列「夏の季語」で、C列「かたつむり」を含む！行を抜き出したいと
勝手に解釈して、
例データ
Sheet1
A列　　　B列（空白　　C列　E列
第2行目からデータとする
夏の季語 かたつむり(蝸牛）の姿 1
秋の季語 すすき
夏の季語 とんぼのはね
冬の季語 雁の群れ
秋の季語 かたつむりの色
夏の季語 歩むかたつむり 2
E列は　=IF(AND(A2="夏の季語",COUNTIF(C2,"*かたつむり*")>=1),MAX($E$1:E1)+1,"")
と入れて下方向に式複写。
条件に合った行に連番を振った。
Sheet2で
A2の式　=INDEX(Sheet1!$A$1:$D$100,MATCH(ROW()-1,Sheet1!$E$1:$E$100,0),1)
B2の式　=INDEX(Sheet1!$A$1:$D$100,MATCH(ROW()-1,Sheet1!$E$1:$E$100,0),3)
最後の引数はSheet1での、データの有る列の番号を意味する。
以上「imogasi方式」。

お礼

う～ん、うまくいかないですね(>_<)
imogasiさんのおっしゃる通りに、A列に種類名のデータ、B列は空白、C列に区切り文字“[”で区切った、種類名より後ろの文字列のデータ、D列は空白、E列に、

=IF(AND(A2="夏の季語",COUNTIF(C2,"*かたつむり*")>=1),MAX($E$1:E1)+1,"")

を入力したのですが、循環参照の警告が出てきてしまいまして・・・

回答No.3

KURUMITO

季語・枕詞の種類のシートのＢ1セルに次の式を入力して下方にオートフィルドラッグすればよいでしょう。
=IF(A1="","",IF(A1="春の季語","sp",IF(A1="夏の季語","su",IF(A1="秋の季語","au",IF(A1="冬の季語","wi","")))))

お礼

これまたすごい関数ですね、IF関数がすごい絡み合ってます・・・こんなの初めてみました(^_^;)
もし、A1が空白であれば、空白をそのまま返して、空白ではなく「春の季語」という文字列があれば、“sp”を返して、ない場合は・・・というように続いていくみたいですね。

ただ、私の質問の仕方が悪かったのですが、種類が100や200もありそうな状況ですと、関数を入力する手間が、ものすごく大変なような気がします・・・

回答No.2

某HN クロメート(Chromate)（@CoalTar）

リンク先はよくわからなかったが、たたき台で
D1セルに 検索文字(夏の季語)
E1セルに
=IF($D1="","",LEFT(INDEX(A:A,MATCH("*"&$D1&"*",$B:$B,0)),2))
C1:D1セルを別シートへ切りとって貼りつけ

お礼

すごいやり方ですね！
INDEX関数は詳しく知らなかったので、調べたところ、

INDEX(配列，行番号，列番号,領域番号)
http://excel.onushi.com/function/f-index.htm
ということみたいで(^_^;)

CoalTarさんが回答していただいた関数は、列番号と領域番号が省略されているみたいですね・・・A列のデータの中から、B列が"*"&$D1&"*"にマッチする行があれば、A列の行のデータを、Left関数で左から2文字切り取って、Eに表示させるのですね～ありがとうございます(>_<)

回答No.1

imogasi

質問文が長すぎて、読む気もしない。何とか簡略に。
例の数を減らし、１例でよいから、どういう思考プロセスでデータがどうだから、こうしたいと言うふうに説明を短くしてほしい。
VLOOUP関数（のTRUE型に限って）は第1引数の語句が、第3引数の検索データを探す関数であるのは知ってますね。
また部分一致検索はと出来ません。
その場合第1引数の内容によって検索表を切り替えたい場合は有るでしょう。その場合は第1引数でIF関数で判断して表が２，３程度に分かれるなら、VLOOKUP関数式の中に組み込めるでしょう。
＝＝＝
この質問は何なんだ？と読んでみると、A列の「AuXX」（Auがついている行）を別シートに、A,B列とも抜き出したいのか。
それならエクセルの関数の、抜き出し問題で、毎日のように質問がある、見飽きた問題だ。
そうなら質問の書き方がガラっと変わるべき。前半など要らない。
Googleで「imogasi方式」で照会すれば、何百と同類の質問と、imogasi方式でない他の方の解法も出てくる。
＝＝＝＝＝
●抜き出し問題なのかどうか補足してください。
ーー
それなら例は
a 千葉市
b 横浜市
a　甲府市
c　藤沢市
からaの分を抜き出し
a 千葉市
a　甲府市
を（別シートに）出したいと書くとおなじだよ。
自分の例を抽象化して、パターン化せよ。

お礼

すいません、もっと短くするように気をつけます<m(__)m>
やっぱVLOOKUは使えないみたいですね・・・。
確かに、ただの抜き出し問題かもしれませんね・・・(^_^;)

＜季語・枕詞の種類＞シートのA列は、

春の季語
夏の季語
秋の季語
・
・
・

と並んでいますが、隣のB列は、空白の状態です。
＜季語・枕詞＞シートから、データを抜き出したいのですが、＜季語・枕詞＞のB列には、「夏の季語[かたつむり(蝸牛)]」など、種類だけでなく、余計な部分([かたつむり(蝸牛)]等)がくっついているんですよね・・・。

でも、imogasiさんの助言で思いつきました、削除すればいいんですよね。“[*]”を“空白”に置換して、＜季語・枕詞＞シートのB列を、種類だけの文字列にして、IDのある＜季語・枕詞＞シートのA列も、後ろの数字の部分を削除すれば、ちょっと時間がかかりそうですが、VLOOKUP関数でもうまくいきそうです(^_^;)