Bạn có biết rằng chúng ta có thể tìm kiếm bằng XMATCH trên các dòng hiển thị (dữ liệu đã lọc) mà không cần sử dụng một phạm vi trợ giúp trong Google Sheets? Công thức này sẽ loại bỏ các dòng bị ẩn.
Phương pháp này chỉ áp dụng cho việc tìm kiếm các từ khóa trong một cột và trả về vị trí tương đối của chúng nếu được tìm thấy hoặc giá trị lỗi #N/A.
Vị trí tương đối có thể không quan trọng trong hầu hết các trường hợp. Thông thường, chúng ta sử dụng kết quả đầu ra để biết liệu một giá trị có hiện diện trong danh sách hay không.
Nhưng nếu bạn quan tâm đến vị trí tương đối, bạn nên biết điều gì xảy ra khi bạn ẩn dòng.
Trước tiên, tôi sẽ giải thích vấn đề này, sau đó chúng ta sẽ đi vào cách tìm dòng hiển thị trong Google Sheets.
Dòng ẩn và vị trí tương đối
Dưới đây, bạn có thể tìm thấy một số ngày trong phạm vi ô B2:B11 và các vị trí tương đối của chúng đã được nhập vào A2:A11 để tham khảo.
Vị trí tương đối của ngày 24/12/2022 trong B2:B11 (lookup_range) là 6 nếu chúng ta sử dụng công thức để tìm kiếm từ khóa từ mục nhập đầu tiên đến mục nhập cuối cùng.
Công thức #1 (E4):
=xmatch(E3,B2:B11,0,1)Nếu chúng ta ẩn dòng số 7, kết quả của XMATCH sẽ giống nhau vì nó không có tùy chọn bỏ qua các dòng bị ẩn.
Làm thế nào để lấy vị trí tương đối của từ khóa tìm kiếm trong dòng hiển thị tiếp theo?
Đó chính là ý nghĩa của “XMATCH dòng hiển thị” trong Google Sheets.
Hãy ẩn dòng số 7.
Công thức sẽ so khớp từ khóa tìm kiếm trong dòng hiển thị số 9 và trả về vị trí tương đối của nó, đó là 8 nếu bạn đếm tất cả các dòng và 7 nếu bạn chỉ đếm các dòng hiển thị.
Chúng tôi có giải pháp cho cả hai trường hợp.
1. XMATCH Dòng Hiển thị và Đếm Tất cả Các Dòng
Tương tự như XLOOKUP Dữ liệu Hiển thị (Lọc), chúng ta cần sử dụng BYROW, một trong các Hàm Trợ giúp LAMBDA, và SUBTOTAL để thêm chức năng này vào XMATCH.
Chúng tôi sẽ sử dụng kết quả của công thức trợ giúp sau để tạo một phạm vi tra cứu ảo trong XMATCH để trả về vị trí tương đối của các dòng hiển thị.
Công thức trợ giúp:
=BYROW(B2:B11,LAMBDA(range,SUBTOTAL(103,range)))Nó sẽ trả về 1 trong các ô hiển thị và 0 trong các ô bị ẩn.
Lưu ý: Nếu bạn muốn tìm hiểu công thức này, hãy kiểm tra hướng dẫn XLOOKUP được đề cập ở trên.
Làm thế nào chúng ta sử dụng công thức trợ giúp này kết hợp với XMATCH?
Chúng ta sẽ thay thế giá trị ngày trong B2:B11 bằng trống nếu công thức trợ giúp trả về 0, nếu không, chúng ta sẽ giữ nguyên B2:B11 và sử dụng nó như là phạm vi tra cứu trong XMATCH.
Công thức chung (Tra cứu ảo Lookup_Range):
if(helper_formula_result=0,"",B2:B11)Công thức #2 (E5):
=ArrayFormula(xmatch(E3,if(BYROW(B2:B11,LAMBDA(range,SUBTOTAL(103,range)))=0,"",B2:B11),0,1))Chúng ta phải sử dụng hàm ARRAYFORMULA vì bài kiểm tra logic của IF không được mảng.
Cú pháp:
XMATCH(search_key, lookup_range, [match_mode], [search_mode])- search_key: E3
- lookup_range: if(BYROW(B2:B11,LAMBDA(range,SUBTOTAL(103,range)))=0,””,B2:B11)
- match_mode: 0
- search_mode: 1
2. XMATCH Dòng Hiển thị và Đếm Các Dòng Hiển thị
Ở đây, chúng ta phải sửa đổi phạm vi tra cứu bằng công thức trợ giúp BYROW.
Thay vì sử dụng bài kiểm tra logic IF, chúng ta sẽ sử dụng hàm FILTER.
Chúng ta sẽ lọc các dòng hiển thị (kết quả công thức trợ giúp>0) và sử dụng nó như là phạm vi tra cứu.
Điều đó sẽ giúp XMATCH so khớp từ khóa tìm kiếm chỉ trên các dòng hiển thị và trả về vị trí tương đối tương ứng.
Ở đây, chúng ta không cần sử dụng hàm ARRAYFORMULA, vì FILTER có thể xử lý mảng.
Công thức #3 (E6):
=xmatch(E3,filter(B2:B11,BYROW(B2:B11,LAMBDA(range,SUBTOTAL(103,range)))>0),0,1)Vị trí tương đối khi tìm kiếm từ mục nhập cuối cùng đến mục nhập đầu tiên
Khi chúng ta có các bản ghi mới nhất ở cuối bảng, trong một số trường hợp, chúng ta có thể cần tìm kiếm từ mục nhập cuối cùng đến mục nhập đầu tiên.
Chúng ta phải thực hiện những thay đổi nào cho các công thức trên?
Các công thức trên không gặp vấn đề gì khi xử lý điều đó.
Thay thế search_mode, đối số cuối cùng trong hàm, 1 bằng -1.
Giữ cho dòng số 7 ẩn và sửa đổi các công thức 1 (E4), 2 (E5) và 3 (E6) như đã đề cập ở trên.
Công thức đầu tiên sẽ trả về 8 và hai công thức XMATCH dòng hiển thị khác sẽ trả về 7 và 8 tương ứng.
Hãy thử nghiệm nó trong Bảng của bạn để hiểu rõ hơn.
Đó là tất cả. Cảm ơn bạn đã đọc bài viết này. Chúc bạn có những trải nghiệm thú vị!
Muốn biết thêm về công cụ hữu ích cho SEO, hãy truy cập Crawlan.com.
