Hướng dẫn một số cách tính tổng lũy kế trong Excel

09/02/2021

4640

Mục lục Hiển thị

Việc tính tổng lũy kế trong Excel thường được sử dụng khi muốn xem xét biến động mang tính tích luỹ qua từng thời điểm, qua mỗi một đối tượng thì con số tổng hay số dư tại thời điểm đó là bao nhiêu. Trong bài viết dưới đây, Ketoan.vn sẽ giới thiệu với các bạn những cách tính tổng lũy kế trong Excel nhé.

1. Tính tổng lũy kế chỉ có phát sinh tăng

Ví dụ ta có bảng doanh thu như hình dưới. Để tính được số tổng doanh thu lũy kế qua từng nghiệp vụ ta có công thức tại ô E4 ở cột Số dư của nghiệp vụ đầu tiên như sau:

=SUM($D$4:D4)

Lưu ý là đối số $D$4 cần được cố định để khi sao chép hàm xuống các ô bên dưới kết quả mới trả về tổng tính từ nghiệp vụ đầu tiên.

Sao chép công thức cho các ô còn lại ta sẽ thu được kết quả là đã tính được tổng số doanh thu lũy kế từ nghiệp vụ đầu tiên.

Tuy nhiên thì với các nghiệp vụ trống chưa nhập thì cột số dư vẫn sẽ được tính tổng. Để giải quyết vấn đề này thì chúng ta sẽ lồng thêm hàm IF vào công thức tính như sau:

=IF(D4="";"";SUM($D$4:D4))

Sao chép công thức cho các ô còn lại trong cột ta sẽ thu được kết quả là các nghiệp vụ có phát sinh thì sẽ được tính tổng lũy kế. Còn các nghiệp vụ chưa phát sinh thì sẽ không được tính tổng lũy kế.

2. Tính tổng lũy kế có cả phát sinh tăng và giảm

Ví dụ ta có bảng chi tiết tồn kho của một mặt hàng như hình dưới. Số tồn kho sẽ bằng số tồn ban đầu cộng số nhập kho trừ số xuất kho. Lúc này ta sẽ có công thức tính số tồn kho sau từng nghiệp vụ như sau:

=$F$7+SUM($D$8:D8)-SUM($E$8:E8)

Lưu ý là đối số $F$7 phải được cố định.

Sao chép công thức cho các ô còn lại ta sẽ thu được kết quả tính được số tồn kho sau từng nghiệp vụ phát sinh.

3. Tính tổng lũy kế ở cả 2 bên Nợ và Có của tài khoản kế toán

Ví dụ ta có bảng chi tiết công nợ 131 của một đối tượng như trong ảnh dưới. Yêu cầu cần tính số dư công nợ của đối tượng này. Do đây là đối tượng công nợ trên tài khoản 131 nên nó sẽ có thể có số dư ở cả bên nợ hoặc bên có.

Đầu tiên, để tính số dư bên Nợ, các bạn nhập công thức sau vào ô F8.

=MAX($F$7+SUM($D$8:D8)-$G$7-SUM($E$8:E8);0)

Công thức trên có nghĩa lấy số lớn hơn giữa (Số dư nợ đầu kỳ cộng tổng số phát sinh nợ trong kỳ trừ đi số dư có đầu kỳ và trừ đi tổng số phát sinh có trong kỳ) và số 0.

Sao chép công thức cho tất cả ô ở cột Nợ ta sẽ thu được kết quả tính được tất cả số dư nợ của các nghiệp vụ phát sinh trong kỳ.

Tiếp theo, chúng ta cần đặt công thức để tính số dư bên có của các nghiệp vụ. Ta có công thức tính số dư bên có tại ô G8 như sau:

=-MIN($F$7+SUM($D$8:D8)-$G$7-SUM($E$8:E8);0)

Công thức trên có nghĩa lấy giá trị âm của số nhỏ hơn giữa (Số dư nợ đầu kỳ cộng tổng số phát sinh nợ trong kỳ trừ đi số dư có đầu kỳ và trừ đi tổng số phát sinh có trong kỳ) và số 0.

Sao chép công thức cho tất cả ô ở cột Có ta sẽ thu được kết quả tính được tất cả số dư có của các nghiệp vụ phát sinh trong kỳ.