時間ごとの集計。

質問

初めまして、よろしくお願いします。
　分間隔ごとの集計できる関数

例）

　　　　　　A　　　　　　　　　　　B　　　　　　　　　
１　１０時３０分１５秒　　　　　　５
２　１０時３０分３０秒　　　　　１０
３　１０時３０分５０秒　　　　　２０
４　１０時３１分５秒　　　　　　　５
５　１０時３１分１５秒　　　　　１５
６　１０時３２分４０秒　　　　　　５
７　１０時３３分５秒　　　　　　２０
８　１０時３３分５０秒　　　　　２５
９　１０時３４分５秒　　　　　　１０

を
　　　　　A　　　　　B
１　１０時３０分　　３５
２　１０時３１分　　２０
３　１０時３２分　　　５
４　１０時３３分　　４５
５　１０時３４分　　１０

例では１分間隔ですが、５分間隔、１０分間隔と作りたいと思いますので、教えて頂きたく、よろしくお願いします。

回答No.2

Cupper · ベストアンサー

ご使用のアプリケーションは Excel でよろしいでしょうか。

質問の意図が良く伝わりませんが、ひょっとして秒単位で作成された一覧から
分単位の時間で1分間隔や5分、10分間隔で集計を取りたいと言うことかな。

SUMIF関数やSUMPRODUCT関数で可能です。
分かりやすい（説明しやすい）のは SUMIF関数でしょう。

　SUMIF(条件範囲,条件,合計範囲)

の書式です。
この場合、条件の設定方法が面倒なだけです。
　※ヘルプでは分かりにくいので補足しますと、
　　「条件は同じ数値を指定可能なときは数値で、
　　　数式を含めてそれ以外の場合は文字列で与える」
　　ことになります。
セルをコピーしたときに数字が増えるようにするには ROW関数や COLUMN関数を使うと便利です。
ROW(A1)*5 や ROW(A1)*10 などとしてコピーすると、セルが一つ隣に移るたびにそれぞれ５、１０ずつ数値が増えます。
　※ ROW関数は行番号、COLUMN関数は列番号を返してくる関数です。

　SUMIF(A:A,"<"&ROW(A1)+1,B:B)
これで、A列の２より小さい数字に対するB列の合計値を求めることができます。
同様に
　SUMIF(A:A,"<"&ROW(A2)+1,B:B)
これで、A列の３より小さい数字に対するB列の合計を求めることができます。
２から３より小さい場合の合計を求めるのであれば、
３より小さい場合の合計から２より小さい場合の合計を引けばOK
　SUMIF(A:A,"<"&ROW(A2)+1,B:B)-SUMIF(A:A,"<"&ROW(A1)+1,B:B)
とすることで対応できます。

あとはA列の時間の表示に関する情報（文字列なのか書式で表示させているのか）が不明なため、実際の条件はその他の関数を組み合わせて設定してください。

お礼

詳しく解説まで付けて頂き、ありがとうございます。参考にさせて頂きたいと思います。

回答No.6

ka_na_de

＃４のka_na_deです。

マクロは全く分からないとの事なので、無理にすすめません。
結果オーライでは後々応用できないですよね。

一応、使い方だけ記しておきますので、
マクロに興味が沸いてきたときにでも、試してみてください。

＜使い方＞
１）下のシート名のタブの上で右クリック
２）「コードの表示」をクリック
３）左側のプロジェクトエクスプローラーで、
　　VBAProjectの文字の上で右クリック、「挿入」→「標準モジュール」
　（プロジェクトエクスプローラーが表示されていなければ、
　　上部の「表示」メニューから選択表示できます。）
４）右側に上記のマクロを貼り付けます。
　　実行は、上部の再生マークボタンを押してください。

＜注意点＞
マクロはやり直しができません。
大切なデータはバックアップをとってから試してみてください。

お礼

補足ありがとうございます。マクロには興味はありますが、知識不足で理解できない状態です。教えて頂いたことは今後の参考にさせて頂きたいと思います。

回答No.5

imogasi

