Excel function to make a salary bar format of the score notice

The class teacher work complex trivial, each to each kind of examination, to the student feedback result is one must complete thing. Now do not advocate all students to play all the results and directly posted, but also to let students know their results as soon as possible, so choose the "performance notice" form to students.

"Performance Notice" in the format is a bit like a payroll, easy to send students, let students understand their own a period of time harvest. Below we introduce the methods and techniques of using the Excel software ranking function rank () and the filtering function to easily organize the student transcript of the payroll format.

　　Calculate rank with rank () function

First of all, we have to calculate the ranking of students in each section and the total score in the class, so that students know their grades and total score in the class position. The calculation process uses the Rank function (Figure 1).

Enter =rank (B2,B:B) in the C2 cell to get the position (rank) of the B2 value in column B. Enter the lower right corner of the C2 cell after the small black dot, you can complete the class ranking of all students. The same way to complete other sections of the ranking. The entire form is then sorted by name so that the student knows the next one may be him and save time. Note that many class teachers sort the overall score from high to low, then fill in the columns 1, 2, 3 ... in the right column.

　　Add header row

First, you cut the title bar line to the end and insert the column to the left. Then in the A1, A2, respectively, enter 2, 5, and select the two cells, double-click (or pull down) A2 cells in the lower right corner of the small black dots to achieve arithmetic progression "2, 5, 8 ..." Fill.

The same way to achieve the title bar before the series "1, 4, 7 ..." of the fill, the same number of fills as last. and select all the title bar (excluding the leftmost number), with the mouse to select the lower right corner of the small black dots, pull down to achieve the title bar copy. In the end (after the title bar) in the same way to achieve the series "3, 6, 9 ..." of the fill, fill the same number as last, and each row only fill the series. They are then sorted from small to large by the number of columns they fill. To do this, complete the addition of the header row (Figure 2).

　　Add a Table box and page settings

Select the entire form (be sure to select the entire form), main menu select "Data" → "Filter" → "AutoFilter", "name" in the column select "Not blank", then select the text and add a table box to the text (No previous form box) (Figure 3).

You can also select blank in the Name column to adjust the line spacing between the score bars. Finally, remove the filter by selecting "Data" → "Filter" → "AutoFilter", deleting the column A and setting the margins. In this way, we can get the score bar we want (Figure 4).