Xây Dựng Công Thức Tính Báo Cáo Chi Phí Tiền Lương Theo Bộ Phận

Trong công việc kế toán tiền lương trên excel (hay Hành chính nhân sự) ở những doanh nghiệp có nhiều bộ phận, phòng ban, chúng ta có thể phải lập một báo cáo về chi phí tiền lương theo bộ phận. Công việc này trên excel được thực hiện như thế nào, sau đây hocexcel.online sẽ hướng dẫn các bạn thực hiện theo 2 cách làm nhé:

Đề bài: Cho sẵn nội dung bảng tổng kết tiền lương hàng tháng, yêu cầu lập báo cáo chi phí lương theo bộ phận như hình sau:

Hướng dẫn cách giải quyết:

Bước 1: Xác định mục tiêu cần đạt được

Yêu cầu ở đây là tính chi phí tiền lương theo bộ phận, theo từng tháng. Vì vậy ở đây có 2 điều kiện:

+ Theo bộ phận

+ Theo tháng

Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học Excel:

Xem thêm: Excel kế toán

Bước 2: Lựa chọn công thức tính:

Các công thức excel giúp tính tổng theo điều kiện gồm: SUMIF / SUMIFS / SUMPRODUCT

Vì ở bước 1 đã xác định là có 2 điều kiện, vì vậy không sử dụng được hàm Sumif, mà chỉ sử dụng được hàm SUMIFS hoặc SUMPRODUCT

Bước 3: Xây dựng công thức tính

Cách 1: áp dụng hàm SUMIFS

Cấu trúc hàm SUMIFS như sau:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)

Sum_range là vùng tính tổng: ở đây là cột “Thực lĩnh”

Criteria_range1: vùng chứa điều kiện thứ nhất, cột “Tháng”

Criteria1: điều kiện thứ 1 nằm trong vùng điều kiện thứ 1, ở đây là các cột Tháng trong Báo cáo. Trong cột Tháng chỉ có các giá trị tháng dạng số, nên “Tháng 1” được hiểu là số “1”

Criteria_range2: vùng điều kiện thứ 2, cột “Bộ phận”

Criteria2: điều kiện thức 2, nằm trong cột thứ 2, ở đây là các nội dung về bộ phận trong Báo cáo.

Cách viết hàm SUMIFS như sau:

=SUMIFS($G$3:$G$26,$A$3:$A$26,1,$C$3:$C$26,$I3)

Các vùng range phải cố định điểm đầu và điểm cuối để khi copy công thức sẽ không thay đổi địa chỉ tham chiếu.

Criteria1 là số 1, khi tính cho các tháng khác phải thay đổi tương ứng

Criteria2 là ô I3 sẽ cố định cột I để khi copy công thức sang những ô khác sẽ giữ nguyên tham chiếu tới cột I

Cách 2: Áp dụng hàm SUMPRODUCT

Cấu trúc hàm SUMPRODUCT:

=SUMPRODUCT(array1, [array2], [array3],…)

Xem thêm: Hàm SUMPRODUCT trong Excel và ví dụ công thức

Array là các mảng dữ liệu. Ở đây ta quan tâm tới 3 mảng dữ liệu:

• Mảng 1 là cột Tháng, có giá trị tương ứng các tháng theo báo cáo
• Mảng 2 là cột Bộ phận, có giá trị tương ứng các bộ phận theo báo cáo
• Mảng 3 là kết quả cần tính, cột thực lĩnh

Cách xây dựng công thức như sau:

=SUMPRODUCT(($A$3:$A$26=1)*($C$3:$C$26=$I3)*$G$3:$G$26)

Với những mảng có yêu cầu điều kiện, ta sẽ gán giá trị ở mảng đó bằng với điều kiện cần tính

Các mảng được liên kết trực tiếp với nhau bằng dấu *

array1 = $A$3:$A$26=1   Cột tháng có giá trị là 1 (khi copy công thức sang tháng khác phải đổi giá trị 1 tương ứng tháng cần tính)

array2 = $C$3:$C$26=$I3   Cột bộ phận có giá trị là ô I3 (cố định cột I để khi copy công thức sẽ không đổi tham chiếu cột I, còn tham chiếu tới hàng thì có thể thay đổi)

array3 = $G$3:$G$26

Sumproduct(array1*array2*arrray3)

Như vậy với bài viết này, chúng tôi đã chia sẻ cho các bạn cách sử dụng cũng như xây dựng công thức tính báo cáo chi phí tiền lương theo bộ phận một cách đơn giản mà hiệu quả nhất.

Blog học excel online tự hào là một trong những trung tâm chuyên đào tạo các khóa học excel online hàng đầu tại Việt Nam với cách chuyên gia hướng dẫn có nhiều năm kinh nghiệm trong nghề sẽ hướng dẫn và chỉ bảo các bạn từng chi tiết về cách sử dụng thành thạo Excel để có thể áp dụng vào học tập cũng như công việc của mình.