Trong Google Sheets, bạn có thể sử dụng công thức mảng của tôi để 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 mà bạn muốn. Bạn cũng có thể sử dụng các cột cách xa nhau.
Hãy xem ảnh chụp màn hình đầu tiên bên dưới để biết tôi đang nói gì.
Tôi có hai công thức mảng để tổng một cột khi giá trị trong một cột khác thay đổi. Đó là gì?
Tôi đã có một công thức không phải là mảng. Đó 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ủa Một Cột trong Google Sheets.
Nhưng tôi khuyến nghị các công thức dưới đây vì nó tự mở rộng!
Trong ví dụ dưới đây, công thức của tôi ở ô C2.
Nó trả về tổng các ô khi giá trị thay đổi trong cột A.
Vì nó là một công thức mảng, nó tính 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à nhìn thấy tổng trong ô C9.
Công Thức Mảng Thường Để 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 giá trị thay đổi trong 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 nhau 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 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ổng khi giá trị thay đổi trong A2:A.
Bạn có thể thấy rằng tôi đã không tô sáng hàng công thức row(A2:A). Vui lòng lưu ý rằng nó sẽ không thay đổi bất kể 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 về công thức, tôi chỉ cung cấp cho bạn logic công thức. Bạn không bắt buộc phải theo.
Thực tế, Công thức chính #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 #1.
search_key:
Công thức, tức là row(A2:A), là search_key trả về số hà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 hàm Array Formula.
=arrayformula(row(A2:A))
range:
Các công thức được gạch dưới màu đỏ và màu 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 đã đóng chúng trong các Dấu Ngoặc Nhọn trong Công thức chính #1.
Để bạn hiểu được những gì nó trả về, tôi đã nhập chúng vào ô 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 trong việc 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ác 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 như sau.
index:
Số chỉ mục 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 hình ảnh #5 phía trên) chứa tổng của mỗi thay đổi giá trị.
Logic Công Thức
Hãy xem logic sau đây về cách 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 vào một 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 hình ảnh #4 ở trên và đầu ra trong cột F bên dưới.
Nếu tôi bọc công thức này bằng Iferror, tôi có thể ẩn lỗi #N/A.
Như vậy, chúng ta có thể sử dụng một công thức mảng để tổng cột B khi giá trị trong cột A thay đổi trong Google Sheets.
Giải Pháp Lambda Để Tổng Cột B Khi Giá Trị Thay Đổi Trong Cột A
Công thức chính #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 như một phương án thay thế cho Công thức chính #1.
Nó tương đối dễ hiểu cách công thức lambda này tổng cột B khi giá trị thay đổi trong cột A.
Nó hoạt động như sau.
Bộ lọc lọc cột B nếu giá trị cột A là A2 (tiêu chí). Hàm Tổng lượng của nó. Đó là giá trị thay đổi cột đầu tiên để đặt vào ô C4 theo hình ả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 chí 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 quá trình trên với mỗi tiêu chí trong bộ lọc (A2, A3, A4, …)
Vai trò của LAMBDA ở đây là rút gọn công thức.
Tờ Mẫu
Đọc Thêm:
- Google Sheets: Tô Đậm Các 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.
