1. Relationship between length of service and the number of days on vacation.
If you have worked for less than 10 years, you should take 5 days off. If you have been working for less than 10 years, you should take 10 days off. If you have been working for more than 20 years, you should take 15 days off. Therefore, if you want to do a good job in this project, you must master the exact participation time of all the comrades. This is the premise.
2. Use the function to automatically differentiate the number of days on vacation for working hours.
In January 2015 of this year, 15 days are required for the participation time before January 1, 1995. For the participation time since January 1, 2005, only five days are allowed; if the parameter time is in the middle, you can take 10 days off. As long as we can find the two points 1995 and 2005, we can make a good distinction.
In Excel, the IF function is required. First, place the cursor in a blank cell, and then open "insert \ function". In the new window that appears, select IF and confirm. Then, a new window is displayed. There are three rows in total. The first row is the condition, the result displayed after the second row is established, and the result displayed after the third row is not valid.
IF you are familiar with the IF function, you can directly program it. Select the cell to be displayed first, and insert the following language directly after the above fx: = IF (D2 <, 15, IF (D2>, 10 ))
The explanation is as follows: D2 indicates the cell in which the parameter work time is placed. If D2 is smaller than 1996, it means that the parameter work time before January 1, January 1995 can be taken for 15 days. The IF function is used again later to make a secondary judgment. IF the parameter time is longer than 1996, but also greater than 2006, then he can only take 5 days. In addition, 10 days off.
The two IF functions have three results: 5, 10, and 15.
3. If the function is correctly written, the system automatically displays the number of days on vacation for the parameter time at the cursor. After a cell is complete, place the cursor in the lower right corner of the cell, show the cross arrow, and pull down until it includes everyone. In this way, all the people will differentiate and display according to the set IF function above.
4. Automatically fill in the remaining days.
The total number of days to be closed is automatically displayed based on the specified time. The number of days on vacation needs to be manually entered, because the situation varies for each person. Then, the remaining days of vacation can still be automatically calculated and displayed through the function.
First place the cursor in the blank cell you want it to display the remaining days, and then directly enter: = E2-G2 after the above fx function. Here, E2 represents the number of days in which the vacation is to be taken, while G2 represents the number of days in which the vacation is to be taken.
5. Add another column to check whether the annual leave is full.
To be beautiful and easy to identify, you should also add a column to show whether the person's annual vacation is full. This item is related to the remaining days. If the remaining days are zero, the display is full; otherwise, the display is not displayed.
Similarly, using the IF function, input = IF (H2 = 0, "full ",""). The advantage of doing so is that you can filter the year-end vacation statistics based on this column.
6. Highlight the cells with full vacations.
First, select the column that is added in the previous step to show whether it is full, and then select "format \ condition format". A new window is displayed, and the above line is the formula, the following formats can be distinguished. In the formula above, select "cell value" and "equal to" full ", which means that when the number of cells is displayed as" full ", the key is highlighted according to the set format. In the following format, you can select the font, color, and so on, in order to highlight the cells that have already met the condition of vacation full.
7. How to filter and display data?
In the year-end statistics, it will certainly show the persons who have full vacations, who are not full, and who are not on vacation.
You can select "data \ filter \ automatic filter" directly without selecting a column. Then, the cells in the first row will show a drop-down arrow and select, you can only display the content you want to display. Suppose that you want to know that all people who have completed their careers and holidays should select the conditions of "career" and "full" respectively in their staffing and remarks. If you want to recover, click "automatic filtering" again ".
8. Simple table creation.
When you fill in all your content, you should add the table line. Otherwise, there will be no table line after printing. First, select the area you want to add to the table line, right-click and select "set cell format", then "border", and select the inside and outside borders. If you want to merge cells, select them in "alignment.
