The summation formula of WPS more than 2012 parts improves the efficiency of achievement statistic

After each examination, analysis and statistics of student achievement is an essential link. There are many schools that use the same grade to unify the exams and then count the number of students in each class in each grade. The following author used Excel 2003 and WPS Office2012 table to count the results of a test.

Statistics in Excel 2003

Open the examination data sent to the grade (Figure 1), first of all, the statistical language score is greater than or equal to 100 of the number of students in each class.

Figure 1

The first step is to select a range of cells (a1:j650), perform data → sort, and then select the primary keyword as "language" and "descending" sort, as shown in Figure 2.

Figure 2

In the second step, enter the formula "=countif (A$2:A$180,L2)" In the M2 cell to count the number of students in Class 1, in which the cell area (a2:a180) is the student with a score greater than or equal to 100 after the order.

Step three, select the M2 cell, and then automatically fill it down, drawing the number of students in the other classes, as shown in Figure 3.

Figure 3

The above steps are then repeated to count the number of students in each class in other disciplines.

Using WPS Office2012 Table statistics

The first step, select cell M2, click the Insert Function button, and in the list of commonly used formulas, select multiple conditional sum, where the sum of the fields to be summed is the class, the condition 1 is the class equals 1, and the condition 2 is the language not less than 100, the specific parameters are shown in Figure 4, and then click Insert Formula. Because the result is the "and" that satisfies the condition, and what is expected to be the number of conditions, the formula "Sumproduct" ($A $ $A $650,1* ($A $ $A $650,0,sum ($A $: $650) -column ($A $ $A $650)) * =1) * (OFFSET ($A $ $A $650,0,sum ($B $ $B $650)-column)) "Modified to" = ( Sumproduct ($A $ $A $650,1* (offset ($A $ $A $650,0,sum ($A $: $A $650)-column ($A $: $A $650)) = $L 2) * (Offset ($A $ : $A $650,0,sum (b$2:b$650)-column ($A $ $A))))/$L 2 ", that is, the number of satisfied conditions (1 of the class language score is greater than 100 points, Note the relative references in the formula and the use of absolute references.

In the second step, select cell M2, automatically fill to P2 cells to the right, and then modify the parameters of the formulas in each cell (that is, the fractional segment to be counted, such as physics is >=80), and then select (M2:P2) The range of cells, automatically fill down, you can quickly count the language, mathematics, English, The number of students in each class in the physics discipline, as shown in Figure 5.

Figure 4

Figure 5

III. Results of the evaluation

From the above statistics, through the comparison of the two software, WPS Office2012 table advantage is more obvious. In this instance, the multiple conditional sum formula is used, and on the basis of the appropriate modification of the formula, you can omit the "sort" of the operation, save a lot of time, but also reduce the duplication of operation and the occurrence of misoperation; the formula is more flexible, according to its own needs, can also be appropriate to modify the formula two times to use, Suitable for some of the more complex statistics.