Dicas para otimizar as obstruções de desempenho do Excel

Artigo
04/07/2023

Aplica-se a: Excel | Excel 2013 | Office 2016 | VBA

Siga estas dicas para otimizar muitas obstruções de desempenho frequentes no Excel.

Otimizar referências e links

Saiba como melhorar o desempenho relacionado a tipos de referências e links.

Não use referência de encaminhamento e referenciamento para trás

Para aumentar a clareza e evitar erros, crie suas fórmulas para que elas não se encaminhem (à direita ou abaixo) para outras fórmulas ou células. A referência de encaminhamento geralmente não afeta o desempenho do cálculo, exceto em casos extremos para o primeiro cálculo de uma pasta de trabalho, em que pode levar mais tempo para estabelecer uma sequência de cálculo sensata se houver muitas fórmulas que precisam ter seu cálculo adiado.

Minimizar o uso de referências circulares com iteração

Calcular referências circulares com iterações é lento porque são necessários vários cálculos e esses cálculos são de thread único. Frequentemente, você pode "desativar" as referências circulares usando álgebra para que o cálculo iterativo não seja mais necessário. Por exemplo, nos cálculos de fluxo de caixa e juros, tente calcular o fluxo de caixa antes dos juros, calcular os juros e calcular o fluxo de caixa, incluindo os juros.

O Excel calcula referências circulares folha por folha sem considerar dependências. Portanto, você geralmente obtém um cálculo lento se suas referências circulares abrangem mais de uma planilha. Tente mover os cálculos circulares para uma única planilha ou otimizar a sequência de cálculo da planilha para evitar cálculos desnecessários.

Antes do início dos cálculos iterativos, o Excel deve recalcular a pasta de trabalho para identificar todas as referências circulares e seus dependentes. Esse processo é igual a duas ou três iterações do cálculo.

Depois que as referências circulares e seus dependentes são identificados, cada iteração requer que o Excel calcule não apenas todas as células na referência circular, mas também todas as células que dependem das células da cadeia de referência circular, juntamente com células voláteis e seus dependentes. Se você tiver um cálculo complexo que dependa de células na referência circular, poderá ser mais rápido isolar isso em uma pasta de trabalho fechada separada e abri-la para recalculação depois que o cálculo circular tiver convergido.

É importante reduzir o número de células no cálculo circular e o tempo de cálculo que é tomado por essas células.

Evitar links entre pastas de trabalho

Evite links entre pastas de trabalho quando for possível; elas podem ser lentas, facilmente quebradas e nem sempre fáceis de encontrar e corrigir.

Usar menos pastas de trabalho maiores geralmente é, mas nem sempre, melhor do que usar muitas pastas de trabalho menores. Algumas exceções a isso podem ser quando você tem muitos cálculos de front-end que raramente são recalculados que faz sentido colocá-los em uma pasta de trabalho separada ou quando você tem RAM insuficiente.

Tente usar referências simples de célula direta que funcionam em pastas de trabalho fechadas. Ao fazer isso, você pode evitar recalcular todas as pastas de trabalho vinculadas ao recalcular qualquer pasta de trabalho. Além disso, você pode ver os valores que o Excel leu da pasta de trabalho fechada, que é frequentemente importante para depurar e auditar a pasta de trabalho.

Se você não puder evitar o uso de pastas de trabalho vinculadas, tente abri-las todas em vez de fechadas e abra as pastas de trabalho vinculadas antes de abrir as pastas de trabalho vinculadas.

Minimizar links entre planilhas

O uso de muitas planilhas pode facilitar o uso da pasta de trabalho, mas geralmente é mais lento calcular referências a outras planilhas do que referências em planilhas.

Minimizar o intervalo usado

Para salvar a memória e reduzir o tamanho do arquivo, o Excel tenta armazenar informações sobre apenas a área em uma planilha usada. Isso é chamado de intervalo usado. Às vezes, várias operações de edição e formatação estendem o intervalo usado significativamente além do intervalo que você consideraria usado no momento. Isso pode causar obstruções de desempenho e obstruções no tamanho do arquivo.

Você pode marcar o intervalo usado visível em uma planilha usando Ctrl+End. Quando isso for excessivo, você deve considerar excluir todas as linhas e colunas abaixo e à direita de sua última célula usada real e salvar a pasta de trabalho. Crie uma cópia de backup primeiro. Se você tiver fórmulas com intervalos que se estendem ou se referem à área excluída, esses intervalos serão reduzidos em tamanho ou alterados para #N/A.

Permitir dados extras

Quando você frequentemente adiciona linhas ou colunas de dados às suas planilhas, você precisa encontrar uma maneira de fazer com que suas fórmulas do Excel se refiram automaticamente à nova área de dados, em vez de tentar encontrar e alterar suas fórmulas todas as vezes.

Você pode fazer isso usando um grande intervalo em suas fórmulas que se estende muito além dos limites de dados atuais. No entanto, isso pode causar um cálculo ineficiente em determinadas circunstâncias e é difícil manter porque excluir linhas e colunas pode diminuir o intervalo sem que você perceba.

Usar referências de tabela estruturadas (recomendado)

A partir do Excel 2007, você pode usar referências de tabela estruturadas, que expandem e contraem automaticamente à medida que o tamanho da tabela referenciada aumenta ou diminui.

Essa solução tem várias vantagens:

Existem menos desvantagens de desempenho do que as alternativas de referência de coluna inteira e intervalos dinâmicos.
É fácil ter várias tabelas de dados em uma única planilha.
As fórmulas inseridas na tabela também se expandem e se contraem com os dados.

Como alternativa, use referências inteiras de coluna e linha

Uma abordagem alternativa é usar uma referência de coluna inteira, por exemplo , $A:$A. Essa referência retorna todas as linhas na Coluna A. Portanto, você pode adicionar o máximo de dados desejado e a referência sempre os incluirá.

Essa solução tem vantagens e desvantagens:

Muitas funções internas do Excel (SUM, SUMIF) calculam as referências de coluna inteiras com eficiência porque reconhecem automaticamente a última linha usada na coluna. No entanto, funções de cálculo de matriz como SUMPRODUCT não podem lidar com referências de coluna inteiras ou calcular todas as células da coluna.
As funções definidas pelo usuário não reconhecem automaticamente a linha usada pela última vez na coluna e, portanto, calculam com frequência referências de coluna inteiras de forma ineficiente. No entanto, é fácil programar funções definidas pelo usuário para que elas reconheçam a linha usada pela última vez.
É difícil usar referências de coluna inteiras quando você tem várias tabelas de dados em uma única planilha.
No Excel 2007 e versões posteriores, as fórmulas de matriz podem lidar com referências de coluna inteira, mas isso força o cálculo para todas as células da coluna, incluindo células vazias. Isso pode ser lento para calcular, especialmente para 1 milhão de linhas.

Como alternativa, use intervalos dinâmicos

Usando as funções OFFSET ou INDEX e COUNTA na definição de um intervalo nomeado, você pode fazer com que a área que o intervalo nomeado se refere para expandir e contratar dinamicamente. Por exemplo, crie um nome definido usando uma das seguintes fórmulas:

  =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
  =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)+ROW(Sheet1!$A$1) - 1,1)

