EXCEL関数について教えてください。

質問

勤務表の作り方でいろいろ教えていただき作っていましたが、どうしても以下のところだけどうして良いかわかりませんので、再度質問させていただきました。

　　　　A　 　　B　 　 　C 　 　　D
１　　6:00 　 4:00 　 (2:00) 　 ( 2:00)　
2 　 10:00 　 4:00 　 (0:00 )　 (4:00 )　　

C=　Aが8：00以内ならBを入力　ただし、A+Cが8:00以内でMAX4:00まで
　　　A+Bが8:00を超える場合、8:00を超えるまでがC　8:00超えた部分をD
D=　A＋Cが8:00を超えた場合の残りのB
　　　すでに、Aだけで8:00を超えている場合、B

Bを分解してA＋Bが8:00以内ならMAX4:00 まででＣに
　A+Bが8:00を超える場合、MAX４:00までがC　超えた部分をD

このような質問でわかっていただけるでしょうか？　
つたない説明ですが、このよう場合の　C　D　にどのような関数を入れたら良いのかご教示いただければ助かります。
よろしくお願いいたします。

回答No.6

kagakusuki

済みません、下記の事柄に関しても、御教え頂けないでしょうか？

>休憩は、深夜勤務の26時～29時の3時間だけです。

との事ですが、昼食や晩御飯(及び早朝勤務の時間帯によっては朝食も)等の食事休憩時間は無く、食事は1回目の退勤から再出勤までの間に行う事になっているか、或いは手漉きとなった合間の時間に食事を摂るので、その時間は休憩時間とは見做さず、勤務時間と見做しているのかのどちらかという事なのでしょうか？
　しかしそれでは、もしも20時よりも前の時刻に再出勤した場合には、休憩時間なしで6時間を超えて連続して勤務するという事になり、

労働基準法第三十四条
使用者は、労働時間が六時間を超える場合においては少くとも四十五分、八時間を超える場合においては少くとも一時間の休憩時間を労働時間の途中に与えなければならない。

に違反してしまう恐れが出て来ないとも限りませんが、その辺りの扱いはどうなっているのでしょうか？
(20時よりも前に再出勤する事は規定か何かで禁止しているという事なのでしょうか？)

　後、

>A=出勤8:00

との事ですが、出勤時刻欄よりも左の列に1～31の日付欄を設けなくとも良いのでしょうか？
　又、表が何年何月度の月度のものかというデータを入力するセルはどのセルで、どの様な形式で入力されているのでしょうか？
　上記の月度のデータに加えて、月度が始まる日(前月度の締日の翌日)は何日からなのかという事が判りますと、曜日の自動表示も可能となりますし、例えば2月等の場合において、28日まで(閏年には29日まで)の表示となる様にする事も出来ますが、そういった機能は付けた方が宜しいのでしょうか？(もし締日が月末ではない場合には、例えば「4月度の月度が始まる日は3月20日」等の様に月度と「月度の初日」の双方の月の関係も明記して頂く様に御願い致します)
　尚、曜日を表示させる場合には、その表示方法も「28 火」や「28(火)」の様にA列の1つのセルにまとめて表示させる様にする事も出来ますし、A=28、B=火の様にA列に日付を示す数字、B列に曜日を表示させる様にする事も出来ます。
　又、「出勤」、「退社」、「再出勤」、「再退社」、等々の各項目名が入力されている行は2行目だと考えて宜しいのでしょうか？

補足

色々ありがとうございます。
作成したい表は、夕方から早朝まで勤務する方を対象としています。また、日中に勤務（2時間くらい）し、その日の夕方から早朝まで再度勤務するパターンもありますので、再出勤・再退社を設定しました。　よって休憩時間は夜勤部分のみでOKなのです。
（今のところ22時までの勤務で8時間を超えることは発生しませんが今度のことは未定なので・・・）

また、全体のレイアウトまで考えていただきありがとうございます。
年月を入れると自動で返す方法は知っていましたので、あえて省きました・・・
確かに、左端から　日、曜日を配置し2行目スタートです。ちなみに、4月度は3月11日から４月10日です。

本当にありがとうございます。

回答No.5

kagakusuki

>それぞれの時間帯のうち、通常勤務（22時まで、8時間以内、以降）、深夜勤務時間、早朝勤務29時以降　は抽出できます。

