Excel five function Statistics student final Exam score Section

At the end of the exam, the teachers have to analyze the students ' test results. The statistics of the number of each score segment is one of the necessary tasks. In Excel, how to quickly and accurately count the number of fractional segments? The following methods may be helpful to you.

Take a look at the original student score sheet first. The results of five subjects are distributed in the C2:g47 range, as shown in the following figure.

First, using the COUNTIF function

The COUNTIF function can count the number of cells within a range of cells that meet the criteria you specify, so it's logical to count the number of fractional segments. We use it to count the scores of Chinese scores in column C.

As shown in Figure 2, we need to count the number of students in the N2 cell with a score of 90 points in the language. Then just enter the formula "=countif" (C2:c47, ">=90") in the N2 cell. The implication is to count the number of cells in the C2:c47 range that are greater than or equal to 90. So, to count the number of students from 80 to 89, then you need to enter the formula "=countif (C2:C47, >=80)-countif (c2:c47," >=90 ")". It is clear that the number of people who are greater than or equal to 80 points minus the number greater than 90 points is exactly the number we want. Other fractional segments can and so on.

Second, using the frequency function

This is a function that is used to calculate the frequency distribution of data in a range of cells, and it is natural for the fractional segment to be statistically justifiable. Take the statistics of math scores in column D as an example.

Let's set the fractional segment in the M8:M12, then set the number of separate fractions (that is, the upper number of the fractional segment) in the L8:l12 range, as shown in Figure 3. Select N8:n12 Cell, enter the formula "=frequency ($D $: $D $47, $L $ $L $)", and then press the "Ctrl+shift+enter" key to confirm that you can add the label "{}" of the array formula at both ends of the formula. At the same time, we can see that the number of the scores has been completed. It is important to note that the formula input must be confirmed by the "Ctrl+shift+enter" key combination to produce an array formula, and the label "{}" of the array formula cannot be entered manually.