Using Excel to set up a notice system in order to simplify

At the end of each semester, the teacher needs to give each student parents a notice, including students, comments and other content, before we often use manual fill or print, must be tedious to students score rankings, input, the workload is very large, actually using some Excel functions and functions, Can be ranked on student performance, and can achieve mass printing notice, the following on a school's final notice printing as an example, to introduce the specific implementation methods.

Organize your grades and comments

Usually a standard parental notice, generally need to include student grades and teacher's comments, so before printing the notice, must be sorted out, start Excel2007, create a new workbook, name it, such as "2009 Notice print", in which a new worksheet, Name it "Student score Details", in which you will copy each student's score. As you also need to aggregate and rank student scores, enter the formula "=sum (B2:H2)" In the I2 cell below "Total score", and enter the equation "=" in the J2 cell below "rank" &rank (I2, $I $: $I $71) & "name", and then copy the two formulas to the corresponding cells in the following range, you can find out the total score of all students and the ranking in this class (Figure 1).

Figure 1

In addition, because you need to add a teacher's comment, create a new worksheet, name it "Teacher's comment," and then copy the student list in the student's transcript in column A of this table, and then type the teacher's comments for each student in column B (Figure 2).

Figure 2

Design Notice Print Template

Although the parental notice format may not be exactly the same in each school, but roughly the same, create a new worksheet, name it "batch print notice," and then, in the A2:k24 cell area of the table, enter the necessary contents of the parental notice, such as title, Chapeau, The results form, the comment form, and the position of the parent to fill in the comments (Figure 3).

Figure 3

Because you want to achieve bulk printing, you must add a notification number in this notification template to select a cell so that you can display different parental notices by using a different ordinal number in the cell, enter "Please enter a number" in cell L3, and then determine the M3 cell as the notification Number option cell. At the same time, in order to be in front of the students ' scores, the data of each student automatically quoted, in the notice of the students ' record list "student name" below the A12 input formula "=offset" (Student score Details! $A $, $M $3*1-1,0) & "", in the 1th course "language" The following B12 in the formula "=vlookup ($A $, student score details! $A $: $K $60,2,0)", enter the formula in B12 below "math" in the 2nd Class "=vlookup" ($A $, student score details! $A $: $K $60,3,0) " , and so on, and then enter the formula in the A14 that holds the teacher's comments =vlookup ($A $, teacher comments! $A $: $B $71,2,0) ". When you enter 1 o'clock in the M3 cell, a 1th student's parental notice is displayed in the notification template, and 2 o'clock the 2nd student's parental notice is displayed.

It is important to note that the offset, VLOOKUP function is used here to automatically refer to the student's score details according to the values in the M3 cell, and to automatically reference the corresponding student's results and comment information in the score sheet and in the comments, and the formula "OFFSET" ( Student score Details! $A $, $M $3*1-1,0) & $m$3*1 is based on the number of rows between different data, and if you are separated by 2 rows, modify it to $m$3*2.

Using control, High efficiency batch printing notice

On the basis of the design of the previous notice template, you can use the control to print the bulk of the notice of the work, in N2, N3, respectively, enter the start ordinal number, the end of the serial number, with its O2, O3 cell as the starting number of the batch printing notice, and then click the Development Tools menu, click in the Control ribbon Insert option, in the list of controls that pops up, select the command button in ActiveX control and drag it below the M3 cell.

Then right click on the button control, in the pop-up right-click shortcut menu, select the Properties command, in the pop-up Properties dialog box, the button control's "Caption" property, that is, the button title to "Batch printing notice." Then right-click the button control, select View Code, and enter the following bulk Print command in the VBA editing window that pops up:

Private Sub CommandButton1_Click ()

For i = Range ("O2") to Range ("O3")

Range ("m3") = I

Activesheet.printout

Next

End Sub

When you have finished typing, close the VBA edit box, click the design mode option in the controls ribbon to exit design mode, then select the print area for the next notice, and then enter the starting number of the O2, O3 cell that you want to print the parent notice in bulk, such as 1~50, and then click the Bulk Print Notice button , you can immediately print out the required 50 student notices.

The above batch printing is only for the notice printing, in fact, using the above methods, slightly modified, but also can be applied to some other bulk printing work areas, such as printing labels, payment notices, transcripts and so on, through this method can greatly improve work efficiency, reduce the chance of working error.