[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『製作終了時間の計算（休憩時間あり）』（あ）
製作の終了時間の計算をしたいのですが、やり方がわかりません。
就業時間 9:00〜17:45
休憩時間1 12：30〜13：30
休憩時間2 15：30〜15：45
商品名 製作時間 開始日 開始時間 終了日 終了時間
1 8時間 7/1 9：00
2 10時間 7/2 17：00
3 25時間 7/3 14:20
製作時間、開始日、開始時間を入れると終了日、終了時間を数式で出したいのですが、日付が変わったり、休憩時間を計算するような形にするのがどうやっていいかわかりません。
よろしくお願いします。
＜ 使用 Excel：Office365、使用 OS：unknown ＞
考え方を書きますと、1日の勤務時間数を求め（7:30ですね？）、必要な時間数をこれで割った商と余りに分けます。 余りが少しでもあるなら1日追加になるよう、切り上げましょう。 余りの方は、休憩時間それぞれより大きいようなら、休み時間分加算すれば良いでしょう。
数式にしてみたのが以下ですが、合っているかは確認していないです。 もっと単純化できそうに思いますが、考えるのが面倒なので、手なりで書いたままです。
E2: =C2-1+ROUNDUP((B2/24/TIMEVALUE("7:30")),0)
F2: =MOD(B2/24,TIMEVALUE("7:30"))+TIMEVALUE("9:00")+LOOKUP(MOD(B2/24,TIMEVALUE("7:30"))+TIMEVALUE("9:00"),{0,0.520833333,0.645833333},{0,0.041666667,0.052083333})
（???） 2019/07/22(月) 16:22
順序だてて考えてみてください
・当日作業できる時間の算出
・翌日以降通日作業する日数の算出
・最終日に終了する時間の算出
の順で考えます
まず開始日は無視します。
１．開始時間から当日の終了時間（17：45）までの「当日の残り時間」を算出します・・・A
２．休憩時間を挟むので開始時間によって変化する調整時間を算出します・・・B
３．A-Bで当日の作業可能時間を算出します。・・・C
４．Cが制作時間より少なければ、当日で終了しますので終了日は同じで、終了時間も算出できます
５．Cが制作時間より多いときは翌日以降に終了しますので、翌日以降の作業時間を算出します。制作時間-C・・D
６．Dを7.5時間で割って、翌日以降で通日かかる日数を算出します・・・E
７．最終日の残り作業時間を算出します。D-E×7.5・・・F
８．最終日に9：00から始まって単純に終了する時間を算出します。9：00＋F
９．休憩分、終了時間が伸びるので、調整時間をFに加算します。・・・G
１０.終了日は、開始日＋E+1 になります。（当日終了は除きます）
１１．終了時間はGです。
面倒なのは２と９ですが、テーブルを作ってLOOKUP関数で参照するとよいでしょう
（渡辺ひかる） 2019/07/22(月) 16:59
ちょっとお聞きしますが、土日祝日って終了日算出に関係するのでしょうか？
（半平太） 2019/07/22(月) 18:46
＞できれば、土日祝日も考慮したいです。
どう「考慮」するんですか？
「できれば」なので、「出来なければ考慮不要」と解釈していいんですね？
（半平太） 2019/07/22(月) 19:01
＞ どう「考慮」するんですか？ ↑ こっちは、どうなんですか？
単純飛ばしですか、土曜は半日換算とかだったりしませんか？
どこの会社も独自ルールがあるので、チャンと説明して頂きたい。
（半平太） 2019/07/22(月) 19:17
（あ） 2019/07/22(月) 19:24
（渡辺ひかる） 2019/07/22(月) 20:32
１．前提条件 (1)開始日 は、 土日祝 ではないものとします。 (2)開始時刻は、休憩時間帯ではないものとします。 (3)製作時間は、時間シリアル値で入力するものとします。 (例：8時間は 8:00 と入力する）
２．休憩補正表を下図の通り、H1:K5に作成してください。
３．数式 (1) E2セル =WORKDAY(C2,ROUNDDOWN((TEXT(B2+D2-"9:00"-LOOKUP(D2,$H$2:$I$4),"[h]:mm")-"0:00:01")/"7:30",0),祝日) (2) F2セル =MOD(TEXT(B2+D2-"9:00"-LOOKUP(D2,$H$2:$I$4),"[h]:mm"),"7:30")+"9:00"+LOOKUP(MOD(TEXT(B2+D2-"9:00"-LOOKUP(D2,$H$2:$I$4),"[h]:mm"),"7:30")-"0:00:01",$J$2:$K$5)
<結果図> 行 ___A___ ____B____ ______C______ ____D____ _____ E _____ ___ F____ _G_ __H__ ______I______ ____J____ _______K_______ 1 商品名 製作時間 開始日 開始時間 終了日 終了時間 開始 製作時間下駄 製作時間 休憩時間水増し 2 1 8:00 2019/7/1(月) 9:00 2019/7/2(火) 9:30 9:00 0:00 -1 8:30 3 2 10:00 2019/7/2(火) 17:00 2019/7/4(木) 10:45 12:30 1:00 0:00:02 0:00 4 3 25:00 2019/7/3(水) 14:20 2019/7/8(月) 17:05 15:30 1:15 3:30:02 1:00 5 4 8:15 2019/7/2(火) 17:00 2019/7/3(水) 17:30 5:30:02 1:15
※J1セルは、単なるマイナス１です。
渡辺ひかるさんへ
＞質問者さんの態度に進展が無ければ、これ以上の回答は、無意味と考えてますがいかがでしょう？
態度は良くないでしょうね。でも無理はないとも思っています。
この種の質問はかなり難問で、私でも100％は自信ないです。
(自信がある回答者がいたら、お目にかかりたいものです。 多分、タダの過信でしょうから、私が突っ込みを入れることになるかも知れない。 すでに別案が提示されていますが、まともなものじゃないと思っているので ロクに読んでもいません。時間の無駄なので）
ましてや、質問者のレベルでは永遠に到達できることはないでしょう。
回答側で作る以外、解決しないと思っています。
（半平太） 2019/07/22(月) 23:05
レスありがとうございます
了解しました
今後ともよろしくお願いします
（渡辺ひかる） 2019/07/23(火) 09:27
E2: =C2+ROUNDUP(((B2/24-(TIMEVALUE("16:30")+LOOKUP(D2,{0,0.520833333,0.645833333},{0,0.041666667,0.052083333})-D2))/TIMEVALUE("7:30")),0)
F2: =TIMEVALUE("9:00")+MOD(B2/24-(TIMEVALUE("16:30")+LOOKUP(D2,{0,0.520833333,0.645833333},{0,0.041666667,0.052083333})-D2),TIMEVALUE("7:30"))+LOOKUP(TIMEVALUE("9:00")+MOD(B2/24-(TIMEVALUE("16:30")+LOOKUP(D2,{0,0.520833333,0.645833333},{0,0.041666667,0.052083333})-D2),TIMEVALUE("7:30")),{0,0.520833333,0.645833333},{0,0.041666667,0.052083333})
なお、1日で終わってしまう条件だと誤動作する問題を抱えています。 最初に1日で収まるかで分岐すれば簡単に解決しますが、この数式が更に長くなってしまう…。
（???） 2019/07/23(火) 10:10
＞半平太さんの例と同じ結果になるようなので
商品名が「4」も同じになりましたか？
気が進まなかったですが、試しに製作時間を10進数で「8.25」を 入れてみましたが、同じにならなかったです。
※どっちが正しいかは、質問者の判断ですけど。
（半平太） 2019/07/23(火) 10:42
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.