[[20150323221912]]　『INDIRECTとoffset？』（minoru）　ページの最後に飛ぶ

『INDIRECTとoffset？』（minoru）

お世話になります。
同じブック内に、データの入力されたシート名　北海道、秋田、青森、・・・沖縄の４０シートあります。
各シートは同じ書式とレイアウトです。
Ａ列は項目　Ｂ列Ｃ列Ｄ列・・・は年月別にデータが入力されています。
Ｂ１、Ｃ１、Ｄ１のセルには年月が入力されています。
まとめシートのＡ列に項目、Ｂ１セルに「北海道」Ｃ１セルに「秋田」Ｄ１セルに「青森」・・・以下シート名が入力されています。
まとめシートのＡ１セルに年月を入力すると、各データシートの該当する年月のデータをＢ２以下のセルに抽出する関数をご教示願います。

シート名「北海道」

       [A]	[B]	[C]	[D]	[E]	[F]
[1]		2014/4	2014/5  2014/6	2014/7	2014/8
[2]	収入	100	200     300     300     200
[3]     支出　　 50      60      60     100      60
[4]     利益     50     140     240     200     140

シート名「秋田」

       [A]	[B]	[C]	[D]	[E]	[F]
[1]		2014/4	2014/5  2014/6	2014/7	2014/8
[2]	収入	200	400     350     500     150
[3]     支出　　 70      80      70     100      50
[4]     利益    130     320     280     400     100

シート名「まとめ」

       [A]	[B]	[C]	[D]	[E]	[F]
[1]	2014/5	北海道　秋田　　青森　　
[2]	収入	200	400     
[3]     支出　　 60      80          
[4]     利益    140     320

まとめシートのＡ１セルに年月入力で各データシートの年月別データを抽出する。
関数でもマクロでも結構ですのでよろしくお願します。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 マクロの方がよさそうですが、そちらは上級者にまかせて･･･
 まとめシートのB2セル =IFERROR(HLOOKUP($A$1,INDIRECT(B$1&"!1:4"),MATCH($A2,INDIRECT(B$1&"!A2:A4"),0)+1),"")
 右と下にフィルコピー
（se_9） 2015/03/24(火) 07:46

 B2 =OFFSET(INDIRECT(B$1&"!A1"),ROW(A1),MATCH($A$1,北海道!$B$1:$F$1))
 B2 =SUMIF(INDIRECT(B$1&"!A:A"),$A2,INDIRECT(B$1&"!C"&MATCH($A$1,北海道!$1:$1),0))

 ？？？
　
（GobGob） 2015/03/24(火) 07:59

 ああ、GobGobさんの回答を見て気付いたけど1行目はどのシートも共通しているから･･･
 =IFERROR(INDEX(INDIRECT(B$1&"!B2:F4"),ROW(A1),MATCH($A$1,北海道!$B$1:$F$1,0)),"")
（se_9） 2015/03/24(火) 08:05

素人まくろですが・・・・

「まとめ」シート上でマクロを行ってください。
まとめシートには、Ａ列の項目名は入っているものとします。
作用上シート名をＡ列の１０行目から貼り付けています。

Sub まとめ()
Dim Sh As Object
Dim rx As Integer
Dim lx As Integer
Dim y As Integer
Dim z As Integer
Dim w As Integer
Dim wknm As String

Application.ScreenUpdating = False

Range("A10", "A" & Cells(Rows.Count, 1).End(xlUp).Row + 1).ClearContents
lx = Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(1, 2), Cells(4, lx)).ClearContents
x = 0
y = 10
For Each Sh In ActiveWorkbook.Sheets

    x = x + 1
    Cells(y, 1).Value = Sh.Name
    y = y + 1
Next Sh

rx = Cells(Rows.Count, 1).End(xlUp).Row
lx = Sheets("北海道").Cells(1, Columns.Count).End(xlToLeft).Column

For y = 2 To lx

    If Cells(1, 1) = Sheets("北海道").Cells(1, y) Then
        w = y
        Exit For
    End If
Next y

z = 2
For y = 10 To rx

    If Cells(y, 1) <> "まとめ" Then
        wknm = Cells(y, 1)
        Sheets(wknm).Select
        Range(Cells(2, w), Cells(4, w)).Select
        Selection.Copy
        Sheets("まとめ").Select
        Cells(2, z).Select
        ActiveSheet.Paste
        Cells(1, z) = wknm
        z = z + 1
    End If
Next y

Application.ScreenUpdating = True

MsgBox "終了"

End Sub
（コヨーテひな） 2015/03/24(火) 11:33

こちらはどうでしょう？

Sub aaa()
Dim a As Integer
Dim b As Integer
Dim c As Integer
a = 1
b = 2
c = 2
Do Until c = 4
If Worksheets("まとめ").Cells(1, 1).Value = Worksheets("北海道").Cells(1, c).Value Then
Exit Do
End If
c = c + 1
Loop
Do Until b = 4

    Do Until a = 4
    If Sheets(a).Name = Worksheets("まとめ").Cells(1, b).Value Then
    Worksheets("まとめ").Cells(2, b).Value = Sheets(a).Cells(2, c).Value
    Worksheets("まとめ").Cells(3, b).Value = Sheets(a).Cells(3, c).Value
    Worksheets("まとめ").Cells(4, b).Value = Sheets(a).Cells(4, c).Value
    End If
    a = a + 1
    Loop
a = 1
b = b + 1
Loop
End Sub
これは青森まで行う時のマクロです。あとはお好みに合わせて変更してください。
（スズメ） 2015/03/24(火) 12:22

se_9　様
GobGob　様

早朝よりの回答ありがとうございます。
OFFSETの中でINDIRECTの使い方がよく判らなかったのですが思うように抽出できました。
sumif関数使用にはビックリしました。

コヨーテひな　様
スズメ　様

コードのご教示ありがとうございます。
自宅のため手元に試行するファイルがありませんので、明日会社で試してみます。

（minoru） 2015/03/24(火) 21:42

コメント返信：

[ 一覧(最新更新順) ]

[[20150323221912]] 『INDIRECTとoffset？』（minoru） ページの最後に飛ぶ

[[20150323221912]]　『INDIRECTとoffset？』（minoru）　ページの最後に飛ぶ