Trên đây là bài viết hướng dẫn cách lọc ra 3 chuỗi phổ biến nhất trong Google Sheets. Trước khi đi vào ví dụ, hãy cho tôi làm rõ một điều quan trọng về kết quả mà công thức sẽ trả về.
Giả sử có năm chuỗi và số lần xuất hiện của chúng như sau:
- Chuỗi D xuất hiện 5 lần
- Chuỗi A xuất hiện 4 lần
- Chuỗi C xuất hiện 4 lần
- Chuỗi E xuất hiện 2 lần
Công thức của tôi sẽ trả về các chuỗi D (xuất hiện 5 lần), A (xuất hiện 4 lần), C (xuất hiện 4 lần) và E (xuất hiện 2 lần).
Nhưng tại sao công thức lại trả về bốn chuỗi khi chúng ta chỉ muốn ba chuỗi phổ biến nhất?
Điều này xảy ra vì có hai chuỗi ở vị trí thứ hai, đó là chuỗi A và chuỗi C, mỗi chuỗi xuất hiện 4 lần.
Và một điều nữa!
Trước đây, chúng ta đã tìm hiểu cách tìm MODE của các giá trị văn bản.
Bài viết đó liên quan đến việc tìm chuỗi (MODE) phổ biến nhất / các chuỗi (MODE.MULT) phổ biến nhất, chứ không phải việc tìm ba chuỗi hoặc n chuỗi phổ biến nhất.
Tôi sẽ giải thích từng bước cụ thể về cách lọc ra 3 chuỗi phổ biến nhất trong Google Sheets.
Hãy lưu ý rằng bạn có thể sử dụng ‘n’ thay vì số 3 mà không cần thay đổi công thức quan trọng nào.
Ví dụ
Trong ví dụ trên, các văn bản mẫu nằm trong ô A2:A và công thức của tôi trong ô C2 sẽ trả về ba giá trị phổ biến nhất cùng với số lần xuất hiện của chúng.
Như bạn có thể thấy, công thức trả về bốn giá trị vì giá trị “A” và “C” lặp lại hai lần, như tôi đã giải thích ở trên.
Hãy chuyển sang phần hướng dẫn.
Lọc ra 3 chuỗi phổ biến nhất trong Google Sheets
Chúng ta sẽ sử dụng một số cột trợ giúp (cột bổ sung) trong các bước ban đầu và sau đó có thể loại bỏ chúng.
Có ba bước chính như sau:
Bước 1 – Sử dụng COUNTIF Array để trả về số lần xuất hiện của các chuỗi
Chuẩn bị một tờ bảng mẫu như hình ảnh ở trên. Hãy nhập các giá trị vào ô A2:A. Để ô khác trống.
Trong tờ bảng đó, hãy chèn công thức ARRAY COUNTIF dưới đây vào ô B2 để trả về số lần xuất hiện của các chuỗi văn bản trong A2:A.
=ArrayFormula(if(len(A2:A),countif(A2:A,A2:A)))Để bạn biết COUNTIF trả về gì, tôi sẽ để hình ảnh tương ứng bên dưới.
Trong công thức trên, bạn có thể muốn biết LEN được sử dụng như thế nào. Bạn có thể tìm hiểu thêm về LEN Function in Google Sheets and Practical Use of It.
COUNTIF trả về số lần xuất hiện của mỗi chuỗi.
Bước 2 – Tìm số lần xuất hiện của N chuỗi phổ biến nhất
Hai công thức Google Sheets tiếp theo xác định số chuỗi phổ biến nhất mà bạn muốn lọc.
Đầu tiên, bạn có thể nhập công thức SORTN, UNIQUE và COUNTIF kết hợp dưới đây vào ô D2.
=sortn(unique(countif(A2:A,A2:A)),3,0,1,0)Lý giải công thức
COUNTIF được nhóm lại trong UNIQUE để trả về các giá trị duy nhất từ các giá trị trong cột B.
Trong công thức trên, thay vì tham chiếu đến kết quả COUNTIF trong cột B2:B, tôi đã sử dụng cùng một công thức trong UNIQUE chính nó.
Con số 3 trong SORTN nghĩa là trả về 3 giá trị sau khi sắp xếp cột 1 (chỉ có một cột) theo thứ tự giảm dần.
Nếu bạn muốn lọc ra 5 chuỗi phổ biến nhất trong Google Sheets, hãy thay số 3 bằng số 5 trong công thức.
Giải thích thêm về SORTN
Bây giờ tôi sẽ nói về con số 0 xuất hiện sau số 3. Đó là một trong bốn display_ties_mode của SORTN. Nó xác định điều gì sẽ xảy ra trong trường hợp có các bản sao.
Nếu bạn muốn, bạn có thể đọc thêm về tie mode tại đây – SORTN Tie Modes in Google Sheets – The Four Tiebreakers.
Con số 1 tiếp theo chỉ định cột sắp xếp, và con số 0 nghĩa là sắp xếp cột theo thứ tự giảm dần (Z-A).
Bước 2.1 – Viết biểu thức chính quy để lọc ra 3 chuỗi phổ biến nhất
Bây giờ tôi sẽ đi vào logic của công thức cuối cùng mà bạn sẽ nhận được sau một bước nữa. Vì vậy, hãy chú ý đặc biệt.
Logic
Chúng ta sẽ lọc các chuỗi trong A2:A dựa trên số lần xuất hiện của chúng. Nhưng không chỉ dựa trên số lần xuất hiện, mà dựa trên n số lần xuất hiện cao nhất trả về bởi công thức bước 2.
Vì vậy, chúng ta sẽ sử dụng chức năng FILTER để lọc A2:A nếu các giá trị trong B2:B (mảng COUNTIF) bằng với kết quả Bước 2.
Để chỉ định B2:B bằng với kết quả Bước 2 trong FILTER, chúng ta có thể sử dụng REGEXMATCH trong FILTER như sau.
Công thức chung
=FILTER(A2:A,regexmatch(B2:B&"",step_2.1_formula))Trước tiên, hãy hiểu công thức REGEXMATCH trong Công thức chung trên.
REGEXMATCH là một trong các hàm Văn bản trong Google Sheets. Vì B2:B chứa các số, tôi đã chuyển đổi chúng thành văn bản bằng cách thêm ký tự trống. Tôi nghĩ là B2:B được thay thế bằng B2:B&””.
Trong trường hợp khác, REGEXMATCH sẽ trả về lỗi #VALUE! nêu rõ điều gì đó tương tự như sau.
“Function REGEXMATCH parameter 1 expects text values. But ‘5’ is a number and cannot be coerced to a text.”
Vậy thì step_2.1_formula là gì?
Để khớp với nhiều số, trong REGEXMATCH, chúng ta nên kết hợp các số theo một cách cụ thể như sau. Đó là công thức bước 2.1 (một phiên bản sửa đổi của công thức D2 [Bước 2]).
="^"&textjoin("$|^",true,sortn(unique(countif(A2:A,A2:A)),3,1,1,0))&"$"Điều trên là theo công thức chung dưới đây.
="^"&textjoin("$|^",true,step_2_formula)&"$"
Đến lúc lọc ra 3 chuỗi phổ biến nhất bằng số lần trên.
Bước 3 – Lọc ra 3 chuỗi phổ biến nhất sử dụng số lần xuất hiện (Kết quả Bước 2.1)
Một lần nữa, hãy tập trung vào công thức chung sau để lọc ra 3 chuỗi phổ biến nhất trong Google Sheets.
=FILTER({A2:A,B2:B},regexmatch(B2:B&"",step_2.1_formula))Tôi đã giải thích tất cả các thông số cần thiết để sử dụng trong bộ lọc. Vì vậy, tôi sẽ đi thẳng vào công thức (không phải công thức cuối cùng).
Sao chép-dán công thức chung trên vào ô C2. Cắt công thức D2 (step_2.1_formula) và thay thế step_2.1_formula trong công thức chung C2 bằng công thức đó.
Công thức
=filter({A2:A,B2:B},regexmatch(B2:B&"",step_2.1_formula))Đến lúc loại bỏ các cột trợ giúp. Để làm điều đó, hãy thay thế B2:B (xuất hiện hai lần trong công thức) bằng công thức trong B2.
=filter({A2:A,if(len(A2:A),countif(A2:A,A2:A))},regexmatch(if(len(A2:A),countif(A2:A,A2:A))&"",step 2.1_formula))Cuối cùng, hãy bọc công thức này bằng UNIQUE để loại bỏ các bản sao (nhiều lần xuất hiện của các hàng).
=unique(filter({A2:A,if(len(A2:A),countif(A2:A,A2:A))},regexmatch(if(len(A2:A),countif(A2:A,A2:A))&"",step_2.1_formula)))Sau đó, sắp xếp cột thứ hai theo thứ tự giảm dần.
=sort(unique(filter({A2:A,if(len(A2:A),countif(A2:A,A2:A))},regexmatch(if(len(A2:A),countif(A2:A,A2:A))&"",step_2.1_formula))),2,0)Đó là tất cả. Cảm ơn bạn đã ở lại và thưởng thức!
- Sample_Sheet_7121