Quando você usa o nome do intervalo dinâmico em uma fórmula, ele se expande automaticamente para incluir novas entradas.

Usar a fórmula INDEX para um intervalo dinâmico geralmente é preferível à fórmula OFFSET porque OFFSET tem a desvantagem de ser uma função volátil que será calculada a cada recalculação.

O desempenho diminui porque a função COUNTA dentro da fórmula de intervalo dinâmico deve examinar muitas linhas. Você pode minimizar essa diminuição de desempenho armazenando a parte COUNTA da fórmula em uma célula separada ou nome definido e, em seguida, referindo-se à célula ou nome no intervalo dinâmico:

 Counts!z1=COUNTA(Sheet1!$A:$A)
 OffsetDynamicRange=OFFSET(Sheet1!$A$1,0,0,Counts!$Z$1,1)
 IndexDynamicRange=Sheet1!$A$1:INDEX(Sheet1!$A:$A,Counts!$Z$1+ROW(Sheet1!$A$1) - 1,1)

Você também pode usar funções como INDIRECT para construir intervalos dinâmicos, mas o INDIRECT é volátil e sempre calcula o thread único.

Os intervalos dinâmicos têm as seguintes vantagens e desvantagens:

Os intervalos dinâmicos funcionam bem para limitar o número de cálculos executados por fórmulas de matriz.
O uso de vários intervalos dinâmicos em uma única coluna requer funções de contagem de finalidade especial.
Usar muitos intervalos dinâmicos pode diminuir o desempenho.

Melhorar o tempo de cálculo de pesquisa

No Office 365 versão 1809 e versões posteriores, o PROCV, PROCH, CORRESP e MATCH para correspondência exata de dados não classificados do Excel estão mais rápidos do que nunca ao pesquisar em múltiplas colunas (ou linhas com PROCH) do mesmo intervalo da tabela.

Dito isto, para versões anteriores do Excel, as pesquisas continuam sendo obstruções de cálculo frequentemente significativas. Felizmente, há muitas maneiras de melhorar o tempo de cálculo de pesquisa. Se você usar a opção de correspondência exata, o tempo de cálculo da função será proporcional ao número de células examinadas antes de uma correspondência ser encontrada. Para pesquisas em grandes intervalos, esse tempo pode ser significativo.

O tempo de pesquisa usando as opções de correspondência aproximadas de VLOOKUP, HLOOKUP e MATCH em dados classificados é rápido e não é significativamente aumentado pelo comprimento do intervalo que você está procurando. As características são as mesmas da pesquisa binária.

Entender as opções de pesquisa

Verifique se você entende as opções de match-type e range-lookup em MATCH, VLOOKUP e HLOOKUP.

O exemplo de código a seguir mostra a sintaxe da função MATCH . Para obter mais informações, consulte o método Match do objeto WorksheetFunction .

  MATCH(lookup value, lookup array, matchtype)

Matchtype=1 retorna a maior correspondência menor ou igual ao valor de pesquisa quando a matriz de pesquisa é classificada crescente (correspondência aproximada). Se a matriz de pesquisa não estiver classificada como crescente, o MATCH retornará uma resposta incorreta. A opção padrão é a classificação de correspondência aproximada crescente.
Matchtype=0 solicita uma correspondência exata e pressupõe que os dados não estão classificados.
Matchtype=-1 retorna a menor correspondência maior ou igual ao valor de pesquisa se a matriz de pesquisa estiver classificada decrescente (correspondência aproximada).

O exemplo de código a seguir mostra a sintaxe das funções VLOOKUP e HLOOKUP . Para obter mais informações, consulte os métodos VLOOKUP e HLOOKUP do objeto WorksheetFunction .

  VLOOKUP(lookup value, table array, col index num, range-lookup)
  HLOOKUP(lookup value, table array, row index num, range-lookup)

Range-lookup=TRUE retorna a maior correspondência menor ou igual ao valor de pesquisa (correspondência aproximada). Essa é a opção padrão. A matriz de tabela deve ser classificada crescente.
O range-lookup=FALSE solicita uma correspondência exata e pressupõe que os dados não estão classificados.

Evite realizar pesquisas em dados não variados sempre que possível porque ele é lento. Se os dados estiverem classificados, mas você quiser uma correspondência exata, consulte Usar duas pesquisas para obter dados classificados com valores ausentes.

Usar INDEX e MATCH ou OFFSET em vez de VLOOKUP

Tente usar as funções INDEX e MATCH em vez de VLOOKUP. Embora o VLOOKUP seja um pouco mais rápido (aproximadamente 5% mais rápido), mais simples e use menos memória do que uma combinação de MATCH e INDEX ou OFFSET, a flexibilidade adicional que a oferta MATCH e INDEX geralmente permite economizar tempo significativamente. Por exemplo, você pode armazenar o resultado de um MATCH exato em uma célula e reutilizá-lo em várias instruções INDEX .

A função INDEX é rápida e é uma função não volátil, que acelera o recálculo. A função OFFSET também é rápida; no entanto, é uma função volátil, e às vezes aumenta significativamente o tempo necessário para processar a cadeia de cálculo.

É fácil converter VLOOKUP em INDEX e MATCH. As duas instruções a seguir retornam a mesma resposta:

 VLOOKUP(A1, Data!$A$2:$F$1000,3,False)

 INDEX(Data!$A$2:$F$1000,MATCH(A1,$A$1:$A$1000,0),3)

Acelerar pesquisas

Como as pesquisas exatas de correspondência podem ser lentas, considere as seguintes opções para melhorar o desempenho:

Use uma planilha. É mais rápido manter pesquisas e dados na mesma planilha.
Quando puder, classifique os dados primeiro (SORT é rápido) e use correspondência aproximada.
Quando você deve usar uma pesquisa de correspondência exata, restrinja o intervalo de células a serem examinadas ao mínimo. Use tabelas e referências estruturadas ou nomes de intervalo dinâmico em vez de se referir a um grande número de linhas ou colunas. Às vezes, você pode pré-calcular um limite de intervalo inferior e um limite de intervalo superior para a pesquisa.

Usar duas pesquisas para dados classificados com valores ausentes

Duas correspondências aproximadas são significativamente mais rápidas do que uma correspondência exata para uma pesquisa em mais de algumas linhas. (O ponto breakeven é de cerca de 10 a 20 linhas.)

Se você puder classificar seus dados, mas ainda não puder usar correspondência aproximada porque não pode ter certeza de que o valor que você está procurando existe no intervalo de pesquisa, você pode usar esta fórmula:

  IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val, _
      VLOOKUP(lookup_val, lookup_array, column, True), "notexist")

A primeira parte da fórmula funciona fazendo uma pesquisa aproximada na própria coluna de pesquisa.

  VLOOKUP(lookup_val ,lookup_array,1,True)

Você pode marcar se a resposta da coluna de pesquisa for a mesma que o valor de pesquisa (nesse caso, você tem uma correspondência exata) usando a seguinte fórmula:

  IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val,

Se essa fórmula retornar True, você encontrou uma correspondência exata, para que você possa fazer a pesquisa aproximada novamente, mas desta vez, retorne a resposta da coluna desejada.

  VLOOKUP(lookup_val, lookup_array, column, True)

