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

Computer Tutorials

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.

Iii. using the DCount function

The DCount function is usually not used, and it can calculate the number of cells in a field that satisfies a database record for a specified condition. This may not sound easy to understand, but it's good to use it for statistical scores, such as the English scores in E-list.

We need to make a setup beforehand. As shown in Figure 4, the number of people with statistics 80 to 89 is: we need to enter "English" in the Q16 and R16 cell fractions (must match the column headings in the original score table), and then enter ">=80", "<90", respectively, in Q17 and R17 cells. These four cells are used as specified criteria.

In N16 cell input formula "=dcount ($E $: $E $47," English ", Q16:r17)", when finished, press ENTER to get the correct result. Note that the first parameter in the formula is the range of cells in the result, and the column headings must be included. So, to count the number of other fractions, you just need to change the third parameter in the formula, which is the range of cells in which the condition is located.

Iv. using the SUM function

The SUM function is used to sum, which the Earth people know. However, it can also be used to perform a multiple-condition count, so it can also be used to calculate fractional segments. Of course, you have to use the array formula to do it. Take the theoretical results in column f as an example.

As shown in Figure 5, enter the formula "=sum ($F $ $F $47>=90) * ($F $ $F $47<=100)" In the N24 cell, and then press the "Ctrl+shift+enter" key combination to produce an array formula, You can get a number of students greater than or equal to 90 points and less than 100 points. Similarly, the array formula "{=sum ($F $: $F $47>=70) * ($F $: $F $47<80)}" can get the number of 70~79 divided by this section. The other fractional segments can and so forth.

V. Use of sumproduct function

The Sumproduct function can return the sum of the product of the corresponding array or region, so it is possible to use it to statistic fractional segments. Take the practice class in the G column as an example.

As shown in Figure 6. To count more than 90 points, you can enter the formula in N30 cell =sumproduct ($G $ $G $47>=90) * ($G $: $G $47<=100), and then press ENTER to get G2: The number of cells in the G47 range is greater than or equal to 90 points or less than 100.

Obviously, to count the number of 80~89 points, you can use the formula "=sumproduct ($G $ $G $47>=80) * ($G $: $G $47<90)", and so on.

Well, five ways to count the number of fractional segments, I believe there must be a way for you. All of the above methods use the environment as Excel 2007, and other versions can be referenced using.