Planilha enorme do Excel demorando muito para atualizar links ou calcular fórmulas

Question

Eu tenho uma planilha do Excel com 5000 linhas e colunas até AY (tamanho 12MB). Exceto pelas seis primeiras colunas, o restante contém vlookups ou outras fórmulas. Todos os vlookups estão em uma planilha do Excel separada. Eu alterei a configuração do Excel para atualizar manualmente os links e calcular fórmulas. Agora, toda vez que tento atualizar os links, o Excel trava ou demora cerca de 15 minutos.

Neste momento, estou trabalhando para dividir a planilha em quatro instâncias; onde atualizarei quatro planilhas diferentes do Excel em seqüência. Mas é necessário copiar manualmente as colunas do índice para o próximo estágio. Demora 3-4 horas para atualizar os links. Ontem comecei a receber mensagens de erro que:

Excel does not enough resources to complete the operation.

Não tive escolha, mas dividir toda a operação em quatro pequenos passos que requerem intervenção manual.

Eu olhei para o índice. Não é realmente aplicável no meu caso. Alguma idéia de como posso fazê-lo rapidamente?

    

Answer 1

5000 linhas por 50 colunas não é uma planilha grande. 12MB é um tamanho de arquivo pequeno / médio perfeitamente razoável e você não deve precisar fazer nada tão drástico como retrabalho para um banco de dados ou algo semelhante.

A partir da sua descrição, o problema parece ser os links. Quão grandes são as pastas de trabalho referenciadas? Eles estão recalculando quando você se conecta a eles? Eu gosto da ideia de pré-abertura: você pode ver o que está acontecendo.

O seu uso de memória (veja o Gerenciador de Tarefas) está excedendo sua RAM física disponível? Uma vez que a memória tem que ser trocada para o disco, as coisas começam a desacelerar muito.

Além da ajuda de pré-abertura e classificação, sugiro dar uma olhada nas fórmulas que não são de pesquisa: qualquer uma pode ser convertida em fórmulas de matriz e chamada uma vez por cálculo, em vez de uma vez por linha?

Na coisa do VLOOKUP: você já tentou usar INDEX(value_range, MATCH(lookup_key, key_range)) ? Existem situações em que é mais rápido.

Você não diz se tem algum VBA. Se fizer isso, observe quantas vezes as funções do VBA são chamadas e quanto tempo elas levam. Código VBA que faz muita referência a objetos do Excel pode ser caro.

    

Answer 2

Se houver dados em seu arquivo que não precisem ser condicionais e permaneçam os mesmos (por exemplo, a data de nascimento de uma pessoa), após a primeira vlookup para obter os dados, 'Copiar' e 'Colar especial - Valores' a coluna / linha inteira para impedir que o arquivo recalcule essas células toda vez que você executar o cálculo. Executar um vlookup em dados provenientes de outro vlookup que eu notei leva algum tempo.

    

Answer 3

Infelizmente, é assim com as planilhas do Excel ... O VBA não é exatamente a opção mais rápida por aí.

Você pode querer considerar a migração dos dados para um banco de dados do Microsoft Access.

    

Answer 4

Abra a (s) pasta (s) de trabalho que você está vinculando antes de abrir a pasta de trabalho que contém os links.

E classifique os dados e use a opção classificada em VLOOKUP (use True para o último parâmetro).

    

Answer 5

Eu uso o Office 10 para contornar isso.

1. Eu abri meu arquivo enorme, salvei-o novamente como um arquivo do Excel 97-2003.
   • Ele solicitou uma mensagem me perguntando algo (não me lembro).
2. Eu cliquei em OK e tive um novo arquivo do Excel 1997-2003.
   • O arquivo passou de 25 MB para menos de 1 MB.
3. Então eu apenas salvei novamente desta vez do antigo arquivo 1997-2003 para uma nova pasta de trabalho e ficou ainda menor.

Eu tive esse problema com várias pastas de trabalho e isso sempre resolveu isso para mim.

    

Answer 6

Talvez obtenha um computador mais rápido ou atualize sua RAM

    

Answer 7

Concordo com o Mike Woodhouse. Isso não é um monte de dados.

Como teste, eu faria uma nova planilha com os dados da planilha mesclada em uma nova planilha. Isso informará se o problema está acontecendo porque os dados estão em uma planilha separada. Isso não deve ser um problema, mas será um bom teste.

    

Answer 8

Não sei se isso é de alguma ajuda para sua situação, pois envolve links de banco de dados, mas tive o mesmo problema, exceto que estava demorando muito para atualizar uma planilha executando uma consulta de banco de dados.

A maneira que eu aumentei as coisas por um fator de 15 foi primeiro fazendo a consulta selecionar os dados necessários em vez de fazer o Excel filtrá-los. Em segundo lugar, na janela Connection Properties da minha conexão DB, marquei a opção "Remover dados do intervalo de dados externo antes de salvar a pasta de trabalho". Isso ajudou tremendamente para que meu arquivo não crescesse continuamente em tamanho com as maiores quantidades de dados que eu possa estar pesquisando.

Agora, toda a atualização demora cerca de 2-3 segundos, em vez de 30 a 45.

    

Answer 9

Na minha experiência, descobri que vincular arquivos externos é o processo mais lento de todos. Eu li que isso é devido ao Excel ter que re-atualizar o modelo de cálculo de cada vez através de um número de diferentes arquivos vinculados, em vez de apenas um.

Eu suspeito de seu primeiro post que isso foi o que estava retardando o cálculo para começar. Você tentou mover todos os dados para o mesmo arquivo de trabalho e atualizar todos os links para serem internos ao arquivo?