Cách dùng hàm SUMIFS lập báo cáo tổng hợp một cách nhanh chóng nhất
1. Hàm SUMIFS là gì?
UMIFS 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.
Công thức hàm SUMIFS
Tham số của hàm SUMIFS:
Range: Phạm vi các ô bạn muốn đánh giá theo tiêu chí Criteria. Các ô trong mỗi phạm vi phải là số hoăc tên, mảng hay tham chiếu chứa số. Giá trị trống và giá trị văn bản bị bỏ qua. Phạm vi được chọn có thể chứa các ngày ở định dạng Excel tiêu chuẩn.
Criteria: Tiêu chí xác định các giá trị được tính tổng. Nó có thể là số, biểu thức hoặc một chuỗi văn bản.
Sum_range: Tham số này là tùy chọn, nó sẽ chỉ ra các ô cần tính tổng. Nếu để trống sum_range, những ô trong phạm vi đánh giá sẽ được thay 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:
Hướng dẫn cách dùng hàm SUMIFS
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
2. 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.
3. 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 :
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.
Như vậy bài viết đã hướng dẫn cho bạn Cách dùng hàm SUMIFS lập báo cáo tổng hợp một cách nhanh chóng nhất. Chỉ cần vài thao tác đơn giản, bạn đã có thể lập báo cáo tổng hợp một cách nhanh chóng rồi. Chúc các bạn thực hiện thao tác thành công!
