Excel to analyze statistics of special students ' achievements

1. Test mix, achievement unification--the old method encounters new problems

On the use of Excel for student performance processing, is already the old topic. But there are still many new problems in the actual work, for example, now many schools are the whole grade class mixed together the examination, in order to prevent the examination to change the improper competition in the volume. and statistical results, is the assignment but unopened examination papers to the academic office, first according to the seat number of the order (the natural order of each test paper) to enter the scores of various subjects, and then analyze the statistics of the whole grade. For example, as shown in Figure 1 (records 11 to 830 hidden), to count two (1) class outstanding number, the traditional practice is to first by the test number, and then through the formula "=countif" (Score! d2:d69, ">=96") "Find out." Its disadvantage is to manually modify the "d2:d69" of the parameters of the two line number (2 and 69), this is a very heavy work. Of course, the simple method is still there, look down.

2. Preliminary preparation--exam number in the class

As shown in Figure 1, remove the top three (class number) from the test number in column B and put it in the S column, that is, enter the formula "=left (b2,3)" in cell S2, and then double-click (or drag) S2 the fill handle in the lower-right corner of the cell.

3. Auxiliary table--class level two in Oneness

Create a new worksheet in the workbook shown in Figure 1 and name it "level" and enter the formula "= score in cell A1!" A1 ", enter, select A1, hold down the fill handle in the lower-right corner of the A1 to the C840 cell, and refer the name, test number, and seat number in the score worksheet to the level worksheet (note that you can't copy and paste it, so you can't keep the two table data consistent). Then select C1, hold down the fill handle in the lower-right corner of the C1 to the L1 cell, and refer to the 9 subjects in Chinese, maths, and so on. Next, enter the IF nested formula in the D2 cell =IF score! D2>=96, Score! $S 2&& "A", IF (score!) D2>=72, Score! $S 2&& "B", IF (score!) D2<48, Score! $S 2&& "D", score! $S 2&& "C")) ". The meaning of the formula in cell D2 is: Look at the score! D2 the score in the cell (that is, the language score in the score sheet Li Yue) is greater than or equal to 96. If it is, fill in the D2 cell with "201a"--The character "201" in cell S2 in the score worksheet Plus "a" ("201" for two (1) classes, and "a" indicates grade "Excellent"); if not (that is, less than 96), see if it is greater than or equal to 72. If it is, fill in "201b" in cell D2, if not (that is, less than 72), and then see if it is less than 48. If so, fill in the D2 cell with "201d"; if not (less than 72 is greater than 48), Then fill in the "201c" in cell D2. Finally, hold down the fill handle in the lower-right corner of the D2 cell to the L840 cell, and you can fill in the grades of each student's grade and the class they belong to.

4. Final statistics--instantaneous alignment of required data

After the auxiliary table system is good, go back to the "Statistics" worksheet (Figure 2), using the AutoFill feature in the A17 to E28 cell area to make a small block of secondary data (Figure 2).

Everything is ready, the following start the total number of classes and excellent rate, pass rate and so on statistics. Still take two (1) class excellent rate as an example, now switch to such a formula "COUNTIF" (Grade! $D: $D, $B 17) ", that is, all cells in the D column of the level worksheet are counted (level!). $D: $D), find the number of cells that have a value of "201a" (the value of the $B17 on this worksheet, the statistics worksheet, representing the two (1) class excellence rate). The following are specific practices:

(1) The total number of classes (in the B4 cell input): "=countif score!" $S: $S, A17) ";

(2) Outstanding number (enter in C4 cell): =countif (grade!) $D: $D, $B 17) ";

(3) Excellent rate (input in D4 cell): "=c4/$B 4 100";

(4) Pass number (enter in E4 cell): =countif (grade!) $D: $D, $B) +countif (level!) $D: $D, $C 17) ";

(5) Passing rate (input in F4 cell): "=e4/$B 4 100";

(6) Low score (input in G4 cell): "=countif" (Grade! $D: $D, $E 17) ";

(7) Low-rate (input in H4 cell): "=g4/$B 4 100";

So far, the rest of the data through the automatic filling function, a moment to complete.

5. Method Reviews-A watch into, year-round use, a labor and Yong Yi

(1) The results of the different grades of the simple apply: For example, first-year production of a statistical table, by copying and pasting the first worksheet ("Score" worksheet) to change the contents of the second-grade data sheet, then the second grade of the results of the statistics will naturally become.

(2) Multiple test scores of the simple use of: This test statistics, to the next test scores, replicable not mistaken, only the first worksheet to change the record of the new students can be.