HƯỚNG DẪN 5 CÁCH TÍNH LŨY KẾ NHANH CHÓNG VÀ ĐƠN ...

Tổng lũy kế là một số đo được sử dụng phổ biến trong nhiều tình huống kinh doanh. Vậy làm thế nào để tính đại lượng này trên phần mềm Excel? Trong bài viết ngày hôm nay, các bạn hãy cùng Gitiho tìm hiểu 5 cách tính lũy kế cực nhanh cực đơn giản trên Excel nhé.

Thành thạo kỹ năng tính lũy kế nhanh chóng nhờ khóa học kế toán tổng hợp từ A - Z tại Gitiho:

KTG01 - Kế toán tổng hợp từ A - Z - Ai cũng có thể trở thành kế toán tổng hợp

Nông Thị Huệ 499,000đ 999,000đ Đăng ký Học thử

XEM NHANH BÀI VIẾT

• 1 Tìm hiểu về lũy kế và cách tính lũy kế
• 2 Cách tính lũy kế bằng công thức tính tổng trong Excel
• 3 Cách tính lũy kế với bảng Excel
• 4 Cách tính lũy kế trên Power Query và Pivot Table
• 5 Tổng kết

Tìm hiểu về lũy kế và cách tính lũy kế

Trước khi tìm hiểu các cách tính lũy kế, chúng ta hãy nói về thuật ngữ lũy kế. Lũy kế là gì? Công thức tính lũy kế như thế nào? Khi nào thì cần tính tổng lũy kế? Nếu bạn còn chưa trả lời được hết các câu hỏi này thì cùng mình ôn lại kiến thức nhé.

Lũy kế là gì?

Lũy kế được định nghĩa là lũy tiến cộng dồn liên tiếp của một dải số liệu. Với mỗi lần dải xuất hiện một giá trị mới, kết quả phép cộng trước đó sẽ được giữ nguyên, đồng thời cộng trực tiếp giá trị mới vào công thức tính tổng để cập nhật kết quả.

Trong tất cả các ngành nghề, lũy kế là một thông số đo đạc phục vụ nhiều mục đích công việc khác nhau. Một số ứng dụng điển hình có thể kể đến như doanh nghiệp tính tổng doanh thu ghi nhận trong năm để đưa vào thuyết minh Báo cáo tài chính, nhân viên kinh doanh kiểm soát tổng sản phẩm thực tế mình đã bán ra so với KPI, kế toán viên cập nhật bảng cân đối kế toán,...

Ngay cả trong cuộc sống thường ngày, chúng ta cũng có thể chứng kiến vô vàn ứng dụng của lũy kế, ví dụ như thanh toán khi mua hàng trong siêu thị, tính tổng lượng calo nạp vào trong một ngày khi ăn kiêng, tính điểm GPA của sinh viên đại học,...

Như vậy, chúng ta đã trả lời được câu hỏi "Lũy kế là gì?" rồi.

Cách tính lũy kế như thế nào?

Với định nghĩa như trên, công thức cách tính lũy kế hoàn toàn không có gì khác một phép cộng bình thường. Tất cả những gì chúng ta cần làm là cộng tất cả các giá trị trong dải số liệu.

Để giúp bạn có một cái nhìn rõ ràng hơn về lũy kế, mình đã chuẩn bị một bài toán đơn giản dưới đây:

Doanh thu quý 01/2021 của công ty A là 200 triệu VNĐ. Sang quý 2, công ty ghi nhận mức doanh thu 250 triệu VNĐ. Nếu tính tổng doanh thu trong năm 2021 của công ty A thì đến hết 6 tháng đầu năm, công ty đã đạt được con số:

200 + 250 = 450 triệu VNĐ

Sau khi kết thúc tháng 7 vừa qua, công ty A tiếp tục thu được 80 triệu VNĐ từ hoạt động kinh doanh chính. Vậy tính đến hết tháng 7, lũy kế doanh thu năm 2021 của công ty A đã đạt:

450 + 80 = 530 triệu VNĐ

Như vậy, để tính tổng doanh thu trong năm 2021, chúng ta tính tổng doanh thu 6 tháng đầu, sau đó cộng thêm doanh thu vừa được cập nhật của tháng 7 vào tổng đã tính. Với quy tắc này, cứ hết mỗi tháng tiếp theo, chúng ta sẽ cộng số liệu của tháng đó vào tổng doanh thu các tháng trong năm trước đó.

Đến cuối năm, chúng ta sẽ có được lũy kế doanh thu của công ty A trong năm 2021, nói cách khác là tổng doanh thu năm 2021 của công ty A.

Vậy là chúng ta đã ôn lại các kiến thức về lũy kế "Lũy kế là gì?", "Tính lũy kế như thế nào?". Bây giờ, hãy cùng khám phá ngay 5 cách tính lũy kế nhanh nhất khi thao tác trên Excel. Không chỉ sử dụng các hàm, mình cũng sẽ hướng dẫn bạn cách tính lũy kế trong Excel bằng các công cụ bảng và Power Query. Tìm hiểu ngay thôi!

