Home  >  Article  >  Topics  >  How to make summary of attendance statistics table in excel

How to make summary of attendance statistics table in excel

爱喝马黛茶的安东尼
爱喝马黛茶的安东尼Original
2019-07-26 15:31:0725538browse

How to make summary of attendance statistics table in excel

Production steps:

First, as shown below, leave the first three lines blank. Enter a name in A4 and add a border starting from A4 to H4.

How to make summary of attendance statistics table in excel

Select A1:H1 and select [Merge Cells].

How to make summary of attendance statistics table in excel

Double-click the merged cell and enter the formula: =TEXT(NOW(),"yyyy year mm monthly attendance sheet")

Note: In order To prevent the function from changing automatically in June, you can paste it into a numerical value and save it at the end of the month.

How to make summary of attendance statistics table in excel

Enter 1 in B3 and fill in the date to the right. In this example, fill in 7 days, which is the 1st to 7th of the month.

Note: The actual situation can be filled to 31 and judged by IF. If it exceeds the maximum number of days in this month, it will be blank.

How to make summary of attendance statistics table in excel

Enter the first day of the month in B2 and fill to the right.

Note: This can also be done using the time formula. If the month and year are generated using drop-down menus, both here and the table header can be automatically generated using formulas and will not change with time.

How to make summary of attendance statistics table in excel

Select the date you just entered, right-click and select [Format Cells]. Enter:

aaaa in [Custom], and click the [OK] button.

How to make summary of attendance statistics table in excel

#In this way, the date just entered will be displayed in the "day of the week" display style.

How to make summary of attendance statistics table in excel

Enter the mark for attendance in an unused blank column, then enter [Option] in the name box and press Enter. This defines the name of an "option".

How to make summary of attendance statistics table in excel

Related recommendations: "excel basic tutorial"

Select the blank space to the right of the name and select [Data]-[Data Effectiveness].

How to make summary of attendance statistics table in excel

Select "Sequence" in [Allow], enter: = option in Source, and check [Provide drop-down arrow].

How to make summary of attendance statistics table in excel

In this way, a drop-down menu is generated in columns B to H, which can mark employee attendance status.

How to make summary of attendance statistics table in excel

Choose drop-down menus on different days based on the actual attendance of employees. The completed effect is as follows:

How to make summary of attendance statistics table in excel

##Finally The first step is to make statistics on attendance for the month.

Set the statistical format on the right side of the recording area as shown below. Among them, I4:N4 needs to enter formula statistics.

How to make summary of attendance statistics table in excel

Double-click the I4 cell and enter the formula: =COUNTIF($B4:$H4,I$3)

Note: The original range and table in the formula Header references are of particular concern.

How to make summary of attendance statistics table in excel

Then pull the formula to the right to return the employee’s attendance statistics for the current month.

How to make summary of attendance statistics table in excel

The above is the detailed content of How to make summary of attendance statistics table in excel. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn