Cách làm báo cáo chi phí lương bằng hàm SUMIFS trong Excel

Hạch toán chi phí lương là câu chuyện không còn xa lạ đối với nghề hành chính nhân sự. Để trả đúng và đủ lương cho người lao động, chúng ta sẽ cần một bản báo cáo chi phí lương chính xác. Trong bài viết ngày hôm nay, Gitiho sẽ hướng dẫn các bạn cách lập báo cáo Excel hàng tháng để theo dõi chi phí lương.

Cách lập báo cáo chi phí lương trên Excel

Để theo dõi và quản lý chi phí lương cho các lao động trong doanh nghiệp, người làm hành chính nhân sự cần tạo một bản báo cáo cụ thể hàng tháng. Vậy làm thế nào để hoàn thiện một bản báo cáo lương trên Excel và hạch toán chi phí lương chính xác cho người lao động?

Mình sẽ hướng dẫn bạn cách làm ngay trong phần bài học này. Hãy cùng thực hành các bước tổ chức và thiết lập dữ liệu trên Excel để tạo nên một bản báo cáo chi phí chuyên nghiệp nhất như trong hình dưới đây.

Cần sử dụng các công thức hàm gì để tạo báo cáo chi phí lương trên Excel? Chúng ta hãy đi tìm hiểu qua từng bước dưới đây nhé.

Xem thêm: Chia sẻ file mẫu bảng tính lương Excel theo thông tư 133

Cách tổ chức dữ liệu trong báo cáo chi phí lương

Thao tác đầu tiên cần thực hiện cũng là thao tác vô cùng quan trọng: Tổ chức dữ liệu cho báo cáo chi phí lương. Thông thường, mỗi tháng chúng ta sẽ phải tạo một bảng lương riêng biệt, hoặc có thể được coi như một sheet riêng biệt trên Excel. Tuy nhiên, để tổng hợp tất cả chi phí lương của người lao động thì chúng ta phải gộp tất cả dữ liệu từ các sheet về một sheet chung.

Lý do phải thực hiện thao tác gộp sheet trong Excel này là vì:

• Thao tác tính toán số liệu trên 1 sheet đơn giản và nhanh chóng hơn rất nhiều so với tính toán trên nhiều sheet với nhiều bảng dữ liệu cùng một cấu trúc. Khi gộp sheet trong Excel, chúng ta chỉ cần tham chiếu tới một bảng mà thôi.
• Báo cáo chi phí lương là một dạng báo cáo tổng hợp dựa trên nhiều điều kiện. Một cách tổ chức dữ liệu tốt sẽ đơn giản hóa các thao tác phía sau, giúp chúng ta tính ra các giá trị cần tìm chỉ bằng 1 công thức.

Hãy nhìn vào bảng báo cáo mẫu ở phía trên, bạn sẽ thấy toàn bộ dữ liệu được tổng hợp và sắp xếp vào một bảng Excel gồm 4 cột Tháng (cột A), Mã nhân viên (cột B), Bộ phận (cột C) và Số tiền (cột D). Cấu trúc dữ liệu như trên là thuận tiện nhất cho các bước tiếp theo.

Nếu bạn cần thêm bất kỳ dữ liệu nào, hãy sắp xếp thêm các cột trong bảng tổng hợp.

Vì yêu cầu của báo cáo chi phí lương là thống kê lương hàng tháng cho từng bộ phận, do đó, chúng ta cần trình bày ít nhất 3 trường dữ liệu, cụ thể là dữ liệu Tháng, Bộ phận và Số tiền lương như trong bảng báo cáo Excel cho chi phí lương bên phải của bảng tổng hợp.

Xem thêm: Hướng dẫn cách gộp các sheet trong Excel kèm theo code VBA

Cách tổ chức báo cáo trong báo cáo chi phí lương

Báo cáo chi phí lương của chúng ta phải đáp ứng được 2 điều kiện, tương ứng với 2 yếu tố bắt buộc thể hiện: Bộ phận và Tháng. Đây là cơ sở để chúng ta tổ chức báo cáo trình bày chi phí lương.

Từ cơ sở trên, ta sẽ xem xét cách tổ chức hiệu quả nhất để giữ được tính liên kết giữa các điều kiện chi phí lương. Có thể thấy, 1 trong 2 điều kiện sẽ được tổ chức trên cùng 1 cột, điều kiện còn lại sẽ được tổ chức trên cùng 1 dòng. Nói cách khác, chúng ta sẽ cần tạo một báo cáo có tính 2 chiều để thể hiện và hạch toán chi phí lương.

Giả sử bạn chọn điều kiện Bộ phận để tổ chức theo cột, nghĩa là điều kiện Tháng sẽ được tổ chức theo dòng. Như vậy, chúng ta sẽ có bảng Excel thể hiện báo cáo chi phí lương như trong hình dưới đây.

Dựa vào cách tổ chức báo cáo này, chúng ta sẽ đến với bước tiếp theo.
 

Cách điền dữ liệu báo cáo chi phí lương bằng hàm SUMIFS

Giờ thì chúng ta đã có một bảng báo cáo chi phí lương, thao tác cuối cùng còn lại chính là điền dữ liệu. Tại bước này, chúng ta cần cộng tổng số liệu về chi phí lương của từng bộ phận trong tháng tương ứng tại bảng tổng hợp nằm trong cùng sheet.

