How to make a payroll? The requirements are very detailed

Name, post salary, seniority allowance, skill allowance, performance salary, health fee, night shift allowance, safety award, overtime salary, supplementary salary, salary payable, pension insurance, medical insurance, unemployment insurance, housing provident fund, other insurance, income tax base, personal income tax, other deductions, actual salary and payee's signature.

Among them, salary payable = post salary+seniority allowance+skill allowance+performance salary+health care fee+night shift allowance and safety award+overtime salary+replacement salary.

Income tax base = salary payable-endowment insurance-medical insurance-unemployment insurance-housing accumulation fund-other insurance.

Actual salary = Payable salary-endowment insurance-medical insurance-unemployment insurance-housing accumulation fund-other insurance-personal income tax-other deductions.

The Secret Skills of Excel

First, create a category drop-down list to fill in the project.

We often need to enter the company name into the form. In order to keep the names consistent, we use the "data validity" function to build a category drop-down list to fill in the entries.

1. In Sheet2, fill the enterprise name in different columns according to categories (such as "industrial enterprise", "commercial enterprise" and "individual enterprise"). ) Establish enterprise name database.

2. Select column A (where the name of "industrial enterprise" is located), enter the character of "industrial enterprise" in the column of "Name" and press Enter to confirm.

Imitate the above operation and name columns B and C as "commercial enterprises" and "individual enterprises" respectively.

3. Switch to Sheet 1, select the column (such as column C) where the enterprise category needs to be entered, and execute the data → validity command to open the data validity dialog box. In the Settings tab, click the drop-down button to the right of Allow, select the "Order" option, and in the "Source" box below, enter the order of "Industrial Enterprise", "Commercial Enterprise" and "Individual Enterprise" (the elements are separated by English commas), and confirm to quit.

Select the column (such as column D) where the enterprise name needs to be entered, and then open the data validity dialog box. After selecting the series option, enter the formula = indirect (C 1) in the source box and confirm to exit.

4. Select any cell in column C (such as C4), click the drop-down button on the right, and select the corresponding "enterprise category" to fill in the cell. Then select the cell corresponding to this cell in column D (such as D4), click the drop-down button, select the required enterprise name from the list of enterprise names in the corresponding category, and fill it in this cell.

Tip: If you don't need to print the column of "Enterprise Category" when printing the report in the future, you can select the column, right-click and select the "Hide" option to hide the column.

Second, create a new "General Document" menu.

Create a new "Frequently Used Documents" menu on the menu bar, and add frequently used workbook documents to it for easy calling at any time.

1. Click the right mouse button in the blank space of the toolbar and select the Customize option to open the Customize dialog box. In the Commands tab, select the new menu item under Category, and then drag the new menu item under Commands to the menu bar.

Press the "Change Selection" button and enter a name (such as "Common Documents") in the "Naming" box of the pop-up menu. 2. Select an item under the category (such as insert option) and an item under the command on the right (such as hyperlink option), drag it to the new menu (common document), and name it as above (such as payroll) to establish the first workbook document list name.

Repeat the above operation and add more document list names.

3. Select a menu item (such as payroll) from the common document menu, right-click and select the option of Assign Hyperlink → Open in the pop-up shortcut menu to open the Assign Hyperlink dialog box. By pressing the drop-down button to the right of "Look in", find the corresponding workbook folder (such as "Salary"). Xls "and so on. ) and select a workbook document.

Repeat the above operation to hyperlink the menu item with its corresponding workbook document.

4. When you need to open the workbook document in the common document menu in the future, just expand the common document menu and click the corresponding option.

Tip: Although we dragged the Hyperlink option to the Common Documents menu, it did not affect the function of the Hyperlink menu item in the Insert menu and the Insert Hyperlink button on the Standard toolbar.

Third, let different types of data be displayed in different colors

In the payroll, if the total wages of more than 2000 yuan are displayed in red, the total wages of more than 1500 yuan are displayed in blue, the total wages of less than 1000 yuan are displayed in brown, and the rest are displayed in black, we can set it like this.

