月間シフト表から総勤務時間を計算する関数

こんにちは。
月間シフト表から、総勤務時間を計算する関数を考えています。
マクロではなく、関数でやりたいです。
A,B,C,D,E,F,G....
Xさん,9:00,18:00,9:00,18:00,9:00,14:00.....
Yさん,9:00,18:00,9:00,14:00,11:00,20:00.....
というようにセルが分かれていて、左から右へ計30日分あるような表です。
一日の稼働時間が8時間を超える場合は一時間休憩時間があるものとみなしますが、休憩時間を記したセルはありません。

それぞれの総勤務時間をどこかのセルにポンと出したいです。
横に続く形式なので、繰り返しで書くととんでもなく長い式になり困っています。
またシフト表の形式を変えることは難しいです。あくまでいまの形式のシフト表から計算したいです。

明確な答えでなくとも、この関数いいよなどでもいいので、アドバイスいただけますと助かります。

よろしくお願いいたします。

No.4 回答者： goomania 回答日時： 2023/05/21 18:05

添付画像をご覧ください。

>左から右へ計30日分あるような表です。

とのことですが、30日分では画像範囲が広くなりすぎて詳細が見えなくなってしまうので、とりあえず7日分を計算する数式です。

画像ではQ3セル以下に各人の「総勤務時間」が表示される訳ですが、Q3セルに

=IF(A3="","",SUMPRODUCT((C3:O3-B3:N3-(C3:O3-B3:N3>1/3)/24)*ISODD(COLUMN(C3:O3))))

という数式を記述し、下方向へコピーしています。また、Q3以下の書式を「[h]:mm」にしています。

退勤時刻の範囲C3:O3,出勤時刻の範囲B3:N3は実際のデータに合わせて調整してください。

No.3 回答者： fujillin 回答日時： 2023/05/20 14:03

こんにちは

＞総勤務時間をどこかのセルにポンと出したいです
＞あくまでいまの形式のシフト表から計算したいです
手順を踏んで計算すれば、簡単にできることと思います。
複雑な形式にして難くしておいて、「ポンと出したい」というのは、自分で自分の首を絞めているような気がします。

それは置いておいて・・
＞8時間を超える場合は一時間休憩時間があるものとみなします
8時間丁度は、休憩時間は無いものとすると解釈しました。

※ 正確なセル範囲がわかりませんので、以下はご提示の例に合わせた例示です。
※ 計算の都合上、表の最終列の右隣の1列は空き列にしておく必要があります。
　（添付の例では、H列がこれに該当します。）
※ 表中の時刻の値は、全てシリアル値（＝エクセルの時間型の値）であると想定しています。

添付図では、I2セルに
=IF(A2="","",SUMPRODUCT((C2:H2-B2:G2-(C2:H2-B2:G2>1/3)/24)*MOD(COLUMN(C2:H2),2)))
の式を入れ、下方にフィルコピーしてあります。

※ 合計値が24時間を超える可能性がある場合は、書式を [h]:mm などにしておく方が良いでしょう。
※ 表の範囲が実際のものとは異なると思いますので、お使いの表に合わせて調整してください。

No.2 回答者： mike_g 回答日時： 2023/05/20 10:31

添付図参照(Excel 2019)

Sheet1 において、
￣￣式 =Sheet2!AG2 を入力したセル AG2 を必要なだけ下方にオート
￣￣フィル
Sheet2 において、
￣￣A2: =Sheet1!A2
￣￣式 =IF(Sheet1!B2>"8:00"*1,Sheet1!B2-"1:00",Sheet1!B2)
￣￣を入力したセル B2 を右方にズズーッと(AF列まで)オートフィル
￣￣AG2: =SUM(B2:AF2) ←書式は [h]:mm
￣￣範囲 A2:AG2 のフィルハンドルを下方にドラッグ&ペースト
オ・シ・マ・イ

No.1 回答者： mike_g 回答日時： 2023/05/20 07:32

》 シフト表の形式を変えることは難しいです。

》 いまの形式のシフト表から計算したい。
を前提にすると、
「総勤務時間を」「ポンと出」せるセルが「どこ」なのか、貴方がキッチリ指定すべきかと。何処？

この回答へのお礼

私の質問文でご気分を害してしまいましたでしょうか、申し訳ございませんでした。
言葉が足りておらず、大変失礼いたしました。
総勤務時間を出すセルはシフト表の表部分以外の箇所であれば、同シート上のいずれのセルでも構いません。

お礼日時：2023/05/20 07:49