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

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

Siga estas dicas para otimizar muitas obstruções de desempenho que ocorrem com frequência no Excel.

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

Não use referência direta e referência inversa

Para aumentar a clareza e evitar erros, projete suas fórmulas para que elas não façam referência direta (à direita ou abaixo) a outras fórmulas ou células. A referência direta geralmente não afeta o desempenho do cálculo, exceto em casos extremos para o primeiro cálculo de uma pasta de trabalho, onde 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

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

O Excel calcula referências circulares planilha por planilha sem considerar as dependências. Portanto, você geralmente obtém cálculos lentos se suas referências circulares abrangem mais de uma planilha. Tente mover os cálculos circulares para uma única planilha ou otimize a sequência de cálculos 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 exige que o Excel calcule não apenas todas as células na referência circular, mas também quaisquer células que dependam das células na cadeia de referência circular, juntamente com as células voláteis e seus dependentes. Se você tiver um cálculo complexo que depende de células na referência circular, talvez seja mais rápido isolá-lo em uma pasta de trabalho fechada separada e abri-la para recálculo após o cálculo circular ter convergido.

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

Evite links entre pastas de trabalho quando possível; Eles podem ser lentos, facilmente quebrados e nem sempre fáceis de encontrar e consertar.

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 são tão raramente recalculados que faz sentido colocá-los em uma pasta de trabalho separada ou quando você não tem RAM suficiente.

Tente usar referências de célula diretas simples que funcionem em pastas de trabalho fechadas. Ao fazer isso, você pode evitar o recálculo de todas as pastas de trabalho vinculadas ao recalcular qualquer pasta de trabalho. Além disso, você pode ver os valores que o Excel leu na pasta de trabalho fechada, o que é frequentemente importante para depuração e auditoria da 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 abri-las das quais estão vinculadas.

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 as referências dentro das planilhas.

Minimizar o intervalo usado

Para economizar memória e reduzir o tamanho do arquivo, o Excel tenta armazenar informações apenas sobre a área em uma planilha que foi 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 atualmente. 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, exclua todas as linhas e colunas abaixo e à direita da última célula usada real e, em seguida, salve a pasta de trabalho. Crie uma cópia de backup primeiro. Se você tiver fórmulas com intervalos que se estendem até ou se referem à área excluída, esses intervalos serão reduzidos em tamanho ou alterados para #N/A.

Permitir dados adicionais

Quando você adiciona frequentemente linhas ou colunas de dados às planilhas, precisa encontrar uma maneira de fazer com que suas fórmulas do Excel se refiram automaticamente à nova área de dados, em vez de tentar localizar 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 de manter, pois a exclusão de linhas e colunas pode diminuir o intervalo sem que você perceba.

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

Esta 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 de coluna e linha inteiras

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

Esta solução tem vantagens e desvantagens:

  • Muitas funções internas do Excel (SOMA, SOMASE) calculam referências de coluna inteira com eficiência porque reconhecem automaticamente a última linha usada na coluna. No entanto, funções de cálculo de matriz como SOMARPRODUTO não podem lidar com referências de coluna inteira ou calcular todas as células na coluna.

  • As funções definidas pelo usuário não reconhecem automaticamente a última linha usada na coluna e, portanto, frequentemente calculam referências de coluna inteira de forma ineficiente. No entanto, é fácil programar funções definidas pelo usuário para que reconheçam a última linha usada.

  • É difícil usar referências de coluna inteira 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 na coluna, incluindo células vazias. O cálculo pode ser lento, 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 à qual o intervalo nomeado se refere se expanda e contraia 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.

O uso da 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 recálculo.

