Skillfully use Excel to help me carry out student score statistics

In the teacher's daily work, the student's achievement carries on the statistical analysis management is a very important also very troublesome work, if can take advantage of Excel powerful data processing function, can let each teacher quickly complete to the student's achievement each analytic statistic work. Here are some tips on how to use Excel to manage your students ' grades.

Quick change of students ' test grade

Sometimes, you will meet the test scores of the students to the actual test scores to the corresponding grade of the situation, such as the test results in 90 points above the results converted into a "A +" form, 85-89 points of the result converted into "A" form .... In general, the IF () function is used in an Excel table to design a formula for conversion, so that the formula that is designed becomes complex, and if the grade type of conversion exceeds the maximum nesting level of the if () function, the if () function is powerless. At this point we can use the following methods to simplify the operation.

1, open the Student Performance worksheet (Figure 1).

2, in the G2 to the I12 cell Input Examination score section and the examination grade comparison table.

3, in the D3 cell input formula "=index (I$3:i$12,match (1, (c3>=g$3:g$12) * (c3<=h$3:h$12), 0))", because the formula is an array of formulas, after entering the above content, you must also press the Ctrl +shift+enter "key, add an array formula label to the above formula, namely curly braces" {} ". The function of this formula is to automatically convert the result to the corresponding grade in D3 cell according to the student's score in the C3 cell.

4. Move the cursor to cell D3, drag the fill handle down to the D12 cell, and quickly copy the formula so that you can quickly complete the conversion of your student's grade (Figure 2).

5, can also according to their own preferences, the G2 to I12 cell range set to "hidden" to make the table more beautiful.

Ii. statistics of the distribution of students ' test scores

When using Excel to manage Students ' test scores, it is often necessary to count the distribution of students ' test scores in each score section, and if the method described below can make this work very convenient.

1, open the Student performance worksheet (This example still uses the worksheet of the previous example).

2, in G3 to G6 cell input students test scores of statistical segmentation points. For example, the statistical segmentation points used are: 60, 69, 79, 89, that is, statistics 60, 61-69, 70-79, 80-89, 90 points above five students in the test performance section of the distribution, of course, you can also according to their actual needs in this different settings.

3, select the formula to design the range of cells B14 to B18, press the F2 key, input formula "=frequency (C3:C12,G3:G6)", because the formula is an array of formulas, after entering the above content, must simultaneously press "Ctrl+shift+enter" key, Adds an array formula label to the above formula's contents, which is the curly brace "{}".

4, when the above operation completed, in B14 to B18 cell quickly get the correct student test results distribution (Figure 3).

5, it is noteworthy that when we design the statistical section, this statistical section must be one more than the number of statistical segmentation points. This extra statistical section represents the number of values that exceed the maximum interval. For example, in this example, we designed the statistical segment point of 60, 69, 79, 894 numeric values, in order to get the correct statistical section distribution data, you must enter the B14 to B18 five cells in the FREQUENCY () function calculation results, More out of this one cell will return the number of students who score more than 90 points in the student score sheet.

Through the above two examples, I believe that the application of Excel to the results of student performance statistics has been understood. In fact, the job of a public-oriented spreadsheet software, it is the application of a lot of occasions, as long as we think, you will find that everything is a simple change.