いままであまり、下記の回答をした人はないと思うが
DSUM関数　を利用してみました。
例データ　A1:B10　　項目見出し（リスト形式のため）が必要で追加
時刻 計数
10時30分15秒 5
10時30分30秒 10
10時30分50秒 20
10時31分05秒 5
10時31分15秒 15
10時32分40秒 5
10時33分05秒 20
10時33分50秒 25
10時34分05秒 10
合計　　　 115(参考）
ーーー
F1:G10まで、各条件を並べる。横に並べるとAND条件になる。
F列　　　G列
時刻 時刻
>=10:30:00 <10:31:00
時刻 時刻
>=10:31:00 <10:32:00
時刻 時刻
>=10:32:00 <10:33:00
時刻 時刻
>=10:33:00 <10:34:00
時刻 時刻
>=10:34:00 <10:35:00
ーー
H2に
=DSUM($A$1:$B$10,2,F1:G2)　
と入れてH10まで式を複写。
H3,H5,H7,H9を削除。
ーー
結果（F1:H10)　答えはH列
時刻 時刻
>=10:30:00 <10:31:00 35
時刻 時刻
>=10:31:00 <10:32:00 20
時刻 時刻
>=10:32:00 <10:33:00 5
時刻 時刻
>=10:33:00 <10:34:00 45
時刻 時刻
>=10:34:00 <10:35:00 10
115（合計。Σ出だした。参考）
ーー
個人的疑問・要望
MSはなぜDSUMにおいて、毎回項目を必要にした仕組みにするのだろうか。
（なんとなく本質問コーナーの回答で、D○○関数は人気が無いようだ。）
そのため上記のように、毎条件が変わるごと「時刻 時刻」を並べなければならなくなり、表が間延びする。上記ではH3、H5など式を抹消もせざるを得ないことになった。また式の複写がうまくいかない。
改善してほしいと思っている点ですが（ゴマメの歯軋り）。
何か良い方法知っている方もいるかもしれない。

補足

回答ありがとうございます。せっかく回答して頂きましたが、私のやり方が悪いのか希望したとおりになりませんでした。この方法は参考にさせて頂きたいと思います。

回答No.4

ka_na_de

マクロは選択外だと思いますが、作ってみました。

＜前提＞
　Sheet1:元データ
　Sheet2のA列に集計する時間が入力済み
　Sheet2のB列に集計を出力

'＜マクロ＞
Sub test()
　Dim r1 As Long
　Dim r2 As Long
　Dim Ws1 As Worksheet
　Dim Ws2 As Worksheet

　Set Ws1 = Worksheets("Sheet1")
　Set Ws2 = Worksheets("Sheet2")

　Ws2.Range("B:B").ClearContents
　For r2 = 1 To Ws2.Range("A65536").End(xlUp).Row
　　For r1 = 1 To Ws1.Range("A65536").End(xlUp).Row
　　　If Ws1.Cells(r1, "A").Value >= Ws2.Cells(r2, "A").Value _
　　　　　And Ws1.Cells(r1, "A").Value < Ws2.Cells(r2 + 1, "A").Value Then
　　　　　　Ws2.Cells(r2, "B").Value = Ws2.Cells(r2, "B").Value + Ws1.Cells(r1, "B").Value
　　　End If
　　Next r1
　Next r2
End Sub

補足

回答ありがとうございます。
　わざわざマクロで作って頂き、ありがとうございます。残念ながら全くの素人のため、せっかく作って頂いたマクロも理解できないため、使うことができません。

回答No.3

noname#204879

Sheet2!B1: =SUMPRODUCT((FLOOR(Sheet1!A$1:A$20,"0:1")=A1)*(Sheet1!B$1:B$20))

補足

せっかく教えて頂きましたが、これもなぜかうまくいかないようです。こちらの時間の書式設定に問題があるのでしょうか？（０時００分００秒と表示されます。）

回答No.1

maron--5

>１分間隔
=SUMPRODUCT((--TEXT(Sheet1!$A$1:$A$10,"hh:mm")=Sheet2!A1)*Sheet1!$B$1:$B$10)

>５分間隔
=SUMIF(Sheet1!$A$1:$A$10,">="&A1,Sheet1!$B$1:$B$10)-SUMIF(Sheet1!$A$1:$A$10,">"&A1+"0:5",Sheet1!$B$1:$B$10)

補足

せっかく教えて頂きましたが、うまくいかないようです。残念です。