Cách tính lũy kế bằng công thức tính tổng trong Excel

Chi tiết cách tính lũy kế

Cách tính lũy kế trong Excel đầu tiên, cũng là cách tính lũy kế đơn giản nhất mình muốn giới thiệu đến các bạn là sử dụng công thức tính tổng trong Excel. Giả sử chúng ta có một bảng dữ liệu như sau:

Với bảng dữ liệu này, chúng ta sẽ thực hành cách thiết lập công thức cho cột Tổng lũy kế (cột C). Đầu tiên, chúng ta xác định yêu cầu của công thức: Tính tổng trong Excel với các tham số tính từ đầu cột Doanh thu (cột B) đến ô cột B cùng hàng.

Như vậy, chúng ta sẽ xây dựng công thức cách tính lũy kế như sau:

Bước 1: Tại ô đầu tiên của cột Tổng lũy kế (ô C2), nhập =B2

Bước 2: Tại ô thứ hai của cột Tổng lũy kế (ô C3), nhập =C2+B3

Công thức này sẽ cộng giá trị mới tương ứng tại cột B (ô B3) vào tổng lũy kế đã tính ở ô phía trên (ô C2). Như vậy, chúng ta có kết quả mới của phép tính lũy kế.

Bước 3: Sử dụng tính năng AutoFill kéo công thức tại ô C3 xuống toàn bộ dải ô phía dưới của cột C.

Lúc này, tất cả các ô còn lại sẽ tự động cập nhật công thức với nguyên tắc như mình đã giải thích ở bước trên. Tại ô C21 là kết quả cuối cùng của bảng dữ liệu hiện tại, bằng tổng tất cả các giá trị ở cột B.

Xem thêm: Hướng dẫn sử dụng AutoFill trên Excel trong đề thi MOS Excel 2010

Vậy là chúng ta đã hoàn thành thao tác tính tổng trong Excel. Cách tính lũy kế bằng phép cộng như trên thật dễ dàng phải không nào? Mình cá rằng bạn sẽ không mất quá nhiều thời gian để thực hiện các bước xây dựng công thức trên cho trang tính của bạn, ngay cả khi bạn có nhiều hàng dữ liệu.

Lưu ý với cách tính lũy kế

Tuy nhiên, cách tính lũy kế trong Excel bằng công thức trên có một điểm trừ. Khi bạn xóa bất kỳ một hàng nào trong phạm vi đã áp dụng công thức, ngay lập tức dữ liệu ở toàn bộ các hàng phía dưới sẽ bị thay thế bằng lỗi #REF! như bạn có thể thấy trong hình dưới đây. Đây là một trong các lỗi thường xuyên xảy ra khi bạn xóa một ô tính đang xuất hiện trong các phép tham chiếu từ ô tính khác.

Trong ví dụ, mình đã xóa đi hàng 16 trong bảng dữ liệu ban đầu. Điều này đồng nghĩa với việc ô C16 đã biến mất, làm hỏng tham chiếu trong công thức ô C17 =C16+B17. Điều thực sự xảy ra là Excel không thể xác định ô bạn muốn tham chiếu và cho rằng tham chiếu đã không còn chính xác.

Lỗi #REF! trong ô C17 sẽ dẫn đến hàng loạt lỗi #REF! tại dải ô phía dưới vì một lý do tương tự. Vì vậy, hãy chỉ dùng cách tính lũy kế bằng công thức trên khi bạn đã chắc chắn không còn một thay đổi nào trong bảng dữ liệu Excel của mình.

Xem thêm: Hướng dẫn tìm hiểu về lỗi #REF! và cách khắc phục lỗi #REF trong Excel

Chi tiết cách tính lũy kế

Nếu bạn không muốn rơi vào tình trạng lỗi #REF! khi xóa dữ liệu, mình sẽ đề xuất một cách tính lũy kế trong Excel khác bằng công thức hàm SUM. Chúng ta hãy cùng xem công thức này có gì khác với phép tính tổng trong Excel phía trên nhé.

Vẫn tiếp tục với bảng dữ liệu trong ví dụ ở phần trước, chúng ta sẽ xây dựng một công thức hàm SUM tại ô đầu tiên của cột Tổng lũy kế (ô C2), sau đó áp dụng công thức này cho toàn bộ dải ô phía dưới.

Cách tính lũy kế được thực hiện qua các bước như sau:

Bước 1: Xây dựng công thức tính tổng trong Excel với hàm SUM:

=SUM($B$2:B2)

Trong công thức hàm SUM chứa tham chiếu dạng $B$2:B2. Mình sẽ giải thích cho bạn lý do chúng ta cần sử dụng tham chiếu này.

• $B$2 - Là tham chiếu tuyệt đối, được cố định trong công thức. Điều này đồng nghĩa với việc tham chiếu sẽ không thay đổi khi chúng ta copy công thức xuống các ô phía dưới.
• B2 - Là tham chiếu tương đối, không cố định trong công thức. Điều này đồng nghĩa với việc tham chiếu sẽ thay đổi dựa vào vị trí ô chúng ta sử dụng công thức.

