Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2010)
時間計算が正しく出来ません
投稿日時: 17/09/22 09:47:46
投稿者: natunokanon

初めまして。
 
パートさんの勤務時間の計算で、
8時半前の勤務は8時半からの勤務にしたくて
また15分区切りでの時給計算という事で
以下の式を作りました
 
=IF(E7="","",IF(E7<"8:30",FLOOR(F7-"8:30"-G7,"0:15"),FLOOR(F7-E7-G7,"0:15")))
 
それぞれ
E7→出勤時間
F7→退勤時間
G7→休憩時間
 
出勤時間が、8時半前だと上手く行くのですが
15:00出勤などと入れてしまうと正しく計算してくれません。
 
皆さんのお知恵を貸して下さい。
宜しくお願い致します。

回答
投稿日時: 17/09/22 11:33:22
投稿者: んなっと

これなら少しは改善されると思います。
=IF(E7="","",IF(E7<"8:30"*1,FLOOR(F7-"8:30"-G7,"0:15"),FLOOR(F7-E7-G7,"0:15")))
 
ただし下のように誤差が生じやすいので、
 
    E   F   G   H    I
 7  8:00 16:00 0:15 7:15  7:15
 8  8:30 16:00 0:15 7:15  7:15
 9  8:40 16:00 0:15 7:00  7:00
10 13:00 16:00 0:15 2:45  2:45
11 14:00 16:00 0:15 1:30× 1:45○
12 15:00 16:00 0:15 0:45  0:45
13 15:30 16:00 0:15 0:00× 0:15○
 
 
=IF(E7="","",FLOOR(TEXT(F7-MAX(E7,"8:30"),"[h]:mm")-G7,"0:15"))
などとして、演算誤差を減らしたほうがいいと思います。
これでも完全ではありません。
 
時間計算の演算誤差を減らす方法は、Google検索して調べて
ご自分で試行錯誤してください。

回答
投稿日時: 17/09/22 12:00:28
投稿者: んなっと

それからExcelというワードを入れないで、
勤務時間 切り捨て 違法
でも検索してください。

回答
投稿日時: 17/09/22 13:51:35
投稿者: WinArrow
投稿者のウェブサイトに移動

計算方法には関係ありませんが、
15分単位に丸めることは、労働基準法に抵触するかしれないので
よく確認したほうがよいですよ!!

回答
投稿日時: 17/09/22 21:09:56
投稿者: コナミ

日毎の15分まるめで切り捨ては確実に違法です。
 
でもまぁ、会社に言われたらそう計算するしかないですからね。

回答
投稿日時: 17/09/23 07:10:03
投稿者: 半平太

んなっと さん
 
何故、Text関数の外にG7を出したんですか?
 
それも一緒に第一引数に入れれば、何の不安もないと思うんですけど?
(1分も働いてないなんて特殊なケースは、話が別ですが・・)

回答
投稿日時: 17/09/23 09:26:54
投稿者: んなっと

「微小な値を加える」方法を使わずに「TEXT関数で文字列に変換する」を使う場合、
引き算するたびにTEXT関数を使わないといけなくなります。
今回の場合であれば、
=FLOOR(TEXT(TEXT(F7-MAX(E7,"8:30"),"[h]:mm")-G7,"[h]:mm"),"0:15")
E〜G列が直接入力であれば、これで完成形です。
 
ちなみに半平太さんがおっしゃっているのは、TEXT関数を最後に1回だけ使う
=FLOOR(TEXT(F7-MAX(E7,"8:30")-G7,"[h]:mm"),"0:15")
ですね。
おっしゃる通り、まさに1分も働いていないときにうまくいきません。
勤務時間が0:00になるときです。
休憩開始時刻と休憩終了時刻が固定されていると仮定します。
さらに、「認められる休憩時間は勤務時間に応じて変化する」などが無視されていると仮定します。
現実には認められないでしょうが、休憩時間中に来て、急用で休憩時間中に帰るパターンですね。
 
    E   F   G   H    I
 7 10:00 10:30 0:30 0:00 #VALUE!
 8 12:00 12:30 0:30 0:00   0:00
 9 14:00 14:30 0:30 0:00 #VALUE!
10 15:00 15:30 0:30 0:00   0:00
 
H7
=FLOOR(TEXT(TEXT(F7-MAX(E7,"8:30"),"[h]:mm")-G7,"[h]:mm"),"0:15")
I7
=FLOOR(TEXT(F7-MAX(E7,"8:30")-G7,"[h]:mm"),"0:15")
I7は、MAX関数などを追加すれば問題なしですね。
=FLOOR(TEXT(MAX(0,F7-MAX(E7,"8:30")-G7),"[h]:mm"),"0:15")
 
