Statistics of student performance are an essential thing for every teacher in daily teaching. It is a window for teachers to understand and grasp students' learning situation. It is also one of the important data sources and basis for schools to evaluate the teaching quality of a class. However, the statistics of student performance is a complicated and time-consuming matter.
In order to save time in counting student scores and get twice the result with half the effort, the author came up with a once-and-for-all solution, which is to use Excel to count student scores with one click.
For this purpose, I designed two tables. The 8 worksheets from "Zhong (1)" to "You Feng" in the first table (Table 1) are to count the test scores of students in a single class (you can set the number of worksheets according to your needs), This table can be used as a final exam score statistics table or as a daily test score statistics table.
Table 1
The "General Table" in the second table (Table 2) is the school's comprehensive statistics on the test scores of a certain grade. This table can also count the test scores of students in the same grade and the same subject at the central school (for example, A5:Q8 ), you can also make statistics on the test scores of all students in the same grade and the same subject under the jurisdiction of the central school (such as A9:Q10).
Table 2
The above two statistical tables look like blank tables. In fact, the tables contain some Excel functions and custom formulas. When we enter the student test scores in the "score" column of the first worksheet, everything is OK. . So how to implement "one-click statistics"? Let's do it together!
Design statistics table
Open Excel and create the first worksheet. First, rename the Sheet1 and Sheet2 worksheets to your own defined names, such as "General Table" and "Loyalty (1)", and then click the "Loyalty (1)" worksheet label , open the "Loyalty (1)" worksheet, and design the performance statistics table according to your specific situation (special reminder: only need to design the content of the A1:V3 cell area, and the other cells do not need to be designed), such as "Title", "Serial Number" , "Name", "Achievements"...the entire design content is as shown in the first table (Table 1).
Cleverly design statistical functions and formulas
To realize statistical automation, it is necessary to set statistical functions or formulas for statistical tables. This is a crucial step. Click the "Zhong (1)" worksheet label in the statistical table to open the "Zhong (1)" score statistics table (for example: Zhongxin Central Primary School 2009-2010 school year first semester final exam score statistics table), and analyze the items to be counted The functions or formulas set for each cell in the row below the name are shown in the table below.
Beautify forms to reflect humanity
In order to beautify the worksheet, express your personality and reflect humanity, you can make the following settings for the worksheet:
1. Input error warning
Click "Data" → "Data Validity" in the menu bar, open the "Data Validity" panel, click "Settings", and set the following in "Validity Conditions": "Allow (A)" is set to: Decimal , Data settings: between, minimum value: 0, maximum value: 100.
Click "Error Warning", check "Display error warning (S) when entering invalid data", set "Style (I):" to "Stop", and set "Error Message (T):" to "Friend, you enter Wrong! Please click 'Cancel' to re-enter!", and finally click the "OK" button. If the entered data exceeds the set range, the system will automatically alarm.
2. Repeat entry prompt
In order to prevent repeated entry of students' names, we will make some suggestive settings for the column where the students' names are located, such as: first select cells B4:B63, click "Format" → "Conditional Formatting" in the menu, and open "Conditional Formatting" Format" dialog box, select "Formula" in "Condition 1(1)", enter the conditional summation function "=COUNTIF($B$2:$B4, $B4)>1" in the right input box, and click "Format" (F )..." set a background color and font format, and finally click "OK" and then "OK".
3.Hide data "0"
Sometimes there are many values in the table that are 0, which makes the table look blurry and very unpleasant. So how to hide the 0 values in the table? The method is: click to open the "Loyalty (1)" worksheet and select N4:U4 cell range, click "Format" → "Cell Format" → "Number" → "Category (C)" → "Custom" → "Type" in the menu bar, enter "Type" 0;-0;;@" (without quotation marks, "@" represents a half-width symbol), and finally click the "OK" button. The "0" that is not displayed in the table is hidden. If you want to display "0", you only need to click "Format" → "Cell Format" → "Number" → "Category (C)" → "General" → "OK" in the menu bar to hide it "0" is displayed.
Copy worksheet to create template
After making multiple settings on the first worksheet (i.e. "Loyalty (1)"), now we start to copy the worksheets of student test score statistics from other classes or schools to create a workbook: right-click the worksheet label "Loyalty (1)" worksheet in "Insert" → "Worksheet" → "OK", and then press the F4 key. Each time you press the F4 key, a new worksheet will be re-inserted, and then other worksheets will be renamed. surface.
Click the "loyalty (1)" worksheet, click the blank space where the column number and row number intersect, select all the "loyalty (1)" worksheet, click the "loyalty (2)" worksheet label, and open the "loyalty (2)" worksheet. 2)" worksheet, right-click cell A1 and select "Paste" to complete all settings of the "Loyalty (2)" and "Loyalty (1)" worksheet. In this way, continue to complete the "Loyalty (3)" worksheet. )" to the "You Maple" worksheet.
Create a "general table" worksheet, which is a summary of all schools and classes. Click the "Loyalty (1)" worksheet label, open the "Loyalty (1)" worksheet, select the F4:V4 cell range, right-click the selected cell range, click the "Copy" button, and click " "General Table" worksheet tab, open the "General Table" worksheet, right-click cell B5, click the "Paste" button, click the paste icon in the lower right corner of cell R5, click and select "Paste Options" → "Link Cell" "Grid" single option. And by analogy, copy and paste the statistical items from the "Loyalty (2)" to "You Feng" worksheets into the "General Table".
In the "Total" column of the "Total Table", the statistical formulas or functions are also set accordingly. At this point, all formulas and functions are set.
Finally, set the worksheet label, right-click the "General Table" worksheet label, left-click "Select All Worksheets (s)", right-click again the "General Table" worksheet label, and left-click "Worksheet" Label color (T)..." Just set your favorite color to reflect your personality.
One-click statistics, once and for all
After passing the above settings, save all worksheets, and the file type can be "template (*.xlt)". In the future, you only need to change two places to get it done once and for all: the first is the worksheet label; the second is the eugenics score standard in the eugenics number and eugenics rate functions (for example: the eugenics standard for the first and second grade of elementary school is 90 points), we only need to change "= Just change "80" in "COUNTIF(C4:C63,">=80")" to "90", and draw inferences from this.