[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『残業計算についてです。』（ゆう）
エクセルほぼ初心者です。業務日誌を作成しています。
呼び出しの様な業務あり呼び出しも超勤とみなしており、残業時間の集計をどうにかエクセルでしたいと考えております。
開始時間と終了時間から 深夜残業（22時から翌朝6時）さらに深夜残業を22時から24時まで、24時から翌朝6時まで、6時以降10時までの残業に分けて集計をしたいのですがどうにもうまくいきません。
開始時間 終了時間 22時から30時 22時から24時 24時から30時 30時以降
21:00 〜 23:00 1:00 1:00 0:00 0:00
23:00 〜 25:00 2:00 1:00 1:00 0:00
24:00 〜 26:00 2:00 0:00 2:00 0:00
27:00 〜 29:00 2:00 0:00 2:00 0:00
28:00 〜 34:00 0:00 0:00 2:00 4:00
のような計算式を作りたいのですができません。
ちなみに22時から24時のところだけ奇跡的にできたのですが式が
=IF(AND(A8<=TIME(22,0,0),I8>=TIME(23,59,59)),TIME(2,0,0),IF(AND(A8<=TIME(22,0,0),I8>=TIME(22,0,0),I8<=TIME(23,59,59)),I8-TIME(22,0,0),IF(AND(A8<TIME(22,0,0),I8<TIME(22,0,0)),"0",IF(A8>TIME(23,59,59),"0",I8-A8))))このようになっています。A8が開始時間・I8が終了時間です。
同じように考えてもほかのところではできませんでした。
考え方から教えてもらえると今後の勉強のためにもよろしくお願いします。
＜ 使用 Excel：Excel2007、使用 OS：Windows10 ＞
例の一番下「28:00〜34:00」の「22時から30時」が 「0:00」になってますが（なってるように見えますが） 「2:00」の間違いなら
22時から30時 =IF(COUNT(A8,I8)<2,"",MAX(0,MIN(I8,"30:00")-MAX(A8,"22:00")))
22時から24時 =IF(COUNT(A8,I8)<2,"",MAX(0,MIN(I8,"24:00")-MAX(A8,"22:00")))
24時から30時 =IF(COUNT(A8,I8)<2,"",MAX(0,MIN(I8,"30:00")-MAX(A8,"24:00")))
30時以降 =IF(COUNT(A8,I8)<2,"",MAX(0,I8-MAX(A8,"30:00")))
他にどんなパターンがあるのか知りませんが、例題でしか考えてません。 これでオッケー？ （笑） 2016/10/07(金) 00:07
開始と終了以外のセル位置がわからなかったんで ↑ のようにしましたが、 「22時から30時」は、 「22時から24時のセル」＋「24時から30時のセル」 でもオッケー
参考まで。 （笑） 2016/10/07(金) 00:37
（ゆう） 2016/10/07(金) 22:24
水を差すつもりではないのですが、一応「お約束」という事で・・・
>エクセルほぼ初心者です。 という事なので、気になりました。 エクセル上での時間シリアルの計算は誤差が発生し易いのでお気を付け下さい。 特に賃金計算の基礎となる数字をコレで出そうとしているのであれば、 計算方法は熟考した方が良いですよ。 例えば
=TIME(21,10,0)-TIME(20,0,0) と =TIME(1,10,0) の 計算結果は、どちらも「1:10」のハズですが、
=TIME(21,10,0)-TIME(20,0,0)=TIME(1,10,0) の 結果が「FALSE」だったりします。 時間計算じゃなくても、 =1.2-1.1=0.1 の 結果が「FALSE」だったりしますので。 「時間シリアルでの計算」コレそのものは別に良いと思うのですが、 その計算結果を以って何かしらを判断したり、算定したりする場合に ひと工夫が必要になるハズです。
「いや、そんなシビアな仕事じゃないんで」という事であれば、 新聞のコラム程度に思って、あまり気にしないでください。
お邪魔しました。
（白茶） 2016/10/07(金) 23:26
■ COUNT()<2,"" の意味
COUNT 関数は数値の個数。 なので、A8 と I8 の数値（時刻）の個数が「2未満」だったら、時間計算しないで「""」にする。
時間計算するのは COUNTが「2」を返す場合、つまり A8とI8 の両方に時刻が入っている場合だけ、ということ。
開始時間と終了時間のセルには常にどちらにも時刻が入力されていて、 空白になることなんかあり得ないんだったら、そんな条件分岐は不要です。
■ MAX(0 はなぜ必要か？
↓ の式で説明します。 22時から24時 =IF(COUNT(A8,I8)<2,"",MAX(0,MIN(I8,"24:00")-MAX(A8,"22:00")))
A8が「26:00」、I8が「29:00」だとする。
MIN(I8,"24:00")-MAX(A8,"22:00") ← の部分は「"24:00"-"26:00"」になるので（注1） 結果はマイナスになってしまう。
注1）
MIN(I8,"24:00") → MIN("29:00","24:00") → MIN だから小さい方の "24:00"
MAX(A8,"22:00") → MAX("26:00","22:00") → MAX だから大きい方の "26:00"
だから「"24:00"-"26:00"」になる
（終了−開始）の計算がマイナスになる場合は、結果を「0」にするために
MAX(0,終了-開始) という式にしています。
（終了−開始）がマイナスなら、MAX だから大きい方の「0」（0:00）になるということ。
こんな説明でいいですか？ （笑） 2016/10/08(土) 02:21
to 白茶さん
> 特に賃金計算の基礎となる数字をコレで出そうとしているのであれば、 > 計算方法は熟考した方が良いですよ。
例えば ↓ な感じにしろってことですかね？
=IF(COUNT(A8,I8)<2,"",TEXT(MAX(0,MIN(I8,"30:00")-MAX(A8,"22:00")),"h:mm")*1)
ではこんな誤差対策をとらなかったら、賃金計算のどんな場合にどんな影響が出るのか 具体例を挙げて教えていただけませんか？
> =TIME(21,10,0)-TIME(20,0,0)=TIME(1,10,0)
↑ 誤差の影響でイコールにならない、ぐらいのことはわかってますので、 こういうことじゃなく、賃金計算の場合にどんな影響が出るのか、ということです。
後学のためですので、よろしくお願いします。 （笑） 2016/10/08(土) 02:27
笑様。どうもです。
>例えば ↓ な感じにしろってことですかね？ > =IF(COUNT(A8,I8)<2,"",TEXT(MAX(0,MIN(I8,"30:00")-MAX(A8,"22:00")),"h:mm")*1) いえいえ。 別にそういう意味ではないのです。 時間を算出する段階で全ての計算にソレをやっていたらキリがありませんし、 計算によっては逆効果になる場面もあろうかと思いますので、 別に普通にやってもよいと思います。 >>「時間シリアルでの計算」コレそのものは別に良いと思うのですが、 >>その計算結果を以って何かしらを判断したり、算定したりする場合に とも申しましたが、 時間の計算が終わった後、その計算結果を何にどう使うか。です。 例えば =IF(TIME(21,0,0)-TIME(20,0,0)>=TIME(1,0,0),"１時間以上","１時間未満") の結果は「１時間未満」になろうかと思います。
賃金計算となると、 残業の拘束時間から1時間10分毎に10分間の休憩時間を差引くとか 所定労働時間を超えた部分が何時間以上だっら、この時間以降の単価はこっちとか 会社の賃金規定によって条件は異なります。
その条件を判定する場合に計算結果の「ナマ数値」を使ったら、 場合によっては未払事故になっちゃう事もあるから気を付けてね。という事です。
スレの趣旨からは完全に逸脱した部分の心配なのです。 まさに「余計なお世話」なのです。 でも知らない人には大切な情報なのです。
>新聞のコラム程度に思って、あまり気にしないでください。 なのです。 >お邪魔しました。 なのです。 すみません・・・
（白茶） 2016/10/08(土) 09:35
この度はお尋ねでもない余計な話で勝手にスレを汚してしまい、申し訳ありませんでした。 エクセルを使っていく上での一助となり得る情報だったなら幸いです。 (↑ゆうさんのお礼レスが「自分にも向けられてる」と勝手に解釈してる辺りがイタいですね。書いてて恥かしい)
汚しついでに白状しておきます。(←反省してねぇな!)
私自身、時間シリアルの扱いには、正直ついてけません。
=--"21:00" は 0.875 ←*24*60 で 1260分
=--"20:00" は 0.833333333333333 ←*24*60 で 1200分
="21:00"-"20:00" は 0.0416666666666666 ←*24*60 で 59.9999999999999分 ズレた─┐
=--"1:00" は 0.0416666666666667 ←*24*60 で 60.0000000000001分 ズレた ｜
ズレ方が違う
=1/24*21 は 0.875 ←*24*60 で 1260分 ｜
=1/24*20 は 0.833333333333333 ←*24*60 で 1200分 ｜
=1/24*21-1/24*20 は 0.0416666666666667 ←*24*60 で 60.0000000000001分 ズレた─┘
=1/24 は 0.0416666666666667 ←*24*60 で 60.0000000000001分 ズレた
=--"21:00" は 0.875
=1/24*21 は 0.875 で、どちらの表記でも同じ。
=--"20:00" は 0.833333333333333 =1/24*20 は 0.833333333333333 で、どちらの表記でも同じ。
でも
="21:00"-"20:00" は 0.0416666666666666 ←*24*60 で 59.9999999999999分 =1/24*21-1/24*20 は 0.0416666666666667 ←*24*60 で 60.0000000000001分 =1/24*21-"20:00" は 0.0416666666666666 ←*24*60 で 59.9999999999999分 ="21:00"-1/24*20 は 0.0416666666666667 ←*24*60 で 60.0000000000001分
もう、わけわからんのデス 誤差法則を見切ってる玄人さんならあるいは可能なのでしょうが、私程度の理解では無理デス。 大小比較等による条件分岐とか、 Lookupの検索値への使用とか、 Trunc,Ceil,Floor等による端数の丸めとかで 予想外の結果が返ってきて、しかもこれになかなか気付かない。 (そんなとこで誤差が出てるなんて思わないですから) ってとこがまたタチ悪い。
だからもう、分単位の整数に変換してから計算するしかないわね。と毎回逃げるです。 整数なら少なくとも人間側の意図通りの結果が保証される程度には精度が上がります。 計算過程で誤差に配慮しないで済む。ということです。(私見です。当然異論はある筈)
あぁ、ヤバイ。また長文を... いい加減もう消えますね。
（白茶） 2016/10/11(火) 23:34
＞ =--"20:00" は 0.833333333333333 ＞ =1/24*20 は 0.833333333333333 で、どちらの表記でも同じ
表記は同じでも、実体には差があります。下記A3セル
行 _____A_____ 1 0.833333333 =--"20:00" 2 0.833333333 =1/24*20 3 1.11022E-16 =(A1-A2) ←括弧でくくってみる 4 1.11022E-16 =0.5^53 0.5の53乗だけA1が大きい 5 0.041666667 ="21:00"-"20:00" 6 0.041666667 ="21:00"-1/24*20 7 1.11022E-16 =A6-A5 その分だけA6の方が大きくなる。 8 何故こっちは括弧でくくらなくても差が出て来るのか不思議
小数の2進数演算が暗算ができる人なんて多分いないでしょうから、 的確に予測するのは無理だと思っています。
小数演算が絡んだ時は、結果が微妙に大きかったり、小さかったりするかも知れないと 思って対応するしかないと思っています。
（半平太） 2016/10/12(水) 00:21
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.