Se a resposta da coluna de pesquisa não corresponder ao valor de pesquisa, você terá um valor ausente e a fórmula retornará "notexist".

Lembre-se de que, se você procurar um valor menor que o menor valor da lista, receberá um erro. Você pode lidar com esse erro usando IFERROR ou adicionando um pequeno valor de teste à lista.

Usar a função IFERROR para dados não variados com valores ausentes

Se você precisar usar a pesquisa de correspondência exata em dados não variados e não tiver certeza se o valor da pesquisa existe, muitas vezes você deve lidar com o #N/A que é retornado se nenhuma correspondência for encontrada. A partir do Excel 2007, você pode usar a função IFERROR , que é simples e rápida.

  IF IFERROR(VLOOKUP(lookupval, table, 2 FALSE),0)

Em versões anteriores, uma maneira simples, mas lenta é usar uma função IF que contém duas pesquisas.

  IF(ISNA(VLOOKUP(lookupval,table,2,FALSE)),0,_
      VLOOKUP(lookupval,table,2,FALSE))

Você pode evitar a pesquisa exata dupla se usar o MATCH exato uma vez, armazenar o resultado em uma célula e testar o resultado antes de fazer um INDEX.

  In A1 =MATCH(lookupvalue,lookuparray,0)
  In B1 =IF(ISNA(A1),0,INDEX(tablearray,A1,column))

Se você não puder usar duas células, use COUNTIF. Geralmente é mais rápido do que uma pesquisa de correspondência exata.

  IF (COUNTIF(lookuparray,lookupvalue)=0, 0, _
      VLOOKUP(lookupval, table, 2 FALSE))

Usar MATCH e INDEX para pesquisas exatas de correspondência em várias colunas

Muitas vezes, você pode reutilizar um MATCH exato armazenado muitas vezes. Por exemplo, se você estiver fazendo pesquisas exatas em várias colunas de resultados, poderá economizar tempo usando um MATCH e muitas instruções INDEX em vez de muitas instruções VLOOKUP .

Adicione uma coluna extra para o MATCH para armazenar o resultado (stored_row) e para cada coluna de resultado use o seguinte:

  INDEX(Lookup_Range,stored_row,column_number)

Como alternativa, você pode usar o VLOOKUP em uma fórmula de matriz. (As fórmulas de matriz devem ser inseridas usando Ctrl+-Shift+Enter. O Excel adicionará o { e } para mostrar que esta é uma fórmula de matriz).

  {VLOOKUP(lookupvalue,{4,2},FALSE)}

Usar INDEX para um conjunto de linhas ou colunas contíguas

Você também pode retornar muitas células de uma operação de pesquisa. Para pesquisar várias colunas contíguas, você pode usar a função INDEX em uma fórmula de matriz para retornar várias colunas ao mesmo tempo (use 0 como o número da coluna). Você também pode usar a função INDEX para retornar várias linhas ao mesmo tempo.

  {INDEX($A$1:$J$1000,stored_row,0)}

Isso retorna a coluna A à coluna J da linha armazenada criada por uma instrução MATCH anterior.

Usar MATCH para retornar um bloco retangular de células

Use as funções MATCH e OFFSET para retornar um bloco retangular de células.

Usar MATCH e INDEX para pesquisa bidimensional

Você pode fazer uma pesquisa de tabela bidimensional usando pesquisas separadas nas linhas e colunas de uma tabela usando uma função INDEX com duas funções MATCH inseridas, uma para a linha e outra para a coluna.

Usar um intervalo de subconjuntos para pesquisa de vários índices

Em planilhas grandes, talvez seja necessário pesquisar com frequência usando vários índices, como pesquisar volumes de produtos em um país/região. Para fazer isso, você pode concatenar os índices e executar a pesquisa usando valores de pesquisa concatenados. No entanto, isso é ineficiente por dois motivos:

A concatenação de cadeias de caracteres é uma operação intensiva em cálculos.
A pesquisa cobrirá um grande intervalo.

Geralmente, é mais eficiente calcular um intervalo de subconjuntos para a pesquisa (por exemplo, encontrando a primeira e última linha para o país/região e procurando o produto dentro desse intervalo de subconjuntos).

Considerar opções para pesquisa tridimensional

Para pesquisar a tabela a ser usada além da linha e da coluna, você pode usar as técnicas a seguir, focando em como fazer o Excel pesquisar ou escolher a tabela.

Se cada tabela que você deseja pesquisar (a terceira dimensão) for armazenada como um conjunto de tabelas estruturadas nomeadas, nomes de intervalo ou como uma tabela de cadeias de caracteres de texto que representam intervalos, você poderá usar as funções CHOOSE ou INDIRECT .

Usar nomes CHOOSE e range pode ser um método eficiente. O CHOOSE não é volátil, mas é mais adequado para um número relativamente pequeno de tabelas. Este exemplo usa TableLookup_Value dinamicamente para escolher qual nome de intervalo (TableName1, TableName2, ...) usar para a tabela de pesquisa.
```
  INDEX(CHOOSE(TableLookup_Value,TableName1,TableName2,TableName3), _
  MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
```
O exemplo a seguir usa a função INDIRECT e TableLookup_Value para criar dinamicamente o nome da planilha a ser usado para a tabela de pesquisa. Esse método tem a vantagem de ser simples e capaz de lidar com um grande número de tabelas. Como o INDIRECT é uma função volátil de thread único, a pesquisa é calculada em cada cálculo, mesmo que nenhum dado tenha sido alterado. Usar esse método é lento.
```
  INDEX(INDIRECT("Sheet" &amp; TableLookup_Value &amp; "!$B$2:$Z$1000"), _
  MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
```
Você também pode usar a função VLOOKUP para localizar o nome da folha ou da cadeia de caracteres de texto a ser usada para a tabela e, em seguida, usar a função INDIRECT para converter o texto resultante em um intervalo.
```
  INDEX(INDIRECT(VLOOKUP(TableLookup_Value,TableOfTAbles,1)),MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
```

Outra técnica é agregar todas as tabelas em uma tabela gigante que tenha uma coluna adicional que identifique as tabelas individuais. Em seguida, você pode usar as técnicas para pesquisa de vários índices mostradas nos exemplos anteriores.

Usar pesquisa curinga

As funções MATCH, VLOOKUP e HLOOKUP permitem que você use os caracteres curinga ? (qualquer caractere único) e * (nenhum caractere ou qualquer número de caracteres) em correspondências exatas alfabéticas. Às vezes, você pode usar esse método para evitar várias correspondências.

Otimizar fórmulas de matriz e SUMPRODUCT

As fórmulas de matriz e a função SUMPRODUCT são poderosas, mas você deve lidar com elas com cuidado. Uma única fórmula de matriz pode exigir muitos cálculos.

A chave para otimizar a velocidade de cálculo das fórmulas da matriz é garantir que o número de células e expressões avaliadas na fórmula da matriz seja o menor possível. Lembre-se de que uma fórmula de matriz é um pouco como uma fórmula volátil: se qualquer uma das células que ela faz referência tiver sido alterada, for volátil ou tiver sido recalculada, a fórmula da matriz calculará todas as células da fórmula e avaliará todas as células virtuais necessárias para fazer o cálculo.