1. Open the payroll workbook, select the payroll column, and execute the format → conditional format command to open the conditional format dialog box. Click the drop-down button on the right side of the second box, select the "greater than or equal to" option, and enter the value "2000" in the following box. Click the Format button to open the cell format dialog box and set the font color to red.

2. Press the "Add" button, and other conditions are set as above (1500 or above, and the font is set to "blue"; Less than 1000, and the font is set to "brown").

3. After setting, press the "OK" button. Look at the payroll. Is the payroll data displayed in different colors according to your requirements?

Fourth, make a "professional symbol" toolbar.

When editing professional tables, we often need to input some special professional symbols. In order to facilitate input, we can make our own "professional symbols" toolbar.

1. Execute the command "Tools → Macros → Record New Macros", open the "Record New Macros" dialog box, and enter the macro name? Such as "fuhao 1" and save the macro in the personal macro workbook, and then "OK" to start recording. Select the "relative references" button on the "Record Macro" toolbar, then enter the required special symbol in the cell, and then click the "Stop" button on the "Record Macro" toolbar to complete macro recording.

Imitate the above operation and record the input "macros" of other special symbols one by one.

2. Open the custom dialog box, click the New button in the toolbar tab, and the new toolbar dialog box will pop up. Enter the name-professional symbol, and a toolbar will appear in the workspace.

Switch to the Commands tab, select Macros under Category, and then drag the Custom Button item under Commands to the professional symbol bar (drag any number of special symbol buttons).

3. Select one of the "custom buttons" and name it after the 1 point of the second secret skill.

4. Right-click a naming button, select the "Specify Macro" option in the pop-up shortcut menu, open the "Specify Macro" dialog box, select the corresponding macro (such as fuhao 1), and confirm to quit.

Repeat this step to link the button with the corresponding macro.

5. Close the "Customize" dialog box, and you can use the "Professional Symbol" toolbar to quickly enter professional symbols in cells like a normal toolbar in the future.

5. Use View Manager to save multiple printed pages.

Some worksheets often need to print different areas, so use the View Manager.

1. Open the worksheet that needs to be printed, drag the rows (or columns) that don't need to be printed with the mouse, select them and click the right mouse button. In the shortcut menu that appears, select the Hide option to hide the rows (or columns) that don't need to be printed.

2. Execute Command View → View Manager, open the View Manager dialog box, click Add to open the Add View dialog box, enter a name (such as uploading a report), and click OK.

3. Show hidden rows (or columns) and repeat the above operations to "add" other print views.

4. When you need to print a form in the future, open the View Manager, select the name of the form to be printed, and click the "Display" button, and the worksheet will be displayed immediately according to the preset interface. Simply set the typesetting, press the "print" button on the toolbar, and everything will be OK.

Sixth, let the data sort on demand.

What if you want to sort employees by department, and the information about the names of these departments is not sorted in pinyin order or stroke order? You can sort by using a custom sequence.

1. Execute the "Format → Options" command, open the "Options" dialog box, enter the "Custom Series" tab, and enter the series sorted by department (such as "Agency, Team, Workshop 1, Workshop 2, Workshop 3", etc.). ) In the box under Input Series, click Add and OK to exit.

2. Select any cell in the "Department" column, execute the "Data → Sort" command, open the "Sort" dialog box, click the "Options" button to pop up the "Sort Options" dialog box, press the drop-down button in it, select the series just defined, and press the "OK" button twice to return. All data will be sorted as required.

Seven, completely hide the data.

The contents of some cells in the worksheet don't want to be seen by visitors, so we must hide them.

1. Select the cell (range) that needs to be hidden, execute the command format → cell, open the cell format dialog box, select the custom option under category in the number tab, and then enter "; ; ;" (English three semicolons).

2. Switch to the Protection tab, select the Hide option, and then press the OK button to exit.

3. Execute the "Tools → Protection → Protection Sheet" command, open the "Protection Sheet" dialog box, set the password, and click "OK" to return.

