エクセルでのデータ抽出について

質問

初めて質問させていただきます。
２つの銘柄の株価データをエクセルにコピペした後、日付と時刻が同じものを行を揃えて抽出したいのですが、いろいろ試してみましたがわかりません。

具体的には次のようにしたいのですが・・・。
【元データ】
　Ａ列　　　　　Ｂ列　　　　Ｃ列　　　　Ｄ列　　　　Ｅ列　　　　Ｆ列
・
・
2007/11/9　　9:20:00　　520　　2007/11/9　　9:25:00　　1544
2007/11/9　　9:15:00　　514　　2007/11/9　　9:20:00　　1520
2007/11/9　　9:10:00　　513　　2007/11/8　 15:00:00　　1430
2007/11/9　　9:05:00　　509　　2007/11/8　 14:55:00　　1425
2007/11/9　　9:00:00　　501　　2007/11/8 　14:50:00　　1427
2007/11/8　　14:55:00 　497 　 2007/11/8　 14:45:00　　1431
・
・
【抽出後】
・
・
2007/11/9　　9:20:00　　520　　2007/11/9　　9:20:00　　1520
2007/11/8　　14:55:00 　497　　2007/11/8　 14:55:00　　1425
・
・
どなたか宜しくお願いいたします。

回答No.3

noname#204879 · ベストアンサー

Sheet1
　　　 A　　　　B　　 C　　　　D　　　　E　　 F
1　DATE　　　 TIME　 POS1　DATE　　　 TIME　 POS2
2　2007/11/9　 9:20　 520　2007/11/9　 9:25　1544
3　2007/11/9　 9:15　 514　2007/11/9　 9:20　1520
4　2007/11/9　 9:10　 513　2007/11/8　15:00　1430
5　2007/11/9　 9:05　 509　2007/11/8　14:55　1425
6　2007/11/9　 9:00　 501　2007/11/8　14:50　1427
7　2007/11/8　14:55　 497　2007/11/8　14:45　1431
8

Sheet2
　　　 A　　　　B　　 C　　 D
1　DATE　　　 TIME　 POS1　POS2
2　2007/11/9　 9:20　 520　1520
3　2007/11/8　14:55　 497　1425
4

Sheet2 において、範囲 A1:C1 には Sheet1!A1:C1 を、
　　　　　　　　 セル D1 には Sheet1!F1 を、
それぞれコピーしておく

１．Sheet2 をアクティブにして、[データ]→[フィルタ]→[フィルタオプションの設定]を実行
２．“指定した範囲”に目玉入れ
３．[リスト範囲]ボックス内にカーソルを移動して、マウスで範囲 Sheet1!A1:C20（下方の空白行を含めても構わない）を撫で撫で
　　（→当該ボックス内は Sheet1!$A$1:$C$20 と表示）
４．[検索条件範囲]ボックス内にカーソルを移動して、マウスで範囲 Sheet1!D1:E7（下方の空白行を含めてはならない！）を撫で撫で
　　（→当該ボックス内は Sheet1!$D$1:$E$7 と表示）
５．[抽出範囲]ボックス内にカーソルを移動して、マウスで範囲 $A$1:$C$1 を撫で撫で
　　（→当該ボックス内は Sheet2!$A$1:$C$1 と表示）
６．[OK]をクリック
７．セル D2 に次式を入力して、此れを下方にズズーッと複写
　　=SUMPRODUCT((Sheet1!D$2:D$7=Sheet2!A2)*(Sheet1!E$2:E$7=Sheet2!B2),Sheet1!F$2:F$7)

お礼

ご回答ありがとうございます。

目的としていたことができました。
自分なりに「フィルタオプションの設定」も試していたのですが、使い方が間違っていたようでした。
また、以前sumproductは一体何のためにあるのだろう？と思っていましたがこのように使えるとは知りませんでした。
大変勉強になりました。

回答No.4

sige1701

Sheet2
　　　 A　　　　B　　 　　C　　 　D　　E
1　　　　　　DATE　　　 TIME　 POS1　POS2
2　　　　　　2007/11/9　 9:20　 520　1520
3　　　　　　2007/11/8　14:55　 497　1425
4
A列を作業用列とします
A2==SMALL(IF(ISNUMBER(MATCH(Sheet1!$A$2:$A$7+Sheet1!$B$2:$B$7,Sheet1!$D$2:$D$7+Sheet1!$E$2:$E$7,0)),ROW(Sheet1!$A$2:$A$7)),ROW(Sheet1!A1))
配列数式です

B2=IF(COUNT(A:A)<ROW(A1),"",INDEX(Sheet1!A:A,$A2))
D列の必要範囲までコピー
E2=SUMPRODUCT((Sheet1!D$2:D$7=B2)*(Sheet1!E$2:E$7=C2),Sheet1!F$2:F$7)

