[[20040413063719]]　『勤務表』（nana）　ページの最後に飛ぶ

『勤務表』（nana）
　

 残業時間を計算する方法を教えて頂きたく宜しくお願いします。

 出勤時間は8:00,退社時間は17:00です。それ以降は18:00までの残業は1時間つきます。
 18:00に満たない時間ですと0となります。18:00以降は30分単位で残業時間を計算しま
 す。18:30までですとプラス30分の残業がつきますが。18:20までですと18時以降の残業
 時間は0なります。18:30～19:00まではまた30分というように 30分単位で残業時間を
 計算します。休憩時間は1時間です。

 　　　A　　　　　　B　　　　　　C　　　　　D　　　　　　E　　
 1　出勤時間　　　退社時間　　　休憩　　　実働時間　　残業時間
 2　　8:00　　　　　17:00　　　　60

 上記のようなフォーマットで勤務表を作成しました。実働時間には
 =IF(A2="","",IF(B2-A2-TIME(0,C2,0)>TIME(8,0,0),TIME(8,0,0),B2-A2-TIME(0,C2,0)))
 という式を立てました。
 残業時間の計算方法を
 =IF(A2="","",IF(B2-A2-TIME(0,C2,0)>TIME(8,0,0),E12-D12-TIME(0,C2,0)-TIME(8,0,0),0))
 というようにしましたが、19:00迄の退社時間という区切りの良い時間ですと
 残業時間がでますが、19:20迄というような半端な退社時間ですと思った時間に
 計算できません。過去ログを読ませていただいてCEIKINGとFLOORを使うのかと思った
 のですが、うまくできませんでした。宜しくお願いします。

 =IF(A2="","",IF(B2>=TIME(18,,),FLOOR(B2-A2-TIME(,C2,)-TIME(8,,),"0:30"),0))
と言う事で如何でしょうか?（ケン）

 残業時間は退社時間が18:00以降の時、30分単位で付与するのであれば
「退社時間-"17:00"」を30分単位で切り捨てる計算ではどうでしょうか？
=IF(A2="","",IF(B2>=TIME(18,0,0),FLOOR((B2-"17:00"),"0:30"),""))
　(マーヤ)

 D2に
 =IF(COUNT(A2:B2)<2,"",
 --TEXT(FLOOR(B2,"0:30")-CEILING(A2,"0:30")-C2*"0:01","h:mm"))

 E2に
 =IF(D2="","",--TEXT(MAX(D2-"8:00",),"h:mm"))
 ということでは、どうでしょう？

 時間の計算は、端数の誤差問題が内包しているので、私は、処理に大変臆病です。　(LOOKUP)

 お世話になります。ケンさん、マーヤさん、LOOKUPさんどうもありがとうございます。
 やってみます！夜になってしまうと思いますが、またご報告させて頂きますので宜しくお願い
 致します。（nana）

 直接の回答ではありませんが、
計算誤差に関してはLOOKUPさんと同意見。
私の勤務先では
時間入力　　　
↓
分単位換算（各時間を1440倍し小数切捨して整数値に）　　
↓
分単位で計算、集計
↓
時間に変換
　
を徹底させて、誤差発生を抑えます。
シリアル値のままでの計算は誤差の原因になりますし、
計算式での誤差吸収もネストが増えてミスの原因になります。
(KAMIYA)

 先程の残業時間の数式を変更しておきます。
=IF(A2="","",IF(B2>=TIME(18,0,0),FLOOR((B2-"17:00"),"0:30"),""))を
　↓　
=IF(A2="","",IF(TIMEVALUE(TEXT(B2,"h:mm"))>=TIME(18,0,0),FLOOR(TIMEVALUE(TEXT(B2,"h:mm"))-"17:00","0:30"),""))
　に変更します。　(マーヤ)

 こんばんは。nanaです。皆さん、どうもありがとうございます。解決しました！
 いろいろな計算式があるのですね。

 でも、まだ質問をしても宜しいでしょうか？
 LOOKUPさん、KAMIYAさんがご指摘して下さっている、シリアル値のままでの計算は誤差
 の原因になるというのを私は理解できていません。難しいことでしょうか？
 ケンさんの提示して下さった式では例えば、出勤時間が13時からになった時にエラーが
 発生してしまうのですが。どうしたらいいでしょうか？
 マーヤさんの提示して下さった数式は休憩時間に関係なく単純に残業時間だけの結果
 が出せるのですね。

 アルバイトの人などの給料計算をする時には、普通、総労働時間を出してから所定内
 の実務時間と残業時間とに分けて給料計算した方がいいのでしょうか？それとも所定内
 の実務時間と残業時間を最初から計算して、それぞれの合計に賃金をかけていった方が
 いいのでしょうか？
 以上、宜しくお願いします。
 （nana）

 私の関係しているところだけに回答します。
