How to count scores with Excel functions

　　Objective: to establish the statistical table of students ' examination results, and to make statistics on the scores and sort the rankings.

Enter the students test results, and calculate the average score, individual total score, and then the results can be sorted. (Create a "Student Files" worksheet in Excel and enter good grades.) ）

　　Step method

　　1, the input student achievement;

Rename the "Sheet2" worksheet to "score statistics", select A1 cell and enter "= Student file!" A1, after you select the A1 cell, drag to the A24 cell by the AutoFill method, and then select the A1~a24 cell and drag to B24, and you will find that the student's information has been automatically filled in.

　　2. Calculate the average score and total score of the students ' sections;

If you fill in the "=average (C2:C24)" In the C26, you can find the average of the cell C2 to the C24, so that the division's average points are calculated.

such as: Fill in the I2 "=sum (C2:H2)", can find C2 to H2 sum, so that even the total score of the student.

Then, the automatic filling function is used to find out the average score of each section and the students ' total points.

　　3, ranking sorting;

Click the "data → sort" command, select "Main keyword" as "total score", Sort by "descending", and then rank according to total score.

Use the AutoFill feature to fill in the serial number 1-23 in J2 to J24, enter "1" in K2, and enter "=k2* (I3>=I2) +j3* (I3<I2)" in K3.

Then select the K3 cell, use the automatic padding to drag down, the rank is lined up.

　　4, statistical results;

In the drawing of each subject, the number of each score section is counted first. Click C4 cell in the new worksheet and type "COUNTIF" (' Midterm score '! $D $: $D $24, >=90″), and then type "C5" in COUNTIF cells (' midterm results '! $D $: $D $24, >=80″)-c4 ", Then enter "=countif" in the C6 cell (' Midterm score '! $D $: $D $24, ">=70″)-c4-c5", and finally enter "C7" in the =countif cell (' Midterm score '! $D $: $D $24, >=60″)- C4-c5-c6 ". In this way, the number of the scores of the language scores are counted out.

　　5, the production of performance distribution map;

Select B4 to C8 cell, click the common → chart Wizard button, select chart type → pie chart, and select Split pie chart from the chart type on the right. Click "Next" button two times, enter the chart title "Language score Distribution", and then click "Next" button to "Finish", the results of the distribution map OK.