VBAで期間を指定し該当する期間のデータを合計する方法を教えてください。

質問

お世話になっております。とても困っています。
皆さんよろしくお願いします。

１月、２月、３月・・・１２月というシート名で以下のようなデータが入っているとします。
”1月”のシートの場合

月　日　人　(1)データ　(2)データ
１　１　　Ａ　　１０　　１００
１　２　　Ａ　　２０　　１５０
１　９　　Ａ　　１５　　１００
１　１　　Ｂ　　１０　　２００
１　５　　Ｂ　　１０　　１００
・　・　　・　　　・　　　・
・　・　　・　　　・　　　・

このようなデータが１月～１２月まで存在します。

このデータから、別のシート（”入力”というシート）で入力した期間に該当する日付のシートから、(1)のデータの合計値と(2)のデータの合計値を”結果”というシートに計算されるという方法を知りたいです。
是非、よろしくお願いします。

”入力”というシート
　　　月　　日　　　　　月　　日
期間　１　　１　　～　　２　　５

”結果”というシート
　　(1)計　　(2)計
Ａ　５０　　５５０
Ｂ　３５　　６００

回答No.2

mitarashi · ベストアンサー

一発で算出出来る案を提示される強者が現れるかもしれませんが、とりあえず前座という事で、配列数式のお勉強をした結果を報告いたします。基本通りで何のひねりもありませんが…
１．まず、日付での抽出がやりにくいので、作業列を各月のシートに設けてください。入れる式の例： =DATEVALUE("2008/" & A2 & "/" & B2)
........A........B........C..................D........E......................F..................
..1....月......日......月日............人......(1)データ......(2)データ..
..2....1........1........2008/1/1....Ａ......10....................100..............
..3....1........2........2008/1/2....Ａ......20....................150..............
簡便のため、1月のデータはSheet1,2月のデータはSheet2,以下同様とします。集計は、SheetAを設けてそこで行います。
............A.......................B.......................C........
..1....開始月日.........終了月日...................
..2....2008/1/2.........2008/2/3...................
..3................................................................
..4.............................(1)データ.................
..5.............................1月...................2月....
..6....Ａ.....................35.....................30......
..7....Ｂ.....................10.....................10......
..8................................................................
..9.............................(2)データ.................
10....Ａ.....................250...................250....
11....Ｂ.....................100...................200....

B6セルに入れる式　{=SUM(IF((Sheet1!$D$2:$D$100=SheetA!$A6)*(Sheet1!$C$2:$C$100>=SheetA!$A$2)*(Sheet1!$C$2:$C$100<=SheetA!$B$2),Sheet1!$E$2:$E$100,""))}
配列数式ですので、Ctrl+Shift+Enterで入力確定してください。
・行方向は算出対象範囲が変わる行は修正の必要があります。
・列方向は抽出先シート名を変更する必要があります。置換を使うと楽です。
・データは100行目までと余裕を持たせてありますが、目的に合わせて変更してください。大きくても（再計算に時間はかかるでしょうが）支障は無い様です。
・言わずもがなですが、12月ヶ月のデータを集計列を設けて集計してください。

補足

ありがとうございます。
さっそく教えていただいた式で試してみましたが、”＃ＶＡＬＵＥ”のエラーとなってしまします。
ＳＵＭ関数の最後（Sheet1!$E$2:$E$100の後ろ）の「,""」はどういう意味でしょうか。
これを除くと、セルB6の結果は0となります。
よろしくお願いします。

回答No.5

NaoDorry

各シートの列タイトル
日付｜名前｜データ１｜データ２｜チェック

チェック列の数式（入力シートの日付にマッチする場合１が入ります）
=IF(AND(A2>入力!$A$1,A2<入力!$B$1),1,0)
下にフィル

"入力"シート
A1=集計期間開始の日付を入力　B1=集計期間終了の日付を入力

ここまで出来たら各シートをcheck列で降順ソートします

"結果"シートA1をクリックして、メニューのデータ→統合
集計の方法　合計

統合元範囲で各シートのcheck列が１の行を選択して追加
この時、日付の列を飛ばして名前列からデータ２までを選択します
（5行目まで１が入っていたらBの1列からDの5行までを選択）
各シートを同じ作業で追加

