I have a table in Excel that I want to filter. It will have a maximum of 1 million rows and 80 columns. All the calculations etc are done programatically in arrays to cut dwn processing time. However, I want to also filter the results to display only certain results based on one column value, followed by a top 5% based on another filter value.
我在Excel中有一个表要过滤。它最多有100万行和80列。所有计算等都以编程方式在数组中完成,以减少dwn处理时间。但是,我还希望过滤结果,以便仅根据一个列值显示某些结果,然后根据另一个过滤器值显示前5%。
When I first did the sheet, it was limited to 65000 results so there were no problems with the size of the data set. I just invoked the worksheet filter functions from code and did it that way. Can I do it that way with a larger data set or is there a way to filter an array the way you d a dataset on a sheet?
当我第一次完成工作表时,它被限制为65000个结果,因此数据集的大小没有问题。我只是从代码调用工作表过滤器函数并以这种方式执行。我可以用更大的数据集这样做吗,或者有没有办法按照数据集在工作表上的方式过滤数组?
Thanks
3 个解决方案
#1
0
You really should put that data in an Access table and use Excel's Database Query to do the job. Since it can also filter retrieved data based on a cell value, it's a great combination.
Storing the data in a database brings you another interesting option (depending on what you want to do): to query your database using PowerPivot.
您确实应该将这些数据放在Access表中并使用Excel的数据库查询来完成这项工作。由于它还可以根据单元格值过滤检索到的数据,因此它是一个很好的组合。将数据存储在数据库中会带来另一个有趣的选项(取决于您想要做什么):使用PowerPivot查询数据库。
#2
1
As already mentioned by everyone, excel 2007 will take you to a million rows, but its slower than the excel 2003 that I presume you're using at the moment so filtering using it wouldn't be advisable.
正如大家已经提到的那样,excel 2007会带你到一百万行,但它比我认为你现在正在使用的excel 2003慢,所以使用它进行过滤是不可取的。
Along with mysql, ms access is also an option.
与mysql一起,ms访问也是一种选择。
#3
0
Although using a relational DB would be preferable in many ways, if you don't have any formulas then filtering your data (1 million rows by 80 columns) using Excel will be reasonably fast (< 1 or 2 seconds depending on what sort of filtering you want to do, which will probably be faster than an un-indexed DB table) assuming that you have enough RAM.
If you do have any formulas then you will probably need to be in Manual calculation mode to avoid the filtering process triggering multiple recalculations.
虽然在许多方面使用关系数据库会更好,但如果你没有任何公式,那么使用Excel过滤你的数据(100万行80列)将相当快(<1或2秒取决于什么样的过滤假设你有足够的RAM,你想做的,可能会比没有索引的数据库表更快。如果您有任何公式,那么您可能需要处于手动计算模式,以避免过滤过程触发多次重新计算。
#1
0
You really should put that data in an Access table and use Excel's Database Query to do the job. Since it can also filter retrieved data based on a cell value, it's a great combination.
Storing the data in a database brings you another interesting option (depending on what you want to do): to query your database using PowerPivot.
您确实应该将这些数据放在Access表中并使用Excel的数据库查询来完成这项工作。由于它还可以根据单元格值过滤检索到的数据,因此它是一个很好的组合。将数据存储在数据库中会带来另一个有趣的选项(取决于您想要做什么):使用PowerPivot查询数据库。
#2
1
As already mentioned by everyone, excel 2007 will take you to a million rows, but its slower than the excel 2003 that I presume you're using at the moment so filtering using it wouldn't be advisable.
正如大家已经提到的那样,excel 2007会带你到一百万行,但它比我认为你现在正在使用的excel 2003慢,所以使用它进行过滤是不可取的。
Along with mysql, ms access is also an option.
与mysql一起,ms访问也是一种选择。
#3
0
Although using a relational DB would be preferable in many ways, if you don't have any formulas then filtering your data (1 million rows by 80 columns) using Excel will be reasonably fast (< 1 or 2 seconds depending on what sort of filtering you want to do, which will probably be faster than an un-indexed DB table) assuming that you have enough RAM.
If you do have any formulas then you will probably need to be in Manual calculation mode to avoid the filtering process triggering multiple recalculations.
虽然在许多方面使用关系数据库会更好,但如果你没有任何公式,那么使用Excel过滤你的数据(100万行80列)将相当快(<1或2秒取决于什么样的过滤假设你有足够的RAM,你想做的,可能会比没有索引的数据库表更快。如果您有任何公式,那么您可能需要处于手动计算模式,以避免过滤过程触发多次重新计算。