How does Excel make a calculated annual vacation statistics form?

About the unit annual vacation statistics, in fact, is a very complex work, because the number of days and each person's seniority, coupled with some comrades like to separate the annual leave, so that will appear to the end of how many days he himself also confused. In order to avoid such a situation, the comrades in charge of this work must be prepared in advance to master and count all the annual leave to ensure that everyone's interests are not lost.

Below, fool I combine my work reality, talk about how to use Excel to easily deal with the complex annual leave statistics problem.

1. The relationship between seniority and annual vacation days.

Those who have worked for 1 years or less than 10 years shall take 5 days, and shall take 20 days after 10 years of less than 10 years, and over 20 years. Therefore, if you want to do this work, it is important to master all the comrades of the exact time to participate in the work, this is a prerequisite.

2, the use of functions, automatically distinguish between the length of the vacation days corresponding.

In 2015 this year, the participation time before 1995, should take 15 days, the participation time in 2005, can only take 5 days, the participation time in the middle period, can take 10 days. As long as you can find 1995 and 2005 of these two points, it can be very good to distinguish.

Then in Excel, you need to take advantage of the IF function. First position the cursor in a blank cell, then click "Insert Function", select if in the new window and confirm that it will jump out of the new window. A total of three lines, the first line is the condition, the second act after the establishment of the display results, the third act is not established after the display results.

If you are familiar with the IF function, you can do simple programming directly. Still, first select the cell that you want to display, and then insert the following language directly after the FX above: =if (D2<1996,15,if (d2>2006,5,10))

The explanation is as follows: D2 represents the time in the cell, if the D2 is less than 1996, also means that the participation time before January 1995, can take 15 days. Again using the IF function, two times to judge, if the participation time than 1996, but also than 2006, then he can only take 5 days, in addition to this case, Hugh 10 days.

Two times if the judgment of the IF function, there will be three kinds of results, that is 5, 10, 15.

3. If the function is written correctly, the system will automatically display the number of vacation days at the cursor at the time of participation. Once a cell is finished, you can place the mouse at the bottom right of the cell, where the cross arrow appears, and then pull down until everyone is included, so that all the people will differentiate and display according to the established if function above.

4, the remaining days of automatic filling.

The total number of days to be taken, will be automatically displayed according to the working time. Vacation days are manually filled in because each person is in a different situation. Then the rest of the vacation days can still be automatically calculated and displayed by the function.

First position the cursor in a blank cell that you want it to display for the remaining number of days, and then enter directly after the FX function above: =E2-G2. Where E2 represents the cells that should take the total number of days, G2 represents the cell where the days of vacation are.

5. Add another column to show whether the annual leave is full.

In order to be beautiful and easy to identify, you should also include a column that shows whether the person's annual vacation is full. This item is related to the number of days remaining, so long as the remaining number of days is zero, then the display is full, otherwise it is not displayed.

Also using the IF function, enter =if (h2=0, "full", ""). The advantage of doing this is that you can filter by this column later when you are going to have year-end vacation statistics.

6, highlight the vacation full cell.

First select the new column in the previous step showing whether it's full, and then choose Format Conditional formatting, which pops up a new window with a formula on the line, and the following format can be distinguished. In the formula above, selecting cell value is equal to full, which means that when the value of a cell is displayed as full, the formatting is highlighted in the format. In the following format, you can select a font, color, and so on to highlight a fully qualified vacation cell.

7, how to screen display?

At the end of the year, it is certain that the people who are on vacation are full, who are not full, and who are not on vacation.

You can choose to display only what you want to display by selecting the data filter autofilter above and then selecting the Drop-down arrow for the first row of cells. Suppose you want to know all the people who have a career and have already taken a vacation, choose the "cause" and "full" conditions respectively in the staffing and notes. If you want to recover, click AutoFilter again.

8, the form of simple production.

When you have completed all the content, you should add the form line, otherwise, there is no form line after printing. First select the area where you want to join the form line, and then right-click to select Format cells, then border, and select the inner and outer borders. If you want to merge cells, select them in alignment.