Office hacks: Excel helps students arrange random exams

Nowadays, elementary school students often take mapping exams. And during the test, it is required that multiple classes in the same grade are mixed, and each student cannot have the same class in front of or behind him/her to prevent children from cheating. In addition, the list is different for each test to prevent students from different classes from cheating because they are familiar with each other. So, how to use Excel to achieve the above conditions to meet the students’ examination arrangements? Next, the author will share a little of his experience with you.

Generation of random student list

Before you start to do some preparatory work: first create an Excel spreadsheet (this example is “fourth grade roster.xls”, Figure 1), and then create a workbook for each class, and rename the class, and then all the students in each class “student class “, “student name” and “student number” of all students in each class, and then copy them into the corresponding workbook for backup.


Next, generate different random natural numbers. Create a spreadsheet named “random not renamed.xls” (Figure 2), in cell C1, enter the function “=RAND ()” (excluding the outermost quotation marks, the same below), the cell’s font color is set to white. Then select cell C1, and drag the fill handle to cell C69, and finally in cell A1, enter the function “= RANK (C1, $C$1: $C$69)”, drag the fill handle to cell A69, in order to obtain 69 different natural numbers.



★ function = RAND (), you can generate a random number greater than 0 and less than 1. The function “=RANK(C1,$C$1:$C$69)” entered in cell A1, is the random number in cell C1 in C1 to C69 all random numbers, according to the order from largest to smallest, ranking a few, the corresponding value will be returned to cell A1. Then by filling the way, you can get C2……C69 random numbers in each cell, in C1 to C69 all the random numbers in order from largest to smallest ranking situation, in this way, A1 ~ A69 cells to produce a natural number of 69 different sizes.

★ function “$C$69”, that a grade with the largest number of students in the class, there are 69 students, if more than 69, please change yourself.

Now sort the students. Select all the data in cells A1 to A63 shown in Figure 2 (assume there are 63 students in a class) and copy it down. And then paste it into the D2 to D64 cells shown in Figure 3, and then select the cells in column D, click the menu “Data → Sort”, so that the fourth grade class of all students to randomly disrupt the list (Figure 3).


Close the “random not renamed.xls” and save the changes made to it, and then reopen it, you can find the data in the 69 cells A1 ~ A69 and re-generated randomly once and different, and then according to the number of second class (such as 62 people), copy the data in the corresponding cells A1 ~ A62. Then paste it into the workbook “Class II” shown in Figure 3, cells D2 to D63, and sort the students in Class II to achieve the purpose of random disruption. Then follow this method, the four classes of “Class 3”, “Class 4”, “Class 5” and “Class 6” will be Finally, you can save the changes made to “random-not-renamed.xls”.

One-click student scheduling and data filtering

From下载笔者所提供的, open the “Exam Room Schedule.xls”. Then copy the information of “Student Class”, “Student Name” and “Student Number” of each workbook as shown in Figure 3 and paste them into “Exam Room Arrangement.xls” spreadsheet “Student Information” below the blank cells (Figure 4, column A, B and C of the student information), and then set the number of candidates assigned to each class (such as 64) in cell S2 shown in Figure 4. Finally, click the button “Schedule”, and you can immediately complete the work of the entire grade of students randomly arranged for the examination room.


In addition, in the screen shown in Figure 4, click the drop-down arrow to the right of “Exam Room” and select “Exam Room 4-1” to view the list of all students taking exams in “Exam Room 4-1”. You can view the “name”, “student number” and “seat number” of all students taking exams in “Exam Room 4-1”. Similarly, click the drop-down arrow to the right of “Class” and select “4_1” to see the distribution of all students in the fourth grade class. Print out the distribution of each class and distribute it to the classroom teachers, and print out the distribution of candidates in different classes in each examination room and distribute it to the invigilators of the corresponding examination rooms.

Leave a Comment