Công Thức Tính Lũy Kế Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Cách Đổi Mật Khẩu Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Giới thiệu chung về khái niệm lũy kế trong Excel

Trong môi trường làm việc hiện đại, việc quản lý, phân tích và trình bày dữ liệu một cách hiệu quả là yếu tố then chốt giúp doanh nghiệp đưa ra quyết định nhanh chóng và chính xác. Một trong những kỹ thuật phổ biến và cực kỳ hữu ích trong việc tổng hợp dữ liệu là lũy kế (cumulative). Lũy kế giúp chúng ta tính tổng dần, tích lũy các giá trị theo một chuỗi thời gian hoặc một danh mục nào đó, từ đó dễ dàng nhận diện xu hướng, phát hiện bất thường và dự báo tương lai.

Microsoft Excel, với khả năng linh hoạt và mạnh mẽ, cung cấp nhiều cách để thực hiện tính lũy kế, từ các công thức cơ bản như SUM, SUMIF, SUMIFS, OFFSET, INDEX, MATCH cho đến các hàm mới như LET, LAMBDA, SCAN (trong Excel 365). Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, từng bước, về cách tính lũy kế trong Excel, từ các công thức đơn giản cho người mới bắt đầu đến các kỹ thuật nâng cao dành cho người dùng chuyên nghiệp.

Có thể bạn quan tâm: Cách Giữ Tiêu Đề Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Hiểu rõ mục tiêu và dữ liệu của bạn

Trước khi bắt đầu viết công thức, bạn cần xác định rõ:

Ví dụ thực tế: Bạn có một bảng doanh thu bán hàng theo tháng cho các khu vực khác nhau và muốn tính tổng doanh thu lũy kế cho mỗi khu vực qua từng tháng.

| Tháng | Khu vực | Doanh thu |
|-------|----------|-----------|
| 01/2026 | Miền Bắc | 120,000 |
| 01/2026 | Miền Trung | 80,000 |
| 01/2026 | Miền Nam | 150,000 |
| 02/2026 | Miền Bắc | 130,000 |
| 02/2026 | Miền Trung | 90,000 |
| 02/2026 | Miền Nam | 160,000 |
| ... | ... | ... |

Có thể bạn quan tâm: Cách Xếp Lịch Trực Trên Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

2. Cách tính lũy kế cơ bản bằng công thức SUM

2.1. Sử dụng công thức SUM với tham chiếu tuyệt đối và tương đối

Giả sử dữ liệu doanh thu nằm ở cột C, bắt đầu từ hàng 2 (C2). Để tính lũy kế cho cột này, bạn có thể sử dụng công thức:

=SUM($C$2:C2)

• $C$2 là tham chiếu tuyệt đối đến ô đầu tiên của dải dữ liệu (không thay đổi khi sao chép công thức).
• C2 là tham chiếu tương đối: khi sao chép công thức xuống các ô dưới, nó sẽ tự động thay đổi thành C3, C4, …, tạo ra dải dữ liệu mở rộng dần.

Bước thực hiện:

1. Nhập công thức trên vào ô D2 (cột D sẽ là cột lũy kế).
2. Kéo công thức xuống tới cuối danh sách (hoặc nhấn Ctrl + D để sao chép nhanh).

Kết quả: Cột D sẽ chứa tổng lũy kế của doanh thu từ đầu danh sách tới dòng hiện tại.

2.2. Khi dữ liệu có tiêu đề và cần bỏ qua hàng tiêu đề

Nếu tiêu đề nằm ở hàng 1 và dữ liệu bắt đầu từ hàng 2, công thức vẫn giữ nguyên như trên (=SUM($C$2:C2)). Tuy nhiên, nếu bạn muốn tính lũy kế cho các nhóm (ví dụ: khu vực), công thức cần được điều chỉnh để chỉ tính trong cùng một nhóm.

3. Tính lũy kế theo nhóm bằng SUMIF và SUMIFS

3.1. Sử dụng SUMIF

Công thức SUMIF cho phép bạn tính tổng các giá trị thỏa mãn một tiêu chí duy nhất.

Công thức tổng quát:

=SUMIF(range, criteria, sum_range)

• range: dải ô chứa tiêu chí (ví dụ: cột Khu vực).
• criteria: tiêu chí cần thỏa mãn (ví dụ: "Miền Bắc").
• sum_range: dải ô chứa giá trị cần tính tổng (ví dụ: cột Doanh thu).