Para otimizar a velocidade de cálculo das fórmulas de matriz:

Tire expressões e referências de intervalo das fórmulas da matriz em colunas e linhas auxiliares separadas. Isso faz muito melhor uso do processo de recalculação inteligente no Excel.
Não referencie linhas completas ou mais linhas e colunas do que você precisa. As fórmulas da matriz são forçadas a calcular todas as referências de célula na fórmula, mesmo que as células estejam vazias ou não usadas. Com 1 milhão de linhas disponíveis a partir do Excel 2007, uma fórmula de matriz que faz referência a uma coluna inteira é extremamente lenta para calcular.
A partir do Excel 2007, use referências estruturadas em que você pode manter o número de células avaliadas pela fórmula da matriz no mínimo.
Em versões anteriores ao Excel 2007, use nomes de intervalo dinâmico sempre que possível. Embora sejam voláteis, vale a pena porque minimizam o tamanho dos intervalos.
Tenha cuidado com fórmulas de matriz que fazem referência a uma linha e a uma coluna: isso força o cálculo de um intervalo retangular.
Use SUMPRODUCT , se possível; é um pouco mais rápido que a fórmula de matriz equivalente.

Considere as opções para usar o SUM para fórmulas de matriz de várias condições

Você sempre deve usar as funções SUMIFS, COUNTIFS e AVERAGEIFS em vez de fórmulas de matriz onde puder, pois elas são muito mais rápidas de calcular. Excel 2016 apresenta funções MAXIFS e MINIFS rápidas.

Em versões anteriores ao Excel 2007, as fórmulas de matriz geralmente são usadas para calcular uma soma com várias condições. Isso é relativamente fácil de fazer, especialmente se você usar o Assistente de Soma Condicional no Excel, mas muitas vezes é lento. Normalmente, há maneiras muito mais rápidas de obter o mesmo resultado. Se você tiver apenas alguns SUMs de várias condições, poderá usar a função DSUM , que é muito mais rápida que a fórmula de matriz equivalente.

Se você precisar usar fórmulas de matriz, alguns bons métodos de aceleração são os seguintes:

Use nomes de intervalo dinâmico ou referências de tabela estruturadas para minimizar o número de células.
Divida as várias condições em uma coluna de fórmulas auxiliares que retornam True ou False para cada linha e referencie a coluna auxiliar em uma FÓRMULA SUMIF ou matriz. Isso pode não parecer reduzir o número de cálculos para uma única fórmula de matriz; no entanto, na maioria das vezes, ele permite que o processo de recalculação inteligente recalcular apenas as fórmulas na coluna auxiliar que precisam ser recalculadas.
Considere concatenar todas as condições em uma única condição e, em seguida, usar SUMIF.
Se os dados puderem ser classificados, conte grupos de linhas e limite as fórmulas da matriz para examinar os grupos de subconjuntos.

Priorizar SUMIFS de várias condições, COUNTIFS e outras funções da família IFS

Essas funções avaliam cada uma das condições da esquerda para a direita por sua vez. Portanto, é mais eficiente colocar a condição mais restritiva em primeiro lugar, de modo que as condições subsequentes só precisem examinar o menor número de linhas.

Considere opções para usar SUMPRODUCT para fórmulas de matriz de várias condições

A partir do Excel 2007, você sempre deve usar as funções SUMIFS, COUNTIFS e AVERAGEIFS e em Excel 2016 funções MAXIFS e MINIFS, em vez de fórmulas SUMPRODUCT sempre que possível.

Em versões anteriores, há algumas vantagens em usar SUMPRODUCT em vez de fórmulas de matriz SUM :

SUMPRODUCT não precisa ser inserido pela matriz usando Ctrl+Shift+Enter.
O SUMPRODUCT geralmente é um pouco mais rápido (5 a 10%).

Use SUMPRODUCT para fórmulas de matriz de várias condições da seguinte maneira:

  SUMPRODUCT(--(Condition1),--(Condition2),RangetoSum)

Neste exemplo, Condition1 e Condition2 são expressões condicionais como $A$1:$A$10000<=$Z4. Como as expressões condicionais retornam True ou False em vez de números, elas devem ser coagidas a números dentro da função SUMPRODUCT . Você pode fazer isso usando dois sinais de menos (--) ou adicionando 0 (+0) ou multiplicando por 1 (x1). O uso -- é um pouco mais rápido que +0 ou x1.

Observe que o tamanho e a forma dos intervalos ou matrizes que são usados nas expressões condicionais e intervalo a serem somados devem ser os mesmos e não podem conter colunas inteiras.

Você também pode multiplicar diretamente os termos dentro do SUMPRODUCT em vez de separá-los por vírgulas:

  SUMPRODUCT((Condition1)*(Condition2)*RangetoSum)

Geralmente, isso é um pouco mais lento do que usar a sintaxe de vírgula e dá um erro se o intervalo a ser somado contiver um valor de texto. No entanto, é um pouco mais flexível, pois o intervalo a ser somado pode ter, por exemplo, várias colunas quando as condições têm apenas uma coluna.

Usar SUMPRODUCT para multiplicar e adicionar intervalos e matrizes

Em casos como cálculos médios ponderados, em que você precisa multiplicar um intervalo de números por outro intervalo de números e somar os resultados, usar a sintaxe de vírgula para SUMPRODUCT pode ser 20 a 25% mais rápido do que uma SOMA inserida na matriz.

  {=SUM($D$2:$D$10301*$E$2:$E$10301)}
  =SUMPRODUCT($D$2:$D$10301*$E$2:$E$10301)
  =SUMPRODUCT($D$2:$D$10301,$E$2:$E$10301)

Todas essas três fórmulas produzem o mesmo resultado, mas a terceira fórmula, que usa a sintaxe de vírgula para SUMPRODUCT, leva apenas cerca de 77% do tempo de cálculo que as outras duas fórmulas precisam.

Esteja ciente de possíveis obstruções de cálculo de função e matriz

O mecanismo de cálculo no Excel é otimizado para explorar fórmulas de matriz e funções que fazem referência a intervalos. No entanto, alguns arranjos incomuns dessas fórmulas e funções podem, às vezes, mas nem sempre, causar um aumento significativo do tempo de cálculo.

Se você encontrar uma obstrução de cálculo que envolve fórmulas de matriz e funções de intervalo, procure o seguinte:

Referências parcialmente sobrepostas.
Fórmulas de matriz e funções de intervalo que fazem referência a parte de um bloco de células que são calculadas em outra fórmula de matriz ou função de intervalo. Essa situação pode ocorrer com frequência na análise de séries temporais.
Um conjunto de fórmulas referenciando por linha e um segundo conjunto de fórmulas referenciando o primeiro conjunto por coluna.
Um grande conjunto de fórmulas de matriz de linha única que abrangem um bloco de colunas, com funções SUM ao pé de cada coluna.

Usar funções com eficiência

As funções estendem significativamente o poder do Excel, mas a maneira como você as usa geralmente pode afetar o tempo de cálculo.

Evitar funções de thread único