O desempenho diminui porque a função CONT.VALORES dentro da fórmula de intervalo dinâmico deve examinar muitas linhas. Você pode minimizar essa diminuição de desempenho armazenando a parte CONT.VALORES da fórmula em uma célula separada ou em um nome definido e, em seguida, fazendo referência à célula ou ao 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 INDIRETO para construir intervalos dinâmicos, mas INDIRETO é volátil e sempre calcula com um único thread.

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.

  • O uso de 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 isso, para versões anteriores do Excel, as pesquisas continuam a ser frequentemente obstruções de cálculo significativas. Felizmente, há muitas maneiras de melhorar o tempo de cálculo da 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 verificadas antes que uma correspondência seja encontrada. Para pesquisas em intervalos grandes, esse tempo pode ser significativo.

O tempo de pesquisa usando as opções de correspondência aproximada de PROCV,PROCH e CORRESP em dados classificados é rápido e não aumenta significativamente 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

Certifique-se de entender as opções de tipo de correspondência e intervalo em CORRESP, PROCV e PROCH.

O exemplo de código a seguir mostra a sintaxe da função CORRESP . 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 em ordem crescente (correspondência aproximada). Se a matriz de pesquisa não estiver classificada em ordem crescente, CORRESP retornará uma resposta incorreta. A opção padrão é a correspondência aproximada classificada em ordem crescente.

  • Matchtype=0 solicita uma correspondência exata e pressupõe que os dados não estão classificados.

  • Matchtype=-1 retornará a menor correspondência maior ou igual ao valor de pesquisa se a matriz de pesquisa for classificada em ordem decrescente (correspondência aproximada).

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

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

  • Procurar=VERDADEIRO retorna a maior correspondência menor que ou igual ao valor de pesquisa (correspondência aproximada). Essa é a opção padrão. A matriz de tabela deve ser classificada em ordem crescente.

  • Procurar=FALSO: solicita uma correspondência exata e pressupõe que os dados não estão classificados.

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

Usar ÍNDICE e CORRESP ou DESLOCAMENTO em vez de PROCV

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

A função ÍNDICE é rápida e não volátil, o 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 PROCV para ÍNDICE e CORRESP. 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 de correspondência exata 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 (CLASSIFICAR é rápido) e use a correspondência aproximada.

  • Quando você precisar usar uma pesquisa de correspondência exata, restrinja ao mínimo o intervalo de células a ser verificado. Use tabelas e referências estruturadas ou nomes de intervalos dinâmicos em vez de fazer referência 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 do que algumas linhas. (O ponto de equilíbrio é de cerca de 10 a 20 linhas.)

Se você puder classificar seus dados, mas ainda não puder usar a correspondência aproximada porque não pode ter certeza de que o valor que está pesquisando existe no intervalo de pesquisa, você poderá 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 é igual ao 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 esta fórmula retornar Verdadeiro, você encontrou uma correspondência exata, portanto pode 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ê tem um valor ausente e a fórmula retorna "notexist".

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

Use a função SEERRO para dados não classificados com valores ausentes

Se você precisar usar a pesquisa de correspondência exata em dados não classificados e não puder ter certeza de que o valor de pesquisa existe, geralmente deverá lidar com o #N/A que é retornado se nenhuma correspondência for encontrada. A partir do Excel 2007, você pode usar a função SEERRO , 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 CORRESP exato uma vez, armazenar o resultado em uma célula e depois testar o resultado antes de fazer um ÍNDICE.

  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 CONT.SE. Geralmente, é mais rápido do que uma pesquisa de correspondência exata.

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

Usar CORRESP e ÍNDICE para pesquisas de correspondência exata 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 resultado, poderá economizar tempo usando uma instrução MATCH e muitas instruções INDEX em vez de muitas instruções VLOOKUP .

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

  INDEX(Lookup_Range,stored_row,column_number)

Alternativamente, você pode usar PROCV 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)}

Use ÍNDICE 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 ÍNDICE em uma fórmula de matriz para retornar várias colunas de uma só vez (use 0 como o número da coluna). Você também pode usar a função INDEX para retornar várias linhas de uma vez.

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

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

Usar CORRESP para retornar um bloco retangular de células

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

Usar CORRESP e ÍNDICE para pesquisa bidimensional

