How to use the WPS form for a separate class

method of using WPS form to divide classes

1. Assigning students

1.1 Preparing the data

Start the WPS form and make the table header in Figure 1.

Enter or copy and paste the data containing the name, gender and academic records into the table. To get the total score, drag the c3:j202 area first, and then click the Sum button "∑" on the toolbar.

Drag and choose the a2:j202 area, perform the data → sort command, and click the right triangle arrow in the key key in the Open dialog box. Select Total score in the Drop-down list, select Descending on the right, select "Sex" in the secondary keyword drop-down list, select Ascending on the right, and click OK , complete the sort. See Figure 2.

In G3, G4 cell, enter "1", "2", drag and choose two cells, the mouse placed in the lower right corner of the area, a small "ten" word, drag the mouse to the G202, release the mouse, the serial number is automatically populated. At this time the ordinal number and rank basically consistent, only a few students are the same score different rank, such as F4, F5 cell.

1.2 Rounds mode

The students are divided by the end-end phase closed cycle. First of all, boys, the first 16 students to 1, 2, 3, 4, 4, 1, 2, 3, 3, 4, 1, 2, 2, 3, 4, 1 classes. Let's try to analyze it. Because it is divided into 4 classes, the first small assignment, the male 1th to 4th students to 1, 2, 3, 4 classes. Because the distribution of the most "loss" is 4 classes, followed by 3 classes, again is Class 2, so the next 3 small assignments have priority of 4 classes, 3 classes, 2 classes, that is, the 5th to 8th students to 4, 1, 2, 3 classes, 9th to 12th students to 3, 4, 1, 2 classes, The 13th to 16th students were in 2, 3, 4 and 1 classes respectively. In this way, the 4-time small distribution is composed of 1-phase closed loop. Girls are distributed in exactly the same way as boys, but in the opposite direction, they are "4, 3, 2, 1, 1, 4, 3, 2, 2, 1, 4, 3, 3, 2, 1, 4". If the allocation of boys, some classes, and in the allocation of girls, it happens to make a seamless remedy. In this way, not only the students ' grades are more balanced, but also the boys and girls have a reasonable match.

1.3 Input Formula

H3=mod ((Row ()-row ($A $), 4)-int ((Row ()-row ($A $))/4), 4 +1.

I3=4-mod ((Row ()-row ($A $), 4)-int ((Row ()-row ($A $))/4), 4.

J3=if (b3= "Men", VLOOKUP (COUNTIF ($B $3:b3, "Men & quot;), g:i,2), VLOOKUP (COUNTIF ($B $3:b3," female "), g:i,3)).

In the formula for cell H3, Row () represents the row number of the active cell. Row ($A $), 4) represents the line number of the row on line 1th of the data range, which is the A3 cell. MOD ((ROW ()-row ($A $), 4) represents the difference between the row number of the active cell minus 3 and the remainder of the "4" class. The int function represents rounding the end of a value. The entire formula is used to determine the starting point for the first class as a cycle-splitting student. If you change the "4" in the formula to "N", it is a general formula.

In the G3 cell formula, the IF function is a function that represents the logical detection; The VLOOKUP function is used to find the specified value in the first column of the datasheet, and this returns the numeric value at the specified column in the current row of the datasheet, countif the specified condition count. The formula means that if the cell B3 is "male", then according to the "male" sex number in the H to J area search for the first "2" column, that is, the H column represents the value of Ban Xu, otherwise, according to the "female" sex number in the H to J area to search for the "3" column, that is, the I column represents the Ban Xu

1.4 Fill Formula

Drag the H3:j3 area, the mouse placed in the lower right corner of the area, there will be a small "ten" word, drag the mouse to J202, release the mouse, the formula is automatically populated to the h4:j202 area.

1.5 Data fine-tuning

Because it is 4 classes, so a closed cycle is 42 people, that is 16 students. Although the total number of men and women is the number of classes "4" of the whole multiple, but the number of boys or girls is not necessarily 16 of the whole multiple, so, according to the above-class, the number of classes may be unequal. In addition, there may be some problems, such as the imbalance of discipline, the concentration of backward discipline, the convergence of students ' intelligence and so on, which can decide whether to fine-tune.

The following is an example of adjusting the discipline score to illustrate the fine-tuning process.

Copy the a2:j202 area data, place the mouse over the A1 cell in the other table, and perform the edit → paste selective → value command. The data is then sorted by "split class" as the "primary keyword". Then execute the data → subtotal command, and in the Open dialog box, in the Drop-down list in category fields, select Split class. In the totals by Drop-down list, select Average, and in selected totals, select language, Math, English, and total score. See Figure 3.

After this operation, the top left corner of the worksheet appears with the words "123", and clicking "2" shows the average score for each class and grade. See Figure 4.

Observe this example each class "total score" the average score, found that 2 classes of grades inferior to 4 classes. Why does this happen when the class is so carefully divided? As long as the "class", "Count", "class" in the way of the subtotal, you will find that 2 classes, 4 classes are 51 people, 49 people. Originally, in the rounds of classes, "tail son" students in Class 2 more "round" 1 times. In addition, 1 classes and 2 classes, language, maths strong and weak English.

After discovering these problems, then go back to the first subtotal interface, click on the top left corner of the worksheet "3", expand the class data, in the table to increase, reduce line operation, a cut, paste, the students make appropriate adjustments.

2. Prizes Publicity

After the data has been adjusted, some forms of processing will be made to prizes the class list, to receive the supervision of students, parents, teachers and the community, and to increase the transparency of the classes. Students do not know the teacher's situation, generally do not have a big mood.

3. Deployment of Teachers

The essence of "selective heat" is "choosing a teacher". After the end of the class, the school should be in accordance with the teacher's teaching ability, experience, performance, age, health, personality and so on, reasonable and evenly carry out teacher strength collocation, the class teacher draw lots to determine the frequency.