Trong bài này, chúng ta sẽ đi xây dựng công thức trong Google Sheets để có thể kiếm tìm được trong đa dạng cột, những kết quả tìm kiếm sẽ được Đánh giá khớp bao lăm phần trăm sở hữu nhu cầu tìm kiếm của người sử dụng. Bạn có thể đọc bài viết và khiến theo từng bước hoặc theo dõi video ở cuối bài.
Dữ liệu đầu vào của thí dụ
Dữ liệu đầu vào của tỉ dụ là tên các khóa học của Thanh, và hai 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 kiếm tìm chúng ta sẽ viết.
vun đắp tính toán % độ khớp
Nối những cột cần tìm kiếm
Ý tưởng của chúng ta ở đây sẽ là: nhập 1 số trong khoảng vào ô tìm kiếm, và công thức sẽ cần Đánh giá xem bao nhiêu từ trong ô kiếm tìm ấy mang 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 những cột với nhau để tạo thành một cột phụ
=JOIN(" ",A2:C2)
Tách chuỗi kiếm tìm ra thành từng từ bởi dấu cách
Ở đây, thí dụ chúng ta sẽ sở hữu một chuỗi tìm kiếm là sap căn bản vba , chuỗi này gồm với 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, " ")
kiếm tìm từng trong khoảng tách ra trong chuỗi kiếm tì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 để kiếm tìm từng trong khoảng này với trong "cột phụ" – lúc chúng ta nối 3 cột A, B, C vào hay ko bằng công thức như sau:
=MATCH("*" & split(B2," ") & "*",join(" ", A2:C2),0)
giả dụ chúng ta 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 có 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ì sở hữu các từ trong chuỗi kiếm tìm sẽ không được tậu 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 thức tiêu 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 lúc đã sở hữu được một dãy kết quả trong khoảng công thức trên, chúng ta sẽ tưởng tượng việc làm thế nào để tính toán được 1 tỷ lệ giữa số trong khoảng trong chuỗi kiếm tìm được mua thấy trong cột phụ với tổng số từ đang được nhập vào ô kiếm tìm. Để tính số trong khoảng được nhập vào ô kiếm tìm, chúng ta có thể tiêu dùng công thức như sau:
=COUNTA(SPLIT(B2," ")Tổng số trong khoảng đã khớp với 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ố trong khoảng được nhập vào ô kiếm tì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ả kiếm tìm
Sau lúc chúng ta đã có mộtcột phụ là tỷ lệ khớp giữa chuỗi kiếm tìm và kết quả kiếm tìm, chúng ta sẽ thiết lập công thức để mang thể trả về kết quả này khi gõ vào ô kiếm tì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 cho theo từng bước chi tiết, bạn hãy theo dõi video sau đây:
0 nhận xét:
Đăng nhận xét