Trong một bảng trong Google Sheets, tôi muốn tìm chuỗi dài nhất trong mỗi hàng. Có những tùy chọn nào để thực hiện điều này?
Nếu đây là câu hỏi của bạn, tôi sẽ cung cấp một vài công thức để giúp.
Trong bài viết này, hãy tìm hiểu hai công thức tốt nhất để tìm chuỗi dài nhất trong mỗi hàng – một công thức mảng và một công thức không mảng.
Trong bảng dưới đây có năm hàng trong phạm vi B2:F6. Trong G2:G6, bạn có thể tìm thấy chuỗi dài nhất tương ứng với mỗi hàng.
Để làm điều đó, chúng ta có thể sử dụng các công thức Match (không mảng) hoặc Dmax (mảng).
Chú ý: Ngoài ra, chúng ta sẽ sử dụng Index và Vlookup với các công thức trước và sau đó.
Hãy xem từng công thức một.
Công thức không mảng để tìm chuỗi dài nhất trong mỗi hàng
Công thức (chèn vào ô G2, sau đó sao chép dọc): =index(B2:F2,1, match(max(len(B2:F2)),len(B2:F2),0))
Bạn có thể chèn công thức này vào ô G2 và kéo thả fill handle xuống ô G2.
Để hiển thị fill handle, nhấp vào ô G2. Sau đó, nhấp và kéo hình vuông màu xanh nhỏ ở góc dưới phải, như thế này:
Giải thích công thức
Để tìm hiểu công thức trên, bạn nên thử nghiệm các công thức riêng lẻ được sử dụng trong kết hợp.
Khi làm như vậy, sử dụng ArrayFormula, Index (không sử dụng đối số offset) hoặc Sort với chúng vì chúng không hoạt động độc lập.
- Hàm Len trả về độ dài của các chuỗi trong B2:F2 (nó trả về một mảng kết quả).
=index(len(B2:F2))
- Trả về độ dài lớn nhất của các chuỗi trong B2:F2 (nó trả về một giá trị duy nhất).
=index(max(len(B2:F2)))
- Hàm Match sử dụng công thức ở điểm số 2 làm khóa tìm kiếm và điểm số 1 là phạm vi tìm kiếm.
Sau đó, trả về vị trí tương đối của giá trị ở điểm số 2 trong mảng ở điểm số 1.
=index(match(max(len(B2:F2)),len(B2:F2),0))
- Công thức Index (công thức cuối cùng) sau đó dịch chuyển một số ô tương ứng trong B2:F2.
Nó trả về chuỗi dài nhất trong hàng đó.
Chú ý: Trong các điểm 1 đến 3, Index không dịch chuyển bất kỳ giá trị nào. Nó chỉ giúp công thức nội bộ sử dụng một mảng như đối số.
Công thức mảng để tìm chuỗi dài nhất trong mỗi hàng
Phần này dành cho những người yêu thích công thức mảng.
Chúng ta có thể sử dụng hàm cơ sở dữ liệu Dmax (với Vlookup là kết hợp) để tìm chuỗi dài nhất theo hàng.
Như bạn có thể đã biết, Dmax trong Sheets chỉ hoạt động với dữ liệu có cấu trúc.
Dữ liệu của chúng ta ở trên không có cấu trúc vì nó không có nhãn trường. Hơn nữa, Dmax chỉ hoạt động với giá trị số.
Phần số chúng ta có thể giải quyết bằng hàm Len, tương tự như ví dụ đầu tiên của chúng ta.
Vậy còn nhãn trường thì sao?
Chúng ta có thể sử dụng nhãn trường ảo.
Hãy tìm hiểu dưới đây cách sử dụng Dmax để tìm chuỗi dài nhất trong mỗi hàng trong Google Sheets.
Công thức (chèn vào ô G2, không sao chép dọc): =ArrayFormula(vlookup(dmax(transpose({B2:B6,len(B2:F6)}),sequence(rows(B2:B6),1,1),{if(,,);if(,,)}),{len(H2:H5),H2:H5},2,0))
Ghi chú:
-
Cuộn xuống screenshot số 4. Xem phạm vi trợ giúp H2:H5. Chúng ta cần phạm vi đó để công thức trên trả về chuỗi dài nhất trong mỗi hàng.
-
Trong H2:H5, chúng ta nên nhập các chuỗi duy nhất trong B2:F6. Tôi sẽ giải thích thêm về điều đó sau.
Khác với ví dụ đầu tiên của chúng ta, ở đây, không cần kéo fill handle màu xanh dưới.
Điều duy nhất bạn cần chú ý là chọn và xóa bất kỳ giá trị / công thức (ẩn) nào trong phạm vi G3:G6.
Cách này, chúng ta có thể đảm bảo rằng công thức trong ô G2 không trả về lỗi REF.
Giải thích công thức:
Hãy tìm hiểu công thức từng phần.
Hãy xem phần đậm trong công thức trên, đó là Dmax. Chúng ta sẽ học phần đó trước.
dmax(transpose({B2:B6,len(B2:F6)}),sequence(rows(B2:B6),1,1),{if(,,);if(,,)})
Cú pháp: DMAX(database, field, criteria)
Cơ sở dữ liệu (database)
Để tìm hiểu công thức Dmax trên, ‘giúp’ trả về chuỗi dài nhất trong mỗi hàng, bạn nên biết ba điểm quan trọng, như tôi đã đề cập ở trên.
Đó là gì?
-
Dmax không hoạt động với chuỗi.
-
Nó không thể trả về các giá trị lớn nhất trong hàng.
-
Nó yêu cầu nhãn trường (dữ liệu có cấu trúc).
Chúng ta sẽ đáp ứng tất cả các yêu cầu này một cách thông minh như sau.
-
Sử dụng Len(B2:F6) thay vì B2:F6.
-
Thay đổi hướng dữ liệu bằng cách sử dụng Transpose. Tôi nghĩa là thay đổi Len(B2:F6) thành Transpose(Len(B2:F6)).
-
Thêm phạm vi ô B2:B6 vào Transpose bằng cặp ngoặc nhọn.
transpose({B2:B6,len(B2:F6)})
Vì vậy, khi chuyển vị, nó, tức là phạm vi ô B2:B6, sẽ hoạt động như nhãn trường.
Khi thử nghiệm, sử dụng Index, ArrayFormula hoặc Sort. Ở đây tôi sử dụng ArrayFormula với nó.
Cơ sở dữ liệu ảo trên là chìa khóa để tìm chuỗi dài nhất trong mỗi hàng bằng cách sử dụng Dmax trong Google Sheets.
Trường (field)
Khi bạn kiểm tra screenshot ở trên, bạn có thể thấy có năm trường (B, C, D, E và F). Để chỉ định chúng một cách linh hoạt, chúng ta có thể sử dụng công thức Sequence sau đây.
sequence(rows(B2:B6),1,1)
Nó trả về số trường theo chiều dọc, vì vậy kết quả của Dmax cũng sẽ dọc. Đây là một phiên bản không động của nó.
{1;2;3;4;5}
Tiêu chí (criteria)
Chúng ta không muốn sử dụng bất kỳ tiêu chí nào. Nhưng trong Dmax, chúng ta phải chỉ định ít nhất hai ô trống theo chiều dọc. Các thử nghiệm IF dưới đây sẽ đảm nhận việc đó.
{if(,,);if(,,)})
Dmax sẽ trả về độ dài của chuỗi dài nhất trong mỗi hàng, không phải chuỗi dài nhất chính nó.
Vlookup giúp chúng ta trả về chuỗi dài nhất theo hàng bằng cách sử dụng đầu ra công thức Dmax.
Làm thế nào?
Phần Vlookup
Cú pháp: VLOOKUP(search_key, range, index, [is_sorted])
Search Key – Dmax ở trên trả về các khóa tìm kiếm.
Range – Hãy xem phạm vi trợ giúp H2:H5 trên screenshot ở trên. Đó là một danh sách chứa các chuỗi duy nhất trong B2:F6.
Chú ý: Bạn có thể nhập vào thủ công hoặc sử dụng =sort(filter(unique(flatten(B2:F6)),unique(flatten(B2:F6))<>””)) trong ô H2.
Chúng ta sẽ sử dụng công thức sau làm phạm vi.
{len(H2:H5),H2:H5}
Mảng đầu tiên chứa độ dài của các chuỗi duy nhất.
Index – 2
Vlookup tìm kiếm kết quả của Dmax, tức là độ dài của chuỗi dài nhất trong mỗi hàng, trong mảng thứ nhất trong phạm vi trên.
Sau đó trả về các chuỗi tương ứng từ mảng thứ hai.
Đó là tất cả.
Hy vọng bạn đã hiểu cách tìm chuỗi dài nhất trong mỗi hàng bằng cách sử dụng các công thức mảng / không mảng trong Google Sheets.
Cảm ơn bạn đã đọc bài viết này. Chúc bạn vui vẻ!
Tương tự: Tìm chuỗi dài nhất trong một cột trong Google Sheets
