Bạn có thể lọc các hàng dựa trên số dòng trong Google Sheets? Có! Bạn có thể sử dụng số dòng làm tiêu chí lọc trong Google Sheets.
Để lọc các hàng dựa trên số dòng đã cho, bạn có thể sử dụng hàm Filter. Điều đó có nghĩa là, trong bộ lọc, các tiêu chí/điều kiện sẽ là số dòng.
Thực tế, công thức sử dụng số dòng là tiêu chí trong hàm Filter rất dễ viết. Trước khi đi vào đó, chúng ta phải hiểu về việc sử dụng lọc như thế nào trong thực tế.
Sử dụng số dòng là tiêu chí lọc trong thực tế sử dụng Google Sheets
Cho mục đích hướng dẫn của chúng ta, tôi sẽ nhập một số dữ liệu vào một trong các tệp Google Sheets của mình từ trang nguồn dưới đây trên Wikipedia. Đó là chỉ số hạnh phúc của các quốc gia trên thế giới – Báo cáo Về Hạnh phúc Thế giới – Wiki.
Bằng cách sử dụng công thức IMPORTHTML sau đây, bạn có thể có được dữ liệu chỉ số hạnh phúc đó trong Google Sheets.
=IMPORTHTML(B1,"table",B2)
Tôi đã nhập công thức này vào ô A3. Ô B1 chứa URL của trang Wiki trên và ô B2 chứa số bảng.
Công thức này sẽ nhập toàn bộ bảng chứa khoảng 150 hàng vào tệp Google Sheets của tôi. Đây là công thức và một số hàng đầu tiên.
Bây giờ chúng ta có một tập dữ liệu mẫu để kiểm tra loại lọc đã đề cập.
Bạn có quen với hàm Match trong Sheets không? Nếu chưa, hãy xem hướng dẫn của tôi – How to Use Match Function in Google Sheets.
Hàm Match trong Sheets có thể tìm kiếm một khóa và trả về vị trí tương đối của khóa/mục đó trong tập dữ liệu. Xem ví dụ dưới đây.
Cho rằng bạn muốn tìm vị trí tương đối của giá trị “Australia” trong phạm vi B4:B trong dữ liệu mẫu trên. Đối với điều đó, bạn có thể sử dụng công thức Match này.
=match("Australia",B4:B,0)
Công thức này sẽ trả về số 10, đó là vị trí tương đối của khóa “Australia” trong phạm vi B4:B.
Nếu bạn bắt đầu đếm từ B4, bạn có thể tìm thấy khóa “Australia” ở vị trí thứ 10.
Thường chúng ta sử dụng vị trí tương đối như vậy trong chức năng Index để thay đổi hàng. Bạn có thể kiểm tra hướng dẫn Index-Match của tôi để sử dụng nó. Dưới đây bạn sẽ có một ví dụ.
Thay đổi vị trí tương đối bằng Index trong Google Sheets
Hãy xem hình ảnh ở trên về mảng/phạm vi được sử dụng trong công thức Index-match này.
=index(A4:I,match("Australia",B4:B,0),0)
Công thức này tương đương với;
=index(A4:I,10,0)
Điều này sẽ trả về hàng thứ 10 từ phạm vi được cung cấp. Nhưng nếu có nhiều khóa trong Match và nhiều vị trí tương đối?
Thay đổi vị trí không liên tục trong Google Sheets
Sau đó, Index sẽ không tiện lợi vì hàm không thể thay đổi vị trí tương đối không liên tục.
Trong trường hợp như vậy, bạn có thể chuyển đổi các vị trí tương đối của các khóa thành số dòng tương ứng. Sau đó, sử dụng các số dòng đó để lọc các hàng theo số dòng.
Đó là mục đích khi sử dụng số dòng làm tiêu chí lọc trong thực tế trong Google Sheets. Tôi sẽ giải thích cách sử dụng nó ở đây trong bài hướng dẫn này.
Chuyển đổi vị trí tương đối thành số dòng trong Google Sheets
Vị trí tương đối thành số dòng – Một khóa tìm kiếm
Đầu tiên, hãy kiểm tra phạm vi được sử dụng trong công thức Match ở trên. Đó là B4:B. Điều đó có nghĩa là phạm vi bắt đầu từ hàng thứ tư. Vì vậy, bạn có thể thêm #3 vào kết quả của Match, là 10, để nhận số dòng thực tế từ vị trí tương đối đó.
Thay vì thêm 3, hãy làm nó trở nên linh hoạt. Đây là công thức chuyển đổi vị trí tương đối thành số dòng tương ứng trong Google Sheets.
Công thức #1:
=match("Australia",B4:B,0)+row(B3)
Công thức này sẽ trả về số dòng 13. Công thức Row trả về #3.
Còn với nhiều khóa trong Match?
Vị trí tương đối thành số dòng – Nhiều khóa tìm kiếm
Hãy xem công thức Match sau đây sử dụng hai khóa tìm kiếm.
Công thức #2:
=ArrayFormula(match({"Australia","Germany"},B4:B,0)+row(B3))
Công thức này sẽ trả về số dòng 13 và 18. Khóa tìm kiếm “Australia” ở vị trí thứ 13 và khóa “Germany” ở vị trí thứ 18.
Bạn đã biết cách chuyển đổi vị trí tương đối thành số dòng tương ứng. Hãy cho tôi chỉ cho bạn cách sử dụng các số dòng đó để trích xuất các hàng đó trong Google Sheets.
Cách sử dụng số dòng làm tiêu chí lọc trong Google Sheets
Đầu tiên, cho phép tôi giải thích cách sử dụng số dòng làm tiêu chí lọc trong Google Sheets. Sau đó, chúng ta có thể thay thế các số dòng đó bằng công thức Match. Điều đó có nghĩa là chúng ta sẽ sử dụng số dòng linh hoạt trong quá trình lọc.
Số dòng là tiêu chí trong hàm lọc Google Sheets
Tôi có công thức Lọc sau đây trong ô J4 sử dụng số 13 (số dòng) là tiêu chí.
=filter(A3:I,row(A3:A)=13)
Công thức trích xuất hàng đã được làm nổi bật, đó là hàng thứ 13.
Để đưa số dòng linh hoạt thay cho row(A3:A)=13 trong công thức, hãy thay bằng Công thức Match # 1 ở trên. Đây là công thức đó.
=filter(A3:I,row(A3:A)=match("Australia",B4:B,0)+row(B3))
Số dòng là tiêu chí trong hàm lọc trong Google Sheets
Làm thế nào để sử dụng nhiều số dòng làm tiêu chí trong Lọc trong Google Sheets?
Trong một bài viết trước đó, tôi đã giải thích cách sử dụng nhiều điều kiện trong cùng một cột (điều kiện HOẶC) trong Lọc – How to Use AND, OR with Google Sheets Filter Function.
Công thức sau đây tuân thủ các gợi ý chi tiết trong hướng dẫn đó.
=filter(A3:I,(row(A3:A)=13)+(row(A3:A)=18))
Công thức Google Sheets này sẽ lọc các hàng 13 và 18. Làm thế nào để nó trở nên linh hoạt bằng cách thay thế hai công thức Row bằng Công thức Match # 2 của chúng ta?
Điều đó hơi phức tạp và hơi phức tạp một chút. Nhưng đừng lo lắng! Tôi sẽ cố gắng giải thích điều đó.
Đây là hướng dẫn từng bước.
Bước 1:
Chúng ta phải đầu tiên chuyển đổi Công thức # 2 cho kết quả như dưới đây.
^13$|^18$
Làm thế nào? Để nhận được điều này, hãy sử dụng hàm Textjoin với Công thức Match # 2.
=ArrayFormula(textjoin("|",true,"^"&(match({"Australia","Germany"},B4:B,0)+row(B3)&"$")))
Bước 2:
Sử dụng công thức trên làm regular_expression trong Regexmatch. Tôi có nghĩa là thay thế “^13$|^18$” trong công thức sau đây bằng công thức vừa rồi.
=ArrayFormula(regexmatch(row(A4:A)&"","^13$|^18$"))
Vì vậy công thức sẽ là;
=ArrayFormula(regexmatch(row(A4:A)&"",textjoin("|",true,"^"&(match({"Australia","Germany"},B4:B,0)+row(B3)&"$"))))
Để kiểm tra nó, chỉ cần nhập nó vào ô J4. Bạn có thể thấy rằng công thức trả về TRUE đối với số dòng là 13 và 18. Trong tất cả các hàng khác, nó trả về FALSE.
Bây giờ chúng ta muốn có một công thức như thế này để trả về các hàng chứa TRUE.
Công thức chung:
=filter(A4:I,regexmatch_formula=TRUE)
Thay đổi công thức regexmatch_formula bằng công thức trên và công thức cuối cùng sẽ như dưới đây.
=filter(A4:I,regexmatch(row(A4:A)&"",textjoin("|",true,"^"&(match({"Australia","Germany"},B4:B,0)+row(B3)&"$")))=TRUE)
Theo cách này, bạn có thể sử dụng số dòng làm tiêu chí lọc trong Google Sheets.
Đọc thêm: Filter Out Matching Keywords in Google Sheets – Partial or Full Match.