A maioria das funções nativas do Excel funciona bem com cálculo multi-threaded. No entanto, sempre que possível, evite usar as seguintes funções de thread único:

UDFs (funções definidas pelo usuário) de VBA e Automação, mas UDFs baseados em XLL podem ser multi threaded
PHONETIC
CÉL quando o argumento "formato" ou "endereço" é usado
INDIRETO
GETPIVOTDATA
MEMBROCUBO
VALORCUBO
PROPRIEDADEMEMBROCUBO
CONJUNTOCUBO
MEMBROCLASSIFICADOCUBO
MEMBROKPICUBO
CONTAGEMCONJUNTOCUBO
ENDEREÇO em que o quinto parâmetro (o sheet_name) é dado
Qualquer função de banco de dados (BDSOMA, BDMÉDIA e assim por diante) que se refere a uma Tabela Dinâmica
TIPO.ERRO
HIPERLINK

Usar tabelas para funções que lidam com intervalos

Para funções como SUM, SUMIF e SUMIFS que lidam com intervalos, o tempo de cálculo é proporcional ao número de células usadas que você está resumindo ou contando. As células não utilizadas não são examinadas, portanto, referências de coluna inteiras são relativamente eficientes, mas é melhor garantir que você não inclua células mais usadas do que você precisa. Use tabelas ou calcule intervalos de subconjuntos ou intervalos dinâmicos.

Reduzir funções voláteis

As funções voláteis podem reduzir o recálculo porque aumentam o número de fórmulas que devem ser recalculadas em cada cálculo.

Geralmente, você pode reduzir o número de funções voláteis usando INDEX em vez de OFFSET e ESCOLHA em vez de INDIRECT. No entanto, OFFSET é uma função rápida e geralmente pode ser usada de maneiras criativas que dão um cálculo rápido.

Usar funções definidas pelo usuário C ou C++

As funções definidas pelo usuário que são programadas em C ou C++ e que usam a API C (funções de suplemento XLL) geralmente executam mais rápido do que as funções definidas pelo usuário que são desenvolvidas usando VBA ou Automação (suplementos XLA ou Automação). Para obter mais informações, confira Desenvolvendo XLLs do Excel 2010.

O desempenho das funções definidas pelo usuário do VBA é sensível à forma como você as programa e as chama.

Usar funções mais rápidas definidas pelo usuário do VBA

Geralmente, é mais rápido usar as funções de planilha e cálculos de fórmula do Excel do que usar funções definidas pelo usuário do VBA. Isso ocorre porque há uma pequena sobrecarga para cada chamada de função definida pelo usuário e informações de transferência significativas do Excel para a função definida pelo usuário. Mas as funções bem projetadas e chamadas definidas pelo usuário podem ser muito mais rápidas do que fórmulas de matriz complexas.

Verifique se você colocou todas as referências às células de planilha nos parâmetros de entrada de função definidos pelo usuário em vez de no corpo da função definida pelo usuário, para que você possa evitar adicionar Application.Volatile desnecessariamente.

Se você precisar ter muitas fórmulas que usam funções definidas pelo usuário, verifique se você está no modo de cálculo manual e se o cálculo é iniciado a partir do VBA. As funções definidas pelo usuário do VBA calculam muito mais lentamente se o cálculo não for chamado do VBA (por exemplo, no modo automático ou quando você pressiona F9 no modo manual). Isso é particularmente verdadeiro quando o Editor do Visual Basic (Alt+F11) está aberto ou foi aberto na sessão atual do Excel.

Você pode capturar f9 e redirecioná-lo para uma sub-rotina de cálculo VBA da seguinte maneira. Adicione essa sub-rotina ao módulo Thisworkbook .

  Private Sub Workbook_Open()
      Application.OnKey "{F9}", "Recalc"
  End Sub

Adicione essa sub-rotina a um módulo padrão.

  Sub Recalc()
      Application.Calculate
      MsgBox "hello"
  End Sub

As funções definidas pelo usuário em suplementos de Automação (Excel 2002 e versões posteriores) não incorrem na sobrecarga do Editor do Visual Basic porque não usam o editor integrado. Outras características de desempenho das funções definidas pelo usuário do Visual Basic 6 em suplementos de Automação são semelhantes às funções VBA.

Se sua função definida pelo usuário processar cada célula em um intervalo, declare a entrada como um intervalo, atribua-a a uma variante que contém uma matriz e loop sobre ela. Se você quiser lidar com referências de coluna inteiras com eficiência, deverá fazer um subconjunto do intervalo de entrada, dividindo-o em sua interseção com o intervalo usado, como neste exemplo.

  Public Function DemoUDF(theInputRange as Range)
      Dim vArr as Variant
      Dim vCell as Variant
      Dim oRange as Range
      Set oRange=Union(theInputRange, theRange.Parent.UsedRange)
      vArr=oRange
      For Each vCell in vArr
          If IsNumeric(vCell) then DemoUDF=DemoUDF+vCell
      Next vCell
  End Function

Se sua função definida pelo usuário estiver usando funções de planilha ou métodos de modelo de objeto do Excel para processar um intervalo, geralmente é mais eficiente manter o intervalo como uma variável de objeto do que transferir todos os dados do Excel para a função definida pelo usuário.

  Function uLOOKUP(lookup_value As Variant, lookup_array As Range, _
                   col_num As Variant, sorted As Variant, _
                   NotFound As Variant)
      Dim vAnsa As Variant
      vAnsa = Application.VLookup(lookup_value, lookup_array, _
                                  col_num, sorted)
      If Not IsError(vAnsa) Then
          uLOOKUP = vAnsa
      Else
          uLOOKUP = NotFound
      End If
  End Function

Se sua função definida pelo usuário for chamada no início da cadeia de cálculo, ela poderá ser passada como argumentos não calculados. Dentro de uma função definida pelo usuário, você pode detectar células não calculadas usando o seguinte teste para células vazias que contêm uma fórmula:

  If ISEMPTY(Cell.Value) AND Len(Cell.formula)>0 then

Existe uma sobrecarga de tempo para cada chamada para uma função definida pelo usuário e para cada transferência de dados do Excel para o VBA. Às vezes, uma função definida pelo usuário de uma fórmula de matriz multicelencial pode ajudá-lo a minimizar essas sobrecargas combinando várias chamadas de função em uma única função com um intervalo de entrada de várias células que retorna um intervalo de respostas.

Minimizar o intervalo de células que a SUM e o SUMIF fazem referência

As funções SUM e SUMIF do Excel são frequentemente usadas em um grande número de células. O tempo de cálculo dessas funções é proporcional ao número de células cobertas, portanto, tente minimizar o intervalo de células que as funções estão fazendo referência.

Usar SUMIF curinga, COUNTIF, SUMIFS, COUNTIFS e outras funções IFS

Use os caracteres curinga ? (qualquer caractere único) e * (nenhum caractere ou qualquer número de caracteres) nos critérios para intervalos alfabéticos como parte das funções SUMIF, COUNTIF, SUMIFS, COUNTIFS e outras funções IFS .

Escolher método para SUMs cumulativos e de data a data

