Bạn có thể dùng một công thức mảng để tính tổng nhiều cột trong Google Sheets?
Tôi có dữ liệu trong hàng trăm hàng và nhiều cột trong một trong các tệp Google Sheets của mình.
Tôi muốn tính tổng của các cột trong mỗi hàng (theo hàng).
Tôi chỉ muốn sử dụng một công thức mảng mở rộng duy nhất.
Công thức SUM có thể không cho kết quả mở rộng ngay cả khi bạn sử dụng công thức ArrayFormula với nó.
Bạn có thể sử dụng hàm MMULT để tính tổng mở rộng trong Google Sheets. Với phương pháp này, một công thức duy nhất có thể tính tổng của các cột trong mỗi hàng.
Trước khi trình bày chi tiết hơn về chủ đề này, hãy xem ảnh chụp màn hình dưới đây để hiểu những gì tôi đã nói.
Tôi đang nói về cột K, tổng của các cột B đến J. Đây là tổng theo hàng.
K2 chứa tổng của phạm vi cột B2:J2.
Công thức tổng thông thường hoặc, bạn có thể nói, công thức tổng không mở rộng cho ô K2 sẽ như sau:
=sum(B2:J2)
Ngay cả khi bạn sử dụng Công thức mảng, công thức Sum trong ô K2 không thể mở rộng sang các hàng dưới.
=arrayformula(sum(B2:J2))
Trong ví dụ trên, có một số hàng hạn chế.
Vì vậy, bạn có thể nghĩ đến việc sao chép và dán công thức sum không phải mảng trên vào phạm vi ô K3:K11.
Nhưng khi bảng có hàng trăm hàng, nên sử dụng Công thức mảng trong một ô mà mở rộng tự động là đáng khuyến nghị.
Nếu chúng ta sử dụng một công thức mảng để tính tổng từng hàng như ví dụ trên, nó sẽ nằm trong ô K2.
Bài đăng này tiết lộ cách lấy tổng mở rộng trong Google Sheets bằng cách sử dụng Công thức mảng.
Công thức mảng để tính tổng nhiều cột trong Google Sheets
Hàm MMULT có lẽ là cách tốt nhất để có được tổng mở rộng trong Google Sheets. Nhưng nó không phải là giải pháp duy nhất.
Đây là một công thức thường xuyên có thể được sử dụng như là một công thức mảng để tính tổng nhiều cột trong Google Sheets.
Công thức mảng 1:
=ArrayFormula(B2:B11+C2:C11+D2:D11+E2:E11+F2:F11+G2:G11+H2:H11+I2:I11+J2:J11)
Tôi bỏ qua phần giải thích của công thức này vì nó khá đơn giản để hiểu. Nó tổng hợp từng cột và tạo ra kết quả mở rộng.
Ở đây tôi có thể chỉ ra hai hạn chế chính của công thức này.
Nếu số cột nhiều hơn, việc nhập công thức trở nên khó khăn mà không gây lỗi. Nó giống như một chuỗi chữ cái.
Nếu có nhiều hơn 10-20 cột, bạn sẽ thấy khó khăn khi nhập công thức này mà không gây ra lỗi.
Vấn đề khác là vấn đề liên quan đến việc chèn cột mới.
Nếu bạn chèn một hoặc nhiều cột mới, bạn nên thay đổi công thức để tích hợp chúng. Điều đó có nghĩa là nó không phải là giải pháp linh hoạt.
Hãy viết một công thức mảng linh hoạt để tính tổng nhiều cột trong Google Sheets.
Công thức mảng linh hoạt để tính tổng nhiều cột trong Google Sheets
Như đã đề cập ở trên, tôi sử dụng MMULT thay vì SUM.
Công thức mảng 2:
=ArrayFormula(MMULT(n(B2:J11),(transpose(COLUMN(B1:J1)^0))))
Tôi đã giải thích chi tiết cách sử dụng công thức MMULT trước đây.
Hãy chọn chủ đề này (MMULT) trong Hướng dẫn chức năng Google Sheets của tôi để nắm vững nó.
Đây là cú pháp của hàm MMULT.
MMULT(matrix1, matrix2)
Đây là cách giải thích công thức mảng 2 của tôi.
Mục đích của MMULT là tính tích của hai ma trận. Nhưng điều chúng ta muốn là Tổng.
Trong công thức mảng MMULT Google Sheets của tôi ở trên (Công thức mảng 2), khoảng n(B2:J11) là ma trận1 của tôi.
Hàm N chuyển đổi các ô trống trong dải và trả về 0 (không). Nếu không, MMULT có thể trả về một lỗi.
Tạo một cột ảo cho ma trận2 chỉ chứa # 1. Do đó, không có tác động của phép nhân trong MMULT.
Tôi đã sử dụng hàm Column để tạo ma trận2.
Ma trận1 có chín cột từ B đến J. Vì vậy, ở đây, ma trận2 có chín hàng, điều này xác nhận tiêu chuẩn nhân ma trận cốt lõi như sau;
“Số cột ma trận1 phải bằng số hàng của ma trận2”
Đó là nó! Bây giờ chúng ta sẽ tiến đến phần thứ hai của bài hướng dẫn này.
Bạn có thể tìm hiểu ở đây cách tổng hợp nhiều cột và nhóm chúng.
Trước tiên, hãy xem kết quả của việc nhóm được đánh dấu trong hình ảnh dưới đây.
Dưới đây là các bước.
Phần nhóm
Chúng ta có thể sử dụng công thức MMULT trên trong Truy vấn làm phạm vi để lấy tổng theo nhóm của nhiều cột trong Google Sheets.
Công thức 3:
=QUERY({A2:A11,ArrayFormula(MMULT(n(B2:J11),(transpose(COLUMN(B1:J1)^0))))},"Select Col1, Sum (Col2) group by Col1 label Sum(Col2)''")
Trong công thức trên, phạm vi Truy vấn là Công thức mảng 2 ở trên (công thức nằm trong Dấu ngoặc nhọn trừ phạm vi A2:A11)
Công thức truy vấn này có hai cột.
Một là cột A2:A11 (vui lòng xem dữ liệu mẫu trên ảnh chụp màn hình ở trên) chứa tên chúng ta muốn nhóm.
Cột thứ hai là kết quả của công thức MMULT trên (tổng theo hàng của phạm vi B2:J11).
Tôi đã kết hợp cả hai cột này bằng cách sử dụng Dấu ngoặc nhọn như sau.
Trong đó, như tôi đã nói ở trên, cột 2 là tổng của phạm vi B2:J11.
Chúng ta có thể dễ dàng nhóm và tổng các phạm vi hai cột này trong Truy vấn.
Đó là những gì tôi đã làm trong công thức trên (Công thức 3).
Kết luận
Bạn có thể chỉnh sửa một chút công thức MMULT ở trên để có kết quả mảng trong Đếm.
Tôi đã giải thích nó riêng.
Tương tự: Cách mở rộng Kết quả Đếm trong Google Sheets giống như Công thức mảng
Học MMULT hơi phức tạp. Nhưng sau khi bạn đã thao tác nó, bạn có một chức năng trong tay để xử lý dữ liệu một cách hiệu quả trong Google Sheets.
