Để tìm chuỗi dài nhất trong một cột trong Google Sheets, chúng ta có thể sử dụng các công thức dựa trên Index-Match hoặc Filter.
Công thức Index-Match
Chúng ta sẽ bắt đầu với công thức Index-Match kết hợp với Max và Len.
Cách 1
Áp dụng công thức sau vào ô C2:
=index( B2:B, match(max(len(B2:B)),len(B2:B),0), 0 )
Công thức index-match trên trả về chuỗi dài nhất trong khoảng cột B2:B trong ô C2, tức là “Ernestine”.
Có thể bạn muốn tìm hiểu công thức này?
Dễ thôi! Dưới đây là cách hoạt động của nó:
Cú pháp:
INDEX(reference, row, column)
Reference – B2:B
Row – match(max(len(B2:B)),len(B2:B),0)
Column – 0
Công thức Match trên đóng một vai trò quan trọng trong việc tìm chuỗi dài nhất trong cột B.
Nó trả về vị trí tương đối của chuỗi dài nhất trong khoảng B2:B.
Index tạo ra chuỗi dài nhất bằng cách dịch chuyển số hàng đó và không cột.
Với ví dụ trên, công thức match trả về số 10, tức là vị trí tương đối của chuỗi dài nhất trong B2:B.
Do đó, Index dịch chuyển mười hàng và không cột.
Hãy cùng tìm hiểu công thức Match. Đây là công thức đó!
Cú pháp:
MATCH(search_key, range, search_type)
Search Key – max(len(B2:B))
Range – len(B2:B)
Search Type – 0
Nếu bạn có thể hiểu phần len(B2:B), bạn sẽ dễ dàng hiểu công thức này. Đây là công thức đó!
Lưu ý: Trong Index, chúng ta có thể sử dụng len(B2:B), nhưng khi sử dụng độc lập, bạn nên sử dụng arrayformula(len(B2:B)).
Phần trên (cột C) là phạm vi công thức Match (Len trả về độ dài của các ký tự trong mỗi tên).
Khóa tìm kiếm là giá trị MAX trong phạm vi này (cột C) và sẽ là 9 (vui lòng xem các ô được đánh dấu màu xanh lam).
Vì vậy, Match so khớp với độ dài 9 (khóa tìm kiếm) và trả về 10, tức là vị trí tương đối của khóa tìm kiếm trong phạm vi.
Cách 2
Đây là một công thức khác bạn có thể sử dụng để tìm chuỗi dài nhất trong một cột trong Google Sheets.
=filter(B2:B,len(B2:B)=max(len(B2:B))) Nó gọn hơn công thức OPTION 1 ở trên.
Ở đây, công thức lọc các tên dựa trên độ dài của chuỗi.
Nếu độ dài của chuỗi bằng với độ dài tối đa trong phạm vi, trả về giá trị đó.
Công thức này đôi khi trả về nhiều giá trị hơn.
Ví dụ, nếu có hai tên có độ dài 9 ký tự mỗi cái, công thức sẽ trả về cả hai cái.
Nhưng Index-match sẽ chỉ trả về tên đầu tiên.
Công thức mảng để tìm chuỗi dài nhất trong mỗi cột
Nếu bạn muốn thực hiện cùng một phân tích trong từng cột, bạn có thể sử dụng công thức mảng thay thế cho index-match.
Trong ví dụ dưới đây, công thức trong ô B8 tìm ra các giá trị dài nhất trong từng cột trong phạm vi B2:F6.
=ArrayFormula( vlookup( dmax( {column(B1:F1);len(B2:F6)},sequence(1,columns(B1:F1),1), {column(B1); if(,,)} ), H2:I5, 2,0 ) )
Công thức giải thích
Đó là một công thức mảng Vlookup.
Cú pháp: ARRAYFORMULA(VLOOKUP(search_key, range, index, [is_sorted]))
Dưới đây là tham chiếu phạm vi trong công thức đó.
Tham chiếu
Xem phạm vi trợ giúp H2:I5. Nó làm vai trò như phạm vi trong Vlookup.
Dải ô I2:I5 chứa tất cả các chuỗi duy nhất trong B2:F6.
Bạn có thể nhập các giá trị đó thủ công hoặc sử dụng công thức sau trong ô I2.
=sort(filter(unique(flatten(B2:F6)),unique(flatten(B2:F6))<>""))Trong H2:H5, tôi đã nhập độ dài của các chuỗi duy nhất trên.
Ở đây, tôi đã sử dụng công thức sau trong ô H2.
=ArrayFormula(len(I2:I5))Bây giờ quay trở lại công thức mảng trong ô B8, công thức trả về chuỗi dài nhất trong từng cột.
Chúng ta đã thấy phạm vi được sử dụng trong công thức này ở trên. Các khóa tìm kiếm là công thức DMAX như sau.
Khóa tìm kiếm
dmax({column(B1:F1);len(B2:F6)},sequence(1,columns(B1:F1),1),{column(B1);if(,,)})
Hãy thử nghiệm nó. Đối với điều đó, chúng ta nên bọc nó trong hàm ARRAYFORMULA như sau.
=ArrayFormula(dmax({column(B1:F1);len(B2:F6)},sequence(1,columns(B1:F1),1),{column(B1);if(,,)}))
Thông qua công thức DMAX trên trả về độ dài của chuỗi dài nhất trong từng cột.
Vlookup sử dụng nó như là khóa tìm kiếm để tìm kiếm cột đầu tiên trong phạm vi H2:I5 và trả về các giá trị tương ứng từ I2:I5.
Giải thích khóa tìm kiếm (DMAX)
Cú pháp:
DMAX(database, field, criteria)
Database – {column(B1:F1);len(B2:F6)}
Như bạn có thể biết, các hàm cơ sở dữ liệu yêu cầu nhãn trường.
Vì phạm vi trên B2:F6 không chứa các nhãn trường giống nhau, tôi đã sử dụng số cột làm nhãn trường.
Hãy thử nghiệm cơ sở dữ liệu (chúng ta nên sử dụng ArrayFormula với cơ sở dữ liệu trong kiểm tra độc lập).
Field – sequence(1,columns(B1:F1),1)
Nó tương đương với ={1,2,3,4,5}, tức là các chỉ mục cột.
Criteria – {column(B1);if(,,)}
Nhãn trường đầu tiên là 2 (vui lòng xem ô B8 ảnh trên), và =column(B1) trả về nó.
IF trả về ký tự null, có nghĩa là không có tiêu chí.
Đó là tất cả về cách tìm chuỗi dài nhất trong mỗi cột trong Google Sheets.
Cảm ơn bạn đã đọc. Chúc bạn vui vẻ!
Tương tự: Tìm chuỗi dài nhất trong mỗi hàng trong Google Sheets.