いずれにせよ式が長くなりますね。
時間計算で「微小な値を加える」を使う回答者が多いのもなんとなくわかるような気がします。

回答
投稿日時: 17/09/23 10:16:24
投稿者: 半平太

んなっと さん
 
ご回答、有難うございます。
 
>引き算するたびにTEXT関数を使わないといけなくなります。
 
この見解には納得しかねます。
 
・・と言っても実証する元気はないですが、理屈から言っての話ですけど・・
 
小数演算誤差は微細な値でしかなく、そんなものが寄ってたかった所で
h:mmなんてガサガサな升で量れば、すぐ中心値に戻るハズです。
 
そうじゃなければ、時間データ100行分をSUM関数で合計するなんてことも
危なくて出来ない、と言う事になると思います。

回答
投稿日時: 17/09/23 10:54:23
投稿者: んなっと

natunokanonさんへ。
 「微小な値を加える」とは、最小単位である"0:01"に
例えば0.01をかけてさらに小さくした0.0000069444のような値を加えておくことです。
  
下の例では8^-8(0.0000000596)を加えています。
=IF(E7="","",FLOOR(F7+8^-8-MAX(E7,"8:30")-G7,"0:15"))
   
半平太さんへ。

引用:
この見解には納得しかねます。

そうかもしれませんね。
いろいろ考えたら、今回は0:00より小さくなることだけが問題なので、
MAX関数などを追加した
=FLOOR(TEXT(MAX(0,F7-MAX(E7,"8:30")-G7),"[h]:mm"),"0:15")
でいいような気がしてきました。

回答
投稿日時: 17/09/23 11:40:28
投稿者: んなっと

半平太さんの書き込みは、とても有意義なものが多いですね。

回答
投稿日時: 17/09/24 08:43:57
投稿者: んなっと

で、半平太さん、0:00より小さくならないようにするため
最初の引き算でいったん誤差をなくした
TEXT(F7-MAX(E7,"8:30"),"[h]:mm")-G7
の式、理由はおわかりいただけたのでしょうか。
式の良し悪しは別として、一応意味はあるつもりです。

引用:
これでも完全ではありません。

であらかじめ防御線をはったつもりでしたが、無駄になってしまいました。

回答
投稿日時: 17/09/24 13:03:04
投稿者: 半平太

>で、半平太さん、0:00より小さくならないようにするため
>最初の引き算でいったん誤差をなくした
>TEXT(F7-MAX(E7,"8:30"),"[h]:mm")-G7
>の式、理由はおわかりいただけたのでしょうか。
 
私の先の書込みがきつい表現だった様で、済みませんでした。m(__)m
 
1時間も考えて、ようやく分かりました。
私と発想が違いますねぇ・・
 
私は、Floor関数の第一引数は、手入力並みの精度か、
それより、ホンのちょっと大きい値にしなければならないと考えます。
 
※そう処置すれば「完全な」数式にしかならないです。
 
それを目指すと、TEXT関数を使う方針なら
G7も第一引数に入れて考えなかったら、全てが始まらないと言う風に考えます。
 
お邪魔しました。m(__)m

回答
投稿日時: 17/09/24 13:59:44
投稿者: LMK

この場合、「FLOOR関数は使わない」が正解です。
数式を作り込んだらその人の責任が重いと言わざるをえません。
使っちゃダメです。

回答
投稿日時: 17/09/24 14:57:39
投稿者: 半平太

LMK さん
 
>数式を作り込んだらその人の責任が重いと言わざるをえません。
 
「その人」って誰ですか?
 
回答者ですか?
教えて貰って使った質問者ですか?
質問者にそんな計算をするように指示した上司ですか?

投稿日時: 17/09/25 13:20:27
投稿者: natunokanon

皆様、
時間計算がこんなに難しいとは思いませんでした。
 
>=FLOOR(TEXT(MAX(0,F7-MAX(E7,"8:30")-G7),"[h]:mm"),"0:15")
 
を使わせて頂きます。
 
労働法の問題は、私には立場上どうにも出来ません。
自分の会社はいわゆるブラック企業なのかも知れません。
 
一応労務管理の先生もお願いしてはいるのですが。。。
 
給料を頂いている自分は、言われた作業を
なるべく簡潔にできる方法を考えなければいけません。
 
これからも、皆様のご教授頼りにしています。
 
本当に有難うございました。