Excel batch generation and printing of Examination seat label Examples Tutorial

In order to make all kinds of examination organization work smoothly, let the examinee smoothly to find own examination room seat sit down quiet Tenkau is very important. It is an essential job to print a seating label for an examination room. Many friends are using Excel to carry on the examination management work, that how to use Excel to realize the batch seat label printing? In order to facilitate the introduction, the next author to 4 classes of examinee data as an example to introduce the implementation process.

Generation of candidates ' roster

According to the items that need to be printed in the seating label, the author has designed the "candidate roster" worksheet. In order to reflect the fairness of the examination, we can in advance the examinee according to the class number "1~4" cycle of the Order, "seat number" can also be based on the size of the examination room designed as "1~30" circulation number, that is, 30 candidates per examination hall.

Design of seating label report for examination Room

The design of seating label should be based on the principle of beauty, practicality, saving paper and easy tailoring, and the author has designed 30 seat labels on a A4 printed page (1 lines 3, a total of 10 lines, the following figure).

The design of the report is done in the table-paste worksheet. You can start by designing a seat tag and then testing the data that you want to call in the label repeatedly. The method of calling the data is (taking the seat tag of the 1th candidate as an example), in the 1th Examinee's "Ticket number" in the B3 cell, enter the formula "=indirect" ("Candidate roster!"). A "&ceiling ((ROW ()-1)/4,1) *3-2+ceiling (COLUMN ()/5,1) -1+ ($P $2-1) *30+2)" Then got the examinee's ticket number.

The function of the indirect function in the formula is to return the reference within the specified cell (that is, the ticket number in the cell of the Candidate Roster worksheet A3), and the "CEILING ((ROW ()-1)/4,1) *3-2+ceiling in the formula (COLUMN ()/5,1)-1 + ($P $2-1) *30+2 "The value obtained is" 3 ", the purpose of this formula is to specify that the range of cells in which the candidate's label is located (" A2:e5 ", that is, 4 rows, 5 columns) is returned in the same number, in order to be in the remaining" name "," Class "," Examination room "and" seat " Cells in the same function to implement the call to the examinee data, this function is mainly through the ceiling function, the function is to the specified number of values rounded up to the nearest integer.

You can then use the same formula to invoke the candidate's "name", "Class," Examination room "and" seat "and other cells in the data, you can simply change the specified column A in the original formula" B "," C "," D "and" E "column on it. It is also necessary to note that because of the large number of candidates, the data call also takes into account when printing more than one page, so at the end of the formula added "($P $2-1) *30+2" ("Candidate roster" table has 2 row headings, so to add "2"), the formula "P2" The cell is a manually set cell for entering the specified page number, which makes it easier to display and print any page label.

The range of cells that you want to print is specified in the print area of the Page Setup dialog box (shown below) in addition to the auxiliary units in the design's table-top sheet.

The design of printing function

In order to facilitate the printing of all candidate seat labels or a specified page label, the author has designed the following two sections of VBA code to assist in the implementation of these two functions: in the table paste worksheet, use the command button on the Control Toolbox toolbar to draw two button controls "CommandButton1" and " CommandButton2, change their "Caption" property to "Print all pages" and "Print the specified page" respectively. Double-click the command button "Print all Pages" and enter the following figure in the Click () event.

Close the code window to return to the Excel worksheet and click the Print all Page button to print all the seat labels on the exam. Click the Print specific page button to enter the page number you want to print as prompted, and the system automatically determines whether the specified page number exceeds the page number range, or if the specified tab is printed within the specified page range, the error message is given.

Through the above operation we can in Excel to achieve the examination room seat label printing, and once and for all, only need to be the candidate's list and other basic information to make some changes can be directly printed, is not very convenient?