エクセル2019の関数を教えてください。

シフト自由制の１０時から１５時までの勤務時間管理表を作成してる中で、休憩時間を引いた実労働時間を算出したいのですが、式がうまく組めません。
条件としては、
1，あり得る開始時間は１０時から１４時半までの間の３０分刻み。
2，あり得る終了時間は１０時半から１５時までの間の３０分刻み。
3，休憩時間は１２時から１３時までの１時間。
（11時半出勤の12時半退勤なら30分。12時出勤の13時退勤なら休憩時間0になります）
4，リモート労働というのもあって、リモートのみ１２時から１３時半、１２時半から１３時半、１２時から１３時、１２時半から１３時、１１時半から１２時半、１２時から１２時半、この６パターンだけは休憩時間が適用されません。

今、組んでみた式は下記ですが、これでは上記の条件が満たされていない事も理解しています。
=IFS(COUNT(D3:D4)<>2,"",SUM(MAX(0,MIN(D4,"12:00"))-D3),MAX(0,D4-(MAX("13:00",D3))))
D3が開始時間、D4が終了時間、D6にプルダウンリストで「休み」、「リモート」を選べるようになっていて、D5に式を入力して休憩時間を引いた実労働時間を出したいです。

付け焼刃で勉強しながらやってる関数なので素人です。
ご教授よろしくお願いします。

質問者からの補足コメント

• 補足に、あり得る全パターンを書き出している別シートを添付しようとしましたが
  なぜか貼れなかったので、新たに質問を立ち上げそこに添付しました。
  ご覧になってみてください。流用できるでしょうか？
  
  https://oshiete.goo.ne.jp/qa/13273754.html

  No.2の回答に寄せられた補足コメントです。 補足日時：2022/12/17 11:37

• 補足に、あり得る全パターンを書き出している別シートを添付しようとしましたが
  なぜか貼れなかったので、新たに質問を立ち上げそこに添付しました。
  ご覧になってみてください。流用できるでしょうか？
  
  https://oshiete.goo.ne.jp/qa/13273754.html

  No.1の回答に寄せられた補足コメントです。 補足日時：2022/12/17 11:38

• 直接添付できました。
  
  黄色帯が１時間休憩の時間帯
  オレンジ帯が３０分休憩の時間帯
  リモートの赤字が条件4にあたる部分です。
  
  別シートに作成していたものなので流用可能です。
  よろしくお願いします。

  
    補足日時：2022/12/17 12:39

• 大変申し訳ありません。
  J9とJ10は0：30が正しいです。従ってK9が２時間、K10が１時間半になります。

    補足日時：2022/12/17 13:23

• ご回答ありがとうございます！
  ご指摘の通り。10:00～12:30 10:30～12:30 の休憩時間も３０分でした。
  申し訳ありませんでした。
  
  しかし、組んでみましたが休憩時間が引かれた時間が返ってきません。
  なぜでしょうか？
  
  表示形式はすべてのセルが時刻の「13：30」にしています。

  
  No.3の回答に寄せられた補足コメントです。 補足日時：2022/12/19 11:34

• 式自体は間違いありませんでした。
  ご指示通り、形式を標準にしてみると「0.041666667」になりました。
  
  5行目のドロップリストの「リモート」を半角にしているので、組んで頂いた式中の「リモート」を
  半角に変えていますがそれが原因でしょうか？

  No.4の回答に寄せられた補足コメントです。 補足日時：2022/12/19 14:03

• お世話になっております。
  組んで頂いた式をコピペした結果、＃VALUE！が返ってきてしまいました。
  但し、６行目をリモートにした場合のみ正常に計算結果がでました。
  
  見にくいかもしれませんがスクショを添付しておきます。

  
  No.5の回答に寄せられた補足コメントです。 補足日時：2022/12/20 11:20

• こちらの式も結果は先と同じ＃VALUE！でした。
  同じくリモートのみ正常な計算がされています。
  
  先程思い付いたのですが、スプリットシートからエクセルに切り替えたのが干渉してるのでしょうか？

  
  No.6の回答に寄せられた補足コメントです。 補足日時：2022/12/20 11:24