Như vậy, tham chiếu $B$2:B2 sẽ bao gồm dải ô tính từ ô B2 đến ô B2, nghĩa là một ô duy nhất. Tuy nhiên, khi kéo công thức xuống các ô còn lại, tham chiếu tương đối B2 sẽ thay đổi và công thức hàm SUM sẽ bao gồm nhiều ô tính hơn.

Bước 2: Sử dụng tính năng AutoFill kéo công thức hàm SUM tại ô C2 xuống toàn bộ dải ô phía dưới của cột C.

Như vậy, bạn sẽ thấy toàn bộ cột C đã cập nhật kết quả lũy kế giống hệt như những gì ta nhận được với cách tính lũy kế đầu tiên đã thực hiện. Hãy thử nhấn vào một ô bất kỳ để kiểm tra công thức nhé. Bạn sẽ thấy tham chiếu $B$2 được giữ nguyên, chỉ có tham chiếu tương đối là được điều chỉnh dựa vào giá trị tương ứng.

Ví dụ ở ô C5, công thức hàm SUM như sau:

=SUM($B$2:B5)

Bước 3: Ẩn lỗi Excel

Như bạn thấy trong hình trên, các ô tính sau khi kéo công thức đều xuất hiện một dấu tam giác nhỏ trên góc trái của ô. Đây là dấu hiệu của lỗi Excel. Lý do là vì Excel đang xác định một tham chiếu dữ liệu khác cho công thức hàm SUM.

Tuy nhiên, hoàn toàn không có lỗi gì xảy ra với công thức chúng ta đã thiết lập. Do đó, bạn hãy ẩn lỗi này đi bằng cách chọn tất cả các ô tính Excel xuất hiện lỗi > nhấn biểu tượng lỗi hình thoi > Ignore Error.

Sau đó, bảng tính Excel của bạn sẽ trở lại bình thường. Vậy là chúng ta đã hoàn thành thêm một cách tính lũy kế trong Excel nữa rồi.

Với cách tính lũy kế bằng hàm SUM như trên, bạn hãy nhớ rằng tham chiếu ô đầu tiên (ô B2) luôn luôn phải đặt dưới dạng tham chiếu tuyệt đối để cố định giá trị đầu tiên trong phạm vi số liệu cần tính tổng. Với tham chiếu tiếp theo, chúng ta sẽ chỉ sử dụng tham chiếu tương đối để Excel tự động điều chỉnh kích thước phạm vi tính tổng dựa trên vị trí tương ứng với ô mà công thức được sao chép.

Điều tuyệt vời là cách tính lũy kế trong Excel này sẽ tự động điều chỉnh tham chiếu khi dữ liệu trong bản có sự thay đổi. Nhờ vậy mà bạn không còn phải bận tâm khi thêm bớt nội dung Excel nữa.

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

Các ví dụ nâng cao

Dựa trên cách tính lũy kế bằng hàm SUM trong Excel, chúng ta có thể xây dựng các công thức khác linh hoạt cho các trường hợp khác nhau. Mình sẽ lấy một số ví dụ để các bạn hiểu rõ hơn về cách xây dựng công thức tính lũy kế bằng các hàm Excel.

Ví dụ 1: Tính số lượng hàng tồn kho

Nếu bạn đã hoặc đang làm công việc kế toán kho, bạn sẽ không còn lạ gì với các thẻ kho. Để tính toán chính xác lượng hàng hóa còn tồn trong kho sau mỗi lần nhập hoặc xuất kho, bạn có thể sử dụng một công thức tính lũy kế bằng hàm SUM.

Giả sử chúng ta có một bảng chi tiết tồn kho như sau:

Yêu cầu đặt ra là xây dựng công thức Excel cho cột Tồn kho (cột F) để tự động cập nhật số liệu hàng tồn kho tương ứng với các lần nhập, xuất kho đã thêm trong bảng.

Trước tiên, chúng ta cần xác định nguyên tắc hoạt động của hàm để tính lượng hàng tồn kho:

Số tồn kho = Tồn đầu kỳ + Số nhập - Số xuất

Dựa vào nguyên tắc này, chúng ta có thể xây dựng công thức tính lũy kế bằng hàm SUM như hướng dẫn ở phần trên. Tại ô F7, chúng ta có công thức như sau:

=$F$6+SUM($D$7:D7)-SUM($E$7:E7)

Trong đó:

• $F$6 - Là ô chứa số liệu Tồn đầu kỳ, được đặt dưới dạng tham chiếu cố định để không bị thay đổi khi kéo công thức xuống các ô phía dưới trong cột.
• SUM($D$7:D7) - Là công thức hàm SUM tính lũy kế Số nhập
• SUM($E$7:E7) - Là công thức hàm SUM tính lũy kế Số xuất

