O Excel 2010 lento com grandes conjuntos de dados

Question

Problema:

Recentemente, tenho trabalhado com conjuntos de dados bastante grandes.

No entanto, o Excel parece lidar com isso muito lentamente (leva de 3 a 5 minutos apenas para abrir ou salvar o arquivo).

Detalhes da planilha

• Linhas: 50.000
• Coloumns: 90
• Planilhas: 1
• Tamanho do arquivo: 157mb.

A folha consiste em dados puros. Nenhuma fórmula ou script VBA foi adicionado ainda. Não há referências de célula (por exemplo, = H3) ou matrizes.

Especificações:
Office 2010 32 bit
Processador: i7 3.4 GHZ Quad Core
Ram: 6 Gb
Windows 7

Medidas tomadas até agora

Eu tentei salvá-lo como um arquivo binário do Excel (.xlsb), o que ajudou um pouco (e reduziu o tamanho do arquivo também).

No entanto, 50.000 linhas não estão nem perto do limite do Excel 2010 (que é 1.048.576 linhas), mas é muito lento. Também me lembro de versões mais antigas do Excel sendo capazes de lidar com grandes blocos de dados relativamente rápido.

Existe alguma maneira de acelerar o Excel?

    

Answer 1

Eu decidi tentar imitar o mais próximo possível do seu cenário. Para fazer isso eu:

• criou uma planilha do MS Excel com 50.000 linhas e 90 colunas de dados (felizmente eu trabalho com grandes conjuntos de dados, então isso não foi muito difícil de fazer)
• garantiu que os dados fossem uma mistura de formatos: Texto, Datas, Número e Geral
• não usou fórmulas ou código VBA

No entanto, para mim, essa planilha tinha apenas 24 MB, o que representa apenas 15% do tamanho do seu, então não posso explicar isso.

Infelizmente, o único contexto que você forneceu é a velocidade de leitura / gravação para abrir / salvar sua planilha, então isso é tudo que eu poderia testar. Meus resultados são os seguintes:

• 7,68 segundos para salvar os dados em um disco rígido local
• 3 mins 53 segundos para salvar os dados em uma unidade de rede
• 10,4 segundos para abrir os dados de uma unidade local
• 50.43 segundos para abrir os dados de uma unidade de rede

Agora, como minhas 50.000 linhas e 90 colunas de dados somaram apenas 24MB, meus dados obviamente terão muito menos para salvar e carregar do que o seu arquivo de 157MB (que é cerca de 6,5 vezes maior).

No entanto, como você pode ver, a velocidade de abrir e salvar arquivos não é determinada apenas pelo tamanho do arquivo em si. Trabalhar com um SSD é muito mais rápido do que um disco rígido local, que por sua vez é muito mais rápido que um disco em rede. Além disso, o tipo de rede tem um enorme impacto nas velocidades de transferência de dados (por exemplo, o Gigabit ethernet é muito mais rápido que o fast ethernet).

Infelizmente, o computador mais próximo que encontrei para executar este teste tinha as seguintes especificações:

• Windows 7 Enterprise (64 bits)
• i5 2,6 GHz
• 8 GB de RAM
• MS Excel 2010 (32 bits)

Resumo

Em poucas palavras:

• existem muitos fatores que afetam as velocidades de leitura / gravação, não apenas a quantidade de dados, mas também seu hardware e / ou ambiente de rede
• um teste melhor seria ver quanto tempo leva para o Excel processar alguns dados reais com seus dados, em oposição a velocidades de transferência de leitura / gravação
• outro fator no caso do Excel é se você está carregando qualquer suplemento etc. e onde eles estão localizados
• seus dados têm 157 MB de tamanho, 6,5 vezes o tamanho da minha planilha também contém 4,5 milhões de células de dados (então, o que isso representa?)
• instale mais RAM (trabalhei com grandes conjuntos de dados no Excel - Windows e Mac - por quase duas décadas e o maior fator no desempenho do Excel é a quantidade de RAM que você instalou
• com 4,5 milhões de células de dados, talvez seja necessário reconsiderar se um banco de dados atenderia melhor às suas necessidades

Answer 2

A planilha não é, definitivamente, a ferramenta certa para manipular grandes conjuntos de dados, porque as células são carregadas como objetos na memória (uma maneira muito ineficiente).

Você pode usar o formato CSV (ou TSV) para seus dados e usar ferramentas dedicadas para manipulá-los. Tal como open-refine ou GNU awk, etc.