生产管理排产

人生の取引

本人小白一枚，请老师帮我解决一下各个分表的起始日期和结束日期的逻辑计算，谢谢


补充内容 (2024-11-19 12:27):
最新的附件详见12楼，请好心的老师帮我完善一下吧

人生の取引

自己顶一下

人生の取引

我再顶一下

人生の取引

顶4

人生の取引

完蛋啦，这是没人会做了鸭，还是我没有描述清楚哦

13825719951

我觉得你没表达清楚. 另外, 我觉得做排产不要管理工序, 只要管理工数, 安排工序的事情交给班长去做. 看看我做的周计划排程: https://club.excelhome.net/thread-1644060-1-1.html

人生の取引

1. 
   
2. Sub ProductionSchedulingRevised()
   
3. '项目占位计划B版2024-10-22-3，虽然能进行排产，我不知道怎么写设备占位的代码。请哪位老师帮忙添加一下吧，谢谢了
   
4. 但没有把设备占位考虑进来，未达到我的使用要求。
   
5. Dim lastRow As Long
   
6. Dim ws As Worksheet
   
7. Set ws = ThisWorkbook.Sheets("排产清单")
   
8. lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
   
9. Dim orderNumbers As Object
   
10. Set orderNumbers = CreateObject("Scripting.Dictionary")
    
11. Dim i As Long
    
12. For i = 2 To lastRow
    
13. Dim orderNumber As String
    
14. orderNumber = ws.Cells(i, 1).Value
    
15. If Not orderNumbers.Exists(orderNumber) Then
    
16. orderNumbers.Add orderNumber, i
    
17. End If
    
18. Next i
    
19. Dim currentDate As Date
    
20. currentDate = Date
    
21. Dim equipmentUsage As Object
    
22. Set equipmentUsage = CreateObject("Scripting.Dictionary")
    
23. Dim processedRows As Object
    
24. Set processedRows = CreateObject("Scripting.Dictionary")
    
25. For Each orderKey In orderNumbers.Keys
    
26. Dim firstProcessRow As Long
    
27. firstProcessRow = orderNumbers(orderKey)
    
28. If Not processedRows.Exists(firstProcessRow) Then
    
29. ws.Cells(firstProcessRow, 10).Value = currentDate
    
30. Dim duration As Long
    
31. duration = ws.Cells(firstProcessRow, 11).Value
    
32. ws.Cells(firstProcessRow, 12).Value = currentDate + duration
    
33. processedRows.Add firstProcessRow, True
    
34. End If
    
35. Dim currentRow As Long
    
36. currentRow = firstProcessRow
    
37. Do While True
    
38. Dim nextProcessRow As Long
    
39. nextProcessRow = FindNextProcessRow(ws, currentRow)
    
40. If nextProcessRow = 0 Then Exit Do
    
41. Dim equipment As String
    
42. equipment = ws.Cells(nextProcessRow, 9).Value
    
43. If IsEquipmentAvailableForPriorityRevised(ws, equipment, ws.Cells(nextProcessRow, 10).Value, ws.Cells(nextProcessRow, 13).Value, equipmentUsage) Then
    
44. ws.Cells(nextProcessRow, 10).Value = ws.Cells(currentRow, 12).Value
    
45. duration = ws.Cells(nextProcessRow, 11).Value
    
46. ws.Cells(nextProcessRow, 12).Value = ws.Cells(nextProcessRow, 10).Value + duration
    
47. processedRows.Add nextProcessRow, True
    
48. currentRow = nextProcessRow
    
49. Else
    
50. currentRow = WaitForEquipmentAvailability(ws, currentRow, equipment, equipmentUsage)
    
51. End If
    
52. Loop
    
53. Next orderKey
    
54. End Sub
    
55. Function FindNextProcessRow(ws As Worksheet, startRow As Long) As Long
    
56. Dim i As Long
    
57. For i = startRow + 1 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
58. If ws.Cells(i, 1).Value = ws.Cells(startRow, 1).Value And ws.Cells(i, 6).Value = ws.Cells(startRow, 6).Value + 10 Then
    
