Nội Dung Bài Viết

Trong công việc xử lý dữ liệu và lập báo cáo kế toán, hàm SUMIFS là một trong những công cụ mạnh mẽ nhất mà Excel cung cấp. Hàm này cho phép người dùng tính tổng các giá trị dựa trên nhiều điều kiện khác nhau, phục vụ đắc lực cho việc tạo ra các báo cáo tổng hợp phức tạp liên quan đến thời gian, nhân sự hay danh mục sản phẩm.

Tuy nhiên, một thách thức lớn mà nhiều người dùng gặp phải là sự không đồng nhất giữa định dạng dữ liệu nguồn và yêu cầu báo cáo. Ví dụ điển hình nhất là khi dữ liệu gốc ghi nhận chi tiết theo ngày (dd/mm/yyyy), nhưng báo cáo lại yêu cầu tổng hợp theo Tháng và Năm. Do hàm SUMIFS yêu cầu điều kiện phải tương thích trực tiếp với vùng dữ liệu, chúng ta không thể đơn thuần so sánh một ngày cụ thể với một con số chỉ tháng.

Bài viết này sẽ hướng dẫn bạn cách tùy biến điều kiện thời gian trong hàm SUMIFS để giải quyết bài toán trên một cách triệt để và chính xác nhất.

Nguyên tắc hoạt động và thách thức khi xử lý dữ liệu ngày tháng

Để áp dụng thành công hàm SUMIFS cho dữ liệu thời gian, trước tiên chúng ta cần hiểu rõ bản chất của vấn đề. Hàm SUMIFS hoạt động dựa trên nguyên tắc so khớp chính xác hoặc so khớp theo dải điều kiện.

Nếu cột dữ liệu của bạn (Criteria Range) chứa thông tin dạng đầy đủ dd/mm/yyyy (ví dụ: 15/01/2025), nhưng bạn lại đặt điều kiện là “Tháng 1”, Excel sẽ không thể tự động hiểu để tính tổng các ngày trong tháng 1.

Giải pháp tối ưu nhất ở đây là chuyển đổi tư duy từ “Tháng X” sang một “Khoảng thời gian”. Cụ thể, để tính tổng doanh số của tháng 1 năm 2012, chúng ta sẽ yêu cầu Excel tính tổng các giá trị thỏa mãn hai điều kiện đồng thời:

Ngày bán hàng lớn hơn hoặc bằng ngày đầu tiên của tháng (01/01/2012).
Ngày bán hàng nhỏ hơn hoặc bằng ngày cuối cùng của tháng đó; hoặc nhỏ hơn ngày đầu tiên của tháng kế tiếp (01/02/2012).

Xem thêm: Sao chép Dữ liệu trong Power BI: Thủ thuật Nâng Cao Hiệu Suất

Phân tích bài toán ví dụ thực tế

Chúng ta hãy cùng xem xét một ví dụ cụ thể để hình dung rõ hơn quy trình thực hiện. Giả sử bạn có một bảng dữ liệu bán hàng và cần lập báo cáo tổng hợp doanh số theo từng nhân viên, trải dài qua các tháng của các năm khác nhau.

Dữ liệu đầu vào bao gồm:

Cột A (Ngày): Chứa dữ liệu ngày tháng giao dịch (dd/mm/yyyy).
Cột B (Sản phẩm): Tên sản phẩm bán ra.
Cột C (Nhân viên): Tên nhân viên thực hiện.
Cột D (Số tiền): Doanh số của giao dịch.

Mục tiêu: Lập bảng tổng hợp (Matrix) với các dòng là các Tháng, các cột là các Năm, và lọc theo tên một Nhân viên cụ thể (ví dụ tại ô H2).

Bảng dữ liệu mẫu và cấu trúc báo cáo tổng hợp doanh số trong Excel

Để giải quyết yêu cầu này, chúng ta cần kết hợp hàm SUMIFS với hàm DATE để tạo ra các mốc thời gian động.

Xây dựng công thức SUMIFS chi tiết

Công thức sẽ được đặt tại ô giao nhau giữa tháng và năm trong bảng báo cáo (ví dụ ô G4). Chúng ta sẽ phân tích từng thành phần của hàm SUMIFS(sum_range, criteria_range1, criteria1, ...):

1. Xác định vùng tính tổng và điều kiện nhân viên

Đây là phần cơ bản nhất của hàm:

Sum_range (Vùng tính tổng): Là cột Số tiền (D4:D31).
Criteria_range1 (Vùng điều kiện 1): Là cột Nhân viên (C4:C31).
Criteria1 (Điều kiện 1): Tên nhân viên cần báo cáo, nằm tại ô H2.

2. Xử lý điều kiện thời gian (Tháng và Năm)