との事ですが、前回の補足コメント欄で御説明頂いた内容

　A　　通常勤務　　　　14:00
　B　　　～22時　　　　8:00　
　C　　　　残業22まで　6:00
　D　　深夜勤務計　　　4:00
　E　　　　 内？
　F　　　　 外？　
　G　　早朝勤務計　4:00
　H　　　内　？
　I　　　外　？

の中には、出勤時刻のデータも退勤時刻のデータも無く、さりとて

>実際には、A＝勤務開始から22時までの勤務
>　　　　　B＝深夜勤務で22時～26時までの4時間　

に関する値も今回は入力されていない以上、通常勤務（22時まで、8時間以内、以降）、深夜勤務時間、早朝勤務29時以降　のそれぞれの値は互いに独立したデータとなってしまうため、抽出する事は不可能で、各々のデータを手入力するしか方法は無いと思われます。
　それを「抽出できます」と仰っておられるという事は、まだ隠している条件や情報があるとしか思えません。
　その条件や情報によっては、前回の補足コメント欄で御説明頂いた内容のみに頼った方法よりも簡単に結果を出す方法もあるかも知れませんし、何より「抽出できます」と仰った値に関して、後になってから「実際にデータを入力してみた処、正しい抽出結果が得られない場合がある事が判りました」などという事になれば、また今回の様な二度手間となる恐れも考えられます。
　例えば、

>D　　深夜勤務計　　　4:00

というのは、回答No.3に対する補足欄で仰っておられた

>Aで8時間を超えない場合、BのMAX4時間のうちどこまで足せば8時間になるのか（C）

と同じデータであると思われますが、その値を求める方法として回答No.4で御伝えした関数

=IF(COUNT($A1,$B1)=2,TIME(,ROUND(MIN("8:00"-MIN($A1,"8:00"+0),$B1)*1440,0),),"")

に関して、

>　尚、

>>BはMAX4：00までしかならないこと。

>という条件がありますので、上記の関数では翌日のAM5:00以降まで勤務する場合に関しては考慮しておりません。

と条件がありますので、

>D　　深夜勤務計　　　4:00

とはならないと思われるのですが、本当に

>勤務時間8:00～33:00

というデータを入力した際に深夜勤務計が4:00になったのでしょうか？

　その様な訳で、二度手間を避けるために実際にどのセルにどのようなデータが入力されるのかという事や、8時間以内や22時以前においても何時から何時までが休憩時間となっているのかという事、早出等の扱いはどうなっているのか、等々の各種の必要な情報に関しても御教え願います。

補足

重ね重ね申し訳ありません。要領悪すぎですね・・・
休憩は、深夜勤務の26時～29時の3時間だけです。なので、あえて休憩時間のセルを設定しなかったのですが、したほうがよいのでしょうか？
始業・退社は人によって違いますので、基本勤務時間はありませんので遅刻・早退の扱いはありません。

今までは、深夜勤務から早朝勤務の基本があったので、Vルックアップ関数で、A勤務は○○時～○〇時で勤務時間を参照させていたのですが、基本勤務も状況によって変更になることが多く、また、多岐にわたりしかも遅刻・早退、勤務も再出勤があったりなど、Vルックアップでは対応がしんどくなりましたので、実際の始業、退社から計算していきたいと、そのほうが基本に振り回されなくて済むと思いこのように変更しようと考えました。

勤務時間　A=出勤8:00　B=退社10:00　C=再出勤10:00　D=再退社33:00
↑これは、1日で2度出勤するパターンがあるためです。途中空くことがほとんどですが。）
E＝　　通常勤務（始業から22時まで）5時以前に勤務することがないので5時に設定
=MIN(MAX(B3,5/24),22/24)-MIN(MAX(A3,5/24),22/24)+MIN(MAX(D3,5/24),22/24)-MIN(MAX(C3,5/24),22/24)

F=　　始業から～22時までの8時間以内　
=MIN(E3,8/24)　　

G=　22時までで8時間以上　
=MAX(MIN(B3,22/24)-MIN(A3,22/24)+MIN(D3,22/24)-MIN(C3,22/24)-F3,0)