Você pode fazer uma pesquisa de tabela bidimensional com eficiência usando pesquisas separadas nas linhas e colunas de uma tabela usando uma função ÍNDICE com duas funções CORRESP incorporadas, 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 realizar a pesquisa usando valores de pesquisa concatenados. No entanto, isso é ineficiente por dois motivos:

  • A concatenação de cadeias de caracteres é uma operação de cálculo intensivo.

  • A pesquisa cobrirá uma grande variedade.

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

Considere as opções de pesquisa tridimensional

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

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

  • Usar ESCOLHER e nomes de intervalo pode ser um método eficiente. ESCOLHER não é volátil, mas é mais adequado para um número relativamente pequeno de tabelas. Este exemplo usa TableLookup_Value dinamicamente para escolher o nome do intervalo (TableName1, TableName2, ...) a ser usado 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 INDIRETO e TableLookup_Value cria dinamicamente o nome da planilha a ser usado para a tabela de pesquisa. Este método tem a vantagem de ser simples e capaz de lidar com um grande número de tabelas. Como INDIRETO é uma função de thread único volátil, a pesquisa é de thread único calculada a cada cálculo, mesmo que nenhum dado tenha sido alterado. O uso deste método é lento.

      INDEX(INDIRECT("Sheet" & TableLookup_Value & "!$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 PROCV para localizar o nome da planilha ou a cadeia de texto a ser usada para a tabela e, em seguida, usar a função INDIRETO 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 consiste em 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 de pesquisa de vários índices mostradas nos exemplos anteriores.

Usar pesquisa curinga

As funções CORRESP, PROCV e PROCH 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 SOMARPRODUTO

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

A chave para otimizar a velocidade de cálculo de fórmulas de matriz é garantir que o número de células e expressões avaliadas na fórmula de 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 às quais ela faz referência tiver sido alterada, volátil ou recalculada, a fórmula de 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:

  • Remova expressões e referências de intervalo das fórmulas de matriz em colunas e linhas auxiliares separadas. Isso faz um uso muito melhor do processo de recálculo inteligente no Excel.

  • Não faça referência a linhas completas ou a mais linhas e colunas do que o necessário. As fórmulas de 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 sejam 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 nas quais você pode manter o número de células avaliadas pela fórmula de 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 do que a fórmula de matriz equivalente.

Considere as opções de uso de SOMA para fórmulas de matriz de várias condições

Você deve sempre usar as funções SOMASES, CONT.SES e MÉDIASES em vez de fórmulas de matriz onde puder, pois elas são muito mais rápidas de calcular. O Excel 2016 introduz as funções MÁXIMOSES e MÍNIMOSES rápidas.

Em versões anteriores ao Excel 2007, as fórmulas de matriz são frequentemente 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 geralmente é lento. Normalmente, existem maneiras muito mais rápidas de obter o mesmo resultado. Se você tiver apenas algumas SOMAS de várias condições, poderá usar a função BDSOMA , que é muito mais rápida do que a fórmula de matriz equivalente.

Se você precisar usar fórmulas de matriz, alguns bons métodos para acelerá-las são os seguintes:

  • Use nomes de intervalos dinâmicos ou referências de tabela estruturada para minimizar o número de células.

  • Divida as várias condições em uma coluna de fórmulas auxiliares que retornam Verdadeiro ou Falso para cada linha e, em seguida, faça referência à coluna auxiliar em uma fórmula SOMASE 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 Recálculo Inteligente recalcule 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 a SOMASE.

  • Se os dados puderem ser classificados, conte os grupos de linhas e limite as fórmulas de matriz para examinar os grupos de subconjuntos.

Priorize SOMASES, CONT.SES e outras funções da família IFS de várias condições

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 primeiro, para que as condições subsequentes precisem examinar apenas o menor número de linhas.

Considere as opções de uso de SOMARPRODUTO para fórmulas de matriz de várias condições

A partir do Excel 2007, você deve sempre usar as funções SOMASES, CONT.SES e MÉDIASES e, no Excel 2016, as funções MÁXIMOSES e MÍNIMOSES, em vez de fórmulas SOMARPRODUTO, sempre que possível.

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

  • SOMARPRODUTO não precisa ser inserido por matriz usando-se Ctrl+Shift+Enter.

  • SOMARPRODUTO geralmente é um pouco mais rápido (5 a 10%).

Use SOMARPRODUTO 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 Verdadeiro ou Falso em vez de números, elas devem ser forçadas a usar números dentro da função SOMARPRODUTO . Você pode fazer isso usando dois sinais de menos (--), 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 usados nas expressões condicionais e no intervalo a ser somado devem ser os mesmos e não podem conter colunas inteiras.

Você também pode multiplicar diretamente os termos dentro de SOMARPRODUTO 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 apresentará um erro se o intervalo a ser somado contiver um valor de texto. No entanto, ele é 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 SOMARPRODUTO para multiplicar e adicionar intervalos e matrizes

Em casos como cálculos de média ponderada, em que você precisa multiplicar um intervalo de números por outro intervalo de números e somar os resultados, o uso da sintaxe de vírgula para SOMARPRODUTO pode ser 20 a 25% mais rápido do que uma SOMA inserida pela 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)

Essas três fórmulas produzem o mesmo resultado, mas a terceira fórmula, que usa a sintaxe de vírgula para SOMARPRODUTO, 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 matriz e função

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 no tempo de cálculo.

Se você encontrar uma obstrução de cálculo que envolva 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 calculado 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 que faz referência a uma linha e um segundo conjunto de fórmulas que faz referência ao primeiro conjunto por coluna.

  • Um grande conjunto de fórmulas de matriz de linha única que abrange um bloco de colunas, com funções de SOMA no rodapé de cada coluna.

Use funções com eficiência

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

Evitar funções com um único thread

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

  • Funções definidas pelo usuário (UDFs) de VBA e automação, mas UDFs baseadas em GLL podem ser multithread
  • PHONETIC
  • CÉL quando o argumento "formato" ou "endereço" é usado
  • INDIRETO
  • GETPIVOTDATA
  • MEMBROCUBO
  • VALORCUBO
  • PROPRIEDADEMEMBROCUBO
  • CONJUNTOCUBO
  • MEMBROCLASSIFICADOCUBO
  • MEMBROKPICUBO
  • CONTAGEMCONJUNTOCUBO
  • ADDRESS onde o quinto parâmetro (o sheet_name) é fornecido
  • 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 manipulam intervalos

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

Reduzir funções voláteis

As funções voláteis podem retardar 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 ÍNDICE em vez de DESLOCAMENTO e ESCOLHER em vez de INDIRETO. No entanto, OFFSET é uma função rápida e muitas vezes pode ser usada de maneiras criativas que fornecem cálculos rápidos.

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 rapidamente do que funções definidas pelo usuário que são desenvolvidas usando VBA ou Automação (XLA ou suplementos de Automação). Para obter mais informações, consulte 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 definidas pelo usuário do VBA mais rápidas

Normalmente, é mais rápido usar os cálculos de fórmula do Excel e as funções de planilha do que usar as 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 uma sobrecarga significativa transferindo informações do Excel para a função definida pelo usuário. Mas funções bem projetadas e chamadas de funções definidas pelo usuário podem ser muito mais rápidas do que fórmulas de matriz complexas.

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

Se você precisar ter muitas fórmulas que usam funções definidas pelo usuário, verifique se 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 interceptar F9 e redirecioná-lo para uma sub-rotina de cálculo VBA da seguinte maneira. Adicione esta sub-rotina ao módulo Thisworkbook .

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

Adicione esta 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 do 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 contenha uma matriz e faça um loop nela. Se você quiser lidar com referências de colunas inteiras com eficiência, deverá criar 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 a 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 fórmula de matriz com várias células pode ajudar 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.

Minimize o intervalo de células a que SOMA e SOMASE fazem referência

As funções SOMA e SOMASE do Excel são usadas frequentemente em várias células. O tempo de cálculo para essas funções é proporcional ao número de células cobertas, portanto, tente minimizar o intervalo de células que as funções estão referenciando.

Usar caracteres curinga SOMASE, CONT.SE, SOMASES, CONT.SES e outras funções SES

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 da função SOMASE, CONT.SE, SOMASES, CONT.SES e outras funções SES .

Escolha o método para SUMs cumulativas e do período até a data

Existem dois métodos de fazer SUMs cumulativas ou do período até a data. Suponha que os números que você deseja somar cumulativamente estejam na coluna A e você queira que a coluna B contenha a soma cumulativa; Você pode fazer o seguinte:

  • Você pode criar uma fórmula na coluna B =SUM($A$1:$A2) e arrastá-la para baixo o quanto for necessário. A célula inicial da SOMA está ancorada em A1, mas como a célula final tem uma referência de linha relativa, ela aumenta automaticamente para cada linha.

  • Você pode criar uma fórmula, como =$A1 nas células B1 e =$B1+$A2 B2, e arrastá-la para baixo o quanto for necessário. 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 subconjuntos

Quando você tem vários índices classificados em uma tabela (por exemplo, Site dentro da Área), geralmente pode economizar tempo de cálculo significativo calculando dinamicamente o endereço de um subconjunto de linhas (ou colunas) para usar na função SOMA ou SOMASE .

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

  1. Conte o número de linhas para cada bloco de subconjunto.

  2. Adicione as contagens cumulativamente de cada bloco para determinar sua linha inicial.

  3. Use DESLOC com a linha inicial e a contagem para retornar um intervalo de subconjunto para a SOMA ou SOMASE que abrange apenas o bloco de linhas do subconjunto.

Usar SUBTOTAL para listas filtradas

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

  • Linhas ocultas resultantes da filtragem de uma lista. A partir do Excel 2003, também é possível fazer com que SUBTOTAL ignore todas as linhas ocultas, não apenas as linhas filtradas.

  • Outras funções de SUBTOTAL .

Usar a função AGREGAR

A função AGREGAR é uma maneira poderosa e eficiente de calcular 19 métodos diferentes de agregação de dados (como SOMA, MÉDIA, PERCENTIL e MAIOR). AGREGAR tem opções para ignorar linhas ocultas ou filtradas, valores de erro e funções SUBTOTAL e AGREGAR aninhadas.

Evite usar DFunctions

As DFunctions DSUM,DCOUNT,DAVERAGE, etc. são significativamente mais rápidas do que 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 as funções SOMASES, CONT.SES e MÉDIASES em vez de DFunctions.

Criar macros VBA mais rápidas

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

Use DoEvents para evitar que o Excel pare de responder

As macros VBA são executadas no thread principal do Excel, que também é responsável por lidar com atualizações da interface do usuário e outras operações críticas. Macros de execução longa que não geram controle podem fazer com que o Excel pare de responder, afetando potencialmente outros processos que dependem do loop de mensagens do Excel.

Para evitar que o Excel trave durante operações longas, use a DoEvents função periodicamente em seu código VBA. DoEvents cede temporariamente o controle ao sistema operacional, permitindo que o Excel processe eventos pendentes e permaneça responsivo. Isso é especialmente importante para macros que executam looping, processamento de dados ou cálculos extensivos.

O exemplo a seguir mostra como incorporar DoEvents em um loop:

  Dim i As Long
  Dim counter As Long
  counter = 0
  
  For i = 1 To 100000
      ' Your processing code here
      Range("A" & i).Value = i * 2
      
      ' Call DoEvents periodically (e.g., every 100 iterations)
      counter = counter + 1
      If counter Mod 100 = 0 Then
          DoEvents
      End If
  Next i

Observação

Embora DoEvents melhore a capacidade de resposta, chamá-la com muita frequência pode retardar a execução da macro. Encontre um equilíbrio ligando DoEvents em intervalos apropriados com base nas operações da sua macro.

Desligue tudo, exceto 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. Muitas vezes, um recálculo ou um redesenho após a execução do código é tudo o que é necessário e pode melhorar o desempenho. Depois que o código for executado, restaure a funcionalidade ao seu estado original.

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

  • Application.ScreenUpdating Desative a atualização de 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 enquanto 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 é executado também melhora o desempenho.

  • Application.Calculation Alterne para cálculo manual. Se Application.Calculation for 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. Recalculando a pasta de trabalho apenas quando necessário enquanto o código é executado, você pode melhorar o desempenho.

  • Application.EnableEvents Desativar 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 à medida que 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 os eventos melhorará o desempenho.

  • ActiveSheet.DisplayPageBreaks Desativar quebras de página. Se ActiveSheet.DisplayPageBreaks estiver definido como False, o Excel não exibirá as quebras de página. Não é necessário recalcular as quebras de página enquanto o código é executado, e calcular as quebras de página depois que o código é executado melhora o desempenho.

Importante

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

O exemplo a seguir mostra a funcionalidade que você pode desativar enquanto a 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 percorrer as 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 contendo 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 percorre as 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 um código otimizado que usa uma matriz para obter e definir os valores das células A1:C10000 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

Use . 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 aplicar zoom e pode perder precisão.
  • . O valor retorna uma moeda VBA ou variável de data VBA se o intervalo foi formatado como Data ou Conversor de Moedas. Isso é lento, pode perder precisão e pode causar erros ao chamar funções de planilha.
  • . Value2 é rápido e não altera os dados que estão sendo recuperados do Excel.

Evite selecionar e ativar objetos

Selecionar e ativar objetos exige mais processamento do que fazer referência direta a objetos. Referenciando um objeto como um intervalo 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 um 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 um código otimizado que faz referência a cada Forma diretamente e altera o texto para "Olá".

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

Use estas otimizações adicionais de desempenho do VBA

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

  • Retorne 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 você mesmo no VBA em vez de usar o objeto WorksheetFunction . Para obter mais informações, consulte Usar funções definidas pelo usuário do VBA mais rápidas.

  • Use o método Range.SpecialCells para reduzir o escopo do número de células com as quais seu código interage.

  • Considere os ganhos de desempenho se você implementou sua funcionalidade usando a API C no SDK do XLL. Para obter mais informações, consulte a Documentação do SDK do XLL do Excel 2010.

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

A partir do Excel 2007, o Excel contém uma ampla variedade de formatos de arquivo em comparação com as versões anteriores. Ignorando as variações de formato de arquivo Macro, Template, Add-in, PDF e XPS, os três formatos principais 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 das 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 multithread que o Excel faz em relação à ordem de cálculo da célula não é salva no formato XLS. Portanto, o cálculo de uma pasta de trabalho pode ficar 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 variando de dois a oito com um fator de redução médio de quatro. A partir do Excel 2007, o desempenho de abertura e salvamento é apenas um pouco mais lento do que o formato XLS.

  • Formato XLSX

    XLSX é o formato XML a partir do 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, poderá abrir a pasta compactada e examinar seu conteúdo). Normalmente, o formato XLSX cria arquivos maiores do que o formato XLSB (1,5 vezes maiores em média), mas eles ainda são significativamente menores do 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 descobrir que abrir, fechar e salvar pastas de trabalho é muito mais lento do que calculá-las. Às vezes, isso ocorre apenas porque você tem uma pasta de trabalho grande, mas também pode haver outros motivos.

