Shulou(Shulou.com)11/24 Report--

The attendance table is the attendance record that records our daily commute to and from work, and it is also the certificate for us to receive our salary. Today, Mr. Wang is here to teach you how to use Excel to make a powerful intelligent attendance table!

The final demo changes the month, and the date changes automatically.

The drop-down menu allows you to select attendance symbols, each of which represents different information, √ = attendance; ● = late; attendance = early departure; △ = sick leave; attendance = official leave; × = absenteeism.

Automatic statistics of attendance, lateness, early departure, sick leave and other days.

01, to start the production of attendance table 1, first enter the attendance year and month in cell A1, and then add name, week, date and other basic information. Enter the formula in the C3 cell:

= A1 enter the formula in the D3 cell:

When the input is complete, select the D3 cell and fill it to the right into the AG cell.

2. Enter the formula in cell C2:

= C3 and then continue to fill to the right.

3, the cursor to the C3 cell, and then use the shortcut key "Ctrl + Shift + right arrow" to select all cells, "right"-"set cell format"-"number"-"Custom", enter "d" in the type.

Similarly, select all the cells of the week, "right"-"format the cell"-"number"-"Custom", and enter "aaa" in the type.

02. Beautify form 4. Add colors and borders to the table, and then align it in the center.

5, adapt to the cell size of the digital area, merge the top table and center it, and enter the "attendance table" and other information. Everyone can set and beautify according to their own needs!

03, automatic update date setting the attendance table at this time can update the date automatically, but it is impossible to automatically hide the excess number of days in the case of a small number of "days per month". The following figure shows:

6. Select the last three cells of "date" and "week", "start"-"style"-"conditional format"-"New Rule"-"use the formula to determine the cells to be formatted", and enter the formula:

= MONTH (AE2) MONTH (AB2) then click format-number-Custom, and enter ";;" in Type.

Note: month can get the date and month, and use the last three cell months to compare the conditions with the first three cell months. ";;" semicolons can hide cell contents.

After setting up, let's modify the date again. If the number of days per month is small, the contents of the following cells will be automatically hidden.

04, increase attendance 7, add attendance information, late, early departure, sick leave and so on in the back of the merged cell.

Add information as shown in the figure:

8. Add attendance symbols, √; ●; attendance; △; attendance; ×.

Then select all the cells that enter the attendance symbol, click "data"-"data tools"-"data validation"-"data validation"-"Settings", allow it to be set to "sequence", and then select "Source" as the attendance symbol cell.

05. Statistical attendance record 9. Enter the formula in the following cells, such as attendance, lateness, early departure, sick leave, etc.

Attendance:

= COUNTIF (C4:AG4, "√") + AI4+AJ4 late:

= COUNTIF (C4:AG4, "●") early departure:

= COUNTIF (C4:AG4, "sick") sick leave:

= COUNTIF (C4:AG4, "△") personal leave:

= COUNTIF (C4:AG4, "absent") absenteeism:

= COUNTIF (C4:AG4, "×")

After the formula is completed, let's take a look at the statistical results.

All right, our attendance schedule has been completed, let's see the effect!

This article comes from the official account of Wechat: Word Alliance (ID:Wordlm123), author: Wang Wangqi

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.