I got the latest version of WPS today, and one of the updated functions let is a new function that Mr. Gu has been looking forward to, the biggest advantage of this function is that you can use the let function to define the corresponding name when you need to reference it multiple times, so that the length of the formula is greatly simplified.
Combined with a requirement to illustrate the use of the let function, there is a set of data, a contract corresponds to multiple processes, and each process has a corresponding quantity, which now needs to be converted into the display method in Table 2:
The operations corresponding to the same contract are combined into a single cell.
The quantities of relative operations are summed together and summed.
The data is shown in Figure 1 below
Figure 1 shows the requirements and the immediate response is the requirement that a 1D report is converted into a 2D report and connected.
One contract corresponds to multiple processes, which can be filtered using the filter function.
The filtered results are summed by the multi-condition summation function.
The result of the sum is connected by a textual connection symbol;
Convert multidimensional data into a row of data and connect it to a cell with a join function
The simulation results are shown in Figure 2 below
Figure 2 needs to transform Table 1 into Table 2, and the first step is to deduplicate the contract data in Table 1.
Enter the function: unique(b3:b10).
The effect is shown in Figure 3 below
Figure 3 After having a contract number, the process corresponding to a contract can be filtered out by using the screening function and the deduplication function.
Enter the function: unique(filter(d3:d10,b3:b10=f3)).
Function Description: Filter the process for contracts with contract number equal to F3, and then delete the corresponding duplicates through unique.
The effect is shown in Figure 4 below
Figure 4 uses the filter function to obtain the unique value of the corresponding process of the contract, and then the multi-condition summation function can be used to sum the number of operations corresponding to the contract.
Input function: sumifs(c:c,b:b,f3,d:d,g3
Function interpretation: This is a summary sum of two conditions, which needs to meet the conditions of "contract and process", and the sum area is the quantity of column c
The effect is shown in Figure 5 below
Figure 5 In order to merge into the same cell later, there needs to be a partition effect for different processes and summary quantities, and this problem is solved here by using text connection symbols.
Entry Function:":("&sumifs(c:c,b:b,f3,d:d,g3#
Function definition: "Connect symbols by text" > "Connect partition symbols (opening parenthesis, closing parentheses, commas) multiple times
The effect is shown in Figure 6 below
Figure 6 is basically done with the above step, and the rest is merge transpose.
Connect the two regions together:
hstack(g3#,h3#
Retranspose: torow(i3
Re-merge: concat(torow(hstack(g3,h3
The finished effect is shown in Figure 7 below:
Figure 7 writes out the results step by step through the auxiliary column method, and now it's the turn of let to define the first one:
Name: A formula: unique(filter(d3:d10,b3:b10=f3)), the name of the deduplication operation of the filter contract.
Input formula: let(a,unique(filter(d3:d10,b3:b10=f3)),concat(torow(hstack(unique(a),":("&sumifs(c:c,d:d,a,b:b,f3)&")，")))
The effect is shown in Figure 8 below
Figure 8 After the merge, you find that you need to remove the last comma, and you can do it again
Name: B Formula:
let(a,unique(filter(d3:d10,b3:b10=f3)),concat(torow(hstack(unique(a),":("&sumifs(c:c,d:d,a,b:b,f3)&")，")))
Enter the following formulas: let(b,let(a,unique(filter(d3:d10,b3:b10=f3)),concat(torow(hstack(unique(a),":("&sumifs(c:c,d:d,a,b:b,f3)&")，")))left(b,len(b)-1))
Function definition: Determine the length of b first, and then use left to extract fields with less than 1 character in length of b from the left.
The effect is shown in Figure 9 below
Learn PMC production planning and operation with Brother Gu, is it enough for a lifetime?