『残業集計表を作成したい』（ひろ）
勤怠表を作成しているのですが数式が上手く行かず困っています。
皆さんのお知恵をお貸しください。
定時時間：始業8:00、終業17:10、10時休憩10:00〜10:10、昼休憩12:00〜12:50、15時休憩15:00〜15:10、残業開始時間17:30〜、残業集計単位15分毎という勤務形態で
E列出勤時間、F列退勤時間、G列に集計結果が出る構成となっており、
=IF(COUNT(E10,F10)<2,"",MIN("8:00",F10-E10))+IF(F10="","",FLOOR(MAX(0,F10-"17:30"),"0:15"))
という式で定時出勤＋定時退勤、残業時は15分刻みで残業時間も合計された実働時間が計算できるようになりました。
これに遅刻、早退、半休の条件を含めたいのですが上手くできませんのですが、どうしたら良いでしょうか？
遅刻、早退時も集計単位は15分毎となります。
ちなみにC列には当日の勤怠条件を数値で手入力しD列にその内容が表示されるようになっています。
1=出勤、2=休日出勤、3=休日、4=有給、5=半休、6=代休、7=遅刻、8=早退、9=欠勤です。
これを利用した形でも良いです。
＜ 使用 Excel：Microsoft365、使用 OS：Windows11 ＞
（?） 2025/02/21(金) 10:31:12
＞遅刻、早退時も集計単位は15分毎となります。
遅刻5分、早退5分だと、合計何分引かれるのですか？
17:10から17:30の間に退社したら、その労働時間はどうなるんですか？
残業時間は、15分単位と言う条件のみで、17:30以降は全てカウントするんですね？
真夜中の12時過ぎの退社時刻は、どういうデータなんですか？ 25:00形式？ それとも1:00形式？
（半平太） 2025/02/21(金) 13:55:16
＞残業時間は別セルに表示されるようになってますが、
なら、今回は残業時間は考慮しないでいいですね。 ※単にそのセルを加算すればいいので。 但し、空白文字列が返されているとマズいので、「+N(そのセル)」として0に換算する必要があります。
=IF(COUNTBLANK(E10:F10),"",REDUCE(0,{"08:00-10:00";"10:10-12:00";"12:50-15:00";"15:10-17:10"},LAMBDA(tWorked,tZone,tWorked+MAX(0,MIN(RIGHT(tZone,5),"17:10"-CEILING(TEXT(MAX(0,"17:10"-F10),"h:m"),"0:15"))-MAX(LEFT(tZone,5),CEILING(E10,"0:15"))))))
※時間計算は、小数演算誤差の問題が付いて回るのでちょっと厄介ではあります。 なお、そちらの残業時間の算出数式の妥当性については検討しておりません。 （半平太） 2025/02/21(金) 17:56:48
＞ちなみにC列には当日の勤怠条件を数値で手入力しD列にその内容が表示されるようになっています。 ＞1=出勤、2=休日出勤、3=休日、4=有給、5=半休、6=代休、7=遅刻、8=早退、9=欠勤です。
ポーッと読んでいましたが（ほぼスルーと同意）、 遅刻と早退を同日にした場合に対応する数値が無いですね。 休日出勤で、遅刻、早退、遅刻早退をした場合も無いです。 半休の場合も同様に無いです。
また、そもそも半休の場合における定時に相当する時間帯の説明がないので、 遅刻、早退の判定は不可能でした。
先述の回答は無視してください。m(__)m
（半平太） 2025/02/22(土) 08:55:33
ついでに、少し曖昧にしていた部分も書いて行きます。
例えば、15:00で早退したとします。 １．定時の17:10まで 2時間10分 あるので、15分単位で考えると14：55に早退したと看做して計算する（のか） ２．定時より 2時間10分前 に退社したのだから、2時間15分を所定実働時間（8時間）から差し引いて計算する（のか）
１と２の 実働時間はそれぞれ 5時間55分、5時間45分となり、10分の相違があります。
ひろさんの会社では、どっちの解釈なんでしょうか？
（半平太） 2025/02/22(土) 09:26:42
> １と２の 実働時間はそれぞれ 5時間55分、5時間45分となり、10分の相違があります。
もう一つありました(これが本命かも) m(__)m
出退勤時刻を15分刻みにする。 ３．15:00 ならぴったりなので、そのまま計算して、実働6時間とする。
（半平太） 2025/02/23(日) 10:46:36
?A仮に14:55早退の場合15分刻みですので14:50で切る形となります。記載漏れていますが10:00〜10:10、15:00〜15:10は休憩時間とし実働には反映されません。
?B計算した結果は定時間内の実働時間ではなく残業した場合の時間も含めた総実働時間が表示されるのが理想です。難しいでしょうか？ なんならマクロでも良いのですが・・・
（ひろ） 2025/02/25(火) 07:38:38
＞なんならマクロでも良いのですが・・・ 手法以前に、計算ルールが理解できていない、と言うことなんですけども。
＞PM半休時は8時以前〜12:10以後打刻、 この「打刻」にはどんな意味が含まれているんでしょうか？ 「全貌判明後なので、半休に遅刻・早退はあり得ず、PM半休時は実働3時間50分、AM半休時は実働4時間10分しかない」 と言うことなんでしょうか？
＞仮に14:55早退の場合15分刻みですので14:50で切る形となります。 14:50になる理由を教えてください。
＞計算した結果は定時間内の実働時間ではなく残業した場合の時間も含めた総実働時間が表示されるのが理想です。 残業時間は別セルに表示されるようになっているとのことなので、それを加算すればいいと思うのですが、
その別セルをこの際、省略したいと言うことなんですか？
（半平太） 2025/02/25(火) 09:36:22
「全貌判明後なので、半休に遅刻・早退はあり得ず、PM半休時は実働3時間50分、AM半休時は実働4時間10分しかない」と言うことなんでしょうか？ 打刻の意味ですが、通常時は当然朝出勤時(8時以前)打刻し退勤時(17時10分以降)も打刻すると思いますが、弊社の場合午後半休の場合12時で打刻すると勤務時間が3:50となってしまう為12:10以降に打刻するルールがあります。（午前半休の場合定時退勤すると4:10の勤務時間となりますがこれも4:00で切ってるようです）。 これが計算を更に面倒にする要因となります。また、ご指摘の通り半休の場合遅刻早退は有り得ません。
＞14:50になる理由を教えてください
14:55早退で計算上は14:50となる理由は端数切捨ての為です。
＞残業時間は別セルに表示されるようになっているとのことなので、それを加算すればいいと思うのですが
確かにそうですね。
（ひろ） 2025/02/25(火) 10:20:34
＞＞14:50になる理由を教えてください ＞14:55早退で計算上は14:50となる理由は端数切捨ての為です。
申し訳ありません。私は理解できないです。
他の回答者のレスをお待ちください。m(__)m
（半平太） 2025/02/25(火) 15:04:45
食指が動く人が現れませんかぁ・・
では、追加でお聞きします。
「端数」の意味は、「15分刻みではない」と解釈しているのですが、 時刻としては 14:50 も端数があるので、「時刻の端数」じゃないのは明白と判断します。
すると、「実働時間の端数」かなと思ったりもするのですが、
14:55 は、8:00からなら5:55、17:10までなら2:05 14:50 は、8:00からなら5:50、17:10までなら2:10
いずれも、15分刻みでは端数があります。
すると何を端数と見て切り捨てるのですか？
（半平太） 2025/02/26(水) 16:36:16
私の考え方は以下ですけど、それで何か問題がありますか？
１．半休 遅刻・早退はあり得ないので、特に計算はせず、実働時間は一律４時間にする。
２．半休以外全て
(1)出社が 8:00を過ぎた場合(遅刻）は、実働換算で何時間何分遅れたかを計算し、15分刻みまで水増しする。
(2)退社が17:10より前の場合(早退）は、実働換算で何時間何分早かったか計算し、15分刻みまで水増しする。
定時時間帯(8:00〜17:10）の実働時間 = ８時間 - (1) - (2)
<サンプル> 行 __E__ __F__ ______G______ 9 出勤 退勤 実働（切捨後) 参考(遅刻水増前) 参考(早退水増前) 10 8:00 14:55 5:45 0:00 2:05 11 8:00 14:50 5:45 0:00 2:10 12 10:15 17:10 5:45 2:05 0:00 13 12:50 17:10 4:00 3:50 0:00 14 10:15 14:50 3:30 2:05 2:10
（半平太） 2025/02/27(木) 15:11:08
マクロとか関数とかの手段とは無関係です。それ以前の話なので。
それで、私の提示したサンプルの「(切捨後)実働時間」は正解なのですか？ それをお聞かせください。
（半平太） 2025/02/27(木) 22:52:38
＞サンプルの通りで合ってます。
そうなんですね。 手計算だとミスが起こり易い考え方なので違うかもなぁ・・と思っていたのですが。
バージョンがMicrosoft365との事なので、以下を提案します。
１．名前定義を2つ設定します。
Ctrlキーを押しながら、F3キー押下で「名前の管理」ダイアログを出して以下の通り設定する
(1) 名前は「実働Pure」とし、参照範囲ボックスに以下の数式を入力する
=LAMBDA(St,Ed,LET(ZN,{"08:00-10:00";"10:10-12:00";"12:50-15:00";"15:10-17:10"},REDUCE(0,ZN,LAMBDA(tWorked,tZone,tWorked+MAX(0,MIN(RIGHT(tZone,5),Ed)-MAX(LEFT(tZone,5),St))))))
(2) 名前は「実働15刻み」とし、参照範囲ボックスに以下の数式を入力する =LAMBDA(preSt,PreEd,CEILING(実働Pure(preSt,PreEd)-"0:0:01","0:15"))
２、残業集計表シートに以下の数式を入力して、下にコピー ※残業時間のセルを補ってください。(下式の「??」。仮にA10セルなら→N(A10）とする)
G10セル =IF(COUNT(E10,F10)<2,"",IF(B10="半休","4:00"*1,"8:00"-実働15刻み("8:00",E10)-実働15刻み(F10,"17:10")+N(??)))
↑
※残業時間が入った別セルを指定する
（半平太） 2025/03/03(月) 13:26:20
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.