こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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




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

野球
水泳



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

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

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

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




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

教えて下さい!!

投稿日時 - 2010-10-19 19:52:56

QNo.6261373

困ってます

質問者が選んだベストアンサー

 回答番号:ANo.3です。

>シート2で出た検索結果の行数によっては、シート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セルと完全に一致する値を持つセルが存在する位置を、上から数えた際の数』番目にあるセルの値とする」

になります。

投稿日時 - 2010-10-22 00:41:48

お礼

すごいです!素敵です!

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

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

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

投稿日時 - 2010-10-23 12:48:16

このQ&Aは役に立ちましたか?

2人が「このQ&Aが役に立った」と投票しています

回答(9)

ANo.8

#7の訂正です
× シート1のA2に
○ シート1のE2に

投稿日時 - 2010-10-20 22:21:42

ANo.7

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

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


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


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

投稿日時 - 2010-10-20 19:58:05

ANo.6

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

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

シート2
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

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

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

投稿日時 - 2010-10-20 19:44:56

補足

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

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

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

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

投稿日時 - 2010-10-21 22:01:27

ANo.5

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

=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)))

投稿日時 - 2010-10-20 15:13:12

補足

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

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

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

投稿日時 - 2010-10-21 23:12:39

ANo.4

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

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

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

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

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

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

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

=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のA列に入力されているデータについて、シート1のB列で一致する行がすべて表示されることになります。

投稿日時 - 2010-10-20 14:34:04

ANo.3

 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のリストの範囲に貼り付けて下さい。

 以上です。

投稿日時 - 2010-10-20 08:39:25

補足

すごい!関数でできてしまうんですね~

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

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

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


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

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

投稿日時 - 2010-10-21 23:07:30

ANo.2

こんばんは

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

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

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

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


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

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

投稿日時 - 2010-10-19 20:29:25

補足

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

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

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

宜しくお願いします!!

投稿日時 - 2010-10-19 23:31:32

ANo.1

VLOOKUP関数で出来ると思います(Hlookup関数は本件では不適か)。Match関数もあります。VBAでも出来ます。
しかしデータを採ってくるのは関数では列単位(フィールド、項目単位)です。
あとは式の複写で対処します。
何かこの点についての誤解や未習得があるのでは(質問する理由が判らない)。
ーー
質問が読みにくく、わかりにくい。
実例を挙げて質問してください。
データ
引っ張ってくるデータ
完成形
をシートのデータの体裁系で記述してください。
わたしなぞ、エクセル関数の質問は、ほとんど例を挙げて回答してます。
ーーー
なおGoogleででも照会すれば、VLOOKUP関数の説明、使用実例は腐るほどあります。

投稿日時 - 2010-10-19 20:18:50

補足

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

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

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

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

投稿日時 - 2010-10-19 23:36:47

あなたにオススメの質問