Se uma ou mais de suas pastas de trabalho abrirem e fecharem mais lentamente do que o razoável, isso poderá ser causado por um dos problemas a seguir.

  • Arquivos temporários

    Arquivos temporários podem se acumular no diretório \Windows\Temp (no Windows 95, no Windows 98 e no 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 os controles usados por pastas de trabalho abertas. Os 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, portanto, você deve limpá-los ocasionalmente. No entanto, se você instalou um software que exige a reinicialização do computador e ainda não o fez, reinicie 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 em Executar. Na caixa de texto, digite %temp% e clique em OK.

  • Acompanhar alterações em uma pasta de trabalho compartilhada

    Acompanhar alterações em uma pasta de trabalho compartilhada faz com que o tamanho do arquivo da pasta de trabalho aumente rapidamente.

  • Arquivo de permuta fragmentado

    Certifique-se de que o arquivo de permuta do Windows esteja localizado em um disco com bastante espaço e que você desfragmente 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>, Proteção, Pasta> de Trabalho, insira a senha opcional) abre e fecha muito mais lentamente do que uma protegida sem a senha opcional.

  • Problemas de intervalo usado

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

  • Grande número de controles em planilhas

    Um grande número de controles (caixas de marca, hiperlinks e assim por diante) em planilhas pode retardar a abertura de uma pasta de trabalho devido ao número de arquivos temporários usados. Isso também pode causar problemas ao abrir ou salvar uma pasta de trabalho em uma WAN (ou até mesmo em uma LAN). Se você tiver esse problema, considere reformular 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. Muitas vezes, é mais rápido abrir uma pasta de trabalho do que ler os links de uma pasta de trabalho fechada.

  • Configurações do verificador de vírus

    Algumas configurações do verificador de vírus podem causar problemas ou lentidão ao abrir, fechar ou salvar, especialmente em um servidor. Se você acha que esse pode ser o problema, tente desativar temporariamente o scanner de vírus.

  • Cálculo lento causando abertura e salvamento lentos

    Em algumas circunstâncias, o Excel recalcula a pasta de trabalho ao abri-la ou salvá-la. Se o tempo de cálculo da pasta de trabalho for longo e estiver causando um problema, verifique se ocálculo foi definido como manual e considere desativar a opção Calcular antes de salvar (Opções>> deFerramentas Cálculo).

  • Arquivos da barra de ferramentas (.xlb)

    Verifique o tamanho do arquivo da barra de ferramentas. Um arquivo de barra de ferramentas típico tem entre 10 KB e 20 KB. Você pode encontrar seus arquivos XLB pesquisando *.xlb usando a pesquisa do Windows. Cada usuário possui 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-a como "barra de ferramentas". "OLD" é mais seguro). Um novo arquivo XLB é criado na próxima vez que você abrir o Excel.

