创建“搜索”功能在MS Excel [英] Creating a "search" function in MS Excel

问题描述

我在XLS文件中列出了> 100,000个诊断代码，需要从所有与许多特定疾病相关的代码中提取出来。我想要做的是将A列中的所有100,000个诊断代码，B列中的诊断标签，然后有一个搜索项单元格（例如C1），其中我可以写出一个单词，如骨折

然后，我会将所有诊断代码（包括字符串骨折）显示在列D中。

有没有简单的方式在Excel中这样做？我在网上看不到很大的成功，但这可能是因为我不确定从哪里开始。条件格式化没有帮助，因为即使它们被突出显示，滚动浏览十万个代码仍然无法管理。

任何初步想法或提示，我可以尝试搜索将非常欢迎。

样本数据集：

  238骨折近端肱骨
 202主动脉瓣狭窄
 990慢性阻塞性肺疾病
 302髋关节骨折
 182复发性骨折
 094 Marfan综合征
 298糖尿病视网膜病变
  

解决方案

我意识到你没有要求过滤器，但如果你打开到一个稍微不同的解决方案，这似乎很好。列A标题和值应从单元格 A2 开始。然后在单元格 B1 中键入搜索字词。它也将使用通配符进行过滤，并且不区分大小写（即显示骨折，骨折，骨折，骨折，骨折，骨折）。

  Option Explicit 
子过滤器（）
 
 Dim MyArray（） As Variant 
 Dim MyNewArray（）As Variant 
 Dim i As Long 
 Dim item As Variant 
 Dim FilterRange As Range 
 
 With ActiveSheet 
设置FilterRange = .Range（.Cells（2，1），.Cells（.Rows.Count，1）.End（xlUp））
 MyArray = Application.Transpose（FilterRange）
i = 0 
对于MyArray中的每个项目
如果UCase（item）像*& UCase（范围（B1））& *然后
 ReDim保存MyNewArray（i）
 MyNewArray（i）= item 
i = i + 1 
结束If 
下一项
 .Range （FilterRange.Address）.AutoFilter字段：= 1，Criteria1：= MyNewArray（），运算符：= xlFilterValues 
结束
 
 End Sub 
  

此外，您可以在 Worksheet 对象中添加以下内容，以便您不必单击一个按钮来运行宏：

  Option Explicit 
 
 Private Sub Worksheet_Change（ByVal Target As Range） 
 
如果不是Application.Intersect（目标，范围（B1））没有，然后
调用过滤器
结束如果
 
 End Sub 
  

老实说，你可以用 Filter > 文本过滤器> 自定义过滤器。你也可以使用通配符。 ：）

I have a list of >100,000 diagnosis codes in a .XLS document and need to extract from this all codes that are relevant to a number of specific diseases.

What I would like to be able to do is include all 100,000 diagnostic codes in Column A, diagnostic labels in column B, and then have a "search term" cell (e.g. C1) in which I can write a word such as "fracture".

I would then like all the diagnostic codes including the string "fracture" to appear in column D.

Is there a simple way of doing this in Excel? I have looked online without much success but this might be because I'm not certain where to start. Conditional formatting hasn't helped as it's still unmanageable to scroll through 100,000 codes even if they are highlighted nicely.

Any initial thoughts or tips as to what I could try searching for would be very welcome.

Sample dataset:

238 Fracture of proximal humerus
202 Aortic stenosis
990 Chronic obstructive pulmonary disease
302 Hip fracture
182 Recurrent fractures
094 Marfan syndrome
298 Diabetic retinopathy

解决方案

I realize you didn't ask for a filter but if you are open to a slightly different solution this seems to work well. Column A title and values should start at cell A2. Then type your search term in cell B1. It will also filter with wildcards and is case insensitive (i.e. show fracture, Fracture, fractures, Fractures, fractured, Fractured).

Option Explicit
Sub Filter()

Dim MyArray() As Variant
Dim MyNewArray() As Variant
Dim i As Long
Dim item As Variant
Dim FilterRange As Range

With ActiveSheet
    Set FilterRange = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
    MyArray = Application.Transpose(FilterRange)
    i = 0
    For Each item In MyArray
        If UCase(item) Like "*" & UCase(Range("B1")) & "*" Then             
            ReDim Preserve MyNewArray(i)
            MyNewArray(i) = item
            i = i + 1
        End If
    Next item
    .Range(FilterRange.Address).AutoFilter Field:=1, Criteria1:=MyNewArray(), Operator:=xlFilterValues
End With

End Sub

Additionally, you could add the following in the Worksheet object so you don't have to click a button to run the macro:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("B1")) Is Nothing Then
    Call Filter
End If

End Sub

Honestly, you can achieve the same thing with Filter > Text Filters > Custom Filter. You can use wildcards, too. :)

这篇关于创建“搜索”功能在MS Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！