エクセルで就業時間管理で教えてください

質問

勤務時間から早朝・日中・夜間　とそれぞれ表示したいのです。
計算式　教えてください。

　　　　　　Ｅ　　　　　　Ｆ　　　　　　　Ｇ　　　　Ｊ　　　　　ｋ　　　　　Ｌ　　　　　　　　Ｘ　　　　　　　　Ｚ
1　　　　開始　　　　終了　　　　総時間　　早朝　　　日中　　　夜間　　　　基本　9：00　　　18：00
2　　　11：00　　　　　20：00　　　　9.0　　　　0　　　　　7.0　　　　2.0　　　　　早朝　6：00　　　9：00
3　　　7：00　　　　　18：00　　　　11.0　　　　2.0　　　　9.0　　　　　0　　　　　夜間18：00　　　22：30
4

上記のようにＥ・Ｆに時間がありＸ・ＺのデータをもとにＧからＬまで自動で表示させたく計算式をいれているのですがＬの夜間だけうまくできません。どなたか助けてください。お願いします。

回答No.6

bunjii · ベストアンサー

>同じようなことなんですが、7：00から8：30の時間でまたしても負になります。
基本時間帯に掛からないときの問題ですね。

早朝のみになる場合もカバーするには終了時刻のチェックも必要ですね。

K2=MIN(MAX(F2,"9:00"),"18:00")-MIN(MAX(E2,"9:00"),"18:00")

考え方としては終了時刻が9:00以前のときは9:00とし、18:00以降のときは18:00とする。
また、開始時刻が9:00以前のときは9:00とし、18:00以降のときは18:00とする。
MAX関数とMIN関数を組み合わせて論理を構成してください。

手作業で検証する場合は時刻を書いたメモを用意してどれとどれを選ぶと正しい計算になるかを見つけます。
開始時刻、9:00の組み合わせと終了時刻、18:00の2組から目的の計算では各々どちらを選べば良いかのシュミレーションを行います。

お礼

本当に助かりました。複雑なデータ処理がスムーズにできました。最近、エクセル関数の素晴らしさに感激しいろいろトライしています。なかなか難しいですね・・本などを見ても、このように関数の組み合わせはわからず・・。これからもっと頑張ってみます。　たくさん教えていただきまして、ありがとうございました。

回答No.5

bunjii

>しかし開始19：00～終了20：30のような場合、日中がうまくできません。『負の値になるとエラーが起きます』と注意して下さったのはこの事ですね。
そうです。

次の式に訂正してください。

K2=MIN(F2,"18:00")-MIN(MAX(E2,"9:00"),"18:00")

尚、加減算の代わりにSUM関数を使うと計算途中のチェックが容易になります。

=SUM(MIN(F2,"18:00"),-MIN(MAX(E2,"9:00"),"18:00"))

お礼

たびたび早速のご回答感謝いたします。
同じようなことなんですが、7：00から8：30の時間でまたしても負になります。式をきちんと理解して行えばクリアできるでしょうか・？頑張ってみます。ホント助かりました。
ありがとうございます。

回答No.4

bunjii

回答No.1への補足を見ましたので、数式の訂正をしてください。

>総時間は9.0と表示されていますが、Ｊ～Ｌは7：00や1：30のように表示されています。
時刻の値はシリアル値と思いますのでそのまま減算したときに時刻（時間）の表示で7:00のようになります。
7.0にするときは以下のようにしてください。

K2=(MIN(F2,"18:00")-MAX(E2,"9:00"))*24
これは24:00=1と言う値が時間のシリアル値となっているため24倍すると表示形式を標準にしたとき通常の数値として読み取れます。
但し、時給の計算等で値の実態を知らないと正しい結果になりませんので注意してください。

お礼

ありがとうございました。

補足

ありがとうございます。Ｇの総時間数以外はシリアル値ですので、回答3の説明で十分大丈夫でした。（でもとても勉強になりました。）
今、夜間と早朝教えていただいた数式で、ばっちりできました。
・・しかし開始19：00～終了20：30のような場合、日中がうまくできません。『負の値になるとエラーが起きます』と注意して下さったのはこの事ですね。夜間のところがクリアできたのでとりあえずは作業できますが、今後のために教えていただけないでしょうか？お時間のある時で構いません。よろしくお願いします。

回答No.3

bunjii

>上記のようにＥ・Ｆに時間がありＸ・ＺのデータをもとにＧからＬまで自動で表示させたく計算式をいれているのですがＬの夜間だけうまくできません。
用語の誤りを訂正させてください。
「Ｅ・Ｆに時間があり」→「Ｅ・Ｆに時刻があり」
時間は「時刻」と「時刻」の間を言いますので、「開始時刻」と「終了時刻」の間が「総時間」です。

G列は終了時刻から開始時刻を引けばその差が求める時間です。
G2=F2-E2　→　20:00-11:00　→　9:00
Excelでの時間の計算は結果が負の値になるとエラーが起りますので注意してください。

早朝の時間は開始が9:00以前の場合のみ該当しますのでMIN関数でどちらか小さい方を使って計算すれば良いでしょう。

J2=MIN(F2,"9:00")-MIN(E2,"9:00")　→　9:00-9:00　→　0:00
J3=MIN(F3,"9:00")-MIN(E3,"9:00")　→　9:00-7:00　→　2:00

夜間は早朝の反対側の時間帯なのでMAX関数でどちらか大きい方を使って計算します。

L2=MAX(F2,"18:00")-MAX(E2,"18:00")　→　20:00-18:00　→　2:00

日中は9:00から18:00までの時間帯が対象ですからMIN関数とMAX関数を利用することになります。

K2=MIN(F2,"18:00")-MAX(E2,"9:00")　→　18:00-11:00　→　7:00

お礼

わかりやすく説明していただき、ありがとうございます。
今入れている数式よりも、わかりやすく簡単で、すべて作り直そうと思っています。　急いでいたので助かりました。
　時刻と時間・・これからは間違えないようにします。

回答No.2

yugere

L2の数式
=TIME(G2,,)-(K2+J2)

をL3以降にもコピーしたらいいできると思いますよ。

お礼

ありがとうございます。試してみましたが、うまくできませんでした。もっと勉強しなければ駄目ですね・・参考にさせていただきます。

回答No.1

karorumon

総時間から、早朝・日中をひけばいいのでは？

お礼

早速の回答ありがとうございました。

補足

早速ありがとうございます。　訂正します・・
総時間は9.0と表示されていますが、Ｊ～Ｌは7：00や1：30のように表示されています。
複数のシートからデータをとばしているのでこのシートで表示は変えられません・・・説明下手でごめんなさい。よろしくお願いします。