After this setting, the contents of the above cells are no longer displayed, and even the transparent function of Excel cannot make them visible.

Tip: Under the "Protection" tab, please do not clear the "∨" sign in the check box before "Lock" to avoid others deleting your hidden data.

Eight, let Chinese and English input methods appear intelligently.

When editing a table, some cells need to enter English and some cells need to enter Chinese. It is really inconvenient to switch input methods repeatedly. Why not set it up and let the input method adjust intelligently?

Select the range of cells that need to input Chinese, execute the data → validity command, open the data validity dialog box, switch to the tab of input method mode, press the drop-down button on the right side of the mode, select the open option, and click OK to exit.

The Chinese input method (Chinese input methodNo. 1 in the input method list) will automatically open when any cell in the cell range that needs Chinese input is selected in the future, and will automatically close when other cells are selected.

Nine, let AutoCorrect input a unified text.

Do you often worry about entering some fixed text, such as computer newspaper? Then look down.

1. Execute Tools → AutoCorrect to open the AutoCorrect dialog box.

2. Enter "pcw" in the box below "Replace" (it can also be other characters, and "pcw" is lowercase), enter "Computer Newspaper" in the box below "Replace with", and then click "Add" and "OK" buttons.

3. If you need to enter the above text in the future, just enter the character "pcw"? At this time, you can ignore the situation of "pcw" and just confirm it.

X. customizing functions in Excel

Excel is rich in functions, but it can't meet all our needs. We can customize a function to perform some specific operations. Next, we will define a function to calculate the trapezoidal area:

1. Execute the menu command Tools → Macros →Visual Basic Editor (or press the shortcut key Alt+F 1 1) to open the Visual Basic editing window.

2. In the window, execute the "Insert → Module" menu command to insert a new module-Module 1.

3. Enter the following code in the code window on the right:

Function V(a, b, h)

V = h*(a+b)/2

End function

4. Close the window to complete the customization function.

In the future, you can use custom functions just like built-in functions.

Tip: The functions customized by the above methods can usually only be used in the corresponding workbook.

XI。 Insert a picture under the header.

The background added to the worksheet is arranged in a row under the whole worksheet. Can it only be ranked below the header?

1. Execute the command Format → Worksheet → Background to open the Worksheet Background dialog box. After selecting the picture to use as the background, press the Insert button to arrange the picture under the whole worksheet.

2. While holding down the Ctrl key, drag the cells (regions) that don't need to be lined with pictures with the mouse and select them at the same time. 3. Press the drop-down button to the right of Fill Color on the Formatting toolbar, and then select White in the color palette that appears.

After this setting, the cell on the left has the line of the picture, but the selected cell (area) has no line of the picture (in fact, the picture is covered with "white").

Hint? Printing pictures under cells is not supported.

Twelve, use the hyphen "&"to merge the text.

If we want to combine the contents of multiple columns into one column, we can do it with a small hyphen "&"without using a function (assuming that columns B, C and D are combined into one column).

1. Insert two empty columns (column E and column F) after column D, and then enter the formula in cell D 1: = b1&; C 1。 D 1 .

2. Select cell D 1 again, and use "fill handle" to copy the above formula to the cell under column D, and the contents of columns B, C and D will be merged into the corresponding cell of column E. ..

3. Select column E, perform copy operation, then select column F, execute Edit → Paste Special command, open the Paste Special dialog box, select numerical option, and press OK to copy the contents of column E (not formula) to column F. ..

4. Delete columns B, C, D and E to complete the merger.

Tip: After completing the operations of steps 1 and 2, the merging effect has been achieved. However, if columns B, C and D are deleted at this time, there will be errors in the formula. Therefore, the third step must be taken to convert the formula into a constant "value".

Thirteen, quickly print student scores.

Many friends often ask the question "how to print the report card". Many people use the method of recording macros or VBA to realize it, which is difficult for beginners. To this end, I give a simple method realized by functions here.

Here, it is assumed that students' grades are saved in the cell area from sheet A 1 to G64 of Sheet 1, where 1 is the behavior title and the name of the second behavior discipline.

