時間ごとの集計をしたいです。

質問

■やりたい事
・時間帯毎の合計値を求めたいです。
・ピボットテーブルではなく、関数で求めたいです。

■データ配列
A B
時間　　個数
09:10 　1
10:20　　2
11:00　　1
11:10　　1
11:20　　1

■理想の回答
09:00-09:59　1個
10:00-10:59　2個
11:00-11:59　3個

■条件
　・EXCELL2010
・関数で求めたいです
■ＴＲＹした事
　時間帯毎のセルの数は出せました。
　=SUMPRODUCT(($A$1:$A$5>=9:00)*($A$1:$A$5<=9:59))
　SUMIFやSUMPRODuct等チャレンジしましたが、うまくいきません。

たくさんのWEｂページをみましたが、自分が求める正解にたどり着くことが出来ませんでした。

回答No.1

mt2015 · ベストアンサー

SUMPRODUCT関数でやってみました。
=SUMPRODUCT(B$2:B$6*(HOUR(A$2:A$6)=HOUR(D2)))

お礼

こういうことが出来るんですね！
大変勉強になります。
ありがとうございました。

回答No.6

bunjii

回答No.4の追加です。
貼付画像のような表の場合は下記の数式をE2セルへ設定して下へ必要数コピーすれば良いと思います。
=IF(D2="","",SUMIFS(B:B,A:A,">="&LEFT(D2,5)*1,A:A,"<="&RIGHT(D2,5)*1))
SUMIFS関数の方がSUMPRODUCT関数より負荷が少ないので多量のデータの場合は今回の提示を優先された方が良いでしょう。

お礼

何度も回答いただきありがとうございます。
こういう方法でも出来るのですね。
これからもっと勉強したいと思います。

回答No.5

imogasi

エクセル関数を使っての回答を求めているようだが、時間の無駄だろう。
エクセルピボットテーブルでグループ化ー時間で１発で表ができるのではないか。
エクセルの勉強を広げておくべきだ。
何で関数を用いて、でなのだろう。
データ変動に対しての即時反応性か？

お礼

ピボットを使いたいと思っているのですが、
WEBを見てもやりたいことが出来ず。
自分で理解できないのであれば、
関数で求めたいと思った次第です。

これからピボットテーブルも勉強したいと思います。

回答No.4

bunjii

>・関数で求めたいです
提示の数式では時間の指定が固定になっていて、然も、「9:00」と「9:59」は時刻を表すシリアル値になっていません。
誤りを確認しながら修正するには関数ウィザードを使うと分かり易くなります。
=SUMPRODUCT(($A$1:$A$5>=9:00)*($A$1:$A$5<=9:59))
　　　　　↓
=SUMPRODUCT(($A$1:$A$7>="9:00"*1)*1,($A$1:$A$7<="9:59"*1)*1,$B$1:$B$7)
=SUMPRODUCT(($A$1:$A$7>="10:00"*1)*1,($A$1:$A$7<="10:59"*1)*1,$B$1:$B$7)
=SUMPRODUCT(($A$1:$A$7>="11:00"*1)*1,($A$1:$A$7<="11:59"*1)*1,$B$1:$B$7)
時間帯を可変ににすれば1つの数式を下へコピーすることで対応できます。
「理想の回答」で下記のように提示していますが同一シートの別の列に集計したいのですか？
時間帯　　　個数
09:00-09:59　1個
10:00-10:59　2個
11:00-11:59　3個
結果のセル位置を提示して頂ければ具体的な数式を提示できます。

お礼

回答いただきありがとうございます。
時間に*1を入れているのは何かの意味があるのかと思いますが、
そこまでの知識がありません。

今回の質問は1日の9：00～18：00までの

1行目、題名
2～13行目　時間ごとの集計
その下に生データのリスト
とするので、
教えていただた方法でうまくいきました。
ありがとうございます。

回答No.3

msMike

E2: =SUMPRODUCT((A$2:A$6>=D2)*(A$2:A$6<D2+"1:0")*(B$2:B$6))

お礼

＋”１：０”は初めて拝見しました。

こういう使いかたが出来るんですね。
本当にありがとうございました。

回答No.2

山田 太郎（@f_a_007）

EXCELは利用したことのないど素人ですが・・・。「SUMIFSを使えばいけるのでは？」と思います。

お礼

ご教授いただきありがとうございます。
SUMIFSで出来ました。