エクセルで年月を入力して表ごとデータを抽出したい。

質問

もう一度整理しなおしました。
以下のような、年月ごとの表が、一つのシートの中に縦横にたくさん並んでいます。フォーマットは全て同じです。
これを別シートに、年月を入力して該当月のデータを表ごと表示するようにしたいのです。

色々調べてみてもなかなかわかりません。
よろしくお願いいたします。

＜以下表の例＞
2005,1
Elements ICP XRD Manual Estim Pond Total
Cu 12 23 12 12 12 71
Au 1.2 6 1.2 1.2 1.2 10.8
Ag 1.4 7 1.4 1.4 1.4 12.6
Pt 1.23 1 1.23 1.23 1.23 5.92
C 4.17 1 4.17 4.17 4.17 17.68
Total 20 38 20 20 20 118

回答No.3

noname#52504 · ベストアンサー

例えば、
・元のデータがSheet1のA1:Z999の範囲にある
・Sheet2のA1セルに年月を示す文字列を入力する
・Sheet2のA1セルに入力する文字列(年月)と値が一致するセルは、
　Sheet1のA1:Z999の範囲にただ一つしかない。
・Sheet2のA2セル以降に該当月のデータを表示する
とします。
----------------------------------------------------------------------
●Ａ案：数式１発で引く

Sheet2のA2セルを

　=OFFSET(Sheet1!$A$1,SUMPRODUCT((Sheet1!$A$1:$Z$999=$A$1)*ROW(Sheet1!$A$1:$Z$999))-1+ROW(1:1),SUMPRODUCT((Sheet1!$A$1:$Z$999=$A$1)*COLUMN(Sheet1!$A$1:$Z$999))-2+COLUMN(A:A))

として右方・下方にフィル

※Sheet1!$A$1は、元シートの左上隅を示すものなので、元データの範囲に関わらずA1固定です。
※それぞれのセルでそれぞれに検索を繰り返すので、
　データの範囲が広いと再計算に非常に時間がかかります。
----------------------------------------------------------------------
再計算負担を軽減するためには…

●Ｂ案：作業セルに、基準となるセルの行番号・列番号を書き出す

　１．Sheet2のB1セルを
　　　=SUMPRODUCT((Sheet1!$A$1:$Z$999=$A$1)*ROW(Sheet1!$A$1:$Z$999))-1
　　としてフォント色を白にする。
　２．Sheet2のC1セルを
　　　=SUMPRODUCT((Sheet1!$A$1:$Z$999=$A$1)*COLUMN(Sheet1!$A$1:$Z$999))-2
　　としてフォント色を白にする。
　３．Sheet2のA2セルを
　　　=OFFSET(Sheet1!$A$1,$B$1+ROW(1:1),$C$1+COLUMN(A:A))
　　として右方・下方にフィル。
----------------------------------------------------------------------
あるいは、

●Ｃ案：配列数式として確定する

　１．Sheet2のA2:G8セルを選択する
　２．数式バーに
　　　=OFFSET(Sheet1!$A$1,SUM(IF(Sheet1!$A$1:$Z$999=$A$1,ROW(Sheet1!$A$1:$Z$999),0)),SUM(IF(Sheet1!$A$1:$Z$999=$A$1,COLUMN(Sheet1!$A$1:$Z$999),0))-1,7,7)
　　と入力して　【　CtrlとShiftを押しながら　】Enter。

　※通常の数式は、数式を打ち込んだあとEnterキーで確定しますが、
　　これは配列数式なので、CtrlとShiftを押しながらEnterで確定してください。
　※３案の中ではこれが最も速い処理です。
----------------------------------------------------------------------

いずれもExcel2003で動作確認済。

以上ご参考まで。

お礼

お返事が遅くなり大変申し訳ありません。
回答いただいた内容が私には難しくて
試してみようと思いつつ時間がたってしまいました。

一度質問を閉じますが、こちらの内容を試してみたいと思います。

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

回答No.2

hallo-2007

整理しなおしました。とありますが、データがグループで縦横に配置されているのは、集計にとって大変厄介です。
私だったらのシートの配置案ですが、
　A　　　B　　　C　D　　E　　　F　　　G
年月　Elements ICP XRD Manual Estim Pond
として、縦方向に入力してきます。合計行は作成せずに集計時に計算させます。
A列にオートフィルター掛ければ、問題が解決していたはずです。

ちなみに、無理やり作業行２行案ですが、データのシートがSheet1として別シートの
A1に、検索したい年月を入れる　2005,1
B1に、=IF(ISERROR(MATCH($A1,Sheet1!A:A,0)),"",MATCH($A1,Sheet1!A:A,0))
C1に、=IF(A2="","",COLUMN())
B1:C1を右へずらずらとオートフィルしておく
D1に、=INDEX(Sheet1!$1:$20000,SUM($2:$2)+ROW(A1)-1,SUM($3:$3)+COLUMN(A1)-1)
D1を、右へ７つ、下へ７つ、オートフィルするでは、いかがですか。

お礼

お返事が遅くなり大変申し訳ありません。
回答いただいた内容が私には難しくて
試してみようと思いつつ時間がたってしまいました。

一度質問を閉じますが、こちらの内容を試してみたいと思います。

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

回答No.1

mitarashi

配置の規則性が分かれば関数でも可能かも知れませんが、分かりませんのでマクロで行う事例です。
'前提　Excel2000用のコード
'マクロは、データの入ったWorkBookに置く
'検索は　例えば「2008,1」で検索すると、1個だけがヒットする事
'検索する年月を、Sheet1のA1セルに入力して、マクロを実行する
'データはSheet2にある
'検索してヒットしたデータをSheet1のA2セル以下に、値だけ複写する
Sub test()
Dim foundCell As Range
Dim cellArray As Variant

With ThisWorkbook
Set foundCell = .Sheets("Sheet2").UsedRange.Find(.Sheets("Sheet1").Range("A1").Value)
If Not foundCell Is Nothing Then
'セル範囲拡大は、提示された構造に合わせた
cellArray = foundCell.Resize(8, 7)
.Sheets("Sheet1").Range("a2").Resize(8, 7) = cellArray
End If
End With
End Sub
やりたいのはこんな事でしょうか？

お礼

お返事が遅くなり大変申し訳ありません。
回答いただいた内容が私には難しくて
試してみようと思いつつ時間がたってしまいました。

一度質問を閉じますが、こちらの内容を試してみたいと思います。

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