Trong bài viết này, chúng ta sẽ tìm hiểu cách sử dụng hàm BYROW trong Google Sheets, một hàm trợ giúp LAMBDA (LHF). Nếu bạn đã biết về hàm LAMBDA, việc sử dụng hàm trợ giúp BYROW sẽ trở nên dễ dàng.
Hàm BYROW có công dụng gì?
Nó mang đến những khả năng mới cho Google Sheets. Trước đây, không có cách nào để loại bỏ các hàng ẩn (hoặc bao gồm các hàng hiển thị) trong các công thức mà không sử dụng một cột trợ giúp. Nhưng bây giờ, điều đó trở nên khả thi sau khi hàm này được triển khai. Tôi sẽ giải thích nó trong phần sau của bài viết này.
Ví dụ:
Để minh họa, dưới đây là một ví dụ về việc sử dụng hàm BYROW như một sự thay thế cho hàm MMULT.
Công thức MMULT dưới đây thay thế cho các công thức =sum(B3:D3), =sum(B4:D4) và =sum(B5:D5) trong F3, F4 và F5 từ bên trong F3.
=mmult(B3:D5,{1;1;1})
Chúng ta cũng có thể sử dụng hàm BYROW như một cách thay thế.
Cú pháp và đối số của hàm BYROW:
BYROW(array_or_range, lambda)
- array_or_range: Mảng hoặc phạm vi trong đó chúng ta muốn kết quả mảng theo hàng (ví dụ: B3:D5).
- lambda: Một hàm LAMBDA cho một hàng duy nhất (ví dụ: B3:D3), thường là hàng đầu tiên, để tính toán một kết quả duy nhất. Hàm BYROW sẽ mở rộng nó trong array_or_range đã cho.
Cách sử dụng hàm BYROW trong Google Sheets:
- Nhập =sum(B3:D3) vào ô F3.
- Chúng ta có thể chuyển đổi nó thành một hàm LAMBDA riêng lẻ như sau: =LAMBDA(r,SUM(r))(B3:D3).
- Trong công thức LAMBDA trên, phần được làm nổi bật là function_call, chúng ta chỉ cần sử dụng nó khi sử dụng LAMBDA độc lập. Vì vậy, trong BYROW, chúng ta phải bỏ qua nó.
- Vì vậy, trong công thức, B3:D5 là array_or_range và LAMBDA(r,SUM(r)) là lambda.
- Hàm BYROW mở rộng LAMBDA được mã hóa cho hàng đầu tiên sang tất cả các hàng trong mảng (array_or_range).
Xử lý phạm vi mở và giá trị 0:
Nếu bạn sử dụng công thức BYROW trên cho array_or_range B3:D thay vì B3:D5, bạn sẽ thấy các giá trị 0 trong cột kết quả ở các hàng trống.
Chúng ta có thể xử lý các phạm vi mở tương tự như cách chúng ta xử lý giá trị 0 trong các công thức thông thường.
=ArrayFormula(if(A3:A=””,,BYROW(B3:D,LAMBDA(r,SUM(r)))))
Công thức IF kiểm tra xem có giá trị trong A3:A và trả về kết quả công thức BYROW trong các hàng đúng.
Nhược điểm của phương pháp này là nó liên quan đến một cột phụ, tức là cột A3:A. Nếu bạn không muốn sử dụng cột phụ đó, bạn có thể sử dụng LET như sau:
=ArrayFormula(LET(result,BYROW(B3:D,LAMBDA(r,SUM(r))),IF(result=0,if(,,),result)))
Nó sẽ loại bỏ tất cả các giá trị 0, bất kể chúng có ở trong hàng trống hoặc trong hàng mà công thức BYROW trả về giá trị 0.
Sử dụng hàm BYROW để bao gồm các hàng hiển thị (loại trừ các hàng ẩn) trong việc tổng hợp:
Tôi thở phào nhẹ nhõm khi phát hiện ra rằng công thức BYROW của tôi có thể mở rộng công thức LAMBDA dựa trên SUBTOTAL.
Bạn có biết lý do là gì?
Theo như tôi biết, hàm SUBTOTAL là hàm duy nhất có khả năng loại trừ các hàng ẩn trong quá trình tính toán.
Chúng ta có thể sử dụng tính năng đó với hàm BYROW để chỉ bao gồm các hàng hiển thị (bỏ qua các hàng ẩn) trong việc tổng hợp.
Ví dụ dưới đây, hàm COUNTIFS trong ô D1 chỉ đếm “Apple” trong các hàng hiển thị trong cột A.
Làm thế nào chúng ta sử dụng hàm BYROW trong Google Sheets để xử lý các hàng ẩn?
=BYROW(A2:A,LAMBDA(range,SUBTOTAL(103,range)))
Nếu áp dụng công thức BYROW trên vào ô E2, bạn sẽ thấy công thức trả về giá trị 1 trong tất cả các hàng không trống và giá trị 0 trong các hàng trống.
Nếu bạn ẩn bất kỳ hàng nào bằng cách nhóm, lọc hoặc ẩn thủ công, giá trị được trả về bởi công thức trên trong ô tương ứng, ngay cả khi bạn không thể thấy nó, sẽ là 0.
Vì vậy, tôi đã sử dụng công thức BYROW trên trong COUNTIFS trong ô D1 để xử lý các hàng ẩn như sau:
=countifs(A2:A,”Apple”,BYROW(A2:A,LAMBDA(range,SUBTOTAL(103,range))),”>0″)
Sử dụng số hàm 103 trong hàm SUBTOTAL để loại trừ tất cả các hàng ẩn (được ẩn qua bộ lọc, nhóm hoặc ẩn thủ công).
Để chỉ loại trừ các hàng được ẩn bằng cách sử dụng bộ lọc, thay thế số 103 bằng 3.
Chúng ta cũng có thể sử dụng 9 hoặc 109 nhưng chỉ với các cột số. Số hàm 103 và 3 hoạt động bất kể loại dữ liệu.
