Thời gian đọc ước tính: 12 phút
Những điểm chính
- Tổng lũy kế (Running Total) giúp theo dõi sự thay đổi tích lũy của dữ liệu theo thời gian, ứng dụng trong quản lý dòng tiền và kho hàng.
- Công thức cộng đơn giản dễ thực hiện nhưng dễ gặp lỗi tham chiếu khi xóa hàng.
- Hàm SUM với tham chiếu hỗn hợp là phương pháp ổn định và phổ biến nhất cho dữ liệu dạng bảng thường.
- Excel Table (Bảng dữ liệu) cung cấp tham chiếu cấu trúc, tự động cập nhật công thức khi thêm dữ liệu mới.
- Power Query hỗ trợ tính toán tổng lũy kế trong quy trình xử lý và làm sạch dữ liệu tự động.
- Pivot Table tích hợp sẵn tính năng tính tổng lũy kế nhanh chóng cho báo cáo tổng hợp.
Tổng lũy kế (hay còn gọi là Running Total hoặc Cumulative Sum) là một chỉ số quan trọng trong phân tích dữ liệu tài chính và kinh doanh. Chỉ số này cho bạn biết tổng giá trị tích lũy tính đến thời điểm hiện tại. Ví dụ, nếu bạn có dữ liệu bán hàng hàng tháng, tổng lũy kế sẽ cho biết doanh thu đạt được từ ngày đầu tháng đến một ngày cụ thể bất kỳ.
Ngoài doanh thu, Running Total còn được ứng dụng rộng rãi trong các tình huống như:
- Theo dõi số dư tiền mặt trong sổ cái kế toán.
- Quản lý mức tồn kho lũy kế.
- Theo dõi tiến độ hoàn thành mục tiêu (KPI) theo thời gian thực.
Trong Microsoft Excel, có nhiều phương pháp để tính toán chỉ số này tùy thuộc vào cấu trúc dữ liệu của bạn. Nếu dữ liệu ở dạng bảng tính thông thường, bạn có thể dùng hàm SUM. Nếu dữ liệu được định dạng là Excel Table, tham chiếu cấu trúc (Structured References) là lựa chọn tối ưu. Đối với các tập dữ liệu lớn cần xử lý tự động, Power Query là công cụ mạnh mẽ nhất.
Bài viết này sẽ hướng dẫn chi tiết 6 phương pháp để tính tổng lũy kế trong Excel, từ cơ bản đến nâng cao.
1. Tính Tổng Lũy Kế Với Dữ Liệu Dạng Bảng Thông Thường
Khi dữ liệu của bạn nằm trong các ô lưới thông thường và chưa được chuyển đổi sang định dạng Excel Table, bạn có thể sử dụng các công thức toán học cơ bản.
Sử dụng toán tử cộng đơn giản
Giả sử bạn có dữ liệu bán hàng theo ngày và cần tính tổng lũy kế tại cột C.
Dữ liệu bán hàng theo ngày để tính tổng lũy kế
Quy trình thực hiện bao gồm 3 bước:
Bước 1: Tại ô C2 (ô đầu tiên cần tính tổng), bạn nhập công thức:
=B2
Thao tác này giúp lấy giá trị doanh thu của ngày đầu tiên làm mốc khởi điểm.
Nhập giá trị B2 vào ô đầu tiên của cột tổng lũy kế
Bước 2: Tại ô C3, bạn nhập công thức sau:
=C2+B3
Công thức này cộng tổng lũy kế của ngày hôm trước (C2) với doanh thu của ngày hiện tại (B3).
Nhập công thức cộng dồn tại ô C3
Bước 3: Sao chép công thức cho toàn bộ cột. Bạn có thể nhấp đúp vào góc dưới bên phải của ô C3 (Fill Handle) hoặc kéo thả xuống các ô còn lại. Excel sẽ tự động điều chỉnh tham chiếu dòng để tính toán chính xác.
Kết quả sẽ hiển thị như hình dưới đây:
Áp dụng công thức cho toàn bộ cột dữ liệu
Ưu điểm và Nhược điểm:
Phương pháp này đơn giản và dễ hiểu về mặt logic toán học. Tuy nhiên, nó có một hạn chế lớn: Nếu bạn xóa bất kỳ hàng nào trong vùng dữ liệu, tất cả các ô phía dưới sẽ báo lỗi tham chiếu #REF! do mắt xích liên kết bị phá vỡ.
Lỗi REF khi xóa một hàng dữ liệu
Nếu dữ liệu của bạn thường xuyên thay đổi hoặc xóa dòng, hãy sử dụng phương pháp tiếp theo với hàm SUM.
Sử dụng hàm SUM với tham chiếu hỗn hợp
Đây là phương pháp chuyên nghiệp hơn, sử dụng khái niệm “Vùng mở rộng” (Expanding Range) trong Excel.
Giả sử bạn vẫn sử dụng tập dữ liệu trên. Tại ô C2, hãy nhập công thức sau:
=SUM($B$2:B2)
Công thức SUM với tham chiếu hỗn hợp để tính tổng lũy kế
Giải thích cơ chế hoạt động của công thức:
Trong công thức =SUM($B$2:B2), chúng ta sử dụng hai loại tham chiếu:
- $B$2 (Tham chiếu tuyệt đối): Dấu
$cố định ô B2 làm điểm bắt đầu. Khi bạn sao chép công thức xuống dưới, điểm bắt đầu này không thay đổi. - B2 (Tham chiếu tương đối): Phần này không có dấu
$. Khi bạn sao chép công thức xuống ô C3, tham chiếu này sẽ tự động biến thành B3.
Kết quả là:
- Tại ô C2:
=SUM($B$2:B2)-> Tính tổng từ B2 đến B2. - Tại ô C3:
=SUM($B$2:B3)-> Tính tổng từ B2 đến B3. - Tại ô C4:
=SUM($B$2:B4)-> Tính tổng từ B2 đến B4.
Ưu điểm lớn nhất của phương pháp này là tính ổn định. Ngay cả khi bạn xóa một hàng ở giữa, công thức sẽ tự động điều chỉnh vùng tính tổng mà không gây ra lỗi #REF!.
2. Tính Tổng Lũy Kế Trong Excel Table (Bảng Dữ Liệu)
Chuyển đổi dữ liệu sang dạng Excel Table (nhấn Ctrl + T) mang lại nhiều lợi ích quản lý dữ liệu, đặc biệt là tính năng Tham chiếu cấu trúc (Structured References). Tính năng này giúp công thức dễ đọc hơn và tự động mở rộng khi có dữ liệu mới.
Giả sử bạn có một bảng tên là “SalesData”. Để tính tổng lũy kế tại cột C, bạn dùng công thức sau:
=SUM(SalesData[[#Headers],[Sale]]:[@Sale])
Công thức tham chiếu cấu trúc trong Excel Table
Cách tạo công thức mà không cần gõ tay:
- Tại ô C2, gõ
=SUM(. - Nhấp chuột vào ô tiêu đề của cột doanh số (ô B1). Excel sẽ tự điền
SalesData[[#Headers],[Sale]]. - Gõ dấu hai chấm
:. - Nhấp chuột vào ô dữ liệu doanh số cùng dòng (ô B2). Excel sẽ điền
[@Sale]. - Đóng ngoặc
)và nhấn Enter.
Lợi ích:
- Tự động điền: Bạn không cần kéo công thức xuống, Excel Table sẽ tự động áp dụng cho toàn bộ cột.
- Tự động cập nhật: Khi bạn nhập thêm dữ liệu bán hàng mới vào cuối bảng, cột Running Total sẽ tự động tính toán mà không cần thao tác thêm.
3. Sử Dụng Power Query Để Tính Tổng Lũy Kế
Power Query là công cụ xử lý dữ liệu mạnh mẽ, đặc biệt hữu ích khi bạn cần trích xuất dữ liệu từ nhiều nguồn, làm sạch và biến đổi trước khi đưa vào Excel. Việc tính toán ngay trong Power Query giúp quy trình làm việc trở nên tự động hóa hoàn toàn.
Giả sử bạn có bảng dữ liệu cần xử lý:
Dữ liệu mẫu để thực hiện Power Query
Quy trình thực hiện chi tiết:
- Chọn một ô bất kỳ trong bảng dữ liệu.
- Trên thanh công cụ, chọn thẻ Data (Dữ liệu).
Chọn thẻ Data trên thanh công cụ - Trong nhóm Get & Transform Data, chọn From Table/Range. Cửa sổ Power Query Editor sẽ mở ra.
Chọn From Table Range để mở Power Query - (Tùy chọn) Nếu cột ngày tháng chưa được sắp xếp, hãy nhấp vào mũi tên lọc ở cột Date và chọn Sort Ascending để đảm bảo tính lũy kế theo đúng trình tự thời gian.
Sắp xếp dữ liệu ngày tháng theo thứ tự tăng dần - Chuyển sang thẻ Add Column (Thêm cột).
Chọn thẻ Add Column - Trong nhóm General, nhấp vào mũi tên bên cạnh Index Column và chọn From 1. Bước này tạo ra một cột số thứ tự để làm mốc tính toán.
Tạo cột Index bắt đầu từ 1 - Chọn Custom Column trong thẻ Add Column.
Chọn Custom Column - Đặt tên cột mới là “Running Total”.
Đặt tên cho cột mới - Nhập công thức sau vào ô Custom column formula:
List.Sum(List.Range(#"Added Index"[Sale],0,[Index]))
Nhập công thức M-code vào Power Query - Kiểm tra thông báo “No syntax errors have been detected” ở góc dưới để đảm bảo công thức đúng.
Kiểm tra lỗi cú pháp - Nhấn OK để tạo cột.
- Xóa cột Index phụ trợ (Chuột phải vào cột Index > Remove).
Xóa cột Index không cần thiết - Vào thẻ File > Close & Load để xuất dữ liệu ra Excel.
Chọn Close and Load để xuất dữ liệu
Kết quả cuối cùng là một bảng dữ liệu mới có chứa cột tổng lũy kế được tính toán tự động.
Kết quả Running Total từ Power Query
Lưu ý về hiệu năng:
Phương pháp dùng List.Sum và List.Range hoạt động tốt với tập dữ liệu nhỏ và vừa. Tuy nhiên, với dữ liệu lớn (hàng chục nghìn dòng), tốc độ xử lý có thể bị chậm. Trong trường hợp đó, bạn nên sử dụng ngôn ngữ M nâng cao hoặc tính toán tại nguồn (SQL) trước khi đưa vào Excel.
4. Tính Tổng Lũy Kế Theo Điều Kiện (Conditional Running Total)
Trong thực tế kinh doanh, bạn thường cần tính lũy kế riêng biệt cho từng danh mục sản phẩm hoặc từng nhân viên bán hàng. Ví dụ: Tính tổng lũy kế riêng cho “Printer” và “Scanner”.
Dữ liệu mẫu tính tổng lũy kế theo điều kiện
Chúng ta sẽ sử dụng hàm SUMIF kết hợp với tham chiếu mở rộng.
Công thức cho sản phẩm Printer (Cột D1 là “Printer”):
=SUMIF($C$2:C2,$D$1,$B$2:B2)
Kết quả tổng lũy kế cho Printer
Công thức cho sản phẩm Scanner (Cột E1 là “Scanner”):
=SUMIF($C$2:C2,$E$1,$B$2:B2)
Kết quả tổng lũy kế cho Scanner
Phân tích công thức:
- Range ($C$2:C2): Vùng chứa điều kiện (tên sản phẩm). Vùng này mở rộng dần khi kéo công thức xuống.
- Criteria ($D$1): Điều kiện cần lọc (ví dụ: “Printer”).
- Sum_Range ($B$2:B2): Vùng chứa giá trị cần cộng (doanh số). Vùng này cũng mở rộng tương ứng với vùng điều kiện.
Kết quả là tại mỗi dòng, Excel chỉ cộng dồn các giá trị phía trên nó thỏa mãn điều kiện là “Printer” hoặc “Scanner”.
5. Tính Tổng Lũy Kế Trong Pivot Table
Pivot Table là công cụ báo cáo nhanh và mạnh mẽ nhất trong Excel. Bạn không cần viết bất kỳ công thức nào để tạo Running Total.
Giả sử bạn đã tạo một Pivot Table với cột Ngày (Date) và Doanh số (Sale).
Pivot Table ban đầu
Các bước thực hiện:
- Kéo trường “Sale” thả vào vùng Values một lần nữa. Bây giờ bạn sẽ có 2 cột Sum of Sale.
Kéo trường Sale vào vùng Values lần thứ hai - Nhấp chuột phải vào tiêu đề cột “Sum of Sale2” vừa tạo, chọn Value Field Settings (Cài đặt trường giá trị).
Chọn Value Field SettingsHoặc nhấp vào mũi tên bên cạnh tên trường trong bảng điều khiển PivotTable Fields.
Mở cài đặt từ bảng điều khiển - Trong hộp thoại, đặt tên mới tại mục Custom Name là “Running Totals”.
Đổi tên cột thành Running Totals - Chuyển sang thẻ Show Values As (Hiển thị giá trị dưới dạng).
Chuyển sang thẻ Show Values As - Trong danh sách thả xuống, chọn Running Total In.
Chọn Running Total In - Tại mục Base field, chọn trường “Date” (vì chúng ta muốn tính lũy kế theo thời gian).
Chọn trường cơ sở là Date - Nhấn OK.
Cột thứ hai trong Pivot Table bây giờ sẽ hiển thị tổng doanh thu lũy kế theo từng ngày.
Kết quả Running Total trong Pivot Table
Phương pháp này cực kỳ linh hoạt. Nếu bạn thay đổi cấu trúc báo cáo (ví dụ: nhóm theo tháng hoặc quý), Pivot Table sẽ tự động tính toán lại Running Total dựa trên ngữ cảnh mới mà không cần sửa công thức.
Những Lỗi Thường Gặp Khi Tính Tổng Lũy Kế
Khi làm việc với các công thức Running Total, người dùng thường mắc phải một số lỗi cơ bản sau:
- Quên cố định điểm đầu: Trong phương thức dùng hàm SUM, việc không sử dụng dấu
$cho ô đầu tiên (ví dụB2:B2thay vì$B$2:B2) sẽ khiến công thức chỉ tính tổng cho một phạm vi trượt chứ không phải lũy kế từ đầu. - Dữ liệu chưa sắp xếp: Đối với Power Query và một số logic tính toán thủ công, nếu dữ liệu ngày tháng lộn xộn, kết quả lũy kế sẽ không phản ánh đúng tiến trình thời gian thực tế.
- Lỗi tham chiếu vòng (Circular Reference): Đôi khi người dùng vô tình chọn vùng tính tổng bao gồm cả ô chứa công thức, gây ra lỗi tính toán vô hạn.
Hiểu rõ bản chất của từng phương pháp sẽ giúp bạn lựa chọn giải pháp phù hợp nhất cho báo cáo tài chính và dữ liệu của mình.
FAQ – Câu Hỏi Thường Gặp
1. Khi nào nên dùng hàm SUM thay vì Pivot Table?
Bạn nên dùng hàm SUM (với tham chiếu tuyệt đối) khi cần tính toán trực tiếp trên bảng dữ liệu thô để phục vụ cho các bước xử lý tiếp theo. Pivot Table phù hợp hơn khi bạn cần tạo báo cáo tổng hợp cuối cùng để trình bày.
2. Làm thế nào để tính tổng lũy kế reset theo từng năm?
Để tính tổng lũy kế và tự động reset về 0 khi sang năm mới, bạn cần sử dụng công thức IF kết hợp SUM hoặc sử dụng tính năng “Running Total In” của Pivot Table và nhóm dữ liệu theo Năm/Tháng.
3. Power Query có làm nặng file Excel không?
Power Query thực chất giúp giảm nhẹ file Excel vì nó nén dữ liệu rất tốt và chỉ tải kết quả cuối cùng ra bảng tính. Tuy nhiên, quy trình tính toán (List.Sum) phức tạp trên tập dữ liệu quá lớn có thể làm chậm quá trình Refresh (làm mới) dữ liệu.
4. Tại sao tôi bị lỗi #REF! khi dùng công thức cộng đơn giản?
Lỗi #REF! xuất hiện vì công thức cộng đơn giản (ví dụ =C2+B3) dựa vào vị trí của ô ngay phía trên. Khi bạn xóa hàng chứa ô C2, công thức tại C3 mất điểm tham chiếu. Hãy chuyển sang dùng hàm =SUM($B$2:B2) để khắc phục.
5. Tôi có thể tính tổng lũy kế cho biểu đồ không?
Có, sau khi tính cột Running Total bằng bất kỳ phương pháp nào nêu trên, bạn có thể chọn cột đó để vẽ biểu đồ đường (Line Chart), giúp trực quan hóa xu hướng tăng trưởng tích lũy.
