エクセル 時間の掛け算がうまくできません

質問

お世話になっております。
今回は時間の掛け算について質問させてください。
セルNに賃金を入力したいのですがうまく労働時間が認識されません。労働時間は40時間以下ですと時給X労働時間、40～60時間ですと時給X労働時間を1.5倍、60時間以上ですと時給X労働時間を2倍です。
私の作った関数です。=ROUND(IF(M47<40,L47*M47*24,IF(M47>60,M47*L47*24*2,M47*L47*24*1.5)),3)
ちなみに、労働時間は=(I47+K47)-(H47+J47)で計算しています。

セルH　　セルI　　　セルJ　　セルK　　セルL　　セルM
開始日　　終了日　開始時　　終了時　　時給　　労働時間
7-Feb-07 7-Feb-07 0:03:12 8:00:00 $22.40 7:56
7-Feb-07 9-Feb-07 8:33:46 23:26:24 $17.45 62:52
7-Feb-07 8-Feb-07 20:30:23 21:02:25 $14.75 24:32
7-Feb-07 9-Feb-07 18:39:50 21:20:00 $13.45 50:40

正しく計算されたら、セルNに上から
178.01/1321.91/361.87/753.25　と表示されるはずなのです。

どうぞよろしくお願いいたします。

回答No.3

Wendy02 · ベストアンサー

こんにちは。

一般のワークシートの計算の時は、労働時間を、補助列で、10進に変えます。一度に変えようとするとミスが分からなくなります。