Sau đó, chúng ta kéo công thức hàm SUM tại ô F7 xuống cho tất cả các ô còn lại. Vậy là chúng ta đã hoàn thành nhanh gọn cách tính lũy kế cho bảng chi tiết hàng tồn kho này rồi.

Xem thêm: 4 Cách tính phần trăm trong Excel nhanh và chuẩn

Ví dụ 2: Tính số dư của sổ kế toán

Công việc của một kế toán viên cần đến rất nhiều phép tính lũy kế với các con số. Bên cạnh ví dụ trên, chúng ta sẽ tìm hiểu thêm một trường hợp tính lũy kế khác trong kế toán, đồng thời học thêm một phép kết hợp khác với hàm SUM. Hãy cùng cùng thực hành cách tính lũy kế trong Excel mới bằng một ví dụ tính số dư của sổ kế toàn.

Giả sử chúng ta có bảng chi tiết công nợ phải thu như hình dưới đây. Vì đối tượng đang làm việc là công nợ trên tài khoản 131 nên sổ kế toán hoàn toàn có thể có số dư bên Nợ hoặc bên Có. Chính vì vậy, nhiệm vụ lúc này là điền công thức tính số dư bên Nợ và bên Có trong lần lượt 2 cột F và cột G.

Tương tự như ví dụ trước, chúng ta sẽ xác định nguyên tắc xây dựng công thức cách tính lũy kế trước tiên.

Hãy bắt đầu với số dư bên Nợ. Nguyên tắc là lấy số lớn hơn giữa số dư Nợ cuối kỳ và số 0. Chúng ta có công thức tính số dư Nợ cuối kỳ như sau:

Số dư Nợ cuối kỳ = (Số dư Nợ đầu kỳ + Số phát sinh bên Nợ trong kỳ) – (Số dư Có đầu kỳ + Số phát sinh bên Có trong kỳ)

Tiếp theo, chúng ta xác định nguyên tắc điền số dư bên Có. Nguyên tắc thực hiện là so sánh và lấy số nhở hơn giữa giá trị âm của số dư Nợ cuối kỳ và số 0. Lý do chúng ta sử dụng nguyên tắc này là vì trên sổ không thể có đồng thời số dư ở cả bên Nợ và bên Có. Điều này đồng nghĩa với việc bắt buộc một trong hai giá trị Nợ và Có trong cùng hàng phải bằng 0.

Để thực hiện các phép so sánh dữ liệu trên, chúng ta sẽ kết hợp sử dụng hàm MAX và hàm MIN.

Cách tính lũy kế số dư bên Nợ lấy giá trị lớn hơn của phép so sánh. Do đó, chúng ta sử dụng hàm MAX. Công thức hàm MAX kết hợp hàm SUM tại ô F7 như sau:

=MAX($F$6+SUM($D$7:D7)-$G$6-SUM($E$7:E7),0)

Trong đó:

• $F$6 - Là ô chứa Số dư Nợ đầu kỳ, được đặt dưới dạng tham chiếu cố định để không bị thay đổi khi kéo công thức xuống các ô phía dưới trong cột.
• SUM($D$7:D7) - Là công thức hàm SUM tính lũy kế Số phát sinh bên Nợ.
• SUM($E$7:E7) - Là công thức hàm SUM tính lũy kế Số phát sinh bên Có.

Cách tính lũy kế số dư bên Nợ lấy giá trị âm nhỏ hơn của phép so sánh. Do đó, chúng ta sử dụng hàm MIN và đặt dấu trừ ở đầu phép tính để xác định so sánh các số âm. Công thức hàm MAX kết hợp hàm SUM tại ô F7 như sau:

=-MIN($F$6+SUM($D$7:D7)-$G$6-SUM($E$7:E7),0)

Trong đó:

• $F$6 - Là ô chứa Số dư Nợ đầu kỳ, được đặt dưới dạng tham chiếu cố định để không bị thay đổi khi kéo công thức xuống các ô phía dưới trong cột.
• SUM($D$7:D7) - Là công thức hàm SUM tính lũy kế Số phát sinh bên Nợ.
• SUM($E$7:E7) - Là công thức hàm SUM tính lũy kế Số phát sinh bên Có.

Chúng ta đã hoàn thành xong 2 công thức hàm SUM xác định số dư bên Nợ và bên Có. Giờ thì chỉ cần kéo lần lượt các công thức này cho cột là bạn đã hoàn thành cách tính lũy kế cho bảng công nợ này rồi.

Kết quả cuối cùng của chúng ta là bảng Excel dưới đây:

Bạn có thể thấy, khi kết hợp hàm SUM với các hàm khác, chúng ta có thể tạo ra các cách tính lũy kế trong Excel mới để áp dụng trong các trường hợp cụ thể. Không chỉ với 2 ví dụ trên, tùy vào yêu cầu nhiệm vụ, các bạn hoàn toàn có thể sáng tạo các công thức mới dựa trên cách tính tổng trong Excel.

Xem thêm: Hướng dẫn lập biên bản đối chiếu công nợ trên Excel

