excel高级筛选_Excel 2007高级筛选器速度缓慢

最新推荐文章于 2024-06-11 11:26:47 发布

culiao2169

最新推荐文章于 2024-06-11 11:26:47 发布

阅读量852

点赞数

文章标签： python java linux excel 人工智能

原文链接：https://contexturesblog.com/archives/2010/11/29/excel-2007-advanced-filter-painfully-slow/

版权

excel高级筛选

It was like riding a lazy snail through molasses in January -- but slower! Usually an Excel Advanced Filter is a speedy way to extract data from a table, but things weren't working right in a sample file that I got last week.

就像在一月份在糖蜜中骑着懒惰的蜗牛一样-但速度要慢一些！通常，Excel 高级筛选器是一种从表中提取数据的快速方法，但是在上周获得的示例文件中，一切都无法正常进行。

And despite what my high school English teachers might think, you can't mix too many similes, when trying to describe excruciating slowness.

而且，尽管我的高中英语老师可能会想，但在描述难以忍受的缓慢性时，您不能混合太多的比喻。

The sample file had code that ran an Advanced Filter in Excel 2007. The code ran quickly in Excel 2003, but screeched to a near halt in Excel 2007. What was the problem?

该示例文件中的代码在Excel 2007中运行了高级筛选器。该代码在Excel 2003中快速运行，但是在Excel 2007中几乎停顿了。问题是什么？

病征 (The Symptoms)

When the code ran in Excel 2007, it looked like the extracted rows were being pasted in the second worksheet, one row at a time. Aha! Turn off the screen updating -- a simple solution. You'd think.

当代码在Excel 2007中运行时，看起来提取的行被粘贴到第二个工作表中，一次粘贴一行。啊哈！关闭屏幕更新-一个简单的解决方案。你会想。

Even with the screen updating turned off, the code barely crawled along. It took almost 3 minutes to extract 1500 rows -- maybe a millisecond faster than it ran with screen updating turned on. Who has that kind of time?

即使关闭了屏幕更新，该代码也几乎没有爬行。提取1500行花了将近3分钟的时间-可能比打开屏幕更新的速度快一毫秒。谁有这样的时间？

继续猜 (Guess Again)

In the next round of solution guessing, I got rid of the few formulas in the worksheet and criteria range. There wasn't anything too complex, but maybe that was slowing things down.

在下一轮解决方案猜测中，我摆脱了工作表和条件范围中的一些公式。没有什么太复杂的，但是也许这会使事情变慢。

I also changed calculation to manual at the start of the code, then set it to automatic at the end of the code.

我还在代码开头将计算方式更改为手动，然后在代码结尾将其设置为自动。

Neither of those changes had any effect on the code's speed.

这些更改均不会影响代码的速度。

清除数据 (Strip the Data Clean)

In round 12 of testing (I've lost track of the test count), I copied the data, and pasted it as values into a new workbook. The code ran like lightning. In July. With jet engines. Hmmm.

在第12轮测试中(我失去了对测试计数的了解)，我复制了数据，并将其作为值粘贴到新的工作簿中。代码像闪电一样运行。在七月。使用喷气发动机。嗯

Maybe it was the formatting and styles in the original file that were slowing things down. To test that theory, I formatted the original table with Normal style, which removed all the borders and fill colour.

也许是原始文件中的格式和样式使事情变慢了。为了验证该理论，我使用Normal样式格式化了原始表格，该表格删除了所有边框并填充了颜色。

That didn't improve things, but when I removed the red fill from the heading cells, I noticed a red comment marker in one of the cells.

这样做并没有改善，但是当我从标题单元格中删除红色填充时，我注意到其中一个单元格中有一个红色注释标记。

鞭打成形状 (Whip Things Into Shape)

Could a comment be the problem? It didn't seem likely, but as soon as I deleted the comment, the code ran perfectly. Put the comment back, and it slowed to a crawl again.

可能是评论吗？似乎不太可能，但是当我删除注释后，代码就完美地运行了。将评论放回去，它又变慢了爬行的速度。

好奇与好奇 (Curiouser and Curiouser)

When I tried to create a sample file to demonstrate this problem, things got even stranger. I created a table with a comment in the heading, and ran the code, expecting it to be slow. It ran quickly, in several tests.

当我尝试创建示例文件来演示此问题时，事情变得更加陌生。我创建了一个在标题中带有注释的表，并运行了代码，期望它很慢。在多个测试中，它运行很快。

Then I added a shape to the worksheet, and assigned a macro, to make it easier to run the code. The code slowed down again.

然后，我在工作表中添加了一个形状，并分配了一个宏，以使其更易于运行代码。代码再次变慢。

I deleted the shape, and the code was still slow, and I had to delete the comment to speed it up again.

我删除了形状，但是代码仍然很慢，我不得不删除注释以再次加快速度。

判决 (The Verdict)

If your Advanced Filters are running slowly in Excel 2007, try removing any comments in the table heading cells. You could delete them at the start of a VBA procedure, run the filter, then add the comments at the end of the code.

如果您的“高级筛选器”在Excel 2007中运行缓慢，请尝试删除表标题单元格中的所有注释。您可以在VBA过程开始时将其删除，运行过滤器，然后在代码末尾添加注释。

The problem seems to occur if there are heading comments, and a shape is added later, as you can see in the short video demonstration below.

如果有标题注释，似乎会出现问题，并且稍后添加了形状，如下面的简短视频演示所示。

Fortunately, this problem appears to be fixed in Excel 2010, so if you upgrade, you should be able to have comments and shapes, without slowing down the Advanced Filters.

幸运的是，此问题在Excel 2010中似乎已得到解决，因此，如果进行升级，则应该能够具有注释和形状，而不会降低“高级筛选器”的速度。

Update: In the comments, PDLobster suggests the following solution, to speed up the filters -- thanks!

更新：在评论中，PDLobster建议以下解决方案，以加快筛选器的速度-谢谢！