The WPS table uses the multi-conditional summation function to count the exam results

The statistics student's achievement is one of the teacher essential work, each class student so many, then how can we be most accurate and effective to statistic the result? This time we will explain how to use the WPS table to calculate the students ' test results quickly and accurately

The WPS table statistics operation steps are as follows:

① Select M2 Cell, click the Insert Function button, and in the list of commonly used formulas, select multiple conditional sum, where the range to sum is the class, condition 1 is the class equals 1, Condition 2 is not less than 100 for the language, 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)) "($A $ $A $650))"

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 (COLUMN (b$2:b$650)-column ($A $ $A $650))))/$L 2 ", that is, the number of meeting 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.

② 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.