Como melhorar o desempenho com arquivos grandes do Excel

Question

Estou trabalhando com o Excel 2007 em um Windows 7, Core i5 2.53GHz (2 núcleos, 4 threads), com 4GB de RAM DDR3 @ 533MHz.

Eu preciso extrair e organizar dados de um banco de dados enorme e, para consegui-lo, eu tenho uma planilha onde eu:

1. a. cole parte dos dados brutos em 6 colunas x 36.600 linhas
   b. extrair os dados dos quais estou interessado em a) e trabalhar com 8 colunas x 36.600 linhas

Copie os resultados das fórmulas de 6 das colunas em 1.b) e cole-os em uma segunda planilha, onde vou compilar todos os dados que preciso e analisá-los.

2. a. para cada grupo de 6 colunas x 36.600 linhas, precisarei adicionar 5 colunas de fórmulas (na maioria pequenas adições com no máximo 2 valores cada, e alguma INDEX-MATCH); b. a quantidade total de dados terminará em 130 desses grupos, portanto: 780 colunas de dados mais 650 colunas de fórmula x 36.600 linhas.
   c. Uma vez que eu tenha compilado e analisado todos os dados, eu vou transformar tudo em valores - que eu irei usar posteriormente com as fórmulas LOOKUPs / INDEX-MATCHes. d. Nem a planilha tem formatação automática, ambas possuem formatação mínima (células coloridas para identificar aquelas que possuem fórmulas), se eu clicar em CTRL + END ela vai direto para o final do intervalo em uso.

Então, meu problema é que, a partir de agora, construí a estrutura da fórmula à qual adicionarei os dados pouco a pouco, mas o arquivo é de 166MB e está consumindo todos os recursos do processador. Eu configurei o cálculo para manual, mas ainda leva 5 minutos para abrir, fechar ou salvar este arquivo.

Perguntas:

1. A lentidão é esperada com um arquivo de 166MB?
2. Funcionaria melhor se eu compilasse os dados como 65 colunas de dados mais 78 colunas com fórmulas x 367.000 linhas?
3. Como estou no processo de aquisição de um novo laptop, um processador melhor superaria as dificuldades? (Estou pensando nas linhas de um QuadCore i7 com 16GB de RAM)

OBSERVAÇÃO sobre a pergunta 3: Não estou procurando recomendações de hardware, pois estou no processo de aquisição da máquina descrita acima. Eu quero saber se posso esperar um melhor desempenho ao trabalhar com este arquivo.

    

Answer 1

Isso é apenas para resolver as perguntas que faço e que não foram respondidas nos comentários

2) Sim, menos colunas com mais linhas são mais eficientes.

3) O novo laptop ajudou. Ainda leva algum tempo para abrir e calcular a planilha, mas é muito mais rápido do que antes (um quarto ou menos do tempo de espera).

    

Answer 2

O Excel é extremamente intensivo em memória e ineficiente ao trabalhar com grandes matrizes. Usando SSD ou ram drive não ajuda porque a maioria será residente na memória e não no disco rígido. Acompanhe o uso de memória no Gerenciador de Tarefas e tente manter o uso do RAM no Excel abaixo de 2 GB. Divida os dados em arquivos menores e use uma planilha de intercalação ou resumo para chamar os dados conforme necessário, em vez de abrir tudo uma vez. Normalmente, um arquivo de 55mb pode ocupar 1gb de memória no Excel e levar de 2 a 3 minutos para abrir, dependendo da estrutura da fórmula; portanto, 5min para abrir 166mb não é inesperado.

Salve em arquivos binários e isso aumentará o carregamento e salvamento de arquivos grandes. Use a CPU e a memória mais rápidas possíveis. Para testar se seu arquivo excel é escalável para núcleos de CPU, execute um cálculo com um temporizador em VBA usando apenas 1 núcleo da CPU e, em seguida, execute novamente usando 2 núcleos. Se a aceleração estiver próxima de 100%, mais núcleos também ajudarão. Use uma área de trabalho em vez de um laptop.