＞マーヤさんの提示して下さった数式は休憩時間に関係なく単純に残業時間だけの結果
が出せるのですね。
　
残業時間は定時以降の時間とし、勤務時間が何時間であるかは関係なし。
ましてや、定時間内の休憩時間は考慮する必要なし。
との考えで、数式をたてました。
これがよいのかどうかは、nanaさんの会社の給料計算がどうなっているかによります。
　
計算式の変更は、計算誤差を防止するためにTIMEVALUE関数などを組み入れたものです。
（最初の式では誤差がでるのを確認しましたので、変更案を提示しました。）
　　　(計算誤差をしっかり理解していない・マーヤより)

 私の数式は13：00以降の出勤の時は-マイナスになってしまいますね。
ただ単に検証不足です。確かに誤差出るようですね。皆さんのを参考にして下さい。
（ケン）

 マーヤさん、ケンさん、どうもありがとうございます。
 時刻に関する計算誤差について勉強してみます。
 (nana）

 わたしも指摘だけしておいて、具体例を挙げないのは不親切ですね。
シリアル値が「近似値」であるために誤差の発生する場合があります。
つまり、スパッと割り切れていない小数として表現されているのが原
因です。
かなり単純な例で説明します。
    A       B       C       D           E
 1 出勤    退勤    休憩    実労時間    残業時間
 2 7:00    17:30   2:00    8:30        0:45
　　
上記のような表で規定労働時間が7時間45分であるとします。また、
D2：=B2-A2-C2
E2：=FLOOR(D2-TIMEVALUE("7:45"),TIMEVALUE("0:15"))
としてあるとします。E2の数式でのD2への参照がシリアル値のまま
の部分です。実際に計算させてみますと、E2は0:30になってしまい
ます。E2の数式で誤差が出ているためです。
　
D2-TIMEVALUE("7:45")＝ 0.0312499999999999
（「0:45」のシリアル値「0.03125」）
この値をFLOOR関数で15分単位で丸めた場合、「0:45」に
「0.0000000000000001」だけ不足しているために0:30が返る
　
ということになります。
　
これを回避するためには、シリアル値の部分をマーヤさんの
手法のようにTEXT関数を介して
=FLOOR(TIMEVALUE(TEXT(D2,"h:mm"))-TIMEVALUE("7:45"),TIMEVALUE("0:15"))
とするか、下記のように時間単位、分単位で計算するように
するかの方法があるようです。

時間単位
分単位

 まあ、要するにシリアル値と時間文字列で単純に計算してい
ると、「あれっ？」というときがあるので、気をつけましょう、
ということです。
(KAMIYA)

 KAMIYAさん、おはようございます。ご親切にどうもありがとうございます。
 >シリアル値と時間文字列で単純に計算していると、「あれっ？」というときがある
 ので、気をつけましょう
 私はこのようなことにまったく無知でした。帰宅してからKAMIYAさんが教えて下さった
 ことをじっくりとやってみたいと思います。すごく勉強になりました。ありがとうござ
 いました。

 回答してくださった皆様、どうもありがとうございました。また解らないことがあった
 ら質問させていただきますので、宜しくお願いします。(nana)

コメント返信：

[ 一覧(最新更新順) ]

[[20040413063719]] 『勤務表』（nana） ページの最後に飛ぶ

[[20040413063719]]　『勤務表』（nana）　ページの最後に飛ぶ