EXCELで時刻別の稼働率

質問

よろしくお願いします。
EXCELデータに開始時間、終了時間があります。
そのデータを元に時間別の部屋稼働率を調査したいです。
例）　
開始　　終了　　部屋番号
09:34　12:00　　1
11:00　13:45　　1
12:05　12:36　　2
23:45　01:13　　2
全体の稼働率と部屋別の時間別稼働率を出したいのですが、
最後のパターンが難しいです。
また、２４時間以上使用する場合もあり、入力時は入力ソフト側にて
対応しているので、入力時は使用時間等は正解なのですが、
CSVにエクスポートした時点で時刻のみの出力になってしまい、
終了から開始を引いてもマイナスになったり、２４時間越えかどうかの
判断が出来ません。
質問書いていてもこんがらがってきました。
最終目的は時間別のグラフ化です。
０～１時は７人
１～２時は４人
　　・
　　・
というデータがあれば完成しそうなのですが出来ますでしょうか？

回答No.6

noname#15703

No.4の続きです。
　SUM(IFをSUMIFとしてませんか？

補足

お返事遅くなりました。
回答欄をコピペしているので間違いありません。

回答No.5

noname#262398

在室時間が分からないと24時間超えかどうか分からないならそのデータは必要です。
> 1900/01/01 01:23
これでも大丈夫ですので。

　in　　　out　　　stay
09:34　12:00　　2:26
11:00　13:45　　2:45
12:05　12:36　　0:31
23:45　01:13　　1:28
12:00　14:00　　26:00
この表を加工します。

in(1)　out(1)　stay(1)　　　in(2)　out(2)　stay(2)
9.57　　　12　　　2.43
11　　　13.75　　　2.75
12.08　　12.60　　0.52
23.75　　　24　　　0.25　　　0　　1.22　　1.22
12　　　　24　　　12　　　　0　　14　　　14　　　
時間のままだと誤差が出る場合もあるので、24を掛けた値にしました。
誤差が出なくても、そのほうがデータとして扱いやすいと思うので。

あとは#4の方の式をちょっと変えれば、
時間　人数
0　　　7
1　　　4
2　　　：
：　　　：
：
最終的にこんな表になればグラフ化も簡単でしょう。

回答No.4

noname#15703

No.3の続きです。
　大体判ったような気がしますが、以下でどうでしょう？
　データとして、開始時刻と終了時刻と部屋番号があったとします（ＣＳＶデータに在室時間を加える件は忘れてください。分単位で人数換算するのだと勘違いしてました）。
　ただし、各行とも日はまたがないとします。

開始　　終了　　部屋番号
09:34　12:00　　1
11:00　13:45　　1
12:05　12:36　　2

　開始時刻の先頭セル（09:34）から最終セル（12:05）までを「開始」と名前定義します。
　終了時刻の先頭セル（12:00）から最終セル（12:36）までを「終了」と名前定義します。
　仮にF1:G24に、
00:00　01:00
01:00　02:00
02:00　03:00
　　　：
と入力し、H1に、
=SUM(IF(G1>開始,IF(F1<終了,1,0),0))
とタイプし、最後にShift+Ctrl+Enterを押します。すると、
{=SUM(IF(G1>開始,IF(F1<終了,1,0),0))}
このように、中カッコが前後に付きますのでこれをH24までコピーします。
　これで合計が出ますので、あとは日数で割ればよいと思います。

お礼

ご回答ありがとうございます。
返事が遅くなりすみません。
アドバイスと全く同じデータを作成しましたが、
”最後にShift+Ctrl+Enterを押します”のところで
関数エラー　”入力した数式は正しくありません”になります。
よろしくお願いします。

回答No.3

noname#15703

No.2の続きです。
＞この時間帯は１０部屋で平均６．３人使用等。
　平均というのは１日平均ですか？
　日付の区別はどこでつけるのでしょうか？

補足

質問の仕方が悪いですね。どうもすみません。
日付は別の列にあります。他のデータもいろいろ入っており、列数は30個ほどあります。

例をあげますと、個室カラオケボックスの使用状況調査。
データには
レンタル日、レンタル開始時刻、レンタル終了時刻、レンタル時間、部屋番号　等があり、使用状況のデータが４年間分あります。
１０部屋の効率を良くするにはどうしたら良いでしょう？というのを考え実現するのがゴール地点です。
そこで、１年ごとの時間別集計や曜日別集計、来客の地域別集計等を考慮して対策をしたいのです。

稼働状況の調査結果で
月曜日の10:00～17:00の時間帯は少ないな！　
　　　　　　　↓　
月曜10:00～17:00まではドリンク無料
なんてしたいのですが…。

こんな補足で分かって頂けたでしょうか？
よろしくお願いいたします。

回答No.2

noname#15703

(1) ＣＳＶにエクスポートする際に、開始時刻と終了時刻ではなく、開始時刻と在室時間にできませんか？

(2)
開始　終了　部屋番号
23:45　01:13　　2
のように日をまたぐ場合は、

開始　在室時間　部屋番号
23:45　 15分　　　2
0:00　1時間13分　2
のように、日をまたがないように行を分けてＣＳＶにエクスポートできませんか？

(3)
０～１時は７人
１～２時は４人
とありますが、１部屋を１人と数えるのですか？　30分使用したら0.5人ですか？

補足

(1) 可能です。
　　エクスポートすると1900/01/01 01:23 とかになりますね。

(2)はやれば出来ると思います。

(3)１部屋一人。各時間に１分でもいればカウントでOK
　この時間帯は１０部屋で平均６．３人使用等。

以上です。

回答No.1

noname#204879

稼働率云々の前に…

》 入力時は使用時間等は正解なのですが、…

どのように入力しているのか具体例を示してください。

補足

ACCESS2000で　時刻(S)　　です。
例）12:34
入力時には在室時間も入力していて
12:00 in
14:00 out
02:00 在室
と自動計算しますが、outが次の日の14:00の場合は
在室時間を26:00と入力し直しています。