H＝深夜勤務　（22時～26時まで）
=MIN(MAX(B3,22/24),26/24)-MIN(MAX(A3,22/24),26/24)+MIN(MAX(D3,22/24),26/24)-MIN(MAX(C3,22/24),26/24)

I=深夜勤務のうち8時間内に入る時間
　？？？
J=深夜勤務のうち8時間を超える時間
　？？？
L＝早朝勤務
=MAX(B3,29/24)-MAX(A3,29/24)+MAX(D3,29/24)-MAX(C3,29/24)

M=早朝勤務のうち8時間以内の時間
　？？？
N＝早朝勤務のうち8時間以上の時間
　？？？

上記のような表を作っている途中です。

今後、もし変更があるとすれば深夜勤務の休憩時間帯です。現在は26時～29時ですが・・・

このような説明でいけますでしょうか？　
現在入れている関数も変更したほうが良ければ教えていただけますか？

本当にすみません・・・・・

回答No.4

kagakusuki

>実際には、A＝勤務開始から22時までの勤務
>　　　　　B＝深夜勤務で22時～26時までの4時間　
>　　Aで8時間を超えない場合、BのMAX4時間のうちどこまで足せば8時間になるのか（C）、BのMAX4時間のうち残りは何時間か（D)　DはCが分かれば引いたら良いのですね。

　要するに、「深夜勤務の内、勤務開始から8時間を超えない分」(休憩時間は除外)をC、「深夜勤務の内、勤務開始から8時間を超えた分」をDとすれば良いという事でしょうか？
　それならば、「MAX4時間」という事は関数に組み込まなくても宜しいのではないでしょうか？

　それで、どの様な関数にすれば良いかと申しますと、まず、C1セルに次の関数を入力して下さい。

=IF(COUNT($A1,$B1)=2,TIME(,ROUND(MIN("8:00"-MIN($A1,"8:00"+0),$B1)*1440,0),),"")

　次に、D1セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER($C1),$C1<$B1),TIME(,ROUND(($B1-$C1)*1440,0),),"")

　そして、C1～D1のセル範囲をコピーして、同じ列範囲の2行目以下に貼り付けて下さい。

　尚、

>BはMAX4：00までしかならないこと。

という条件がありますので、上記の関数では翌日のAM5:00以降まで勤務する場合に関しては考慮しておりません。
　因みに何故、TIME関数やROUND関数を使用しているのかと申しますと、Excelでは日時のデータを扱う際に、シリアル値という1日を数値の1として数える形式で日時を表していて、1時間は24分の1日、1分は1440分の1日なので、Excel内では
1時間=1/24≒0.0416666666666667
1分=1/1440≒0.000694444444444444
として扱われますが、Excelで扱う事が出来る桁数は15桁までですので、左から16桁目の所で誤差が発生します。
　そのため、Excelで時刻のデータのまま計算処理を行い、その結果を基にしてまた時刻データのまま計算処理を行うといった事を繰り返したり、計算処理結果がある値と=(イコール)であるのかや、単純な大小を比較する様な判定を行う際に、微妙な所で誤差が影響して想定した結果が得られない場合があります。
　あくまで念の為ですが、その様なおそれを避けるためにROUND関数とTIME関数を組み合わせる事で誤差をキャンセルしています。

　後、余談ですが、B列のセルの書式設定の表示形式を[ユーザー定義]の

[赤][>0.16667]"超過 "h:mm;h:mm

に設定しておきますと、B列のセルに4:00を超える値(例えば4:01等)が入力された際に、赤い文字で

超過 4:01

という形式で表示されますので、入力ミスやルールを無視して時間超過をした場合にも直ぐに判ります。

補足

ありがとうございます!!!　BをC、Dに分解できました!!
この分解が分かれば、あとはできると思っていましたが甘かったです・・・

＊例　勤務時間8:00～33:00（かなり極端にしています。）

深夜勤務だけでなく、早朝勤務もあります。
それぞれの時間帯のうち、通常勤務（22時まで、8時間以内、以降）、深夜勤務時間、早朝勤務29時以降　は抽出できます。
（ちなみに、26時～29時は休憩で勤務時間には含めません。）

