Cách lập báo cáo trên Excel hiệu quả với hàm SUMIFS

Trong các hàm tính toán của Excel, hàm SUMIFS là một hàm cực kì hữu ích khi chúng ta cần lập báo cáo tổng hợp với các điều kiện tính toán nhất định cho các đối tượng khác nhau. Trong bài viết ngày hôm nay, hãy cùng Gitiho tìm hiểu cách lập báo cáo trên Excel với hàm SUMIFS nhé.

Giới thiệu về hàm SUMIFS trong Excel

Hàm SUMIFS trong Excel có chức năng tính tổng dãy số với các điều kiện nhất định cho các đối tượng cụ thể. Ta có công thức hàm như sau:

=SUMIFS(sum_range, criteria_range1, criteria 1, [criteria_range2, criteria2], ...)

Trong đó:

• sum_range là phạm vi các ô chứa giá trị cần tính tổng.
• criteria_range1 và criteria1 lần lượt là phạm vi cần được kiểm tra và điều kiện áp dụng cho phạm vi đó. Nếu báo cáo trên Excel sum_range của bạn chứa nhiều phạm vi nhỏ với các điều kiện khác nhau, bạn có thể nhập tiếp các cặp criteria_range2 và criteria2,... tương ứng.

Xem thêm: Hàm SUMIFS, COUNTIFS và cách viết điều kiện theo dạng số, ngày, text trong Excel

Cách lập báo cáo trên Excel với hàm SUMIFS

Phân tích yêu cầu sử dụng hàm SUMIFS

Để lập báo cáo trên Excel, trước tiên chúng ta cần xác định các yêu cầu cụ thể để sử dụng hiệu quả các hàm và công cụ Excel. Chúng ta sẽ tiến hành lập báo cáo dựa trên các dữ liệu dưới đây.

 

Chúng ta có dữ liệu ban đầu là một bảng bao gồm các cột Ngày, Sản phẩm, Nhân viên, Số tiền. Trong đó các giá trị ở cột Ngày được hiển thị dưới định dạng dd/mm/yyyy.

Dựa vào bảng dữ liệu tổng hợp, chúng ta cần tạo báo cáo theo doanh thu dựa vào cột Số tiền (cột D) và cột Nhân viên (cột C) theo từng mốc thời gian tại cột Ngày (cột A). Để làm được điều này, chúng ta cần sử dụng hàm SUMIFS trong Excel với 2 điều kiện: tên nhân viên và thời gian xác định.

Xây dựng công thức hàm SUMIFS

Tại cột G, bắt đầu từ ô G4, chúng ta tiến hành xây dụng công thức hàm SUMIFS để tính doanh thu của nhân viên Long trong năm 2012 theo công thức tổng quát:

=SUMIFS(sum_range, criteria_range1, criteria 1, [criteria_range2, criteria2], ...)

Chúng ta xác định ngay sum_range là cột Số tiền (cột D). Vậy còn điều kiện được xác định như thế nào? Hãy cùng tìm hiểu với chúng mình nhé.

Xác định điều kiện hàm SUMIFS: Nhân viên

Điều kiện 1 là công thức được lập để tính doanh thu của nhân viên Long. Do đó, chúng ta xác định vùng chứa điều kiện là cột Nhân viên, cụ thể là giá trị Long. Ta điền vào đối số criteria_range1 và criteria1 như sau:

• criteria_range1: cột C
• criteria1: H2

Xem thêm: Cách sử dụng hàm SUMIFS trong Excel tính tổng nhiều điều kiện

Xác định điều kiện hàm SUMIFS: Thời gian

Điều kiện tiếp theo ta cần xác định là các mốc thời gian tại báo cáo trên Excel. Vì trong bảng dữ liệu của chúng ta không có cột Tháng và cột Năm, do đó chúng ta sẽ dựa vào quy tắc: tròn tháng tính từ ngày bắt đầu của tháng đến ngày kết thúc của tháng. Ví dụ, tháng 1 năm 2012 sẽ được tính từ ngày 01/01/2012 đến ngày 31/01/2012.

Như vậy, chúng ta điền cặp phạm vi-điều kiện 2 vào công thức hàm SUMIFS trong Excel:

• criteria_range2: Cột A
• criteria2: “>=”&DATE(G3,F4,1)

Với criteria2, ta bắt đầu tính thời gian từ ngày 01/01/2012 nên phải thêm ">=" để xác định "từ ngày". Ta kết hợp sử dụng hàm DATE trong Excel để xác định ngày theo năm (ô G3) và tháng (ô G4).

Tiếp theo, ta có cặp phạm vi-điều kiện 3:

• criteria_range3: cột A
• criteria3: “<=”&EOMONTH(DATE(G3,F4,1),0)

Với criteria3, ta tính doanh thu tại dữ liệu báo cáo trên Excel đến ngày cuối tháng 1/2012 nên phải thêm toán tử "<=" để xác định "đến ngày". Ta kết hợp sử dụng hàm EOMONTH trong Excel để xác định ngày cuối tháng chứa giá trị hàm DATE trong criteria2.

Công thức hàm SUMIFS hoàn chỉnh

Kết hợp tất cả các đối số vừa xây dựng, chúng ta điền vào ô G4 công thức hàm SUMIFS trong Excel hoàn chỉnh như sau:

=SUMIFS($D$4:$D$31,$C$4:$C$31,$H$2,$A$4:$A$31,”>=”&DATE(G$3,$F4,1),$A$4:$A$31,”<=”&EOMONTH(DATE(G$3,$F4,1),0))

Lưu ý: Cần sử dụng tham chiếu tuyệt đổi với tất cả các range, tên nhân viên và các giá trị năm, giá trị tháng.

Sau khi đã điền công thức vào ô G4, chúng ta chỉ cần sử dụng tính năng FillDown để sao chép công thức xuống các ô dưới là đã hoàn thành công việc tính toán trong báo cáo trên Excel rồi.

Xem thêm: Hàm SUMIF trong Excel và cách khắc phục một số lỗi thường gặp khi sử dụng

Tổng kết

Chúng ta vừa tìm hiểu cách sử dụng hàm SUMIFS để tính toán doanh thu theo nhiều điều kiện trong file báo cáo trên Excel. Để tạo được một bản báo cáo chuyên nghiệp nhất, các bạn cần sử dụng rất nhiều kiến thức Excel khác. Một cách nhanh nhất để làm được điều đó chính là đăng kí ngay khóa học Tuyệt đỉnh Excel với Gitiho. Bên cạnh đó, bạn đừng quên truy cập blog Gitiho để đọc thêm các bài viết về chủ đề Excel nhé.

Gitiho chúc bạn làm việc hiệu quả!