Sau khi xác định thao tác cần làm là cộng tổng dựa trên các điều kiện nhất định, bạn có thể dễ dàng tìm ra hàm Excel cần sử dụng cho bước tính toán này. Đó chính là hàm SUMIFS.

Như tên gọi của nó, hàm SUMIFS trong Excel là hàm ghép từ hàm SUM và hàm IFS. Do đó, chức năng của hàm SUMIFS cũng chính là sự kết hợp chức năng tính tổng của hàm SUM và chức năng áp dụng nhiều điều kiện cùng lúc cho dữ liệu của hàm IFS. Từ đó, chúng ta hiểu rằng SUMIFS là hàm tính tổng nhiều điều kiện trong Excel.

Nếu bạn còn chưa biết đến hàm tính tổng trong Excel này thì mình sẽ nói qua về cú pháp của hàm trước tiên:

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

Trong đó:

• sum_range - Là phạm vi ô tính cần thực hiện tính tổng dữ liệu. Phạm vi này có thể là một ô duy nhất, một dải ô hoặc các dải ô được đặt tên. Excel sẽ chỉ lọc ra các ô tính có chứa dữ liệu trong phạm vi ô để tính tổng bằng hàm SUMIFS.
• criteria_range1 - Là phạm vi ô tính thứ nhất để áp dụng đánh giá theo điều kiện thứ nhất.
• criteria1 - Là điều kiện đầu tiên cần phải đáp ứng. Bạn có thể viết tham số điều kiện dưới dạng văn bản, số, hoặc một công thức, một biểu thức hay một tham chiếu ô.
• criteria_range2, criteria2,... - Là các cặp phạm vi-điều kiện khác bạn có thể đưa vào cú pháp hàm. Giới hạn tối đa cho số cặp phạm vi-điều kiện trong công thức SUMIFS là 127.

Giờ thì bạn đã biết ý nghĩa và cách điền các tham số của hàm SUMIFS trong Excel rồi. Chúng ta sẽ thiết lập công thức hàm SUMIFS để điền dữ liệu cho bảng báo cáo chi phí trong sheet Excel tổng hợp chi phí lương nhé.

Các tham số trong công thức hàm SUMIFS như sau:

• sum_range - Phạm vi cần tính tổng là cột Tiền lương (cột D) trong bảng tổng hợp. Trong đó, cần sử dụng tham chiếu tuyệt đối để đảm bảo cột được cố định trong công thức.
• criteria_range1 - Phạm vi ô tính áp dụng điều kiện thứ nhất là cột Bộ phận (cột C) trong bảng tổng hợp.
• criteria1 - Điều kiện thứ nhất nằm tại cột Bộ phận (cột F) của bảng báo cáo chi phí lương. Trong đó, cần sử dụng tham chiếu tuyệt đối để đảm bảo cột được cố định trong công thức.
• criteria_range2 - Phạm vi ô tính áp dụng điều kiện thứ hai là cột Tháng (cột A) trong bảng tổng hợp.
• criteria1 - Điều kiện thứ hai nằm tại dòng Tháng (dòng 3) của bảng báo cáo chi phí lương. Trong đó, cần sử dụng tham chiếu tuyệt đối để đảm bảo cột được cố định trong công thức.

Với các tham số như trên, tại ô G4, chúng ta sẽ điền công thức dưới đây:

=SUMIFS($D$2:$D$19,$C$2:$C$19,$F4,$A$2:$A$19,G$3)

Bạn có thể để ý trong công thức hàm SUMIFS có sử dụng cả dạng tham chiếu tuyệt đối và tham chiếu tương đối. Mục đích của tham chiếu tuyệt đối là cố định cả hàng và cột của dữ liệu, trong khi tham chiếu tương đối sẽ chỉ cố định một trong hai yếu tố hàng hoặc cột của dữ liệu.

Bất kể khi bạn lập báo cáo chi phí lương hàng tháng như trong bài viết, hay lập các loại báo cáo khác, hãy chú ý đến việc sử dụng các loại tham chiếu để đảm bảo kết quả các công thức Excel không bị sai lệch nhé.

Sau khi đã có công thức điền dữ liệu với hàm SUMIFS ở ô G4, bạn chỉ cần kéo công thức xuống toàn bộ phạm vi cần điền dữ liệu. Như vậy, chúng ta đã hoàn thành được bảng báo cáo dành cho chi phí lương như trong hình dưới đây.

Bạn có thể thêm dữ liệu tính tổng chi phí lương theo từng tháng và theo từng bộ phận để có cái nhìn tổng quan và loại chi phí này.

Xem thêm: Hướng dẫn sử dụng hàm SUMIFS tính tổng nhiều điều kiện

Tổng kết

Chúng ta đã hoàn thành các bước tạo bảng Excel dành cho báo cáo chi phí lương, bao gồm tổ chức dữ liệu, tổ chức báo cáo và cuối cùng là điền dữ liệu. Các bạn có thể áp dụng các bước tương tự để lập các loại báo cáo khác trên Excel.

Hy vọng bài viết đã đem lại kiến thức hữu ích cho công việc của các bạn hành chính nhân sự. Để tìm hiểu các thủ thuật Excel khác, hay các bài học, bài chia sẻ về ngành nghề của mình, bạn hãy tham khảo các bài viết trên blog Gitiho ngay nhé.

Gitiho xin cảm ơn bạn đọc và chúc bạn thành công!