excel模糊查找
If you work with data in Excel, you know what a mess it can be. I help my customers clean up data that they've imported from another computer system, or from reports received from another department or group. Those files can be filled with spelling mistakes, strange abbreviations, extra spaces or missing punctuation.
如果您在Excel中使用数据,您会知道它可能是一团糟。 我帮助我的客户清理他们从另一台计算机系统或从另一部门或小组收到的报告中导入的数据。 这些文件中可能充满拼写错误,奇怪的缩写,多余的空格或标点符号丢失。
清理数据 (Cleaning the Data)
It can take you hours, or days, to match items between lists, and eliminate any duplicates. Your favourite Excel functions, like VLOOKUP or MATCH, can do some of the work, but fail in other areas.
在列表之间匹配项并消除重复项可能需要花费您数小时或数天的时间。 您喜欢的Excel函数(例如VLOOKUP或MATCH )可以完成某些工作,但在其他方面却失败。
Shaking your fist, or cursing at the data, doesn't help, as I'm sure you know. Fortunately, there is a new Fuzzy Lookup Add-in for Excel, from Microsoft's BI Labs, that looks promising.
我确定你知道,动摇拳头或诅咒数据都无济于事。 幸运的是,微软的BI Labs提供了一个新的Excel模糊查找加载项,看起来很有希望。
Excel 2010的模糊查找加载项 (Fuzzy Lookup Add-in for Excel 2010)
The new add-in is named Fuzzy Lookup Add-in for Excel 2010, and it helps you find best matches, for items that aren't an exact match. The project description says:
新的加载项名为Excel 2010的模糊查找加载项,它可以帮助您查找与不完全匹配的项目的最佳匹配。 项目说明说:
It can be used to identify fuzzy duplicate rows within a single table or to fuzzy join similar rows between two different tables. The matching is robust to a wide variety of errors including spelling mistakes, abbreviations, synonyms, and added or missing data.
它可用于识别单个表中的模糊重复行,或用于模糊连接两个不同表之间的相似行。 匹配对于各种错误(包括拼写错误,缩写,同义词以及添加或丢失的数据)具有鲁棒性。
That sounds useful, so I downloaded the add-in, and installed it for testing.
听起来很有用,所以我下载了该加载项,并安装了它进行测试。
设置模糊查找 (Set Up the Fuzzy Lookup)
When you open Excel 2010, after installing the Fuzzy Lookup add-in, you'll see a Fuzzy Lookup tab on the Ribbon, and it contains the Fuzzy Lookup command.
打开Excel 2010时,在安装“模糊查找”加载项之后,您将在功能区上看到“模糊查找”选项卡,其中包含“模糊查找”命令。
Follow these steps to set up the Fuzzy Lookup:
请按照以下步骤设置模糊查找:
The add-in works with formatted tables, so create one or more Excel tables in your workbook, if you haven't done so already. The add-in download includes a sample file named Portfolio, which contains Excel tables.
该外接程序可用于格式化表格,因此,如果尚未在工作簿中创建一个或多个Excel表格 ,则可以使用它。 加载项下载包括一个名为Portfolio的示例文件,其中包含Excel表。
- On the Ribbon, click the Fuzzy Lookup command, to open the Fuzzy Lookup pane. 在功能区上,单击“模糊查找”命令,以打开“模糊查找”窗格。
- Select the left and right tables for the comparison (to identify matches in a single table, set the left and right tables to be the same) 选择左表和右表进行比较(要识别单个表中的匹配项,请将左表和右表设置为相同)
- Columns with the same heading will be automatically joined. You can leave that join, or delete, and create new joins for other fields. 具有相同标题的列将自动合并。 您可以保留该联接,也可以删除并为其他字段创建新联接。
- For the Output columns, select the columns in each table that you want in the output table. Include the FuzzyLookup.Similarity column, to see how close the matches are. 对于“输出”列,在每个表中选择要在输出表中使用的列。 包括FuzzyLookup.Similarity列,以查看匹配的接近程度。
- Leave the number of matches at the default of 1, or increase it to see more matches for each item in the left table. 将匹配数保留为默认值1,或增加它以在左侧表格中查看每个项目的更多匹配项。
运行模糊查找 (Run the Fuzzy Lookup)
After you set up the Fuzzy Lookup, follow these steps to run it: Select a cell in an empty part of the workbook. The results will start in the active cell, so make sure there is enough room, below and to the right of the selected cell.
设置模糊查找后,请按照以下步骤运行它:在工作簿的空白部分中选择一个单元格。 结果将从活动单元格开始,因此请确保在所选单元格的下方和右侧有足够的空间。
Click the Go button, to output the results.
单击执行按钮,输出结果。
配置模糊查找 (Configure the Fuzzy Lookup)
You can change the configurations in the Fuzzy Lookup add-in, and create your own.
您可以在“模糊查找”加载项中更改配置,然后创建自己的配置。
自己测试 (Test It Yourself)
To do your own testing, you can download Fuzzy Lookup Add-in for Excel 2010. The download page has a couple of videos that you can watch, for an overview of how the add-in works. ____________
若要进行自己的测试,可以下载Excel 2010的Fuzzy Lookup加载项。下载页面上有几个视频,您可以观看这些视频,以概述加载项的工作原理。 ____________
翻译自: https://contexturesblog.com/archives/2011/05/06/fuzzy-lookup-add-in-for-excel-2010/
excel模糊查找