Take the following table as an example, and share with you a few tips for dealing with Excel payroll.
1. Department summary table
Do a conditional summation on the department.
=SUMIF(Salary table!C:C,A2,Salary table!N:N)
The syntax of the SUMIF function is as follows:
=SUMIF(Condition area, condition, sum area)
According to the job number, query related data.
The function of VLOOKUP to return multiple columns is used here, the name is the second column, and the department is the third column. COLUMN is used to generate numbers.
=VLOOKUP($C$2,Payroll!$A:$O,COLUMN(B1),0)
3. Salary slip
The formula is the same as Case 2.
The formula is as follows:
=VLOOKUP($A2,Payroll!$A:$O,COLUMN(B1),0)
Select the area A1:N3 , then drop down. When it is pulled down, the job number will change to 2, 3, etc.
The above are a few tips to share with you today, try it out.
If you are a new student, long press the QR code below - identify the QR code in the picture - follow , and you can learn Excel together every day .
