Sharing practical Excel skills: quickly organize attendance data!

In the previous article "Excel Chart Learning: Creating Multi-Series Multi-Conditional Histograms with Target Values", we learned about an Excel chart tutorial, and today we share an article on organizing attendance data The tutorial is just two simple formulas. Once you learn it, you will not be afraid no matter how many people there are in your company.

Attendance machines are used by almost every company, and at the beginning and end of each month, it takes us a lot of time to sort out the attendance data exported by the machine. Today we will teach you With two formulas, you can sort out the attendance data in one minute.

The attendance data we usually export from the attendance machine is basically in the following format when opened through Excel:

In order to further collect attendance data, we hope to The above format is organized into the following effect:

Every person’s daily punch-in record is recorded on the same line, and abnormalities are marked after comparison according to the commuting time.

The working hours are as follows:

The morning working time is 8:00, the morning working time is 12:00, the afternoon working time is 13:30, and the afternoon working time is 17:30

To achieve this kind of effect conversion, it feels like a very troublesome thing. In fact, as long as you master two formulas and some basic operating skills, it can be completed in one minute. Let’s take a look at how to achieve it.

1. Basic data sorting

#Add a few columns to the right of the basic data and divide the time into morning work, morning get off work, and afternoon work and afternoon off work, and mark the corresponding time:

Copy the three columns of card number, person and date to the corresponding positions on the right, and then use "Delete Duplicates" Function:

After clicking OK, duplicate content will be deleted, and only one line will be retained per person per day:

Next The task is to fill in the corresponding punch-in time into the corresponding position, and do not display the specific time for abnormal data, only display the word "abnormal". In order to achieve this purpose, two formulas need to be used. Let’s look at the first formula first.

2. Use the formula to remark the clock-in time

In order to facilitate the statistics of the clock-in time, you must first make notes based on the commuting time, and implement the following The effect in the picture:

It is divided into four situations according to the commuting time:

1. Checking in before 8 o'clock is regarded as working in the morning;

2. Checking in after 12 o'clock is considered as leaving work in the morning. Considering that there is still work in the afternoon, it is artificially stipulated that checking in between 12:00 and 12:30 is considered leaving work in the morning;

3. Similarly, It is artificially stipulated that clocking in between 13:00 and 13:30 means going to work in the afternoon;

4. Clocking in after 17:30 means going to get off work in the afternoon;

5. Clocking in at other times is invalid. , displays blank.

The formula in cell E2 is:

=IF(D2=$L$1,D2&lt ;=$O$1),$L$2,"")&IF(AND(D2>=$P$1,D2=$N$1,$ N$2,""), double-click to fill to achieve the effect in the picture.

The first IF is : =IF(D2

When d2( When the clock-in time) is less than or equal to k1 (morning work time), the result of the if function is k2 (the four words "morning work time"), otherwise a null value is returned;

The second if is ：IF(AND(D2>=$L$1,D2

When d2 (Punch-in time) is greater than or equal to L2 (morning off-duty time) and less than or equal to o1 (artificially specified punch-in deadline), the result of the if function is L2 (the four words "off-duty in the morning"), otherwise a null value is returned.

The third if is : IF(AND(D2>=$P$1,D2

When d2 (punch time) is greater than or equal to p2 (artificially specified start time of clocking in to work) and less than or equal to M1 (afternoon work time), the result of the if function is M2 (the four words "off work in the afternoon"), otherwise a null value is returned .

The fourth if is : IF(D2>=$N$1,$N$2,"")

When d2 (check in time) is greater than or equal to N1 (afternoon off-duty time), the result of the if function is N2 (the four words "off-duty in the afternoon"), otherwise a null value is returned.

After completing the remark information, it is time to fill in the corresponding time in the corresponding area. At this time, you can use a formula to right-click and pull down to complete the time filling. Let's take a look at which magical formula it is. Bar.

3. Filling time

Enter the formula in cell K2:

=TEXT(SUMIFS($ D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E,K$2),"hh:mm:ss;;Exception;")

Pull down to the right to complete the time filling.

This formula uses two functions, text and sumifs. Let’s take a look at the principle of the formula.

The structure of the sumifs function is sumifs (data area required to be summed, condition area 1, condition 1, condition area 2, condition 2...) , in today's example we used The actual sum of the three conditions is column D. The three conditions are date, card number and remark information. The numbers that meet the three conditions are all unique, so the summation result and the reference result are consistent.

So the formula is:

SUMIFS($D:$D,$C:$C,$J3,$A:$A,$H3,$E:$E ,K$2)

Use the formula to get the result to be some numbers, because in Excel, the essence of date and time are numbers, and integers represent dates. Decimals represent time. Change the cell format in the above area to time and see the effect. The numbers become specific times, as shown below:

In fact After setting the cell format, we have basically achieved the goal. In order to improve the display effect and force the display to be in time format, we added a text function outside sumifs. Even in the regular format, it is displayed according to time. At the same time, the location of 0 is displayed as abnormal.

Briefly explain the usage of text, text (data, specified format), in this example, the second parameter format is defined as the display mode of hours, minutes and seconds, the letter h , m and s respectively represent hours, minutes and seconds, both of which are displayed with two digits.

The semicolon in the format code can set the display mode separately according to the data type. text specifies that the data is divided into four types: positive number; negative number; zero; text. In this example, positive numbers are displayed according to the time format, negative numbers and text are not displayed without a specified format, and zero is displayed as the word "abnormal".

Regarding the text function, today you just need to understand its usage in this example.

Summary:

1. Reasonable use of auxiliary columns: The organization of attendance data has always been a troublesome problem, and it is often very difficult to get it right in one step. , at this time, reasonable use of auxiliary columns can reduce the difficulty of the problem a lot.

2. The formula is not omnipotent: the basic data of attendance is time, and related regulations together form the logical relationship between the data. This example is suitable for more standardized situations, if multiple shifts coexist. In this case, it cannot be achieved by this method. It must also be comprehensively considered based on the commuting time corresponding to each person's shift, and the supporting software of the attendance machine is needed to complete the statistical summary of the data.

Related learning recommendations: excel tutorial

The above is the detailed content of Sharing practical Excel skills: quickly organize attendance data!. For more information, please follow other related articles on the PHP Chinese website!