/thread-75 1 186- 1- 1 . html
[Original] Excel Automatic Production Scheduling (Softening Ⅱ)? [copy link]?
[Note: Original works, please do not reprint]
It has the function of "excel Production Plan 4- Flexible Scheduling", which is more constructive than it. Multi-line integration of ideas: more concise, more intuitive, more flexible and more rigorous!
What did Zhirou do?
◎ It has the function of "Excel Production Scheduling 4- Flexible Scheduling"
◎ Fault-tolerant processing: do not enter the order data line, and arrange the error value of the regional formula for fault-tolerant processing.
◎ Simplified as a flexible formula structure, one formula integrates multiple lines to schedule production at the same time.
◎ Three fine-tuning items were added.
1. Line classification (automatic adjustment of line classification and scheduling);
2. The first task start time (associated with the planned line time);
3. Daily planned working hours (automatically adjust the scheduling according to the planned working hours and production capacity; Can be adjusted at the same time, can also be adjusted separately)
◎ Integrating multi-line load rate can make the overall load situation more intuitive.
◎ Select the planned quantity, and the load working time of P6 will be automatically displayed.
◎ Column A-I is the data input area; 5 Fine-tuning items (highlighted); Everything else is automatically generated data (don't move).
How to automatically generate production schedule with EXCEL?
1. Create a new excel workbook and rename it "Production Statistics Report". Open the workbook and rename sheet 1 as "output record". Sheet2 was renamed "Output Report".
2. In the cell A 1 of the "Output Records" worksheet, enter the following field names: date, production line (machine number/team), product code, product name, specifications, unit and quantity, and enter some output records.
3. Add the "Report Week" field in column I of the "Output Record" worksheet, and enter the formula in cell I2: =if(len(A2)>0, weeknum (A2)-weeknum (Year (A2)&; “-”& amp； Month (A2) and "-"&; 1)+ 1,"")
4. Add a field "Report Month" in the J column of the Output Record worksheet. Enter the formula in J2 cell: = if (len (A2) > 0, month (A2), "").
5. Perspective the data in the Output Record worksheet: Insert-Insert Pivot Table-Change the data area to Output Record in the pop-up perspective option box! $ a: $ j "—Select the location where the pivot table is placed as the existing worksheet, put the cursor in the Location box, and click A4 cell of Output Report—OK.
6. Pull report month into the area field, report week, production line, product code, product name, specification and model, and unit into the data area. Line field, and summarize as needed.
7. Now this output report is ready. When viewing and filtering data, open the arrow under the triangle, leave the required data in it, check the unwanted data and click OK.
After the template is completed, enter data in the "Output Record" worksheet every day, and refresh the pivot table when viewing the report.