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

SUMIFS là 1 hàm rất quen thuộc và hữu ích khi chúng ta muốn tính tổng theo nhiều điều kiện. Hàm này thường xuyên được sử dụng trong việc lập các báo cáo tổng hợp với các điều kiện liên quan tới thời gian, tới nhiều đối tượng cùng lúc. Về cấu trúc và cách sử dụng hàm SUMIFS các bạn có thể xem tại đây:

Hướng dẫn sử dụng hàm Sumifs trong Excel qua các ví dụ cụ thể

Tuy nhiên có 1 hạn chế là các điều kiện trong hàm SUMIFS phải có cấu trúc hoặc nội dung tương ứng với vùng chứa điều kiện đó. Tức là:

Nếu criteria_range là dữ liệu dạng dd/mm/yyyy thì criteria trong đó phải là dạng dd/mm/yyyy

Vậy nếu dữ liệu của chúng ta là dạng dd/mm/yyyy mà điều kiện cần tính của chúng ta là điều kiện dạng Tháng thì sao? Làm thế nào để tùy biến điều kiện của hàm SUMIFS theo từng tháng trong từng năm? Chúng ta cùng tìm hiểu ví dụ sau:

Yêu cầu:

Lập báo cáo tổng hợp doanh số bán hàng theo từng tháng trong các năm theo:

Nhân viên
Sản phẩm

Xem nhanh

Phân tích yêu cầu

Dữ liệu ban đầu: bảng dữ liệu gồm các cột: Ngày / Sản phẩm / Nhân viên / Số tiền; trong đó cột Ngày ở dạng dd/mm/yyyy

Yêu cầu cần thực hiện: Báo cáo theo doanh thu (cột số tiền) của Nhân viên (cột C) theo từng tháng, từng năm (cột A). Ở đây để tính được cần thông qua 2 điều kiện: tên nhân viên và thời gian (làm tương tự với Sản phẩm (cột B)) => Do đó sử dụng hàm SUMIFS để tính tổng theo nhiều điều kiện.

Xây dựng công thức tính

Xác định các thành phần trong hàm SUMIFS như sau (áp dụng tại ô G4, giả sử bảng dữ liệu tới dòng 31):

sum_range: vùng cần tính tổng, là cột Số tiền (cột D), từ dòng 4 tới dòng cuối của bảng dữ liệu = D4:D31
criteria_range1: vùng chứa điều kiện thứ 1, là cột Nhân viên (cột C), từ dòng 4 tới dòng cuối của bảng dữ liệu. Lưu ý động rộng của các Range là bằng nhau (có cùng số dòng) = C4:C31
criteria1: điều kiện thứ 1 nằm trong vùng điều kiện thứ 1, cụ thể ở đây là nhân viên được chọn để lập báo cáo (tại ô H2) = H2

Xét điều kiện thứ 2 là điều kiện thời gian

Vì không có cột Tháng và cột Năm trong bảng dữ liệu, do đó phải dựa vào nguyên tắc: tròn tháng sẽ tính từ ngày bắt đầu của tháng tới ngày cuối của tháng đó.

=> Tháng 1 năm 2012 sẽ tính từ 01/01/2012 đến 31/01/2012 => Ở đây tách điều kiện ngày ra thành 2 điều kiện

criteria_range2: vùng chứa điều kiện thứ 2, là cột Ngày (cột A), từ dòng 4 tới dòng cuối của bảng dữ liệu = A4:A31
criteria2: điều kiền thứ 2, tính bắt đầu từ ngày 01/01/2012. Do đó phải thêm toán tử “>=” để xác định “từ ngày”, kết hợp với hàm xác định ngày dựa theo 2 yếu tố đã xác định là năm (ô G3) và tháng (ô F4) bằng cách sử dụng hàm DATE = “>=”&DATE(G3,F4,1)
criteria_range3: vùng chứa điều kiện thứ 3, là cột Ngày (cột A), từ dòng 4 tới dòng cuối của bảng dữ liệu, giống criteria_range2 = A4:A31
criteria3: điều kiện thứ 3, tính đến ngày cuối tháng 1 năm 2012. Do đó phải thêm toán tử “<=” để xác định “đến ngày”, kết hợp với hàm xác định ngày cuối tháng là hàm EOMONTH, xét cho tháng 1 năm 2012 (xác định bởi hàm Date trong criteria2) = “<=”&EOMONTH(DATE(G3,F4,1),0)

Công thức được xây dựng như sau

G4 =SUMIFS(D4:D31,C4:C31,H2,A4:A31,”>=”&DATE(G3,F4,1),A4:A31,”<=”&EOMONTH(DATE(G3,F4,1),0))

Để có thể áp dụng công thức tại G4 tương tự cho các tháng khác, năm khác thì làm như sau:

Tất cả các Range (gồm Sum_range, Criteria_range) đều phải cố định khi sao chép công thức để không làm thay đổi tọa độ tham chiếu tới các vùng dữ liệu này: $D$4:$D$31 , $C$4:$C$31, $A$4:$A$31
Tham chiếu tới tên Nhân viên (H2) phải cố định => H2 được cố định là $H$2
Tham chiếu tới các giá trị năm (dòng 3) sẽ cố định => G3 được cố định dòng là G$3 để khi sao chép công thức sang phải thì tham chiếu tới các cột Năm tương ứng sẽ thay đổi theo, sao chép xuống các tháng bên dưới thì không thay đổi trong năm đó.
Tham chiếu tới các giá trị tháng (cột F) sẽ cố định => F4 được cố định cột là $F4 để khi sao chép công thức xuống các tháng bên dưới thì thay đổi theo dòng tương ứng các tháng đó, khi sao chép công thức sang phải thì giữ nguyên cột Tháng trong các năm tương ứng.

Công thức hoàn chỉnh là:

G4=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))

=> Sao chép công thức từ G4 sang phải cho các ô H4, I4 (fillright)

=> Sao chép công thức từ G4 xuống dưới cho các tháng tương ứng tới G15, H15, I15 (filldown)

=> Hoàn thành báo cáo tổng hợp theo nhân viên.

Áp dụng tương tự với bảng báo cáo tổng hợp theo sản phẩm.

Tải về tài liệu tham khảo: https://drive.google.com/open?id=1i6qJezLzp05kw_PyhriHDPuWc1FqZPu2

Video hướng dẫn: