[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『時間の丸め 計算式短くなりますか? 変ですか?』（勤怠管理）
すみません｡数式が長くなり後で確認しようとすると理解するまでに時間が掛かってしまいます｡もっと簡単にできますか。
丸め条件
5:00~ 7:30 0:15 7:30~ 8:00 0:30 8:00~10:00 0:05 10:00~10:10 0:10 10:10~12:00 0:05 12:00~13:00 0:30 13:00~15:00 0:05 15:00~15:10 0:10 15:10~17:00 0:05 として
A1 ｾﾙ
出勤時刻入力(例 8:00)
B1 ｾﾙ
=IF(A1="","",IF(AND(A1>"5:00"*1,A1<"7:30"*1),CEILING(A1,"0:15"),IF(OR(AND(A1>"7:30"*1,A1<"8:00"*1),AND(A1>"12:00"*1,A1<"13:00"*1)),CEILING(A1,"0:30"),IF(OR(AND(A1>"10:00"*1,A1<"10:10"*1),AND(A1>"15:00"*1,A1<"15:10"*1)),CEILING(A1,"0:10"),CEILING(A1,"0:05")))))
どうでしょうか?
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
VLOOKUP案。
A B C D E 1 7:35 8:00 5:00 0:15 2 7:30 0:30 3 8:00 0:05 4 10:00 0:10 5 10:10 0:05 6 12:00 0:30 7 13:00 0:05 8 15:00 0:10 9 15:10 0:05
D:E列に対応リスト。 B1 =IFERROR(CEILING(A1,VLOOKUP(A1,D:E,2)),"5:00未満") （GobGob） 2017/02/23(木) 18:07
退勤がFLOORでえーかどーかはそちらのルールなんで わかりまへんよ。 （GobGob） 2017/02/24(金) 00:01
A B C D E F 1 7:35 17:13 8:00 17:00 5:00 0:15 2 7:30 0:30 3 8:00 0:05 4 10:00 0:10 5 10:10 0:05 6 12:00 0:30 7 13:00 0:05 8 15:00 0:10 9 15:10 0:05 10 17:00 0:30 11 17:30 0:05
C1 =IFERROR(CEILING(A1,VLOOKUP(A1,D:E,2)),"5:00未満")
D1 =IFERROR(FLOOR(A1,VLOOKUP(B1,D:E,2)),"5:00未満")
どうでしょうか? ちなみに､"5:00未満"を空白""でも大丈夫ですか?
（勤怠管理） 2017/02/24(金) 00:45
えーとね。。。。
その退勤にFLOORを使うかどうかってのは勤怠管理さんの会社のルールであって 良いかどうかはわかりまへん。
「退勤が会社のルールで切捨て丸めになるのでFLOOR関数に変更すればよいか？」って質問なら FLOORなんでしょーね。。。みたいな回答っすわ。
「5:00未満」はただのエラー対策なんで、空白でもいいんでないっすか？ （GobGob） 2017/02/24(金) 08:21
会社のルールってことなんで、質問者さんに言っても始まらないんですが 1日ごとの勤務時間の丸めは労働基準法違反なので。 調査が入ったら、全部計算し直しさせられて未払賃金を支払わないといけなくなりますよ。 従業員の立場じゃどうしようもないでしょうけど、危機感は持っておいた方がいいかも。
>丸めに掛かる労働時間は申告制になってます
これも意味がわからない。 （コナミ） 2017/02/24(金) 10:43
表示上は 0:15 となっていても、実データは「0:15±α」です。
誤差が正か負かで、丸めたときに繰り上がるのか繰り下がるのか
その時々で結果が別れます。
小数誤差による影響を完全に排除したいならば、シリアル値ではなく、
『分の整数値（３０分とか１２０分とか）』で扱うべきでしょう。
整数値を１５や３０などの整数で丸め(FLOOR/CEILING)ても誤差は
発生しません。
処理後の「分データ」は、[ 24*60 ⇒ 1440 ]で割ればシリアル値になります。
シリアル値を「分データ」にするには 『 =TEXT(A1,"[m]")*1 』という風に
するのが安全です。
[ =INT(A1*1440) ] でも同じようになりますが、小数演算による小数誤差により、
INT関数の結果が期待通りにならない場合があります。
見た目通りの「分データ」にするには TEXT関数による『時刻編集』が安全です。
（角田） 2017/02/26(日) 00:24
現在エラーが発生しています。
もしかしたらこれが原因かも･･･。普通の空白セルならば問題は無いのですが
この計算式による空白は #VALUE! 表示されてしまいます。
エクセルはあまり得意ではないので･･･。
C1 =IFERROR(CEILING(A1,VLOOKUP(A1,D:E,2)),"")
で返された空白はご説明の現象に当てはまりますか?
よろしくお願いします。
（勤怠管理） 2017/02/26(日) 01:21
なぜ突然、小数演算誤算の話が出てきたのか分からないですが、 今後、私の回答にも影響する事なので書き込みます。
「C、D」列以外のデータがすべて手入力なら、小数演算誤算の影響は受けません。
誤差が出たという話は過去10年間一度もなかった、と言う経験則にすぎませんが、 反証が出るまで、この路線で行きます。
※なぜ発生しないかについては、我々一ユーザーには分からない仕掛けがあると思っています。 多分、当該関数による時間データの演算では、PC内部の計算精度が高くなる一方、 最終表示されるDouble型/Date型の精度の低さ、およびエクセルの有効桁数15桁と言う仕様が 旨いこと絡み合っているんだろうなぁと思っています。
※手入力データというのは、「一つ一つ手で入力した時刻データ」のことです。 初めの数個を手入力して、あとはフィルコピーで埋める、というのは手入力データと呼べません。 今回のケースでは、データに規則性がないので、「C、D」列以外は手入力であろうと推測します。
（半平太） 2017/02/26(日) 08:52
＞ なぜ突然、小数演算誤算の話が出てきたのか分からないですが、
CEILING/FLOORの基準値に 0:15 や 0:30 を使うことになりますので、
基本的に「小数を小数で丸め」という事になります。
十進法（六十進法）で切りが良いと人の目には映る「0:15 や 0:30」も、
二進法では丁度切りの良いピッタリの二進表現ではない（近似値）ですから、
それを考えると、結果に違いが【出るかもしれない】ので、
それならば【１００％確実な整数演算方法もありますよ】という意味で
紹介させて貰いました。
議論に混乱を招いたようで申し訳ありません。
＞ 我々一ユーザーには分からない仕掛けがあると思っています。
ＭＳも上手い仕組み（お助け機能）を考えて組み込んでいるのでしょうね。
（角田） 2017/02/26(日) 15:35
＞ＭＳも上手い仕組み（お助け機能）を考えて組み込んでいるのでしょうね。
どうなんでしょうね。 私は、PCのノーマルな演算メカニズムで普通に対応できているかも知れないと思っています。
・・と言っても、人間にとっては、ギョッとなる処理でしょうけども。
5分は、小数にすると 1/288 ですよね。 つまり、0.003472222・・・の循環小数です。
でも、エクセルは桁数の限界で「0.00347222222222222」までしか認識していないです。 つまり「0.000000000000000002222222222222・・・・」だけ小さい値で認識しています。
そうだとすると、 =CEILING(1,"0.05") が 289/288 ⇒ 1.00347222222222 よりホンの少し小さい値、 =FLOOR (1,"0.05") が 288/288 ⇒ 1.00000000000000 よりホンの少し小さい値 であるべきです。
でも、実際は両者とも、完全な「1」を返します。
＞結果に違いが【出るかもしれない】
その見解に異論ないですが、現実に演算誤差が認識されたと言う報告が10年間ないんです。 【出るかもしれない】けど一度も出たことがない事象の為に、 面倒な仕掛けを付加するのは避けたいです。
掲示板の質問者は、同じ答えが得られるなら、簡略なものを望みます。 Q&Aの回答は、採用されてナンボの面もあります。
（半平太） 2017/02/26(日) 19:49
半平太さん 角田さん コメント有難う御座います。
すみません。議論の内容にまったくついて行けずコメントするのに時間だけが
過ぎてしまいました。エクセルと言う以前に「十進法」「二進法」もまだ
なんとなく理解した程度です。(ポンコツですみません）
『定時間内の勤務時間が知りたい』[20170223205526]
の返信コメントになってしまいますが、N( ) 関数で無事表示できました。
丸め作業は完了し、所定時間内の労働時間もお陰様でできました。
次は総労働時間と所定時間外の労働時間を『定時間内の勤務時間が知りたい』
の方で作って行きたいと思います。
ご教授願えましたらそちらの方でもお力添えをお願したいです。
本当に有難うございました。
（勤怠管理） 2017/02/27(月) 10:26
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.