お礼

ご回答ありがとうございます。

しばらくうまくいきませんでしたが、下記のA1を固定したらできました。

A2=SMALL(IF(ISNUMBER(MATCH(Sheet1!$A$2:$A$7+Sheet1!$B$2:$B$7,Sheet1!$D$2:$D$7+Sheet1!$E$2:$E$7,0)),ROW(Sheet1!$A$2:$A$7)),ROW(Sheet1!$A$1))

行を詰める作業が発生しそうですが、すっきりした感じで整理できそうです。

回答No.2

imogasi

関数では多分不可能（なぐらい複雑）では無いかと思います。
AーC、D-F両方がデータの数によって、不確定要素を発生させるから、自分の納まる行が計算しにくい。
ーー
質問で、「抽出後」の実例が、私には不明瞭だと思う。
私の下記の模擬例で、質問の主旨は間違ってないか見てください
Sheet1
A-F列
1 b c 1 y z
1 b c 1 y z
1 b c 2 y z
2 b c 2 y z
2 b c 2 y z
3 c c 2 y z
3 b c 2 y z
3 b c 3 y z
4 b c 3 y z
4 b c 3 y z
4 b c 5 y z
6 b c 5 y z
ーー
Sheet1をSheet2にコピー貼り付けして
Sheet2をアクチブにしておく。
Sub test01()
k = 2
For i = 1 To 100
c1 = Application.WorksheetFunction.CountIf(Range("$a$1:$A$100"), i)
c2 = Application.WorksheetFunction.CountIf(Range("$D$1:$D$100"), i)
If c1 = c2 Then
k = k + c1
'--
ElseIf c1 > c2 Then
Range(Cells(k + c2, "D"), Cells(k + c2 + c1 - c2 - 1, "F")).Insert Shift:=xlDown
k = k + c1
'--
Else 'C1<c2
Range(Cells(k + c1, "A"), Cells(k + c1 + c2 - c1 - 1, "C")).Insert Shift:=xlDown
k = k + c2
End If
Next
End Sub
実行結果
「－」は空白の意味。（OKWAVE画面で、列が左に寄らないよう入れたもの）
1 b c 1 y z
1 b c 1 y z
1 b c
2 b c 2 y z
2 b c 2 y z
ー ー ー 2 y z
ー ー ー 2 y z
ー ー ー 2 y z
3 c c 3 y z
3 b c 3 y z
3 b c 3 y z
4 b c
4 b c
4 b c
ー ー ー 5 y z
ー ー ー 5 y z
6 b c
こういうのを望んでいるのではないですか。
ーー
上例で日付が１，２，３・・になっていますが、日付シリアル値も整数数字です。
だから、本質的にコードを変えなくても済みます。
Sub test02()
For i = DateSerial(2007, 11, 1) To DateSerial(2007, 11, 30)
'(上記コードのFor-Nextまで）
Next
End Sub
のように修正して動くはず。
ーー
上記の実行方法は
Sheet2をアクチブにして
シート画面で
ALT+F11ーー＞VBEの画面になる
（メニューで）挿入ー標準モジュール
出てきた画面に、上記コードを張り付け
F5（実行）

お礼

ご回答ありがとうございます。

今、動かしてみましたが望んでいた形にするのには私のスキルからして少し時間がかかりそうです。
目的としていたことは、上記mike.gさんの回答でできましたが、最終的にはマクロで動かしたいと思いますので、そのときの参考にさせていただきたいと思います。

回答No.1

shintaro-2

あまりスマートな方法ではありませんが・・・

g列にA,C 列の日付を比較するif文、ｈ列にB列D列の時刻比較するif文を入力し、

全体にフィルタをかけて、g,hでif文の成立する行だけ表示するようにすれば、一応可能です。

お礼

早速のご回答ありがとうございます。
力不足でよくわからないのですが、if文でA列とD列の日付を比較すると3,4,5行が消えてしまいます。
すると2007/11/8　14:55:00のデータが抜けてしまいます。
また、その後、B列とE列の時間の項目を比較する場合、行がずれているためif文で対応するセルが一致しません。
10日分ぐらいのデータをとると同じ日付の同じ時刻のデータが10行程度ずれていることもあるのですが・・・。

日付で比較処理後の状態
1行目　2007/11/9　【9:20:00】　520　　2007/11/9　　9:25:00　　1544
2行目　2007/11/9　　9:15:00　　514　　2007/11/9　【9:20:00】　1520
・
・
・
6行目　2007/11/8　　14:55:00 　497 　 2007/11/8　 14:45:00　　1431