Đầu tiên, hãy quên đi việc sử dụng ISBLANK hoặc LEN với MMULT, vì đó không phải là một giải pháp thực tế để tham chiếu dãy vô hạn trong hàm MMULT của Google Sheets. Trong bài viết này, hãy để tôi chia sẻ với bạn một mẹo/công thức MMULT hữu ích sẽ giúp bạn sử dụng MMULT trong các dòng vô hạn trong Google Sheets.
Hãy xem công thức MMULT dưới đây, nó tổng các hàng trong một phạm vi bị đóng/khép kín, ví dụ A2:C6.
=mmult(A2:C6,{1;1;1})
Cú pháp MMULT: MMULT(matrix1, matrix2)
Chú ý: Khi bạn muốn tổng các hàng bằng cách sử dụng MMULT, sử dụng ma trận 2 trong MMULT như trên. Ma trận 2 chỉ chứa các số 1 và số lượng số 1 này phải bằng số cột trong ma trận 1.
Tôi hy vọng công thức trên đủ đơn giản để bạn hiểu. Nhưng một chuyên gia sẽ không sử dụng công thức MMULT như trên. Tại sao?
Chỉ cần xóa bất kỳ giá trị nào trong phạm vi A2:C6. MMULT sẽ trả về lỗi! Để khắc phục lỗi đó, sử dụng MMULT như dưới đây.
Hàm N trong MMULT để giải quyết vấn đề ô trống
=ArrayFormula(mmult(n(A2:C6),{1;1;1}))
Hàm N biến ô trống thành số 0 trong khi giữ nguyên các số khác. Việc sử dụng ArrayFormula được bảo đảm vì hàm N không phải là một công thức mảng.
Bây giờ hãy tập trung vào vấn đề thực sự của chúng ta, tức là làm thế nào để sử dụng MMULT trong các dòng vô hạn (một số người gọi là phạm vi mở) trong Google Sheets.
Tôi muốn thay thế ma trận 1 trong công thức trên, tức là A2:C6, với một phạm vi vô hạn như A2:C. Làm thế nào để làm điều đó?
Tôi đã đề cập trước đó rằng phương pháp ISBLANK hoặc LEN sẽ không hoạt động ở đây. Trước tiên, hãy hiểu hai phương pháp trên, sau đó tôi sẽ giải thích cách sử dụng đúng MMULT trong các dòng vô hạn hoặc bạn có thể gọi là phạm vi vô hạn trong Google Sheets.
Cách sử dụng MMULT trong các dòng vô hạn/phạm vi mở trong Google Sheets
Sử dụng LEN với MMULT trong phạm vi mở
Thông thường, bạn có thể nghĩ đến việc sử dụng công thức LEN như sau với MMULT.
=ArrayFormula(If(len(A2:A),mmult(n(A2:C),{1;1;1}),))
Công thức MMULT này chắc chắn sẽ hoạt động trong các dòng vô hạn trong Google Sheets. Nhưng nó có một hạn chế như sau.
Điều đó có nghĩa là nếu có bất kỳ ô trống nào giữa phạm vi dữ liệu trong cột đầu tiên (cột áp dụng LEN), MMULT sẽ bỏ qua hàng đó trong tính toán. Tình huống này cũng xảy ra với ISBLANK và MMULT.
Sử dụng ISBLANK với MMULT trong phạm vi mở
Sử dụng ISBLANK như dưới đây trong MMULT để xử lý các hàng vô hạn trong MMULT trong Google Sheets, nhưng với hạn chế đã đề cập ở trên.
=ArrayFormula(If(isblank(A2:A)=FALSE,mmult(n(A2:C),{1;1;1}),))
Đến lúc nghĩ về một công thức đúng để sử dụng MMULT trong các dòng vô hạn.
Cách sử dụng đúng MMULT trong các dòng vô hạn
Công thức (đây không phải là công thức cuối cùng của tôi):
=ArrayFormula(mmult(N(array_constrain(A2:C,MATCH(2,1/(A2:A<>""),1),3)),{1;1;1}))
Giải thích:
Trước khi diễn giải công thức trên, hãy chia sẻ với bạn cách điền ma trận2 một cách động.
Bạn có thể thay thế {1;1;1} bằng sequence(columns(A2:C2),1)^0. Ma trận2 được tạo ra từ dãy này sẽ hữu ích nếu ma trận1 của bạn có một số lượng cột rất lớn. Tôi sẽ tích hợp công thức ma trận2 này vào công thức trên.
=ArrayFormula(mmult(N(array_constrain(A2:C,MATCH(2,1/(A2:A<>""),1),3)),sequence(columns(A2:C2),1)^0))
Phần duy nhất còn lại để diễn giải trong công thức là ma trận1, đó là N(array_constrain(A2:C,MATCH(2,1/(A2:A<>””),1),3))
Nếu bạn kiểm tra công thức này riêng lẻ, hãy bao gồm ArrayFormula như sau.
=ArrayFormula(N(array_constrain(A2:C,MATCH(2,1/(A2:A<>""),1),3)))
Trong đó, công thức Match trả về số hàng trong phạm vi, tức là 5.
=ArrayFormula(MATCH(2,1/(A2:A<>"")))
Bài viết liên quan (nhưng trong Excel, cũng hoạt động tốt trong Sheets): Địa chỉ ô không trống cuối cùng bỏ qua ô trống trong một cột trong Excel.
Về phần Array_Constrain, nó giới hạn số hàng trong A2:C thành 5 (số hàng trả về của công thức Match) và 3 cột và đó là ma trận1 của MMULT.
Cú pháp của ARRAY_CONSTRAIN (để giúp bạn hiểu diễn giải công thức):
ARRAY_CONSTRAIN(input_range, num_rows, num_cols)
input_range – A2:C
num_rows – MATCH(2,1/(A2:A<>””),1)
num_cols – 3
Để mang tính động hơn, bạn có thể thay thế 3 (num_cols) bằng columns(A2:C2) và công thức cuối cùng để sử dụng MMULT trong các dòng vô hạn trong Google Sheets là:
=ArrayFormula(mmult(N(array_constrain(A2:C,MATCH(2,1/(A2:A<>""),1),columns(A2:C))),{1;1;1}))
Trong ví dụ của tôi, ma trận1 là phạm vi mở. Trong trường hợp của bạn, nếu ma trận2 là phạm vi mở, bạn có thể làm theo các bước ma trận1 trên với ma trận2 và ngược lại. Đó là tất cả. Chúc vui vẻ!
