How to make a pay bar with Excel

How to use Excel to make a pay-bar method of using Excel to make the payroll, perhaps some netizens have been using for a long time, but how to make a beautiful pay bar faster and better, see the author's method.





"Small problems to the old financial", perhaps you have been working in the financial mouth for many years, maybe you have to face Excel every day, maybe you have been accustomed to using the salary list to do employee payroll statistics and report to the superior, the table of the items in a copy and paste after printing to each employee. But have you ever thought of finding a simpler and more efficient way?  It seems to have some difficulty: in the salary schedule, the salary item (that is, the header) usually only appears at the beginning of a line in the table, and in the payroll, in order to facilitate the reading of the requirements of each person's salary has a head, then how cleverly to make the salary sheet into a convenient printing of the pay bar?





"All roads lead to Rome", but the road is far and near, the method is also difficult to easily, here to provide you with three of the easiest way to use.





method One: Macro command control Method





may have heard of the Office family's macro functionality, but because of the need to use VBA for programming, macros have been daunting to many people, but it's much simpler to use a ready-made macro. Open the Excel table you want to work with, select the tools → macros →visual Basic editor, double-click Sheet1 in the window to the left of the editor, the Code editing window appears, and the Code Editor window enters the following code (in order to not break the original payroll, so here's the use of the The Sheet1 content is copied to the Sheet2 method, so the final build result is displayed in Sheet2):





Sub makesalarylist ()





Dim I as Integer





Dim Endrow as Integer





the last line of data





Endrow = Sheet1.range ("a65536"). End (Xlup). Row-1





the title over





Worksheets (1). Range ("1:1"). Copy (Worksheets (2). Cells (1, 1))





for i = 3 to Endrow





each piece of data to the head





Worksheets (1). Range ("2:2"). Copy (Worksheets (2). Cells (3 * i-7, 1))





put the data over





Worksheets (1). Range (Cells (i, 1), Cells (I, 256)). Copy (Worksheets (2). Cells (3 * i-6, 1))





Next i





End Sub





closes the editor, will automatically return to just Excel table, select "tool → macro → macro", will pop up the following dialog box:





Click "Execute", will be generated in Sheet2 such as Gz-2 shown in the conversion into the payroll, how, not complicated bar. Of course, if your table total Sheet2 has entered other content, you can copy him to Sheet3 to generate, code modification is also very simple, the Sheet2 modified into Sheet3 can be, other code unchanged.





Method II: Formula Filling method





compared to macro commands, formula filling method easier to understand, but the need for manual operation of a little more, "fish and bear paws can not be both", to use which method depends on your hobby.





first Open the Excel payroll that you want to manipulate, and in order to not break the original table (SHEET1) structure, we still use the method of operating in Sheet2. Because this payroll is a total of l columns, 18 rows, the table header item to be copied is in the second row, so enter the following formula in the first cell:





=if (MOD (Row (), 2) =0,index (sheet1! $A: $L, INT (((ROW () +1)/2)) +2,column (), sheet1! A$2)





the next job is simple, we just fill the formula with the fill handle to the other cells, column to the L column, and the row is filled to 18 rows.





because this formula needs to be modified in a different table, so here's a little explanation:





because the odd line in the payroll is the header, even row is the data, so in this formula first of the odd and even row, if odd rows, directly to the wage table A2 cell data (that is, the sheet1! in the formula) A$2, if the table header data is changed to sheet1! in the third column of row 4th c$4). If even rows, use the index () function to fetch the number. The first parameter of the function is a range of ranges in the specified payroll (that is, the sheet1! $A: $L, if it is not from A to L column, you can modify this parameter, such as modifying to sheet1! $B $p, which means to take a number between B and P columns. Of course, if you want to put the converted data in Sheet3 instead of Sheet2, then just do it in Sheet3, and you don't need to change the content of the formula.





Use this method can not automatically insert blank lines, to print after the cut caused a certain amount of trouble, therefore, we recommend that after the full selection of all cells, by adjusting the row height and column width to solve this problem.





method Three: Word mail merge method





to macro and formula use not very skilled friends don't worry, there is still a trick waiting for you.





First we determine the main document (payroll table) and data source (Excel or Access format Records), and then use the Mail Merge Wizard to merge the field information from the data source.





Click "tools → letters and mail → mail Merge" and then go to the Mail Merge Wizard on the right side of the screen.





First step: Select a document type. Select "Letter".





The second step: Select the start document. Select the default use current document.





Step three: Select Recipients. Click the browse button in the Use existing list area to locate the location of the format data source by selecting the Data Source dialog box, and then selecting and turning it on. It then pops up the Mail Merge Recipients dialog box, where you can specify the records that participate in the mail merge, select Default all, and make sure you return to the Word editing window.





Fourth Step: Write a letter. To locate the insertion point in the second row of the table, click the Insert Field button on the Mail Merge toolbar, to the left of the Insert Word field, open the Insert Merge Field dialog box, select the Ordinal field in the list box below field, and click the Insert button to merge the ordinal field into the main document. Then use the same method to insert the rest of the fields into the main document table in the corresponding position.





Step Fifth: Preview the letter. Here we can look at the general effect of the pay bar, you can also adjust the width of the "name" table, so that the name on one line display. Then select the table area after name, right-click the pop-up menu and choose Distribute Columns evenly so that the columns are the same width and make the pay bar more attractive. If this goes directly into the print operation, a sheet of paper can only print one pay bar, so select the entire payroll form, copy it, paste it into a blank line below the original table, place the insertion point in the blank row between the first and second tables in the main document, and click the Insert Word Field button on the Mail Merge toolbar. Select the next record command in the pop-up menu, and so on, and so on, you can arrange a few more payroll tables on one page and make the most of your resources. Excel Tutorial





finally enters step sixth: complete the merge. Then print out the payroll, and it's done.





OK, no longer laborious duplication of labor to create a payroll data table, through a simple transformation of a data table to achieve two completely different functions, quickly drill it.