[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『指定した時間を除いた計算』（ここあ）
WindowsXP Excel2003 使用です。
仕事でダイヤ表を作っているのですがアドバイスください。
Ａ B C D E 便 荷役時間 ピッチ １ 7：00〜7：35 50 ２ 7：45〜8：20 45 ３ 8：40〜9：15 55 ４ 9：30〜10：05 50 ５ 10：40〜11：15 70 ６ 11：50〜12：25 70 ・ ・ ・ ・ ・ ・
・荷役時間はトラックが荷物を積みに来る時間です。毎週変わります。 ・ピッチは前便の荷役終了時間からその便の荷役終了時間までの時間です。今回アドバイスを頂きたいのはここです。
困っているのが休憩時間はピッチには含まないというところです。 休憩時間（非稼働時間）は毎週時間が変わり、その時間帯も決まっていません。 今は地道に一つずつ自分でピッチの計算をして、休憩時間とかぶってないかどうか確認して、休憩時間分を引いて、ということをしているのですが実際は２０便近くあるので時間がかかってしまって…。 単純に便と便の間の時間を計算するだけの関数なら出来たのですが、休憩時間を除いた時間の計算がよくわかりません。指定した時間帯（休憩時間）を除いた時間計算は出来るのでしょうか？
わかりにくい説明ですみません。どうかよろしくお願いします。
>休憩時間を除いた時間の計算がよくわかりません 休憩時間がどのように指定され、その結果としてどのようになればいいのかな 例題に、休憩時間を記載し、期待する結果も記載ください
ちなみに、E2がなぜ50になるのでしょう? 0ではないのかな
【例題】
A B C D E 便 荷役開始 荷役終了 ピッチ 1 7:20 〜 7:45 45 2 8:30 〜 8:55 25 3 9:35 〜 10:00 65 4 10:45 〜 11:10 60 5 11:45 〜 12:10 60 6 13:55 〜 14:20 60 7 16:05 〜 16:30 120 8 20:05 〜 20:30 45 9 21:10 〜 21:35 0 10 22:15 〜 22:40 40 11 23:10 〜 23:35 55 12 0:05 〜 0:30 45 13 1:00 〜 1:25 55 14 2:55 〜 3:20 45 15 3:50 〜 4:15 55 16 4:45 〜 5:10 45 17 5:35 〜 6:00 50
稼働時間…昼勤8：30〜17：15、夜勤22：00〜6：45 休憩時間…10：30〜10：40、12：20〜13：30、15：30〜15：40、0：00〜0：10、2：00〜3：10、5：00〜5：10 非稼働時間…昼勤終了時〜夜勤開始時、夜勤終了時〜昼近開始時
ピッチ＝前便の荷役終了時間〜その便の荷役終了時間 ※ただし休憩時間、非稼働時間はこれに含みません ※1便のピッチは最終便の荷役終了時間〜1便の荷役終了時間になります
上で挙げた稼働時間は基本時間です。残業がある場合は0.5H単位で増えていきます。 昼勤は最大3.0H残業、夜勤は最大1.5H残業です。そのときは休憩時間も増えます。
【稼働体制】 定時＝稼働時間（昼8：30〜17：15、夜22：00〜6：45）休憩時間（10：30〜10：40、12：20〜13：30、15：20〜15：30、0：00〜0：10、2：00〜3：10、5：00〜5：10） 0.5H残業＝稼働時間（昼8：30〜17：45、夜22：00〜7：15）休憩時間は定時と変わらず 1.0H残業＝稼働時間（昼8：30〜18：15、夜22：00〜7：45）休憩時間は定時と変わらず 1.5H残業＝稼働時間（昼8：30〜18：45、夜22：00〜8：15）休憩時間（10：30〜10：40、12：20〜13：30、15：20〜15：30、17：20〜17：30、0：00〜0：10、2：00〜3：10、5：00〜5：10、6：50〜7：00） 2.0H残業＝稼働時間（昼8：30〜19：15、夜22：00〜8：15）休憩時間は1.5H残業の時と変わらず 2.5H残業＝稼働時間（昼8：30〜19：45、夜22：00〜8：15）休憩時間（10：30〜10：40、12：20〜13：30、15：20〜15：30、17：20〜17：30、18：50〜19：00、0：00〜0：10、2：00〜3：10、5：00〜5：10、6：50〜7：00） 3.0H残業＝稼働時間（昼8：30〜20：15、夜22：00〜8：15）休憩時間は3.0H残業の時と変わらず
【現状】 その週に出る翌週分の稼動計画を基にダイヤを作り、計画で出た日ごとの稼動体制からピッチを自分で数えていいます。そのため時間が掛かり、人の手で計算しているので間違える可能性があります。
初心者でうまく伝えられずすみません。現状より少しでもいいので時間が掛からず、正確にピッチを求める方法があるようでしたら、アドバイスよろしくおねがいします。
（ここあ）
こんばんは。 かなりめんどそうな作業のようですので、ちょっと考えてみました。 以下に、数式で組んだものを挙げていますが、いっぱいいっぱいです。 （コタ）
１．実動時間テーブル まずは、休憩と非稼動時間を除いた実動時間を求めるために、残業時間ごとのテーブルをSheet2に作成します。 ・16行目までが入力データで、18行目以降はそれを元に、実働時間を算出しています。 ・0時以降8:30までの時間は、全て24時+αに換算しています。（途中で大小が逆転しないように）
【Sheet2】シート ※「_」セルは未入力または"" [R/C] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [1] パターン 1 1.5 2 2.5 3 3.5 4 4.5 5 5.5 6 6.5 7 7.5 [2] 残業 0 0 0.5 0.5 1 1 1.5 1.5 2 2 2.5 2.5 3 3 [3] 開/終 開始 終了 開始 終了 開始 終了 開始 終了 開始 終了 開始 終了 開始 終了 [4] 昼勤 8:30 17:15 8:30 17:45 8:30 18:15 8:30 18:45 8:30 19:15 8:30 19:45 8:30 20:15 [5] 夜勤 22:00 6:45 22:00 7:15 22:00 7:45 22:00 8:15 22:00 8:15 22:00 8:15 22:00 8:15 [6] _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ [7] 休憩昼1 10:30 10:40 10:30 10:40 10:30 10:40 10:30 10:40 10:30 10:40 10:30 10:40 10:30 10:40 [8] 休憩昼2 12:20 13:30 12:20 13:30 12:20 13:30 12:20 13:30 12:20 13:30 12:20 13:30 12:20 13:30 [9] 休憩昼3 15:20 15:30 15:20 15:30 15:20 15:30 15:20 15:30 15:20 15:30 15:20 15:30 15:20 15:30 [10] 休憩昼4 _ _ _ _ _ _ 17:20 17:30 17:20 17:30 17:20 17:30 17:20 17:30 [11] 休憩昼5 _ _ _ _ _ _ _ _ _ _ 18:50 19:00 18:50 19:00 [12] _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ [13] 休憩夜1 0:00 0:10 0:00 0:10 0:00 0:10 0:00 0:10 0:00 0:10 0:00 0:10 0:00 0:10 [14] 休憩夜2 2:00 3:10 2:00 3:10 2:00 3:10 2:00 3:10 2:00 3:10 2:00 3:10 2:00 3:10 [15] 休憩夜3 5:00 5:10 5:00 5:10 5:00 5:10 5:00 5:10 5:00 5:10 5:00 5:10 5:00 5:10 [16] 休憩夜4 _ _ _ _ _ _ 6:50 7:00 6:50 7:00 6:50 7:00 6:50 7:00 [17] _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ [18] 実働昼1 8:30 10:30 8:30 10:30 8:30 10:30 8:30 10:30 8:30 10:30 8:30 10:30 8:30 10:30 [19] 実働昼2 10:40 12:20 10:40 12:20 10:40 12:20 10:40 12:20 10:40 12:20 10:40 12:20 10:40 12:20 [20] 実働昼3 13:30 15:20 13:30 15:20 13:30 15:20 13:30 15:20 13:30 15:20 13:30 15:20 13:30 15:20 [21] 実働昼4 15:30 17:15 15:30 17:45 15:30 18:15 15:30 17:20 15:30 17:20 15:30 17:20 15:30 17:20 [22] 実働昼5 _ _ _ _ _ _ 17:30 18:45 17:30 19:15 17:30 18:50 17:30 18:50 [23] 実働昼6 _ _ _ _ _ _ _ _ _ _ 19:00 19:45 19:00 20:15 [24] _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ [25] 実働夜1 22:00 24:00 22:00 24:00 22:00 24:00 22:00 24:00 22:00 24:00 22:00 24:00 22:00 24:00 [26] 実働夜2 24:10 26:00 24:10 26:00 24:10 26:00 24:10 26:00 24:10 26:00 24:10 26:00 24:10 26:00 [27] 実働夜3 27:10 29:00 27:10 29:00 27:10 29:00 27:10 29:00 27:10 29:00 27:10 29:00 27:10 29:00 [28] 実働夜4 29:10 30:45 29:10 31:15 29:10 31:45 29:10 30:50 29:10 30:50 29:10 30:50 29:10 30:50 [29] 実働夜5 _ _ _ _ _ _ 31:00 32:15 31:00 32:15 31:00 32:15 31:00 32:15 [30] 実働個数 8 8 8 8 8 8 10 10 10 10 11 11 11 11
B18 =B$4 B19 =IF(C7="","",C7+(C7<"8:30"*1)) →B19:B23フィルコピー B25 =B$5 B26 =IF(C13="","",C13+(C13<"8:30"*1)) →B26:B29フィルコピー B30 =COUNT(B18:B29) →B30:C30フィルコピー C18 =IF(B7="",IF(B18="","",C$4+(C$4<"8:30"*1)),B7+(B7<"8:30"*1)) →C18:C23フィルコピー C25 =IF(B13="",IF(B25="","",C$5+(C$5<"8:30"*1)),B13+(B13<"8:30"*1)) →C25:C29フィルコピー ☆ここまででBC列作成済。このあと→B18:C30を選択して、O30までフィルコピー
２．ピッチ計算 次に実際の荷役時間に対して、実働時間との交差を考慮しながら、ピッチを計算します。 ・G3に残業時間を入れると、G〜I列にSheet2から参照した実働時間を表示します。 ・0時をまたぐので、途中で大小関係が変わらないように、J列に24時換算した時間を出力します。 ・荷役開始と終了の時間が実働時間テーブルの何行目に該当するかを、KL列に出力します。 ※両方とも実働時間外だと、K10,L10のように開始＞終了となります。 ※サンプルデータでは、2行以上ずれていないので、E列の数式はその前提で組んであります。 ・これらを元に、E列でピッチ時間を計算します。 ※E2セルは、「最終便の荷役終了時間」が不明なので、パスしました。
【Sheet1】シート ※「_」セルは未入力または"" [R/C] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [1] 便 荷役開始 〜 荷役終了 ピッチ _ 【稼働体制】 _ _ 終了換算 _ _ [2] 1 7:20 〜 7:45 不明 _ 残業時間 パターン _ 7:45 開始 終了 [3] 2 8:30 〜 8:55 25 _ 0 1 1.5 8:55 1 1 [4] 3 9:35 〜 10:00 65 _ _ _ _ 10:00 1 1 [5] 4 10:45 〜 11:10 60 _ 【実働時間】 8 _ 11:10 1 2 [6] 5 11:45 〜 12:10 60 _ 0 _ 0 12:10 2 2 [7] 6 13:55 〜 14:20 60 _ 1 8:30 10:30 14:20 2 3 [8] 7 16:05 〜 16:30 120 _ 2 10:40 12:20 16:30 3 4 [9] 8 20:05 〜 20:30 45 _ 3 13:30 15:20 20:30 4 4 [10] 9 21:10 〜 21:35 0 _ 4 15:30 17:15 21:35 5 4 [11] 10 22:15 〜 22:40 40 _ 5 22:00 24:00 22:40 5 5 [12] 11 23:10 〜 23:35 55 _ 6 24:10 26:00 23:35 5 5 [13] 12 0:05 〜 0:30 45 _ 7 27:10 29:00 24:30 5 6 [14] 13 1:00 〜 1:25 55 _ 8 29:10 30:45 25:25 6 6 [15] 14 2:55 〜 3:20 45 _ 9 _ _ 27:20 6 7 [16] 15 3:50 〜 4:15 55 _ 10 _ _ 28:15 7 7 [17] 16 4:45 〜 5:10 45 _ 11 _ _ 29:10 7 8 [18] 17 5:35 〜 6:00 50 _ _ _ _ 30:00 8 8
E3 =ROUND(IF(K3>L3,0,IF(K3=L3,MIN(INDEX($H$7:$I$17,K3,2),J3)-MAX(INDEX($H$7:$I$17,K3,1),J2) ,INDEX($H$7:$I$17,K3,2)-J2+J3-INDEX($H$7:$I$17,L3,1)))*24*60,) →E3:E18フィルコピー G3 : 入力（残業時間：0〜3の範囲内で0.5刻み）2/8 0:11追記 H3 =G3*2+1 I3 =H3+0.5 H5 =HLOOKUP(H3,Sheet2!B1:O30,30,FALSE) H7 =IF($G7>$H$5,"",SMALL(INDEX(Sheet2!$B$18:$O$29,,H$3*2-1),$G7)) →H7:I17フィルコピー J2 =D2 J3 =D3+(D3<"8:30"*1) →J3:J18フィルコピー K3 =MATCH($J2,$I$6:$I$17,1) →K3:K18フィルコピー L3 =MATCH($J3,$H$7:$H$17,1) →L3:L18フィルコピー
コタさんこんばんは。 お返事ありがとうございます。 見させていただいた限り…かなり難しそうですね(^^；) まだ挑戦してみてませんが先にお返事を頂いた事にお礼を言いたくて。 どうもありがとうございました。
(ここあ)
こんばんは。 ＞ かなり難しそうですね(^^；) そうですね。複数の控除時間を考慮しないといけないので、けっこう複雑になってしまいました。 まずは上のサンプルデータで、ゆっくりと確認してみてください。
あと、【Sheet1】の「G3 : 入力」の部分に入力内容を追記しておきました。 （コタ）
みなさんどうもお世話になっております。
コタさん先日はありがとうございました。 コタさんのアドバイスをもとに作ってみました。
ごめんなさい僕の説明不足でした。 残業時間は昼夜で同じではないんです。 昼勤1.0H残業の時、夜勤も同じく1.0H残業かというとそういう訳でもなくて 定時の場合もあれば、1.5H残業の時もあるし、昼勤3.0H残業の時に夜勤定時もあるといった感じなんです。昼夜で残業時間はバラバラなんです。
僕の説明が足りずすみませんでした。【稼働体制】を昼夜で分けておくべきでした… こんなにご丁寧に教えて頂いたのに…申し訳ありません。
もしよろしければ再びアドバイスよろしくお願いします。
(ここあ)
＞ 昼夜で残業時間はバラバラなんです。 ふーむ、そうでしたか。 言われてみればそのほうが自然でしたね。^^ なるべく上の回答から変更が少なくなるように、考えてみました。 （コタ）
１．実動時間テーブル 昼夜の残業パターンの組み合わせを羅列すると多くなってしまうので、昼/夜の残業時間（B2,C2）をもとに BC列に出力するようにしました。 ・18行目以下は前と同じ数式です。
【Sheet2】シート ※「_」セルは未入力または"" [R/C] [A] [B] [C] [D] [E] [F] [1] 【実働計算】 _ _ _ _ _ [2] 残業昼/夜 0 0 _ [残業 MAX時] [3] 開/終 開始 終了 _ 開始 終了 [4] 昼勤 8:30 17:15 _ _ _ [5] 夜勤 22:00 6:45 _ _ _ [6] _ _ _ _ _ _ [7] 休憩昼1 10:30 10:40 0 10:30 10:40 [8] 休憩昼2 12:20 13:30 0 12:20 13:30 [9] 休憩昼3 15:20 15:30 0 15:20 15:30 [10] 休憩昼4 _ _ 1.5 17:20 17:30 [11] 休憩昼5 _ _ 2.5 18:50 19:00 [12] _ _ _ _ _ _ [13] 休憩夜1 0:00 0:10 0 0:00 0:10 [14] 休憩夜2 2:00 3:10 0 2:00 3:10 [15] 休憩夜3 5:00 5:10 0 5:00 5:10 [16] 休憩夜4 _ _ 1.5 6:50 7:00 [17] _ _ _ _ _ _ [18] 実働昼1 8:30 10:30 _ _ _ [19] 実働昼2 10:40 12:20 _ _ _ [20] 実働昼3 13:30 15:20 _ _ _ [21] 実働昼4 15:30 17:15 _ _ _ [22] 実働昼5 _ _ _ _ _ [23] 実働昼6 _ _ _ _ _ [24] _ _ _ _ _ _ [25] 実働夜1 22:00 24:00 _ _ _ [26] 実働夜2 24:10 26:00 _ _ _ [27] 実働夜3 27:10 29:00 _ _ _ [28] 実働夜4 29:10 30:45 _ _ _ [29] 実働夜5 _ _ _ _ _ [30] 実働個数 8 8 _ _ _
B18 =B$4 B19 =IF(C7="","",C7+(C7<"8:30"*1)) →B19:B23フィルコピー B25 =B$5 B26 =IF(C13="","",C13+(C13<"8:30"*1)) →B26:B29フィルコピー B30 =COUNT(B18:B29) →B30:C30フィルコピー C18 =IF(B7="",IF(B18="","",C$4+(C$4<"8:30"*1)),B7+(B7<"8:30"*1)) →C18:C23フィルコピー C25 =IF(B13="",IF(B25="","",C$5+(C$5<"8:30"*1)),B13+(B13<"8:30"*1)) →C25:C29フィルコピー ↓修正後 B2 =Sheet1!G3 C2 =Sheet1!G4 B4 : 入力（8:30固定） C4 ="17:15:"+B2/24 B5 : 入力（22:00固定） C5 ="6:45:"+C2/24 B7 =IF($B$2<$D7,"",E7) →B7:C11フィルコピー B13 =IF($C$2<$D13,"",E13) →B13:C16フィルコピー
２．ピッチ計算 残業時間の設定を昼夜別（G3,G4）にしました。 ・×〜となっている数式は変更ありです。
【Sheet1】シート ※「_」セルは未入力または"" [R/C] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [1] 便 荷役開始 〜 荷役終了 ピッチ _ 【稼働体制】 _ _ 終了換算 _ _ [2] 1 7:20 〜 7:45 不明 _ 残業昼/夜 開始 終了 7:45 開始 終了 [3] 2 8:30 〜 8:55 25 _ 0 1 2 8:55 1 1 [4] 3 9:35 〜 10:00 65 _ 0 _ _ 10:00 1 1 [5] 4 10:45 〜 11:10 60 _ 【実働時間】 8 _ 11:10 1 2 [6] 5 11:45 〜 12:10 60 _ 0 _ 0 12:10 2 2 [7] 6 13:55 〜 14:20 60 _ 1 8:30 10:30 14:20 2 3 [8] 7 16:05 〜 16:30 120 _ 2 10:40 12:20 16:30 3 4 [9] 8 20:05 〜 20:30 45 _ 3 13:30 15:20 20:30 4 4 [10] 9 21:10 〜 21:35 0 _ 4 15:30 17:15 21:35 5 4 [11] 10 22:15 〜 22:40 40 _ 5 22:00 24:00 22:40 5 5 [12] 11 23:10 〜 23:35 55 _ 6 24:10 26:00 23:35 5 5 [13] 12 0:05 〜 0:30 45 _ 7 27:10 29:00 24:30 5 6 [14] 13 1:00 〜 1:25 55 _ 8 29:10 30:45 25:25 6 6 [15] 14 2:55 〜 3:20 45 _ 9 _ _ 27:20 6 7 [16] 15 3:50 〜 4:15 55 _ 10 _ _ 28:15 7 7 [17] 16 4:45 〜 5:10 45 _ 11 _ _ 29:10 7 8 [18] 17 5:35 〜 6:00 50 _ _ _ _ 30:00 8 8
↓2/10 10:39修正
×E3 =ROUND(IF(K3>L3,0,IF(K3=L3,MIN(INDEX($H$7:$I$17,K3,2),J3)-MAX(INDEX($H$7:$I$17,K3,1),J2)
,INDEX($H$7:$I$17,K3,2)-J2+J3-INDEX($H$7:$I$17,L3,1)))*24*60,) →E3:E18フィルコピー
G3 : 入力（残業時間・昼：0〜3の範囲内で0.5刻み）
×H3 =G3*2+1
×I3 =H3+0.5
×H5 =HLOOKUP(H3,Sheet2!B1:O30,30,FALSE)
×H7 =IF($G7>$H$5,"",SMALL(INDEX(Sheet2!$B$18:$O$29,,H$3*2-1),$G7)) →H7:I17フィルコピー
J2 =D2
J3 =D3+(D3<"8:30"*1) →J3:J18フィルコピー
K3 =MATCH($J2,$I$6:$I$17,1) →K3:K18フィルコピー
L3 =MATCH($J3,$H$7:$H$17,1) →L3:L18フィルコピー
↓修正後
H3 : 入力（1固定）
I3 : 入力（2固定）
G4 : 入力（残業時間・夜：0〜1.5の範囲内で0.5刻み）
H5 =Sheet2!B30
H7 =IF($G7>$H$5,"",SMALL(INDEX(Sheet2!$B$18:$C$29,,H$3),$G7)) →H7:I17フィルコピー
↓2/10 10:39追記
E3 =ROUND((SUMPRODUCT((("0"&$I$7:$I$17)-("0"&$H$7:$H$17))*($G$7:$G$17>=K3)*($G$7:$G$17<=L3))
-MAX(0,J2-INDEX($H$7:$I$17,K3,1))-MAX(0,INDEX($H$7:$I$17,L3,2)-J3))*24*60,) →E3:E18フィルコピー
（連投で失礼します） ごめんなさい、数式修正のご連絡です。
○変更箇所 E3:E18 ○変更内容 上の回答に追記 ○変更理由 以下のとうり ・ピッチ計算をしているE列の式ですが、最初の回答時に、 ※サンプルデータでは、2行以上ずれていないので、E列の数式はその前提で組んであります。 （行というのは、実働時間テーブル上で、開始時間または終了時間の含まれる行のことです） との注釈を入れていました。 ・しかし、今回の変更作業中に、昼の残業時間を3にすると、以下のように8便のところが、 開始4、終了6となって、2行ずれることに気が付きました。 なので、ピッチ計算結果もまちがっていて、誤：140→正：205 です。
[R/C] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [1] 便 荷役開始 〜 荷役終了 ピッチ _ 【稼働体制】 _ _ 終了換算 _ _ [2] 1 7:20 〜 7:45 不明 _ 残業昼/夜 開始 終了 7:45 開始 終了 [9] 8 20:05 〜 20:30 140 _ 3 13:30 15:20 20:30 4 6 ・SUMPRODUCT関数を使って、開始〜終了行（4〜6）の実働時間を合計しておいて、荷役開始（または 荷役終了）と実働時間開始（または終了）との時間差を控除するようにしました。 （コタ）
こんにちは。いつもお世話になっております。
コタさん丁寧なご説明ありがとうございました。 本日ようやく完成しました。仕事が夜勤週だったためなかなかパソコンを触れずお返事が遅くなってしまいました。 お礼を言うのが遅くなってしまい申し訳ありませんでした。 本当にありがとうございました。
ひとつ伺いたいところがありましたので、質問させてください。
[R/C] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [1] 便 荷役開始 〜 荷役終了 ピッチ _ 【稼働体制】 _ _ 終了換算 _ _ [2] 1 7:20 〜 7:45 不明 _ 残業昼/夜 開始 終了 7:45 開始 終了 [3] 2 8:30 〜 8:55 25 _ 0 1 2 8:55 1 1
上の2便のピッチなんですが、夜勤定時の時も1.5残業の時も昼勤稼動開始の8:30から数えた「25」のままになってしまいました。 サンプルデータの場合だと通常1.5残業の時は7:45〜8:15までの30分と昼勤稼動開始から数えた25分で合計55分となるようにしたいのですが、どう数式を入れたらよいのかよく分かりませんでした。
どうかよろしくお願いします。 何度もごめんなさい。
（ここあ）
こんにちは。 ＞ 1.5残業の時は7:45〜8:15までの30分と昼勤稼動開始から数えた25分で合計55分 ふーむ、そうなのですか。 ちょっと考えてみますが、その前に3点確認させてください。
1.サンプルで1便となっているのは、＝前日の最終便のような感じですが、そうなのでしょうか。 2.残業時間夜（1.5）の設定は、その日の夜（22:00〜翌8:15）の設定と思うのですが、 1便（＝前日の最終便？）も同じ残業時間夜（1.5）でいいのでしょうか。 3.荷役終了時間について、必ず1便は8:30より前、2便は8:30より後となっているでしょうか。 （例外がある場合は、ご提示ください） （コタ） 11:43修正
1.昼勤の稼動開始から便を数えるというわけではないので、1便は当日の1便目に当たります。 なのでサンプルで言うと週の最終日は17便の5:35の荷役が終了したら、もうトラックは入ってこないといった感じです。
2.そうです。昼勤稼動開始の8:30〜夜勤稼動開始の22:00までに入ってくるトラックが昼勤の残業時間対象で、 夜勤稼動開始22:00〜昼勤稼動開始8:30までに入ってくるトラックが夜勤の残業時間対象です。
3.生産計画によるのですが、生産が多い週はその分、トラックに製品を引取ってもらう回数(便数)も増える為、 必ずしも1便は8:30より前、2便は8:30より後という形になるとはいえないんです。 なのであくまでも計画に左右されるので『こういった状況の場合が1便は8:30より前、2便は8:30より後にはならない』と詳しい状況をはっきり言えないんです…申し訳ありません。
便 荷役開始 〜 荷役終了 1 6:50 〜 7:15 2 7:35 〜 8:00 3 8:15 〜 8:40 ・ ・ ・
過去にはこういったダイヤの週もありました。 このときは忙しく昼夜残業MAXの29便体制でした。
（ここあ）
もうちょっと確認させてください。
4.1枚のシートには、1日分の便のみ設定されるのですよね。 （一番最初のご質問に、「実際は２０便近くあるので時間がかかってしまって」とありますので、 1日1シートだと思いますが、念のため） 5.荷役終了時間について再度確認ですが、1便の開始時間は、早くとも0:00以降となっているでしょうか。 （確認というより、この前提でないと、もう数式では対応しきれない。。）
上の4.5.がともにYesであれば、けっこう大掛かりな修正にはなりますが、対応できると思います。 （コタ）
＞ けっこう大掛かりな修正にはなりますが、対応できると思います。
わー、すいません(>_<)!!
(ここあ)
できましたー（たぶん）。 Sheet1,2とも修正したので、レイアウト含めて全て再掲しました。 もう一度新規ブックに貼り付けるのがいいかもです。 （コタ）
１．実動時間テーブル 当日の0時から翌日夜勤後までの実働テーブルを作るために、18〜21行目に実働朝を追加しました。 ・18行目未満は前と同じ数式です。
【Sheet2】シート ※「_」セルは未入力または"" [R/C] [A] [B] [C] [D] [E] [F] [1] 【実働計算】 _ _ _ _ _ [2] 残業昼/夜 0 1.5 _ [残業 MAX時] [3] 開/終 開始 終了 _ 開始 終了 [4] 昼勤 8:30 17:15 _ _ _ [5] 夜勤 22:00 8:15 _ _ _ [6] _ _ _ _ _ _ [7] 休憩昼1 10:30 10:40 0 10:30 10:40 [8] 休憩昼2 12:20 13:30 0 12:20 13:30 [9] 休憩昼3 15:20 15:30 0 15:20 15:30 [10] 休憩昼4 _ _ 1.5 17:20 17:30 [11] 休憩昼5 _ _ 2.5 18:50 19:00 [12] _ _ _ _ _ _ [13] 休憩夜1 0:00 0:10 0 0:00 0:10 [14] 休憩夜2 2:00 3:10 0 2:00 3:10 [15] 休憩夜3 5:00 5:10 0 5:00 5:10 [16] 休憩夜4 6:50 7:00 1.5 6:50 7:00 [17] _ _ _ _ _ _ [18] 実働朝1 0:10 2:00 _ _ _ [19] 実働朝2 3:10 5:00 _ _ _ [20] 実働朝3 5:10 6:50 _ _ _ [21] 実働朝4 7:00 8:15 _ _ _ [22] _ _ _ _ _ _ [23] 実働昼1 8:30 10:30 _ _ _ [24] 実働昼2 10:40 12:20 _ _ _ [25] 実働昼3 13:30 15:20 _ _ _ [26] 実働昼4 15:30 17:15 _ _ _ [27] 実働昼5 _ _ _ _ _ [28] 実働昼6 _ _ _ _ _ [29] _ _ _ _ _ _ [30] 実働夜1 22:00 24:00 _ _ _ [31] 実働夜2 24:10 26:00 _ _ _ [32] 実働夜3 27:10 29:00 _ _ _ [33] 実働夜4 29:10 30:50 _ _ _ [34] 実働夜5 31:00 32:15 _ _ _ [35] 実働個数 13 13 _ _ _
B2 =Sheet1!G3 C2 =Sheet1!G4 B4 : 入力（8:30固定） C4 ="17:15:"+B2/24 B5 : 入力（22:00固定） C5 ="6:45:"+C2/24 B7 =IF($B$2<$D7,"",E7) →B7:C11フィルコピー B13 =IF($C$2<$D13,"",E13) →B13:C16フィルコピー ↓修正後（2/16） B18 =F13 B19 =IF(C14="","",C14) →B19:B21フィルコピー B23 =B4 B24 =IF(C7="","",C7+(C7<"8:30"*1)) →B24:B28フィルコピー B30 =B5 B31 =IF(C13="","",C13+(C13<"8:30"*1)) →B31:B34フィルコピー B35 =COUNT(B18:B34) →B35:C35フィルコピー C18 =IF(B14="",IF(B18="","",C$5),B14) →C18:C21フィルコピー C23 =IF(B7="",IF(B23="","",C$4+(C$4<"8:30"*1)),B7+(B7<"8:30"*1)) →C23:C28フィルコピー C30 =IF(B13="",IF(B30="","",C$5+(C$5<"8:30"*1)),B13+(B13<"8:30"*1)) →C30:C35フィルコピー
２．ピッチ計算 実動時間テーブルがMAX15行になりました。 ・計算方法はほぼ変わっていませんが、参照範囲が変更になります。
【Sheet1】シート ※「_」セルは未入力または"" [R/C] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [1] 便 荷役開始 〜 荷役終了 ピッチ _ 【稼働体制】 _ _ 終了換算 _ _ [2] 1 7:20 〜 7:45 不明 _ 残業昼/夜 開始 終了 7:45 開始 終了 [3] 2 8:30 〜 8:55 55 _ 0 1 2 8:55 4 5 [4] 3 9:35 〜 10:00 65 _ 1.5 _ _ 10:00 5 5 [5] 4 10:45 〜 11:10 60 _ 【実働時間】 13 _ 11:10 5 6 [6] 5 11:45 〜 12:10 60 _ 0 _ 0 12:10 6 6 [7] 6 13:55 〜 14:20 60 _ 1 0:10 2:00 14:20 6 7 [8] 7 16:05 〜 16:30 120 _ 2 3:10 5:00 16:30 7 8 [9] 8 20:05 〜 20:30 45 _ 3 5:10 6:50 20:30 8 8 [10] 9 21:10 〜 21:35 0 _ 4 7:00 8:15 21:35 9 8 [11] 10 22:15 〜 22:40 40 _ 5 8:30 10:30 22:40 9 9 [12] 11 23:10 〜 23:35 55 _ 6 10:40 12:20 23:35 9 9 [13] 12 0:05 〜 0:30 45 _ 7 13:30 15:20 24:30 9 10 [14] 13 1:00 〜 1:25 55 _ 8 15:30 17:15 25:25 10 10 [15] 14 2:55 〜 3:20 45 _ 9 22:00 24:00 27:20 10 11 [16] 15 3:50 〜 4:15 55 _ 10 24:10 26:00 28:15 11 11 [17] 16 4:45 〜 5:10 45 _ 11 27:10 29:00 29:10 11 12 [18] 17 5:35 〜 6:00 50 _ 12 29:10 30:50 30:00 12 12 [19] _ _ _ _ _ _ 13 31:00 32:15 _ _ _ [20] _ _ _ _ _ _ 14 _ _ _ _ _ [21] _ _ _ _ _ _ 15 _ _ _ _ _
G3 : 入力（残業時間・昼：0〜3の範囲内で0.5刻み）
G4 : 入力（残業時間・夜：0〜1.5の範囲内で0.5刻み）
H3 : 入力（1固定）
I3 : 入力（2固定）
I6 : 入力（0固定）
↓修正後（2/16 12:08）
H5 =Sheet2!B35
H7 =IF($G7>$H$5,"",SMALL(INDEX(Sheet2!$B$18:$C$34,,H$3),$G7)) →H7:I21フィルコピー
J2 =D2
J3 =D3+OR(D3<D2,J2>"24:00"*1) →J3:J18フィルコピー
K3 =MATCH($J2,$I$6:$I$21,1) →K3:K18フィルコピー
L3 =MATCH($J3,$H$7:$H$21,1) →L3:L18フィルコピー
E3 =ROUND((SUMPRODUCT((("0"&$I$7:$I$21)-("0"&$H$7:$H$21))*($G$7:$G$21>=K3)*($G$7:$G$21<=L3))
-MAX(0,J2-INDEX($H$7:$I$21,K3,1))-MAX(0,INDEX($H$7:$I$21,L3,2)-J3))*24*60,) →E3:E18フィルコピー
ちなみに、最初の3便を忙しかった週の時間にしても、正しく計算されました。
[R/C] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [1] 便 荷役開始 〜 荷役終了 ピッチ _ 【稼働体制】 _ _ 終了換算 _ _ [2] 1 6:50 〜 7:15 不明 _ 残業昼/夜 開始 終了 7:15 開始 終了 [3] 2 7:35 〜 8:00 45 _ 0 1 2 8:00 4 4 [4] 3 8:15 〜 8:40 25 _ 1.5 _ _ 8:40 4 5 [5] 4 10:45 〜 11:10 140 _ 【実働時間】 13 _ 11:10 5 6 （コタ）
こんばんは。
ご報告させていただきます。
コタさんへ 無事にできました(^^) 説明不足でいろいろご迷惑をお掛けしたと思いますが 最後まで付き合っていただき本当にありがとうございました。
大変お世話になりました。
(ここあ)
こんにちは。 コタさん先日はありがとうございました。
すみません少し気になった事がありましたので質問させてください。
【Sheet1】シート
※「_」セルは未入力または"" [R/C] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [1] 便 荷役開始 〜 荷役終了 ピッチ _ 【稼働体制】 _ _ 終了換算 _ _ [2] 1 7:30 〜 7:55 不明 _ 残業昼/夜 開始 終了 7:55 開始 終了 [3] 2 8:35 〜 9:00 30 _ 1 1 2 9:00 4 4 [4] 3 9:45 〜 10:10 70 _ 0.5 _ _ 10:10 4 4 [5] 4 10:45 〜 11:10 50 _ 【実働時間】 11 _ 11:10 4 5 [6] 5 11:55 〜 12:20 70 _ 0 _ 0 12:20 5 5 [7] 6 13:55 〜 14:20 50 _ 1 0:10 2:00 14:20 5 6 [8] 7 15:05 〜 15:30 60 _ 2 3:10 5:00 15:30 6 7 [9] 8 16:05 〜 16:30 60 _ 3 5:10 7:15 16:30 7 7 [10] 9 17:15 〜 17:40 70 _ 4 8:30 10:30 17:40 7 7 [11] 10 18:20 〜 18:45 35 _ 5 10:40 12:20 18:45 7 7 [12] 11 20:05 〜 20:30 0 _ 6 13:30 15:20 20:30 8 7 [13] 12 21:10 〜 21:35 0 _ 7 15:30 18:15 21:35 8 7 [14] 13 22:05 〜 22:30 30 _ 8 22:00 24:00 22:30 8 8 [15] 14 23:10 〜 23:35 65 _ 9 24:10 26:00 23:35 8 8 [16] 15 0:05 〜 0:30 45 _ 10 27:10 29:00 24:30 8 9 [17] 16 1:00 〜 1:25 55 _ 11 29:10 31:15 25:25 9 9 [18] 17 2:55 〜 3:20 45 _ 12 _ _ 27:20 9 10 [19] 18 3:50 〜 4:15 55 _ 13 _ _ 28:15 10 10 [20] 19 6:50 〜 7:15 170 _ 14 _ _ 31:15 10 11 [21] 20 7:25 〜 7:50 #VALUE! _ 15 _ _ 31:50 12 11
試しに過去のダイヤで作ってみたりしていたのですが、上のダイヤの時に20便のピッチが#VALUE!と表示されてしまいました。 どういった状況になると#VALUE!と表示されてしまうのでしょうか？
何度も本当にすみません。 よろしくお願いします。
（ここあ）
こんにちは。 ＞ どういった状況になると#VALUE!と表示されてしまうのでしょうか あー、そんなケースがありましたか。
[R/C] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [1] 便 荷役開始 〜 荷役終了 ピッチ _ 【稼働体制】 _ _ 終了換算 _ _ [2] 1 7:30 〜 7:55 不明 _ 残業昼/夜 開始 終了 7:55 開始 終了 [20] 19 6:50 〜 7:15 170 _ 14 _ _ 31:15 10 11 [21] 20 7:25 〜 7:50 #VALUE! _ 15 _ _ 31:50 12 11
直接の原因は、19便（20行目）の荷役終了7:15（=31:15）が、実働時間テーブル上で12行目（K21）になっているためです。 （このとき、E21セル数式中、MAX(0,J20-INDEX($H$7:$I$21,K21,1))の中の部分が、 J20-INDEX($H$7:$I$21,K21,1)→ 31:15-INDEX($H$7:$I$21,12,1)→ 31:15-""（長さ0文字列） となって、数値-文字列が計算できないため、#VALUE!となるのです。）
つまり、テーブル上の最終時間31:50以降の荷役終了時間を、想定していなかったためのミスでした。m(_ _)m 対応方法としては、E列の数式を、IF(K3>L3,0,〜)のようにIF関数で分岐するのが簡単かと思います。
【Sheet1】シート ※「_」セルは未入力または"" [R/C] [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [1] 便 荷役開始 〜 荷役終了 ピッチ _ 【稼働体制】 _ _ 終了換算 _ _ [2] 1 7:30 〜 7:55 不明 _ 残業昼/夜 開始 終了 7:55 開始 終了 [3] 2 8:35 〜 9:00 30 _ 1 1 2 9:00 4 4 [4] 3 9:45 〜 10:10 70 _ 0.5 _ _ 10:10 4 4 [5] 4 10:45 〜 11:10 50 _ 【実働時間】 11 _ 11:10 4 5 [6] 5 11:55 〜 12:20 70 _ 0 _ 0 12:20 5 5 [7] 6 13:55 〜 14:20 50 _ 1 0:10 2:00 14:20 6 6 [8] 7 15:05 〜 15:30 60 _ 2 3:10 5:00 15:30 6 7 [9] 8 16:05 〜 16:30 60 _ 3 5:10 7:15 16:30 7 7 [10] 9 17:15 〜 17:40 70 _ 4 8:30 10:30 17:40 7 7 [11] 10 18:20 〜 18:45 35 _ 5 10:40 12:20 18:45 7 7 [12] 11 20:05 〜 20:30 0 _ 6 13:30 15:20 20:30 8 7 [13] 12 21:10 〜 21:35 0 _ 7 15:30 18:15 21:35 8 7 [14] 13 22:05 〜 22:30 30 _ 8 22:00 24:00 22:30 8 8 [15] 14 23:10 〜 23:35 65 _ 9 24:10 26:00 23:35 8 8 [16] 15 0:05 〜 0:30 45 _ 10 27:10 29:00 24:30 8 9 [17] 16 1:00 〜 1:25 55 _ 11 29:10 31:15 25:25 9 9 [18] 17 2:55 〜 3:20 45 _ 12 _ _ 27:20 9 10 [19] 18 3:50 〜 4:15 55 _ 13 _ _ 28:15 10 10 [20] 19 6:50 〜 7:15 170 _ 14 _ _ 31:15 10 11 [21] 20 7:25 〜 7:50 0 _ 15 _ _ 31:50 12 11
↓2/18 13:08修正後
E3 =IF(K3>L3,0,ROUND((SUMPRODUCT((("0"&$I$7:$I$21)-("0"&$H$7:$H$21))*($G$7:$G$21>=K3)*($G$7:$G$21<=L3))
-MAX(0,J2-INDEX($H$7:$I$21,K3,1))-MAX(0,INDEX($H$7:$I$21,L3,2)-J3))*24*60,)) →E3:E21フィルコピー
（コタ）13:08修正
ありがとうございます！
できました。何度もご迷惑をかけてすみませんでした。
関数はうまく式を作り上げたときのパズルを解いたような感じが好きなんですが、 まだまだ勉強不足ということをものすごく感じました(^^；)
本当にありがとうございました。
(ここあ)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.