Using Excel formula to statistic and analyze students ' test results

End of the final exam, teachers need to statistical analysis of students ' achievements, such as the calculation of students in the class ranking, the grade of students in the year, the ranking of individual grades. With the help of Excel formula, we can take care of it in a very short time!

　　Basic analysis: The ranking of students ' grades to understand each student's learning achievement

Create a new workbook and name it "the final grade analysis form for the third year of the first semester of the 2010~2011 school year", in which a new "student performance Schedule" is entered to enter the students ' results for the examination. This process only manual input, there is no shortcut to go.

Enter totals, class rankings, Grade rankings in the K2, L2, M2 of the worksheet, and then enter the formula in K3, L3, M3 cells =sum ($D $ $D e3:j3) * ($K $: $K =sumproduct Gt K3)) +1 "," =rank (K3, $K $: $K $144) ", respectively, to calculate the total score of each student, the ranking in this class and the rank of this grade, and copy these formulas to the corresponding cell below, the student's Test ranking came out.

　　Graphic Analysis: Visual display results to facilitate the comparison of the scores of the subjects

Although the results are analyzed by the function, the figures are dull and not intuitive, and the graph will be more image-making. When you edit a chart, select I37 cell to set its data validation from the source "= $C $ $C $", and then copy the column names from the analysis results table into B3:G33 and enter the formula in B34 under the first course "Geography" =vlookup ($I $44, $C $ $I $ 8,column (), 0) ", and copy this formula to the C34:g34 cell range that follows, and enter the formula in D31 cell" = "2010~2011 the first semester of the third year final exam" &D2& "&I44&" Chart "".

Then execute the Insert → chart command, insert a column chart with the source data range set to "= Score analysis!" $B $33: $G $34 "and modify the chart format as needed. Finally, add a title text box, select this text box to enter "= $D $31" In the formula bar, so that when you want to view a class's results, just click I37, select a comparison analysis indicator (for example, pass rate), and in the chart you will visualize the passing rate comparisons for each course.