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


Eu tenho uma planilha do Excel com 5000 linhas e colunas até AY (tamanho 12MB). Exceto pelas seis primeiras colunas, o resto 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.

No 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:

O Excel não possui recursos suficientes para concluir a operação.

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 fazer isso rapidamente?


4


origem


Isso soa como um bom candidato para um aplicativo de banco de dados (acesso ms ou outro) em vez de uma planilha. O Excel realmente não foi feito para lidar com grandes quantidades de dados. - Stewbob


Respostas:


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 precisa fazer nada tão drástico como retrabalho para um banco de dados ou algo semelhante.

De 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.

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 consulta: 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 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.


3





Se houver algum dado em seu arquivo que não precise ser condicional e permaneça o mesmo (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 inteira / row 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.


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.


2





Abra a pasta 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).


2





Eu uso o Office 10 para contornar isso.

  1. Eu abri meu arquivo enorme, salvei-o novamente como um arquivo do tipo Excel workbook 1997-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 para mim.


2





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


1



isso só vai tão longe, infelizmente: o Excel não vai tirar vantagem de mais de um processador ou núcleo do processador :( - warren


Eu concordo com 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.


0





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

A maneira que eu agorei as coisas por um fator de 15 foi primeiro fazendo a consulta selecionar os dados necessários em vez de fazer o Excel filtrá-lo. 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, de modo que meu arquivo não estava crescendo continuamente em tamanho com as grandes quantidades de dados que eu posso estar consultando.

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


0





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.

Suspeito, no seu primeiro post, que foi isso 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?


0