Fazer otimizações adicionais de desempenho

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 sua pasta de trabalho, tente usar as 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 da Tabela Dinâmica como totais e subtotais intermediários 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 continuem visíveis.

    Se você ainda quiser usar Tabelas Dinâmicas como resultados intermediários, use a função INFODADOSTABELADINÂMICA.

  • Formatos condicionais e validação de dados

    Formatos condicionais e validação de dados são ótimos, mas usar muitos deles pode retardar significativamente o cálculo. Se a célula for exibida, todas as fórmulas de formato condicional serão avaliadas em cada cálculo e quando a exibição da célula que contém o formato condicional for 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 exigem tempo adicional de cálculo. O uso de nomes que fazem referência 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 faz referência a eles é calculada, você deve evitar colocar fórmulas ou funções com uso intensivo de cálculos em nomes definidos. Nesses casos, pode ser significativamente mais rápido colocar sua fórmula ou função de cálculo intensivo em uma célula sobressalente em algum lugar e referir-se 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 relacionadas à obtenção dos dados de entrada na forma apropriada para os cálculos ou que estão sendo usadas como medidas de defesa contra alterações no tamanho ou na forma dos dados. Quando você tiver blocos de fórmulas usados apenas ocasionalmente, poderá 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 pela não percepção de que as fórmulas foram convertidas em valores, o método de pasta de trabalho separada 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 com reconhecimento de endereço grande. 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 está 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, consulte a seção "Grandes conjuntos de dados e a versão de 64 bits do Excel" em Desempenho do Excel: melhorias de desempenho e limite.

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.

  • Last updated on