Cách mở rộng kết quả đếm trong Google Sheets Như Công thức Mảng

GiGiKing·Tháng ba 24, 2024·0 comments

Nếu bạn hỏi tôi cách mở rộng kết quả đếm trong Google Sheets, câu trả lời của tôi sẽ là sử dụng hàm MMULT, chứ không phải sử dụng bất kỳ hàm Count nào!

Hầu hết các hàm Count lấy một mảng và trả về một ô đơn. Countif và Countifs là ngoại lệ nhưng không phù hợp cho việc đếm từng hàng.

Cập nhật: Bây giờ chúng ta có thể mở rộng các hàm đếm bằng cách sử dụng Lambda. Tôi sẽ thêm giải pháp đó ngay sau công thức MMULT.

Trước tiên, hãy cố gắng hiểu sự khác nhau giữa Số hàng và Đếm từng hàng.

Số hàng:

Row Count - Non expanding formula

Đếm từng hàng:

Each Row Count - Non expanding formula

Mở rộng kết quả đếm có nghĩa là chỉ có một công thức duy nhất trả về số lượng giá trị trong mỗi hàng.

Khi chúng ta xem xét ví dụ trên, tôi muốn thay thế nhiều công thức đếm (xem công thức trong ô G2:G4) bằng một công thức duy nhất trong ô G1.

Có lẽ bạn mong đợi một công thức duy nhất sẽ trả về kết quả đếm mở rộng trong Google Sheets giống như công thức mảng.

Điều đó hoàn toàn có thể với MMULT!

Vui lòng lưu ý rằng bạn có thể sử dụng BYROW cùng với các hàm Đếm để trả về kết quả mảng như trên trong Google Sheets.

Hãy cuộn xuống để xem hình ảnh, tôi đã tô đậm công thức duy nhất màu đỏ trả về kết quả đếm trong mỗi hàng. MMULT là chức năng chính được sử dụng trong công thức đó.

Bạn có thể tìm hiểu cách mở rộng kết quả đếm trong Google Sheets bằng cách sử dụng MMULT và BYROW.

Như một lưu ý phụ, nếu bạn muốn bao gồm điều kiện, bạn có thể chuyển sang hướng dẫn sau: Countif Across Columns Row by Row – Array Formula in Google Sheets

Mở rộng kết quả đếm trong Google Sheets sử dụng MMULT

Công thức MMULT dưới đây, chúng ta có thể sử dụng để mở rộng kết quả đếm trong Google Sheets, cũng nằm trong các hướng dẫn dưới đây. Tôi hy vọng bạn sẽ tìm thấy thời gian rảnh để kiểm tra chúng.

Nếu bạn đã làm theo các hướng dẫn này, bạn chỉ cần đọc hướng dẫn này để hiểu cách sử dụng công thức MMULT như là một công thức đếm. Nếu không, bạn có thể mở một bảng tính Google Sheets trống và áp dụng các bước dưới đây.

Dữ liệu Ví dụ:

Count Function to Return an Array Result in Google Sheets

Đây là một bảng chấm công cơ bản ghi lại trong một tuần. Trong đó, chỉ có một công thức duy nhất trong ô H2. Công thức đó đếm tất cả các hàng riêng lẻ và đặt tổng kết quả vào ô cuối cùng trong mỗi hàng.

Thường thì, chúng ta có thể sử dụng COUNTA như sau trong ô H2 và kéo xuống theo ý muốn để đếm văn bản.

=counta(B2:G2)

Tôi đã sử dụng MMULT vì các hàm đếm trong Google Sheets không thể trả về kết quả mảng mở rộng.

Công thức MMULT dưới đây có thể mở rộng Kết quả Đếm trong Google Sheets.

Công thức MMULT để đếm giá trị trong mỗi hàng

=mmult( iferror(ArrayFormula(len(B2:G8)/len(B2:G8)),0), ArrayFormula(transpose(COLUMN($B$2:$G2)^0)) )

Dưới đây là một công thức hoàn thiện hơn, nhưng tôi sẽ giải thích công thức trên chi tiết trong phần giải thích công thức dưới đây.

=ArrayFormula( if(len(A2:A),( mmult( iferror(len(B2:G)/len(B2:G),0), transpose(COLUMN($B$2:$G2)^0)) ), ) )

