我正在尝试制作一个文档搜索引擎,在搜索文件时使用关键字和文档名称。。在excel电子表格中。
它的工作原理是通过空格分解搜索词和文件名,以及通过逗号分解关键字。然后,它为每个关键字匹配每个搜索项,并为每个匹配项添加一个计数器。关键字匹配越多=计数器越高,然后我通过这个计数器对表进行排序,这样我就可以在顶部获得最佳匹配结果。
搜索功能已经运行良好,但当涉及到单数和复数术语时,它找不到它。
假设doc1的关键字是Templates。在搜索字段中搜索单词Template时,它无法匹配。我使用了一个LIKE语句,并尝试在前面和后面添加一个通配符,但仍然不起作用。
奇怪的是,恰恰相反。如果我搜索一个复数术语,并且关键字是单数,它就会得到匹配。我不知道为什么,它只是起作用,这是一件好事。
Sub Searchresult()
Dim x As Long, y As Long, count As Long
Dim search() As String, keyword() As String, names() As String
Dim result As String
Dim tbl As ListObject, sortcol As Range, lrow As Long
With Worksheets("Sheet3") 'Prep for placing results in table.
Set tbl = .ListObjects("tblSearch")
Set sortcol = .Range("tblSearch[sort]")
tbl.DataBodyRange.ClearContents
End With
With Worksheets("Sheet2")
search = Split(.Range("F1").Value, " ") 'split search terms via spaces
For x = 2 To 1000 Step 1
count = 0
lrow = Worksheets("Sheet3").Cells(Rows.count, 1).End(xlUp).Row + 1
keyword() = Split(.Range("d" & x), ",") ' split keywords via comma
names() = Split(Replace(Replace(Replace(Replace(.Range("c" & x), "-", ""), "(", ""), ")", ""), "'", ""), " ") 'splits names via spaces, deleting any unwanted characters
For i = LBound(keyword) To UBound(keyword)
For j = LBound(search) To UBound(search)
If "*" & UCase(search(j)) & "*" Like "*" & UCase(keyword(i)) & "*" Then 'compare search term and keyword
Worksheets("Sheet3").Range("A" & lrow, "B" & lrow).Value = .Range("A" & x, "B" & x).Value
count = count + 1
Worksheets("Sheet3").Range("C" & lrow).Value = count
End If
Next
Next
For k = LBound(names) To UBound(names)
For l = LBound(search) To UBound(search)
If "*" & UCase(search(l)) & "*" Like "*" & UCase(names(k)) & "*" And Len(names(k)) > 2 Then 'compare search term and document name
Worksheets("Sheet3").Range("A" & lrow, "B" & lrow).Value = .Range("A" & x, "B" & x).Value
count = count + 1
Worksheets("Sheet3").Range("C" & lrow).Value = count
End If
Next
Next
Next
End With
With tbl.Sort 'sort everything based on count to get best result
.SortFields.Clear
.SortFields.Add Key:=sortcol, SortOn:=xlSortOnValues, Order:=xlDescending
.Header = xlYes
.Apply
End With
End Sub
如何确保LIKE语句能够匹配单词的单数/复数项?它不必太复杂,就可以找到单数/复数(例如:"副本/副本"(时会发生变化的单词,但让它匹配只添加一个"s"的单词就可以了。
编辑:多亏了Brian的解释,通过添加一个OR语句来修复代码,该语句翻转Like语句,以便再次比较术语。它现在可以匹配,即使它是单数/复数。
If UCase(search(j)) Like "*" & UCase(keyword(i)) & "*" Or UCase(keyword(i)) Like UCase(search(j)) & "*" Then 'compare search term and keyword
If (UCase(search(l)) & "*" Like UCase(names(k)) & "*" Or UCase(names(k)) Like UCase(search(l)) & "*") And Len(names(k)) > 2 Then
在您的示例代码中,"关键字"需要是单数,然后它将匹配单数和复数(正如您所看到的(。此外,您不需要在"搜索"项上使用通配符。
"TEMPLATE" Like "*" & "TEMPLATE" & "*" 'returns True
"TEMPLATES" Like "*" & "TEMPLATE" & "*" 'returns True
这样想吧。在上述两种情况下,我们本质上都在说"第一个术语包含第二个术语吗?">