比较 Excel 中的多个数据列表以查找相关数据 [英] Comparing Multiple Lists of Data in Excel to Find Correlating Data
问题描述
我以为我知道从哪里开始，但遗憾的是我已经足够长了，我需要一些帮助.首先，我使用的是四个产品数据列表，我需要将它们与我自己的产品数据进行比较.
I thought I knew where to start with this but sadly I it's been long enough to where I need some assistance. First of all what I am working with is four lists of product data that I need to compare against my own product data.
例如，每个产品列表都包含以下数据:
So for example each of the product lists contain the following data:
Product Category
Product Name
Inventory
Product Price
每个列表都有至少一千条记录.
Each of the lists have at least a thousand records.
我需要的是第五个列表，它告诉我哪些竞争对手的产品没有在我的列表中.
What I need is a fifth list that tells me which of the competitors products I don't have on my list.
最后但并非最不重要的一点是清单包含重复记录.
Last but not least and where this gets trickier is that the inventory contains duplicate records.
当我花时间研究这个问题时，我什至很难构建这个问题.
Its hard for me even to construct this question as I spend time with it.
不过，我想要的是一份由我的产品列表中没有的所有竞争对手共享的产品列表.
What I want though is a list of products that are shared by all of the competitors that I do NOT have on my product list.
推荐答案
如果您熟悉数据透视表，也许可以使用多合并范围选项来满足您的需求...
If you are familiar with PivotTables perhaps the multiple-consolidation ranges option could be used to suit your needs...
通过访问数据透视表向导对话框(快捷键:Alt+D+P)选择多合并范围选项，然后在类似于下图的对话框中输入设置.数据显示在同一张纸上，但同样可以存储在不同的纸上，甚至是封闭的工作簿中.
Select the multiple-consolidation ranges option by accessing the PivotTable wizard dialog (Shortcut: Alt+D+P) then enter settings in the dialog similar to below. The data is shown on the same sheet but could equally be stored in different sheets or even closed workbooks.
重新排列列并将产品名称分组为匹配"和不匹配"，如图所示.有几种方法可以自动分组，最简单的可能是在数据透视表旁边添加一个计数列并过滤有 4 个项目的行，然后您可以通过按 Alt+ 选择行字段中的可见项目；并选择组选项.将此组重命名为Matched"，然后在计数列中过滤少于 4 个项目并将该组重命名为Unmatched"
Rearrange columns and group product names into "Matched" and "Unmatched" as shown. There are several methods to automate the grouping, the simplest is probably to add a count column beside the pivottable and filter on rows where there are 4 items, then you can select visible items in the row field by pressing Alt+; and choose the group option. Rename this group "Matched", then filter for less than 4 items in the count column and rename the group "Unmatched"
现在您可以使用简单的 vlookup 或其他方式将所有匹配的产品名称与您自己的列表进行比较.
Now you can compare all matched product names with your own list using a simple vlookup or otherwise.
这篇关于比较 Excel 中的多个数据列表以查找相关数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
