Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn 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.

Yêu cầu lập báo cáo chi phí lương cho các bộ phận là một yêu cầu thường gặp trong công việc Hành chính nhân sự. Việc này giúp theo dõi chi phí lương trong doanh nghiệp. Do đó dạng báo cáo này thường phải làm cụ thể cho từng tháng trong năm.

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

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

Để có thể phục vụ cho việc lập báo cáo này thì cách tổ chức dữ liệu rất quan trọng. Thông thường các bảng lương đều được làm riêng mỗi tháng 1 bả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ừ các bảng lương vào 1 bảng, bởi:

• Việc tính toán trên dữ liệu từ 1 bảng dễ hơn so với nhiều bảng, chỉ cần tham chiếu tới 1 bảng.
• Với dạng báo cáo theo nhiều điều kiện thì cách làm phụ thuộc nhiều vào cách tổ chức dữ liệu. Tổ chức tốt thì chỉ cần dùng 1 công thức là có thể tính ngay ra kết quả.

Trong ví dụ trên chúng ta thấy bảng tổng hợp tiền lương của các tháng đã được tổ chức thành 4 cột: Tháng, Mã nhân viên, Bộ phận, Số tiền. Bởi yêu cầu báo cáo chi phí lương theo các 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ập báo cáo trên Excel

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

Có bao giờ bạn tự hỏi: báo cáo tổ chức thế nào thì tốt? Với yêu cầu báo cáo trong ví dụ trên chúng ta thấy có 2 điều kiện:

• Bộ phận
• Tháng

Như vậy chắc chắn sẽ có 1 điều kiện tổ chức trên cùng 1 cột, 1 điều kiện tổ chức trên cùng 1 dòng (như vậy thì mới có tính liên kết giữa mỗi phần của điều kiện này với các phần của điều kiện kia). Đây là dạng báo cáo 2 chiều.

Cách tổ chức: Chọn 1 đ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ẽ tổ chức trên cùng 1 dòng. Khi viết điều kiện trong hàm SUMIFS chúng ta cũng tham chiếu dựa vào các điều kiện này.

Tham khảo: Tìm hiểu về các dạng báo cáo thường gặp trên Excel

Cách sử dụng hàm SUMIFS để tính 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 tiền lương theo 2 điều kiện là Bộ phận và Tháng. Do đó các thành phần trong hàm SUMIFS được xác định như sau:

• Sum_range: Vùng tính tổng là cột Tiền lương ở bảng dữ liệu D2:D19. Các tham chiếu tới bảng dữ liệu đều cần cố định toàn bộ các tham chiếu cả hàng và cột.
• Criteria_range1: Vùng điều kiện thứ 1 là cột Bộ phận C2:C19
• Criteria1: Điều kiện thứ 1 trong vùng Criteria_range1 là từng bộ phận. Tham chiếu tới cột F, từ F1, do đó cần cố định cột F
• Criteria_range2: Vùng điều kiện thứ 2 là cột Tháng A2:A19
• Criteria2: Điều kiện thứ 2 trong vùng Criteria_range2 là từng tháng. Tham chiếu tới dòng 3 từ cột G, do đó cần cố định dòng 3

Công thức tại ô G4 có thể 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 cần chú ý cách cố định tham chiếu trong các Range, Criteria. Điều kiện nào cố định cột, điều kiện nào cố định dò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 được sau khi đã cố định các tham chiếu đúng cách) cho toàn bộ bảng báo cáo và thu được kết quả như sau:

Như vậy chúng ta đã có thể lập được báo cáo chi phí lương một cách dễ dàng rồi. Việc sử dụng hàm SUMIFS để lập báo cáo không khó, nhưng quan trọng ở việc phải tổ chức dữ liệu một cách hợp lý để có thể sử dụng được hàm này.

Tham khảo: Cách lập báo cáo tổng hợp theo từng tháng với hàm SUMIFS

Ngoài ra còn rất nhiều kiến thức bổ ích khác nữa mà bạn có thể học được trong khóa học: HCNS101-Trọn bộ kỹ năng Hành chính nhân sự. Đây là khóa học rất hữu ích và giúp bạn nắm bắt được đầy đủ kiến thức, xây dựng những kỹ năng cần thiết để hoàn thành tốt công việc. Chi tiết xem tại:

Xem thêm: Hàm, công thức Excel sử dụng trong kế toán tiền lương, nhân sự: Hàm SUMIF