Google Sheets có menu Dữ liệu Tạo bộ lọc cũng như lệnh Lọc xem hỗ trợ các công thức tùy chỉnh. Để lọc tối đa N giá trị trong Google Sheets, bạn có thể sử dụng menu bộ lọc này và trong đó là một công thức tùy chỉnh.
Ví dụ, tôi muốn lọc tối đa 10 giá trị trong một cột. Ở đây con số 10 là N và bạn cũng có thể điều khiển từ một ô đầu vào.
Ý nghĩa là bạn có thể nhập #10 trong một ô. Đơn giản chỉ cần thay đổi con số đó thành 5 và làm mới bộ lọc để lọc tối đa 5 giá trị. Đầu tiên, hãy cho tôi chỉ cho bạn thấy cách lọc tối đa N động này hoạt động.
Tôi có 15 tên học sinh trong ô B2: B16 và điểm của họ trong ô C2: C16. Để giúp bạn dễ hiểu kết quả bộ lọc, tôi đã đặt các số tuần tự từ 36-50 là điểm số của họ. Ô điều khiển, nghĩa là giá trị thứ N, nằm trong ô E1.
Bạn có thể thay đổi số N trong ô E1 để điều khiển số hàng được lọc (số hàng tối đa). Nhưng khi bạn thay đổi, bạn cũng cần điều chỉnh cột bộ lọc và nhấp vào OK. Sau đó, kết quả sẽ được làm mới.
Công thức tùy chỉnh Filter By Condition để lọc tối đa N giá trị trong Google Sheets
Để lọc, theo ví dụ trên, tôi đã sử dụng công thức tùy chỉnh sau đây trong menu lọc (Ngày > Tạo bộ lọc > Lọc theo điều kiện > Công thức tùy chỉnh).
=REGEXMATCH($B$2:$B,textjoin("|",true,ArrayFormula("^"&array_constrain(sortn($B$2:$C,$E$1,0,2,0),9^9,1)&"$")))
Chúng ta muốn lọc số điểm cao nhất N đúng không? Vậy tại sao tôi đã áp dụng công thức tùy chỉnh này trong cột tên (ô B1) thay vì cột điểm?
Để hiểu điều này, bạn phải biết công thức trên hoạt động như thế nào. Dưới đây là thông tin chi tiết từng bước.
Công thức tùy chỉnh giải thích cách lọc những con số tối đa N – Bước giải thích công thức
Tôi đã bắt đầu viết mã từ công thức SORTN dưới đây.
Bước 1: Trích xuất động N Điểm và Tên
=sortn($B$2:$C,$E$1,0,2,0)
Công thức SORTN trên đây trích xuất động N điểm. Để lọc ra 10 số (điểm) hàng đầu, hãy thay đổi giá trị trong ô E1 thành 10. Tương tự, nhập số 2 vào ô E1 sẽ lọc ra 2 số hàng đầu.
Để biết thêm về SORTN và các đối số ‘phức tạp’ của nó, bạn có thể xem hai hướng dẫn sau.
- Cách sử dụng chức năng SORTN trong Google Sheets để trích xuất N hàng đã được sắp xếp.
- Các chế độ Tiebreaker của SORTN trong Google Sheets – Tứ quan xử lý.
Một lựa chọn khác để trích xuất số 3, 5, 10 hoặc số N lớn nhất trong Google Sheets là sử dụng hàm LARGE trong hàm Lọc như tiêu chí.
=filter(B2:C,C2:C>=large(C2:C,N))
Thay N trong công thức bằng số bạn muốn điều khiển số hàng để trích xuất một cách động. Nhưng trong bài hướng dẫn này, tôi đang sử dụng công thức SORTN.
Bước 2: Hạn chế số cột trong Google Sheets
Như bạn có thể thấy, công thức SORTN trên trả về một đầu ra hai cột.
Chúng tôi muốn lọc tối đa N giá trị trong Google Sheets từ hai cột dữ liệu trên. Vì vậy bây giờ chúng ta có thể so khớp bất kỳ tên nào hoặc điểm nào vì chúng ta đã có tên cũng như tên của những người đạt điểm cao nhất N.
Tôi sẽ so khớp tên trong cột B của menu Lọc. Vì vậy tôi muốn loại bỏ cột 2 khỏi đầu ra SORTN này. Hàm Array_Constrain sẽ giúp làm điều đó.
=array_constrain(sortn($B$2:$C,$E$1,0,2,0),9^9,1)
Tôi chọn cột tên vì trong hàm Regexmatch, mà tôi sẽ sử dụng sau này, tiêu chí văn bản sẽ chỉ hoạt động.
Nếu chúng ta sử dụng tiêu chí số trong Regex, chúng ta phải chuyển đổi số đó thành văn bản bằng cách sử dụng hàm To_Text hoặc thêm khoảng trắng vào số đó. Tôi không muốn bước thêm đó.
Bước 3: Biểu thức Regex để lọc tối đa N giá trị trong Google Sheets
Nếu giá trị E1, giá trị N, là 3, công thức dưới đây sẽ trả về ^ Học sinh 15 $|^ Học sinh 14 $|^ Học sinh 13 $ như biểu thức chính quy.
=textjoin("|",true,ArrayFormula("^"&array_constrain(sortn($B$2:$C,$E$1,0,2,0),9^9,1)&"$"))
Dấu | đã được sử dụng để thực hiện nhiều so khớp (đại diện cho OR). Dấu ^ và $ đã được sử dụng cho phù hợp chính xác của từ khóa.
Phải đọc: Loại bỏ các từ khóa khớp trong Google Sheets – Khớp một phần hoặc hoàn toàn.
Nếu bạn không sử dụng các ký hiệu phù hợp chính xác đó, Regexmatch sẽ không phân biệt “Học sinh 1” và “Học sinh 15” vì cả hai đều khớp một phần.
Hướng dẫn Regexmatch của tôi sẽ cho bạn một số ý tưởng về biểu thức này – Cách sử dụng chức năng REGEXMATCH trong Google Sheets.
Tôi đã sử dụng biểu thức chính quy trên trong Regexmatch trong công thức cuối cùng của mình. Bằng cách này, bạn có thể lọc tối đa N giá trị trong Google Sheets bằng lệnh menu bộ lọc. Thưởng thức!
Đọc thêm:
- Cách lọc theo tháng bằng menu Lọc trong Google Sheets.
- Lọc các giá trị duy nhất sử dụng menu Lọc trong Google Sheets.
- Lọc theo khoảng ngày bằng menu Lọc trong Google Sheets.
