Se sua planilha está lenta, travando ou demora para responder, este artigo é para você! Hoje iremos aprender a como melhorar o desempenho da sua planilha. São dicas de uso de fórmulas, configurações e se você usa VBA, também deixamos algumas dicas para melhorar o desempenho que irá fazer você economizar um tempo precioso nas suas análises com o ganho de produtividade que você irá conseguir.
Para desempenho, use todo poder de processamento!
Por padrão, o Excel já utiliza todo o poder de processamento do computador ao calcular uma planilha, porém, em alguns casos, é possível que esta opção esteja desativada. Para verificar, acesse Arquivo -> Opções -> Acesse o campo Avançado -> Procure o subtítulo “Formulas” e verifique se os cálculos multithread estão ativos, e marque a opção de usar todos os processadores neste computador, conforme imagem abaixo.
Agora o Excel está usando todos os processadores disponíveis, no seu sistema a quantidade de processadores disponível pode variar.
Evite usar muitas fórmulas voláteis
Fórmulas voláteis são recalculadas toda vez que há uma alteração na planilha, com isso, o uso de muitas formulas pode ocasionar lentidão nos cálculos da planilha. Devemos evitar referenciar essas formulas em planilhas com grande volume de dados, por exemplo: se usarmos a formula HOJE dentro de um PROCV, toda vez que alterarmos uma célula, este PROCV será recalculado. Abaixo, estão algumas fórmulas voláteis:
=HOJE() , =AGORA(), =DESLOC() , =ALEATÓRIO(), =ALEATÓRIOENTRE(), =INFORMAÇÕES(), =INDIRETO(), =CÉLULA()
Tome cuidado com fórmulas matriciais!
As fórmulas matriciais, como diz o nome, utiliza matrizes para fazer os cálculos. Essas fórmulas são bastante úteis quando temos que fazer testes lógicos em várias células para obtermos o resultado.
Esse tipo de fórmula é uma ótima solução para uma quantidade relativamente baixa de dados, porém, quando usadas em uma quantidade grande de colunas e linhas, os cálculos da planilha podem ficar extremamente lentos.
Uma solução, se você sempre utilizar este tipo de fórmula nos mesmos intervalos seria utilizar VBA para fazer os cálculos e deixar os dados estáticos (deixarei um exemplo abaixo), ou selecionar os intervalos com o as fórmulas, copiar e colar valores para deixas os mesmos estáticos sem a necessidade de VBA. Uma outra solução seria desativar os cálculos automáticos e deixá-los em manual.
Algumas fórmulas já são matriciais por padrão, são elas SOMASE, SOMASES, CONTSE, CONTSES, etc.
Utilize o modo de cálculo manual
Se você tem uma grande quantidade de dados com fórmulas complexas em sua planilha e a mesma fica muito tempo calculando, talvez uma das soluções será desativar o modo de cálculo automático.
O Excel, por padrão, utiliza o cálculo automático, este recurso é ótimo para termos o resultado assim que editamos alguma fórmula ou algum dado, porém, quando temos muitas fórmulas complexas, o tempo de processamento pode ser muito grande, com isso, podemos mudar para o modo de cálculo manual.
Para alterar, vamos na guia Fórmulas -> Opções de Cálculo -> Manual
Use as fórmulas de maneira eficaz!
Algumas fórmulas são semelhantes e podem nos retornar o mesmo resultado, porém, pensando em desempenho, devemos utilizar a mais adequada a situação. Por exemplo, em casos que usamos o SE para ignorar números negativos, podemos utilizar a fórmula MÁXIMO, pois, o SE irá fazer o cálculo duas vezes para testar a condição, a fórmula MÁXIMO fará uma vez só, o que a deixará bem rápida. O mesmo serve para a fórmula MÍNIMO, para ignorarmos valores positivos. Exemplo de uso:
Além das fórmulas, uma outra maneira de ganhar desempenho é evitar utilizar colunas inteiras por exemplo “A:B” para realizar os cálculos, ao invés disso, podemos colocar o intervalo exato da tabela de dados, por exemplo “A1:B300”.
Exemplo de fórmula que utiliza toda a coluna:
Exemplo de fórmula que utiliza somente o intervalo necessário:
Planilha binária, pouco conhecida, mas muito útil.
Normalmente nós salvamos nossa planilha no formato .XLSX, esse formato é o padrão do Excel. Quando salvamos neste formato, o Excel compacta este arquivo com vários outros arquivos do XML aberto, que, dependendo da quantidade de dados da sua planilha, o arquivo acaba ficando bastante pesado, além disso, não é possível salvar macros neste formato.
Por outro lado, como o próprio nome diz, a planilha binária tem seus dados salvos no formato binário, este formato tem a vantagem de ser mais leve que o formato .XLSX, em alguns casos, o arquivo pode ficar até 50% menor e também é aberta em menos tempo, além de poder ter macros. A desvantagem deste formato é que ele não tem compatibilidade com grande parte de outros programas, o que pode ser um problema se você os utiliza para importar os dados de sua planilha.
Aumentando a velocidade de cálculo do seu código VBA
Se você usa VBA, também é possível aumentar a velocidade das operações de cálculo. Abaixo, explicaremos o qual a função dos códigos e mostraremos um exemplo usando o SOMASE, mostrando como é possível substituir as fórmulas por valores estáticos e explicaremos passo a passo qual a função de cada comando, você também pode copiar o código da planilha de exemplo para sua pasta de trabalho e fazer o teste ou alterar conforme sua necessidade:
Os códigos e suas funções:
Application.ScreenUpdating: Neste código é possível desativar a atualização da tela, com ele desativado, o Excel não acompanha a execução do código na planilha, com isso ganhamos bastante tempo em códigos complexos.
Sub exemplo()
Application.ScreenUpdating = False
‘Código
Application.ScreenUpdating = True
End sub
Application.Calculation: Com este código é possível desativarmos o cálculo automático, deixando a planilha com a execução dos códigos mais rápida.
Sub exemplo()
Application.Calculation = xlCalculationManual
‘Código
Application.Calculation = xlCalculationAutomatic
End sub
Application.DisplayAlerts: Este código possibilita desativarmos os alertas que possam aparecer na planilha, o que evita interações desnecessárias na execução do código.
Sub exemplo()
Application.DisplayAlerts = False
‘Código
Application.DisplayAlerts = True
End sub
Lembrando, que desativamos a função no começo do código e depois reativamos no final do código, assim evitamos algum problema após a execução.
Código VBA utilizando SOMASE
No exemplo de código, vamos simular uma situação em que queremos substituir as formulas SOMASE pelo cálculo estático, assim, além de automatizarmos essa tarefa, a planilha não ficará calculando toda vez. Exemplo:
Abaixo está o código. O mesmo estará na planilha de exemplo:
