How to accomplish complex tasks without using Excel functions

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 the list of commonly used formula functions.

Figure 1

Find reference data

On the left score list, student names are in column C, and the total score and average are in L and M columns. 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.

Triple Recommendation: Click for free Download the latest version of WPS Office software "to see more computer tutorials

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.

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.

Two, multiple conditions sum

For example, we would like 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.

The To sum area is the E column of the math score, which 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". Click on "Insert Formula" to OK.

Figure 4

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

The use of "common formula" can also complete the calculation of personal income tax (new and old algorithms), the use of identity cards to extract the date of birth and gender. These operations are simpler and will not be verbose.