Trong phạm vi đã sắp xếp trong Google Doc Sheets, bạn có thể sử dụng công thức mảng của tôi để tính tổng cột B khi giá trị trong cột A thay đổi.
Tất nhiên, bạn có thể thay thế các cột A và B bằng các cột khác theo sự lựa chọn của bạn. Bạn cũng có thể sử dụng các cột khác nhau.
Hãy nhảy đến ảnh chụp màn hình đầu tiên bên dưới để biết ý tôi muốn nói gì.
Tôi có hai công thức mảng tính tổng một cột khi có sự thay đổi giá trị trong một cột khác. Chúng là gì?
Tôi đã có một công thức không phải mảng trước đó. Nó rất đơn giản để hiểu và sử dụng – Thêm Tổng dựa trên Mỗi Thay đổi trong Giá trị Cột trong Google Sheets.
Nhưng tôi khuyến nghị các công thức dưới đây vì nó tự động mở rộng!
Trong ví dụ dưới đây, công thức của tôi nằm trong ô C2.
Nó trả về tổng của các ô khi có thay đổi giá trị trong cột khác.
Nó tính tổng các ô trong cột B khi có sự thay đổi giá trị trong cột A.
Vì nó là một công thức mảng, nó xem xét tất cả các thay đổi giá trị trong cột A. Tôi đang thêm các giá trị mới và xem tổng xuất hiện trong ô C9.
Công thức mảng thông thường để tính tổng cột B khi giá trị trong cột A thay đổi
Công thức sau tính tổng các ô cho đến khi có sự thay đổi giá trị trong một cột khác. Chỉ cần nhập công thức này vào ô C2. Nó bao gồm toàn bộ các cột A và B.
Công thức chính 1:
=iferror(ArrayFormula(vlookup(row(A2:A),{if(len(A2:A),lookup(unique(A2:A),A2:A,row(A2:A))),if(len(A2:A),sumif(A2:A,unique(A2:A),B2:B))},2,0)))
Làm thế nào chúng ta sử dụng hai cột khác ngoài A và B?
Đầu tiên, hãy xem công thức trên ảnh chụp màn hình dưới đây và hai màu sắc nổi bật khác nhau.
Nổi bật màu xanh: Đây là cột đầu tiên trong dữ liệu của chúng ta. Bất kỳ thay đổi nào trong các giá trị trong cột này đều ảnh hưởng đến tổng.
Nổi bật màu cam: Cột để tính tổng khi giá trị thay đổi trong A2: A.
Bạn có thể thấy tôi đã không làm nổi bật hàng công thức row(A2:A). Vui lòng lưu ý rằng nó sẽ giữ nguyên không phụ thuộc vào các cột được sử dụng trong công thức.
Nhưng nếu phạm vi cột A của bạn là A5: A và phạm vi cột B là B5: B, bạn nên thay đổi công thức row(A2:A) thành row(A5:A).
Giải thích công thức
Thay vì giải thích chi tiết công thức, tôi chỉ cung cấp cho bạn logic của công thức. Không bắt buộc phải theo.
Thực tế, Công thức chính số 1 ở trên là một công thức kết hợp hoạt động cùng nhau như một công thức Vlookup.
Cú pháp: VLOOKUP(search_key, range, index, [is_sorted])
Dưới đây là các đối số công thức Vlookup được sử dụng trong Công thức chính số 1.
search_key:
Công thức, tức là hàng(A2: A), là search_key trả về số dòng từ 2, 3, 4, 5 … theo thứ tự.
Khi kiểm tra công thức ROW này, bạn phải bọc nó trong Array Formula.
=arrayformula(row(A2:A))
range:
Các công thức được gạch chân màu đỏ và xanh lá cây là phạm vi trong Vlookup.
Nó tạo thành một phạm vi hai cột vì tôi đã đặt chúng trong dấu ngoặc nhọn trong Công thức chính số 1.
Để bạn hiểu nó trả về gì, tôi đã nhập chúng vào các ô F2 và H2, tương ứng.
Lưu ý: Ở đây cũng, chúng ta phải sử dụng chức năng Array Formula để làm cho chúng hoạt động khi sử dụng độc lập.
F2:
=ArrayFormula(if(len(A2:A),lookup(unique(A2:A),A2:A,row(A2:A))))
Công thức này trả về số hàng của các giá trị thay đổi trong cột A.
H2:
=ArrayFormula(if(len(A2:A),sumif(A2:A,unique(A2:A),B2:B)))
Đây là một công thức Sumif nhóm cột A và trả về tổng.
Cả hai công thức trên cùng nhau tạo thành phạm vi cho Vlookup thực sự như sau.
index:
Chỉ số là số cột trong phạm vi.
Tôi đã sử dụng #2 làm số chỉ mục vì cột 2 trong phạm vi (vui lòng xem ảnh # 5 ở trên) chứa tổng của mỗi thay đổi trong giá trị.
Logic công thức
Hãy xem logic đằng sau cách tính tổng cột B khi giá trị trong cột A thay đổi.
Để giải thích, tôi đang nhập khóa tìm kiếm Vlookup và phạm vi trong tab khác (phạm vi trong A: B và khóa tìm kiếm trong D: D). Sau đó là một Vlookup trong F2.
Vui lòng kiểm tra cột C trong ảnh # 4 ở trên và kết quả trong cột F bên dưới.
Nếu tôi bọc công thức này với Iferror, tôi có thể ẩn lỗi #N/A.
Như vậy, chúng ta có thể sử dụng công thức mảng để tính tổng cột B khi giá trị trong cột A thay đổi trong Google Sheets.
Giải pháp Lambda để tính tổng cột B khi giá trị trong cột A thay đổi
Công thức chính số 2:
=ArrayFormula(ifna(lambda(x,y,byrow(x,lambda(r,if(row(r)=xmatch(r,x,0,-1)+1, sum(filter(y,x=r)),))))(A2:A,B2:B)))
Chúng ta có thể sử dụng công thức trên là một phương án thay thế cho Công thức chính số 1 của chúng tôi.
Nó khá dễ hiểu cách công thức lambda này tính tổng cột B khi giá trị thay đổi trong cột A.
Nó hoạt động như sau.
Công thức FILTER lọc cột B nếu giá trị cột A là A2 (tiêu chuẩn). Hàm SUM tính tổng. Đó là giá trị thay đổi cột đầu tiên để đặt vào ô C4 theo ảnh # 4.
Để tìm hàng để đặt, chúng ta đã sử dụng hàm XMATCH, trả về hàng cuối cùng của khóa tìm kiếm (tiêu chuẩn lọc) trong A2.
Sử dụng IF, chúng ta đặt tổng đó vào hàng đó.
Chúng ta đã sử dụng BYROW để lặp lại quy trình trên với mỗi tiêu chuẩn trong FILTER (A2, A3, A4, …)
Vai trò của LAMBDA ở đây là rút gọn công thức.
Sheet Mẫu
Đọc thêm:
- Google Sheets: Đánh dấu hàng khi giá trị thay đổi trong bất kỳ cột nào.
- Làm thế nào để tìm hàng cuối cùng trong mỗi nhóm trong Google Sheets.
