Thẻ: Google Sheets nâng cao

Trong bài này, chúng ta sẽ đi xây dựng công thức trong Google Sheets để có thể tìm kiếm được trong nhiều cột, các kết quả tìm kiếm sẽ được đánh giá khớp bao nhiêu phần trăm với nhu cầu tìm kiếm của người sử dụng. Bạn có thể đọc bài viết và làm theo từng bước hoặc theo dõi video ở cuối bài.

Dữ liệu đầu vào của ví dụ

Dữ liệu đầu vào của ví dụ là tên các khóa học của Thanh, và 2 cột dữ liệu CourseId và Assistant chúng ta điền thêm vào để minh họa cho chức năng tìm kiếm chúng ta sẽ viết.

Xây dựng tính toán phần trăm độ khớp

Nối các cột cần tìm kiếm

Ý tưởng của chúng ta ở đây sẽ là: nhập một số từ vào ô tìm kiếm, và công thức sẽ cần đánh giá xem bao nhiêu từ trong ô tìm kiếm đó có thể xuất hiện ở bất cứ dòng dữ liệu nào của chúng ta. Để tìm kiếm được ở nhiều cột, chúng ta sẽ đi nối các cột với nhau để tạo thành một cột phụ

=JOIN(" ",A2:C2)

Tách chuỗi tìm kiếm ra thành từng từ bởi dấu cách

Ở đây, ví dụ chúng ta sẽ có một chuỗi tìm kiếm là sap cơ bản vba , chuỗi này gồm có 4 từ, chúng ta sẽ sử dụng hàm Split để có thể tách 4 từ này ra như sau với giá sử cụm từ này được lưu trong ô B2:

=SPLIT(B2, " ")

Tìm kiếm từng từ tách ra trong chuỗi tìm kiếm

Sau khi tách được các từ trong chuỗi tìm kiếm ra, chúng ta sẽ kết hợp công thức như sau để tìm kiếm từng từ này có trong “cột phụ” – khi chúng ta nối 3 cột A, B, C vào hay không bằng công thức như sau:

=MATCH("*" & split(B2," ") & "*",join(" ", A2:C2),0)

Nếu chúng ta sử dụng công thức này, thì kết quả chỉ được ghi vào một ô, vậy nên, chúng ta sẽ bao ra ngoài công thức trên với hàm ArrayFormula:

=ArrayFormula(MATCH("*" & split(B2," ") & "*",join(" ", A2:C2),0))

Trong công thức trên, sẽ xuất hiện ở vùng kết quả những lỗi #NA bởi vì có những từ trong chuỗi tìm kiếm sẽ không được tìm thấy trong vùng A2:C2, chúng ta sẽ chuyển những lỗi này thành số 0 bằng cách sử dụng hàm IFERROR như sau:

=ArrayFormula(IFERROR(MATCH("*" & split(B2," ") & "*",join(" ", A2:C2),0),0))

Tính toán tỷ lệ khớp

Sau khi đã có được một dãy kết quả từ công thức trên, chúng ta sẽ nghĩ đến việc làm thế nào để tính toán được một tỷ lệ giữa số từ trong chuỗi tìm kiếm được tìm thấy trong cột phụ với tổng số từ đang được nhập vào ô tìm kiếm. Để tính số từ được nhập vào ô tìm kiếm, chúng ta có thể sử dụng công thức như sau:

=COUNTA(SPLIT(B2," ")

Tổng số từ đã khớp có thể được tính bằng công thức:

=ArrayFormula(SUM(IFERROR(MATCH("*" & split(B2," ") & "*",join(" ", A2:C2),0),0)))

Chúng ta tính toán ra số từ được nhập vào ô tìm kiếm như sau:

=COUNTA(SPLIT(B2," ")

Và sau đó, chúng ta sẽ tính ra tỷ lệ khớp như sau:

=ArrayFormula(
   SUM(
     IFERROR(
       MATCH("*" & split(B2," ") & "*",join(" ", A2:C2),0)
       ,0))
   ) / counta(split(B2," ")
)

Thiết lập công thức FILTER để trả về kết quả tìm kiếm

Sau khi chúng ta đã có mộtcột phụ là tỷ lệ khớp giữa chuỗi tìm kiếm và kết quả tìm kiếm, chúng ta sẽ thiết lập công thức để có thể trả về kết quả này khi gõ vào ô tìm kiếm như sau:

=FILTER(Sheet1!A1:D,Sheet1!D:D>0,Sheet1!D:D>B1)

Trong công thức trên: Dữ liệu nguồn ở Sheet1, cột A, B, C; cột D là cột chứa tỷ lệ khớp.

Để có thể làm theo từng bước chi tiết, bạn hãy theo dõi video sau đây: