条件にあったデータの抽出（別のシートに）

質問

エクセル２００７を使って、検索によるデータの抽出をおこなっています。

データは様々な商品の価格情報で、その店舗、地域等の情報がすべて番号で入力されています。

year item price region
2004 45 23 1
2004 1 5 1
2007 5 98 3

といった具合に多くのデータがあります。

それを異なるシートにその条件にあったものだけを（隙間なく）そのシートの上のほうに詰めて表示
できるようにしたいと奮闘しています。

例えば、別のシートにitem45、region 1のデータを
year item price region
2004 45 23 1
2003 45 22 1
2002 45 30 1

と表示するためにはどのようにしたらよろしいでしょうか？

お分かりの方、いらっしゃいましたら教えていただけませんでしょうか？

回答No.5

MackyNo1

数式で処理する場合は、データ範囲が大きかったり、表示するデータ数が多いと再計算に時間がかかりシートの動きが重くなりますのであまりお勧めできませんが、例示のデータなら以下のような関数で該当データを表示できます(元データがSheet1のA～D列の100行目までにある場合）。

A列
=INDEX(Sheet1!A:A,SMALL(INDEX(((Sheet1!$B$2:$B$100<>45)+(Sheet1!$D$2:$D$100<>1))*10000+ROW($2:$100),),ROW(1:1)))&""

B～D列(右方向に3つコピー）
=IF($A2="","",INDEX(Sheet1!B:B,SMALL(INDEX(((Sheet1!$B$2:$B$100<>45)+(Sheet1!$D$2:$D$100<>1))*10000+ROW($2:$100),),ROW(1:1))))

このようなケースでは、フィルタオプションの操作をマクロで記録して、このマクロを実行するようにすることをお勧めします。

回答No.4

KURUMITO

例えばシート1がまとめのシートでシート2、シート3、…にデータが入力されているとします。
すべてのシートは同じ項目が並んでおり、シート2以降のシートではA1セルにyear,B1セルにitem,C1セルにprice,D1セルにregionの項目名が有り、各データが下方に入力されているとします。
シート2以降についてはそれらのシートのシート名をCtrlキーを押しながらクリックして同じ作業グループにします。
その上で作業列としてシート2のE2セルには次の式を入力して下方にドラッグコピーします。

=IF(INDEX(A:D,ROW(),MATCH(Sheet1!$A$1,A$1:D$1,0))&"/"&INDEX(A:D,ROW(),MATCH(Sheet1!$B$1,A$1:D$1,0))=Sheet1!$A$2&"/"&Sheet1!$B$2,MAX(E$1:E1)+1,"")

上記の式はシート1での抽出の条件を設定することで有効に作動します。
そこでシート1を選択して次の作業を進めます。
A1セルには抽出したい項目名を例えばitemと入力し、B1セルには抽出したい項目名を例えばregionのように入力します。
A2セルにはA1の項目で抽出したい数値、例えば45と入力します。B2セルも同様で例えば1と入力します。
3行目には使うシート名を入力するのですがA3セルは空白のままで、B3セルにはSheet2、C3セルにはSheet3、D3セルにはSheet4・・・・・のように横に入力していきます。
4行目は各シートでの該当する行の数を表示させうためにA4セルには0と入力します。B4セルには次の式を入力して横方向にドラッグコピーします。

=IF(B3="","",MAX(INDIRECT(B3&"!E:E")))

5行目には該当する行の総数を表示させるためにA5セルには0と入力します。B5セルには次の式を入力して横方向にドラッグコピーします。

=IF(B4="","",A5+B4)

A7セルからD7セルにはシート2と同じ項目名を並べます。

A8セルには次の式を入力して横方向にドラッグコピーしたのちに下方向にもドラッグコピーします。

=IF(OR(ROW(A1)>MAX($5:$5),A$7=""),"",INDEX(INDIRECT(INDEX($3:$3,MATCH(ROW(A1)-0.1,$5:$5,1)+1)&"!A:D"),MATCH(ROW(A1)-INDEX($5:$5,MATCH(ROW(A1)-0.1,$5:$5,1)),INDIRECT(INDEX($3:$3,MATCH(ROW(A1)-0.1,$5:$5,1)+1)&"!E:E"),0),COLUMN(A1)))

これですべてのシートについて、1行目位で指定した項目と2行目で指定した数値についての該当する行が表示されますね。

回答No.3

K Kazz（@JazzCorp）

'キーのリストに一致した行だけを別シートに集める
'抽出キーは、列のニモニックとキー値をセットで指定（例えば、[A1：B、B1：goodsA]、[A2：D、B2：domainA]）、複数可
'抽出キーの矛盾はチェックしない
'ターゲットのシートをアクティブにして実行するのが分かり易い

Option Explicit
Sub ExtractRowsWithMatchKeys()
Const xSource = "Sheet1" 'ソースのシート
Const xTarget = "Sheet2" 'ターゲットのシート
Const xKey = "Keys" '抽出キーのシート
Const xKey_Col = "A" '抽出キーを設定する列位置
Const xHeads = 1 'ヘッダの行数
Dim xSheet As Worksheet
Dim xSheet2 As Worksheet
Dim xKeyWord(1 To 100) As Variant
Dim NoData As Boolean
Dim MayBeMatch As Boolean
Dim kk As Long
Dim mm As Long
Dim nn As Long
Dim xCell As Range
Dim xLast As Long

Application.ScreenUpdating = False
NoData = True
'For nn = 1 To Worksheets.Count
For Each xSheet In Worksheets
If (xSheet.Name = xKey) Then
xLast = xSheet.Cells(Rows.Count, xKey_Col).End(xlUp).Row
For kk = 1 To xLast
If xSheet.Cells(kk, xKey_Col) <> Empty Then
xKeyWord((kk - 1) * 2 + 1) = xSheet.Cells(kk, xKey_Col)
xKeyWord((kk - 1) * 2 + 2) = xSheet.Cells(kk, xSheet.Cells(kk, xKey_Col).Column + 1)
End If
Next kk
NoData = False
End If
Next
If (NoData) Then
MsgBox ("No Keys found!!")
Exit Sub
End If

Set xSheet = Sheets(xSource) 'ソース
Set xSheet2 = Sheets(xTarget) 'ターゲット
xSheet2.UsedRange.Clear
xSheet.Range("1:" & xHeads).Copy
xSheet2.Range("1:" & xHeads).PasteSpecial
' Paste:=xlPasteValues
xLast = xSheet.Cells(Rows.Count, xKey_Col).End(xlUp).Row
mm = xHeads + 1
For nn = xHeads + 1 To xLast
MayBeMatch = True
kk = 1
Do Until xKeyWord(kk) = Empty
' exit do
If (xSheet.Cells(nn, xKeyWord(kk)).Value <> xKeyWord(kk + 1)) Then
MayBeMatch = False
Exit Do
End If
kk = kk + 2
Loop
If (MayBeMatch) Then
'セルの値だけをコピー
xSheet.Rows(nn).Copy
xSheet2.Rows(mm).PasteSpecial Paste:=xlPasteValues
mm = mm + 1
End If
Next nn
xSheet2.Select
Application.ScreenUpdating = True
End Sub

回答No.2

noname#204879

添付図参照
Sheet1 のＡ～Ｄ列範囲、
Sheet2 の範囲 A1:D1、
Sheet2 の範囲 F1:G2、
にそれぞれ dbase、ref、output という名前を付ける。
Sheet2 をアクティブにして、次の[フィルタオプションの設定]を実行すればＯＫ！
[抽出先]→“指定した範囲”
[リスト範囲]→ dbase
[検索条件範囲]→ ref
[抽出範囲]→ output

「データの量がとても多」い場合は時間が掛るけど、トイレに行って戻ってくるまでには希望のデータが出ているかと。

回答No.1

PXU10652

「例えば、別のシートにitem45、region 1のデータを
と表示するためにはどのようにしたらよろしいでしょうか？」
　オートフィルタで、該当データをセレクトし、それを別シートにコピペする。

補足

ご回答有難う御座いました。

データの量がとても多く、シートもかなり多くにまたがっています。
それらをひとつのシートに自動的に集めるようなことを考えています。
なので、コピーアンドペーストをしない、という条件で、ご回答いただけると助かります。