How to make use of WPS to produce a score sheet and a statistical score sheet

The statistics student's achievement is one of the teacher essential work, each class student so many, then how can we be most accurate and effective to statistic the result? This time we will explain how to use the WPS table to calculate the students ' test results quickly and accurately

　The WPS table statistics operation steps are as follows:

① Select M2 Cell, click the Insert Function button, and in the list of commonly used formulas, select multiple conditional sum, where the range to sum is the class, condition 1 is the class equals 1, Condition 2 is not less than 100 for the language, and then click Insert Formula.

Because the result is the "and" that satisfies the condition, and what is expected to be the number of conditions, the formula "Sumproduct" ($A $ $A $650,1* ($A $ $A $650,0,sum ($A $: $650) -column ($A $ $A $650)) =1) * (OFFSET ($A $ $A $650,0,sum ($B $ $B $650)-column)) "($A $ $A $650))"

Modified to "= (Sumproduct ($A $ $A $650,1* (OFFSET ($A $ $A $650,0,sum ($A $: $A $650)-column ($A $: $A $650)) = $L 2) * ( OFFSET ($A $: $A $650,0,sum (COLUMN (b$2:b$650)-column ($A $ $A $650))))/$L 2 ", that is, the number of meeting conditions (1 of the class language score is greater than 100 points, Note the relative references in the formula and the use of absolute references.

② Select cell M2, automatically fill to P2 cells to the right, and then modify the parameters of the formulas in each cell (that is, the fractional segment to be counted, such as physics is >=80), and then select (M2:P2) The range of cells, automatically fill down, you can quickly count the language, mathematics, English, The number of students in each class in the physics discipline.

　1, the production of performance table

First open the WPS table to make a record sheet, this relatively simple to do not elaborate. Then add another 4 columns to the right of the record table (J:M). and enter the column headings as shown (Figure 1).

Select J1 Click Data/Effectivity, and under the Settings tab of the Data Validation window, click Allow Drop-down list to select sequence, enter formula = $C $1:h$1 in source, and determine completion settings. Select the K column right click Select Format cells, in the Format Cells window, in the Number tab, in category, select text to make sure that the K column is formatted as text.

　2. Set the formula

Select the J2 cell and enter the formula =if (ISERROR (VLOOKUP (A2,l:m,2,false)), "", VLOOKUP (A2,l:m,2,false)). The formula indicates that the A2 number in the L:m finds and displays the corresponding score, and appears empty when an error is not found. In the L2 input formula =value ("2007" &left (k2,3)), the formula extracts the K2 data from the left three-digit number and adds 2007 to the front, and then converts it to a numeric value. Since the previous section of the classmate's school number is generally the same, in order to speed up the input speed we set to only enter the last three digits of the school number, then the L2 formula will automatically add the number of the same part "2007" to the previous number to display the full number, if necessary can also be set to enter only two-digit number. Then, in M2 input formula =value (MID k2,4,5), the K2 data is extracted from the 4th digit 5 digits (i.e. fractions) and converted to a numeric value. Of course if the number after the number of less than 5, the computer will be smart to extract only the actual few. Finally select the J2:l2 range and drag its fill handle down to copy the same number of rows as the student record (Figure 2).

Note: The value function is used to convert the extracted text to a numeric value. If the number of characters in addition to Arabic numerals, for example: 2007-001 or LS2007001, the study number will automatically become a text format, at this time the formula L2 no longer need to convert to a value should be changed to = "LS2007" &left (k2,3), otherwise the value function will be wrong.

　　3. Prevent input errors

A lot of input will inevitably have input errors, select the K column click the menu "format/conditional format", in the Conditional formatting window under Condition 1 Drop-down button Select "Formula" and enter Formula =l1=2007, do not format. Then click the Add button, add Condition 2, set the formula to =countif (L:L,L1) >1, click the Format button later, and set the shading to red in the Pattern tab of the Format window to determine the completion setting (Figure 3).

This changes to a red display when there are two identical school numbers in the L column. By the preceding formula, the L column will appear as "2007" when K is empty, so the preceding condition 1 is not formatted when l1=2007, just to avoid the repetition. In addition, if you are worried about the score input error, you can add another condition 3, set the formula to =m1>120, the color is yellow, you can enter a score of more than 120 when the yellow reminder.

　4. Fast Entry Score

OK, to this complex setting has been completed, when you need to enter a result, just click the J1 cell Drop-down button, select the column heading you want to enter from the Drop-down list (for example: midterm), and then select K2 Enter the first student's number and score 00159, carriage return, rear L2, M2 will show the student's number 2007001, score 59, and the score will automatically fill in the J-column cell with the school number 2007001 lines. As long as we repeat the input number score, enter, you can easily complete all the students record entry. If the number repeats when you enter, the cells you enter and the cells they repeat are displayed in red, reminding you that the input may be wrong (Figure 4). Since the number and results are entered in the order of the test paper, you can easily turn out the wrong input for the test paper proofing even if you enter the error.

When all input is complete, then select the J column for replication, and then select the "midterm" results in the E column, right-click Select "Paste", in the pop-up window, click to select the "Value" single option, to determine the J-column scores in a numerical way to paste into the E column to complete the score entry.