How to use Excel to deal with students ' grades perfectly

Most schools have a comprehensive assessment of their students after the final exams, one of which is grade evaluation. So how to the results of the treatment of students to reflect the situation of students? The author explored a set of methods in practice, now introduced to everyone.

There are a lot of software that can deal with students ' scores, here we mainly introduce the application of Excel in common use. We selected a class of 10 students as an example, the results of their collection, sorting, statistics of the number of scores, draw the distribution curve. According to the requirements of the design form and input results, after the treatment of the results shown in Figure 1.

Figure 1

Calculate the total score of students ' subjects

First, the scores of the first students were calculated, then the scores of the other students were obtained by the copy function.

1. Calculate the total score of the first student

(1) Select the function. Select cell H4, and then click Insert/function in the menu bar or the FX button in the toolbar. Then pop up the Paste Function dialog box, first select math and trigonometric functions in the "Function category" column of the dialog box, and then select "Sum" in "Function name" and press "OK" to exit.

(2) Select the range of cells you want to calculate. When the function is selected, the function parameter Input dialog box opens. Then click the Collapse Dialog button (that is, the button to the right of the Number1), the dialog disappears, then select the range of cells, select the method (click C4 Drag to G4 or click C4 to press SHIFT while clicking G4), and finally you must press the ENTER key To confirm, the function Parameter Input dialog box appears again, and you can get the first student score after you exit by clicking the OK button in the dialog box.

2. Calculate the total score of other students

Click the H4 cell and point to the small black square (that is, the fill handle) in the lower-right corner of the cell. To the cursor into the black "ten" word, press the left mouse button down to H13, each branch of the total score is very easy to show (depending on the need to choose a different function of the data processing, such as the Max function to calculate the highest score, Min calculates the lowest score, average calculates the average score, etc.).

Rank by total score 1. Name of a range of cells

First, select the range of cells for the total score data h4-h13, then click Insert/Name/definition in the menu, enter or modify the name "Total Score" in the "Current workbook name" column in the Define Name dialog box, and display the range of cells that you just selected in the Reference Location column H3-H16 ( Of course, you can also click the Collapse button on the right side to select a range of cells again, and then press OK to exit.

2. Select function to determine rank

Enter "Rank" in cell I3, click the I4 cell, select the Data/Function menu item, or click the FX button in the toolbar to eject the Paste Function dialog box and select the RANK function in the statistics class to eject the function parameter Input dialog box. Enter H4 in the number column (or click the Collapse button on the right side of the box to disappear, click H4 and then press "enter", which is the cell H4 that you just selected, enter "Total score" in ref, click OK to exit to get the first student's score ranking, and then select the cell J3 , copy the copy handle to get the rank of the class score.

Statistics on the number of sections in each section

In the A14-a19 respectively input "49 cent below", "50-59 points" 、... "90 points above", first statistics "high number" achievement in "49 cent below" the number of methods and the total score is basically the same, just select different functions. Select statistics in the function category, select COUNTIF (g3:g65,>=80) in the function name, eject the function parameter Input dialog box, select or enter the calculated area C4~C13 in the Range column, enter "<=49" in the Criteria column, and press The number of people who meet the criteria in the C14 when the OK button exits. To count the number of people between 50-59 points just type ">=50 and <=59" in the Criteria column. The same method can be used to find the number of other fractions, and finally, the number of each section by the method of duplication.

Draw the performance distribution curve (take the "high number" course as an example) 1. Select the data range to create the chart

Select A14 Drag to C19 or click A14 to hold down the CTRL key and then click C19.

2. Alternative chart types and subtypes

(1) After you select the data range where you created the chart, click the Chart Wizard button on the Standard toolbar or select the Insert Chart menu command to eject The Chart Wizard dialog box;

(2) In chart type, click the Custom Type tab, select Smooth line type in the Options box (select the appropriate curve in the chart type to select it by holding down the View Sample button and then selecting again);

(3) Click Next in the dialog box to eject the Chart Source Data dialog box, select the column option on the Data Range tab, and then click the Next button in the dialog box to eject the Chart Options dialog box and add descriptive text to the dialog box.

(4) Click the Next button to eject the chart Position dialog box, select the "Insert as new sheet" option in the dialog box, and then click the "Finish" button to exit and get the score curve shown in Figure 2 (you can edit the graph).

Figure 2

Note : More wonderful tutorials Please pay attention to the triple computer tutorial section, triple Computer office group: 189034526 welcome you to join

Classification:

• Excel Tutorials