エクセルの計算式について教えて下さい

質問

次の計算式にて計算させています。
　求める時間：J24=H24+TIME(0,0,$E$15)+V24+R24
　　H24　⇒　数値(時間)が入力されているセル
　　　　H24 6:30(開始時間)
　　　　H25 7:24
　　　　H26 8:19
　　　　↓
　　 　 H40 0:05
　　　　H41 1:10(終了時間) 　　　1:00+α　　 *α(単位は時間で、0.5時間等と変化させます
　　 V24　⇒　数値(時間)が入力されているセル 　0:15
　　 R24　⇒　数値(時間)が入力されているセル　 0:55
　　 $E$15 ⇒　固定する数値が入力されているセル　単位：秒

そこでJ24=H24+TIME(0,0,$E$15)+V24+R24の値が1:00+$C$19を超えた場合は1:00+$C$19と表示させたく次の式を作成しました。
　求める時間：
J24=IF(AND(H24+TIME(0,0,$E$15)+V24+R24>=(1+$E$15)/24,(1+$E$15)/24<H24),(1+$E$15)/24,IF(AND(H24+TIME(0,0,$E$15)+V24+R24>(1+$E$15)/24,H24+TIME(0,0,$E$15)+V24+R24<(6+30/60)/24),"Error",H24+TIME(0,0,$E$15)+V24+R24))
しかしH41(1:10)に対してJ41=1:00+$C$19と計算させたかったのですが、J41=H41+TIME(0,0,$E$15)+V41+R41としか計算されなかったため、色々と試行錯誤しましたが直りませんでした。

そこで今回作成した式の間違いと正しい式を教えて下さい。
よろしくお願い致します。

回答No.5

cafe_au_lait · ベストアンサー

すみません、I24の式を訂正いたします。

I24=MIN(H24+TIME(0,0,$E$15)+V24+R24,1+"1:00"+$C$19/24)

修正後は(15+15/60)/24+$C$19と判断いたしました。C19=0.25であれば、15:30～16:15間をカウントしないことになります。

H25=IF(AND(I24<(16+15/60)/24,I24>(15+$C$19+15/60)/24),(16+15/60)/24,I24+TIME(0,1,0))

等号の有無はおまかせしますが、時間の比較では誤差が生じる場合があります。
Errorについては考慮していません。どういう誤入力に対応させるのかが不明確でしたので。

お礼

何度もどうもありがとうございました。

この計算式で対応出来ました。
MIN関数を用いる部分がポイントで、時間も２４時間ではなく２５時間と考えることもポイントなのですね。
＊私の作成した計算式は長いだけでエラーしているので、まだまだ勉強しないといけないです。

どうもありがとうございました。

回答No.4

cafe_au_lait

NO.1、3です。状況がわかってきました。
お礼の中で、I列→J列、2:30→2:03、1:10+0.30=1:18→1:00+0.30=1:18と読み替えました。また、お礼中の式はH26に入力されるものと解釈いたしました。

H41に表示されている1:10は、実際には1日と1時間10分、つまり25:10です。したがって、比較対象は1+"1:00"+$C$19のはずです。

基本的な部分だけなら、以下の式でOKだと思います。

J24=MIN(H24+TIME(0,0,$E$15)+V24+R24,1+"1:00"+$C$19)
H25=J24+TIME(0,1,0)

以下は補足要求です。

・負の値を入力することはあるのか
・No.3のお礼で"IF(J25=(16+15/60)/24+$C$19,(16+15/60)/24,…)"の目的(C19に負の値を入力しなければ意味がない)
・H列、J列において、どういう条件で"Error"を表示させたいのか

お礼

ご回答ありがとうございます。

＞"IF(I25=(16+15/60)/24+$C$19,(16+15/60)/24,…)"の目的(C19に負の値を入力しなければ意味がない)
まず訂正です。（すみません）
J列ではなくI列の間違いで、J列は数式に関係ありません。
＊H24は6:30を手入力しますが、H25～H41までは下記の式です。
=IF(I24>(16+15/60)/24,I24+TIME(0,1,0),IF(I24>(15+15/60)/24+$C$19,(16+15/60)/24,IF(I24>=(6+30/60)/24,I24+TIME(0,1,0),IF(I24>1/24+$C$19,I24+TIME(0,1,0),Error))))
また2番目のIF式でご指摘通りミスがあり、(16+15/60)/24+$C$19から(16+15/60)/24+$C$19へ修正しました。
・基本は、I列の1個上の行に1分を加算したものをH列としています。
・ただ15:15～16:15はC19(0:00～1:00)に対して変化し、C19=0.5であれば、15:45～16:15間はカウントせずに16:15からとします。

＞負の値を入力することはあるのか
負の値はなく、全て正の値です。

＞H列、I列において、どういう条件で"Error"を表示させたいのか
特に大した意味は持たせてなく、誤入力と判断出来るようにするためだけです。
また私の計算式に対する精度の低さをフォロー出来るようにさせたかっただけで、特に深い意味はありません。

よろしくお願いします。

回答No.3

cafe_au_lait

各セルに入力されている値、欲しい結果、現在得られている値を補足していただけますか？
また、αとは何で、どこのセルにあるのですか？「1+αとしたい」とはどういう意味でしょうか？

"1:00"の説明はNo.2さんのとおりです。

お礼

何度もありがとうございます。

値は次のようになっています。
＊24～41は、エクセルの行
＊Ｈ、Ｉ、Ｒ、Ｖは、エクセルの列
＊Ｈ列はH24のみ6:30と入力し、それ以降は次式で自動計算
　 =IF(J25>(16+15/60)/24,J25+TIME(0,1,0),IF(J25=(16+15/60)/24+$C$19,(16+15/60)/24,IF(J25>=(6+30/60)/24,J25+TIME(0,1,0),IF(J25>1/24+$C$19,J25+TIME(0,1,0),Error))))

　　　　　H I R V
24 6:30 7:23 0:00 0:00
25 7:24 8:18 0:00 0:00
26 8:19 9:22 0:10 0:00
27 9:23 10:17 0:00 0:00
28 10:18 11:57 0:45 0:00
29 11:58 12:51 0:00 0:00
30 12:52 13:56 0:10 0:00
31 13:57 15:00 0:10 0:00
32 15:01 16:37 0:00 0:42
33 16:38 17:32 0:00 0:00
34 17:33 18:36 0:10 0:00
35 18:37 19:31 0:00 0:00
36 19:32 21:10 0:45 0:00
37 21:11 22:05 0:00 0:00
38 22:06 23:10 0:10 0:00
39 23:11 0:04 0:00 0:00
40 0:05 1:09 0:10 0:00
41 1:10 2:03 0:00 0:00

E15 3216 単位は秒ですが数字のみ
C19 0.30 単位は時間ですが数字のみ・・・質問ではこれをαと表現していました。

I41を2:30ではなく、1:10+0.30=1:18と計算される計算式を求めたいです。
よろしくお願いします。

回答No.2

wisemac21

数式の間違い
J41=1:00+$C$19　→　="1:00"+$C$19
時間の表示形式で演算する時は、ダブルコーテーションでくくる

その他は全体がよく分からないので判断できません。

お礼

ご回答ありがとうございます。

＞時間の表示形式で演算する時は、ダブルコーテーションでくくる
勉強になりました。
式を複雑にしてしまっていたのですね。
今後は、表示されている形式を数式に反映していくために活用させて頂きます。

どうもありがとうございました。

回答No.1

cafe_au_lait

(1+$E$15)/24というのが突然出てきていますが、ここを"1:00"+$C$19にすればよいのではないでしょうか？あるいは、TIME(1,0,$E$15)ということでしょうか？

お礼

ご回答ありがとうございます。

ご指摘して頂いた通り(1+$E$15)/24は間違いで(1+$C$19)/24です。
　$C$19　固定する数値が入力されているセル　単位：時間

訂正した求める時間：
J24=IF(AND(H24+TIME(0,0,$E$15)+V24+R24>=(1+$C$19)/24,(1+$C$19)/24>H24),(1+$C$19)/24,IF(AND(H24+TIME(0,0,$E$15)+V24+R24>(1+$C$19)/24,H24+TIME(0,0,$E$15)+V24+R24<(6+30/60)/24),"Error",H24+TIME(0,0,$E$15)+V24+R24))
です。

"1:00"+$C$19やTIME(1,0,$E$15)⇒TIME(1+$E$15,0,0)でも試してみましたが、
　1:00　⇒　2:53　 ・・・ここは1+αとしたい
でした。

時間はシリアル値で1以下の数字となり、表現方法は
　(1) 1:53
(2) (1+53/60)/24
(3) TIME(1,53,0)
となると理解していますが、正しいのでしょうか。

よろしくお願いします。

＜追　記＞
"1:00"と" "で囲むことにより、何をしているのか教えて下さい。