There are already 4 questions, and the topic of question 5 is: analyze the number of tasks in the task schedule, and analyze the proportion of each range segment? This question is an open-ended question, there is no standard answer, only a reference answer.
The ability examined in this topic is the basic analytical ability of a production planner. In general, the quantity analysis of production tasks in the production task schedule can be used to analyze the order structure. The analysis of the order structure can see the structure of the order quantity, mainly looking at the proportion of small orders, medium orders, and large orders.
The definition of small order, medium order, and large order needs to be defined according to the actual situation of the factory. Different products have different minimum quantities or minimum purchase quantities, and the purpose of analysis is to balance the needs of all parties.
Again, the number of production tasks is analyzed against the following basic data.
Knowledge points of this question
Test Center:
1. The use of the VLOOKUP function fuzzy lookup
2. THE USE OF COUNTIFS MULTI-CONDITION FUNCTIONS
3. Textjoin text merge function use
4. Use of IF logical judgment function;
5. Rank function app
Difficulty: ★★★★
Answer: See the steps below
Define the range of quantities
The question requires the proportion of each range segment to be analyzed for the number of tasks in the production task? So the first step is to define the range segment. If you don't know how to define a range of quantities, let's start with a generic definition:
Rule 1: less than 100, corresponding to 0 to 99;
Rule 2: Less than 500, corresponding to 100 to 499
Rule 3: Less than 1000, corresponding to 500 to 999
Rule 4: Less than 1500, corresponding to 1000 to 1499
……
Rule 7: If it is greater than 5000, it corresponds to 5000 and above;
The range of quantities is dynamically changing, so in order to make the next time the quantity changes, the range corresponds to the change, we need to use formulas to define the range;
The formula defines a range of quantities
First enter the function of cell merge:
=TEXTJOIN("-",,H3,H4-1), the purpose of this function is to connect the first rule with the second rule by the symbol "-", and the purpose of H4-1 is to make the exact reference range in the subsequent function reference. Fill down to get the following figure:
At this time, the linkage between quantity and range is formed, and when the quantity changes, the range is also changed synchronously, which has more processing solutions for subsequent analysis of different quantity rules. Another problem with this formula is that when the formula is filled to the last cell, the details are wrong, as shown in the figure above, the rule of 5000 should correspond to 5000 and 5000 or more.
At this point, change the formula to:
=IF(H4="",H3&"above",TEXTJOIN("-",,H3,H4-1))
Formula interpretation: When 5000 is empty, use 5000 to connect Chinese "and above 5000", and the final result is shown in the following figure:
The number of tasks corresponds to the range
With the range of the number of tasks, you can use VLOOKUP or XLOOKUP's fuzzy query to return the corresponding range results, and if there are fewer than 3 conditions, you can also use IFS to judge.
Entry formula:
=VLOOKUP(F3,$I$3:$J$9,2,1)
Function Definition:
The database in the defined range is referenced by the quantity, and the range in column 2 is returned, and if it is not found, find the closest quantity.
This is defined according to the VLOOKUP function:
Approximate match - 1/TRUE assumes that the first column in the table is sorted numerically or alphabetically, and then searches for the closest value
For example, the number 5, there is no reference range for the corresponding one, and the closest is 0, so the return is 0-99, the same is 1560, and the closest is 1500, so it returns 1500-1999.
Percentage of analyses:
Statistically count the range results just analyzed by VLOOKUP, and enter the function:
=COUNTIFS(G:G,J3), COUNT THE NUMBER OF RANGE BANDS
=K3/SUM(K:K), analyze the proportion of the range segment;
=RANK. AVG (L3, $L$3:$L$9), ranking the percentage
Of course, you can also analyze the proportion of orders in different range segments by changing the quantity range segment, and judge the difficulty of the order result through these.
In general:
The smaller the number of orders, the more switching, the greater the complexity of production. Important attention is required.
The effect is as follows:
I'm Gugo:
Engaged in the manufacturing industry for 18 years, he has rich practical experience in enterprise operation, supply chain management, intelligent manufacturing system and other aspects. Enterprise intelligence, flexible planning and operation management experts, good at improving enterprise efficiency through enterprise process optimization and standardization, enterprise management, and introduction of planned operations; It has rich experience in improving the on-time delivery rate of enterprises, reducing enterprise inventory, and exporting intelligent manufacturing talents. Learn PMC production planning, pay attention to Guge plan!
Welcome to submit questions related to the production plan by private message, and Teacher Gu will update the solution to the public account article.