Hướng dẫn tính lũy kế doanh số bán hàng bằng hàm SUM và vẽ đồ thị doanh số

Trong bài viết này, Học Excel Online sẽ hướng dẫn cho các bạn cách dùng nâng cao của hàm SUM để nhanh chóng tính ra 1 luỹ kế (cumulative sum/running total) trong bảng tính của bạn.

Luỹ kế là tổng các số được cập nhật liên tục, luôn có số mới được thêm vào dãy.

Luỹ kế được áp dụng phổ biến trong cuộc sống hằng ngày, ví dụ như tính tỉ số hiện tại của trận đấu, biểu thị doanh số bán hàng, tính toán số dư sau mỗi lần rút và gửi tiền. Những ví dụ sau sẽ minh hoạ cho bạn cách nhanh nhất để tìm ra luỹ kế và tạo biểu đồ luỹ kế.

TÍNH LUỸ KẾ TRONG EXCEL:

Để tính luỹ kế trong Excel, bạn dùng hàm SUM kết hợp với việc sử dụng tham chiếu tuyệt đối và tham chiếu tương đối.

Ví dụ, để tính luỹ kế cho các số trong cột B bắt đầu từ ô B2, nhập công thức sau vào ô C2 và sao chép xuống các ô dưới.

=SUM($B$2:B2)

Trong công thức luỹ kế, tham chiếu đầu tiên sẽ luôn là tham chiếu tuyệt đối với ký hiệu $ ($B$2). Vì tham chiếu tuyệt đối không bao giờ thay đổi dù công thức di chuyển, nên nó luôn tham chiếu về ô B2. Tham chiếu thứ 2 không có ký hiệu $ là tương đối và nó sẽ điều chỉnh dựa trên vị trí tương đối của ô mà công thức được sao chép.

Vì thế, khi công thức SUM được sao chép tới ô B3, nó trở thành SUM($B$2:B3), và trả tổng giá trị các ô từ B2 đến B3. Trong ô B4, công thức trở thành SUM($B$2:B4), và tổng giá trị từ B2 đến B4, và cứ thế tiếp tục.

Tương tự, bạn có thể dùng hàm SUM để tính luỹ kế cho số dư tài khoản ngân hàng. Để tính toán, bạn nhập số tiền gửi là số dương, số tiền rút ra là số âm trong 1 cột (ví dụ cột C). Sau đó, nhập công thức tính luỹ kế sau vào cột D:

=SUM($B$C:C2)

Thực tế, hình trên không phải minh hoạ chính xác 1 luỹ kế. Tuy nhiên, bạn đã có kết quả như mình mong muốn.

Xem thêm: Các ví dụ về hàm Sum trong Excel – Cách chỉ tính tổng các cột, hàng hoặc ô được hiển thị

Nhìn qua, công thức tính luỹ kế của chúng ta trông hoàn hảo, nhưng lại có 1 điểm hạn chế lớn. Khi bạn sao chép công thức vào cột, bạn sẽ để ý thấy giá trị luỹ kế trong các ô giống nhau từ ô cuối cùng trở xuống.

Để chữa lỗi này, chúng ta lồng công thức tính luỹ kế vào hàm IF:

=IF(C2=””,””,SUM($C$2:C2))

Với công thức trên, Excel sẽ thực hiện như sau: Khi ô B2 trống, sẽ trả kết quả là 1 chuỗi trống, hoặc không sẽ áp dụng công thức tính luỹ kế.

Giờ bạn có thể sao chép công thức cho các ô mà bạn muốn, và ô công thức sẽ trống đến khi bạn nhập số vào hàng tương ứng ở cột C. Ngay khi bạn làm việc này, luỹ kế tính được sẽ xuất hiện.

CÁCH TẠO BIỂU ĐỒ LUỸ KẾ:

Khi bạn tính được luỹ kế bằng công thức SUM, tạo biểu đồ luỹ kế chỉ mất vài phút.

1. Chọn dữ liệu của bạn, bao gồm cả cột luỹ kế, và tạo biểu đồ cột dạng 2D bằng cách nhấn nút tương ứng trong tab Insert, nhóm Charts:

1. Trong biểu đồ mới tạo, chọn dạng dữ liệu Cumulative Sum, nhấn chuột phải để chọn Change Series Chart Type…trong menu.

1. Nếu bạn sử dụng Excel 2013 hoặc 2016, chọn dạng biểu đồ Combo, và nhấn vào biểu tượng đầu tiên trong hộp thoại Change Chart Type.

Hoặc bạn có thể vào Custom Combination để chọn kiểu biểu đồ đường cho dữ liệu Cumulative Sum (trong ví dụ là Line with Markers).

Trong Excel 2010 trở về trước, chỉ cần chọn dạng biểu đồ đường cho dữ liệu Cumulative Sum.

1. Nhấn OK. Và bạn đã có biểu đồ luỹ kế
2. Bạn có thể nhấn chuột phải vào đường luỹ kế trong biểu đồ, và chọn Add Data Labels từ menu.

Kết quả, biểu đồ luỹ kế của bạn sẽ trông như thế này:

Để tô điểm thêm cho biểu đồ, bạn có thể thay đổi tên biểu đồ, chọn mẫu biểu đồ khác, màu khác…

Bài viết đã hướng dẫn bạn tính luỹ kế trong Excel. Cảm ơn các bạn đã đọc. Chúc các bạn thực hiện thành công!

Xem ngay: Khóa học Excel cơ bản