Cách tính lũy kế với bảng Excel

Tại sao nên dùng bảng Excel?

Khi làm việc với các danh sách, dữ liệu tổng hợp trên Excel, việc biến các dữ liệu này về dạng bảng là một ý tưởng tuyệt vời để bạn thuận lợi thực hiện các thao tác sau này. Công cụ bảng của Excel cho phép bạn dễ dàng quản lý dữ liệu của mình, đồng thời kết hợp tính năng Power Query và Power Pivot một cách đơn giản.

Khi dữ liệu của bạn được trình bày dưới dạng bảng, Excel sẽ đặt tên cho bảng và tiêu đề các cột trong bảng, tạo nên các tham chiếu có cấu trúc từ bảng và tên cột. Bằng cách này, bạn có thể dễ dàng quản lý các tham chiếu của mình khi sử dụng trong các công thức hàm. Ngoài ra, Excel cũng tự động điều chỉnh các tham chiếu mỗi lần bạn thực hiện các thay đổi trên dữ liệu của mình như thêm bớt hàng/cột.

Xem thêm: Hướng dẫn cách tìm dòng cuối trong VBA cho dữ liệu bảng Excel

Chi tiết cách tính lũy kế

Tất nhiên bạn vẫn có thể sử dụng các công thức tính tổng trong Excel hoặc công thức hàm SUM kết hợp các hàm khác như hướng dẫn phía trên để tính tổng cộng dồn với cho bảng Excel. Tuy nhiên, hãy để mình chỉ cho bạn các cách tính lũy kế tối ưu hơn nhé.

Giả sử chúng ta có một bảng Excel như trong hình dưới đây. Mình đã chuyển đổi dữ liệu từ trang tính về dạng bảng bằng cách chọn tất cả các ô tính chứa dữ liệu và nhấn tổ hợp phím Ctrl+T. Nếu bạn đã có dữ liệu sẵn trên trang tính Excel, hãy sử dụng thao tác này để biến các dữ liệu đó về dạng bảng Excel nhé.

Sau khi đã có bảng Excel trên, bạn có thể đặt tên cho bảng để thuận tiện cho các thao tác sau này trong trường hợp file Excel chứa nhiều hơn 1 bảng. Mặc định Excel sẽ chỉ định bảng của bạn là Table1 (hoặc Table2, Table3,... tùy thuộc vào số bảng đang tồn tại trong file).

Để đổi tên mặc định này, hãy nhấn vào một ô bất kỳ trong bảng Excel, đi đến thẻ Design > nhóm Properties > xóa tên mặc định và nhập tên mới dưới mục Table Name.

Chúng ta vẫn sẽ sử dụng hàm SUM để tính lũy kế cho bảng Excel này. Tại ô C2, chúng ta có công thức như sau:

