vba 在 Excel 中过滤大型列表的最佳方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14066753/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the best way to filter a large list in Excel?
提问by Marc L
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 列。所有计算等都是在数组中以编程方式完成的，以减少处理时间。但是，我还想过滤结果以仅显示基于一个列值的某些结果，然后是基于另一个过滤器值的前 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
谢谢
采纳答案by Patrick Honorez
You really should put that data in an Access table and use Excel's Database Queryto 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查询您的数据库。
回答by MarioTheHedgehog
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 访问也是一种选择。
回答by Charles Williams
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 万行 x 80 列）将相当快（< 1 或 2 秒，具体取决于过滤类型您想要这样做，假设您有足够的 RAM，这可能比未索引的 DB 表更快）。
如果您确实有任何公式，那么您可能需要处于手动计算模式，以避免过滤过程触发多次重新计算。
