Khi làm việc với bảng dữ liệu Excel, chắc chắn sẽ có lúc ta muốn tính lũy kế theo ngày, theo tháng hoặc theo nhóm đối tượng nào đó? Dưới đây là các hàm tính lũy kế trong Excel:
- Tính lũy kế cộng dồn theo thứ tự dòng:
=SUM($C$2:C2) - Tính lũy kế cộng dồn theo từng Nhóm:
=SUMIF($B$2:B2; B2; $C$2:C2) - Tính lũy kế cộng dồn Ngày ≤ hiện tại:
=SUMIFS(C; A; "<="&A2) - Tính lũy kế cộng dồn Ngày + Nhóm:
=SUMIFS(C; A; "<="&A2; B; B2) - Tính lũy kế cộng dồn trong Tháng (và + Nhóm nếu cần): tạo EOMONTH rồi SUMIFS theo Tháng + Ngày ≤
- Tính lũy kế cộng dồn chỉ hàng hiển thị khi Filter:
=SUMPRODUCT(SUBTOTAL(103; OFFSET(...)); giá trị) - Nếu sử dụng Excel 365:
=SCAN(0; cột_giá_trị; LAMBDA(a; v; a+v))
Dưới đây sẽ là cách áp dụng và giải thích chi tiết về các hàm công thức tính lũy kế. Trong bài này ta sẽ sử dụng bảng dữ liệu mẫu sau:
Tải file công thức tính lũy kế trong Excel
1. Lũy kế cơ bản theo hàng (cộng dồn từ đầu đến dòng hiện tại)
Để tính lũy kế theo hàng cộng dồn từ đầu đến dòng hiện tại ta sử dụng công thức sau:
=SUM($C$2:C2)
Trong đó:
SUM(number1; [number2]; …): Cộng các số.$C$2: Cố định điểm đầu (dùng dấu $ để khi kéo xuống không đổi).C2: Tương đối (khi kéo xuống sẽ thành C3, C4,…)
Khi bạn kéo công thức xuống, vùng sẽ “mở rộng” như:
D2: SUM($C$2:C2)→ cộng C2D3: SUM($C$2:C3)→ cộng C2:C3D10: SUM($C$2:C10)→ cộng C2:C10
=> Đây là cách lũy kế kinh điển, đơn giản và nhanh nhất.
2. Tính lũy kế theo nhóm (mỗi nhóm cộng dồn riêng)
Trong các trường hợp cần tính lũy kế cho từng nhóm riêng biệt, ví dụ như sản phẩm A, B, C hoặc các loại tài sản khác nhau. Để tính lũy kế theo nhóm ta sử dụng công thức sau:
=SUMIF($B$2:B2; B2; $C$2:C2)
Trong đó:
- SUMIF(range; criteria; [sum_range]):
- range = vùng dùng để đối chiếu tiêu chí (ở đây là cột Nhóm).
- criteria = giá trị tiêu chí (ở đây là B2, là nhóm của dòng hiện tại).
- sum_range = vùng cộng (cột Giá trị).
- Vì dùng vùng mở rộng
$B$2:B2và$C$2:C2, khi kéo xuống:- Ở dòng nào, hàm chỉ cộng những dòng cùng nhóm từ đầu tới dòng đó → Lũy kế sẽ reset khi sang nhóm khác.
Lưu ý: trong công thức trên có vùng mở rộng là $B$2:B2 và $C$2:C2, tại sao không sử dụng B:B + C:C mặc dù công thức vẫn chạy? Bởi vì khi dùng B:B + C:C chạy trên bảng dữ liệu lớn sẽ gây chậm và dễ nuốt đầu bảng nếu có tiêu đề. Ta nên dùng $B$2:B2 để ổn định và chạy nhanh hơn.
3. Tính lũy kế theo ngày (cộng tất cả giá trị có Ngày ≤ Ngày hiện tại)
Trong các tình huống cần tính giá trị lũy kế cập nhật đến ngày hiện tại thì ta sẽ sử dụng công thưc sau:
=SUMIFS($C$2:$C$1000; $A$2:$A$1000; "<="&A2)
Trong đó:
- SUMIFS(sum_range; criteria_range1; criteria1; …): Cộng theo nhiều điều kiện.
sum_range = $C$2:$C$1000(cột Giá trị).criteria_range1 = $A$2:$A$1000(cột Ngày).criteria1 = "<="&A2nghĩa là ngày ≤ ngày tại dòng hiện tại.
- Vì trong Excel, dấu so sánh phải đặt trong chuỗi (“<=”), nên ta dùng nối chuỗi &A2
Lưu ý: Khi áp dụng công thức trên cần phải đảm bảo cột Ngày thật sự là kiểu Date. Nếu dữ liệu là Text thì dùng Text to Columns hoặc VALUE() để chuyển về ngày/số.
Trong trường hợp ta muốn kết hợp cả việc tính lũy kế theo nhóm và tính đến ngày hiện tại thì ta sẽ sử dụng công thức sau:
=SUMIFS($C$2:$C$1000; $A$2:$A$1000; "<="&A2; $B$2:$B$1000; B2)
Trong công thức trên:
- Thêm một cặp vùng điều kiện + tiêu chí cho Nhóm.
- Kéo xuống là có lũy kế riêng cho từng nhóm theo thời gian.
Nếu gặp vấn đề về trùng ngày hoặc cần đúng thứ tự ngày thì ta sẽ tạo thêm một cột STT (ví dụ D2:D) rồi dùng công thức sau:
=SUMIFS($C$2:$C$1000; $A$2:$A$1000; "<"&A2)
+SUMIFS($C$2:$C$1000; $A$2:$A$1000; A2; $D$2:$D$1000; "<="&D2)
- Phần 1 cộng tất cả ngày nhỏ hơn.
- Phần 2 cộng cùng ngày nhưng STT ≤ STT hiện tại → giữ đúng thứ tự.
4. Tính lũy kế theo tháng và theo nhóm trong tháng
Để tính lũy kế theo tháng, ta cần phải tạo cột tháng (ví dụ: F2) với công thức như sau:
=EOMONTH(A2; 0)
Trong đó:
- EOMONTH(start_date; months): Trả về ngày cuối tháng.
- Ở đây, months=0 → cuối chính tháng của A2. (Dùng “ngày cuối tháng” để gom nhóm tháng ổn định, không lệ thuộc ngày 1/15/…).
Tiếp theo, ta tính lũy kế trong cùng tháng đến ngày hiện tại (G2) với công thức sau:
=SUMIFS($C$2:$C$1000; $F$2:$F$1000; F2; $A$2:$A$1000; "<="&A2)
Trong đó:
- Điều kiện 1: cùng tháng ($F$2:$F$1000; F2)
- Điều kiện 2: ngày ≤ ngày hiện tại
Nếu cần tính lũy kế theo tháng và theo nhóm thì ta sử dụng công thức sau:
=SUMIFS($C$2:$C$1000; $F$2:$F$1000; F2; $A$2:$A$1000; "<="&A2; $B$2:$B$1000; B2)
5. Tính lũy kế chỉ tính hàng đang hiển thị (lọc/ẩn)
Với các bảng dữ liệu lớn, sẽ có lúc ta cần tính lũy kế sau khi đã lọc hoặc ẩn các dữ liệu. Để tính lũy kế lúc này ta sẽ sử dụng công thức sau:
=SUMPRODUCT(
SUBTOTAL(103; OFFSET($C$2; ROW($C$2:C2)-ROW($C$2); 0; 1));
$C$2:C2
)
Trong đó:
- SUBTOTAL(function_num; ref1; …): 103 = COUNTA nhưng bỏ qua các hàng ẨN do lọc (tức chỉ “đếm nếu đang hiển thị”).
- OFFSET(reference; rows; cols; [height]; [width]):
- Tạo ra một vùng đúng 1 ô tại cùng hàng tương ứng trong cột C.
- Khi lồng với ROW($C$2:C2), nó sẽ tạo danh sách các ô từ C2 đến C(dòng hiện tại).
- ROW($C$2:C2)-ROW($C$2) → cho ra 0,1,2,… để OFFSET nhảy từng dòng.
- SUBTOTAL(103; …) với vùng 1 ô:
- Trả về 1 nếu ô đó đang hiển thị, 0 nếu bị ẩn bởi Filter.
- SUMPRODUCT(array1; array2):
Nhân từng phần tử của mảng hiển thị (0/1) với mảng giá trị $C$2:C2, rồi cộng lại.
⇒ Kết quả: Cộng dồn chỉ các dòng đang hiển thị đến dòng hiện tại.
6. Tính lũy kế bằng SCAN (chỉ áp dụng với Excel 365)
Nếu đang sử dụng Excel 365 thì có thể tính lũy kế một cách đơn giản hơn bằng SCAN với công thức như sau:
=SCAN(0; C2:C1000; LAMBDA(a; v; a+v))
Trong đó:
SCAN(initial_value; array; lambda_accumulator):- Duyệt qua mảng
C2:C1000, cộng dồn từng phần tử và trả về danh sách lũy kế.
- Duyệt qua mảng
LAMBDA(a; v; a+v):- a = giá trị tích lũy đến bước trước,
- v = giá trị hiện tại,
- a+v = công thức cộng dồn.
Trên đây là các công thức tính lũy kế theo ngày, tháng, theo nhóm và điều kiện. Mong rằng bài viết này hữu ích và chúc các bạn áp dụng thành công!
