休憩時間がある場合の時間計算

質問

経営時間は９時から２２時までです。
１７時から時給が変更になります。
休憩時間はそれぞれとる時間帯が違います。
素人なりに色々勉強して下記の式までは作ったのですが、

題：Ｅ２１出勤時間・Ｆ２１休憩開始時間・Ｏ２１休憩終了時間・Ｐ２１退社時間
２２行目より日々の勤務時間を入力します。

＜前半の勤務状態を集計します＞
・１７時までの勤務時間
Ｇ２２　=IF(ISERROR(MIN("17:00",F22)-E22),"",MIN("17:00",F22)-E22)
・１７時以降の勤務時間
Ｈ２２　=IF(ISERROR(MAX("17:00",F22)-"17:00"),"",MAX("17:00",F22)-"17:00")

＜後半の勤務時間を集計します＞
・１７時までの勤務時間
Ｑ２２　=IF(ISERROR(MIN("17:00",P22)-O22),"",MIN("17:00",P22)-O22)
・１７時以降の勤務時間
Ｒ２２　=IF(ISERROR(MAX("17:00",P22)-O22),"",MAX("17:00",P22)-O22)

上記内容ですと、１７時をすぎてからの出社の場合＃＃＃がでてしまいます。
また、１８時から１９時の出勤の場合、
１７時以降の勤務時間が１時間ではなく、２時間となってしまいます。
マクロはわからないので、関数でつくりたいのですが、どうしてもうまくできません。
前半、後半、１日の計をそれぞれ１７時前と、後で集計し、１ヶ月の集計もおこないたいと思っています。
本当に素人ですので、わかりやすくご説明お願い致します。

回答No.3

KURUMITO · ベストアンサー

Ｇ22セルには次の式を入力します。
=IF(OR($E22="",$P22=""),0,MAX(MIN("17:00",$P22)-MAX($E22,"9:00"),0))-IF(OR($O22="",$F22=""),0,MAX(MIN($O22,"17:00")-MAX($F22,"9:00"),0))
この式は17：00までの勤務時間の計算で休憩時間を差し引いたものです。

Ｈ22セルには次の式を入力します。
=IF(OR($E22="",$P22=""),0,MAX(MIN("22:00",$P22)-MAX($E22,"17:00"),0))-IF(OR($O22="",$F22=""),0,MAX(MIN($O22,"22:00")-MAX($F22,"17:00"),0))
この式は17：00以降で22：00までの勤務時間の計算で休憩時間を差し引いたものです。

したがってＱ列やＲ列は必要ないでしょう。

お礼

早々のご回答ありがとうございます。
数式を理解するのに時間がかかってしまい、お返事が遅くなってしまい申し訳ありません。
こちらの計算結果は完璧でした。
私が使ったことのないＯＲが出てきたので、戸惑いましたが
勉強になりました。
本当にありがとうございました。
足し算引き算、ＳＵＭしか使ったことがなかったのですが、
関数という便利な物に出会い、勉強すればする程エクセルの素晴らしさを感じていました。
ですが、今回は挫折しそうでした＾＾；
私の場合、式をパット見て理解出来ないので、日本語に置き換えて紙に書いて理解するので、本当時間がかかります。
KURUMITOさんはそんな事をしなくてもわかるとは思いますが、
こうして私が書いた式に目を通して回答して頂いたことを感謝します。
本当にありがとうございました。

回答No.2

zongai

・営業時間は9:00～22:00
・17:00を境に集計を分ける
・休憩時間がまちまち（一度限りとする）
・最後に17:00前後での勤務時間を集計する

E列：出勤時間
F列：休憩開始時間
O列：休憩終了時間
P列：退社時間
G列：－（前半と後半統合により排除）
H列：－（前半と後半統合により排除）
Q列：～17:00までの勤務時間
R列：17:00～の勤務時間

…と、勝手に表を変更して考えます。

休憩の取り方によって、3つのケースに分けられます。

（Ａ）[休憩終了時間]が17:00より前の時
[～17:00の分]＝17:00－[出勤時間]－（[休憩終了時間]－[休憩開始時間]）
[17:00～の分]＝[退社時間]－17:00