Há dois métodos de fazer SUMs de período a data ou cumulativo. Suponha que os números que você deseja soma cumulativamente estejam na coluna A e que você queira que a coluna B contenha a soma cumulativa; você pode fazer qualquer um dos seguintes procedimentos:

Você pode criar uma fórmula na coluna B como =SUM($A$1:$A2) e arrastá-la para baixo até onde precisar. A célula inicial da SOMA está ancorada no A1, mas como a célula de acabamento tem uma referência de linha relativa, ela aumenta automaticamente para cada linha.
Você pode criar uma fórmula como =$A1 na célula B1 e =$B1+$A2 na célula B2 e arrastá-la até onde precisar. Isso calcula a célula cumulativa adicionando o número dessa linha à SOMA cumulativa anterior.

Para 1.000 linhas, o primeiro método faz com que o Excel faça cerca de 500.000 cálculos, mas o segundo método faz com que o Excel faça apenas cerca de 2.000 cálculos.

Calcular somas de subconjunto

Quando você tem vários índices classificados em uma tabela (por exemplo, Site dentro da Área), muitas vezes você pode economizar tempo de cálculo significativo calculando dinamicamente o endereço de um intervalo de subconjunto de linhas (ou colunas) a ser usado na função SUM ou SUMIF .

Para calcular o endereço de um intervalo de subconjunto de linhas ou colunas:

Conte o número de linhas para cada bloco de subconjunto.
Adicione as contagens cumulativamente para cada bloco para determinar sua linha inicial.
Use OFFSET com a linha inicial e a contagem para retornar um intervalo de subconjunto para a SOMA ou SUMIF que abrange apenas o bloco de subconjunto de linhas.

Usar SUBTOTAL para listas filtradas

Use a função SUBTOTAL para listas filtradas por SUM . A função SUBTOTAL é útil porque, ao contrário da SUM, ela ignora o seguinte:

Linhas ocultas resultantes da filtragem de uma lista. A partir do Excel 2003, você também pode fazer com que o SUBTOTAL ignore todas as linhas ocultas, não apenas linhas filtradas.
Outras funções SUBTOTAL .

Usar a função AGGREGATE

A função AGGREGATE é uma maneira poderosa e eficiente de calcular 19 métodos diferentes de agregação de dados (como SOMA, MEDIAN, PERCENTILE e LARGE). O AGGREGATE tem opções para ignorar linhas ocultas ou filtradas, valores de erro e funções SUBTOTAL e AGGREGATE aninhadas.

Evitar o uso de DFunctions

Os DFunctions DSUM, DCOUNT, DAVERAGE e assim por diante são significativamente mais rápidos do que as fórmulas de matriz equivalentes. A desvantagem das DFunctions é que os critérios devem estar em um intervalo separado, o que os torna impraticáveis de usar e manter em muitas circunstâncias. A partir do Excel 2007, você deve usar funções SUMIFS, COUNTIFS e AVERAGEIFS em vez das DFunctions.

Criar macros VBA mais rápidas

Use as dicas a seguir para criar macros VBA mais rápidas.

Desative tudo, menos o essencial enquanto o código está em execução

Para melhorar o desempenho das macros VBA, desative explicitamente a funcionalidade que não é necessária durante a execução do código. Geralmente, um recalculação ou um redesenho após a execução do código é tudo o que é necessário e pode melhorar o desempenho. Após a execução do código, restaure a funcionalidade no estado original.

A seguinte funcionalidade geralmente pode ser desativada enquanto sua macro VBA é executada:

Application.ScreenUpdating Desativar a atualização da tela. Se Application.ScreenUpdating estiver definido como False, o Excel não redesenhará a tela. Enquanto o código é executado, a tela é atualizada rapidamente e geralmente não é necessário que o usuário veja cada atualização. Atualizar a tela uma vez, após a execução do código, melhora o desempenho.
Application.DisplayStatusBar Desative a barra de status. Se Application.DisplayStatusBar estiver definido como False, o Excel não exibirá a barra de status. A configuração da barra de status é separada da configuração de atualização de tela para que você ainda possa exibir o status da operação atual, mesmo quando a tela não estiver sendo atualizada. No entanto, se você não precisar exibir o status de cada operação, desativar a barra de status enquanto o código for executado também melhorará o desempenho.
Application.Calculation Alternar para cálculo manual. Se Application.Calculation estiver definido como xlCalculationManual, o Excel só calculará a pasta de trabalho quando o usuário iniciar explicitamente o cálculo. No modo de cálculo automático, o Excel determina quando calcular. Por exemplo, sempre que um valor de célula relacionado a uma fórmula é alterado, o Excel recalcula a fórmula. Se você alternar o modo de cálculo para manual, poderá aguardar até que todas as células associadas à fórmula sejam atualizadas antes de recalcular a pasta de trabalho. Ao recalcular apenas a pasta de trabalho quando necessário durante a execução do código, você pode melhorar o desempenho.
Application.EnableEvents Desative eventos. Se Application.EnableEvents estiver definido como False, o Excel não gerará eventos. Se houver suplementos ouvindo eventos do Excel, esses suplementos consumirão recursos no computador enquanto registram os eventos. Se não for necessário que o suplemento registre os eventos que ocorrem durante a execução do código, desativar eventos melhora o desempenho.
ActiveSheet.DisplayPageBreaks Desativar quebras de página. Se ActiveSheet.DisplayPageBreaks estiver definido como False, o Excel não exibirá quebras de página. Não é necessário recalcular quebras de página enquanto o código é executado e calcular as quebras de página após a execução do código melhora o desempenho.

Importante

Lembre-se de restaurar essa funcionalidade no estado original após a execução do código.

O exemplo a seguir mostra a funcionalidade que você pode desativar enquanto sua macro VBA é executada.

  ' Save the current state of Excel settings.
  screenUpdateState = Application.ScreenUpdating
  statusBarState = Application.DisplayStatusBar
  calcState = Application.Calculation
  eventsState = Application.EnableEvents
  ' Note: this is a sheet-level setting.
  displayPageBreakState = ActiveSheet.DisplayPageBreaks 

  ' Turn off Excel functionality to improve performance.
  Application.ScreenUpdating = False
  Application.DisplayStatusBar = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  ' Note: this is a sheet-level setting.
  ActiveSheet.DisplayPageBreaks = False

  ' Insert your code here.

  ' Restore Excel settings to original state.
  Application.ScreenUpdating = screenUpdateState
  Application.DisplayStatusBar = statusBarState
  Application.Calculation = calcState
  Application.EnableEvents = eventsState
  ' Note: this is a sheet-level setting
  ActiveSheet.DisplayPageBreaks = displayPageBreaksState

Ler e gravar grandes blocos de dados em uma única operação

Otimize seu código reduzindo explicitamente o número de vezes que os dados são transferidos entre o Excel e seu código. Em vez de fazer loop pelas células uma de cada vez para obter ou definir um valor, obtenha ou defina os valores em todo o intervalo de células em uma linha, usando uma variante que contém uma matriz bidimensional para armazenar valores conforme necessário. Os exemplos de código a seguir comparam esses dois métodos.

