Financial management of enterprises is always ultimately wage and salary shall design, if we can master the Excel design methods and techniques of Article wages, you can quickly design more with less accurate and elegant good calculation of pay slips, or is inefficient and can not be ensure accuracy.
Features of Article wages
Let us look at what wages are the characteristics section.
1. Wages may be the first line, two lines may, depending on the wage part business needs. But the same is that every item (or a worker's wage information) has a section head, section head with the specified number of repetitive.
2. The middle of each entry has a blank line, to facilitate cutting.
Based on the above characteristics, we can tentatively make the program pay the head section:
First of all, a "pay schedule" for storage of employee payroll information, but also for the usual editing and summary.
Followed by a "wage entry table" for the reference salary information, generating in the form of wages shall present wages.
Need to use a function that can vary, there are many formulas can reach the goal, but the final goal is to choose the most efficient and easy to understand those.
Now, with two instances of the first article to pay all the Excel Xiangjie design process, and analyze the techniques, I hope we can expect to benefit, giving top priority to design their own demand for wage Excel formula.
Single wage clause head design
Look at the following data, it is a simple one-line wage information was obtained from the first section (data randomly generated, non-real wages), shown in Figure 1.
Use this table of data, we can function formula to use the data generated need to be the form of wages.
Steps with the following formula:
1. Into the "single header section wage" worksheet.
2. Select cell B1, enter the following formula:
= CHOOSE (MOD (ROW (), 3) +1, "", single header wage Details! A $ 1, OFFSET (single header wage Details! A $ 1, ROW () / 3 +1,))
3. Select cell B1, put the mouse cells in the lower right corner, when an arrow to a cross (Fig. 2)
J1 is right to pull the cell. And then selected B1: J1 to drop, until the formula resulted in a blank. Article wage effect at this time see Figure 3.
4. Basically achieve their goals, but the table has no border, print is not very beautiful. Now add the border for it: select B1: J2, and click the Borders button in the toolbar button to add borders shaped field; then click the big box button to display the payroll entries around the border thicker, more beautiful.
5. Select B1: J3 cell, drag down, until the last line of data.
Formula explanation:
= CHOOSE (MOD (ROW (), 3) +1, "", single header wage Details! A $ 1, OFFSET (single header wage Details! A $ 1, ROW () / 3 +1,))
1.CHOOSE function is to select the function to support the 30 parameters. The first parameter is the selection number (the number), and the remaining parameter is the selected value. The first parameter is N, the result returned to the rest of the first N-parameter values.
2.MOD function is to request the number of functions in support of two parameters, the first parameter is the dividend, the second parameter is the divisor, the result returns the remainder.
3.ROW function is to return the specified number of trekking function, if the parameter is omitted, returns the current row.
4.OFFSET function is a function to return the offset. Support 5 5 parameters, respectively [reference to the region], [rows], [columns], [height], [width].
5. "", Said blank, to return empty.
The clever use of the formula MOD and ROW functions to produce a cycle sequence 2/3/1/2/3/1/2/3/1, and then through the CHOOSE function parameters dynamically change the data referenced wage schedule, which "" The role is currently No. 3 in multiples of trekking returns empty, resulting in a blank line to facilitate the production of the wage cut after the article.
Of course, there are many formulas to achieve functions such as IF function with the following realization, you explore it more user to:
= IF (MOD (ROW (), 3) = 1, one-line header wage Details! A $ 1, IF (MOD (ROW (), 3) = 2, OFFSET (single header wage Details! A $ 1, ROW () / 3 +1,0 ),""))
Article wages double head design
Double-section and single section head shall pay the first implementation method is basically the same, just some differences in the formula. Are behind in the following:
= CHOOSE (MOD (ROW (), 4) +1, "", double header wage Details! A $ 1, double header wage Details! A $ 2, OFFSET (double header wage Details! A $ 1, ROW () / 4 +2,))
Enter the formula again after the pull back down to the last line, then the method described above to set the border, some of which cells need to remove the left border or right border, make it look beautiful.