Simple small functions make Excel statistical attendance more intuitive

It's time to get paid again. Leadership will be based on attendance and overtime to give some subsidies, but the actual situation will be more complex, the accountant asked me to ask what good way to simplify the work. I offered him the following solution:

This assumes that attendance is divided into three kinds of situations, namely "have overtime", "Perfect attendance", "There is absence", of which work day more than 20 days for "have overtime", 20 days for "perfect attendance", the rest is "absence." The table, as shown in Figure 1, needs to show an attendance status in the "Attendance type" column.

Figure 1

Position the mouse pointer over the E4 cell, input formula "=if" (d4>20, "There is overtime", IF (d4=20, "perfect attendance", "There is absence")) "(Figure 2, excluding outside quotes), the corresponding cell will display the corresponding" absence type "after pressing ENTER. Then enter the corresponding formula in the corresponding cell of each record.

Figure 2

The first time the workload may be larger, but later need to use, as long as the change of attendance data, "attendance type" came out, for some fixed types of forms, such as teachers to determine the grade of students, this method can still bring great convenience.

Small tip:

1. The symbols used in the company are in English half-width, and there is no space between all the characters and symbols.

2.IF supports up to seven levels of nesting, but it should be sufficient to deal with everyday applications.

3. If there are only two cases, you only need to use the "insert" → "function" command, and then select the "IF" function, the final input of the corresponding criteria and discriminant results can be. Of course, as in the text, it is also possible to enter the IF function directly.

4. This article in the Excel 2003 SP2 environment test passes.