通过搜索提取多列 [英] Extracting Multiple Columns with Search

问题描述

我在获取表以提取多列信息时遇到麻烦.目前，我有两个Excel工作表:

I am having trouble getting my table to extract information across multiple columns. Currently I have two excel sheets:

第一个excel工作表是一个主日志，上面有一个巨大的表，里面填满了我公司的所有工程标准程序的超链接和名称.这是我从中提取信息的表.

The first excel sheet is a master log with a giant table filled with the hyperlinks and names of all the Engineering Standard Procedures for my company. This is the table I am extracting information from.

第二张excel表格如下图所示.目的是，在搜索栏中键入的任何单词都将从其中的第一个excel工作表返回任何部分结果.目前，我的表仅通过搜索一列并返回该esp声明的超链接来运行.

The second excel sheet is pictured below. It's purpose is that any word typed into the search bar returns any partial results from that first excel sheet in it. Currently my table functions by only searching one column and returning the hyperlink for that esp stated.

此处是屏幕截图的原始链接.感谢@Jvdv的帮助.

Here is the original link to the screenshots. Thanks to the help of @Jvdv.

我想要的是以下任何一种.

What I would like is either of the following..

1. 在搜索栏中输入关键字后.结果显示与该ESP关联的关键字以及ESP名称和超链接.

例如:如果我输入销售"一词.我的提取表的第一列将显示为材料供应商标识图".关键字列中将包含材料销售"，而超链接列将提供相应的超链接.

For example: If I type in the word Sales. The first column of my extraction table would read "Material Vendor Identification Chart" The keywords column would have "materials sales" in it and the hyperlink column would provide the respective hyperlink.

或

1. 在搜索栏中输入关键字后.该程序同时搜索关键字和ESP的名称，并返回与超链接的部分匹配.

推荐答案

以下解决方案使用的是 Power Query (在 Excel 2010和更高版本中可用).我的是 Excel 2016 .

The following solution is using Power Query which is available in Excel 2010 and later versions. Mine is Excel 2016.

为解决您的问题，我使用了以下示例数据.它存储在名为 Tbl_ESP 的表中.

To tackle your question, I used the following sample data. It is stored in an Table called Tbl_ESP.

请注意，我添加了一个名为 Hyperlinks 的新列，该列显示了A列中每个功能超链接后面的链接.目前没有excel公式可以自动执行此操作，因此您必须使用 VBA 或在新列中手动复制和粘贴链接.此步骤的目的是允许您在搜索结果表中使用超链接.

Please note I've added a new column called Hyperlinks which displays the link behind each functional hyperlinks in column A. At the moment there is no excel formula to do that automatically so you have to either use VBA or manually copy and paste links in the new column. The purpose of this step is to allow you to have the hyperlinks working in the Search Result Table.

在另一个工作表中，我创建了一个名为 Tbl_Search 的下表，您可以在其中键入关键字，并且该表不区分大小写.在我的解决方案中，您只能使用'空格'分隔每个关键字(您可以更改设置以使用逗号或其他首选定界符分隔每个关键字).

In another worksheet I created the following Table called Tbl_Search, in which you can type in the key words and it is case insensitive. In my solution you can only use 'space' to separate each key word (you can change the setting to use comma or other preferred delimiter to separate each key word).

然后，您可以使用数据选项卡中的来自表功能，将两个表都添加到Power Query Editor中.您可以先添加一个，然后退出编辑器，然后使用相同的功能添加另一个.请查看本文以了解其他方法:安装Power Query的完整指南

Then you can use From Table function in the Data tab to add both tables to the Power Query Editor. You can add one first, then exit the editor and add another one using the same function. Check this article out for other methods: The Complete Guide to Installing Power Query

我首先在Power Query编辑器中通过以下步骤开始使用 Tbl_Search :

1. 将文本字符串更改为小写；
2. 将文本用空格"分隔，然后将每个单词放到新行中；

1. changed the text string to lower-case;
2. Split the text by "space" and put each word in a new row;

1. 填充，以防在只有一个关键字的情况下该列覆盖空值；
2. 将列转换为列表.

