HLOOKUPでもVLOOKUPでもなくて、それに似た関数はありますか

質問

HLOOKUPでもVLOOKUPでもなくて、それに似た関数はありますか？

やりたいことは、
同じファイルにシート１とシート２があり、
シート１には
a列 b列 c列 d列
１　ミッキー　サッカー　チョコレート
２　ミニー　　野球　　　クッキー
３　ドナルド　テニス　　ビスケット
４　デイジー　水泳　　　ポテトチップス
・
・
・

という、10,000行ほどのエクセル表があり、
シート２には、

野球
水泳
・
・

などという風に、シート１のc列の文字列が全部ではなく、複数ある1列のみのエクセル表があります。

そして、このシート２のセルをそれぞれシート１で検索して、それを含む一行全部を表示した
エクセル表を新たにシート３に作成したいのですが、関数かVBAかで可能でしょうか？

例えば、この場合、シート３には

a列 b列 c列 d列
２　ミニー　　野球　　　クッキー
４　デイジー　水泳　　　ポテトチップス
・
・
・

というように表示したいのです。
HLOOKUPやVLOOKUPは検索される文字列は端っこしかダメなんですよね？

教えて下さい！！

回答No.9

kagakusuki · ベストアンサー

回答番号:ANo.3です。

>シート２で出た検索結果の行数によっては、シート３に関数をコピーした行数と合ってないと、
ゼロという結果が表示されてしまいますよね？

この解消法はあるのでしょうか？

　参照先が空欄の場合には、0を表示させたくない場合には、一般的には、次の様な操作を行います。

メニューの[ツール]をクリック
　　↓
現れた選択肢の中にある[オプション]をクリック
　　↓
現れた「オプション」ウィンドウの[表示]タグをクリック
　　↓
「ウィンドウ オプション」欄の[ゼロ値]と記されている箇所をクリックして、チェックを外す　　↓
「オプション」ウィンドウの[OK]ボタンをクリック

　但し、この方法では、関数の計算結果が0の場合も、表示されなくなってしまいます。

　関数の数式のみで対応する場合には、次の様な数式になります。

=IF(COUNTIF(Sheet1!$C:$C,Sheet2!$A1)=0,"",IF(INDEX(Sheet1!A:A,MATCH(Sheet2!$A1,Sheet1!$C:$C,0))="","",INDEX(Sheet1!A:A,MATCH(Sheet2!$A1,Sheet1!$C:$C,0))))

この関数を日本語で説明しますと、

「もし、Sheet1のC列の中に、Sheet2のA1と同じ値を持つセルの個数が0である場合には、空欄とし、そうではない場合において、もし、Sheet1のA列中の、上から数えて『Sheet1のC列中にあるSheetSheet2のA1セルと完全に一致する値を持つセルが存在する位置を、上から数えた際の数』番目にあるセルの値が、空欄である場合には、空欄とし、そうではない場合には、Sheet1のA列中の、上から数えて『Sheet1のC列中にあるSheetSheet2のA1セルと完全に一致する値を持つセルが存在する位置を、上から数えた際の数』番目にあるセルの値とする」

になります。

お礼

すごいです！素敵です！

本当に本当に助かりました！

感謝の気持ちでいっぱいです。

ありがとうございました。

回答No.8

rukuku

＃７の訂正です
×　シート１のA2に
○　シート１のE2に

回答No.7

rukuku

オートフィルタを使う方法です。

E列を作業用に使います。
オートフィルタでは１行目がタイトル行として使われますので、
以下のようなタイトルをシート１の1行目に追加してください
A　　　　B　　　　　C　　　　D　　　　　E
NO．　　項目1　　項目2　　項目3　　作業列

そして、シート１のA2に以下の数式を入れ、データのある行分だけ
コピー（オートフィル）します。
=COUNTIF(Sheet2!A:A,Sheet1!C2)

シート２のリストに重複がなければ、E列の条件で「1」を選びます。
シート２のリストに重複がある場合には、「オプション-1以上」の設定をします。

回答No.6

rukuku

>VBAまったくの超初心者で、まったく何もわからず、図々しいお願いですが、
>そのVBAをちょこっと載せて頂けたら…

シート１
1 ミッキー サッカー チョコレート
2 ミニー 野球 クッキー
3 ドナルド テニス ビスケット
4 デイジー 水泳 ポテトチップス
5 ミッキー サッカー チョコレート
6 ミニー 野球 クッキー
7 ドナルド テニス ビスケット
8 デイジー 水泳 ポテトチップス

シート２
A列に
野球
水泳