O exemplo de código a seguir mostra um código não otimizado que passa por células uma de cada vez para obter e definir os valores das células A1:C10000. Essas células não contêm fórmulas.

  Dim DataRange as Range
  Dim Irow as Long
  Dim Icol as Integer 
  Dim MyVar as Double 
  Set DataRange=Range("A1:C10000") 

  For Irow=1 to 10000 
      For icol=1 to 3
          ' Read the values from the Excel grid 30,000 times.
          MyVar=DataRange(Irow,Icol) 
          If MyVar > 0 then 
              ' Change the value.
              MyVar=MyVar*Myvar 
              ' Write the values back into the Excel grid 30,000 times.
              DataRange(Irow,Icol)=MyVar
          End If 
      Next Icol 
  Next Irow

O exemplo de código a seguir mostra o código otimizado que usa uma matriz para obter e definir os valores das células A1:C10000 tudo ao mesmo tempo. Essas células não contêm fórmulas.

  Dim DataRange As Variant
  Dim Irow As Long 
  Dim Icol As Integer 
  Dim MyVar As Double 
  ' Read all the values at once from the Excel grid and put them into an array.
  DataRange = Range("A1:C10000").Value2 

  For Irow = 1 To 10000 
      For Icol = 1 To 3 
          MyVar = DataRange(Irow, Icol) 
          If MyVar > 0 Then 
              ' Change the values in the array.
              MyVar=MyVar*Myvar 
              DataRange(Irow, Icol) = MyVar 
          End If 
      Next Icol 
  Next Irow 
  ' Write all the values back into the range at once.
  Range("A1:C10000").Value2 = DataRange

Usar. Value2 em vez de . Valor ou . Texto ao ler dados de um intervalo do Excel

. O texto retorna o valor formatado de uma célula. Isso é lento, pode retornar ### se o usuário ampliar e puder perder a precisão.
. O valor retornará uma moeda VBA ou uma variável de data VBA se o intervalo for formatado como Data ou Moeda. Isso é lento, pode perder precisão e pode causar erros ao chamar funções de planilha.
. O Value2 é rápido e não altera os dados recuperados do Excel.

Evitar selecionar e ativar objetos

Selecionar e ativar objetos é mais intensivo em processamento do que referenciar objetos diretamente. Ao referenciar um objeto como um Range ou uma Forma diretamente, você pode melhorar o desempenho. Os exemplos de código a seguir comparam os dois métodos.

O exemplo de código a seguir mostra o código não otimizado que seleciona cada Forma na planilha ativa e altera o texto para "Olá".

  For i = 0 To ActiveSheet.Shapes.Count
      ActiveSheet.Shapes(i).Select
      Selection.Text = "Hello"
  Next i

O exemplo de código a seguir mostra o código otimizado que faz referência diretamente a cada Forma e altera o texto para "Olá".

  For i = 0 To ActiveSheet.Shapes.Count
      ActiveSheet.Shapes(i).TextEffect.Text = "Hello"
  Next i

Use essas otimizações adicionais de desempenho do VBA

A seguir está uma lista de otimizações de desempenho adicionais que você pode usar em seu código VBA:

Retorne os resultados atribuindo uma matriz diretamente a um Intervalo.
Declare variáveis com tipos explícitos para evitar a sobrecarga de determinar o tipo de dados, possivelmente várias vezes em um loop, durante a execução do código.
Para funções simples que você usa com frequência em seu código, implemente as funções por conta própria no VBA em vez de usar o objeto WorksheetFunction . Para obter mais informações, confira Usar funções mais rápidas definidas pelo usuário do VBA.
Use o método Range.SpecialCells para reduzir o escopo do número de células com as quais o código interage.
Considere os ganhos de desempenho se você implementou sua funcionalidade usando a API C no SDK XLL. Para obter mais informações, confira a Documentação do SDK do Excel 2010 XLL.

Considere o desempenho e o tamanho dos formatos de arquivo do Excel

A partir do Excel 2007, o Excel contém uma grande variedade de formatos de arquivo em comparação com versões anteriores. Ignorando as variações de formato de arquivo Macro, Modelo, Suplemento, PDF e XPS, os três formatos main são XLS, XLSB e XLSX.

Formato XLS

O formato XLS é o mesmo formato das versões anteriores. Ao usar esse formato, você fica restrito a 256 colunas e 65.536 linhas. Quando você salva uma pasta de trabalho do Excel 2007 ou Excel 2010 no formato XLS, o Excel executa uma marcar de compatibilidade. O tamanho do arquivo é quase o mesmo que versões anteriores (algumas informações adicionais podem ser armazenadas) e o desempenho é um pouco mais lento do que as versões anteriores. Qualquer otimização multi threaded que o Excel faz em relação à ordem de cálculo de célula não é salva no formato XLS. Portanto, o cálculo de uma pasta de trabalho pode ser mais lento depois de salvar a pasta de trabalho no formato XLS, fechar e reabrir a pasta de trabalho.
Formato XLSB

XLSB é o formato binário que começa no Excel 2007. Ela é estruturada como uma pasta compactada que contém muitos arquivos binários. É muito mais compacto do que o formato XLS, mas a quantidade de compactação depende do conteúdo da pasta de trabalho. Por exemplo, dez pastas de trabalho mostram um fator de redução de tamanho que varia de dois a oito com um fator de redução médio de quatro. A partir do Excel 2007, abrir e salvar o desempenho é apenas um pouco mais lento que o formato XLS.
Formato XLSX

XLSX é o formato XML que começa no Excel 2007 e é o formato padrão a partir do Excel 2007. O formato XLSX é uma pasta compactada que contém muitos arquivos XML (se você alterar a extensão do nome do arquivo para .zip, você poderá abrir a pasta compactada e examinar seu conteúdo). Normalmente, o formato XLSX cria arquivos maiores que o formato XLSB (1,5 vezes maior em média), mas eles ainda são significativamente menores que os arquivos XLS. Você deve esperar que os tempos de abertura e salvamento sejam um pouco mais longos do que para arquivos XLSB.

Abrir, fechar e salvar pastas de trabalho

Você pode achar que abrir, fechar e salvar pastas de trabalho é muito mais lento do que calculá-las. Às vezes, isso é apenas porque você tem uma pasta de trabalho grande, mas também pode haver outros motivos.

Se uma ou mais pastas de trabalho abrirem e fecharem mais lentamente do que é razoável, ela poderá ser causada por um dos seguintes problemas.

Arquivos temporários

Arquivos temporários podem se acumular no diretório \Windows\Temp (no Windows 95, Windows 98 e Windows ME) ou no diretório \Documents and Settings\User Name\Local Settings\Temp (no Windows 2000 e no Windows XP). O Excel cria esses arquivos para a pasta de trabalho e para controles usados por pastas de trabalho abertas. Programas de instalação de software também criam arquivos temporários. Se o Excel parar de responder por qualquer motivo, talvez seja necessário excluir esses arquivos.

Muitos arquivos temporários podem causar problemas, então você deve ocasionalmente limpo-los. No entanto, se você instalou um software que exige que você reinicie seu computador e ainda não o tenha feito, você deverá reiniciar antes de excluir os arquivos temporários.

Uma maneira fácil de abrir seu diretório temporário é no menu Iniciar do Windows: clique em Iniciar e clique em Executar. Na caixa de texto, digite %temp%e clique em OK.