1. Filled Down the column to overwrite null in case there is only one key word;
2. Convert the column to a List.

我已将该查询重命名为 Search ，以后将用作搜索条件.

I have re-named this query as Search, which will be used as the search criteria later on.

然后我按照以下步骤开始研究 Tbl_ESP :

1. 添加了一个名为Combined的自定义列，以将Description和KeyWords列中的字符串连接为一个文本字符串，并在其之间留有空格；
2. 将最后一步中的文本字符串转换为小写;
3. 使用以下公式添加了另一个名为Match的自定义列，该列旨在查找文本中是否包含搜索列表中的任何关键字上一步中的字符串，如果是，则返回 TRUE ，否则返回 FALSE ;

1. Added a custom column called Combined to concatenate the strings in Description and KeyWords column into one text string with a space in between;
2. convert the text string from last step into lower-case;
3. Added another custom column called Match with the following formula, which aims to find out if any of the key words from the Search list is contained in the text string from previous step, if so returns TRUE otherwise FALSE;

=List.Count(Splitter.SplitTextByAnyDelimiter(Search)([Combined]))>1

1. 过滤 Match列，仅使用 TRUE ；
2. 删除其他列，除了Description，KeyWords和Hyperlinks;
3. 添加了一个索引列，该列从1开始并将此列移动到表的开头，那么我将得到以下内容:

1. Filter the Match column with TRUE only;
2. Remove other columns except Description, KeyWords, and Hyperlinks;
3. Added an Index Column starts from 1 and move this column to the beginning of the table, then I have the following:

现在是时候将该表关闭并加载到搜索表所在的工作表中.

Now it's time to Close and Load the above table to the worksheet where your Search table sits.

最后一步是在上表的末尾添加一列，然后键入以下公式:

The last step is to add a column to the end of the above table and type in the following formula:

=IF(ISBLANK(G4),LEFT(E4,6)&" Obsolete",HYPERLINK(G4,LEFT(E4,6)))

然后您应该具有以下内容:

Then you should have the following:

您可以选择隐藏G列 Hyperlinks，以便仅显示嵌入了超链接的ESP#的名称.

You can choose to hide Column G Hyperlinks so it will only show the name of the ESP # with hyperlink embedded.

每次在搜索单元格中输入新的关键字字符串时，都需要转到数据标签以刷新数据，或按 Ctrl+Alt+F5 以获取更新的搜索结果.

Each time you type in a new string of key words in the search cell, you need to go to Data tab to Refresh Data, or press Ctrl+Alt+F5 to get an updated search result.

请注意，上面执行的所有步骤都使用Power Query Editor的内置功能，您可以在线搜索所有专有技术，但随时可以问我任何问题.

Please note all steps performed above are using built-in functions of Power Query Editor, and you can google all the know-hows online but feel free to ask me any questions.

以下是幕后代码，仅供参考:

Here are the codes behind the scene for reference only:

对于 Tbl_Search

let
    Source = Excel.CurrentWorkbook(){[Name="Tbl_Search"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Search", type text}}),
    #"Lowercased Text" = Table.TransformColumns(#"Changed Type",{{"Search", Text.Lower, type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Lowercased Text", {{"Search", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Search"),
    #"Filled Down" = Table.FillDown(#"Split Column by Delimiter",{"Search"}),
    Column1 = #"Filled Down"[Search]
in
    Column1

对于 Tbl_ESP

let
    Source = Excel.CurrentWorkbook(){[Name="Tbl_ESP"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ESP #", type text}, {"Description", type text}, {"KeyWords", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Combined", each [Description]&" "&[KeyWords]),
    #"Lowercased Text" = Table.TransformColumns(#"Added Custom1",{{"Combined", Text.Lower, type text}}),
    #"Added Custom" = Table.AddColumn(#"Lowercased Text", "Match", each List.Count(Splitter.SplitTextByAnyDelimiter(Search)([Combined]))>1),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Match] = true)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Description", "KeyWords", "Hyperlinks"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Other Columns", "Index", 1, 1),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Index", "#"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"#", "Description", "KeyWords", "Hyperlinks"})
in
    #"Reordered Columns"

这篇关于通过搜索提取多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！