处理巨大的Excel文件 [英] Handling huge Excel file

问题描述

急需您的帮助.我正在处理一个工作簿，该工作簿在一张纸中有7000行X 5000列数据.每个数据点都必须进行操作并将其粘贴到另一个工作表中.这些操作相对简单，其中每个操作将使用少于10行代码(简单的乘法和除法，并加上几个If).但是，该文件不时崩溃，并得到各种类型的错误.问题是文件大小.为了克服这个问题，我正在尝试几种方法

Need your help badly. I am dealing with a workbook which has 7000 rows X 5000 columns data in one sheet. Each of this datapoint has to be manipulated and pasted in another sheet. The manipulations is relatively simple where each manipulation will take less than 10 lines of code (simple multiplications and divisions with a couple of Ifs). However, the file crashes every now and then and getting various types of errors. The problem is the filesize. To overcome this problem, I am trying a few approaches

a)将数据和输出分开在不同的文件中.保持两个文件均处于打开状态，并逐块读取数据(通常为200行x 5000列)，然后对其进行处理并将其粘贴到输出文件中.但是，如果两个文件都打开，那么我不确定它是否可以解决问题，因为这两种方式消耗的内存都是相同的，即不是一个文件占用大量内存，而是两个文件一起消耗相同的内存.

a) Separate the data and output in different files. Keep both files open and take data chunk by chunk (typically 200 rows x 5000 columns) and manipulate that and paste that in output file. However, if both files are open, then I am not sure it remedies the problem since the memory consumed will be same either way i.e. instead of one file consuming a large memory, it would be two files together consuming the same memory.

b)将数据和输出分开在不同的文件中.通过宏在输出文件中插入链接，访问仍处于关闭状态的数据文件中的数据，操作数据并将其粘贴到输出中.这可以逐块完成.

b) Separate the data and output in different files. Access the data in the data file while it is still closed by inserting links in the output file through a macro, manipulate the data and paste it in output. This can be done chunk by chunk.

c)将数据和输出分开在不同的文件中.运行宏以打开数据文件，然后将一部分数据(例如200行)加载到内存中的数组中，然后关闭它.处理数组并打开输出文件，然后粘贴数组结果.

c) Separate the data and output in different files. Run a macro to open the data file and load a chunk of data say 200 rows into memory into an array and close it. Process the array and open the output file and paste the array results.

这三种方法中哪个更好?我相信还有其他更有效的方法.请提示.

Which of the three approaches are better? I am sure there are other methods which are more efficient. Kindly suggest.

我对Access不熟悉，但是我尝试将原始数据导入Access并失败，因为它只允许255列.

I am not familiar with Access but I tried to import the raw data into Access and it failed because it allowed only 255 columns.

有没有一种方法可以使文件保持打开状态，但可以将其洗入和洗出内存.然后可以尝试对上面的a和c进行细微的修改. (恐怕反复打开和关闭会使文件崩溃.)

Is there a way to keep the file open but wash it in and out of Memory. Then slight variations to a and c above can be tried. (I am afraid repeated opening and closing will crash the file.)

期待您的建议

推荐答案

如果您不想离开Excel，可以使用的一个技巧是将基本excel文件保存为二进制".xlsb".

If you don't want to leave Excel, one trick you can use is to save the base excel file as a binary ".xlsb". This will clean out a lot of potential rubbish that might be in the file (it all depends on where it first came from.)

通过这样做，我将Web数据的负载缩减了99.5％(从300MB减少到1.5MB)，现在excel中的各种操作就像梦一样.

I just shrank a load of webdata by 99.5% - from 300MB to 1.5MB - by doing this, and now the various manipulation in excel works like a dream.

另一个技巧(从80年代开始:))，如果您使用大量的单元格公式而不是宏进行迭代，则可以:

The other trick (from the 80s :) ) if you are using a lot of in cell formulae rather than a macro to iterate through, is to:

1. 关闭计算.
2. 复制公式
3. 打开计算，或手动运行计算
4. 复制并粘贴公式输出的特殊值.

这篇关于处理巨大的Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！