有小伙伴想从20多万行的数据中,根据几万行数据查找其对应的详细信息,用Vlookup或者Index Match直接就卡死了。
难道这个问题在Excel中就无解了吗?那你也太小瞧Excel了。虽然处理大量数据,我们提倡到数据库中去处理,但是大家都知道,自Excel 2007之后,单张表最大数据行数已经增加到了1048576行,对就是一百多万行。既然Excel给设计成这么大的容量,我们应该相信,微软就会提供相应的处理能力。
废话少说,我们直接来看一下怎么处理这种问题。
用罗斯文数据库截取了一些数据,并用VBA在工作表中重复填充,得到一个超过二十万行的数据源。ID列是一个递增的连续序列数,订单ID列是用随机数函数生成的1~200000之间前的数,会有重复。这个放在一个文件中的工作表中,工作表命名为“数据源”。
如下图所示,数一数前面的行标,确实是6位数,超过二十万行的数据。
在第二个文件的工作表中存放了一些订单ID,截取了23758个订单ID,工作表命名为“查询表”。
根据第二个表中的“订单ID”从第一个表中查询所有其他列的相关信息。
打开第二个文件(或者新建一个文件也行),在【数据】选项卡下面依次单击“新建查询”、“合并查询”、“启动查询编辑器”。
在查询编辑器中,点击【开始】选项卡,在最右边的“新建查询”组中依次单击“新建源”、“文件”、“Excel”。
在弹出的对话框中选择数据源文件后,弹出以下对话框。
点击红色方框中的“数据源”,然后点击“确定”按钮。
我们就会导入一份数据,如下图所示。
用同样的方法,将“查询表”中的数据也导入查询编辑器。
在查询编辑器的【开始】选项卡中,点击“合并”组中的“合并查询”下拉菜单中的“将查询合并为新查询”。
在弹出的对话框中,进行以下设置:
第一个下拉框中选择“查询表”,第二个下拉框中选择“数据源”表。这时,数据都被部分加载显示在这里。
同时选中上下两个“订单ID”字段,这两个表会通过选中的字段关联在一起。
在最下面的链接类型中选择“左外部”。如果你了解SQL,那你应该明白,这就叫做Left Join,就是查询结果包含第一个表中的所有数据并匹配上第二个表中相应的数据。
设置完之后,点击“确定”按钮就创建了一个合并查询,如下图所示。
我们需要点击下图红色方框中的展开按钮。
这里列出了所有字段,如果我们只想查看部分字段的话,我们可以在这里只选择要查看的字段。
这样就得到了一个查询结果。
这里需要注意,我们需要点击【开始】选项卡下面的“关闭并上载至...”,而不是“关闭并上载”,这是因为“关闭并上载”会直接加载所有三个查询的数据,那个二十多万行的数据也会被加载,而“关闭并上载至...”则会弹出以下对话框,这里我们选择“仅创建链接”。
这时在工作表的右边就出现了三个链接。
我们右键点击最下面的Merge1链接,点击菜单中的“加载到...”。
在弹出的对话框中,查看数据的方式选择“表”,然后点击“加载”按钮。
咻~几万行查询结果数据就被加载到表中了。
是不是感觉比使用公式好玩又省时省力呢?而且,如果数据源中同一个“订单 ID”有多行,这里也会全部查询出来。这里我们全程没有用到任何公式、编程,纯菜单式操作,这么实用的知识还不随手分享出去?
关于链接的类型
左外部:返回第一个表中所有行,并从第二个表中用关联字段查询匹配行的内容;
右外部:相对于左外部来讲,就是返回第二个表中的所有行,并从第一个表中用关联字段查询匹配行的内容;
完全外部:两个表中所有行都返回,如果有匹配行就返回关联的信息;
内部:只返回两个表中都有的行,比如一个表中有,另一个表中没有,则不返回信息;
左反:返回只在第一个表中存在,不在第二个表中存在的行;
右反:返回只在第二个表中存在,不在第一个表中存在的行。
联系客服