Excelによるある一定間隔内の数値集計とグラフ化について

質問

よろしくお願いします。
Excel2003(OS：Windows XP Professional)についての質問です。

サンプルのように、ある時刻に目の前を通った人の人数をカウント
したとします。
例えば10分間隔に区切って人数を集計(＊)し、縦軸を人数、横軸を時間
として折れ線グラフ化したいと思います。
どのように工夫すればより効率よくできるでしょうか。
　＊例：9:00～9:10 であれば、集計値は「4」。
なお、サンプルは微量ですが、実際には膨大な量のデータです。
また、判りやすくするために、人をカウントしていますが、
実際は全くことなるデータについて集計とグラフ化を実施したく
質問しています。
どうぞ、アドバイスのほど宜しくお願い致します。
floor関数やsumif関数が使えるのでは？と思って検討中です。

以下、サンプルです。
－－－－－－－－－－－－－－－－－－－－－－－－
時間　　　人数
9:01:44 　　1
9:09:55 　　3
9:32:18 　　2
10:04:11 　　1
10:08:48 　　6
10:38:37 　　1
11:22:21 　　0
11:39:59 　　0
12:57:37 　　2
14:02:18 　　0
16:53:10 　　9
17:02:27 　　1
17:25:32 　　3
17:32:41 　　11
17:44:48 　　3
17:47:38 　　9

回答No.2

Mr_Holland · ベストアンサー

折れ線グラフを作るときの横軸(時間)は等間隔にされたいのですよね。
　sumif関数を使う方法をお伝えします。
　データは2行目以降に入力されていて、1行目はタイトルとして使用されているとし、使用されている列はA列とB列だけだとします。

０）D1セルに例えば「時間帯」と入れ、E1セルに例えば「時間帯別人数」などとタイトルをつけます。
１）D2セルに「9:00」と入力します。
２）D3セルに「=D2+"0:10"」と入力し、以下、必要な時間範囲が出るまでコピーします。
３）E2セルに「=SUMIF(A:A,">="&D2,B:B)-SUMIF(A:A,">="&D3,B:B)」と入力し、以下コピーします。
４）列Dと列Eの範囲を選択して、折れ線グラフを描画します。

　これで求めるグラフが得られるのではないでしょうか？
　もし、時間帯を広げてグラフを作成したいときは、２）の"0:10"の値を適当に変えて見てください。

お礼

シンプルかつ的確なご回答、ありがとうございます。
非常に参考になりました。
また、お礼の投稿が遅れ、申し訳ありませんでした。

回答No.4

noname#204879

A　　　 B　　C　 D　　　E
1　 時間　　　人数　　 時間　 人数
2　　9:01:44　　 1　　　9:00　　 4
3　　9:09:55　　 3　　　9:10　　 0
4　　9:32:18　　 2　　　9:20　　 0
5　 10:04:11　　 1　　　9:30　　 2
6　 10:08:48　　 6　　　9:40　　 0
7　 10:38:37　　 1　　　9:50　　 0
8　 11:22:21　　 0　　 10:00　　 7
9　 11:39:59　　 0　　 10:10　　 0
10　12:57:37　　 2　　 10:20　　 0
11　14:02:18　　 0　　 10:30　　 1
12　16:53:10　　 9　　 10:40　　 0
13　17:02:27　　 1　　 10:50　　 0
14　17:25:32　　 3　　 11:00　　 0
15　17:32:41　　11　　 11:10　　 0
16　17:44:48　　 3　　 11:20　　 0
17　17:47:38　　 9　　 11:30　　 0
‥　　　　　　　　　　 ‥:‥　　 0
25　　　　　　　　　　 12:50　　 2
‥　　　　　　　　　　 ‥:‥　　 0
49　　　　　　　　　　 16:50　　 9
50　　　　　　　　　　 17:00　　 1
51　　　　　　　　　　 17:10　　 0
52　　　　　　　　　　 17:20　　 3
53　　　　　　　　　　 17:30　　11
54　　　　　　　　　　 17:40　　12
55　　　　　　　　　　 17:50　　 0

D2: 9:00:00
D3: =D2+"0:10"
E2: {=SUM((A$2:A$17>=D2)*(A$2:A$17<D2+"0:10")*(B$2:B$17))}
　　(配列数式)

お礼

ご回答ありがとうございます。
お礼の投稿が遅れ申し訳ございません。
やってみましたが、うまく動かないようです。

回答No.3

imogasi

エクセルの関数にFREQUENCY関数というのがある。これが使えないかやってみてください。
頻度表(ヒストグラム）を作る関数です。
時間もエクセルでは、セルの値は、２４時間を１とする数値で持たれているので数値の刻み巾にに落ちる度数のカウントの問題になります。
10
この関数を使うためには、１０分間隔の実データが、同一列の連続セルに必要だが、
時刻はセルの値としては、数値ですから、１０分間隔は、「編集」「フィル」「連続データ」の作成で作れるはず。
例　それをした結果
0:10:00
0:20:00
0:30:00
0:40:00
・・
または、上２セルを範囲指定し、下のセルの右下で＋ハンドルを出し、下へ引っ張る。スタートは観察開始のキリのいい時刻。
頻度表ができてしまえば、後はそこを範囲指定し、挿入ーグラフ・・・の問題で、普通にできる。
朝時間がなく、実際例が挙げられないが、自分でやってみてください。

お礼

ご回答ありがとうございます。
お礼の投稿が遅れ申し訳ありません。
FREQUENCY関数は、よく判らず、すぐには使えそうにないです。
後半、「編集」「フィル」「連続データ」については参考になりました。

回答No.1

konominmin

こんばんわ。

おっしゃるとおり　”FLOOR"関数で10分単位にまるめ、それをピボットテーブルで集計し、ピボットグラフにされてみてはいかがですか？

時間をA列とすると、実時間としてB列を作り、
B2=IF(A2<>"",FLOOR(A2,"0:10"),"")　と入力します。
すると下のようになりますから、これをピボットテーブルで集計しグラフにするとできます。

A　　　　　　B　　　C
時間　　　　実時間　人数
9:01:44 　　9：00　　1
9:09:55 　　9：00　　3
9:32:18 　　9：30　　2
　・　　　　　・
　・　　　　　・　
17:44:48 　17:40　　3
17:47:38 　17:40　　9

お礼

ご回答、ありがとうございます。
参考にさせていただきました。
ピボットだと、0の場合は困るようですね。

補足

補足です。
ご意見は、当日参考にさせていただいたのですが、
お礼の投稿が遅くなりました。
申し訳ありません。