Four problems and solutions in Excel statistical scores

First, the preface

For teachers, often need Excel for student performance statistics, will be puzzled by some common problems. The main problems encountered are: how to count the number of different fractions, how to keep the number of the premise of the same, how to hundred to different fractions and how to show the score in red, this paper focuses on the solutions to these problems and techniques to analyze and discuss.

This article assumes that the reader has already had a certain basis for the basic operation of Excel, has mastered how to sum, average and how to use the automatic fill handle to copy the formula and so on, this article does not do the detailed introduction to these general operations, only some difficult operation skills to discuss.

Two, Excel statistics student achievement four problems

Let's say we need to count the results shown in Figure 1 when we are counting our students ' scores.

In

Fig. 1 The result chart of the student's achievement statistics

Here, the hypothesis number, name, score and row 15 have been previously lost, you need to let Excel statistics of other relevant data results. At this point, the main problems that are difficult to solve in the performance statistics and their position in the diagram are as follows:

Question 1: How to count the number of students in different grades? (pictured in A16~e16)

Question 2: How to keep the school number in the same order, the premise of the student achievement ranking? (pictured in f2~f13)

Question 3: How do I convert hundred to different grades? (Level 1 and Rank 2 columns in the figure)

Question 4: How do you make a failing grade appear in red? (The Red Display section of the figure, that is, line 12th)

Three, the solution to the statistics student achievement four difficult problem method

Below, the solution and techniques are discussed for the four problems presented above.

1. Count the number of students in different fractions

Counting the number of students in different fractions is a very common requirement, and the desired results are shown in Figure 1, A16~e16. Here, suppose you need to count the numbers of 90~100, 80~89, 70~79, 60~69, and the number of people below 60 points and five different fractions.

In general, the best way to count the different fractional segments is to use the COUNTIF (x,y) function. There are two parameters, the first parameter x is the range of statistics, it is generally best to use absolute reference, the second parameter y is a statistical condition, to quote.

For people less than 60 points, just use a COUNTIF () function, such as entering a formula in a E16 cell: =countif ($C $: $C $, "<60").

For the number of other fractional segments between two fractions, it is necessary to subtract two countif () functions. If you enter a formula in cell A16: =countif ($C $: $C $, "<=100")-countif ($C $ $C $, "<90"), that is, the number of people less than 90 is subtracted from the 100.

If you want to count the number of 80~89, 70~79 and 60~69 fractions, just use the automatic fill handle to copy the formula to the right three cells, then "<=100" and "<90" make the appropriate changes, you can get the correct results.

2. Keep the school number in the same order without changing the premise of the score ranking

Student scores are often used in student performance statistics. In particular, the method discussed here is not a general sort, as it will change the student's order of study numbers. What is needed here is the ability to rank the students ' grades with the same order of numbers, and the desired results are shown in f2~f13 in Figure 1.

It is best to use the rank (x,y,z) function if you want to keep the order of the school numbers in place. There are three parameters, the first parameter x is the cell of a student's score; the second parameter Y is the region of the whole class; the third parameter z is optional, indicating the statistical way, if the province writes or writes 0, the result high rank leans forward, generally uses this way, if writes 1, then the result high rank leans upon, This situation is generally less used.

In order to keep the learner's order unchanged, you can enter the formula in cell F2: =rank (C2, $C $: $C $13,0), and then copy it to several cells below using the automatic fill handle. Note that here $c$2: $C $ $ is used as an absolute address to ensure that the formula does not change at the time of the copy, because as a second parameter, this is the area where the class scores are located, and the area is the same.