• 今、別シートで試したところ計算自体は正常に動きました。
  どうやらドロップダウンリストが干渉しているようです。
  この場合、６行目は手打ちにしなければダメなんでしょうか？

  
    補足日時：2022/12/20 11:36

• 3行目と４行目はオートフィルを使用することはなく必ず手入力なんです。
  エクセルのブックにコピーなりシートの移動なりした方がいいんでしょうか？
  
  スプレッドシートをエクセルで開いていても中身はスプレッドシートのままですよね？

  No.7の回答に寄せられた補足コメントです。 補足日時：2022/12/21 10:06

No.8 ベストアンサー 回答者： Belltreeriver 回答日時： 2022/12/21 11:54

数式の問題ではなく、貴殿の使用環境や使用方法に原因があると思われます。

当方の環境では問題なく処理されエラーは再現できません。
EXCELのまっさらのシートに最初から表を作成して試してください。
どうしても今の使用方法が変えられないなら、使用環境や使用方法を詳細に書いて、現在起きている事象を再質問されることをお勧めします。

この回答へのお礼

ご指摘の通り、新しいブックにコピーして作り直してみます。
色々とご考案頂いて本当にありがとうございました。
またお見掛けされた際は、よろしくお願いしたします。

お礼日時：2022/12/21 13:00

No.7 回答者： Belltreeriver 回答日時： 2022/12/20 20:03

3行目、4行目の開始/終了時間を全て手入力（オートフィルNG)してみてください。

スプレッドシートで時間を扱う場合はEXCELより詳細な注意点が沢山あります。時間入力もオートフィルを使用すると見た目の時間表示は同じでも内容は微妙に異なります。

No.6 回答者： Belltreeriver 回答日時： 2022/12/20 09:39

貴殿が調べた数式（必ず12:0-13:00が休憩の前提）を活かすなら次の式です。

試してみてください。
D5=IF(OR(COUNT(D3:D4)<>2,D3>=D4),"",
SUM(
MAX(0,MIN(D4,"12:00")-D3),MAX(0,D4-MAX("13:00",D3)),
IF(ASC(D6)="ﾘﾓｰﾄ",
SUMPRODUCT((D3=1*{"12:00";"12:00"})*(D4=1*{"13:00";"13:30"}))/24+
SUMPRODUCT((D3=1*{"12:30";"12:30";"11:30";"12:00"})*(D4=1*{"13:30";"13:00";"12:30";"12:30"})/48),
0)))
※休憩時間帯の例外勤務扱いする時間を加算する考え方です。

No.5 回答者： Belltreeriver 回答日時： 2022/12/19 15:46

リモート以外の結果は如何でしたか？

数式は終了時間(M4)－始業時間(M3)－休憩時間(IF 以降)です。
休憩時間が全くマイナスされないということはIF以降で休憩時間が計算されていないということです）。私のPC環境ではNGが確認（再現）できません。
日付付きの時間だと休憩時間の計算が[0]なので、休憩時間がマイナスされないこと（こちらは確認済）になります。

表6行目に入力する「リモート」は全角でも半角でも対応するよう変更しました。
Ｍ5=IF(OR(COUNT(M3:M4)<>2,M3>=M4),"",M4-M3-IF(AND(ASC(M6)="ﾘﾓｰﾄ",SUMPRODUCT((M3=1*{"12:00";"12:30";"12:00";"12:30";"11:30";"12:00"})*(M4=1*{"13:30";"13:30";"13:00";"13:00";"12:30";"12:30"}))),0,IF(AND(M3<="12:00"*1,M4>="13:00"*1),"1:00",IF(OR(M3="12:30"*1,M4="12:30"*1),"0:30",0))))

No.4 回答者： Belltreeriver 回答日時： 2022/12/19 13:25

補足の画像では数式内容が正しく読み読み取れません。

M5の数式を拡大し、添付しました。数式が正しく入っていれば時間以外の日付情報が入っていませんか。
書式を標準にして、セル内容をご確認ください。時間だけなら1以下の小数点が表示されるはずです。異なる場合は時間のみで再入力してください。
時間（From-To）が正しく入力されていれば添付図のように正しく表示されます。

No.3 回答者： Belltreeriver 回答日時： 2022/12/17 20:24

