EXCEL 数字の集計方法でお知恵をお貸しください

質問

EXCEL2000を利用しています。

■はじめに
SHEET1のＡ列に　年月日(書式はyyyy/mm/dd)、Ｂ列に　時(書式はテキスト)、Ｃ列にランダムなデータ。
SHEET2のＡ列に　年月日(書式はyyyy/mm/dd)　が入っています。

SHEET1 Ａ列の 年月日は、同じ日が24回繰り返して、1日ずつ増えています。Ｂ列は0～23の連続番号が繰り返して並んでいる。（※写真参照してください）

SHEET2 Ａ列の 年月日は、一日ずつ増える連続データです。

■相談したいこと
SHEET2 Ｂ列に SHEET1の同日7時～翌日6時までの和を表示させたい。
例えば、写真のようにSHEET2の2010年4月1日には、SHEET1の 2010年4月1日7時～2010年4月2日6時までの値を足したものを表示させたい。

SHEET2のＢ列に、どういった関数をいれれば良いか、若しくは、VBAでも結構です。良いお知恵をお貸しください。

回答No.4

popuplt · ベストアンサー

Ｎｏ２です。作業列なしで・・・
=SUMIF(Sheet1!$A$2:$A$1000,A2,Sheet1!$C$9:$C$1007)
　　　　　　　　　　　↑　　範囲がずれています　↑
　　　　　　　　　　　 ￣￣￣￣￣￣￣￣￣￣￣￣

Excel関数のバグ?を利用します。
sumifの検索範囲と合計範囲は行がずれていても計算できます。
4月1日の場合は、検索で配列（A2:A1000)の1～24行となり、合計はC9からの24個が、
4月2日の場合は、25～48行となり、合計はC33からの24個のデータが合計されます。

お礼

ご返事おくれて、申し訳ございません。回答ありがとうございました。
NO3の方の回答が、一番秀逸だと思いましたが、この回答を見て、感動しました。
これは、すばらしいです。なるほどなー。と関心させられました。
とても勉強になりました。ありがとうございました。

回答No.5

KURUMITO

シート２のB２セルには次の式を入力して下方にドラッグコピーします。日付に応じた式となっていますのでシート２ではどこの日付から始まっても対応します。

=IF(OR(A2="",COUNTIF(Sheet1!A:A,A2)=0),"",SUM(INDEX(Sheet1!C:C,MATCH(A2,Sheet3!A:A,0)+7):INDEX(Sheet1!C:C,MATCH(A2,Sheet1!A:A,0)+30)))

出来るだけ要望に近く日時を念頭に置いた式を作って対応するとしたら次のような式でもよいでしょう。一部の行のデータが無い場合でも対処できる式となります。

=SUM(INDEX(Sheet1!C:C,MATCH(A2,Sheet1!A:A,0)+MATCH(7,INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0)):INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0)+24),0)-1):INDEX(Sheet1!C:C,MATCH(DATE(YEAR(A2),MONTH(A2),DAY(A2)+1),Sheet1!A:A,0)+MATCH(6,INDEX(Sheet1!B:B,MATCH(DATE(YEAR(A2),MONTH(A2),DAY(A2)+1),Sheet1!A:A,0)):INDEX(Sheet1!B:B,MATCH(DATE(YEAR(A2),MONTH(A2),DAY(A2)+1),Sheet1!A:A,0)+50),0)-1))

お礼

ご返事が遅れて、申し訳ございません。
日付に抜けがあった場合に、最適な関数だと思いますが、今回日付に抜けはございません。
しかし、勉強になりました。ありがとうございました。

回答No.3

tom04

こんばんは！

Sheet1もSheet2も規則正しく並んでいるようですね！
結局、シリアル値等は一切考えなくて
Sheet1のC9セルから24行ずつの合計を表示すればよいと思います。

Sheet2のB2セルに
=SUM(OFFSET(Sheet1!$C$9,24*(ROW(A1)-1),,24))

という数式を入れオートフィルで下へコピーではどうでしょうか？m(_ _)m

お礼

ご返事が遅くなり申し訳ございません。回答ありがとうございました。
長すぎず、複雑すぎず 一番求めていた、回答に近いものだと思います。

回答No.2

popuplt

Ｄ列を作業列として使います。
[D1]＝A1-(B1<7) 下へコピーします。
Sheet2の[B1]＝SUMIF(Sheet1!D1:D100,A1,Sheet1!C1:C100)

お礼

ご返事が遅くなり申し訳ございません。回答ありがとうございました。
excelでこんなことができるのか！？って感動しました。
とても斬新で勉強になりました。

回答No.1

MarcoRossiItaly

=sumproduct((sheet1!A$2:A$10000=a2)*(7<=sheet1!B$2:B$10000)+(sheet1!A$2:A$10000=A2+1)*(sheet1!B$2:B$10000<7),sheet1!C$2:C$10000)
あるいは
=sumifs(sheet1!C$2:C$10000,sheet1!A$2:A$10000,a2,sheet1!B$2:B$10000,">=7")+sumifs(sheet1!C$2:C$10000,sheet1!A$2:A$10000,a2+1,sheet1!B$2:B$10000,"<7")　　…… Excel2007 以後

SUMIFS 関数の数式では、例えば「"7<="」というふうに、不等号と数字を逆順で書くと正しく機能しないので、ご注意ください。なお「10000」という数字は適当ですから、お使いのシートの状況に合わせて変更してください。

お礼

ご返事が遅くなり申し訳ございません。回答ありがとうございました。
理解するのに時間がかかりましたが、sumproductのいい勉強になりました。