WPS Table Simple formula complete complex task

First 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 a list of commonly used formula features, as shown in Figure 1.

Find reference data

1, as shown in the list of achievements on the left of Figure 2, the student name is in column C, and the total score and the average score 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.

2, first position the mouse in cell P3, 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.

3, click the "list to refer to" the right input column. 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.

4, click the "Find conditions" input 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".

5. Click the "Return column" input box. The meaning of this parameter is to solve "what to look for?" 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.

6, the above operation is equivalent to 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."

7, the third parameter to "$M: $M", the same method can be found 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.

Multiple conditions sum

1, for example, we want to calculate the "Class 1" Chinese score is greater than or equal to 60 of the students ' math score. When you open the formula list in the Insert Function dialog box, click the multi-conditional sum option, as shown in the table in Figure 2.

2, "To sum the area" for the mathematical results in the E column, that is, "$E: $E." The first condition is column B ($B: $B) equals "1", and the second condition is column D ($D: $D) not less than "60", as shown in Figure 4. Click on "Insert Formula" to OK.

3, know how to write with the formula?

"=sumproduct ($E: $E, 1* ($E: $E, 0,sum (column ($B)-column ($E: $E)) =1) * (offset ($E: $E, 0,sum (column ($D: $D)- COLUMN ($E: $E))) >=60)) ". I don't know if you're surprised, but I was a little freaked out anyway.