The shop floor statistician of a factory needs to perform payroll on the production work orders of the month, and Figure 1 below shows the total amount of the completed quantity of each production task for the employees of their factory and the unit price of the corresponding work order products.
Because some work orders are collectively piecework, that is, completed by multiple people, it is necessary to evenly distribute the amount of these work orders completed by multiple people to each member, because the number of work orders is too large, some work orders are completed by one employee, and some are two, so the statistician is required to manually count one by one, and the workload is relatively large.
Now the demand is to design a functional formula to calculate the employee's salary with one click.
Figure 1: How much is the piece-rate wage of each employee in the source data?The first thing you need to judge is how many employees complete each ticket. There is a certain rule through visual observation, and different employees are separated by the symbol "+", so the number of employees can be judged by counting this "+" sign.
Once you have determined the number of employees, you can divide the amount by the number of employees to get the average amount. Finally, according to the number of employees, the corresponding employees are expanded, and then the conditions can be summarized statistically. One of the better ways to solve this need is to use auxiliary columns. The solution is shown in Figure 2 below
In Figure 2, if a work order is completed by multiple employees, the employees are separated by the symbol "+", so you only need to use textsplitb to separate the specified symbols
Employee Breakdown: =textsplit(f3,"+"), drop-down padding.
Stats Staff: =columns(textsplit(f3,"+"Pull down the padding, so you get the number of employees.
Each employee should be paid: = e3 g3, the total amount of the number of employees.
After completion, the effect is shown in Figure 3 below
Figure 3 If you need to count the amount of money that should be paid by each member separately, you need to summarize, the source data is a two-dimensional table, which is more difficult to count, and it is simple to convert it with auxiliary columns.
Input function: =textsplit(f3,"+"), drop-down padding.
Function definition: F3 employees are sorted under the symbol "+".
The effect is shown in Figure 4 below
Figure 4 Entry function: =expand(h3,,g3,h3), pull-down fill.
Function Definition: Expand the amount, the number of extensions is the number of employees, and the number of fills is the amount.
The effect is shown in Figure 5 below
Figure 5 completes these two steps, which is equivalent to converting both employees and amounts into a 2D table.
The goal of this step is to convert the above auxiliary 2D tables (Employees and Amounts) into a single column, which can be used as sumifs sum and condition regions, respectively.
Enter the function: =tocol(j3:l7,1) to convert the employee's two-dimensional into a column;
Enter the function: =tocol(n3:p7,1) to convert the two dimensions of the amount into a column;
The effect is shown in Figure 6 below
Figure 6 In the previous step, you can get the total amount for each employee by converting them into a column.
Input function: =unique(r3
Input function: =sumifs(s3 ,r3 ,u3
The final result is shown in Figure 7 below
Figure 7