[[20180123132358]]　『時間の計算について』（koyayuu）　ページの最後に飛ぶ

『時間の計算について』（koyayuu）

Excel2013の時間(時刻ではない)の計算について質問があります。

私の職場では第1週から第4(5)週まで、基本稼働時間は29時間以内、
残業は認められますが、基本の29時間に合計して38時間45分を超え
ないように指示されています。

各自が手書きで提出した残業時間表を元に各週(第1週が1日でも関係なく)
残業時間+29時間＜=38時間45分の条件でその週の合計が38時間45分を超え
たら、セルを赤くしたいと考えています。

表示形式に関しては基本が24時間を超えているので、[h]:mm とし、
38:45を表示可能にしてあります。

各週の時間外合計にはSUM関数を使い、以下の数式は入力済みです。

= SUM(B3:B7)

このセルにはB3(月曜日):B7(金曜日)に時間1:15とか2:30とか入力すると、
その正確な合計値が入ります。ただ、私としては、この合計値に29を足して
38:45を超えた場合、セルの背景色を赤色にしたいのですが、以下のように
足してしまうと、おそらくですが、シリアル値になってしまいます。

= SUM(B3:B7) + 29:00

もしも、この値が29:00 + （その週の時間外の合計）の時間数になってくれれば、
条件付き書式で38:45<　の際、背景色を赤色に変えられると考えていますが、
29:00時間を足す、うまい方法はないものでしょうか。

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

 =TEXT(SUM(B3:B7)+"29:00","[h]:m")*1
 ではどうか。
 演算子（+-*/等）を使った数式で時刻や日時、数値を表す文字列を使うとその文字列をそれが表す時刻や数値に変換する。
 なお、TEXT関数は誤差対策のため。

（ねむねむ） 2018/01/23(火) 14:12

シリアル値では1日（24時間）が1なので、
24で割れば時間になりますね。。。

=SUM(A1:A5)+29/24
（まっつわん） 2018/01/23(火) 15:52

ねむねむさん、ありがとうございます。
早速試してみます。

まっつわんさん、先日、参加した講習会で講師の先生がシリアル値が１日で１ということはおっしゃってましたが、「理論はその通りで間違いないが、絶対に24や、60で割ってはダメ、必ずどこかで狂い始める」との発言をもらっていますので、すいませんが、その方法は取らないことにします。
（koyayuu） 2018/01/24(水) 09:42

＞狂い始める
趣旨がわかりませんね。。。

1回足すだけなので誤差があっても1回限りなので、
「始める」というのはなんだろう。。。。

どんどん足し算していくなら解らないでもないですが。。。なんだろう。。。。
ここ、ちゃんと聞きたいなぁ。。。

で、その先生はその時の対策は話されなかったのかなぁ？？？？？

（まっつわん） 2018/01/24(水) 09:52

あぁ、あと、秒は無視してるのに、どんだけの精度を求めてるのかな？？？

臨機応変に柔軟に対応していいと思いますが。。。

（まっつわん） 2018/01/24(水) 09:56

逆にシリアル値のまま表示させて24を掛けるというのは？
=SUM(A1:A5)*24+29
38:45も38.75で比較して上げる必要がありますが
（774） 2018/01/24(水) 09:58

＞臨機応変に柔軟に対応していいと思いますが。。。

わたしもそう、おもいます。
割り算って、割り切れない数値、ありますよね。1÷3とか。。。

19：20とか入力せず。
19と20を分けて入力し、時間は60を掛け分にして計算
戻すときは　余りをを求めて、時間と分に分けるのはどうでしょうか。
（隠居じーさん） 2018/01/24(水) 11:01

 ＞1回足すだけなので誤差があっても1回限りなので、 
 ＞「始める」というのはなんだろう。。。。

 １回目で誤差が発現するかも知れないし、しないかも知れない。
 (凡人には）何回目かは予測できないタイミングで発現する、と言う意味だと思います。

 第一、SUM(A1:A5)なら既に小数を5回計算しています。
 24や60で割る前から危ない状態になっています。

 ・・なので「やってはダメなんて」云っていたら、
 時間(小数）の計算は一切できない、と言っているのと同じことになる。

 対策は、ねむねむさんが初っ端に示しているんですがねぇ・・

（半平太） 2018/01/24(水) 11:17

 > 第一、SUM(A1:A5)なら既に小数を5回計算しています。

あぁ、、、、
小数の計算を始めたら、
演算誤差が生じ始める。

ということで、
24で割ることそのものが問題ではないということかな。
それなら納得。
（まっつわん） 2018/01/24(水) 13:28

ねむねむさん、ありがとうございます。

 以下の関数式を入れたところ、29:00を足した前提で、時間数が表示されるようになりました。
 =TEXT(SUM(B2:B3)+"29:00","[h]:mm")*1

 ただ、ここに条件付き書式を入れて、38:45以上の場合、セルの背景を赤にしたいのですが、なぜか、このセルにその書式を
 当てはめると、29:00と表示されて、背景が赤くなります。条件付き書式ではない、なにか別の方法を取るべきでしょうか。

 まっつわんさん、隠居じーさんさん、半平太さん、先ほどの講習会の先生の発言は、この問題を相談した結果ではなく、
 Excelの時間計算に質問した時に発言された内容です。真意を聞いても、自分で調べろと言うだけでしょう。
 1年半くらい前に、受講した時には、もう少し大らかだった気がするのですが。失礼な物言いになり、申し訳ありませんでした。

（koyayuu） 2018/01/24(水) 14:11

 もし、38:45以上という式が
 =A1>="38:45"
 という形で有れば
 =A1>="38:45"*1
 としてみてくれ。
 違う場合は条件付き書式の設定を書いてみてくれ。 
（ねむねむ） 2018/01/24(水) 14:18

はい、38:45までは有効です。
それを前提に条件付き書式を設定したら、すべてうまく行きました。
ありがとうございます。
（koyayuu） 2018/01/24(水) 14:40

 一件落着したようなので・・・

 ＞24で割ることそのものが問題ではないということかな。

 まっつわんさんは

 (1) 1:00 は　1/24   と同じと思っているんですね？
 (2) 0:01 は　1/1440 と同じと思っているんですね？
 (3) 0:09 は　9/1440 と同じと思っているんですか？

 調べたら、(3)は厳密には同じじゃなかった。(差し引くと0にならなかったので）

 この現象が何故起きるのか説明できますか？
 説明できないと「24で割ることそのものが問題ではない」と言い難くないですか？

（半平太） 2018/01/24(水) 15:09

コメント返信：

[ 一覧(最新更新順) ]

[[20180123132358]] 『時間の計算について』（koyayuu） ページの最後に飛ぶ

[[20180123132358]]　『時間の計算について』（koyayuu）　ページの最後に飛ぶ