tổng số đang chạy (còn được gọi làTổng tích lũy) được sử dụng rất phổ biến trong nhiều trường hợp.Đây là số liệu cho bạn biết tổng các giá trị cho đến nay là bao nhiêu.
Ví dụ: nếu bạn có dữ liệu bán hàng hàng tháng, tổng số đang chạy sẽ cho bạn biết có bao nhiêu lần bán hàng đã được thực hiện từ ngày đầu tiên của tháng đến một ngày cụ thể.
Có những tình huống khác mà tổng thường được sử dụng, chẳng hạn như tính toán số dư tiền mặt trong bảng sao kê / sổ cái ngân hàng, tính toán lượng calo trong kế hoạch bữa ăn, v.v.
Trong Microsoft Excel, có một số cách khác nhau để tính tổng số đang chạy.
Phương pháp bạn chọn cũng phụ thuộc vào cấu trúc của dữ liệu.
Ví dụ: nếu bạn có dữ liệu dạng bảng đơn giản, bạn có thể sử dụng công thức SUM đơn giản, nhưng nếu bạn có bảng Excel, tốt hơn nên sử dụng tham chiếu có cấu trúc.Bạn cũng có thể sử dụng Power Query để thực hiện việc này.
Trong hướng dẫn này, tôi sẽ trình bày tất cả chúngTính tổng đang chạy trong Excelcác phương pháp khác nhau.
Vậy hãy bắt đầu!
Nội dung
Tính toán tổng số đang chạy bằng cách sử dụng dữ liệu dạng bảng
Nếu bạn có dữ liệu dạng bảng (nghĩa là các bảng trong Excel không được chuyển đổi thành bảng Excel), bạn có thể sử dụng một số công thức đơn giản để tính tổng đang chạy.
Sử dụng toán tử bổ sung
Giả sử bạn có dữ liệu bán hàng theo ngày và bạn muốn在 trong cột CTính toánTổng số đang chạy.
Dưới đây là các bước để làm điều này.
Bước 1 - Trong ô C2, ô đầu tiên mà bạn muốn chạy tổng, hãy nhập
= B2
Điều này chỉ nhận được cùng một giá trị bán hàng trong ô B2.
Bước 2- Trong ô C3, nhập công thức sau:
= C2 + B3
Bước 3- Áp dụng công thức cho toàn bộ cột.Bạn có thể sử dụng tay cầm điền để chọn và kéo nó hoặc chỉ cần sao chép-dán ô C3 vào tất cả các ô còn lại (thao tác này sẽ tự động điều chỉnh tham chiếu và đưa ra kết quả chính xác).
Điều này sẽ cung cấp cho bạn kết quả hiển thị bên dưới.
Đây là một phương pháp rất đơn giản nhưng hoạt động tốt trong hầu hết các trường hợp.
Logic rất đơn giản - mỗi ô lấy giá trị trên đó (là tổng tích lũy của ngày trước đó) và thêm giá trị vào ô liền kề (là giá trị bán hàng của ngày hiện tại).
Chỉ có một nhược điểm - nếu bạn xóa bất kỳ hàng hiện có nào trong tập dữ liệu này, tất cả các ô bên dưới sẽ trả về lỗi tham chiếu (#REF!)
Nếu tập dữ liệu của bạn có khả năng này, hãy sử dụng phương pháp tiếp theo bằng công thức SUM
Sử dụng SUM với các tham chiếu ô bị khóa một phần
Giả sử bạn có dữ liệu bán hàng theo ngày và bạn muốn tính tổng số đang chạy trong cột C.
Dưới đây là công thức SUM sẽ cung cấp cho bạn tổng số đang chạy.
= SUM ($ B $ 2: B2)
Hãy để tôi giải thích cách hoạt động của công thức này.
Trong công thức SUM ở trên, tôi đã sử dụng tham chiếu để thêm dưới dạng $ B $ 2: B2
- $ B $ 2 - Đây là tham chiếu tuyệt đối, có nghĩa là tham chiếu này không thay đổi khi tôi sao chép cùng một công thức vào ô bên dưới.Vì vậy, khi bạn sao chép công thức trong ô bên dưới, công thức sẽ thay đổi thành SUM ($ B $ 2: B3)
- B2 - Đây là phần thứ hai của tham chiếu, đó là tham chiếu tương đối, có nghĩa là khi tôi sao chép công thức xuống hoặc sang phải, phần này sẽ điều chỉnh.Vì vậy, khi sao chép công thức trong ô bên dưới, giá trị sẽ trở thành B3
Điều thú vị về cách tiếp cận này là nếu bạn xóa bất kỳ hàng nào trong tập dữ liệu, công thức này sẽ điều chỉnh và vẫn cung cấp cho bạn tổng số đang chạy chính xác.
Tính tổng số đang chạy trong trang tính Excel
Khi làm việc với dữ liệu dạng bảng trong Excel, cách tốt nhất là chuyển nó sang bảng Excel.Nó giúp quản lý dữ liệu dễ dàng hơn và cũng giúp bạn dễ dàng sử dụng các công cụ như Power Query và Power Pivot.
Sử dụng bảng Excel mang lại một số lợi ích, chẳng hạn như tham chiếu có cấu trúc (giúp bạn dễ dàng tham chiếu dữ liệu trong bảng và sử dụng nó trong công thức) và tự động điều chỉnh tham chiếu khi bạn thêm hoặc xóa dữ liệu khỏi bảng.
Mặc dù bạn vẫn có thể sử dụng công thức trên mà tôi đã chỉ cho bạn trong một trang tính Excel, nhưng hãy để tôi chỉ cho bạn một số cách tốt hơn để thực hiện.
Giả sử bạn có một bảng Excel như bảng dưới đây và bạn muốn tính tổng đang chạy trong cột C.
Đây là công thức để làm điều này:
= SUM (Dữ liệu bán hàng [[# Headers], [Giảm giá]]: [@ Giảm giá])
Công thức trên có vẻ hơi dài nhưng bạn không nhất thiết phải tự viết.Những gì bạn thấy trong công thức tính tổng được gọi là tham chiếu có cấu trúc và đó là một cách hiệu quả để Excel tham chiếu đến các điểm dữ liệu cụ thể trong bảng Excel.
Ví dụ: SalesData [[# Headers], [Sale]] đề cập đến tiêu đề Sales trong bảng SalesData (SalesData là tên của bảng Excel mà tôi đã cung cấp khi tạo bảng)
Trong khi [@Sale] đề cập đến giá trị trong một ô trong cùng một hàng của cột Giảm giá.
Tôi chỉ giải thích điều này ở đây để bạn hiểu, nhưng ngay cả khi bạn không biết gì về tham chiếu có cấu trúc, bạn vẫn có thể dễ dàng tạo công thức này.
Dưới đây là các bước để thực hiện việc này:
- Trong ô C2, nhập = SUM (
- Chọn ô B1, là tiêu đề của cột có giá trị bán hàng.Bạn có thể sử dụng chuột hoặc sử dụng các phím mũi tên.Bạn sẽ nhận thấy rằng Excel tự động nhập một tham chiếu có cấu trúc vào ô
- Thêm: (dấu hai chấm)
- Chọn ô B2.Excel sẽ tự động chèn lại các tham chiếu có cấu trúc vào các ô
- Đóng dấu ngoặc và nhấn Enter
Bạn cũng sẽ nhận thấy rằng bạn không phải sao chép công thức trong toàn bộ cột, trang tính Excel sẽ tự động thực hiện điều đó cho bạn.
Một lợi ích khác của phương pháp này là nếu bạn thêm một bản ghi mới vào tập dữ liệu này, trang tính Excel sẽ tự động tính toán tổng số đang chạy cho tất cả các bản ghi mới.
Mặc dù chúng tôi đã bao gồm tiêu đề cột trong công thức, hãy nhớ rằng công thức bỏ qua văn bản tiêu đề và chỉ xem xét dữ liệu trong cột
Tính tổng số đang chạy với Power Query
Power Query là một công cụ tuyệt vời khi kết nối với cơ sở dữ liệu, trích xuất dữ liệu từ nhiều nguồn và chuyển đổi nó trước khi đưa vào Excel.
Nếu bạn đã sử dụng Power Query, sẽ hiệu quả hơn khi thêm các tổng đang chạy khi chuyển đổi dữ liệu trong chính Power Query Editor (thay vì trước tiên lấy dữ liệu trong Excel và sau đó thêm các tổng đang chạy bằng bất kỳ phương pháp nào ở trên).
Mặc dù không có chức năng tích hợp nào trong Power Query để thêm tổng số đang chạy (tôi ước là có), bạn vẫn có thể làm điều đó với một công thức đơn giản.
Giả sử bạn có một bảng Excel trông giống như thế này và bạn muốn thêm các tổng đang chạy vào dữ liệu này:
Dưới đây là các bước để thực hiện việc này:
- Chọn bất kỳ ô nào trong bảng Excel
- dữ liệu nhấp chuột
- Trong tab Get & Transform, nhấp vào biểu tượng from Table / Range.Thao tác này sẽ mở bảng trong Power Query Editor
- [Tùy chọn] Nếu cột ngày của bạn chưa được sắp xếp, hãy nhấp vào biểu tượng bộ lọc trong cột ngày, sau đó nhấp vào Sắp xếp Tăng dần
- Bấm vào tab Thêm cột trong Power Query Editor
- Trong nhóm Chung, hãy nhấp vào menu thả xuống Cột Chỉ mục (không nhấp vào biểu tượng Cột Chỉ mục, nhưng nhấp vào mũi tên nghiêng nhỏ màu đen bên cạnh để hiển thị thêm tùy chọn)
- Nhấp vào tùy chọn "Từ 1".Làm điều này sẽ thêm một cột chỉ mục mới sẽ bắt đầu từ 1 và nhập các số tăng dần 1 trong suốt cột
- Nhấp vào biểu tượng Cột tùy chỉnh (cũng trong tab Thêm cột)
- Trong hộp thoại Cột tùy chỉnh mở ra, hãy nhập tên cho cột mới.Trong ví dụ này, tôi sẽ sử dụng tên "Running Total"
- Trong trường công thức cột tùy chỉnh, hãy nhập công thức sau:List.Sum (List.Range (# "Đã thêm chỉ mục" [Giảm giá], 0, [Chỉ mục]))
- Đảm bảo có hộp kiểm ở cuối hộp thoại cho biết "Không phát hiện thấy lỗi cú pháp"
- Bấm OK.Điều này sẽ thêm một cột tổng số đang chạy mới
- thả cột chỉ mục
- Bấm vào tab Tệp, sau đó bấm Đóng và Tải
Các bước trên sẽ chèn một trang tính mới vào sổ làm việc của bạn với một bảng có các tổng đang chạy.
Bây giờ, nếu bạn nghĩ rằng đây là quá nhiều bước so với cách tiếp cận trước đây với các công thức đơn giản, bạn đã đúng.
Nếu bạn đã có một tập dữ liệu và tất cả những gì bạn cần làm là thêm các tổng đang chạy, thì tốt nhất bạn không nên sử dụng Power Query.
Sử dụng Power Query có ý nghĩa trong các tình huống mà bạn phải trích xuất dữ liệu từ cơ sở dữ liệu hoặc kết hợp dữ liệu từ một số sổ làm việc khác nhau và cũng có thể thêm tổng số đang chạy vào chúng trong quá trình này.
Ngoài ra, khi bạn tự động hóa việc này với Power Query, lần tiếp theo khi tập dữ liệu của bạn thay đổi, bạn không phải thực hiện lại, bạn chỉ có thể làm mới truy vấn và nó sẽ cung cấp cho bạn kết quả dựa trên tập dữ liệu mới.
Cái này hoạt động ra sao?
Bây giờ hãy để tôi giải thích nhanh những gì xảy ra với cách tiếp cận này.
Điều đầu tiên chúng tôi làm trong Power Query Editor là chèn một cột chỉ mục bắt đầu từ 1 và tăng dần khi nó đi xuống ô.
Chúng tôi làm điều này bởi vì chúng tôi cần sử dụng cột này khi tính toán tổng số đang chạy trong một cột khác được chèn trong bước tiếp theo.
Sau đó, chúng tôi chèn một cột tùy chỉnh và sử dụng công thức sau
List.Sum (List.Range (# "Đã thêm chỉ mục" [Giảm giá], 0, [Chỉ mục]))
Đây là công thức List.Sum sẽ cung cấp cho bạn tổng của phạm vi được chỉ định trong đó.
Phạm vi được chỉ định bằng cách sử dụng hàm List.Range.
Hàm List.Range nhận xuất phạm vi được chỉ định trong cột bán hàng và phạm vi thay đổi theo giá trị Chỉ mục.Ví dụ: đối với bản ghi đầu tiên, phạm vi là giá trị bán hàng đầu tiên.Phạm vi này mở rộng khi bạn đi xuống ô.
Vì vậy, đối với ô đầu tiên.List.Sum sẽ chỉ cung cấp cho bạn tổng giá trị ưu đãi đầu tiên, đối với ô thứ hai, ô thứ hai sẽ cung cấp cho bạn tổng giá trị bán hàng đầu tiên, v.v.
Mặc dù cách tiếp cận này hoạt động tốt, nhưng nó có thể trở nên rất chậm đối với các tập dữ liệu lớn (hàng nghìn hàng).
Tính toán tổng số đang chạy dựa trên tiêu chí
Cho đến nay, chúng tôi đã thấy các ví dụ về tính toán tổng số đang chạy của tất cả các giá trị trong một cột.
Nhưng trong một số trường hợp, bạn có thể muốn tính toán tổng số đang chạy cho một bản ghi cụ thể.
Ví dụ: bên dưới tôi có một tập dữ liệu mà tôi muốn đếm tổng số máy in và máy quét đang chạy trong hai cột khác nhau.
Điều này có thể được thực hiện bằng cách sử dụng công thức SUMIF, tính toán tổng số đang chạy trong khi đảm bảo rằng các điều kiện cụ thể được đáp ứng.
Đây là công thức để thực hiện việc này cho cột máy in:
=SUMIF($C$2:C2,$D$1,$B$2:B2)
Tương tự như vậy, để tính toán tổng số hoạt động của máy quét, hãy sử dụng công thức sau:
=SUMIF($C$2:C2,$E$1,$B$2:B2)
Trong công thức trên, tôi đã sử dụng SUMIF sẽ cho tôi một tổng trong một phạm vi khi các điều kiện cụ thể được đáp ứng.
Công thức có ba tham số:
- phạm vi: Đây là phạm vi điều kiện sẽ được kiểm tra so với điều kiện đã chỉ định
- tiêu chuẩn: đây là tiêu chí sẽ chỉ được kiểm tra nếu tiêu chí này được đáp ứng, sau đó giá trị trong tham số thứ ba, phạm vi tổng sẽ được thêm vào
- [tổng_phạm vi]: đây là phạm vi của tổng các giá trị cần thêm vào khi điều kiện được đáp ứng
Ngoài ra, trongphạm viVàtổng_dảitham số, tôi đã khóa phần thứ hai của tham chiếu để khi chúng tôi di chuyển xuống ô, phạm vi tiếp tục mở rộng.Điều này cho phép chúng tôi chỉ xem xét và thêm các giá trị lên đến phạm vi đó (do đó là tổng số đang chạy).
Trong công thức này, tôi sử dụng các cột tiêu đề (Máy in và Máy quét) làm tiêu chí.Bạn cũng có thể mã hóa tiêu chuẩn cố định nếu tiêu đề cột của bạn không hoàn toàn giống với văn bản tiêu chuẩn.
Nếu bạn cần kiểm tra nhiều điều kiện, bạn có thể sử dụng công thức SUMIFS.
Chạy tổng trong bảng tổng hợp
Nếu bạn muốn thêm tổng số đang chạy vào kết quả PivotTable của mình, bạn có thể dễ dàng thực hiện việc này bằng cách sử dụng chức năng tích hợp sẵn trong PivotTable.
Giả sử bạn có một bảng tổng hợp như bảng dưới đây với ngày tháng trong một cột và giá trị bán hàng trong một cột khác.
Dưới đây là các bước để thêm một cột bổ sung sẽ hiển thị doanh số bán hàng tích lũy theo ngày:
- Kéo trường Bán hàng và thả nó vào vùng Giá trị.
- Thao tác này sẽ thêm một cột khác có giá trị bán hàng
- Nhấp vào tùy chọn Tổng doanh số 2 trong vùng Giá trị
- Nhấp vào tùy chọn Cài đặt Trường Giá trị
- Trong hộp thoại Cài đặt trường giá trị, thay đổi Tên tùy chỉnh thành Tổng số đang chạy
- Nhấp vào tab "Giá trị hiển thị dưới dạng"
- Trong menu thả xuống Hiển thị giá trị dưới dạng, hãy chọn tùy chọn "Tổng số đang chạy"
- Trong các tùy chọn Trường Cơ bản, hãy đảm bảo Ngày được chọn
- Bấm OK
Các bước trên sẽ thay đổi cột bán hàng thứ hai thành cột "tổng số đang chạy".
Vì vậy, đây là một số phương pháp bạn có thể sử dụng để tính toán tổng đang chạy trong Excel.Nếu bạn có dữ liệu dạng bảng, bạn có thể sử dụng các công thức đơn giản và nếu bạn có bảng Excel, bạn có thể sử dụng các công thức sử dụng tham chiếu có cấu trúc.
Tôi cũng đã trình bày cách tính tổng số đang chạy bằng cách sử dụng Power Query và PivotTables.
Tôi hy vọng bạn thấy hướng dẫn này hữu ích.
