On the last day of 2023, I received the demand for a factory's production plan, and the material arrears of their factory are very difficult to calculate, because the factory is relatively small and there is no informatization, so the material requirements and material arrears are manual calculations.
Requirements: Table 1 is some products that need to be assembled, there is an assembly sequence, and now it is necessary to calculate the actual number that can be assembled according to the parts of these assembled products (Table 2 bill of materials) and the corresponding inventory of the parts (Table 3), if not, what are the names of the parts gap that are assembled?What is the number of gaps in the assembly parts?
Because many parts in the assembly use the same parts, the actual assembly quantity needs to be considered to be reflected in the corresponding part assembly quantity in order. For example, if you use 500 parts in assembly sequence 1, but after assembling 2, you may not be able to use 400 parts in actual parts, and you can only assemble 300 parts with a gap of 100 parts. It can be reflected in the name of the gap and the corresponding number of gaps.
Therefore, the popular point of demand is to tell the production planWhat can be produced, what cannot be produced, what cannot be produced, what is owed?How much do you owe?
Figure 1 Table 2 is the bill of materials of these assembled products, it is also very painful to see such a bill of materials (BOM), which is also some characteristics of small and medium-sized factories, the basic form is not standardized, and it is difficult to do data analysis and sorting in the future.
Figure 2 and Table 3 show the inventory of parts, which is a standard one-dimensional data sheet.
Figure 3 Table 2 is the bill of materials of the part, that is, the BOM table, if you want the production schedule of Table 1 and the bill of materials of Table 2 to be linked, the current BOM table cannot be linked, so the existing BOM table needs to be organized. Organize into a standard one-dimensional table.
Insert the auxiliary column, name it the assembly name, and enter the function:
b3=if(c3="","",if(c2="Part name", d2, b2)), pull-down padding.
Function Interpretation: The purpose of this step is to place the assembly name on the left side of the BOM table to form a linkage with Table 1 and bring out the order demand.
If cell C3 is empty, a null value () is returned""）。
If the value of cell C2 is equal to"Part name", D2:38281040190 is returned.
If the value of cell c2 is not equal to"Part name", b2:"The name of the assembly"。
In this way, according to the principle of relative reference, the drop-down fill fills the assembly name to the right side of the part with one click.
According to this assembly, reference the scheduling order in Table 1 and enter the function:
if(b3="","", xlookup(b3, plan to be released!B:B, Plan Issued!a:a,0))
Then refer to the inventory by parts:
if(b3="","", xlookup(d3, stock quantity!c:c, stock quantity!d:d,0))
This way the BOM is organized.
The final result is shown in Figure 4 below
Fig. 4 In the calculation of the undermaterial, it is necessary to sort out the data of the bill of materials of the parts corresponding to the assembly name in Table 2 for a second time, that is, two constraints:
Constraint 1: The parts are put together as they are.
Constraint 2: In the same part **, the order is ascending according to the scheduling order, that is, the assembly that is ranked first is preferentially deducted as mentioned in the above requirement.
Enter the function according to these two constraints:
sort(sort(filter(b3:f5000,(b3:b5000<>"")*(d3:d5000<>"-")),5),3)
Function Definition: Filter the data that column b is not empty and column d is not equal to the symbol "-", the result displayed is columns b to f, and then sort according to this result, the first sorting is column 5, the sorting method is ascending, that is, the scheduling order, and the second sorting is column 3, that is, the part, and the sorting method is also ascending. Two sorts are equivalent to the two constraints above. The effect is shown in Figure 5 below
The above formula in Figure 5 is a large dynamic data, in order to facilitate the subsequent secondary writing function, cooperate with choosecols to divide it into a single-column dynamic array, and enter the parameters from 1 to 5 and 5 formulas in turn
choosecols(sort(sort(filter(b3:f5000,(b3:b5000<>"")*(d3:d5000<>"-")),5),3),1)
choosecols(sort(sort(filter(b3:f5000,(b3:b5000<>"")*(d3:d5000<>"-")),5),3),2)
The big logic of judging the undersupply is to subtract the demand from the inventory, and consider the deduction order while subtracting the demand, such as inventory 400, the first demand is 700, the operation is -300, the second demand is 300, the cumulative underpayment is -600, and the single demand is -300. Enter the following functions separately:
Order Requirements: xlookup(i3, scheduled to be released!B4: B12, plan issued!c4:c12), the number of assemblies that need to be assembled.
Cumulative demand: sumifs($m$3:m3,$k$3:k3,k3),This is the cumulative demand corresponding to the part, if there are generic parts: A and B orders share C parts, C corresponds to A's demand is 200, B's demand is 300, and the cumulative demand is 500.
Cumulative material shortage: IF(L3-N3>0,0,L3-N3), send the cumulative demand with inventory, greater than 0, displayed as 0;
Single sheet undermaterial: if (abs(O3) can be assembled:
if(countifs($p$3:$p$60,"<0",$h$3,h3)>0,0,m3), an assembly must have no undermaterial, that is, there is no negative undermaterial, in order to assemble, if there is no undermaterial, return m3, m3 is the assembly demand. The final result is shown in Figure 6 below
Figure 6 Table 1 is the main table of production planning and scheduling, and the information of the outstanding material calculated according to the bill of materials can be used on this table.
The actual number of units that can be assembled.
xlookup(b4:b12, parts assembly table!i:i, parts assembly table!q:q)
Assembly Part Notch Name:
textjoin(",",,filter(Parts Assembly Table!$k$3 , (Parts Assembly Table!.)$i$3 = b4)*(Parts Assembly Table!$p$3:$p$60<0)))
Number of assembly part gaps:
textjoin(",",,filter(Parts Assembly Table!$p $3: $p $60, (Parts Assembly Table!)$i$3 = b4)*(Parts Assembly Table!$p$3:$p$60<0)))
When completed, it is shown in Figure 7 below
Figure 7 has calculated the corresponding undermaterial information of the scheduling parts in Table 1 to the above step, and the core knowledge points of this demand are:
Knowledge point 1: Filter the relative citation method of IF and convert irregular data into standard one-dimensional data
Knowledge point 2: Use the filter function to empty the data containing blank rows and special symbols.
Knowledge point 3: Use the sorting function to sort the same type of data together.
Knowledge point 4: Use the accumulated inventory minus the cumulative demand to determine whether the material is underpaid.
Knowledge point 5: Use statistical functions to count negative numbers to judge whether the materials are complete, so as to judge the assembly data that can be produced;
Knowledge point 6: Use the filter function plus merge function to refer the name and quantity of the undermaterial parts to Table 1
I'm Brother Gu:
He has been engaged in the manufacturing industry for 20 years, and has rich practical experience in enterprise operation, chain management, and intelligent manufacturing systems. Expert in enterprise intelligence and flexible plan operation management, good at improving enterprise efficiency through enterprise process optimization and standardization, enterprise management, and introduction of plan operationHe has rich experience in improving the on-time delivery rate of enterprises, reducing enterprise inventory, and exporting intelligent manufacturing talents. Learn the PMC production plan and pay attention to the Guge plan!