Acompanhamento de alterações em uma pasta de trabalho compartilhada

O acompanhamento de alterações em uma pasta de trabalho compartilhada faz com que o tamanho do arquivo da pasta de trabalho aumente rapidamente.
Arquivo de troca fragmentado

Verifique se o arquivo de troca do Windows está localizado em um disco que tem muito espaço e que você desfragmenta o disco periodicamente.
Pasta de trabalho com estrutura protegida por senha

Uma pasta de trabalho que tem sua estrutura protegida com uma senha (menu Ferramentas>Proteção>Contra a Pasta de Trabalho> insira a senha opcional) é aberta e fecha muito mais lenta do que uma protegida sem a senha opcional.
Problemas de intervalo usados

Intervalos usados superdimensionados podem causar abertura lenta e aumento do tamanho do arquivo, especialmente se forem causados por linhas ocultas ou colunas com altura ou largura não padrão. Para obter mais informações sobre problemas de intervalo usados, consulte Minimizar o intervalo usado.
Grande número de controles em planilhas

Um grande número de controles (marcar caixas, hiperlinks e assim por diante) em planilhas pode diminuir a velocidade ao abrir uma pasta de trabalho devido ao número de arquivos temporários usados. Isso também pode causar problemas para abrir ou salvar uma pasta de trabalho em uma WAN (ou até mesmo em uma LAN). Se você tiver esse problema, considere reprojetar sua pasta de trabalho.
Grande número de links para outras pastas de trabalho

Se possível, abra as pastas de trabalho às quais você está vinculando antes de abrir a pasta de trabalho que contém os links. Geralmente, é mais rápido abrir uma pasta de trabalho do que ler os links de uma pasta de trabalho fechada.
Configurações do scanner de vírus

Algumas configurações do scanner de vírus podem causar problemas ou lentidão com abertura, fechamento ou salvamento, especialmente em um servidor. Se você acha que esse pode ser o problema, tente desligar temporariamente o scanner de vírus.
Cálculo lento causando abertura lenta e salvamento

Em algumas circunstâncias, o Excel recalcula sua pasta de trabalho quando ela é aberta ou salva. Se o tempo de cálculo da pasta de trabalho for longo e estiver causando um problema, verifique se você tem o cálculo definido como manual e considere desativar a opção calcular antes de salvar (Cálculode Opções>de Ferramentas>).
Arquivos da barra de ferramentas (.xlb)

Verifique o tamanho do arquivo da barra de ferramentas. Um arquivo típico da barra de ferramentas está entre 10 KB e 20 KB. Você pode encontrar seus arquivos XLB pesquisando *.xlb usando a pesquisa do Windows. Cada usuário tem um arquivo XLB exclusivo. Adicionar, alterar ou personalizar barras de ferramentas aumenta o tamanho do arquivo toolbar.xlb. Excluir o arquivo remove todas as personalizações da barra de ferramentas (renomeando-o como "barra de ferramentas. OLD" é mais seguro). Um novo arquivo XLB será criado na próxima vez que você abrir o Excel.

Fazer otimizações de desempenho adicionais

Você pode fazer melhorias de desempenho nas áreas a seguir.

Tabelas Dinâmicas

As tabelas dinâmicas fornecem uma maneira eficiente de resumir grandes quantidades de dados.
- Totais como resultados finais. Se você precisar produzir totais e subtotais como parte dos resultados finais da pasta de trabalho, tente usar tabelas dinâmicas.
- Totais como resultados intermediários. As tabelas dinâmicas são uma ótima maneira de produzir relatórios de resumo, mas tente evitar a criação de fórmulas que usam resultados de Tabela Dinâmica como totais intermediários e subtotais em sua cadeia de cálculo, a menos que você possa garantir as seguintes condições:
- A Tabela Dinâmica foi atualizada corretamente durante o cálculo.
- A Tabela Dinâmica não foi alterada para que as informações ainda fiquem visíveis.
Se você ainda quiser usar tabelas dinâmicas como resultados intermediários, use a função GETPIVOTDATA .
Formatos condicionais e validação de dados

Formatos condicionais e validação de dados são ótimos, mas usar muitos deles pode reduzir significativamente o cálculo. Se a célula for exibida, cada fórmula de formato condicional será avaliada em cada cálculo e quando a exibição da célula que contém o formato condicional é atualizada. O modelo de objeto do Excel tem uma propriedade Worksheet.EnableFormatConditionsCalculation para que você possa habilitar ou desabilitar o cálculo de formatos condicionais.
Nomes definidos

Os nomes definidos são um dos recursos mais poderosos do Excel, mas levam tempo de cálculo adicional. O uso de nomes que se referem a outras planilhas adiciona um nível adicional de complexidade ao processo de cálculo. Além disso, você deve tentar evitar nomes aninhados (nomes que se referem a outros nomes).

Como os nomes são calculados sempre que uma fórmula que se refere a eles é calculada, você deve evitar colocar fórmulas ou funções intensivas em cálculos em nomes definidos. Nesses casos, pode ser significativamente mais rápido colocar sua fórmula ou função com uso intensivo de cálculo em uma célula sobressalente em algum lugar e se referir a essa célula, diretamente ou usando um nome.
Fórmulas que são usadas apenas ocasionalmente

Muitas pastas de trabalho contêm um número significativo de fórmulas e pesquisas que estão preocupadas em colocar os dados de entrada na forma apropriada para os cálculos ou estão sendo usadas como medidas defensivas contra alterações no tamanho ou forma dos dados. Quando você tem blocos de fórmulas que são usados apenas ocasionalmente, você pode copiar e colar valores especiais para eliminar temporariamente as fórmulas ou colocá-las em uma pasta de trabalho separada e raramente aberta. Como os erros de planilha geralmente são causados por não notar que as fórmulas foram convertidas em valores, o método de pasta de trabalho separado pode ser preferível.
Usar memória suficiente

A versão de 32 bits do Excel pode usar até 2 GB de RAM ou até 4 GB de RAM para versões de 32 bits do Excel 2013 e 2016. No entanto, o computador que está executando o Excel também requer recursos de memória. Portanto, se você tiver apenas 2 GB de RAM no computador, o Excel não poderá aproveitar os 2 GB completos porque uma parte da memória é alocada para o sistema operacional e outros programas em execução. Para otimizar o desempenho do Excel em um computador de 32 bits, recomendamos que o computador tenha pelo menos 3 GB de RAM.

A versão de 64 bits do Excel não tem um limite de 2 GB ou até 4 GB. Para obter mais informações, confira a seção "Conjuntos de dados grandes e a versão de 64 bits do Excel" no desempenho do Excel: desempenho e limita melhorias.

Conclusão

Este artigo abordou maneiras de otimizar a funcionalidade do Excel, como links, pesquisas, fórmulas, funções e código VBA para evitar obstruções comuns e melhorar o desempenho.

Confira também

Suporte e comentários

Tem dúvidas ou quer enviar comentários sobre o VBA para Office ou sobre esta documentação? Confira Suporte e comentários sobre o VBA para Office a fim de obter orientação sobre as maneiras pelas quais você pode receber suporte e fornecer comentários.