How does Excel automatically tally student scores?

This automatic statistical table can automatically statistic the highest score, the lowest score, the total score, the average score, the rank and so on data information, also may display the score according to the custom condition in the different color. Automatic statistics can also be saved as a template for later use. The end of the article provides an. xls file for everyone to download for reference.

At the end of the semester, the teacher's one important task is to count the students ' achievements, in the computer gradually popularized today, we will this complex work to Excel to automatically complete it.

Related functions: AVERAGE, COUNTIF, IF, ISERROR, MAX, MIN, RANK, SUM

1, start Excel, select A1 to K1 cell, press the "Merge and Center" button on the Formatting toolbar, merge it into a single cell, and then enter the caption for the tab (see Figure 1).

(larger picture, please pull the scroll bar to watch)

2, according to the table format, the relevant column headings and related content entered into the corresponding cell (see Figure 1).

3, select J3 cell, enter formula: =sum (C3:I3), used to calculate the total score of the first student.

4, select the K3 cell, enter the formula: =rank (J3, $J $: $J $62), to calculate the first student total score (here, assuming a total of 60 students).

Note: If you modify the above formula to: =countif ($J $: $J $62, ">=" &j3), you can also calculate the rank of the first student.

2 5, select both J3 and K3 cells, when you move the mouse to the "fine cross" shape in the lower-right corner of the K3 cell (usually referred to as the "fill handle" state), hold down the left key and drag down to the K62 cell to complete the other student's total score and ranking statistics.

6, respectively selected C63, C64 cell, input formula: =max (C3:C62) and =min (C3:C62), used to statistical "language" subject to the highest and lowest points.

7, select the C65 cell, enter the formula: =if (ISERROR (AVERAGE (C3:C62)), "", AVERAGE (C3:C62)), used to statistics the "language" of the subject of the average score.

Note: the =average (C3:C64) formula can also be used to calculate the average score, but if the result is not entered in the score table, the formula will display an incorrect value, so we use the "ISERROR" function to eliminate the error.

8, select C66 cell, input formula: =sum (C3:C62), used to statistics "language" discipline total score.

9, at the same time, select C63 to C66 cell, use "fill handle" to copy the formula to D63 to J66 cell, complete other disciplines and total score of the highest score, the lowest score, the average score and the total score of the statistical work.

At this point, a basic performance statistics production completed (see xls file download), let us further deal with.

10, select C3 cell, execute "format, conditional Formatting command, open the Conditional Formatting dialog box (Figure 2), press the Drop-down button to the right of the leftmost box, select the Formula option in the Drop-down list that appears, and enter the formula in the box on the right: =c3>=average (C3: C62), and then press the Format button to open the Format Cells dialog box and set the font color to blue.

Then press the "Add" button, followed by the above operation, add a formula for "=C3

Note: After this setting, when the student's "language" score is greater than or equal to the average time, the display of blue, vice versa display red.

3 11, select the C3 cell again, press the Format Brush button on the Formatting toolbar, and then drag the D3 to the J3 range to copy the conditional formatting to the appropriate area to complete the conditional formatting work for other disciplines and scores.

12, once again select C3 Cell, open the Conditional Formatting dialog box, modify two of these formulas to: =c3>=average ($C $: $C $62) and =C3

Modify the formula in the conditional formatting of the D3 to J3 cell to an absolute reference (add the "$" symbol), as described above.

Note: The purpose of this is to copy the conditional formatting from the Format Painter to the following cells.

13, at the same time select C3 to J3 cell, press the Format Painter button on the Formatting toolbar, and then drag it over the C4 to J62 cell range to copy the conditional formatting to the appropriate area to complete the other students ' grades and total scores.

Fill in the results of the students in the table to try, the effect is good.

If you often want to count the students ' grades, we will save them as templates for easy call at any time.

4 14, delete the students ' scores in the worksheet, and execute the "file, save (saved as)" command, open the Save As dialog box (Figure 4), press the Drop-down button to the right of the save type, and in the Drop-down list that appears, select the template (*.xlt) option, and then give a name (such as "score statistics "), press the Save button.

15, after the need for statistical results, start Excel, to do the file, create New command, expand the New Workbook task pane (Figure 5), click the "Templates on this machine" option, open the Templates dialog box, and double-click the "Score statistics" template to create a new table of achievements.

16, the student's results into the corresponding cell, named Save can quickly complete the performance statistics processing work.