Sự khác biệt giữa hai công thức trên là gì?

Trong công thức thứ hai, để hoạt động trong các hàng vô hạn, tôi đã sửa đổi tham chiếu mảng B2:G8 thành B2:G. Tiếp theo, di chuyển công thức ArrayFormula lên phía trước, vì vậy chỉ cần một công thức ArrayFormula duy nhất.

Ngoài ra, tôi đã thêm if(len(A2:A) vào công thức để điều khiển các hàng trống. Vì chúng ta đang sử dụng hàng vô hạn làm phạm vi.

MMULT trong Sheets dễ gặp lỗi nếu có các ô trống trong phạm vi (ma trận).

Lưu ý: Các công thức trên sẽ đếm cả số và chuỗi văn bản trong mỗi hàng và mở rộng kết quả.

Giải thích công thức

Để hiểu công thức này, bạn nên tìm hiểu cách sử dụng công thức MMULT trong Google Sheets. Vui lòng học trước MMULT, sau đó tiếp tục.

Để tham khảo nhanh, đây là cú pháp của MMULT.

MMULT(matrix1, matrix2)

Trong ví dụ trên, bạn có thể thấy hai công thức mảng. Kết quả của hai công thức đó hoạt động như ma trận trong MMULT. Đó là những công thức đó?

Công thức 1 như ma trận 1

iferror(ArrayFormula(len(B2:G8)/len(B2:G8)),0)

Công thức này sẽ trả về kết quả mảng như dưới đây.

create matrix for counta in Google Sheets

Công thức Len đếm độ dài của các ký tự trong phạm vi và trả về kết quả đó dưới dạng giá trị số trong mỗi ô.

Sau đó, tôi đã sử dụng cùng công thức để chia kết quả Len đầu tiên. Điều này để đảm bảo nó sẽ luôn trả về 1 hoặc 0.

Nếu độ dài của ký tự hoặc số trong các ô là một hoặc nhiều hơn một, công thức sẽ trả về 1, ngược lại trả về 0.

Công thức 2 như ma trận 2

ArrayFormula(transpose(COLUMN($B$2:$G2)^0))

Công thức Column bên trong trả về số cột từ 2 đến 7 của phạm vi B2:G2 theo hàng (ngang). Tôi đã sử dụng ký hiệu ^0 để chuyển đổi kết quả của công thức cột trả về 1 thay vì số cột thực tế.

Chúng tôi muốn kết quả trên dưới dạng cột (theo chiều dọc) để sử dụng trong MMULT. Tại sao?

Ma trận 1 (công thức 1) chứa 6 cột. Là một quy tắc trong nhân ma trận (MMULT), tổng số cột trong ma trận 1 phải bằng số hàng trong ma trận 2.

Vì vậy, những gì tôi đã làm là chuyển đổi đầu ra của công thức cột thành một cột và 6 hàng.

Vui lòng lưu ý rằng cả hai ma trận chỉ chứa số 1. Vì vậy, tích ma trận của hai ma trận sẽ bằng số đếm hoặc tổng.

Đó là tất cả. Hãy làm theo các bước một cách cẩn thận để có được kết quả đếm mở rộng trong Google Sheets.

Mở rộng kết quả đếm trong Google Sheets sử dụng BYROW (Mới)

Chúng ta đã sử dụng giải pháp MMULT vì chưa có cách nào khác để mở rộng công thức =counta(B2:G2) trong mỗi hàng.

Nhưng bây giờ chúng ta có thể sử dụng BYROW để mở rộng công thức Counta trên trên trong Google Sheets.

Chèn giải pháp BYROW này vào ô H2, và hãy xem điều kỳ diệu!

=byrow(B2:G,lambda(row,counta(row)))

Chúng ta có thể bao gồm một kiểm tra logic để bỏ qua các hàng trống, tương tự như MMULT của chúng ta.

Nhưng ở đây, nó sẽ nằm trong LAMBDA như sau.

=byrow(B2:G,lambda(row,if(counta(row)=0,,counta(row))))

Nếu bạn có bất kỳ thắc mắc nào về việc sử dụng các công thức trên, hãy viết cho tôi trong phần bình luận. Chúc vui vẻ!

Tài nguyên:

GiGiKing

Related posts