Use Excel to easily process student scores

After the end of the final examination, the director asked the class teacher to calculate the score of the class and report it to the teaching office as soon as possible. The process includes entering the scores of each subject → calculating the total score, average score and ranking → counting the number of people, passing rate, excellent rate and overall index of each subject, printing various statistical reports → making statistical analysis charts of each subject, etc. With Excel, we don't need to roll around, carry your back, and take a calculator to calculate the students' scores one by one!

I quickly open my computer, start excel2000, and enter the student's test score, as shown in 1. Then, enter the formula "= sum (C2: I2)" in cell J2, and drag the fill handle to fill it down to get the total score of each person. Enter the formula "= average (C2: I2)" in cell K2, and drag the fill handle to fill it down. Then the average score of each person is obtained.

You only need to keep one decimal point for the average score. Select column K, right-click, and select "set cell format (f )... ", 2, select" value "in the number label, and set the number of decimal places to 1.

Next we will rank the students based on the total score.

Enter the formula "rank (J2, J $2: J $77,0)" in the L2 cell, and drag the fill handle to fill it down, you can get the ranking of each person in the class (see figure 1 ).

Note: The rank function is used for ranking. Its syntax is:

Rank (number, ref, order)

Number indicates the number to be located.

Ref is an array or reference that contains a group of numbers. Non-numeric parameters in Ref are ignored.

Order is a number that specifies the ranking method.

· If order is 0 or omitted, Microsoft Excel uses ref as the data list in descending order.

· If order is not zero, Microsoft Excel uses ref as the data list in ascending order.

Finally, click L1 cells and select "sort"> "ASCENDING" from the "Tools" menu to display the scores of each student in the order of ranking.

In addition, we also hope to highlight the failing subjects. It is best to display them in red. Then, drag C2: e78 (the scores of all students, numbers, and foreign subjects) and execute the "Conditional format" command under the "format" menu to bring up the "Conditional format dialog box ". Set the condition to be less than 72 points in red (because the total score of each subject is 120 points), click "format", and set the color to red. Then press "OK. Then, we can use the same method to display the four subjects with less than 60 points in red (because each of these four subjects has a total score of 100 points ).

Next we will count the score sections of each subject, as well as the pass rate, superior rate, and comprehensive index.

Next we will count the score sections of each subject, as well as the pass rate, superior rate, and comprehensive index.

(1) Number of persons below 60: Enter the formula "= countif (C2: c77," <60 ")" in cell c78, and drag the fill handle to the right to fill in cell i78;

(2) 60 minutes ~ 69: Enter the formula "= countif (C2: c77," >= 60 ")-countif (C2: c77," >= 70 ")", drag the fill handle to fill it to the right;

(3) 70 minutes ~ 79: Enter the formula "= countif (C2: c77," >= 70 ")-countif (C2: c77," >= 80 ")", drag the fill handle to fill it to the right;

(4) 80 minutes ~ 89: Enter the formula "= countif (C2: c77," >= 80 ")-countif (C2: c77," >= 90 ")" in cell C81 ")", drag the fill handle to fill it to the right;

(5) number of persons above 90: Enter the formula "= countif (C2: c77,"> = 90 ")" in cell c82, and drag the fill handle to fill the right;

(6) average score: Enter the formula "= average (C2: c77)" at the c83 cell, and drag the fill handle to the right to fill it with i83;

(7) Maximum score: Enter the formula "= max (C2: c77)" at the c84 cell, and drag the fill handle to the right to fill it with i84;

(8) low score rate: the ratio of the number of persons below 40 in each subject to the total number of persons. Enter the formula "= countif (C2: c77," <= 40 ")/count (C2: c77) * 100" in cell c85, and drag the fill handle to fill the right with i85;

(9) pass rate: the pass rate of the language, number, and foreign subjects is divided into 72 points. Therefore, enter the formula "= (countif (C2: c77," >= 72 ") in cell c86 ") /count (C2: c77) * 100 ", and drag the fill handle to the right to fill the e86; and the four subjects such as science, chemistry, politics, and calendar pass the score of 60 points, therefore, enter the formula "= (countif (F2: f77," >= 60 ")/count (F2: f77) * 100" in cell f86 ", and drag the fill handle to fill the right to i86;

(10) Survival rate: the 96-Plus Language, number, and foreign subjects are eugenics. Therefore, enter the formula "= (countif (C2: c77," >= 96 ") in cell c87 ") /count (C2: c77) * 100 ", drag the fill handle to the right and fill it with e87; 80 points or more for four subjects, including theory, chemistry, politics, and calendar, which are eugenics, therefore, enter the formula "= (countif (F2: f77," >= 80 ")/count (F2: f77) * 100" in cell f87 ", drag the fill handle to the right and fill it with i87; 3 shows.

(11) comprehensive index: the formula for calculating the comprehensive index of our school is Z = [(1 + excellent rate-low score)/2 + pass rate + average score/total score of this subject]/3. Therefore, enter the formula "= (1 + c87/100-c85/100)/2 + c86/100 + c83/120)/3" in cell c88 ", drag the fill handle to the right and fill it with e88. Enter the formula "= (1 + f87/100-f85/100)/2 + f86/100 + f83/100) in cell f88) /3 ", drag the fill handle to fill the right to i88. 3.

By the way, to give others a more intuitive understanding of the score segments of each subject, you can consider using charts. Click the "chart" command in the "insert" menu to bring up the "chart wizard" dialog box. In the "chart type" list box, select a chart type, for example, "Pie Chart ", click "Next", click the compression list box on the right of the "data area" text box, drag and select b78: c82, and click the compression list box again. Click "Next" and enter the chart title, for example, "analysis chart of the Chinese scores of the first (1) Class", click "Next", and then click "finish ". 4. Other subjects are also processed, but when dragging and selecting a data area, you need to hold down the "Ctrl" key because it is a discontinuous area. Good! Everything is OK!

Slow! In order to not repeat the preceding tedious work in future exams, it is best to save the worksheet as a template. So I copied the worksheet to another workbook and deleted the scores of all students in the unit section (C2: i77 in the table ), run the "Save as" command in the "file" menu and select "template (*. xlt) ", save it as a template file, this can be done once and for all.