統合の基準　上端行、左端行にチェックでＯＫを押すと
望みの計算結果が表示されると思います。
自動で処理したい場合は入力シートにボタンを配置して
ボタンに以下のコードを登録して下さい

Sub calc()
Dim i, a, b As Integer
Dim hani() As Variant

For i = 1 To 12 'ワークシートをループ
a = 1
Do While Sheets(i & "月").Cells(a, 1) <> "" '最終行を調べる
a = a + 1
Loop
'降順でソート
Sheets(i & "月").Activate
Range(Cells(1, 1), Cells(a - 1, 5)).Select
Selection.Sort key1:=Range("E2"), order1:=xlDescending

'checkが1の最終行を調べる
b = 2
Do While Sheets(i & "月").Cells(b, 5) <> "1"
b = b + 1
Loop
'統合する範囲を配列に格納
ReDim Preserve hani(i - 1)
hani(i - 1) = Sheets(i & "月").Range(Cells(1, 2), Cells(b, 4)).Address(ReferenceStyle:=xlR1C1, External:=True)
Next
'統合する
Sheets("結果").Range("A1").Consolidate _
Sources:=hani, Function:=xlSum, TopRow:=True, LeftColumn:=True
End Sub

・シート名は半角数字+月として、12月まで作成しないとエラーになります。

お礼

ご回答ありがとうございました。
いままで「統合集計の方法」を使ったことが無かったので、勉強になりました。もう少し使い方を勉強してみます。
ありがとうございました。

回答No.4

mitarashi

ANo.2です。
式を入力して確定するときに、Enterでは無くて、Ctrl+Shift+Enterで行いましたか？確定後の式が、中括弧{　}で囲われていますか？
これがうまくいっていないと、#VALUEになると思います。配列数式の解説は、参考URLなどでお調べ下さい。

参考URL：

http://home.att.ne.jp/zeta/gen/excel/c01p09.htm,http://pc.nikkeibp.co.jp/pc21/special/hr/hr4.shtml

お礼

早速のご回答ありがとうございます。
Ctrl+Shift+Enterで確定して、無事にうまくできました。
有難うございました。

回答No.3

hallo-2007

わかりやすい方法で
1月シートに2行ほど挿入
　　A　　　B　　　C　　　D
1　　　　　Ａ　
2　　　　　Ｂ
と準備
A1には　=DATE(2008,入力!A2,入力!B2)
A2には　=DATE(2008,入力!A3,入力!B3)
C1には　=SUMPRODUCT((DATE(2008,$A$4:$A$8,$B$4:$B$8)>$A$1)*(DATE(2008,$A$4:$A$8,$B$4:$B$8)<$A$2)*($C1=$C$4:$C$8)*D$4:D$8)
入れて右へフィル、下フィルしてみる。
同様の2行を全てのシートへ挿入、コピィ
結果のシートには1月～12月のシートを合計を串刺しでSUM関数

月ごとにシートを分けた（多分、このままでは年毎にブックが出来る)事が集計を厄介しています。
この際、ひとつのシートに毎月のデータを縦方向にコピィして、今後もひたすら縦方向に入力したほうが良いと思います。
日付も年月日(2008/1/1)で入力しておいたほうが良いでしょう。
試しに、一枚のシートにデータ集めて、結果のシートに入れる関数を考えてみてください。
SUMPRODCUT関数でよいと思いますが、一度で集計できて、年を跨いだ期間も集計できるでしょう。

お礼

ご回答いただき有難うございました。
入力されたデータシートが莫大な数の行で存在するので、行数が少ないデータの際に使わせていただきます。
参考になりました。
ありがとうございました。

回答No.1

NaoDorry

確認なのですがVBAでやらなくてはダメなんですか？
このような処理はもともとエクセルの得意とするところで
ワークシート関数やピポットテーブルを使うほうがよっぽど簡単です。

VBAでやるとなると、ある程度のコードを書いていただかないと
全てを書くにはあまりに長すぎると思うのですが。

補足

ご解答ありがとうございます。
VBAでなくても関数でできれば教えてください。