Trong Google Sheets, chính PRODUCT là một hàm mảng. Nó lấy các mảng (dải ô) và trả về kết quả duy nhất trong một ô. Vì vậy, chúng ta không thể sử dụng nó như một hàm mảng để tính tích hàng trong Google Sheets.
Cách sử dụng kéo và thả công thức
Khi bạn muốn nhân một loạt số với nhau theo hàng, bạn có thể sử dụng một công thức PRODUCT kéo và thả hoặc một công thức mảng thay thế.
Trong ví dụ trên, tôi đã sử dụng công thức PRODUCT trong ô G2 và kéo xuống để bao phủ tất cả các hàng.
Trong bài viết này, tôi sẽ giới thiệu với bạn ba công thức mảng thay thế cho công thức non-array PRODUCT trên.
Nhân hàng bằng dấu *
Nếu số lượng cột giới hạn như trên, chúng ta có thể sử dụng dấu * để nhân hàng trong Google Sheets.
Một số bạn có thể nghĩ rằng bạn có thể sử dụng công thức dựa trên toán tử * như dưới đây.
=ArrayFormula(B2:B*C2:C*D2:D*E2:E)Nhưng điều đó không đúng!
Nó sẽ trả về 0 trong một số ô trong cột kết quả. Bạn cũng có thể thấy rằng có một số 0 như một dấu vết trong toàn bộ cột sau hàng cuối cùng (hàng số 10).
Khi bạn nhân số > 0 với số 0 hoặc số 0 với số > 0, kết quả sẽ là 0.
Vì công thức xem các giá trị trong các ô trống như số 0, vấn đề đã nêu trên xảy ra.
Cách sửa lỗi
Để công thức này hoạt động chính xác, chúng ta nên chỉ định hai điều trong công thức. Trong các bước dưới đây, tôi sẽ giải thích chúng.
-
Thay thế các ô trống trong dải với số 1 trong biểu thức công thức.
-
Giới hạn công thức mở rộng chỉ đến ô cuối cùng không trống trong cột A.
Làm thế nào để thực hiện hai điểm trên trong công thức và trả về tích hàng chính xác bằng cách sử dụng công thức mảng PRODUCT thay thế trong Google Sheets?
Chúng ta có thể sử dụng một loạt các điều kiện IF với mỗi cột như dưới đây.
=ArrayFormula(if(A2:A="",1,A2:A)*if(B2:B="",1,B2:B)*if(C2:C="",1,C2:C)*if(D2:D="",1,D2:D)*if(E2:E="",1,E2:E))Công thức trên trong ô G2 sẽ trả về tích hàng chính xác trong Google Sheets nhưng sẽ bỏ lại số 1, thay vì số 0, trong các hàng trống.
Bây giờ chúng ta phải loại bỏ số 1 bằng cách giới hạn công thức chỉ mở rộng đến hàng cuối cùng có giá trị.
Đây là một ví dụ.
=ArrayFormula(if(A2:A="",,if(B2:B="",1,B2:B)*if(C2:C="",1,C2:C)*if(D2:D="",1,D2:D)*if(E2:E="",1,E2:E)))Công thức trên là một công thức thay thế cho công thức mảng PRODUCT trong Google Sheets.
Tuy nhiên, tôi không đề xuất sử dụng công thức trên trong hai tình huống sau.
-
Nếu có nhiều cột, việc viết công thức mà không gặp lỗi có thể là một nhiệm vụ khó khăn.
-
Khi bạn chèn các cột mới giữa các cột hiện có, bạn có thể muốn chỉnh sửa công thức thủ công. Điều này mất thời gian và dễ gây lỗi.
Công thức Query kết hợp (Dynamic)
Công thức bạn sẽ nhận được ở đây sẽ giúp bạn khắc phục các hạn chế trên.
Trước hết, hãy xem cách chúng ta có thể viết một công thức để thay thế công thức # 1 và công thức # 2 ở trên.
Query sau đây trong ô G1 sẽ thay thế công thức # 1 và Query và IF kết hợp sau đó sẽ thay thế công thức # 2.
=ArrayFormula(query({if(B2:E="",1,B2:E)},"Select Col1*Col2*Col3*Col4",0))=ArrayFormula(if(len(A1:A),query({if(B2:E="",1,B2:E)},"Select Col1*Col2*Col3*Col4",0),))Chú ý: Vui lòng chèn các công thức trên (một trong hai) vào ô G1 vì nó trả về một phần tiêu đề khác với tích.
Các công thức trên, công thức 1 và công thức 3 là các công thức sai và trong khi đó công thức 2 và công thức 4 là những công thức không động để trả về tích hàng trong Google Sheets chính xác.
Chúng ta có thể sửa đổi công thức 4 như một công thức thay thế cho công thức mảng PRODUCT động cho tích hàng trong Google Sheets.
= { "Tích hàng";ArrayFormula(if(len(A2:A),Query(query(({if(B2:E100="",1,B2:E100)}),"Select "&"Col"&TEXTJOIN("*Col",1,COLUMN(B1:E1)-1)),"offset 1",0),))}Giải thích công thức
Hãy tách bỏ câu truy vấn nội bộ (một phiên bản được sửa đổi của công thức # 4) và tìm hiểu nó trước.
=ArrayFormula(query({if(B2:E100="",1,B2:E100)},"Select "&"Col"&TEXTJOIN("*Col",1,COLUMN(B1:E1)-1)))Trong đó, mệnh đề SELECT trong Công thức 4, tức là Select Col1Col2Col3Col4, đã được thay thế bằng một tham chiếu cột động, tức là Select “&”Col”&TEXTJOIN(“Col”,1,COLUMN(B1:E1)-1).
Bạn có thể thay đổi phần văn bản theo ý của mình.
Công thức Query bên ngoài trong công thức tích hàng cuối cùng là để dịch chuyển 1 hàng (xem G1 trong hình ảnh trên) để loại bỏ tiêu đề.
Sau khi loại bỏ tiêu đề, chúng ta đã thêm một tiêu đề tùy chỉnh bằng Cặp ngoặc nhọn theo cú pháp dưới đây.
{"Tích hàng"; Công thức Truy vấn}Bạn có thể thay đổi văn bản theo ý của bạn.
Công thức mảng tích hàng chảy xuống sử dụng BYROW – Mới
Tôi đã sử dụng cột A (chứa ngày) để giới hạn việc mở rộng của mảng trong các hàng trống trong hai công thức mảng tích hàng (dấu * và truy vấn).
Nhưng nếu bạn sử dụng công thức sau đây tích hàng Byrow trong ô G1, bạn có thể tránh việc sử dụng cột đó.
={"Tích hàng (Byrow)";byrow(B2:E,lambda(row,if(count(row)=0,,product(row))))}Bởi vì công thức đếm số lượng giá trị trong mỗi hàng và nếu nó bằng 0, nó trả về một ô trống khác là tích.
Tôi đã thêm công thức trên vào ô H1 trong bảng mẫu dưới đây.
Đó là tất cả về sự thay thế công thức mảng PRODUCT cho tích hàng theo hàng trong Google Sheets.
Cảm ơn bạn đã theo dõi. Chúc bạn có những phút giây thú vị!
