Please see
/thread-751186-1-1.html
[Original] Excel automatic production scheduling (to soft Ⅱ) ? [Copy link] ?
[Note: original work, please do not reproduce]
Both "excel production planning 4 - to soft scheduling" function, more constructive than its improvement, the idea of integrating multiple lines: more concise and intuitive, more flexible, more structured!
To the soft Ⅱ to do:
◎ both "Excel production schedule 4 to soft scheduling" function
◎ fault-tolerant processing: did not enter the order data line, scheduling area formula error value fault-tolerant processing
◎ simplify to the soft scheduling formula structure, a formula integration of multi-line
Increase three fine-tuning items
1. line (adjust line scheduling automatically adjusted);
2. first task start time (associated with the scheduling column time);
3. daily scheduled hours (according to the scheduled hours of production capacity automatically adjusted scheduling; can be adjusted at the same time, or adjusted by line)
Integration of multiple lines The load rate, can be more intuitive to see the overall load status
◎ Select the number of scheduling automatically in the P6 display load hours
◎ A-I columns of the data input area; 5 fine-tuning items (highlighted); the other automatic generation of data (do not move)
EXCEL automatic production scheduling table how to do it?
1. Create a new excel workbook, renamed "Production Statistics Report". Open the workbook, rename sheet1 as "production records. sheet2 renamed "production report".
2. In the "Production Records" worksheet from cell A1, enter the following field names: date, production line (machine number/shift), product code, product name, specifications, units, quantities, and enter part of the production records.
3. In the "Production Records" worksheet I column to add a field "statement week", in cell I2 and enter the formula: = if (len (A2) & gt; 0, WEEKNUM (A2) - WEEKNUM (), if (A2) & gt; 0, WEEKNUM (A2) - WEEKNUM (A2) - WEEKNUM (A2) - WEEKNUM (). YEAR (A2)& "-"& MONTH (A2)& "-"& 1)+1,""")
4. In the "production records" worksheet J column to add a field "statement month". In cell J2 enter the formula: = IF (LEN (A2) & gt; 0, MONTH (A2), "").
5. In the "yield records" worksheet pivot out: Insert - Insert Pivot Table - in the pop-up pivot options box to change the data area to "yield records! $ A: $ J" - place the location of the pivot table Select the existing worksheet, and the cursor hit the "location" of the box and then click on the "yield statements "A4 cell - OK.
6. Pull the "statement month" into the face of the field, "statement week", "production line", "product code", "product name", "specification type", "product name", "product name", "product name", "product name", "product name", "product name", "product name", "product name", "product name", "product name". , "specifications", "unit" level of seven items pulled into the line field, "quantity" pulled into the data area. Line fields and summarize depending on their needs.
7. Now this yield report has been done, view and filter data, open the triangle under the arrow, you need to leave the data inside, do not need the data in front of the checkmark removed, click OK.
After the template is ready, every day in the "yield record" worksheet inside the data entry, view the report to refresh the pivot table on the line.