如何比较2个excel工作簿中的2个excel列和大量行 [英] How to compare 2 excel columns in 2 excel workbooks with huge number of rows

问题描述

大家好，



我有两个excel工作簿，每个工作簿都有大量行（以十万分之一为单位）。

我需要检查书1的E列行数与书2的B列行匹配的数量。



我试过vlookup，索引匹配，匹配 - 但全部在不着！这些操作将永远执行。然后我需要粘贴值。



因为我没有映射任何值，只是匹配列并计算相似的值，是否有任何替代和有效的方法不需要几个小时？



请帮助!!



我尝试过：



我尝试了vlookup，索引匹配，只匹配。

我找到了一个建议使用部分匹配2的解决方案vlookups，但这也无济于事。

我也尝试了条件格式化，在这种情况下，过滤需要花费很多时间。

解决方案

< blockquote>

Quote：

我有两个excel工作簿，每个工作簿都有大量的行（以十万分之一为单位）。

行数如此之多，任何事情都很重要，而excel可能不是解决这个问题的最佳工具。

Excel非常好，但它是以牺牲速度为代价的。像SQL这样的数据库将以更有效的方式处理数据。

对数据进行排序会对性能产生巨大影响。

引用：

我试过vlookup，索引匹配，只匹配。

我找到了一个解决方案，建议使用2个vlookups的部分匹配，但这也无济于事。

我也尝试过条件格式化，在这种情况下，过滤需要花费很多时间。

您使用的确切公式和数据很重要。并非每个公式都适用于所有数据，有些组合比其他组合更有效。



如果没有关于你所做的事情的细节，就不可能给你更好的建议。那些普遍性。教你数据库超出了本论坛的范围。

Hi All,

I have two excel workbooks each of which has huge amount of rows (in lakhs).
I need to check how many E column rows of book 1 are matching with B column rows of book 2.

I have tried vlookup, index-match, match - but all in vain! These operations are taking forever to execute. And then I need to pastespecial the values.

Since I am not mapping any value, just matching columns and counting similar values, is there any alternate and effective way that does not take hours?

Please help!!

What I have tried:

I have tried vlookup, index-match, only match.
I have found a solution that suggests using partial match with 2 vlookups but that does not help either.
I have tried conditional formatting as well in which case the filtering is taking a lot of time.

解决方案

Quote:

I have two excel workbooks each of which has huge amount of rows (in lakhs).

With such a large number of rows, anything matters, and excel may not be the best tool for this problem.
Excel is very nice, but it is at expense of speed. Database like SQL will handle you data in a more efficient fashion.
Sorting the data have a huge impact on performance.

Quote:

I have tried vlookup, index-match, only match.
I have found a solution that suggests using partial match with 2 vlookups but that does not help either.
I have tried conditional formatting as well in which case the filtering is taking a lot of time.

The exact formulas you used and the data matters. Not every formula work with every data, and some combinations are more efficient than others.

Without details on what you have done, it is impossible to give you better advices than those generalities. And teaching you databases is out of scope of this forum.

这篇关于如何比较2个excel工作簿中的2个excel列和大量行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！