として、以下のプログラムを試してみてください。

Sub　sumple1()
　　'変数の定義
　　Dim　Line1　　As　Long
　　Dim　Line2　　As　Long
　　Dim　Line3　　As　Long
　　Dim　LastLine1　　As　Long
　　Dim　LastLine2　　As　Long

　　'sheet3のデータを消去
　　Worksheets("Sheet3").Cells.Clear

　　'最終行の取得
　　LastLine1　=　Worksheets("Sheet1").Range("A65536").End(xlUp).Row
　　LastLine2　=　Worksheets("Sheet2").Range("A65536").End(xlUp).Row

　　'条件に合うものをコピー
　　For　Line2　=　1　To　LastLine2
　　　　For　Line1　=　1　To　LastLine1
　　　　　　If　Worksheets("Sheet1").Cells(Line1,　3)　=　Worksheets("Sheet2").Cells(Line2,　1)　Then
　　　　　　　　Line3　=　Line3　+　1
　　　　　　　　Worksheets("Sheet3").Cells(Line3,　1)　=　Worksheets("Sheet1").Cells(Line1,　1)
　　　　　　　　Worksheets("Sheet3").Cells(Line3,　2)　=　Worksheets("Sheet1").Cells(Line1,　2)
　　　　　　　　Worksheets("Sheet3").Cells(Line3,　3)　=　Worksheets("Sheet1").Cells(Line1,　3)
　　　　　　End　If
　　　　Next　Line1
　　Next　Line2
End　Sub

並び順が期待通りでないと思ったら、上記のプログラムに続いて、以下のプログラムを実行してください。

Sub　sumple1a()
　　Worksheets("Sheet3").Cells.Sort　Key1:=Range("A1"),　Header:=xlNo
End　Sub

------------------------------------------------------------------
>オートフィルタですと、シート２にある文字列を一つずつフィルタを
>かけなくてはいけなくて
マクロを使って、「シート２にある文字列でフィルタを掛けて、
結果をシート３にコピーする」ということを順次行うことも出来ます。
という意味で回答しました。

⇒オートフィルタと関数を使えばもう少しうまく出来そうな方法を
　思いつきましたので別途回答します。

補足

またまた回答、本当にありがとうございます！
マクロ実行してみたところ、欲しかった結果が出ました。

でも、、、
質問したときに、書くのを忘れてたのですが、
シート１のエクセル表は更新されていくので、行が段々増えていくのです。

回答して頂いたものだと、行の数だけ、
Worksheets("Sheet3").Cells(Line3,　3)　=　Worksheets("Sheet1").Cells(Line1,　3)
この実行を増やさなくてはいけないですよね？

それだとちょっと使い勝手が…
それを解消する方法はありますか？
シート１と同じ行分だけ、マクロの中の↑の行も増やせていけるなんて、
もう無理でしょうか？

回答No.5

KURUMITO

回答Ｎｏ4です。
大変失礼をしました。肝心のシート3のＡ2セルへの入力の式は次のように訂正してください。なお、Ａ1セルへは文字列の入力が無くてもかまいません。

=IF(ROW(A1)>MAX(Sheet2!$C:$C),"",INDEX(Sheet1!$A:$C,MATCH(INDIRECT("Sheet2!A"&MATCH(ROW(A1)-1,Sheet2!$C:$C,1)+1)&ROW(A1)-INDIRECT("Sheet2!C"&MATCH(ROW(A1)-1,Sheet2!$C:$C,1)),Sheet1!$D:$D,0),COLUMN(A1)))

補足

回答して頂いて、本当にありがとうございます！

関数でできるなんて、素晴らしいです！

色んなやり方があるんですね。

回答No.4

KURUMITO

重複のデータがたくさん含まれていても対応できます。
シート1やシート2、シート3ではいずれも2行まから下方にデータがあるとします。
シート1ではＤ列を作業列としてＤ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B2="","",B2&COUNTIF(B$2:B2,B2))

シート2でも作業列を作ります。
Ｂ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",COUNTIF(Sheet1!B:B,A2))

さらに、Ｃ1セルには0を入力して、Ｃ2セルには次の式を入力して下方いオートフィルドラッグします。

=IF(B2="","",SUM(B$2:B2))

シート3はお求めの表になりますがＡ1セルには何かの表題などを入力してください。Ａ1セルには必ず文字列が入力されていることが必要です。その上でＡ2セルには次の式を入力してＣ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>MAX(Sheet2!$C:$C),"",INDEX(Sheet1!$A:$C,MATCH(INDIRECT("Sheet2!A"&MATCH(ROW(A1)-1,Sheet2!$C:$C,1)+1)&ROW(A1)-COUNTA($A$1:$A1)+1,Sheet1!$D:$D,0),COLUMN(A1)))

