シート間の検索したデータの受け渡しについて

質問

シート１に日付、名前、その他の項目があり、シート２に日付を入力すると、シート１から検索された行を全てシート２に表示したいのですが、そのようなことは可能でしょうか？
１行だけだと、わかるのですが、何行もある場合はどのように表示したらいいのかわかりません。
どなたかご教授お願いします。
また、このような処理はエクセルだけでは厳しいのでしょうか？
やはり、マクロを使用したほうがよいのでしょうか？

回答No.3

ka_na_de · ベストアンサー

一例です。
Findメッソドを使わない方法です。
標準モジュールに登録して実行してください。

＜前提＞
　Sheet1:元データ
　Sheet1のＡ列：日付
　Sheet2のＡ１セル：検索する日付
　Sheet2の２行目以下に結果出力

'＜マクロ＞
Sub test()
　Dim r1 As Long
　Dim r2 As Long
　Dim Ws1 As Worksheet
　Dim Ws2 As Worksheet

　Set Ws1 = Worksheets("Sheet1")
　Set Ws2 = Worksheets("Sheet2")

　r2 = 2
　Ws2.Range("A2:IV65536").ClearContents
　For r1 = 1 To Ws1.Range("A65536").End(xlUp).Row
　　If Ws1.Cells(r1, "A").Value = Ws2.Range("A1").Value Then
　　　Ws2.Rows(r2).Value = Ws1.Rows(r1).Value
　　　r2 = r2 + 1
　　End If
　Next r1
End Sub

補足

上記テストでやってみます。
ご連絡ありがとうございます。

回答No.2

imogasi

（１）>はエクセルだけでは厳しいのでしょうか
エクセルを関数と、捉えていると、＃１のご回答のように難しい式になります。作業列もつかわず、その点は良いのですが。
エクセルは抜き出し問題は苦手です。数の計算などが関数のメインで
データを好きなように抜き出す、並べるのは、やればやっとできる、傍流の処理です。
この類の質問は毎日載っているが。
（２）作業列を使う方法はimogasi方式で沢山私が回答してます。
（３）＞マクロを使用したほうがよいのでしょうか
マクロもFindメソッドなど使う方法は、WEB上で、諸所に例が載っているが、やや難しい。マクロはエクセル、アクセス、ワード他それぞれあって、質問者が言っているのは、エクセルのVBAのことになります。
（４）ユーザー関数を使って式を少し判りやすく
aのセル範囲で、bと等しくて、ｃ番目の行数を探す関数。
標準モジュールに貼り付ける。
Function fn(a, b, c)
Dim cl As Range
i = 0
k = 0
For Each cl In Range(a)
i = i + 1
If cl = b Then
k = k + 1
If k = c Then
fn = i
Exit Function
End If
End If
Next
fn = ""
End Function
を定義する。
ロジックは幼稚な総なめ法のままにしてある。
ーー
例データ　A2:A１４
2007/7/1 山田
2007/7/5 鈴木
2007/7/1 大賀
2007/6/2 大川
2006/12/1 小野
2007/7/1 星野
2007/7/2 近藤
2007/7/3 千葉
2007/7/4 神田
2007/4/1 上野
2007/5/23 庄司
2007/7/1 木村
2007/3/31 米原
ーー
D2に
=INDEX($A$1:$B$20,fn("$A$1:$A$20",DATE(2007,7,1),ROW()-1),COLUMN()-3)
これをE2に複写し、
ｄ２：E２をD5:E５まで式を複写する。
結果
2007/7/1 山田
2007/7/1 大賀
2007/7/1 星野
2007/7/1 木村
行を下へ複写しすぎると、#VALUE!出るが、2007/7/1をContIFした数より行数－１が多ければ空白とする式を入れるが、長くなるので今回略。

補足

デバックしながら確認したのですが、どうも値が入りません。
標準モジュールに上記のコードを貼り付けるだけで問題ないと思うのですが・・。

シート１のA4列に日付、B4列に名前、C4列からデータがあり、
シート２のC3に日付が入力されているとする。
そして、シート２のD3に下記の計算式を入力しても値が表示されなかったのですが。。他におかしなところありますか？
何かありましたら教えてください。

=INDEX(シート１!$A$1:$C$1000,(fn("シート１!$A$1:$A$1000",$C$3,ROW()-3)),1)

回答No.1

zap35

具体的なシートの構成が分からないので的確な回答はできないかもしれませんが、Sheet1のＡ列に日付があるとします。別シートのＡ１セルにも検索したい日付を入力しておき、そのシートの適当なセルに以下の式を貼り付けて、右方向、および下方向にコピーしてみてください

=INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$A$1:$A$100=$A$1)*ROW(Sheet1!$A$1:$A$100),),COUNTIF(Sheet1!$A:$A,$A$1)-ROW(A1)+1))

ただしこの式だと、表示すべき行数以上ではエラーになります。
エラーを回避するよう条件判定を追加したのが下の式になります。

=IF(COUNTIF(Sheet1!$A:$A,$A$1)-ROW(A1)>=0,INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$A$1:$A$100=$A$1)*ROW(Sheet1!$A$1:$A$100),),COUNTIF(Sheet1!$A:$A,$A$1)-ROW(A1)+1)),"")

補足

早速ご回答頂き、ありがとうございます。
やってみたのですが。。。
４件表示しなければいけないところ、２件ほどしか検索にひっかけることができませんでした。
また、検索項目を２個指定した場合は同じようにさらにややこしい式になりますよね。。
エクセルの関数って難しいです。