上記パターンでいくと、通常勤務で既に8時間を超えているので、深夜勤務の時間も、早朝勤務も8時間超えのセル　F、Iになります。
通常勤務で8時間に満たなければ、Dを8時間内（E）8時間外（F)　に分け、それでも満たなければGをとなります。

　A　　通常勤務　　　　14:00
　B　　　～22時　　　　8:00　
　C　　　　残業22まで　6:00
　D　　深夜勤務計　　　4:00
　E　　　　 内？
　F　　　　 外？　
　G　　早朝勤務計　4:00
　H　　　内　？
　I　　　外　？

このようなパターンも可能なのでしょうか？
後出しになってしまいすみません。本当にすみません。勤務パターンはこれ以上変更はありません!!!

今一度ご教示いただけると助かります。

回答No.3

kagakusuki

後、

>A+Bが8:00を超える場合、8:00を超えるまでがC　8:00超えた部分をD

という条件と、

>A+Bが8:00を超える場合、MAX４:00までがC　超えた部分をD

という条件は矛盾してしまう場合がありますが、その場合はどちらの条件に従えば良いのでしょうか？
　例えば、Aが3:00でBが12:00の場合、

>A+Bが8:00を超える場合、8:00を超えるまでがC　8:00超えた部分をD

という条件に従えば、Cが8:00－3:00＝5:00、Dが3:00+12:00－8:00＝7:00になります。
　一方、

>A+Bが8:00を超える場合、MAX４:00までがC　超えた部分をD

という条件に従えば、CがMAXの４:00となりますし、「超えた部分」という部分の意味を、そのまま「A+Bが8:00を超えた部分」という意味だとした場合には、Dは3:00+12:00－8:00＝7:00になりますが、もし「A+BがA+Cを超えた部分」という意味だとした場合には、Dは12:00－4:00＝8:00になります。

補足

ありがとうございます。Aだけで８時間を超えた場合はDになります。
意味は後者になります。
説明で抜けていたものがあります。
BはMAX4：00までしかならないこと。なので、C＋D＝MAX4：00になります。
　　　　　　　　　　　　　Bを分解して（8:00以内）　（8:00以上）
A＝2:00　B=4:00　の場合　　　　　　　C=4:00　　　D＝0:00
A=6:00　B＝3:00　の場合　　　　　　　C=2:00　　　D=1:00
A＝8:00　B＝0:00　の場合　　　　　　　C=0:00　　　D＝4:00

実際には、A＝勤務開始から22時までの勤務
　　　　　B＝深夜勤務で22時～26時までの4時間　
　　Aで8時間を超えない場合、BのMAX4時間のうちどこまで足せば8時間になるのか（C）、BのMAX4時間のうち残りは何時間か（D)　DはCが分かれば引いたら良いのですね。
単純にC=IF(A>8/24,””,””)　Aが８:００より大きければ空白　はわかるのですが、
A+B＞8/24になる場合、Bを分解する方法がわかりません。

何度も申し訳ありません・・・

回答No.2

kagakusuki

確認したい事があります。

>D=　A＋Cが8:00を超えた場合の残りのB
>　　　すでに、Aだけで8:00を超えている場合、B

との事ですが、AとBがともに8:00を超えている場合には、Dをどの様な値にすれば良いのでしょうか？
　例えばAが12:00で、Bが10:00の場合、「8:00を超えた場合の残りのB」という条件に従うと、Dの値はA＋C-8:00の計算結果である14:00という事なります。
　一方、「Aだけで8:00を超えている場合、B」という条件に従うと、Dの値はBと同じ10:00という事なります。
　どちらにの条件に従えば良いのでしょうか？

回答No.1

kuma56

>このよう場合の　C　D　にどのような関数を入れたら良いのか・・・・
基本的に、条件によって計算式を変えるようなものだから　IF関数　を使うことになるんじゃないのかい？？？
例
Aが8：00以内なら・・・・・IF(A<=８,・・・・・
A＋Cが8:00を超えた場合・・・・IF(A+C＞8,・・・・

>どうしても以下のところだけどうして良いかわかりませんので
どうしてもって言うけど、何か自身で考えた方法を試しましたか？
だとしたら、その時はどんな方法を取りましたか？
それらが判ると、質問す自身のスキルを推測できたりするし、うまくいかない要因を解明するのに役立つなどして、よりよいアドバイスが貰いやすいと思いますよん！