Cách lập báo cáo chi phí lương theo bộ phận và theo tháng với hàm SUMIFS trên Excel

trong bài này Học Excel trực tuyến sẽ hướng dẫn các bạn cách làm báo cáo chi phí tiền lương theo bộ phận và theo tháng với TỔNG HỢP . chức năng trên Excel.

Yêu cầu lập báo cáo chi phí tiền lương cho bộ phận là yêu cầu phổ biến trong công việc Hành chính nhân sự. Điều này giúp theo dõi chi phí tiền lương trong doanh nghiệp. Vì vậy, loại báo cáo này thường phải được thực hiện cụ thể theo từng tháng trong năm.

Dưới đây là ví dụ để chúng ta hình dung cụ thể về yêu cầu báo cáo chi phí tiền lương như sau:

Cách tổ chức dữ liệu

Để có thể phục vụ cho báo cáo này, cách tổ chức dữ liệu là rất quan trọng. Thông thường bảng lương được lập riêng từng tháng (1 sheet hoặc nhiều sheet). Nhưng để tổng hợp được chi phí lương thì gần như bắt buộc chúng ta phải tổng hợp dữ liệu từ bảng lương vào 1 bảng, vì:

• Việc tính toán trên dữ liệu từ 1 bảng sẽ dễ dàng hơn so với từ nhiều bảng, chỉ cần tham chiếu đến 1 bảng.
• Với báo cáo đa điều kiện, cách thực hiện phụ thuộc khá nhiều vào cách sắp xếp dữ liệu. Tổ chức tốt thì chỉ cần dùng 1 công thức tính ra ngay kết quả.

Trong ví dụ trên ta thấy bảng tổng kết lương của các tháng đã được sắp xếp thành 4 cột: Tháng, Mã nhân viên, Phòng ban, Số tiền. Do yêu cầu báo cáo chi lương theo tháng cho từng bộ phận nên tối thiểu phải có 3 trường dữ liệu: Tháng, Bộ phận, Số tiền lương.

Tham khảo: Hướng dẫn cách xây dựng bảng dữ liệu chuẩn để làm báo cáo trên Excel

Cách tổ chức báo cáo

Bạn đã bao giờ tự hỏi: báo cáo tổ chức tốt như thế nào? Với yêu cầu báo cáo ở ví dụ trên, ta thấy có 2 điều kiện:

Như vậy, chắc chắn sẽ có 1 điều kiện trên cùng một cột, 1 điều kiện trên cùng một hàng (do đó sẽ có sự liên kết giữa từng phần của điều kiện này với các phần khác). Đây là một báo cáo hai chiều.

Cách tổ chức: Chọn điều kiện theo cột, ví dụ cột Bộ phận. Điều kiện còn lại là Tháng sẽ được tổ chức trên cùng một dòng. Khi viết các điều kiện trong hàm SUMIFS, chúng ta cũng tham chiếu đến các điều kiện này.

Tham khảo: Tìm hiểu về các loại báo cáo thông dụng trên Excel

Cách sử dụng hàm SUMIFS để tính toán kết quả

Hàm SUMIFS là hàm tính tổng theo nhiều điều kiện. Cụ thể trong trường hợp này chúng ta cần tính tổng lương theo 2 điều kiện: Bộ phận và Tháng. Do đó, các thành phần trong hàm SUMIFS được định nghĩa như sau:

• tổng_dải: Tổng tích là cột Lương trong bảng dữ liệu D2:D19. Các tham chiếu đến bảng dữ liệu cần phải cố định tất cả các tham chiếu hàng và cột.
• Criteria_range1: Vùng điều kiện thứ nhất là cột Bộ phận C2:C19
• tiêu chí1: Điều kiện đầu tiên trong phạm vi Criteria_range1 là một phần. Tham chiếu đến cột F, từ F1 nên cần sửa cột F
• Criteria_range2: Phạm vi điều kiện thứ 2 là cột Tháng A2:A19
• tiêu chí2: Điều kiện thứ hai trong phạm vi Criteria_range2 là mỗi tháng. Đề cập đến hàng 3 từ cột G, vì vậy dòng 3 . cần phải được sửa chữa

Công thức trong ô G4 có thể được viết như sau:

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

Khi viết công thức chú ý cách sửa tham chiếu trong Range, Criteria. Điều kiện nào cố định cột, điều kiện nào cố định hàng.

Tham khảo: Hướng dẫn cách viết điều kiện hàm SUMIFS để đạt hiệu quả tốt nhất

Sao chép công thức tại ô G4 (có thể sao chép sau khi đã sửa đúng tham chiếu) cho toàn bộ bảng báo cáo và nhận được kết quả như sau:

Vì vậy, chúng tôi có thể dễ dàng lập báo cáo chi phí tiền lương. Việc sử dụng hàm SUMIFS để tạo báo cáo không khó nhưng quan trọng là bạn phải sắp xếp dữ liệu một cách hợp lý thì mới có thể sử dụng được hàm này.

Tham khảo: Cách lập báo cáo tổng kết hàng tháng với SUMIFS. chức năng

Ngoài ra còn rất nhiều kiến ​​thức bổ ích khác mà bạn có thể tìm hiểu trong khóa học: HCNS101-Toàn tập kỹ năng Hành chính Nhân sự. Đây là khóa học vô cùng bổ ích và giúp bạn nắm bắt đầy đủ kiến ​​thức cũng như xây dựng các kỹ năng cần thiết để hoàn thành tốt công việc. Xem chi tiết tại:

Xem thêm: Các hàm và công thức Excel dùng trong tính lương và kế toán nhân sự: SUMIF . chức năng