（Ｂ）休憩が17:00を跨いだとき
[～17:00の分]＝[休憩開始時間]－[出勤時間]
[17:00～の分]＝[退社時間]－[休憩終了時間]

（Ｃ）[休憩開始時間]が17:00より後の時
[～17:00の分]＝17:00－[出勤時間]
[17:00～の分]＝[退社時間]－17:00－（[休憩終了時間]－[休憩開始時間]）

それぞれのケースを条件式にすると
（Ａ）=IF([0列]<17/24,[TrueＡ],[False])
（Ｂ）=IF(AND([F列]<17/24,[0列]>17/24),[TrueＢ],[False])
（Ｃ）=IF([F列]>17/24,[TrueＣ],[False])
※「True●」は上に書いたそれぞれのパターンの計算式
※1日はシリアル値（書式で標準としたときの値）で「1」、「17/24」は1日の17/24で「17:00」

これを一つにまとめると
[判定式]=IF([0列]<17/24,[TrueＡ],IF([0列]>17/24,[TrueＣ],[TrueＢ]))

（Ａ）の条件が成立すれば[TrueＡ]実行、違うなら（Ｃ）の条件判定へ。
（Ｃ）の条件が成立すれば[TrueＣ]実行、違う（ＡでもＣでも無い）なら[TrueＢ]を実行。

[～17:00の分(Q22)]=IF(O22<17/24,17/24-E22-(O22-F22),IF(F22>17/24,17/24-E22,F22-E22))
[17:00～の分(R22)]=IF(O22<17/24,P22-17/24,IF(F22>17/24,P22-17/24-(O22-F22),P22-O22))

各々の計算は17:00を基点にしている関係で
[～17:00の分(Q22)]の式は、出勤時間が17:00以降の時はエラー
[17:00～の分(R22)]の式は、退社時間が17:00以前の時はエラー
になってしまいます。

そこで
エラーの時に何も表示させない（""）ようにするため
=IF(ISERROR（[～17:00の分(Q22)]の式),"",[～17:00の分(Q22)]の式）
とすると長ったらしいので、
単純に、[出社時間]と[退社時間]で判断。条件を加えて、

[～17:00の分(Q22)]=IF(E22>17/24,"",IF(O22<17/24,17/24-E22-(O22-F22),IF(F22>17/24,17/24-E22,F22-E22)))
[17:00～の分(R22)]=IF(P22<17/24,"",IF(O22<17/24,P22-17/24,IF(F22>17/24,P22-17/24-(O22-F22),P22-O22)))

17:00前後の足し算についてはSUMですぐできますよね。
合計の書式はユーザー定義で
[h]:mm
としておくのを忘れないように

駆け足で書いてみました。
お役に立てれば幸いです。

補足

早々のご回答ありがとうございます。
理解するのに時間がかかってしまい、お返事を付けるのが遅くなってしまいました。ごめんなさい。
ＩＦの関数をきちんと理解していない状態だったので、とても勉強になりました。ありがとうございます。
この式の場合、早退をして退社時刻が１７時よりも前の場合、
～１７時の集計が違ってくるようです…。
退社時刻が１７時よりも前の場合の条件も加える場合どうなるのかがわかりません。
私なりにもう一度調べてみますが、もしご回答頂けたら嬉しいです。

回答No.1

有田 賢治（@ARIKEN43）