Ví dụ thực tế: Tính lũy kế doanh thu cho khu vực “Miền Bắc” đến tháng hiện tại.

Giả sử:

• Khu vực ở cột B (B2:B100).
• Tháng ở cột A (A2:A100), dạng ngày tháng.
• Doanh thu ở cột C (C2:C100).

Công thức ở ô D2 (đối với dòng đầu tiên, tháng 01/2026):

=SUMIF($B$2:B2, B2, $C$2:C2)

Giải thích:

• $B$2:B2 – dải khu vực từ đầu danh sách tới dòng hiện tại.
• B2 – tiêu chí là khu vực của dòng hiện tại.
• $C$2:C2 – dải doanh thu tương ứng.

Kéo công thức xuống, mỗi dòng sẽ tính tổng lũy kế cho khu vực tương ứng tới thời điểm hiện tại.

3.2. Sử dụng SUMIFS cho nhiều tiêu chí

Khi cần tính lũy kế dựa trên nhiều điều kiện (ví dụ: khu vực + tháng ≤ hiện tại), bạn dùng SUMIFS.

Công thức tổng quát:

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …)

Ví dụ: Tính lũy kế doanh thu cho khu vực “Miền Bắc” trong các tháng đến và bao gồm tháng của dòng hiện tại.

=SUMIFS($C$2:$C$100, $B$2:$B$100, B2, $A$2:$A$100, "<=" & A2)

• sum_range: $C$2:$C$100 (doanh thu).
• criteria_range1: $B$2:$B$100 (khu vực) → criteria1: B2 (khu vực hiện tại).
• criteria_range2: $A$2:$A$100 (tháng) → criteria2: "<=" & A2 (các tháng ≤ tháng hiện tại).

Kết quả: Cột D sẽ hiển thị tổng lũy kế doanh thu cho khu vực tương ứng, chỉ tính các tháng trước hoặc bằng tháng hiện tại.

4. Kỹ thuật lũy kế nâng cao với hàm OFFSET và INDEX

4.1. OFFSET – tạo dải ô động

Cú pháp:

=OFFSET(reference, rows, cols, height, width)

• reference: ô bắt đầu.
• rows, cols: số hàng/cột dịch chuyển từ reference.
• height, width: kích thước của dải ô trả về (tùy chọn).

Ứng dụng tính lũy kế: Tính tổng dải ô từ đầu danh sách tới ô hiện tại bằng cách tạo dải động.

=SUM(OFFSET($C$2, 0, 0, ROW()-1, 1))

Giải thích:

• OFFSET($C$2,0,0,ROW()-1,1) tạo dải ô bắt đầu từ C2, chiều cao = ROW()-1 (số hàng từ 2 tới hàng hiện tại), chiều rộng = 1.
• SUM tính tổng dải này.

4.2. INDEX – trả về giá trị hoặc dải ô tại vị trí cụ thể

Cú pháp:

=INDEX(array, row_num, column_num)

• array: dải dữ liệu.
• row_num: số hàng muốn trả về.
• column_num: số cột muốn trả về (nếu có).

Để tạo dải ô từ C2 tới C hiện tại:

=SUM($C$2:INDEX($C:$C, ROW()))

• INDEX($C:$C, ROW()) trả về ô trong cột C tại cùng hàng với công thức.
• Dải $C$2:INDEX(...) tự động mở rộng khi sao chép công thức xuống.

Ưu điểm: Tránh sử dụng các tham chiếu toàn cột (có thể làm chậm file lớn) và không cần dùng OFFSET (được coi là hàm “volatile”, tính lại mỗi khi workbook thay đổi).

5. Tính lũy kế trong bảng PivotTable

Có thể bạn quan tâm: Công Cụ Hỗ Trợ Excel: Tối Ưu Hoá Công Việc, Nâng Cao Năng Suất Và Mở Rộng Khả Năng Phân Tích Dữ Liệu

PivotTable là công cụ mạnh mẽ để tóm tắt và phân tích dữ liệu. Để hiển thị lũy kế trong PivotTable:

1. Tạo PivotTable: Chọn dữ liệu → Insert → PivotTable.
2. Kéo trường giá trị (ví dụ: Doanh thu) vào vùng Values.
3. Thiết lập hiển thị lũy kế:
4. Click chuột phải vào bất kỳ giá trị nào trong cột Doanh thu trong PivotTable.
5. Chọn Show Values As → Running Total In.
6. Chọn trường thời gian (ví dụ: Tháng) làm Base Field.
7. Nếu muốn lũy kế theo nhóm (khu vực), hãy đặt khu vực vào Rows và thiết lập Running Total In dựa trên Tháng.

PivotTable tự động cập nhật khi dữ liệu nguồn thay đổi, rất thuận tiện cho báo cáo định kỳ.

6. Lũy kế trong Excel 365: Sử dụng hàm SCAN

Excel 365 (với cập nhật Dynamic Arrays) giới thiệu hàm SCAN, cho phép thực hiện phép toán lặp lại trên một mảng và trả về mảng kết quả lũy kế.

Cú pháp:

=SCAN(initial_value, array, lambda(accumulator, current_value, result))

• initial_value: giá trị khởi đầu (thường là 0 cho tổng).
• array: dải dữ liệu cần lũy kế.
• lambda: hàm tùy chỉnh tính toán.

Ví dụ: Lũy kế tổng doanh thu trong cột C (C2:C10).

=SCAN(0, C2:C10, LAMBDA(acc, cur, acc + cur))

Kết quả là một mảng (có thể hiển thị trong cột D) chứa tổng lũy kế. Ưu điểm:

• Không cần sao chép công thức xuống từng ô; một công thức duy nhất trả về toàn bộ mảng.
• Tính năng spill tự động mở rộng mảng tới các ô bên dưới.

6.1. Lũy kế có điều kiện bằng SCAN + FILTER

Nếu bạn muốn lũy kế chỉ cho một khu vực cụ thể, kết hợp FILTER:

=SCAN(0, FILTER(C2:C100, B2:B100="Miền Bắc"), LAMBDA(acc, cur, acc + cur))

• FILTER(C2:C100, B2:B100="Miền Bắc") trả về mảng doanh thu chỉ cho khu vực “Miền Bắc”.
• SCAN tính lũy kế trên mảng này.

Kết quả có thể được đặt vào một cột mới hoặc sử dụng trong biểu đồ.

7. Tạo biểu đồ lũy kế (Cumulative Chart)

Biểu đồ lũy kế giúp hiển thị xu hướng tích lũy một cách trực quan.

Bước 1: Đảm bảo bạn đã có cột lũy kế (theo các phương pháp trên).
Bước 2: Chọn dữ liệu (cột thời gian + cột lũy kế).
Bước 3: Vào Insert → Line Chart (đường).
Bước 4: Định dạng trục X (tháng) và trục Y (giá trị lũy kế).

Nếu muốn hiển thị các nhóm trên cùng một biểu đồ:

• Đặt mỗi nhóm (khu vực) vào một cột riêng (ví dụ: D, E, F) với lũy kế tương ứng.
• Chọn toàn bộ dải dữ liệu (tháng + các cột lũy kế) và chèn Line Chart. Excel sẽ tạo một đường cho mỗi nhóm, giúp so sánh xu hướng lũy kế giữa các khu vực.

8. Các lưu ý quan trọng khi sử dụng công thức lũy kế

9. Thực hành: Xây dựng bảng lũy kế hoàn chỉnh

9.1. Bước chuẩn bị dữ liệu

1. Nhập dữ liệu vào một bảng Excel, đặt tiêu đề: Tháng, Khu vực, Doanh thu.
2. Chuyển dữ liệu thành Table: Chọn phạm vi → Ctrl + T. Đặt tên Table là tblDoanhThu.

9.2. Thêm cột lũy kế tổng (cho mọi khu vực)

Trong Table, thêm cột mới và đặt tiêu đề “Lũy Kế Tổng”. Công thức:

=SUM(INDEX(tblDoanhThuDoanh thu,1):@Doanh thu)

Giải thích:

• tblDoanhThuDoanh thu là cột doanh thu của Table.
• INDEX(...,1) trả về ô đầu tiên của cột.
• @Doanh thu là giá trị hiện tại trong cùng hàng.
• Dải INDEX(...,1):@Doanh thu tạo dải từ đầu tới ô hiện tại, SUM tính tổng.

9.3. Thêm cột lũy kế theo khu vực