できれば、超過手当ての場合は、別の列で、Extra Overtime Payで、出したほうがよいです。まとめて計算を出して、給与明細というような方法、日本ではしませんね。(一応経験者。）

今回、補助列は設けさせていただきます。
仮に、O列にします。
また、数式は、47行目にあるようですが、ここでは、2行目からとします。置換で、2を47に直してもよいかと思います。

O2: 本来は、指数は、影響のない小数点第7位で丸めることにしましたが、今回は、分単位ですから、6位でも同じです。

=ROUND(M2*24,7)

N2 の数式：
=ROUND(L2*(MIN(O2,40)+MIN(MAX(O2-40,0),20)*1.5+MAX(O2-60,0)*2),2)

0.00が出るのが問題なら、

=IF(M2=0,"",ROUND(L2*(MIN(O2,40)+MIN(MAX(O2-40,0),20)*1.5+MAX(O2-60,0)*2),2))

としてください。
なお、

四捨五入(ROUND)
178.01 : 1,321.92 : 361.87 : 753.26

端数の1セントが合いませんね。通常、こういう場合は、ROUNDDOWN－切り捨てだと思います。

切り捨て(RONWDDOWN)
178.00 : 1,321.91 : 361.87 : 753.25

これは、お任せします。

お礼

Wendy02さん、ご回答ありがとうざいます。
ご回答いただいてすぐに内容が理解できなかったため、あとでゆっくり教えていただいたことを消化したあと、お礼を書こうと思っていたので、お礼が遅くなってしまってすいません。

給料計算ですが、なかなかややこしいんですね。私も日本にいたころ（大昔）は給与計算をやっていたのですが、そういえばいくつかのセルを使って出していたのを思い出しました。
（Wendy02さんほどの高度な関数は使っていませんでしたが。）

今、MaxやMin　Roundを使って例の集計表に取り組んでいます。もうすこし完成までには時間がかかりそうですが、おかげさまで何とかなりそうです。MaxやMinなど知ってはいましたが、こういう使い方をするなんて、まったく考え付きませんでした。教えてくださりありがとうございました。。

回答No.5

Wendy02

こんばんは。

#3の回答者です。

>北米の大学に行っていまして、質問の問題ははInfomation Systemの授業の課題なのです）

本来は、ここでは、Homework の質問を、丸々出したら反則なんですね。こちらのカテゴリでは、あまり削除はされるケースは少ないのですが。

それはともかく、実際は、それぞれ、別々に計算して、それを足し算するというのが、問題集の解答で、私たちが書いたような、ひとまとめにしてしまうというのは解答としては良くないかもしれません。#3で説明したように、その考えの手順が見えづらくなります。おそらく、別々に計算したために、1セントの誤差が出てきたのだと思います。ただし、ROUNDDOWN 自体は間違いなさそうな気がします。

また、私の

=ROUND(M2*24,7)

という考え方は、本当に、何度も何度も、考えたあげくに、こうしたら、誤差が少ないという考えに基づいているわけですね。時間のまま置いておくと、思わぬ誤差丸め(浮動小数点丸めとは異質です）が発生することがあるので、なるべく、一旦、目に見える形で10進にしておいたほうが無難だと考えています。(ただ、それを人に分からせるには、浮動小数点丸めではない、Excelの問題点を説明しなければなりませんが。）

=ROUND(L2*(MIN(O2,40)+MIN(MAX(O2-40,0),20)*1.5+MAX(O2-60,0)*2),2)

これを、それぞれの時給単価を掛けます。
　MIN(O2,40)　　.....40時間まで
　MIN(MAX(O2-40,0),20)*1.5...40時間より以上60時間まで
　MAX(O2-60,0)*2)　....60時間より以上
　これは、IF構文にしてもよいです。

お礼

Wendy02さん。再度ご回答ありがとうございます。

＞本来は、ここでは、Homework の質問を、丸々出したら反則なんですね

はい。反則でした。前例を作ってしまい、申し訳ありませんでした。私の投稿をみて、他の学生さんが投稿しないことを願います。
（ただ、追加で付け加えさせていただけるならば、これは宿題のほんの1部で（％でいうと、5％程度）、この宿題はグループで取り組むもので、自分一人でやらなくてもよいものなのです。また、外部の人に助言を求めたり、参考書を見るのも許されています。そのかわり、今後テストがあるので、「自分でがんばらなかったら、テストでいい成績とれないので、なるべく自分でがんばってね」というのが教授の見解です。また、この授業は一般教養で、コンピューター関連を選考していない人たちがとるので、みんな知識がばらばらなのです。私の組んだグループの人たちは、エクセルは足し算機能以外はあまりわからない人たちなので、結局相談できる人がいないために、ここに投稿させていただきました。）

今、エクセルと格闘しております。明日ぐらいに、’完成しました！’という投稿ができるようにがんばります。

ありがとうございました！

補足

できました～！！
Wendy02さんから教えていただいたことを元に、
課題のほかの要素をくみあわせて、ついに完成しました！！
やったぁぁ。。。
とってもうれしいです。
いろいろとありがとうございました。

回答No.4

tetsus_2000

横から失礼。
これまでの皆さんのやり取りで抜け落ちている部分がありますので。

「労働時間は40時間以下ですと時給X労働時間、40～60時間ですと
時給X労働時間を1.5倍、60時間以上ですと時給X労働時間を2倍です。」

これは、例えば、労働時間が70時間だった場合、その内の
　・40時間は時給X労働時間
　・20時間は時給X労働時間X1.5
　・10時間は時給X労働時間X1.0
で計算するのが一般的な計算方法です....あくまでも「一般的」なの
で、montreさんの場合に当てはまるか否かは不明ですが。

これであれば、おそらく
>　正しく計算されたら、セルNに上から
>　178.01/1321.91/361.87/753.25　と表示されるはずなのです。
に近い数字になるのではないでしょうか（未検算です）。

お礼

ご回答ありがとうございます。
まさにその通りでした！
たしかに、実生活でも、残業や休日出勤したら、残業や休出分だけ増額ですよね。
ご指摘ありがとうございます。

回答No.2

zap35

＞正しく計算されたら、セルNに上から
＞178.01/1321.91/361.87/753.25　と表示されるはずなのです。
２番目と４番目の賃金は合っていますか？

40時間以上は時給、40時間超～60時間未満は時給の1.5倍、60時間以上は時給の2倍でよければ、N47の式は
=ROUND(IF(M47*24<=40,L47*M47*24,IF(M47*24<60,L47*M47*36,L47*M47*48)),2)
ですが、178.01/2194.42/361.87/1022.26という結果になりました。

例えば２番目の「62時間52分」は「62.867時間」ですから電卓で計算すると
　17.45×62.867×2　＝　2,194.05
になるはずですが、エクセルで計算した「2,194.42」とは誤差が生じています。これはJ列、K列が「秒」まで入力されており、M列には表示されていない「秒」の分まで時給を計算するからです。

分未満を切り捨てるなら労働時間は
　=(I47+FLOOR(K47,"0:01"))-(H47+FLOOR(J47,"0:01"))
で計算してください。FlOOR関数は基準値単位に切り捨てする関数です

お礼

こんにちは。
計算方法ですが、ほかの方がご指摘してくださった通り、
労働時間を40時間以下のもの、40～60時間のもの、60時間以上のもの、と分解して、それぞれに倍数を掛け合わせる方法だそうです。

私もzap35さんと同じ考え方でしたので、ご回答を読んだときに「やっぱり教授が間違っている！」と強く確信したのですが。。（汗）
給料計算ってややっこしいんですね。。。

ともかく、いろいろとありがとうございました。

補足

早速ご回答ありがとうございます。
Floor関数を使うのですね。まったく知らない関数だったので、勉強になりました。教えてくださってありがとうございます。

早速、試してみました。教えていただいた通りに入力しましたところ、
確かに2番目と4番目の数字がそれぞれ、2194.05と1022.25となりました。この数字に関しまして、早速教授に連絡をとってみます。（私は今、北米の大学に行っていまして、質問の問題ははInfomation Systemの授業の課題なのです）
教授から返信がき次第、後日折り返し、投稿させていただきたいと思います。
お忙しいところ恐縮ですが、もうすこしお付き合いください。

回答No.1

dogsiva

IF関数の、条件部分の「M47」を「M47*24」にしてみるのはいかがでしょうか。
(M列に入っている実数値が日数(12時間なら0.5)のため)

お礼

（お礼を投稿しようとしたら、補足の欄に間違ってにゅうりょくしてしまいましたので、再度投稿します）

早速ご回答ありがとうございます。
実数値が日数(12時間なら0.5)計算なのですね。
勉強になりました。
ありがとございます。

（ただ、残念ながら、これですと2番目の数字が、2194.41となってしまうのです）

補足

早速ご回答ありがとうございます。
実数値が日数(12時間なら0.5)計算なのですね。
勉強になりました。
ありがとございます。

（ただ、残念ながら、これですと2194.41となってしまうのです）