これでシート2のＡ列に入力されているデータについて、シート1のＢ列で一致する行がすべて表示されることになります。

回答No.3

kagakusuki

Sheet1のC列に重複した値が無い場合には、INDEX関数とMATCH関数を組み合わせて使います。
　まず、Sheet3のA1に、次の数式を入力して下さい。

=IF(COUNTIF(Sheet1!$C:$C,Sheet2!$A1)=0,"",INDEX(Sheet1!A:A,MATCH(Sheet2!$A1,Sheet1!$C:$C,0)))

　続いて、Sheet3のA1セルをコピーして、Sheet3のリストの範囲に貼り付けて下さい。

　以上です。

補足

すごい！関数でできてしまうんですね～

回答して頂いて、ありがとうございます！

一つ質問ですが、これだとセルをコピーしたところすべてに結果が出てしまうのですが、
シート２で出た検索結果の行数によっては、シート３に関数をコピーした行数と合ってないと、
ゼロという結果が表示されてしまいますよね？

この解消法はあるのでしょうか？

あと、この関数を日本語で説明するとどんな感じでしょうか？
「もし、シート１のC列にある文字列とシート２のセルA1と同じ文字列の個数が空白なら、シート１のA列と、シート２のセルA1とシート１のC列が一致したセルの位置の値を返す」
みたいなことですか？
こんがらがってわかりません…

図々しいお願いですが、教えて下さい！

回答No.2

rukuku

こんばんは

「関数で」というと分かりませんがは、VBAで実現することは出来ます。
全体の流れだけ、回答します。

一番単純な考え方では、
１．シート２の「野球」を取得する
　　↓
２．シート１のC列を上から順にチェックし、「野球」ならば、その行をシート３の一番下にコピーする
　　↓
３．シート１最終行まで来たら、シート２の次の項目である「水泳」を取得する
　　↓
４．シート１のC列を上から順にチェックし、「水泳」ならば、その行をシート３の一番下にコピーする
…以下、３～４を同様の繰り返しで出来ます。

もう少し気の利いた方法として、オートフィルタを使うことも考えられますが、1万行くらいなら上記の
方法でもそこそこのスピードで出来ると思います。

＞HLOOKUPやVLOOKUPは検索される文字列は端っこしかダメなんですよね？
HLOOKUPやVLOOKUPは「端っこ限定」ですが、VBAをうまく使えば「端っこ」でなくてもOKです。

キーワードは
・For To ～ Next　…繰り返し処理
・IF Then　…条件分岐
・End(xlUp).Row　…最終行の所得
などです。

一つ一つは基本的なことですが、うまく組み合わせて使うのは、「慣れ」が必要です。
分からない部分が出てきたら、またこのサイトで質問してみてください。
p(^^)q

補足

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

オートフィルタですと、シート２にある文字列を一つずつフィルタをかけなくては
いけなくて、それが時間がかかるので、VBAや関数などで、ボタンを押下すれば
一発でシート３に結果が出るとか、短縮できないかと思いまして…

VBAまったくの超初心者で、まったく何もわからず、図々しいお願いですが、
そのVBAをちょこっと載せて頂けたら…

宜しくお願いします！！

回答No.1

imogasi

VLOOKUP関数で出来ると思います（Ｈｌｏｏｋｕｐ関数は本件では不適か）。Ｍａｔｃｈ関数もあります。ＶＢＡでも出来ます。
しかしデータを採ってくるのは関数では列単位（フィールド、項目単位）です。
あとは式の複写で対処します。
何かこの点についての誤解や未習得があるのでは（質問する理由が判らない）。
ーー
質問が読みにくく、わかりにくい。
実例を挙げて質問してください。
データ
引っ張ってくるデータ
完成形
をシートのデータの体裁系で記述してください。
わたしなぞ、エクセル関数の質問は、ほとんど例を挙げて回答してます。
ーーー
なおＧｏｏｇｌｅででも照会すれば、ＶＬＯＯＫＵＰ関数の説明、使用実例は腐るほどあります。

補足

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

質問がわかりにくくてすみません。。。

例を挙げて質問したつもりなんですが、実際に使う文字列を…ということでしょうか？

VLOOKUPですと、探す文字列は表の１列目しかダメ…ですよね？
質問で例をあげたとおり、３列目とかにある文字列で行を検索したいのです。