mimi557788さん 今日は！
>経営時間は９時から２２時までです。１７時から時給が変更になりま
>す。休憩時間はそれぞれとる時間帯が違います。
■Excel(エクセル)実用編：時給計算書の作成例(Excel2002・2003)↓
http://www.eurus.dti.ne.jp/~yoneyama/Excel/jituyou/jikyu.htm
内容：1.完成例・2.基本数値の入力・3.勤務時間の計算・4.支払時間の計算・5.支給額の計算・6.日付・7.時刻/時間の基本的な計算方法などは別ページで説明しています。
完成例→サンプルファイル(jikyu-01.zip　約20KB)のダウンロードで詳細手順のコードが入手できます。
■Excel実用編：勤務時間の計算例「関数使用」(Excel2002・2003)↓
http://www.eurus.dti.ne.jp/~yoneyama/Excel/nyumon/kinmu/index.html
内容：はじめに勤務時間計算の条件→一つのセルでいろんな条件を付けて計算しようとすると、長い数式になり修正が非常に大変になります。
このページでは、なるべく平易な分かりやすい数式となるように、各時間帯別に勤務時間を計算する方法で説明しています。
(作業列をちょっと多めに使用しています)
勤務時間計算の条件設定
通常勤務の時間帯　：　 8:00～17:00（休憩時間　12:00～13:00）
残業時間帯　　　　：　17:00～22:00
深夜残業時間帯　　：　22:00～30:00 (翌朝 6:00）
出勤時間は30分単位で切り上げ、ただし、8:00以前の出勤はすべて8:00出勤とします。
退勤時間は30分単位で切り捨てとします。
勤務時間の計算は30分単位で切り捨てとします。
計算表のスタイルを決める→計算表のスタイル
各勤務時間帯の時刻を6,7行目に入力します：各勤務時間帯の計算式を同一のものにするためです。
目障りならこれらの行を非表示にすればOKです。
計算用の出勤時間と退勤時間をF列とG列に表示することにします。
数式を簡潔にして、見やすくするためです。目障りならこれらの列も非表示にすればOKです。
24時以降は25:00，30:00の入力としています。
セルの表示形式を『[h]:mm』とします。
時間の切り上げと切り捨て→ここでは、まず以下の計算の条件を処理します。
(4) 出勤時間は30分単位で切り上げ、ただし、8:00以前の出勤はすべて8:00出勤とします。
(5) 退勤時間は30分単位で切り捨てとします。
(6) 勤務時間の計算は30分単位で切り捨てとします。
■Excel講座 Q&A(Tips)：時刻・時間関連(Excel2002・2003)↓
http://www.eurus.dti.ne.jp/~yoneyama/Excel/ex-q-a/q_jikoku.html
内容：時刻の計算→勤務時間の計算を例として説明します。
出社時刻 8:00　退社時刻 18:30　の場合="18:30"-"8:00"　と数式を入力します。(時刻は『"』(ダブルクォーテーションマーク)でくくります。)　
0.4375 と表示されます。 0.4375 はシリアル値と呼ばれる値ですので、これを　10:30　と表示するには【書式】→【セル】を選択し、【表示形式】タブの分類で【時刻】、種類で【13:30】を選択すると、「10:30」と表示されます。
出社時刻がB3セル、退社時刻がC3セルに入力されている場合、D3セルに勤務時間を求めるには「=C3-B3」となります。　セルの表示形式は(1)と同様に時刻に変更します。
勤務時間の計算→パートの人の勤務時間計算で、定時が 9:00～17:00　となっている場合の計算方法を考えてみましょう。
　　　　　9:00　　　　　　　　17:00
　(A)　<---+-----------------+----->
　(B)　　　　　　<-----------+----->　　
　(C)　　　　　　<------->
詳細な操作手順が図解されています。自己流で此処まで作成出来たのは大したもんです。プログラムを作成するには結果は同じでもコードは作成者毎に微妙な差が有りますので、参考になる他のプログラムを参考にする事がノウハウに繋がります。プログラム作成基本を身に付けましょう。上記内容をご自分で作成された物を比較しご自分に合ったプログラムに当てはめて頑張って下さい。

お礼

早々のお返事ありがとうございました。
初歩的な事しかわからなかった私が、関数の素晴らしさをしってから
ネットに公開されている情報を参考に色々と作ってみました。
今回は本当に難しく、悩みました。
今までは休憩のない勤務体系だったので、それ用にエクセルをくんでいました。
その入力内容から給与明細書や、給与台帳、振込依頼書等、自動転記をくんでいたため、出来る限り計算結果が出るセルの場所などを変更したくないというのもあり、難儀していました。
教えて頂いたサイトはとてもわかりやすく参考になりました。
ただサイトをのせるのではなく、きちんと説明付きで教えて頂き、
ありがとうございます。

私の周りにはエクセルの初心者ばかりで、素人の私がこの給与関係のエクセルを作るのにどれだけ大変だったのかを理解してくれる人も、評価してくれる人もいませんでした。
なので、ARIKEN43さんが大したもんですって言ってくれた事、
とても嬉しかったです。
ありがとうございます。