[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『日毎の最大稼働数の求め方』（タイム）
はじめまして。過去ログを検索してみたのですが、 似たような内容がないようでしたので書き込みをさせていただきました。 下記のようなデータが１年分あるのですが、 各日において時間内、時間外毎の最大稼働数を表示させる方法を 教えていただけると助かります。 時間内は、07：30～17：00 時間外は、00：00～07：29、17：01～23：59 Excelは、2007です。
列A 列B 列C
機械 作業開始年月日時 作業終了年月日時
A 201004010015 201004011818
B 201004011031 201004020332
C 201004011315 201004011617
A 201004011919 201004020218
D 201004020130 201004020815
B 201004020432 201004021012
: : :
: : :
↓↓
4月1日 時間内3台 時間外2台
4月2日 時間内2台 時間外3台
： ： ：
機械は全部で4台です。 よろしくお願い致します。
A 201004010015 201004011818
2010年4月1日0時15分 ～ 2010年4月1日18時18分
これは「時間内」「時間外」稼動しとるけど どうカウントするん？ （時間内○台、時間外○台）
カウントするルールがよーわからん
(GobGob)
カウントやないんかも？ 最大稼動数ってのはどういう意味？
(GobGob)
早速の回答ありがとうございます。 説明が分かりにくくて申し訳ありません。 最大稼動数というのは、各時間帯で同時に最大で何台稼働したかということです。
4月1日は、 00:00～07:29の間に稼働しているのは、“A”のみなので、1台・・・① 07:30～17:00の間に稼働しているのは、“A”、“B”、“C”の3台で、 13:15～16:17の間は同時に3台とも稼働していたので、3台・・・② 17:01～23:59の間に稼働しているのは、“A”、“B”の2台で、 17:00～18:18、19:19～23:59間は同時に2台稼働、 18:19～19:18の間は“B”のみなので、1台、 ⇒同時に稼働したのは最大で、2台・・・③
時間内は、②の3台 時間外は、①と③の大きい方で、2台
という感じでカウントしたいのですが良い方法はありますか？
(タイム)
４月２日の時間外は4台じゃないの？(2行目の終了で+１されないの？）
一応 D E F G 1 2010 時間内 時間外 2 4月1日 3 2 3 4月2日 2 4
E列から抽出。
E1に年度（数値）、E2から月日（シリアル値）
F2 =COUNTIF(B:C,">="&E$1&TEXT(E2,"mmdd")&"0730")-COUNTIF(B:C,">="&E$1&TEXT(E2,"mmdd")&1700) G2 =MAX(COUNTIF(B:C,">="&E$1&TEXT(E2,"mmdd")&"0000")-COUNTIF(B:C,">"&E$1&TEXT(E2,"mmdd")&"0730"),COUNTIF(B:C,">"&E$1&TEXT(E2,"mmdd")&1700)-COUNTIF(B:C,">="&E$1&TEXT(E2,"mmdd")&2359))
F2:G2 下フィルコピー。
(GobGob)
説明が悪くて何度もすみません。各時間帯における稼働回数ではなく、 「同時刻に最大で何台稼働したか」を表示したいのです。
たとえば、１日分を図にすると、（○は稼働、×は休止） 時間 0000－－－0730－－－1700－－－2359 機械A ××○○○○○○○○○○○○○○○ 機械B ××××○○○○××××××××× 機械C ×××××××○○○××××○○○ 機械D ×××××××○○○○○×××○○ 稼働台数 ００１１２２２４３３２２１１２３３
00:00～07:29及び、17:01～23:59の間では最大３台同時に稼働・・・（時間外） 07:30～17:00の間では最大４台同時に稼働・・・（時間内） ⇒時間内４台、時間外３台
お手数おかけ致しますが、よろしくお願い致します。
(タイム)
では、同時刻って言うのは何分単位なん？ ３０分単位？ 1時間単位？ 8時18分から稼動した場合はどう処理するの？（8:30実績？9:00実績？実績なし？) 16:10分に終了した場合はどう処理するの？
・・・イマイチ理解してないけど
(GobGob)
同時刻というのは、１分でも同じ時間に稼働していればその時の台数としてカウントしたいのです。
1年間で、4台必要だった日数は何％、3台必要だった日数は何％、 2台必要だった日数は何％、1台必要だった日数は何％、必要なかった日数は何％かを 時間内、時間外の時間帯でそれぞれ調べたいのです。
例えば、機械“A”と“B”しか稼働しなかった日で、 “A”と“B”の稼働時間が1分も重複していなければ、その日は1台しか必要なかったということになり、 1分でも重複していれば、その日は2台必要だったということがわかります。
機械の導入・削減の資料に使うもので、同時に4台稼働している日数が少なければ削減、 同時に4台稼働している日数が多ければもう１台導入するという方向性で考えたいと思っております。
よろしくお願い致します。
(タイム)
とりあえず時間内 F2:=MAX(MAX(INDEX((COUNTIF($B$2:$B$4000,"<="&$B$2:$B$4000)-COUNTIF($C$2:$C$4000,"<="&$B$2:$B$4000))*($B$2:$B$4000<(E$1&TEXT($E2,"mmdd")&1700)*1)*($C$2:$C$4000>(E$1&TEXT($E2,"mmdd")&"0730")*1),)),MAX(INDEX((COUNTIF($B$2:$B$4000,"<="&$C$2:$C$4000)-COUNTIF($C$2:$C$4000,"<="&$C$2:$C$4000))*($B$2:$B$4000<(E$1&TEXT($E2,"mmdd")&1700)*1)*($C$2:$C$4000>(E$1&TEXT($E2,"mmdd")&"0730")*1),))) 時間外は 17:00～翌7:30の時間帯で計算してはどうでしょう。計算しやすく、時間管理もしやすくなります。 (NB)
思い付きで書いているので、上手く行かなかったらゴメン 何回もループを掛けているので、多分物すごく遅いと思います データはSheet1のA1行に列見出しが有る物とします 結果の出力は以下のコードではE1からとして有ります
Option Explicit
Public Sub Sample_2()
Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long
Dim lngCount As Long
Dim lngMax As Long
Dim lngRows As Long
Dim rngList As Range
Dim rngResult As Range
Dim vntData As Variant
Dim vntSecS As Variant
Dim vntSecE As Variant
Dim lngDateS As Long
Dim lngDateE As Long
Dim blnMap() As Boolean
Dim strMachine() As String
Dim strProm As String
'Listの先頭セル位置を基準とする（先頭列の列見出しのセル位置）
Set rngList = Worksheets("Sheet1").Range("A1")
'結果出力の先頭セル位置を基準とする（先頭列の列見出しのセル位置）
Set rngResult = Worksheets("Sheet1").Range("E1")
'時間内と時間外の時刻の区間先頭
vntSecS = Array(#12:00:00 AM#, #7:30:00 AM#, #5:01:00 PM#)
vntSecE = Array(#7:29:00 AM#, #5:00:00 PM#, #11:59:00 PM#)
With rngList
'行数の取得
lngRows = .Offset(Rows.Count - .Row).End(xlUp).Row - .Row
If lngRows <= 0 Then
strProm = "データが有りません"
GoTo Wayout
End If
'B、C列データを配列に取得
vntData = .Offset(1).Resize(lngRows, 3).Value
End With
'データをシリアル値に変換
lngDateS = Int(ConvDate(vntData(1, 2)))
k = 0
For i = 1 To lngRows
vntData(i, 2) = ConvDate(vntData(i, 2))
'開始日を取得
If lngDateS > Int(vntData(i, 2)) Then
lngDateS = Int(vntData(i, 2))
End If
vntData(i, 3) = ConvDate(vntData(i, 3))
'終了日を取得
If lngDateE < Int(vntData(i, 3)) Then
lngDateE = Int(vntData(i, 3))
End If
'機械の台数を取得
For j = 1 To k
If strMachine(j) = vntData(i, 1) Then
Exit For
End If
Next j
If j > k Then
k = j
ReDim Preserve strMachine(1 To k)
strMachine(k) = vntData(i, 1)
End If
Next i
'稼働状況配列を確保
ReDim blnMap(1 To UBound(strMachine, 1), _
1 To (lngDateE - lngDateS + 1) * 24 * 60)
'データの先頭～最終迄
For i = 1 To lngRows
'現在のデータがどの機械かを取得
For j = 1 To UBound(strMachine, 1)
If strMachine(j) = vntData(i, 1) Then
k = j
Exit For
End If
Next j
'一分毎の機械の稼働状況をMap
For j = GetPosition(lngDateS, vntData(i, 2)) _
To GetPosition(lngDateS, vntData(i, 3))
blnMap(k, j) = True
Next j
Next i
'結果用配列を確保
ReDim vntData(lngDateS To lngDateE, 1 To 3)
'データの開始日～終了日まで
For i = lngDateS To lngDateE
'結果配列の1列目に日付を代入
vntData(i, 1) = Format(i, "m月d日")
'時間内、時間外の区分時間より
For j = 0 To 2
'最大稼働台数を0に
lngMax = 0
'区分時間の先頭～最終まで
For k = GetPosition(lngDateS, i + vntSecS(j)) _
To GetPosition(lngDateS, i + vntSecE(j))
'稼働台数を初期化
lngCount = 0
'機械の台数分繰り返し
For l = 1 To UBound(strMachine, 1)
'其の時刻に稼働しているなら
If blnMap(l, k) Then
'稼働台数をカウント
lngCount = lngCount + 1
End If
Next l
'最大稼働台数を更新
If lngMax < lngCount Then
lngMax = lngCount
End If
Next k
'時間区分が
Select Case j
Case 1 '時間内なら
vntData(i, 2) = lngMax
Case 0 '早朝残業なら
vntData(i, 3) = lngMax
Case 2 '残業なら
If vntData(i, 3) < lngMax Then
vntData(i, 3) = lngMax
End If
End Select
Next j
Next i
'結果を出力
With rngResult
.Resize(, 3).EntireColumn.ClearContents
.Resize(, 3).Value = Array(Format(lngDateS, "yyyy"), "時間内", "時間外")
.Offset(1).Resize(lngDateE - lngDateS + 1, 3).Value = vntData
End With
strProm = "処理が完了しました"
Wayout:
Set rngList = Nothing
Set rngResult = Nothing
MsgBox strProm, vbInformation
End Sub
Private Function ConvDate(vntDate As Variant) As Double
' 文字列を日付時刻に変換
Dim strDate As String
strDate = "yyyy/mm/dd hh:mm:00"
Mid(strDate, 1, 4) = Left(vntDate, 4)
Mid(strDate, 6, 2) = Mid(vntDate, 5, 2)
Mid(strDate, 9, 2) = Mid(vntDate, 7, 2)
Mid(strDate, 12, 2) = Mid(vntDate, 9, 2)
Mid(strDate, 15, 2) = Mid(vntDate, 11, 2)
ConvDate = CDate(strDate)
End Function
Private Function GetPosition(lngStart As Long, vntDate As Variant) As Long
GetPosition = Int((vntDate - lngStart) * 24 * 60) + 1
End Function
(Bun)
NBさん、Bunさんお返事ありがとうございます。
NBさんへ 時間内はこれでうまくいきました。ありがとうございます。 時間外はこれを参考に考えてみます。
Bunさんへ このような長いプログラムを作っていただいてありがとうございました。 上記内容をそのままコピーして実行してみたら、 「 ConvDate = CDate(strDate) 」の行で、 「実行エラー13、型が一致しません」と出ました。
せっかく作っていただいたのに私がVBAはほとんど分からなくて申し訳ないです。
(タイム)
>上記内容をそのままコピーして実行してみたら、 >「 ConvDate = CDate(strDate) 」の行で、 >「実行エラー13、型が一致しません」と出ました。 > > >せっかく作っていただいたのに私がVBAはほとんど分からなくて申し訳ないです。
変ですね、ヴァージョンの違いかな？ Excel2007迄はこれで動いているのですがね？ 下記の様に直して見て下さい
ConvDate = CDbl(CDate(strDate))
CDbl関数で修飾して見て下さい
PS:Upしたコードは標準モジュールに記述してますよね？
あ！、後
>「 ConvDate = CDate(strDate) 」の行で、 >「実行エラー13、型が一致しません」と出ました。
此れが出るのは、作業開始年月日時、作業終了年月日時が 「201004010015」こう言う形式に成っていず アルファベットやハイフン等が入っているデータが有る場合 起こる様です
(Bun)
一応、データチェックするコードをUpします 前回のコードとは別の標準モジュールに以下を記述して下さい
Option Explicit
Public Sub DataChek()
Dim i As Long
Dim j As Long
Dim lngRows As Long
Dim rngList As Range
Dim vntData As Variant
Dim strProm As String
'Listの先頭セル位置を基準とする（先頭列の列見出しのセル位置）
Set rngList = Worksheets("Sheet1").Range("A1")
With rngList
'行数の取得
lngRows = .Offset(Rows.Count - .Row).End(xlUp).Row - .Row
If lngRows <= 0 Then
strProm = "データが有りません"
GoTo Wayout
End If
'列データを配列に取得
vntData = .Offset(1, 1).Resize(lngRows, 2).Value
'Key列に就いて繰り返し
For i = 1 To lngRows
For j = 1 To 2
'もし、データが「201004010015」無い場合
If Not DateCheck(vntData(i, j)) Then
'BuckColorを変更する
.Offset(i, j).Interior.ColorIndex = 34
End If
Next j
Next i
End With
strProm = "処理が完了しました"
Wayout:
Set rngList = Nothing
MsgBox strProm, vbInformation
End Sub
Private Function DateCheck(vntDate As Variant) As Boolean
' 文字列を日付時刻に変換出来るかを確認
Dim strDate As String
strDate = "yyyy/mm/dd hh:mm:00"
Mid(strDate, 1, 4) = Left(vntDate, 4)
Mid(strDate, 6, 2) = Mid(vntDate, 5, 2)
Mid(strDate, 9, 2) = Mid(vntDate, 7, 2)
Mid(strDate, 12, 2) = Mid(vntDate, 9, 2)
Mid(strDate, 15, 2) = Mid(vntDate, 11, 2)
DateCheck = IsDate(strDate)
End Function
実行すると、日付時刻と認識されないデータのバックがブルーに成ります
(Bun)
Bunさん丁寧な回答ありがとうございます。
早速データチェックを行ってみると、こちらのデータに不備があることがわかりました。 データを修正したら無事出力されました。
おかげでほしいデータを得ることができました。 これを機会にVBAも勉強してみようと思います。 コメントをしていただいた皆様、本当にありがとうございました。
(タイム)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.