エクセルマスターの方、教えてください！

質問

稼働時間をExcel2002を使って自動計算するようにしたいのですが、勉強不足の為、うまく作成できません。
お分かりになる方、お力添えお願いします！

●計算したい内容
開始時刻Aと終了時刻Bの時刻を入力することによって、
時間帯毎（0：00～8：00/8：00～20：00/20：00～24：00）
の稼働時間を自動集計したい。

例1）A7：00 B23：00であれば、
0：00～8：00⇒1H
8：00～20：00⇒12H
20：00～24：00⇒3H

例2）A23：00 B15：00であれば、
0：00～8：00⇒8H
8：00～20：00⇒7H
20：00～24：00⇒1H

例3）また日付をまたぐ場合
A7：00（2008/4/1） B23：00（2008/4/3）であれば、
0：00～8：00⇒17H
8：00～20：00⇒36H
20：00～24：00⇒11H

このような計算をエクセルの機能や関数等を使って、計算することは可能でしょうか。
ご回答お待ちしております。よろしくお願いいたします。

回答No.3

cafe_au_lait · ベストアンサー

すみません、なぜか表の答えが違っていました。

開始 2008/4/1 7:00
終了 2008/4/3 23:00
8 17
20 36
24 11

"2008/4/1 7:00"までB1に入ることを示したかったのですが、まぎらわしいので行列番号を省略しました。
A3:A5にはそれぞれ8,20,24と入力してください。17,36,11のところに数式が入力されています。「整数で」と言ったのは、「8:00ではなく8」という意味です。普通に入力してください。

A3:A5は、数式中に数字を入力してしまえば参照しなくてもよいです。この場合、A列は必要ないですね。

B3=MAX(8-HOUR(B$1),0)+MIN(8,HOUR(B$2))+(INT(B$2)-INT(B$1)-1)*8
B4=MAX(20-HOUR(B$1),0)+MIN(20,HOUR(B$2))+(INT(B$2)-INT(B$1)-1)*20-B$3
B5=MAX(24-HOUR(B$1),0)+MIN(24,HOUR(B$2))+(INT(B$2)-INT(B$1)-1)*24-B$4-B$3

お礼

何度もご親切にありがとうございました！
自分の意図する答えが導き出せました☆
エクセルの詳しい人が回りにおらず、本当に助かりました。
本回答を参考によい表が作れそうです＾＾

回答No.2

cafe_au_lait

- A B
1 開始 2008/4/1 7:00
2 終了 2008/4/3 23:00
3 8 9
4 20 0
5 24 -2

B1に開始日時、B2に終了日時をシリアル値で入力
A3:A5に各時間帯の終了時間を整数で入力

B3=MAX($A3-HOUR(B$1),0)+MIN($A3,HOUR(B$2))+(INT(B$2)-INT(B$1)-1)*$A3
B4=MAX($A4-HOUR(B$1),0)+MIN($A4,HOUR(B$2))+(INT(B$2)-INT(B$1)-1)*$A4-B$3
B5=MAX($A5-HOUR(B$1),0)+MIN($A5,HOUR(B$2))+(INT(B$2)-INT(B$1)-1)*$A5-B$4-B$3

分以下を考慮する場合、A3:A5をシリアル値(8:00など)で入力し、数式中のHOUR(セル)の部分をTEXT(セル,"h:mm")に変更してください。この場合、計算結果もシリアル値になります。

お礼

cafe_au_laitさん。
ご回答ありがとうございます。
>3 8 9
>4 20 0
>5 24 -2
こちらはA3からA5のセルに入力するのでしょうか???

>A3:A5に各時間帯の終了時間を整数で入力
の部分の意味もいまいちわからないのですが。。。

エクセル初心者なもので、不甲斐ないのですが、
可能であれば、もう少しご説明いただけるとありがたいです。
お手数をおかけしますが、よろしくお願いいたします。

回答No.1

chie65536

A1に開始時刻、B1に終了時刻とする。

0：00～8：00
=MIN("8:00",B1)-MIN("8:00",A1)+IF(B1>1,MIN("8:00",MOD(B1,1)),0)+IF(B1>2,"08:00"*(INT(B1)-1),0)

8：00～20：00
=MAX("8:00",MIN("20:00",B1))-MAX("8:00",MIN("20:00",A1))+IF(B1>1,MAX("8:00",MIN("20:00",MOD(B1,1)))-"8:00",0)+IF(B1>2,"12:00"*(INT(B1)-1),0)

20：00～24：00
=MIN("24:00",MAX("20:00",B1))-MAX("20:00",A1)+IF(B1>1,MIN("24:00",MAX("20:00",MOD(B1,1)))-"20:00",0)+IF(B1>2,"04:00"*(INT(B1)-1),0)

但し、上記の式を使う場合、A1とB1は、以下の条件に従った入力をしなければならない。

A1は「0:00～23:59の値」しか許さない。つまり、時分のみで日付を付けてはいけない。「時」も24以上の値は許さない。「時」が24を超えたり、日付を付けた値を入力すると誤動作する。

B1は「A1より大きい値」しか許さない。つまり、A1に「23：00」、B1に「15：00」は誤動作する。こういう場合は、A1に「23：00」、B1に「39:00」か「1900/1/1 15:00」と入力する。

開始時刻は「時:分」しか許さない。

終了時刻は「時:分」か「1900/1/日数 時:分」しか許さない。

開始時刻は以下のように入力する事。
00:00⇒00:00または1900/1/0 00:00
01:00⇒01:00または1900/1/0 01:00
　　～
23:00⇒23:00または1900/1/0 23:00
23:59⇒23:59または1900/1/0 23:59
(24:00以上は入力禁止)

終了時刻は以下のように入力する事。
00:00⇒00:00または1900/1/0 00:00
01:00⇒01:00または1900/1/0 01:00
　　～
23:00⇒23:00または1900/1/0 23:00
翌日00:00⇒24:00または1900/1/1 00:00
翌日01:00⇒25:00または1900/1/1 01:00
　　～
翌日23:00⇒47:00または1900/1/1 23:00
翌々日00:00⇒48:00または1900/1/2 00:00
翌々日01:00⇒49:00または1900/1/2 01:00
　　～
翌々日23:00⇒71:00または1900/1/2 23:00
３日後00:00⇒72:00または1900/1/3 00:00
３日後01:00⇒73:00または1900/1/3 01:00
　　～
３日後23:00⇒95:00または1900/1/3 23:00
以下略
(開始時刻より小さい値は入力禁止)

間違って日付を付けて「7/30 15:00」なんて入力をすると、とんでもない稼動時間が表示されるので、気を付ける事。

お礼

こんなに短い時間で、回答のような数式を導きだすことができるなんて、感動です。
参考にさせていただきます。ありがとうございました。
日付入力の部分は、引き続き考えてみたいと思います。。。