=SUM(DataDT[[#Headers],[Doanh thu]]:[@[Doanh thu]])

Trong đó:

• DataDT[[#Headers],[Doanh thu]] - Là tiêu đề cột Doanh thu trong bảng DataDT
• [@Doanh thu]] - Là giá trị trong cột Doanh thu nằm cùng hàng với ô chứa công thức đang tạo (ô B2)

Có thể bạn đang tự hỏi tại sao lại phải dùng đến tham chiếu phức tạp kia trong khi hoàn toàn có thể nhập tham chiếu thông thường ($B$2:B2). Những gì bạn nhìn thấy trên thanh công thức lúc này chính là tham chiếu có cấu trúc - một tính năng vô cùng hiệu quả của Excel để xác định dữ liệu trong bảng.

Đúng là công thức cách tính lũy kế trong Excel này có vẻ khá dài dòng, nhưng hãy yên tâm là bạn không phải nhập từng ký tự một để hoàn thành công thức tính tổng trong Excel kia đâu. Để viết công thức này, bạn hãy thực hiện các bước sau:

Bước 1: Tại ô C2, nhập =SUM(

Bước 2: Nhấn chọn ô tiêu đề cột Doanh thu (ô B1). Lúc này, Excel sẽ tự động hiện tham chiếu có cấu trúc cho ô B1.

Bước 3: Nhập ký tự hai chấm ":"

Bước 4: Nhấn chọn ô B2. Một lần nữa Excel sẽ hiển thị tham chiếu có cấu trúc thay vì giá trị B2 như thông thường.

Bước 5: Đóng ngoặc và nhấn Enter để kết thúc công thức.

Sau khi nhấn Enter, bạn sẽ thấy công thức được áp dụng toàn bộ cho cả cột C thay vì chỉ ô C2 như với các cách tính lũy kế đã học phía trên. Đó là một lợi ích khác khi bạn sử dụng bảng Excel. Các thao tác sẽ được tự động hóa dựa trên cấu trúc của bảng, do đó, bạn không cần phải kéo công thức xuống dải ô phía dưới nữa. Excel sẽ thay bạn làm điều đó.

Ngoài ra, khi sử dụng công cụ bảng trong Excel, bạn sẽ không phải áp dụng lại công thức cho các hàng dữ liệu được thêm vào, bởi Excel sẽ tự động tính lũy kế bao gồm tất cả các nội dung mới bạn vừa nhập.

Mặc dù trong công thức hàm SUM, phạm vi tham chiếu bao gồm cả ô tiêu đề cột Doanh thu (ô B1), nhưng Excel sẽ tự động loại trừ các ô tiêu đề và chỉ chấp nhận các số liệu hiển thị trong cột mà thôi. Hãy lưu ý điều này khi làm việc với bảng Excel bạn nhé.

Xem thêm: Hướng dẫn cách tạo tham chiếu trong Excel bằng lệnh VBA

Chi tiết cách tính lũy kế

Giả sử mình có một bảng dữ liệu doanh thu dưới đây bao gồm 2 sản phẩm máy in và máy fax. Thay vì tính lũy kế doanh thu của tất cả các sản phẩm, mình cần tính tổng doanh thu tính riêng của từng sản phẩm và điền vào 2 cột khác nhau.

Như vậy, chúng ta cần xây dựng công thức phân biệt 2 giá trị "Máy in", "Máy fax" và chỉ nhặt ra những con số doanh thu tương ứng với một trong hai giá trị. Nhắc đến đây, có thể bạn đã nhận ra chúng ta cần sử dụng hàm nào.

Hàm Excel chúng ta cần đến chính là hàm SUMIF - hàm tính tổng có điều kiện trong Excel.

Đầu tiên, chúng ta sẽ xây dựng công thức hàm SUMIF tính tổng trong Excel để tính lũy kế cho sản phẩm Máy in. Tại ô D2, bạn hãy điền công thức như sau:

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

Trong đó:

• $C$2:C2 - Là phạm vi áp dụng điều kiện.
• $D$1 - Là điều kiện dành cho phạm vi $C$2:C2, nghĩa là giá trị trong phạm vi phải trùng khớp với nội dung của ô D1.
• $B$2:B2 - Là phạm vi các giá trị được đưa vào tính tổng trong Excel nếu đạt điều kiện.

Tương tự như vậy, chúng ta có thể lập công thức hàm SUMIF để tính lũy kế doanh thu cho sản phẩm Máy fax. Tại ô E2, hãy điền công thức tính tổng trong Excel dưới đây:

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

Trong đó:

• $C$2:C2 - Là phạm vi áp dụng điều kiện.
• $E$1 - Là điều kiện dành cho phạm vi $C$2:C2, nghĩa là giá trị trong phạm vi phải trùng khớp với nội dung của ô E1.
• $B$2:B2 - Là phạm vi các giá trị được đưa vào tính tổng trong Excel nếu đạt điều kiện.

Trong cả 2 công thức hàm tính tổng có điều kiện mà chúng ta đã sử dụng, bạn sẽ nhìn thấy sự xuất hiện của các tham chiếu dạng cố định một nửa như $C$2:C2 và $B$2:B2. Nguyên tắc này đã được giải thích trong các phần trên.

Vậy là ta đã xác định được cách tính lũy kế với dữ liệu có điều kiện. Trong trường hợp bạn có nhiều hơn một điều kiện để phân loại dữ liệu và tính tổng trong Excel, hãy sử dụng hàm SUMIFS - một hàm tính tổng có điều kiện khác nhé. Để tìm hiểu kỹ hơn về hàm SUMIFS, hãy tham khảo bài viết dưới đây.

Xem thêm: Hướng dẫn sử dụng hàm SUMIFS tính tổng nhiều điều kiện

Cách tính lũy kế trên Power Query và Pivot Table

Cuối cùng, chúng ta hãy tìm hiểu các cách tính lũy kế không trực tiếp trên trang tính Excel mà thông qua các trình chỉnh sửa khác như Power Query và Pivot Table nhé.

Cách tính lũy kế trên Power Query

Power Query là gì?

Khi nói về kết nối database hay trích xuất và chuyển đổi dữ liệu từ nhiều nguồn khác nhau trước khi chèn chúng vào Excel, Power Query là một trong những công cụ hàng đầu. Công cụ này có khả năng tạo nên một quy trình tổ chức, xử lý, tái cấu trúc lại bảng dữ liệu một cách tự động.

Trong trường hợp bạn chưa biết đến Power Query và muốn tìm hiểu kỹ hơn về công cụ này, hãy tham khảo bài viết dưới đây nhé. Bật mí nho nhỏ cho người hay phải làm việc với dữ liệu Excel: Mình chắc chắn rằng Power Query chính là công cụ bạn cần để cải thiện hiệu suất công việc đấy.

Xem thêm: Tìm hiểu nhanh những kiến thức xung quanh Power Query

Chi tiết cách tính lũy kế

Nếu bạn đã và đang sử dụng Power Query, thay vì nhập dữ liệu vào trang tính Excel rồi tính lũy kế bằng các phương thức ở các phần trên, cách tính lũy kế ngay khi chuyển đổi dữ liệu trên trình chỉnh sửa truy vấn Power Query sẽ hiệu quả hơn gấp nhiều lần.

Mặc dù không có tính năng nào sẵn có trong Power Query giúp chúng ta tính lũy kế, chúng ta vẫn có thể xây dựng công thức cực kỳ đơn giản.

Giả sử chúng ta có một bảng Excel gồm 2 cột như trong hình dưới đây. Nhiệm vụ đặt ra là tính lũy kế và thêm vào bảng dữ liệu.

Để đưa bảng dữ liệu Excel trên vào Power Query, chúng ta thực hiện các bước sau:

Bước 1: Chọn một ô bất kỳ trong bảng dữ liệu.

Bước 2: Đi đến thẻ Power Query > nhóm Excel Data > From Table/Range.

Lúc này, cửa sổ Excel của bạn sẽ được chuyển sang Power Query với giao diện như sau:

Như bạn thấy trong hình, dữ liệu của mình đã được sắp xếp theo đúng trình tự thời gian. Trong trường hợp dữ liệu của bạn đang lộn xộn và bạn cần sắp xếp theo trình tự tăng dần hoặc từ A đến Z, hãy nhấn nút mũi tên cạnh tiêu đề của cột và nhấn chọn Sort Ascending.

Vậy là bảng dữ liệu của chúng ta trong Power Query đã sẵn sàng để thực hiện các thao tác sau. Hãy cùng theo dõi cách tính lũy kế qua các bước dưới đây bạn nhé.

Bước 1: Thêm cột Index

Bước đầu tiên thay vì thêm cột tính tổng lũy kế, chúng ta sẽ thêm cột Index để xác định thứ tự các dòng dữ liệu.

Bạn hãy đi đến thẻ Add Column > nhóm General > Index Column > From 1.

Lúc này, trên bảng Power Query sẽ xuất hiện cột Index với số thứ tự từ 1-11 bên phải cột Doanh thu. Cột này sẽ giúp ích trong trường hợp chúng ta cần kiểm tra, xác nhận lại kết quả sau khi thực hiện lọc dữ liệu theo dòng. Khi bạn chèn lại bảng vào trang tính Excel, cột Index cũng sẽ được thêm vào bảng.

Xem thêm: Chuẩn hoá dữ liệu đơn giản trên Excel bằng Power Query

Bước 2: Thêm cột Tổng lũy kế

Bạn hãy đi đến thẻ Add Column > nhóm General > Custom Column để thêm một cột mới với công thức trong Power Query.

Bước 3: Thiết lập công thức cách tính lũy kế

Hộp thoại Custom Column hiện ra. Đây là nơi bạn thiết lập các thông tin cho cột mới của mình. Hãy điền các thông tin vào ô tương ứng.

1. New column name (Tên cột): Tổng lũy kế
2. Custom column formula (Công thức): Bạn có thể chọn một trong 2 công thức sau

=List.Sum(List.FirstN(#"Added Index"[Doanh thu],[Index]))

Công thức đã tạo được dựa trên sự kết hợp của 2 hàm List.Sum và hàm List.FirstN. Trong đó:

• Hàm List.FirstN trả về một bộ giá trị trong danh sách bằng cách xác định số giá trị cần trả về hoặc các giá trị thỏa mãn một điều kiện nào đó. Trong trường hợp này, hàm List.FirstN trả về các giá trị tương ứng trong cột Doanh thu.
• Hàm List.Sum tính tổng các giá trị trong danh sách hàm List.FirstN trả về.

Hoặc công thức 2:

=List.Sum(List.Range(#"Added Index"[Doanh thu],0,[Index]))

Cả 2 công thức trên đều sử dụng hàm List.Sum lồng một hàm khác. Tại công thức thứ hai, chúng ta kết hợp hàm List.Range với chức năng xác định phạm vi ô tính trong cột Doanh thu để đưa vào cột mới. Kết quả của hàm List.Range sẽ phụ thuộc vào giá trị trong cột Index.

Ví dụ, giá trị cột Index của dòng đầu tiên là 1, do đó hàm List.Range sẽ chỉ lấy một giá trị đầu tiên ở cột Doanh thu để đưa vào danh sách tính tổng bằng hàm List.Sum sau đó.

Như vậy, chúng ta đã tạo nên công thức cách tính lũy kế trên Power Query. Dựa vào công thức của mình, bạn có thể thay thế các giá trị để phù hợp với dữ liệu của bạn, bao gồm:

• [Doanh thu] - Tên cột chứa dữ liệu cần dùng cho thao tác tính tổng lũy kế
• #"Added Index" - Tên bước tạo cột Index
• [Index] - Tên cột index

Sau khi đã thiết lập công thức cho cột mới mang tên Tổng lũy kế, chúng ta nhấn OK và đóng hộp thoại Custom Column.

Giờ thì mọi thao tác với cách tính lũy kế đã được thực hiện xong. Bạn sẽ thấy bảng dữ liệu mới của mình đã có thêm cột Tổng lũy kế với toàn bộ các giá trị được điền tự động theo công thức như trong hình dưới đây:

Bạn có thể giữ nguyên hoặc xóa cột Index nếu cảm thấy không còn cần thiết. Với bảng dữ liệu cuối cùng, hãy đi đến thẻ Home > nhóm Close > Close & Load hoặc nhấn File trên góc trái màn hình và nhấn Close & Load để nạp lại bảng đã tạo vào trang tính Excel.

Excel sẽ tạo một sheet mới để hiển thị bảng dữ liệu được xuất từ Power Query. Dưới đây là những gì bạn sẽ nhận được.

Xem thêm: Hướng dẫn về một số hàm DAX căn bản trong Power Pivot (Phần 1)

Cách tính lũy kế với Pivot Table

Pivot Table là gì?

Pivot Table là một công cụ thống kê dữ liệu tự động trong Excel dùng để trích lọc dữ liệu, tạo báo cáo, thống kê hay nói ngắn gọn hơn là phân tích dữ liệu theo một yêu cầu nhất định. Với chức năng thống kê và kiểm soát dữ liệu vô cùng chi tiết, Pivot Table được coi là một trong những công cụ hỗ trợ mạnh mẽ nhất của Excel.

Xem thêm: Pivot table là gì? Hướng dẫn sử dụng Pivot table trong Excel

Cách tính lũy kế với Pivot Table

May mắn là Pivot Table đã tích hợp sẵn chức năng tính tổng lũy kế, do đó bạn sẽ không phải mày mò xây dựng công thức tính tổng trong Excel cho dữ liệu của mình nữa. Mình sẽ hướng dẫn bạn cách tính lũy kế cực kỳ đơn giản trên Pivot Table nhé.

Giả sử bạn có một bảng dữ liệu Pivot Table như hình dưới đây, trong đó có 2 cột: 1 cột Ngày và 1 cột Doanh thu.

Hãy nhấn vào ô bất kỳ trong bảng và nhìn sang cửa sổ PivotTable Fields bên phải màn hình. Chúng ta sẽ thực hành cách tính lũy kế theo các bước dưới đây:

Bước 1: Kéo dữ liệu Doanh thu vào phần Values.

Lúc này, bạn sẽ thấy phần Values xuất hiện thêm một dòng Sum of Doanh thu2. Đồng thời, trên bảng dữ liệu chính cũng hiển thị một cột Doanh thu mới.

Bước 2: Nhấn mũi tên bên cạnh dữ liệu Sum of Doanh thu2 và chọn Value Field Settings.

Bước 3: Hộp thoại Value Field Settings hiện ra. Chúng ta điền dữ liệu cho cột mới.

Hãy nhấn vào mục Show Values As và điền đầy đủ các thông tin dưới đây:

1. Custom Name: Tổng lũy kế
2. Show values as: Running Total In
3. Base field: Ngày

Khi thiết lập đầy đủ các thông tin này, Pivot Table sẽ tự động thiết lập công thức tính lũy kế dựa trên dữ liệu trong cột Ngày.

Sau khi nhấn OK và đóng hộp thoại, bạn sẽ thấy cột Doanh thu2 trên bảng dữ liệu được đổi tên thành cột Tổng lũy kế và dữ liệu trong cột cũng được áp dụng cách tính lũy kế cài đặt sẵn trong Pivot Table.

Vậy là chúng ta đã hoàn thành cách tính lũy kế trên Power Pivot và Pivot Table rồi! Hãy tìm hiểu thêm về các công cụ này nhé, bởi nó có thể giúp ích rất nhiều cho công việc phân tích dữ liệu trên Excel của bạn đó.

Xem thêm: Hướng dẫn cách tạo Pivot Table trong Excel cho người mới bắt đầu

Tổng kết

Như vậy, chúng ta đã hoàn thành bài học ngày hôm nay. Bên cạnh 5 cách tính lũy kế trong Excel đơn giản và nhanh chóng nhất, chúng ta cũng đã ôn lại các kiến thức cơ bản trả lời cho các câu hỏi "Lũy kế là gì?", "Tính lũy kế như thế nào?".

Vậy là bạn đã nắm được không chỉ các phương thức tính lũy kế cho bảng dữ liệu thông thường trên trang tính, mà còn cả các công thức tính tổng trong Excel khi sử dụng công cụ bảng, Power Pivot hay Pivot Table. Tất nhiên bạn có thể chọn bất cứ cách tính lũy kế nào phù hợp với dữ liệu của mình.

Hy vọng bài viết đã đem lại kiến thức hữu ích cho bạn trong việc vận hành Excel. Để tìm hiểu thêm các thủ thuật thú vị khác hay các mẹo đẩy nhanh tốc độ thao tác với trang tính, bạn hãy tham khảo ngay các bài viết về chủ đề tin học văn phòng trên blog Gitiho nhé.

Gitiho xin cảm ơn bạn đọc và chúc bạn thành công!