59. FindNextProcessRow = i
    
60. Exit Function
    
61. End If
    
62. Next i
    
63. FindNextProcessRow = 0
    
64. End Function
    
65. Function IsEquipmentAvailableForPriorityRevised(ws As Worksheet, equipment As String, startDate As Date, priority As Long, equipmentUsage As Object) As Boolean
    
66. If equipmentUsage.Exists(equipment) Then
    
67. Dim lastUsedDate As Date
    
68. lastUsedDate = equipmentUsage(equipment)
    
69. Dim i As Long
    
70. For i = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
71. If ws.Cells(i, 9).Value = equipment And ws.Cells(i, 10).Value > startDate And ws.Cells(i, 13).Value <= priority Then
    
72. If ws.Cells(i, 10).Value < lastUsedDate Then
    
73. IsEquipmentAvailableForPriorityRevised = False
    
74. Else
    
75. IsEquipmentAvailableForPriorityRevised = True
    
76. End If
    
77. Exit Function
    
78. End If
    
79. Next i
    
80. Else
    
81. IsEquipmentAvailableForPriorityRevised = True
    
82. End If
    
83. End Function
    
84. Function WaitForEquipmentAvailability(ws As Worksheet, currentRow As Long, equipment As String, equipmentUsage As Object) As Long
    
85. Dim endDate As Date
    
86. endDate = ws.Cells(currentRow, 12).Value
    
87. Dim nextAvailableRow As Long
    
88. Do
    
89. nextAvailableRow = FindNextAvailableTimeForPriorityRevised(ws, currentRow, equipment)
    
90. If nextAvailableRow = 0 Then
    
91. ' Wait until equipment is available
    
92. DoEvents
    
93. Else
    
94. Dim newStartDate As Date
    
95. newStartDate = ws.Cells(nextAvailableRow, 10).Value
    
96. If IsEquipmentAvailableForPriorityRevised(ws, equipment, newStartDate, ws.Cells(nextAvailableRow, 13).Value, equipmentUsage) Then
    
97. Exit Do
    
98. Else
    
99. currentRow = nextAvailableRow
    
100. End If
     
101. End If
     
102. Loop
     
103. WaitForEquipmentAvailability = nextAvailableRow
     
104. End Function
     
105. Function FindNextAvailableTimeForPriorityRevised(ws As Worksheet, currentRow As Long, equipment As String) As Long
     
106. Dim endDate As Date
     
107. endDate = ws.Cells(currentRow, 12).Value
     
108. Dim i As Long
     
109. For i = currentRow + 1 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
     
110. If ws.Cells(i, 9).Value = equipment And ws.Cells(i, 9).Value > endDate Then
     
111. FindNextAvailableTimeForPriorityRevised = i
     
112. Exit Function
     
113. End If
     
114. Next i
     
115. FindNextAvailableTimeForPriorityRevised = currentRow
     
116. End Function
     

复制代码

人生の取引

13825719951 发表于 2024-10-24 14:51
我觉得你没表达清楚. 另外, 我觉得做排产不要管理工序, 只要管理工数, 安排工序的事情交给班长去做. 看看我 ...

因为要生产的件太多了，而且每个件的工序必需要一步一步的干，我们要做全盘的规划，如果要一个工序一个工序去抠设备的占位的话，根本就搞不完，而且手工排产出来的还有可能会有错误，所以我就想把数据往表里面一丢，全由系统去进行排产

13825719951

人生の取引 发表于 2024-10-24 15:07
因为要生产的件太多了，而且每个件的工序必需要一步一步的干，我们要做全盘的规划，如果要一个工序一个工 ...

好吧, 我再看一下你的表各.

人生の取引

13825719951 发表于 2024-10-24 15:12
好吧, 我再看一下你的表各.

好的，谢谢你，我刚刚也看了一下您发链接，不适用我们重型行业的生产模式，我们这个行业是单件多工序，无法使用流水线作业方式