Simple student performance statistics program!

1. First, select the **2 cell, find the vlookup function, and click OK.

2. Click the Find Value box, and then click the A2 cell, which means that I want to find the score of Zhang 1 in Table 2.

3. In the data table, click to Table 1, and then select the two columns of data for name and grade.

4. Enter 2 in column order, which means that we find the second column of data from the two columns of data in Table 1.

5. After clicking OK, the corresponding score of Zhang 1 in Table 1 will come out.

6. Finally, in Table 2, pull the formula to the last line, and everyone's results will come out.

Summary. Then enter the serial number and grade.

Compile statistical tables that reflect students' academic performance.

Wait a minute, what kind of ** are you going to compile**Is there a template?

It's this. Pro, this is simply sorted.

Enter a grade. Send the detailed answer.

Build a new one**.

Then enter the serial number and grade.

Finally, they are sorted in descending order according to grades.

Okay, thank you, teacher.

Hello, has the last question been answered for you? If you have any new confusion, please feel free to find me again, and I will answer it for you as soon as possible. I wish you all the best!

First, make the basic ** format, enter the basic values, and then enter [=sum(single subject) number of students] in the average score below to get the result.

Please click Enter a description.

Similarly, enter [=sum(All Subjects, Number of Subjects] on the right to get the result.

Please click Enter a description.

Then drag the cursor to the lower right corner to form a black +, and pull down to get the full average.

Please click Enter a description.

Then select the average friend modulus series, and then right-click Format Cells and click Enter Description.

Then select [Value], then enter the number of decimal places you want to display, and click OK.

Please click on the Lost Bridge to enter ** description.

At the end, you can see the full effect.

Please click Enter a description.

For teachers, they often need to use excel to conduct student achievement statistics, and they will be stumped by some common problems. The main problems encountered are: how to count the number of people in different score bands, how to rank under the premise of keeping the student number unchanged, how to convert the 100-point system into different score bands, and how to use red to display the failing scores, etc., this paper focuses on the analysis and discussion of the solutions and skills of these problems.

This article assumes that the reader has a certain foundation in the basic operations of Excel, and has mastered how to sum, average, and how to use autofill handles to copy formulas.

1. Four problems in excel statistics of students' grades.

Here, assuming that the student number, name, grade and other columns and row 15 have been entered in advance, you need to let excel count other relevant data results. At this time, the main problems that are difficult to solve in the performance statistics and their position in the graph are as follows:

Question 1: How can I count the number of students in different bands? (A16-E16 in the figure).

Question 2: How to rank students under the premise of keeping the order of student numbers unchanged? (F2-F13).

Q3: How do I convert the 100-point scale into different bands? (Rank 1 and Grade 2 columns in the figure).

Q4: How do I make a failing score appear in red? (The part shown in red in the figure, i.e. row 12).

2. Methods to solve the four problems in the statistics of students' grades.

Below, we will discuss the methods and techniques for solving each of the four problems raised above.

1. Count the number of students in different score bands.

Counting the number of students in different bands is a very common requirement, and the required results are shown in Figure 1 a16-e16. Here, let's say you need to count the number of people in five different band of 69 and below 60.

In general, the best way to count different bands is to use the countif(x,y) function. There are two parameters, the first parameter x is the statistical range, and it is generally best to use absolute references; The second parameter y is the statistical condition, which should be put in quotation marks.

For the number of people less than 60 points, just use a countif( ) function, such as enter the formula in cell e16: =countif($c$2:$c$13,"<60"）。

For other counts in the band between two fractions, you need to subtract them using two countif( ) functions. For example, enter the formula =countif($c$2:) in cell A16

c$13,"<100"）-countif（$c$2:$c$13,"<90"), i.e., subtract the number of people less than 90 from the number of people less than or equal to 100.

If you want to count the number of people in the -79 and 60-69 segments, just use the autofill handle to copy the formula to the three cells on the right, and then set it"<=100"with"<90"Make the corresponding changes and you can get the right results.