1. switch to Sheet2 worksheet, select cell A 1, and enter the formula: =IF(MOD(ROW (), 3)=0, 〃 〃, IF(0MOD? ROW()，3(= 1，sheet 1！ A $2 index (sheet 1! $A:$G, INT((ROW()+4)/3)+ 1), column ()). 2. Select cell A 1 again and copy the above formula to cell B 1 to g/kloc-0 with "fill handle". Then, select cells A 1 to G 1 at the same time, and use fill handle to copy the above formula into cells A2 to G 185.

At this point, the score column is basically formed, and the following is a simple modification.

3. After adjusting the row height and column width, select cells A 1 to G2( 1 student's score column area) at the same time, press the drop-down button on the right side of the border in the format toolbar, select all border options in the border list that appears later, and add a border to the selected area (if you don't need a border, you don't need to do this step or below.

4. Select cells A 1 to G3 at the same time, click the format brush button on the standard toolbar, then hold down the left mouse button, and drag from A4 to G 186 to add borders for all the music scores.

Press the "Print" button to print the report card.

14.Excel helps you choose a function.

When using functions to process data, we often don't know which function is suitable. Excel's "search function" function can help you narrow down the scope and choose the appropriate function.

Execute the "Insert → Function" command, open the "Insert Function" dialog box, enter the requirements (such as "Count") in the box below "Search Function", and then click the "Execute" button. The function related to "Count" will be selected immediately and displayed in the list box under "Select Function". Combined with viewing related help files, you can quickly determine the required functions.

15. View data in multiple cells of different worksheets at the same time.

Sometimes, when we edit a worksheet (Sheet 1), we need to check other worksheets (Sheet2, Sheet 3 ...), which can be realized by using the "monitoring window" function of Excel.

Execute Command View → Toolbar → Monitoring Window, open the monitoring window, click the "Add Monitoring" button, expand the "Add Monitoring Point" dialog box, select the cell to be viewed with the mouse, and then click the "Add" button. Repeat the above operation and add other "monitoring points".

In the future, no matter which worksheet you are in, you can view the data and related information in the cells of all monitoring points as long as you open the monitoring window.

Sixteen, quickly draw a border for the cell.

Before Excel 2002, it was troublesome to add a border to the cell area, and Excel 2002 completely extended this function.

Click the drop-down button to the right of the top border of the formatting toolbar, and select the option of drawing borders from the pop-up drop-down list, or execute the command View → Toolbar → Borders to expand the border toolbar.

Click the drop-down button on the leftmost side of the toolbar, select a border style, and then drag in the cell area where you need to add a border to quickly draw a border for the corresponding cell area.

Tip: ① If you draw the wrong border, it doesn't matter. Select the Erase Border button on the toolbar, and then drag it to the wrong border to clear it. ② If you need to draw a border with different colors, you can press the "Line Color" button on the right side of the toolbar, select the desired color in the palette that pops up later, and then draw the border. ③ This function can also draw diagonal lines in cells.

Seventeen, control the length of the text input in a specific cell.

Can you imagine that Excel can automatically judge, analyze and pop up a warning when you fill in a two-digit number in a cell where four digits are entered, or when you enter a number in a cell where words are entered? Excel is not difficult to realize this function.

For example, we put the cursor in the cell where "Year" is registered. In order to unify the input and facilitate the calculation, we hope that the "year" will be represented by a four-digit number. So, we can click the "Validity" option in the "Data" menu. Select "Text Length" from the "Allow" drop-down menu of "Validity Condition" in the "Settings" card. Then select "Equal to" in the data drop-down menu with a length of "4". At the same time, we came to the error warning card again, set the error warning displayed when invalid data is entered to Stop, and fill in the columns of "Title" and "Error Information" with "Illegal Text Input!" And "Please enter a four-digit year." Words.

Obviously, if someone doesn't enter four digits in this cell, Excel will pop up a warning dialog box and tell you the reason for the error. You can't continue to enter until you enter the correct "Style" value. Isn't it amazing? In fact, in Excel's judgment of data validity, there are many special types of data formats to choose from, such as text type, sequence size, time distance and so on. If you are interested, why not make your own decision and design a test standard to make your Excel show its unique brilliance?

18. Fill fixed cells of multiple tables in groups.

We know that every time you open Excel, the software always opens multiple worksheets by default. It can be seen that Excel is more suitable for the collaborative work of multiple interrelated tables besides the powerful processing ability of a single table. Of course, to coordinate the association, you need to synchronize the input first. Therefore, in many cases, it is necessary to enter the same content in the same cell of multiple tables at the same time.

So how do you edit tables in groups? First, we click the tab name of the first worksheet "Sheet 1", and then hold down the Shift key to click the tab name of the last table "Sheet3" (if the tables we want to associate are not together, we can hold down the Ctrl key to click). At this point, we see that the word "workgroup" appears in the title bar of Excel, and we can edit the workgroup. Write something casually in a cell where you need to enter multiple tables at once. We found that all the tables in the "Workgroup" displayed the corresponding contents in the same position.

However, it is not enough to synchronize the input. For example, what if you need to change the format of data in the same position in multiple tables? First, we must change the data format of the first table, then click the Fill option in the edit menu, and then select Go to the same worksheet in the submenu. At this time, Excel will pop up the dialog box of "Fill Group Worksheet", where we select "Format" and click "OK", and the data formats of all tables in the same group will change.

Nineteen, change the case of the text

In Excel, the most powerful support for table processing and data manipulation is not formula or database, but function. Don't think that the functions in Excel are only for numbers. In fact, Excel has a special function to edit anything in the table. Such as changing the case of text.

In Excel 2002, there are at least three functions about text case conversion. They are: "=UPPER", which converts all words into uppercase; "=LOWER", which converts all text into lowercase; "= prop", which converts the text into "appropriate" case, such as capitalizing the first letter of each word. For example, if we enter lowercase "excel" in cell A 1 of a table, and then enter "=UPPER(A 1)" in the target cell, the result after entering enter will be "EXCEL". Similarly, if we enter "Mr. Wei Wei" in cell A3, and then enter "=PROPER(A3)" in the target cell, then the result we get will be "Mr. Wei Wei".

Twenty, extract specific characters from the string.

In addition to direct input, extracting specific characters from existing cell contents is definitely a time-saving and trouble-saving method, especially for some information with the same style, such as employee list, hometown and other information.

If you want to extract the title from A4 cell quickly, you'd better use the function "=RIGHT (the number of characters extracted from the source cell)", which means "extract 2 characters from the rightmost characters in A4 cell" and enter it in this position. Of course, if you want to extract the name, you have to use the "=LEFT (source cell, number of characters extracted)" function. On the other hand, instead of starting from the left and right ends, we extract a few characters directly from the data. For example, if we want to extract the word "Wuhan" from A5 cell, we only need to enter "= mid (A5,4,2)" in the target cell. It means: extract two characters after the fourth character in A5 cell, namely the fourth and fifth words.

Twenty-one, change the cardinal number into ordinal number.

It is a complex problem to convert English cardinal words into ordinal numbers. Because it has no fixed pattern: most numbers use the suffix "th" when they become ordinal numbers, but most numbers ending in "1", "2" and "3" end in "st", "nd" and "rd" respectively. Moreover, although the three numbers "1 1", "12" and "13" are different, they still end with "th". Therefore, it seems complicated to implement. In fact, as long as you clear your mind, find a suitable function and write a formula, you can easily convert it. If you don't believe me, please see: "= A2 &；; IF(OR(value(RIGHT(A2，2))={ 1 1， 12， 13})，〈th〉IF(OR(value(RIGHT(A2))= { 1，2，3}，CHOOSE(RIGHT(A2)， Although the formulas of "〈ST〉" and "nd" rd) are a long string, their meanings are clear: ① If the number ends with "1 1", "12" and "13", then add the suffix "th"; ② If the principle of 1 is invalid, check the last number and use "st" at the end of "1", "nd" at the end of "2" and "rd" at the end of "3"; ③ If the principle of 1 and 2 is invalid, use "th". So the conversion between cardinal words and ordinal numbers is so easy and fast.

Twenty-two, complete the numbers with special symbols.

Everyone who has dealt with finance knows that there is a conventional "safe form filling method" when filling out forms, that is, filling in the blanks in the amount, or adding symbols such as "$" in front of the amount data. In fact, there is a similar input method in Excel, which is the "REPT" function. Its basic format is "=REPT ("special symbol ",filled with numbers)".

For example, if we want to fill 16 bits with "#" at the end of the number in cell A2, we only need to change the formula to "= (A2&; REPT(〃#〃， 16-LEN(A2))"； If we want to use "#" in the A3 cell from the left to 16, we should change it to "= rept (〃 # 〃,16-len (a3))&; a3”； In addition, if you want to use "#" to fill the value in A4 from both sides, you need to change it to "= rept (〃 # 〃, 8-len (A4)/2)&; A4 & ampREPT(〃#〃)8-LEN(A4)/2)"； If you are not professional enough and want to add a "$" symbol at the top of A5 cell number, change it to: "= (text (A5, 〃 $ #, # # 0.00 (&; Rept (〃 # 〃, 16-len (text (a5, 〃 $ #, # # 0.00)) will certainly meet your requirements.

Twenty-three, create a text histogram

In addition to repeated input, another derivative application of the "REPT" function is that you can create a histogram composed of plain text directly in the worksheet. Its principle is also very simple, that is, using the intelligent repetition of special symbols, different comparison effects are displayed in the specified cells according to the calculation results.

For example, we make an annual balance sheet first, and then use "E-column" as the display area of "within budget" months in the histogram, and "G-column" as the display area of "over budget" months in the histogram. Then, according to the numerical value of the existing result "D column" in the table, it is represented by the "N" character in the font "Wingdings". The specific steps are as follows:

Write the formula "= if (d3; 0, rept (〃 n ? round (D3 *100,0)), 〃), and drag fill handle to G 14 at the same time. We see that a plain text histogram without Excel chart function has been displayed, which is convenient, intuitive and simple.

Twenty-four, calculate the total number of words in the cell.

Sometimes, we may be interested in the number of characters in a cell and need to calculate the total number of words in the cell. To solve this problem, in addition to the "replacement" function of virtual computing, we should also use the "pruning" function to delete spaces. For example, "How many words?" Now enter it in cell A 1 Words, then we can use the following expressions to help:

"= if (len (a1) = 0,0, LEN(TRIM(A 1))-LEN (replacement (TRIM(A 1),", "))

The meaning of this formula is to create a new string with the "replace" function, delete the spaces between characters with the "trim" function, then calculate the digit difference between this string and the original string to get the number of "spaces", and finally add+1 to the number of spaces to get the number of characters in the cell.

Twenty-five, about the conversion of the euro

This is a new tool in Excel 2002. If you choose the default method when installing Excel 2002, you may not find it in the Tools menu. However, we can first select the add-in in the Tools menu, and then select the Euro Tool option in the pop-up window. After confirmation, Excel 2002 will be installed automatically.

When finished, we open the Tools menu again and click Euro Conversion. A separate window dedicated to the conversion between the euro and the currencies of EU member countries will appear. Like other function windows in Excel, we can use the mouse to set the "source area" and "target area" for currency conversion, and then select different currencies before and after conversion. Shows the price list of "100 Euro" converted into other currencies of EU member states. Of course, in order to make the display of the euro more professional, we can also click the "Euro" button on the Excel toolbar, so that all the converted currency values are in the Euro style.

26. Make a table super search engine.

As we know, the biggest difference between Excel and Word forms is that Excel brings all the contents (including static text) filled in the form into the database. We can use "function query" to locate the target data accurately, just like a search engine in a web page.

Adopt it! thank you