Using Excel formula to statistic and analyze students ' test results

Source: Internet
Author: User
Tags comparison

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.