[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『日にち＆時間計算』（ごりら）
SHEET 1
A B C D E
数量 効率 開始日時 終了日時 シフト
5000 200 2007/4/1 13:30 ? 1
上記のようなデータを作成しています。 A列が生産する数量、 B列がこの商品の1時間あたりの生産可能数、 Cが開始予定日時、 E列が現在のシフトです。 求めたいのはD列の終了日時です。 シフトは、何パターンかあるのですがとりあえず2パターンとして、 1が8：00から17：00まで、 2が8：00から18：00まで。 昼休みが12：00から12：50、休憩が15：00から15：10までです。 休日はないと仮定しています。 ちなみにシフトは、SHEET 2のA列、B列にそれぞれ、開始時間と終了時間を入力しています。
上記の例であげれば、生産に必要な時間は25時間ですから、2007/4/4 14:30となります。 これを求めたいのですが、初心者なものでただただ原始的にIF関数にて計算していくだけで、 パターン化できません。すいませんが、教えて下さい。
←行頭に半角空白を入れると、書いたとおりの表示になります。これを入れないと、 空白が勝手に詰まって、表が崩れます。
ご指摘有難うございます。修正しておきました。（ごりら） すいません、どなたか上記悩みについて教えて頂けないでしょうか。
レスが付かないようなので、一応考えた方法をUPします。 が、
>上記の例であげれば、生産に必要な時間は25時間ですから、2007/4/4 14:30となります。
結果の1分の違いは、端数処理のためです。考え方の参考にでもして下さい。 ←この行のみ修正。
一般的な関数ですので、ヘルプを参照するなどして、紐解いてください。
また、滅茶苦茶になるので、要所要所(と私が考えてるだけですが)の為に作業列を設けました。
全体のレイアウトは、こんな感じです。
【Sheet1】
A B C D E F G H I J
1 数量 効率 開始日時 終了日時 シフト 初日生産数 終日稼動生産数 終日稼動日数 最終日生産残数 最終日実稼動時間
2 5,000 200 2007/4/1 13:30 2007/4/4 14:31 1 666 1,600 2 1,134 5:41
【Sheet2】
A B C D E F G
1 シフト 開始時間 昼休憩_始 昼休憩_終 休憩_始 休憩_終 終了時間
2 1 8:00 12:00 12:50 15:00 15:10 17:00
3 2 8:00 12:00 12:50 15:00 15:10 18:00
Sheet1の作業列の式は、こんな感じです。区切りのよさげなところで改行してます。 ※数量等に関しては、超えない方がよいと思い、端数を勝手に切り捨てにしました。 F2の式： =ROUNDDOWN((VLOOKUP($E2,Sheet2!$A$2:$G$3,7,0)-MOD(C2,1) -IF(MOD(C2,1)>=VLOOKUP($E2,Sheet2!$A$2:$G$3,6,0),0, VLOOKUP($E2,Sheet2!$A$2:$G$3,6,0)-MAX(VLOOKUP($E2,Sheet2!$A$2:$G$3,5,0),MOD (C2,1))) -IF(MOD(C2,1)>=VLOOKUP($E2,Sheet2!$A$2:$G$3,4,0),0, VLOOKUP($E2,Sheet2!$A$2:$G$3,40)-MAX(VLOOKUP($E2,Sheet2!$A$2:$G$3,3,0),MOD(C2,1))))*24*$B2,0) G2の式： =ROUNDDOWN((VLOOKUP($E2,Sheet2!$A$2:$G$3,7,0)-VLOOKUP($E2,Sheet2!$A$2:$G$3,2,0) -(VLOOKUP($E2,Sheet2!$A$2:$G$3,6,0)-VLOOKUP($E2,Sheet2!$A$2:$G$3,5,0)) -(VLOOKUP($E2,Sheet2!$A$2:$G$3,4,0)-VLOOKUP($E2,Sheet2!$A$2:$G$3,3,0)))*24*$B2,0) H2の式： =INT(($A2-$F2)/$G2) I2の式： =$A2-$G2*$H2-$F2 J2の式： =CEILING(($I2/$B2)/24,--"0:01")
最終的には、上記の結果等を元にして↓こんな感じ。作業列なしでは、私にゃ無理。。。 D2の式： =DATE(YEAR(C2),MONTH(C2),DAY(C2)+H2) +IF($I2>0,1+VLOOKUP($E2,Sheet2!$A$2:$G$3,2,0) +IF(VLOOKUP($E2,Sheet2!$A$2:$G$3,3,0)>=VLOOKUP($E2,Sheet2!$A$2:$G$3,2,0) +$J2,$J2, IF(VLOOKUP($E2,Sheet2!$A$2:$G$3,5,0)>=VLOOKUP($E2,Sheet2!$A$2:$G$3,2,0)+$J2 +(VLOOKUP($E2,Sheet2!$A$2:$G$3,4,0)-VLOOKUP($E2,Sheet2!$A$2:$G$3,3,0)), $J2+(VLOOKUP($E2,Sheet2!$A$2:$G$3,4,0)-VLOOKUP($E2,Sheet2!$A$2:$G$3,3,0)), $J2+(VLOOKUP($E2,Sheet2!$A$2:$G$3,6,0)-VLOOKUP($E2,Sheet2!$A$2:$G$3,5,0)) +(VLOOKUP($E2,Sheet2!$A$2:$G$3,4,0)-VLOOKUP($E2,Sheet2!$A$2:$G$3,3,0)))), VLOOKUP($E2,Sheet2!$A$2:$G$3,7,0))
あーしんど。以上です。 尚、一つの例でしか試していませんので、間違い等ありましたらご了承下さい。 また、条件追加には対応しません。 言ってみれば、書きっぱなしの、書き逃げ になります。（笑） (sin)
>>上記の例であげれば、生産に必要な時間は25時間ですから、2007/4/4 14:30となります。 >ここで、既に違っているのであてには出来ません。考え方の参考にでもして下さい。 と私も最初思ったんですが、実際計算してみると 4/1 13:30～17:00 休憩時間10分除いて実働3:20 4/2 8:00～17:00 休憩時間60分除いて実働8:00 4/3 8:00～17:00 休憩時間60分除いて実働8:00 4/4 8:00～14:30 休憩時間50分除いて実働5:40 で3:20+8:00+8:00+5:40=25:00 となり、合ってるのでは？と。この時点で考えが間違ってるんでしょうか？ 休憩時間を考慮していたら、ちんぷんかぷん？？？ 諦めました。 （素）
思い込みの為勘違いしてましたので、上レスのコメント変更しました。 ※計算結果の時間が 14:41だと思い込んでました。失礼しました。。。 素さん、悩ませてゴメンナサイ。 (sin)
>素さん、悩ませてゴメンナサイ。 いえ、そんなつもりでは無いんです。
恥ずかしながら途中まで作成したものを･･･
よく分からないんで開始の日と時間を別セルにしました。
最終での休憩時間がどうしても考慮できなかったんで
終了時間がおかしいです。
sheet1の開始日，開始時間，シフトを入力してもらえれば
嘘っぽいのが出来るはずです。
私も書き逃げです･･･
【sheet1】
[A] [B] [C] [D] [E] [F] [G]
[1] 数量 効率 開始日 開始時間 終了日 終了時間 シフト
[2] 5000 200 4/1 13:30 *1 *2 *3
【sheet2】
[A] [B] [C] [D] [E] [F]
[1] 実働時間 シフト 実働日数 余り時間 終了日 終了時間
[2] *4 1 *5 *6 *7 *8
[3] 2 *9 *10 *11 *12
[4]
[5] 開始時間 終了時間 実働時間
[6] 8:00 17:00 8:00
[7] 8:00 18:00 9:00
*1=VLOOKUP(G2,Sheet2!B2:F3,4) *2=VLOOKUP(G2,Sheet2!B2:F3,5) *3= 1 or 2 を入力 *4=Sheet1!A2/Sheet1!B2/24 *5=ROUNDDOWN(A2/Sheet2!C6,0) *6=MOD(A2,Sheet2!C6) *7=IF(Sheet1!D2+D2>17/24,Sheet1!C2+C2+1,Sheet1!C2+C2) *8=IF(Sheet1!D2+D2>Sheet2!B6,Sheet2!A6+(Sheet1!D2+D2-Sheet2!B6),Sheet1!D2+D2) *9=ROUNDDOWN(A2/Sheet2!C7,0) *10=MOD(A2,Sheet2!C7) *11=IF(Sheet1!D2+D3>18/24,Sheet1!C2+C3+1,Sheet1!C2+C3) *12=IF(Sheet1!D2+D3>Sheet2!B7,Sheet2!A7+(Sheet1!D2+D3-Sheet2!B7),Sheet1!D2+D3) （素）
<追記> 文章でフォントをいじるの（太字など）は、どうするんですか？
>文章でフォントをいじるの（太字など）は、どうするんですか？ 記憶を頼りに探し出してきました。 [[20060325102607]] 『ソートすると空白が先に』（苦学生） このスレの下の方を見て下さい。 いや、全部読んでも構いませんけど・・・
Link先に行って、一読後に上方にある【返信・編集】をポチッと押して、編集画面で見るとよく分ります。 尚、プレビューを見る分には構いませんが、 決して編集したまま、
ページの更新ボタンを 押さないように！！
注意してくださいまし。 >>> 改ざん犯人として、アク禁となります。(笑） (sin)
こんなスレもありました～。(ROUGE) [[20060810165303]]『エクセルのことではないのですが、、』（ミリタリー）
できましたぁ～！ ありがとうございまする～！ （素）
関数では面倒な計算のようでしたので、マクロを書いてみました。 For～Next の途中で抜け出すという、何とも締まらないものですが・・・ 30日制限付きです(-_-;) 一応、下記の結果になりました。 (Hatch) 数量 効率 開始日時 終了日時 シフト 5000 200 2007/4/1 13:30 2007/4/4 14:30 1 5000 200 2007/4/1 13:30 2007/4/4 10:40 2
Function keisan(r As Range)
Dim myTime, sTime, kTime, rTime
Dim i As Integer, j As Integer
Dim dCnt, ans
myTime = Round(TimeValue(r.Value) * 1440, 0)
rTime = (r.Offset(0, -2).Value / r.Offset(0, -1).Value) * 60
Select Case r.Offset(0, 2).Value
Case 1
sTime = Array(480, 720, 770, 900, 910, 1020)
Case 2
sTime = Array(480, 720, 770, 900, 910, 1080)
End Select
For j = 0 To 30
For i = LBound(sTime) To UBound(sTime) Step 2
If sTime(i) <= myTime And myTime <= sTime(i + 1) Then
kTime = kTime + sTime(i + 1) - myTime
If kTime >= rTime Then
ans = (sTime(i + 1) - (kTime - rTime)) / 1440
ans = Int(r.Value) + dCnt + ans
keisan = ans
Exit Function
End If
If i + 2 > UBound(sTime) Then
myTime = sTime(0)
Else
myTime = sTime(i + 2)
End If
End If
Next i
dCnt = dCnt + 1
Next j
End Function
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.