Thêm cột “Lũy Kế Khu Vực” với công thức:

=SUMIFS(tblDoanhThuDoanh thu, tblDoanhThuKhu vực,@Khu vực, tblDoanhThuTháng,"<="&@Tháng)

9.4. Thêm cột lũy kế phần trăm so với tổng

Nếu muốn biết mỗi khu vực chiếm bao nhiêu phần trăm của tổng lũy kế:

=@Lũy Kế Khu Vực/@Lũy Kế Tổng

Định dạng ô dưới dạng Percentage.

9.5. Tạo PivotTable và biểu đồ

1. PivotTable: Chọn Table → Insert → PivotTable.
2. Rows: Tháng
3. Columns: Khu vực

4. Values: Doanh thu (đặt hiển thị là Running Total In → Tháng).

5. Biểu đồ: Chọn PivotTable → Insert → Line Chart.

Bạn sẽ có một biểu đồ hiển thị lũy kế doanh thu theo tháng cho mỗi khu vực, giúp so sánh nhanh chóng.

10. Các công thức lũy kế nâng cao khác

10.1. Lũy kế trung bình (Cumulative Average)

Công thức:

=AVERAGE($C$2:C2)

Hoặc dùng SUM và COUNT để tránh tính lại toàn bộ dải:

=SUM($C$2:C2)/COUNT($C$2:C2)

10.2. Lũy kế tỷ lệ phần trăm (Cumulative Percentage)

Nếu muốn tính phần trăm lũy kế dựa trên tổng toàn bộ:

=SUM($C$2:C2)/SUM($C$2:$C$100)

10.3. Lũy kế với hàm XLOOKUP để lấy giá trị trước đó

Đôi khi bạn muốn lấy giá trị lũy kế của dòng trước (đối với các tính toán phức tạp). Sử dụng XLOOKUP:

=IFERROR(XLOOKUP(A2-1, A$2:A$100, D$2:D$100, 0), 0) + C2

• A2-1 giả sử A là số tuần hoặc ngày liên tục.
• XLOOKUP trả về lũy kế của tuần trước, cộng với giá trị hiện tại.

11. Tối ưu hiệu suất cho file Excel lớn

Khi dữ liệu lên tới hàng chục hoặc hàng trăm nghìn, công thức lũy kế có thể làm chậm workbook. Dưới đây là một số mẹo:

1. Sử dụng Table và công thức cấu trúc: Excel tự động tính toán chỉ những ô thay đổi.
2. Tránh hàm volatile: OFFSET, INDIRECT, NOW, TODAY tính lại mỗi khi workbook thay đổi.
3. Sử dụng SUMIFS thay vì SUMPRODUCT: SUMIFS tối ưu hơn cho các tiêu chí đơn giản.
4. Tận dụng Power Query: Nhập dữ liệu, thực hiện tính lũy kế trong Power Query rồi tải lại vào Sheet. Power Query thực hiện tính toán một lần, giảm tải cho công thức.
5. Sử dụng VBA (nếu phù hợp): Viết macro để tính lũy kế một lần và ghi giá trị tĩnh vào ô, thay vì để công thức tính lại liên tục.

12. Kết luận

Việc tính lũy kế trong Excel là một kỹ năng thiết yếu cho bất kỳ ai làm việc với dữ liệu – từ nhân viên kế toán, nhà phân tích tài chính, tới nhà quản lý bán hàng. Bài viết đã cung cấp:

• Công thức cơ bản (SUM, SUMIF, SUMIFS) để bắt đầu nhanh.
• Kỹ thuật nâng cao (INDEX, OFFSET, SCAN) cho các trường hợp phức tạp.
• Cách tích hợp lũy kế vào PivotTable và biểu đồ để trực quan hoá dữ liệu.
• Mẹo tối ưu hiệu suất khi làm việc với dữ liệu lớn.
• Ví dụ thực tế từ việc tính lũy kế doanh thu theo khu vực và thời gian.

Hãy áp dụng những công thức và phương pháp này vào công việc hằng ngày của bạn. Khi đã thành thạo, bạn sẽ nhận ra rằng việc tổng hợp, phân tích và dự báo trở nên nhanh chóng, chính xác và trực quan hơn rất nhiều. Chúc bạn thành công trong việc khai thác sức mạnh của Excel để đưa ra những quyết định kinh doanh sáng suốt!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.