WPS 2012 complete complex tasks with common formulas

In table operations, we often encounter some more complex tasks, such as the summation of multiple conditional data, such as finding or referencing eligible data in another worksheet. These tasks can be accomplished using a combination of functions or functions, but for most people, such tasks are too complicated. In the WPS 2012 Table tool, but only "commonly used formula" can be completed, completely do not have to memorize those complex function combination, simple operation, worry and labor, why not?

Open the Insert Function dialog box in the Function library function group on the Formula tab of the ET 2012 click on the Insert Function (FX) button. Click on "Common formulas" to see a list of commonly used formula features, as shown in Figure 1.

Figure 1

Find reference data

As shown in the list of achievements on the left of Figure 2, student names are in column C, and the total score and average are in columns L and M. To the right is the table that needs to be completed. We need to find the corresponding student's total score and the average score in the left-hand table, which is what the common formula can do for us.

Figure 2

First position the mouse over the P3 cell, and then open the Insert Function dialog box and switch to the common Formulas tab. In the formula list, click the "Find other table data" option at the bottom.

Click on the right side of the "List to reference" entry bar. The meaning of this argument is "where to find?" ”。 Box select the c2:m350 range of cells in the source worksheet to complete the input for the first parameter. Note that this area must have the lookup criteria in the first column of the range. In this case, the student name is searched, and the condition is the student's name, so the first parameter must start with column C.

Click the "Find Condition" entry box. The meaning of this parameter is to resolve "what is the search based on?" ”。 We use the O3 cell as the lookup condition, so click the O3 cell here, or just enter "O3".

Click the "Return to which column" entry box. The meaning of this parameter is to solve "what to find?" ”。 What we need is total score, and the total score is in column L of the cell range, so we just click on the column in column L, or directly enter "$L: $L", as shown in Figure 3, click "Insert Formula" to get results.

Figure 3

The above operation is equivalent to the input formula "=vlookup (O3, $C $: $M $350,column ($L: $L)-column ($C $ $M $350) +1,0)". Obviously, the dialog box operates much simpler than writing a formula directly. At least do not "look at the formula and dizziness."

To change the third parameter to "$M: $M", the same method can be used to find the required average points.

Select O3:p3 Cell, drag its fill handle to copy the formula down, and all the students ' scores and average points in the table are found.