时间戳

首页 > 解决方案 > 从字符串 excel 中检索多个匹配项

excel - 从字符串 excel 中检索多个匹配项

问题描述

对不起，如果这是一个愚蠢的问题，但我已经绞尽脑汁好几天了，我似乎无法提出解决方案。

我有一个短语列表和一个需要搜索、提取和替换的关键字列表。

例如，我在工作表 1 列 A 中有以下关键字列表，这些关键字需要提取并替换为 B 列中的关键字。

red      -     orange

blue     -     violet

green    -     pink

yellow   -     brown

在表 2 中，我在 A 列中有一个短语列表。

The girl with blue eyes had a red scarf.

I saw a yellow flower.

My cousin has a red car with blue rims and green mirrors.

我想在 B 列中提取与每个短语匹配的关键字，它们的显示顺序与它们的显示顺序完全相同：

COLUMN A                                                        COLUMN B

The girl with blue eyes had a red scarf.                        violet, orange

I saw a yellow flower.                                          brown

My cousin has a red car with blue rims and green mirrors.       orange, violet, pink

有什么办法可以通过公式或VBA来实现？这也需要与 Excel 2016 一起使用，所以我不能使用像“TEXTJOIN”这样的花哨功能。

提前谢谢大家！

干杯!

乐

我能够找到一些代码几乎可以完成我需要它做的事情，但它没有保持正确的顺序。

无论如何它可以被修改以产生所需的结果？不幸的是，我对 VBA 不是很好。:(

Sub test()
Dim datacount As Long
Dim termcount As Long

datacount = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
termcount = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To datacount

    dataa = Sheets("Sheet1").Cells(i, "A").Text
    
    result = ""
            
    For j = 1 To termcount
    
        terma = Sheets("Sheet2").Cells(j, "A").Text
        termb = Sheets("Sheet2").Cells(j, "B").Text
        
        If InStr(dataa, terma) > 0 Then
        
             
        If result = "" Then
           
           result = result & termb
           
        Else
        
            result = result & ", " & termb
            
        End If
            
        End If
        
    Next j
    
       Sheets("Sheet1").Cells(i, "B").Value = result
    
    Next i
End Sub

标签： excel

解决方案

我建议您使用自 Excel 2013 以来的内置函数Power Query 。

假设 Sheet1 上的颜色文本字符串位于名为Tbl_LookUp的表中 Tbl_LookUp

假设 Sheet2 上的短语在另一个名为Tbl_Phrases的表中 Tbl_Phrases

转到DataExcel 的选项卡并将两个表加载到 Power Query 编辑器（您可以在 Excel 2016 中搜索如何将数据从表加载到 PQ 编辑器）。请注意，屏幕截图来自 Excel 365。 数据加载表

加载后，转到Tbl_Phrases查询，然后执行以下步骤：

  1. 添加从 1 开始的索引列 添加索引列
  2. 按分隔符拆分短语列，space用作分隔符并选择将结果放入rows 拆分列
  3. 将当前查询与Tbl_LookUp查询合并，使用该Phrase列匹配该Old Text列 与查找表合并
  4. 展开新列以显示列中的New Text内容 展开表格 显示新文本
  5. New Text按列分组Index，可以选择对New Text列中的值求和，分组后会报错。转到公式字段并将公式的这一部分替换List.Sum([New Text])为Text.Combine([New Text],", ")。按回车键，错误将更正为所需的文本字符串。 组文本

以下是上述查询的完整M 代码。您可以将其复制并粘贴到高级编辑器中，而无需手动完成每个步骤：

let
    Source = Excel.CurrentWorkbook(){[Name="Tbl_Phrases"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Phrases", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"Phrases", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Phrases"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Phrases", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Phrases"}, Tbl_LookUp, {"Old Text"}, "Tbl_Replace", JoinKind.LeftOuter),
    #"Expanded Tbl_Replace" = Table.ExpandTableColumn(#"Merged Queries", "Tbl_Replace", {"New Text"}, {"New Text"}),
    #"Grouped Rows" = Table.Group(#"Expanded Tbl_Replace", {"Index"}, {{"Look up color", each Text.Combine([New Text],", "), type nullable text}})
in
    #"Grouped Rows"

当您在Tbl_Phrases查询中完成添加索引列时，即上面的第 1 步，您可以制作查询的副本（只需右键单击原始查询并选择“复制”），然后您将有第二个查询称为Tbl_Phrases (2)。在您完成编辑原始查询并以所需的文本字符串结束之前，无需处理此查询。

然后，您可以使用索引列将Tbl_Phrases (2)查询与Tbl_Phrases查询合并。展开新列以显示列中的内容look up colour。最后，将该Phrases列与look up color带有 delimiter的列合并(space)-(space)，并删除索引列，然后您应该拥有所需的文本字符串。

合并列

这是Tbl_Phrases (2)查询的M 代码。提醒一下，您必须先完成Tbl_Phrases查询，否则合并查询步骤将导致错误：

let
    Source = Excel.CurrentWorkbook(){[Name="Tbl_Phrases"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Phrases", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, Tbl_Phrases, {"Index"}, "Tbl_Phrases", JoinKind.LeftOuter),
    #"Expanded Tbl_Phrases" = Table.ExpandTableColumn(#"Merged Queries", "Tbl_Phrases", {"Look up color"}, {"Look up color"}),
    #"Merged Columns" = Table.CombineColumns(#"Expanded Tbl_Phrases",{"Phrases", "Look up color"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Index"})
in
    #"Removed Columns"

然后，您可以将Tbl_Phrase (2)查询加载到同一工作簿中的所需工作表（或 Sheet2 上的某个位置）。 结果

如果您有任何问题，请告诉我。

推荐阅读