WPS "Common formula" function use detailed

In the WPS table operation, we don't always need to write a lot of functions for data processing, WPS has prepared a lot of common formulas for us to call. Let's try it today.

In WPS, click the Insert Function (FX) button in the Function Library feature group on the Formula tab of the Ribbon to open the Insert Function dialog box. Click on "Common formulas" to see the list of commonly used formula functions.

Find reference data

Student names are in column C, and the total score and average points 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.

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 parameter 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 to look for?". 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 look for?" We need the total score, and the total score is in the cell range of the L column, so we directly click on the column in the L column, or directly into the "$L: $L", click "Insert Formula" to get results.

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.