Trong bài viết này, hãy khám phá cách viết một công thức mảng cho tổng cộng điều kiện (tổng cộng tích lũy có điều kiện) trong Google Sheets. Tôi cũng đã bao gồm hướng dẫn về cách tính tổng cộng chạy cho từng nhóm trong bài hướng dẫn này.
Trước đây, khi tính tổng chạy dùng công thức mảng (còn được gọi là CUSUM), chúng ta bị giới hạn trong việc sử dụng các hàm như SUMIF hoặc MMULT trong Google Sheets, trong khi Excel cho phép sử dụng MMULT.
Tuy nhiên, cho đến gần đây, theo hiểu biết của tôi, công thức phù hợp duy nhất để tính tổng chạy mảng có điều kiện trong Google Sheets là MMULT. Hiện nay, với các giải pháp mới nổi, các hàm Lambda cung cấp thêm khả năng.
Chú trọng chính của bài viết này là Công Thức Mảng Tổng Cộng Điều Kiện (CUSUM điều kiện) trong Google Sheets.
Tôi đã trình bày hai loại ví dụ.
Trong một ví dụ, tôi sẽ minh họa tổng chạy theo nhóm, chỉ ra các tính toán CUSUM riêng biệt cho từng nhóm hoặc giá trị duy nhất.
Ví dụ khác minh họa cách loại bỏ các giá trị cụ thể khỏi tổng chạy (CUSUM).
Hơn nữa, chúng ta có thể giải quyết hai vấn đề này bằng cách sử dụng các hàm MMULT hoặc Lambda trợ giúp. Hãy bắt đầu với MMULT.
Công thức Mảng Tổng Cộng Điều Kiện Theo Nhóm trong Google Sheets – MMULT
Tính năng chính: Hoạt động với cả dữ liệu đã sắp xếp và chưa được sắp xếp.
Chúng ta có dữ liệu mẫu trong các ô A2:B, trong đó A2:B2 chứa tiêu đề. Để truy cập dữ liệu mẫu, vui lòng sao chép bảng mẫu của tôi bằng cách nhấp vào nút bên dưới.
Hãy tạo công thức CUSUM điều kiện trong ô C3, cụ thể là trong cột ‘Tổng chạy riêng biệt cho Mỗi Nhóm’.
Có ba nhóm hoặc giá trị duy nhất trong bộ dữ liệu trên (tham khảo cột A): Jan, Feb và Mar.
Mặc dù cột nhóm A được sắp xếp, đáng lưu ý là ngay cả khi dữ liệu không được sắp xếp theo nhóm như hiển thị ở trên, công thức MMULT của tôi vẫn hoạt động. Nói cách khác, tên tháng có thể ở bất kỳ thứ tự nào.
Công thức:
=ARRAYFORMULA(IF(LEN(A3:A), MMULT( N(ROW(A3:A)>=TRANSPOSE(ROW(A3:A)))*N(A3:A=TRANSPOSE(A3:A)), N(B3:B) ),))
Giải thích Công thức
Lưu ý: Công thức sử dụng các phạm vi A3:A và B3:B (phạm vi mở). Chúng tôi sẽ kiểm tra công thức trong A3:A10 và B3:B10 (phạm vi đóng).
Công thức tổng chạy điều kiện (theo nhóm) mảng trên trong Google Sheets bao gồm ba phần.
Hai phần đầu tiên tạo thành ma trận 1 và phần thứ ba tạo thành ma trận 2.
Cú pháp MMULT: MMULT(matrix1, matrix2)
Các điều kiện phải được áp dụng trong ma trận 1. Đây là điều mà tôi đã làm và tôi đã giải thích quy trình bên dưới.
PHẦN 1 (điều kiện đầu tiên trong công thức tổng chạy)
Chúng ta nhằm tính tổng chạy với các điều kiện cụ thể. Đoạn mã sau của công thức tạo ra một ma trận cho mục đích này.
N(ROW(A3:A10)>=TRANSPOSE(ROW(A3:A10)))Phần công thức Phần 1 này, khi sử dụng với ArrayFormula, tạo ra các giá trị ma trận sau đây.
Hãy lý giải công thức này:
ROW(A3:A10) (sử dụng ArrayFormula) cho ra các số trình tự dọc từ 3 đến 10.
Khi chúng ta chuyển vị công thức này bằng TRANSPOSE(ROW(A3:A10)) (với ArrayFormula), các số được trả về theo cấu trúc ngang.
Công thức sau đó kiểm tra xem mỗi giá trị trong trình tự số dọc có bằng giá trị chuyển vị của nó (trình tự số ngang) hay không và trả về TRUE hoặc FALSE.
Hàm N chuyển đổi TRUE thành 1 và FALSE thành 0, như được minh hoạ trong hình ảnh trên.
PHẦN 2 (điều kiện thứ hai trong công thức tổng chạy)
Chúng ta nhằm tính tổng chạy theo nhóm và phần này giúp chúng ta chỉ định nhóm đó.
N(A3:A10=TRANSPOSE(A3:A10))Phần công thức Phần 2 này, khi sử dụng với ArrayFormula, tạo ra các giá trị ma trận sau đây.
Công thức kiểm tra xem giá trị trong A3:A10 có bằng các giá trị chuyển vị trong A3:A10 và trả về TRUE hoặc FALSE. Tôi đã chuyển đổi các giá trị Logic này thành 1 hoặc 0 (số) bằng hàm N.
Vì vậy, Phần 1 * Phần 2 bằng ma trận 1 trong MMULT, điều quan trọng để có được tổng chạy có điều kiện trong Google Sheets.
N(ROW(A3:A10)>=TRANSPOSE(ROW(A3:A10)))*N(A3:A10=TRANSPOSE(A3:A10))
PHẦN 3
Ma trận 2 là dải B3:B10 chính nó.
N(B3:B10)Trong ma trận 2, mục đích của hàm N không phải là chuyển đổi TRUE hoặc FALSE thành 1 hoặc 0, vì không có giá trị TRUE hoặc FALSE trong dải.
Ở đây, nó chuyển đổi các ô trống, nếu có, thành 0 để tránh lỗi trong MMULT (vì các tham số 1 và 2 của MMULT yêu cầu các giá trị số).
Toàn bộ công thức được bao quanh bởi một IF và kết hợp LEN. Nó kiểm tra xem độ dài của ô tương ứng trong cột A có lớn hơn 0 hay không. Nếu đúng, nó tính toán kết quả MMULT; nếu không, nó trả về kết quả trống.
Đó là giải thích về tổng chạy theo nhóm, một loại tổng chạy có điều kiện trong Google Sheets.
Tổng Cộng Chạy Theo Nhóm Thông Qua Công Thức Mảng BYROW trong Google Sheets
Tính năng chính: Hoạt động với cả dữ liệu đã sắp xếp và chưa được sắp xếp.
Công thức MMULT trên có thể gây ra vấn đề về hiệu suất với các tập dữ liệu lớn.
Đây là một giải pháp thay thế cho tổng chạy theo nhóm trong Google Sheets bằng cách sử dụng hàm BYROW.
Chèn công thức sau vào ô C3, sẽ mở rộng xuống:
=BYROW(A3:A, LAMBDA(r, IF(r=””, ,SUM(FILTER(B3:B, A3:A=r, ROW(A3:A) <= ROW(r))))))
Lưu ý: Hãy tự tin thay thế BYROW bằng MAP trong công thức trên.
Phân Tích Công Thức
FILTER(B3:B, A3:A=r, ROW(A3:A) <= ROW(r))Hàm FILTER lọc B3:B trong đó A3:A bằng r, và số hàng của A3:A nhỏ hơn hoặc bằng số hàng của r.
Ở đây, r đại diện cho ô hiện tại trong mỗi hàng trong A3:A. Ở hàng đầu tiên, A3:A=r sẽ là A3:A=A3, hàng thứ hai A3:A=r sẽ là A3:A=A4, và cứ như vậy.
Cùng logic áp dụng cho ROW(A3:A) <= ROW(r). Ở đây, r cũng sẽ là A3, A4, và cứ thế cho mỗi hàng. Hàm BYROW giúp lặp qua mỗi hàng trong A3:A như đã nêu trên.
IF(r="", ,SUM(FILTER(B3:B, A3:A=r, ROW(A3:A) <= ROW(r))))Hàm IF trả về tổng các giá trị đã lọc trong mỗi hàng nếu r không trống.
Tổng Cộng Điều Kiện Bằng Công Thức Mảng MMULT trong Google Sheets
Hãy bắt đầu với dữ liệu mẫu mới trong phạm vi A2:B (bạn có thể lấy nó từ bảng mẫu của tôi đã được chia sẻ ở trên).
Trong dữ liệu mẫu (phân loại chi tiêu hàng tháng), chúng ta muốn áp dụng công thức tổng chạy mảng có điều kiện vào ô C3.
Nếu chúng ta chèn công thức tổng chạy mảng tiêu chuẩn mà không có điều kiện vào ô C3, nó sẽ trả về tổng cộng sai (CUSUM).
=ARRAYFORMULA(IF(LEN(A3:A), MMULT( IF(ROW(B3:B)>=TRANSPOSE(ROW(B3:B))=TRUE, 1, 0), N(B3:B) ),))
Lưu ý: Để hiểu công thức này, bạn có thể tham khảo các giải thích cho PHẦN 1 và PHẦN 3 trong ví dụ công thức tổng chạy riêng biệt từng nhóm của chúng tôi. Phần 1 tương ứng với ma trận 1 và Phần 3 tương ứng với ma trận 2 trong MMULT.
Tôi muốn loại trừ các hàng “Tổng” và hàng tiêu đề tài khoản (2. Chi Phí Quản Trị) khỏi việc xem xét trong tổng cộng (tích lũy cuối kỳ).
Thêm Điều Kiện vào Công Thức Tổng Cộng Mảng
Sửa đổi 1 (để loại trừ hàng “Total” và các hàng phụ):
Trong công thức được cung cấp ở trên, thay thế:
IF(LEN(A3:A)bằng:
IF((A3:A<>"Total")*(NOT(ISNUMBER(LEFT(A3:A,1)*1)))Sửa đổi này kiểm tra xem A3:A không chứa chuỗi “tổng” và ký tự đầu tiên trong bất kỳ chuỗi nào không phải là một số. Nếu ký tự đầu tiên là một số, có nghĩa là hàng chứa một tiêu đề phụ, mà chúng ta muốn loại trừ.
Sửa đổi 2 (để loại trừ các giá trị hàng “Tổng” trong tổng cộng tích lũy):
Thay đổi ma trận 2, tức là:
N(B3:B)bằng:
IF(A3:A<>"Total", N(B3:B), 0)Dưới đây là công thức tổng chạy có điều kiện mà chúng ta đã chờ đợi để sử dụng trong ô C3.
Conditional running total array formula in Google Sheets
=ARRAYFORMULA(IF((A3:A<>”Total”)(NOT(ISNUMBER(LEFT(A3:A,1)1))), MMULT( IF(ROW(B3:B)>=TRANSPOSE(ROW(B3:B))=TRUE, 1, 0), IF(A3:A<>”Total”, N(B3:B), 0) ),))
Tổng Cộng Điều Kiện Bằng Công Thức Mảng SCAN trong Google Sheets
Ở đây cũng vậy, mục đích của sự thay thế MMULT này là cải thiện hiệu suất.
Bạn có thể nhập công thức sau vào ô C3:
=ARRAYFORMULA(IF((A3:A=”Total”)+(B3:B=””), ,SCAN(0, A3:A, LAMBDA(a, v, IF(v=”Total”, a, OFFSET(v, 0,1 )+a)))))
Công thức này sử dụng hàm SCAN trong Google Sheets để tính toán lặp lại. Đây là giải thích:
-
SCAN(0, A3:A, LAMBDA(a, v, …): Hàm SCAN lặp lại các giá trị trong dải A3:A, áp dụng hàm Lambda cho mỗi phần tử. Hàm Lambda có hai tham số: a (bộ nhớ tạm) và v (giá trị hiện tại).
-
IF(v=”Total”, a, OFFSET(v, 0,1)+a): Đây là logic cốt lõi của hàm Lambda. Nó kiểm tra xem giá trị hàng hiện tại (v) có bằng “Total” không. Nếu đúng, nó trả về bộ nhớ tạm (a), hiệu quả là bỏ qua hàng “Total”. Nếu sai, nó thêm giá trị hiện tại (v) vào cột tiếp theo (OFFSET(v, 0, 1)) vào bộ nhớ tạm (a).
Vì vậy, toàn bộ công thức thực sự tính toán tổng cộng, loại trừ các hàng có giá trị “Total”.
Theo cách này, bạn có thể thêm các điều kiện/các tiêu chí khác vào tổng chạy. Cảm ơn bạn đã theo dõi, chúc bạn vui vẻ!
