Excel function quickly arranges student seating by height data

This paper mainly introduces how to use Excel to sort the data by using the functions of row and if , so as to quickly complete the sorting work of all kinds of seating chart and work arrangement of enterprise employees.

School is almost one months, at this time many schools will be based on height and other factors to the students to rearrange the seats . It is usually required to keep the tall man at the back and the dwarf in front. It used to be that students lined up in rows and queues, and the students who were standing in line were often arguing. This year may wish to change the way, first according to the student height randomly arranges out the seating chart, then lets everybody according to the chart to take a seat, thus has nothing to contend for. However, hand-row seating workload is also very large, or let Excel and function to help fix it.

　　1. Make Student record form

Open Excel2007, rename the Sheet1 worksheet to student records, and set up the table as needed (Figure 1). Enter the school number, name, height, or copy from the existing form in the C:e column. In the H, I column input height and coefficient table, this H2 fixed to 1, the following 157, 168 can be freely modified. can also add a few more height and coefficient, but height to ascending arrangement, the coefficient descending. Students with severe myopia can enter a vision factor of 1 or 2 in column F to give him a bit of a front line.

In A2 cell Enter Formula =row ()-1 automatically generates ordinal number, in B2 input formula =if (C2,vlookup (e2,h:i,2) +f2+rand (),). The formula uses VLOOKUP to extract the height coefficient + vision coefficient +rand () to generate a random number with a height difference in vision. Select A2:B2 drag its lower-right corner of the black square (fill handle) down to B97, usually not more than 96 people in one class? Now that B2 is selected, click Sort on the Start tab to select Descending, and you will randomly sort by following 157 rows of front, 157-168, and 168 rows, and the visual coefficient of 1 can make it 3 in the 1 stalls.

　　2. Preparation of Seating chart

To divide the students into 6 groups (columns) For example, we have to build a "seating table" worksheet, in A3, A4, respectively, enter 1, 7, and B3, B4 set a thick border. In B3 input formula =vlookup (A3, student record! $A: $F, 4,false), double-click the fill handle to copy the formula to B4. Select the A3:C4 mouse point to its fill handle, press and hold right to drag to Q4, loosen the right button in the pop-up menu select Fill Series, you can fill out the first two rows of ordinal number and student name. Then select A3:q4 Drag the fill handle down to Q18, fill out 96 seats and serial numbers, and the student automatically appears in the seating chart by serial number. Finally, the appropriate adjustment of the row Height column width, draw a rectangle for the podium table can (Figure 2).

Note: The number of groups is different, just start to change the number of A4, example: divided into 8 groups to 9, the other operations are the same. If you are using a double table, simply delete the empty columns between the two groups as soon as all are set up so that the two groups merge together.

　　3. Decorate the seating chart

A cell with no students in the seating chart displays error values #n/a and borders so that it disappears automatically. With the A:q column selected, click Conditional Formatting on the Start tab to select New rule. In the new Formatting Rule window, select the specification type to format only cells that contain the following, and select Error in the cell value Drop-down list (Figure 3). Then click the Format button to set the font color to white in the pop-up window, and set the border to none on the Borders tab. All the way to determine the completion of the settings, no student's border and error values will automatically disappear.

The number on the left side of the seat does not need to be printed and must be hidden first. Select Column A and click the group icon on the Data tab to group. Also select the D, G, J, M and P columns separately. After the combination in the upper left corner will display 1, 2 of the button, click 1 to hide all ordinal columns (Figure 4), click 2 to restore the display number.

　　4. Automatic seat arrangement

Often a semester needs to rearrange the seats, if the students do not change, you just select B2 on the "Student Records" Worksheet select "Sort" in the "Start" tab, choose "Descending" to randomly generate a new seating chart. Even if the students have changed or need to row seats for other classes, also as long as in the "Student record" worksheet to enter the new class student's number, name, height, to individual high myopia re-enter the myopia coefficient, and then select B2 Descending sort, you can see in the "Seating chart" sheet of the random row of the seating chart.

If you need to adjust individual student seats, you can change the number of seating chart to achieve. In this case, the height of 174 Li Lili was assigned to the middle of the front row, which affected the line of sight of the student behind, and adjusted her to the left. You just click the "2" button on the seating Chart worksheet to display the ordinal column, change the serial number 7 to 10, 10 to 7, and let her swap seats with the Caixiao of the serial number 7. Remember to hide the ordinal column after modification.

Now you can print the seating chart and post it on the podium, so that the student will be seated by the drawing. Although the operation is a bit verbose, but once the setup is complete, then just repeat the 4th step to arrange the seating chart, the application is quite simple.