Vì bảng dữ liệu không có cột riêng cho Tháng và Năm, chúng ta sử dụng cột Ngày (Cột A) làm vùng điều kiện cho cả hai mốc thời gian (đầu tháng và cuối tháng).

Chúng ta cần công thức xác định ngày đầu tháng dựa trên số liệu năm (dòng 3) và tháng (cột F). Hàm DATE(Year, Month, Day) sẽ rất hữu ích ở đây:

DATE(G3, F4, 1): Trả về ngày mùng 1 của tháng F4 trong năm G3.

Thiết lập các điều kiện:

Điều kiện “Từ ngày”:
- Criteria_range2: Cột Ngày (A4:A31).
- Criteria2: Lớn hơn hoặc bằng ngày đầu tháng. Cú pháp: ">=" & DATE(G3, F4, 1).
Điều kiện “Đến ngày”:
- Criteria_range3: Vẫn là cột Ngày (A4:A31).
- Criteria3: Nhỏ hơn ngày đầu tiên của tháng kế tiếp. Đây là mẹo giúp công thức gọn hơn so với việc tìm ngày cuối tháng (30, 31 hay 28, 29).
- Cú pháp: "<" & DATE(G3, F4 + 1, 1). (Hàm DATE thông minh sẽ tự hiểu tháng 12 + 1 là tháng 1 năm sau).

3. Công thức tổng hợp sơ bộ

Tại ô G4, công thức chưa cố định địa chỉ sẽ trông như sau:

=SUMIFS(D4:D31, C4:C31, H2, A4:A31, ">="&DATE(G3,F4,1), A4:A31, "<"&DATE(G3,F4+1,1))

Tối ưu hóa công thức với tham chiếu tuyệt đối và hỗn hợp

Để công thức trên có thể hoạt động đúng khi bạn sao chép (Copy/Paste hoặc Drag) sang các ô khác (các tháng khác, năm khác), việc sử dụng kỹ thuật cố định địa chỉ (dấu $) là bắt buộc.

Xem thêm: Hàm REPLACE và SUBSTITUTE trong Excel: Hướng dẫn chi tiết

Dưới đây là quy tắc cố định cho từng thành phần:

Vùng dữ liệu nguồn ($): Các vùng D4:D31, C4:C31, A4:A31 cần được cố định hoàn toàn để không bị trượt khi copy công thức.
- => $D$4:$D$31, $C$4:$C$31, $A$4:$A$31.
Ô chứa tên nhân viên ($): Luôn nằm tại H2 dù công thức ở đâu.
- => $H$2.
Dòng chứa Năm (G3): Khi copy công thức xuống các tháng dưới, dòng 3 phải giữ nguyên (để vẫn lấy đúng năm). Khi copy sang phải, cột G phải đổi thành H, I…
- => Cố định dòng: G$3.
Cột chứa Tháng (F4): Khi copy sang phải các năm khác, cột F phải giữ nguyên (để vẫn lấy đúng tháng). Khi copy xuống dưới, dòng 4 phải đổi thành 5, 6…
- => Cố định cột: $F4.

Công thức hoàn chỉnh cuối cùng

Kết hợp tất cả các yếu tố trên, công thức chuẩn tại ô G4 sẽ là:

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

Thao tác áp dụng:

Nhập công thức trên vào ô G4.
Kéo công thức sang phải (Fill Right) cho các năm còn lại.
Kéo công thức xuống dưới (Fill Down) cho các tháng từ tháng 1 đến tháng 12.

Như vậy, bạn đã hoàn thành bảng báo cáo tổng hợp doanh số theo ma trận Tháng – Năm một cách tự động hoàn toàn.

Kết luận và Lời khuyên

Việc sử dụng hàm SUMIFS kết hợp với hàm DATE và các toán tử so sánh (>=, <) là kỹ thuật nền tảng để xử lý dữ liệu thời gian trong Excel. Phương pháp này không chỉ áp dụng cho báo cáo theo tháng mà còn có thể tùy biến cho báo cáo theo quý, theo tuần hoặc bất kỳ khoảng thời gian tùy chọn nào.

Một số điểm cần lưu ý:

Đảm bảo cột Ngày trong dữ liệu nguồn (Cột A) phải được định dạng chuẩn Date (không phải Text).
Kiểm tra kỹ các dấu $ trong công thức, đây là nguyên nhân phổ biến nhất gây sai lệch số liệu khi lập bảng tổng hợp.
Bạn có thể áp dụng logic tương tự để tính tổng theo Sản phẩm thay vì Nhân viên, chỉ cần thay đổi vùng Criteria_range1 và Criteria1 tương ứng.

Hy vọng bài viết này đã giúp bạn nắm vững kỹ thuật trích xuất báo cáo động trong Excel. Hãy thực hành ngay trên dữ liệu của bạn để thấy hiệu quả tức thì!