Công thức Excel: Nhận tổng cột
Công thức Excel: Nhận tổng cột

Công thức chung
=MMULT(SEQUENCE(1,ROWS(rng),1,0),--rng)
Tóm lược

Để nhận một mảng tổng số cột dựa trên một dải giá trị số, bạn có thể sử dụng công thức dựa trên Chức năng MMULT, với sự giúp đỡ từ SỰ NỐI TIẾPCỘT. Trong ví dụ được hiển thị, công thức trong K5 là:

=MMULT(SEQUENCE(1,ROWS(data),1,0),--data)

Ở đâu dữ liệuphạm vi được đặt tên C5: I13. Kết quả là một mảng trong tổng số với bảy tổng, một tổng cho mỗi cột trong phạm vi, như được thấy trong hàng 15.

Giải trình

Khi làm việc với các công thức Excel, có một số trường hợp bạn muốn mảng tổng chứ không phải là một tổng. Các chức năng như TỔNG sẽ không hoạt động, bởi vì chúng tổng hợp lại kết quả và trả về một giá trị duy nhất. Các Chức năng MMULT, trả về tích ma trận của hai mảng (còn được gọi là “tích chấm”) rất hữu ích trong những trường hợp này.

Trong ví dụ này, mục tiêu là trả về một mảng có bảy tổng phụ, một tổng cho mỗi ngày có tên trong hàng 4. Chúng ta có thể thực hiện điều này với phép nhân ma trận bằng cách sử dụng hàm MMULT. MMULT có hai mảng, array1array2và yêu cầu số lượng các cột trong mảng 1 giống với số lượng các hàng trong mảng 2. Ma trận kết quả sẽ có cùng số hàng với ma trận đầu tiên và cùng số cột với ma trận thứ hai.

Trong ví dụ, chúng ta chỉ muốn một hàng tổng, vì vậy mảng đầu tiên chỉ cần chứa một hàng. Ngoài ra, vì chúng ta không muốn thay đổi bất kỳ giá trị gốc nào, nên mảng này chỉ nên chứa số 1 (tức là nhân với 1 không làm thay đổi giá trị ban đầu). Các Hàm SEQUENCE cung cấp một cách thuận tiện để tạo mảng mà chúng tôi cần, với một chút trợ giúp từ Hàm ROWS:

SEQUENCE(1,ROWS(data),1,0) // returns {1,1,1,1,1,1,1,1,1}

Để ý hàng được đặt thành 1, cột là 9 (từ ROWS), khởi đầu giá trị là 1 và bươc giá trị bằng không. Kết quả là một mảng 1 x 7 chỉ được lấp đầy bởi 1s.

Đối với mảng thứ hai, chúng tôi sử dụng tập hợp đầy đủ các giá trị trong phạm vi được đặt tên dữ liệu (C5: I13):

=MMULT({1,1,1,1,1,1,1,1,1},--data)

Chúng tôi sử dụng một Âm kép ở đây để bảo vệ khỏi các ô trống, điều này sẽ khiến MMULT ném ra #VALUE! lỗi. Âm kép buộc bất kỳ ô trống nào được đánh giá là 0. Với hai mảng này, hàm MMULT tính tích ma trận và trả về một mảng chứa 7 tổng, một cho mỗi cột trong dữ liệu: 

{59,77,67,68,69,72,67}

Các giá trị này được trả về ô C15 và tràn vào khoảng C15: I15.

Cú pháp thay thế

Trong các phiên bản Excel cũ hơn không có chức năng SEQUENCE, bạn có thể thấy một cú pháp khác cho công thức này dựa trên Hàm TRANSPOSE:

=MMULT(TRANSPOSE(ROW(data)^0),data)

Mặc dù khó hiểu hơn một chút, nhưng cú pháp này là một cách thông minh để thực hiện điều tương tự. Nâng bất kỳ số nào lên lũy thừa của không (0) dẫn đến kết quả là 1, vì vậy Chức năng ROW trả về 9 số:

ROW(data) // returns {5;6;7;8;9;10;11;12;13}

được nâng lên thành lũy thừa bằng 0 với toán tử số mũ (^):

ROW(data)^0) // returns {1;1;1;1;1;1;1;1;1}

Cuối cùng, TRANSPOSE lật mảng:

TRANSPOSE({1;1;1;1;1;1;1;1;1}) // returns {1,1,1,1,1,1,1,1,1}

và kết quả được chuyển cho MMULT cho mảng thứ hai như trước.

https://exceljet.net/formula/get-column-totals