Skip to main content

Posts

Showing posts from July, 2019

SQL EXCEL GET TEXT

以 SQL 语句引入 Excel 格式门诊日志,筛查得到疑似传染病门诊日志:

SQL 命令文本
sselect 姓名, 性别, 年龄, 病人诊断, 结束时间 from [工作表名$] where 病人诊断 like "%鼠疫%" or 病人诊断 like "%霍乱%" or 病人诊断 like "%非典型肺炎%" or 病人诊断 like "%艾滋病%" or 病人诊断 like "%AIDS%" or 病人诊断 like "%HIV%" or 病人诊断 like "%病毒性肝炎%" or 病人诊断 like "%脊髓灰质炎%" or 病人诊断 like "%禽流感%" or 病人诊断 like "麻疹%" or 病人诊断 like "%流行性出血热%" or 病人诊断 like "%狂犬病%" or 病人诊断 like "%流行性乙型脑炎%" or 病人诊断 like "%乙脑%" or 病人诊断 like "%登革热%" or 病人诊断 like "%炭疽%" or 病人诊断 like "%痢疾%" or 病人诊断 like "%肺结核%" or 病人诊断 like "%结核性胸膜炎%" or 病人诊断 like "%伤寒%" or 病人诊断 like "%流脑%" or 病人诊断 like "%百日咳%" or 病人诊断 like "%白喉%" or 病人诊断 like "%流行性脑脊髓膜炎%" or 病人诊断 like "%新生儿破伤风%" or 病人诊断 like "%猩红热%" or 病人诊断 like "%布鲁氏菌病%" or 病人诊断 like "%淋病…

Excel VBA Get Text

按照多于2个关键字完全匹配方式,在 Excel VBA 检索传染病日志。缺陷:自定义的传染病诊断,无法得到结果。

Sub 传染病筛选() '适用于 Excel 2007, 2010 Dim sht As Worksheet With Range("a1").CurrentRegion '利用 with 语句获取 A1 当前区域,避免多次引用,提升速度 .AutoFilter Field:=7, Criteria1:=Array("手足口病", "乙肝", "病毒性肝炎", "流行性感冒"), Operator:=xlFilterValues '以第7列为条件,筛选关键词, 完全匹配 On Error Resume Next '防错,有错误时继续下一步 ShtNmae = ActiveSheet.Name '记录当前表的名称 Set sht = Sheets("查询结果") '将工作表”查询结果”赋予变量 sht, If Err.Number <> 0 Then Sheets.Add After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = "查询结果" Else Sheets(Sheets.Count).Cells.Clear End If Sheets(ShtName).Select .SpecialCells(xlCellTypeVisible).Copy Sheets(Sheets.Count).[a1] '将筛选后的所欲对象复制到“查询结果”中 .AutoFilter End With End Sub
TOP