質問：休憩時間は12:00～13:00の時間帯との説明ですが

10:00～12:30 10:30～12:30 の休憩時間は1:00でよろしいでしょうか？
この時間帯勤務が0:30の休憩でよければ次の数式で試してください。

D5=IF(OR(COUNT(D3:D4)<>2,D3>=D4),"",D4-D3-IF(AND(D6="リモート",SUMPRODUCT((D3=1*{"12:00";"12:30";"12:00";"12:30";"11:30";"12:00"})*(D4=1*{"13:30";"13:30";"13:00";"13:00";"12:30";"12:30"}))),0,IF(AND(D3<="12:00"*1,D4>="13:00"*1),"1:00",IF(OR(D3="12:30"*1,D4="12:30"*1),"0:30",0))))

No.2 回答者： fujillin 回答日時： 2022/12/16 17:05

こんにちは

入力値は全てシリアル値で、必ず 開始時刻＜終了時刻 が成り立つものと仮定してよいものとします。

全体の時間は、　=終了時刻 - 開始時刻
休憩時間は、　=MAX(0,MIN(終了時刻,"13:00")-MAX(開始時刻,"12:00"))
で求められることはご存じと思います。

1～3の条件での実労時間は、ご提示の式とは少し変わりますが、
　=全体時間 - 休憩時間　で求められます。
休憩時間を敢えて求めているのは、4の条件に一致したら 休憩時間*0、その他の場合は 休憩時間*1 とすれば、全て同じ計算でご質問の結果を計算することができると考えてのことです。


4の条件で、他のパターン（6パターン以外）が存在するのかしないのか、存在するならどのようなものなのかがよくわかりませんけれど、もしも「リモートの場合は6パターンのみ」というのであれば、上記の0、1は「リモートか否か」で判定しても良いことになります。

「いろいろなパータンはあるけれど、ご提示の6パターンだけ休憩時間なし」という場合は、条件式にしても良いですが、それなりに煩雑になるので別に表を作成しておいた方が簡単にできると思います。
添付図のような表を別シートにでも作成しておいて、名前の定義でA1:D4を「休憩換算表」、A1:D1を「終了時間帯」と名前を設定しておきます。（←好きな名称で良いです）
（表中の0は休憩時間なしを意味します。仮に、11:30～13:30だと休憩時間ありなので、実労1時間になりますけれど・・・？？）

この表を参照するのには、
　=VLOOKUP(開始時刻,休憩換算表,MATCH(終了時刻,終了時間帯,0),0)
で可能ですが、当然表にない範囲の時刻もあり得るので、その場合は1となるように
　=IFERROR(VLOOKUP(開始時刻,休憩換算表,MATCH(終了時刻,終了時間帯,0),0),1)
としておけば、6パターンなら0、それ以外は1という結果になります。
ただし、リモートの場合のみ適用なので、最終的な係数としては
　=IF(D6="リモート",上記の式,1)
としておく必要があります。

ここまでの内容をまとめて
　=全体時間 - 休憩時間 * 係数
とすることで、お求めの結果になるものと思います。


※ 開始時刻、終了時刻の入力がされていることのチェック、開始時刻＜終了時刻であることのチェックに関してはお分かりと思いますので、省略してあります。
※ 時刻が30分単位となっていますけれど、本当にそれでよいのかは気になるところですが・・
（休憩換算表は時間が一致していることで検索していますので、端数の分を入力すると、異なる結果になります）

No.1 回答者： Belltreeriver 回答日時： 2022/12/16 15:34

休憩のルールが今いちわかりませんが、次のローテク数式で試してみてください。

D5=IF(OR(COUNT(D3:D4)<>2,D3>=D4),"",D4-D3-IF(AND(D6="リモート",SUMPRODUCT((D3=1*{"12:00";"12:30";"12:00";"12:30";"11:30";"12:00"})*(D4=1*{"13:30";"13:30";"13:00";"13:00";"12:30";"12:30"}))),0,IF(AND(D3<"12:00"*1,D4>="13:00"*1),"1:00",IF(AND(D3>="11:00"*1,D4>"12:00"*1,D4<"13:00"*1),"0:30",0))))