Hãy cùng tôi tìm hiểu cách tìm kiếm Mode của giá trị văn bản trong Google Sheets. Bạn có thể thấy rằng trên Internet đã có nhiều công thức để tìm chuỗi văn bản xuất hiện phổ biến nhất, có thể gọi là Mode, trong một danh sách. Nhưng liệu có cách nào để tìm nhiều Mode của giá trị văn bản trong Google Sheets không?
Đôi khi trong một danh sách có thể có hai hoặc nhiều giá trị văn bản xuất hiện phổ biến nhất. Tôi có một công thức Google Sheets mà bạn có thể sử dụng để tìm MODE cũng như MODE.MULT của chuỗi văn bản.
Như bạn có thể biết, chúng ta không thể sử dụng hàm MODE hoặc MODE.SNGL (cả hai đều được sử dụng như nhau) để tìm Mode của các giá trị không số. Tương tự, chúng ta không thể sử dụng MODE.MULT để trả về nhiều Modes của các giá trị không số. Tất cả ba chức năng này đều là chức năng thống kê.
Vậy làm sao để tìm kiếm Mode của giá trị văn bản trong Google Sheets?
Dựa trên danh sách dưới đây ở cột A, Mode của chữ “Italy” là “Italy”, vì nó lặp lại 4 lần, nhiều hơn bất kỳ giá trị văn bản nào khác trong danh sách đó.
Nhưng trong danh sách ở cột D, chuỗi “Italy” và “France” lặp lại mỗi chuỗi 4 lần. Vì vậy, cả hai giá trị văn bản này là các giá trị văn bản xuất hiện phổ biến nhất (nhiều Modes) trong danh sách này.
Công thức để tìm Mode của các giá trị không số trong Google Sheets
Công thức của tôi sẽ hoạt động trong cả hai trường hợp, tức là trả về giá trị đơn và nhiều Modes, mà không cần bất kỳ sửa đổi nào.
Công thức để tìm Mode/Modes của các chuỗi văn bản trong Google Sheets:
=unique(filter(A2:A, regexmatch(match(A2:A,A2:A,0)&"", "^"&textjoin("$|^",true,MODE.MULT(iferror(match(A2:A,A2:A,0))))&"$")))
Công thức này được lập trình để hoạt động trong danh sách ở các cột A (phạm vi A2:A). Nếu danh sách của bạn để tìm kiếm Mode nằm ở bất kỳ cột nào khác, vui lòng sửa đổi các tham chiếu của ô trong công thức tương ứng.
Cách công thức tìm kiếm chuỗi văn bản xuất hiện phổ biến nhất trong Google Sheets?
Tôi biết chỉ cần cung cấp một công thức phức tạp không đủ để bạn hiểu được. Một số bạn có thể muốn biết là công thức này tìm kiếm chuỗi văn bản xuất hiện phổ biến nhất từ một danh sách như thế nào.
Tôi đã sử dụng nhiều chức năng trong công thức của tôi. Tôi sẽ cố gắng giải thích vai trò của từng chức năng được sử dụng một cách đơn giản nhất có thể.
Match để trả về vị trí tương đối của tất cả các chuỗi văn bản trong danh sách
Trong ô B2, nhập công thức Match này.
Công thức 1:
=ArrayFormula(match(A2:A,A2:A,0))
Nó sẽ trả về vị trí tương đối của mỗi mục trong phạm vi, ở đây là A2:A, dựa trên các giá trị duy nhất trong phạm vi đó.
Ví dụ, “Italy”, “France” và “Ireland” là ba giá trị duy nhất trong danh sách ở cột A2:A.
Vị trí tương đối của chuỗi “Italy” là 1 vì nó ở hàng đầu tiên trong phạm vi A2:A. Vị trí tương đối của chuỗi “France” là 5 vì nó ở hàng thứ năm trong phạm vi A2:A.
Hãy nhìn vào vị trí tương đối của chuỗi “Ireland” và bạn sẽ thấy nó là 8.
Vai trò của chức năng MODE.MULT trong việc tìm các giá trị văn bản xuất hiện phổ biến nhất
Sau khi nhập công thức Match ở trên, chúng ta đã có một vài số trong cột B, phải không?
Những gì chúng ta muốn là tìm kiếm Mode của những số đó. Đối với điều đó, bạn có thể sử dụng các hàm MODE, MODE.SNGL hoặc MODE.MULT.
Tôi đề xuất bạn tiếp tục với hàm MODE.MULT vì nó có thể trả về nhiều giá trị Mode.
Sử dụng công thức Match cùng với MODE.MULT trên, nhưng bao gồm hàm IFERROR để loại bỏ giá trị lỗi N/A.
Công thức 2:
=ArrayFormula(MODE.MULT(iferror(match(A2:A,A2:A,0))))
Công thức trên sẽ trả về 1, đó là số xuất hiện phổ biến nhất trong cột B. Bây giờ, chúng ta có thể lọc các hàng chứa giá trị 1 bằng một công thức Lọc.
Lọc để lọc một danh sách dựa trên giá trị khớp xuất hiện phổ biến nhất
Cú pháp Lọc:
FILTER(range, condition1)
Công thức tổng quát để lọc chuỗi văn bản xuất hiện phổ biến nhất sẽ như sau.
FILTER(A2:A, Formula 1=Formula 2)
Vì vậy, công thức sẽ là:
=filter(A2:A, match(A2:A,A2:A,0)=MODE.MULT(iferror(match(A2:A,A2:A,0))))
Tôi đã loại bỏ ArrayFormula từ công thức 1 và công thức 2 vì nó không cần thiết trong Lọc.
Hãy bao gồm hàm Unique để trả về Mode của giá trị văn bản từ cột A.
=unique(filter(A2:A, match(A2:A,A2:A,0)=MODE.MULT(iferror(match(A2:A,A2:A,0)))))
Nếu bạn chỉ muốn trả về một giá trị văn bản xuất hiện phổ biến nhất, thì công thức trên đã đủ cho bạn. Trong danh sách ở cột A ở trên, chỉ có một chuỗi văn bản xuất hiện phổ biến nhất.
Như đã hứa ở đầu, tôi sẽ cung cấp cho bạn một công thức linh hoạt hơn để hoạt động tốt trong trường hợp Mode đơn hoặc nhiều Modes.
Hãy bỏ qua bước trước “Lọc để lọc …” và đây là phần tiếp theo từ bước trước đó “Vai trò của chức năng MODE.MULT …”.
TextJoin để kết hợp các số để sử dụng như một biểu thức Regex
Công thức 3:
Chỉnh sửa công thức 2 như sau. Tôi có sử dụng hàm TextJoin ở đây.
=ArrayFormula( "^"&textjoin("$|^",true,MODE.MULT(iferror(match(A2:A,A2:A,0))))&"$" )
Nó sẽ đặt ký hiệu “|” giữa các giá trị Mode mà công thức 2 trả về như một dấu phân cách. Ngoài ra, một ký hiệu “^” sẽ được chèn trước mỗi giá trị và một ký hiệu “$” sau mỗi giá trị.
Bạn đang sử dụng công thức này trong phạm vi A2:A. Vì vậy, bạn sẽ không thấy bất kỳ sự khác biệt nào vì chỉ có một giá trị Mode trong phạm vi đó.
Để kiểm tra, hãy sử dụng phạm vi D2:D (xem hình ảnh đầu tiên ở trên) thay vì A2:A để xem kết quả dưới đây.
^1$|^5$
Chúng ta có thể sử dụng giá trị này trong công thức RegexMatch cho việc so khớp này hoặc khớp vậy. Điều đó có nghĩa là khớp với số 1 hoặc 5, hoặc cả hai.
Bạn có thể thích: Regexmatch trong Tiêu chí Lọc trong Google Sheets.
Regexmatch giá trị Mode trong các số vị trí tương đối được trả về bởi Match
Đây là bước cuối cùng. Chúng ta sẽ sử dụng bộ lọc theo công thức tổng quát sau đây.
FILTER(A2:A, regexmatch(Formula 1&" ",Formula 3)
Lưu ý: Trong đó, tôi đã thêm một khoảng trắng vào ” ” (ký tự cách). Điều đó là vì đầu ra của Công thức 1 là các vị trí tương đối là các giá trị số. Thêm một khoảng trắng chúng ta có thể định dạng các số thành kiểu văn bản. Tôi đã làm như vậy vì Regexmatch yêu cầu đầu vào là văn bản.
Công thức cuối cùng của tôi dựa trên công thức tổng quát ở trên. Hãy xem lại công thức cuối cùng đó một lần nữa.
=unique(filter(A2:A, regexmatch(match(A2:A,A2:A,0)&"", "^"&textjoin("$|^",true,MODE.MULT(iferror(match(A2:A,A2:A,0))))&"$")))
Logic của công thức:
Công thức trên lọc phạm vi A2:A nếu vị trí tương đối trả về từ Công thức 1 bằng với các Mode trả về từ Công thức 3. Sau đó duy nhất kết quả để loại bỏ các giá trị trùng lặp.
Bạn có thể sử dụng phương pháp này để tìm kiếm Mode của giá trị văn bản trong Google Sheets.
Hãy truy cập Crawlan.com để tìm hiểu thêm về SEO và các công cụ tìm kiếm khác!
