Compartilhar via

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

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

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

Saiba como melhorar o desempenho relacionado com tipos de referências e ligações.

Não utilizar referências de reencaminhamento e referências para trás

Para aumentar a clareza e evitar erros, crie as suas fórmulas para que não se refiram (à direita ou abaixo) a outras fórmulas ou células. Normalmente, as referências de reencaminhamento não afetam o desempenho do cálculo, exceto em casos extremos para o primeiro cálculo de um livro, em que poderá demorar mais tempo a estabelecer uma sequência de cálculo sensata se existirem muitas fórmulas que precisam de diferir o cálculo.

Minimizar a utilização 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 estes cálculos são de thread único. Frequentemente, pode "anular a inscrição" das referências circulares com álgebra para que o cálculo iterativo já não seja 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, em seguida, calcular o fluxo de caixa, incluindo os juros.

O Excel calcula referências circulares folha a folha sem considerar dependências. Por conseguinte, normalmente obtém cálculos lentos se as referências circulares abrangem mais do que uma folha de cálculo. Tente mover os cálculos circulares para uma única folha de cálculo ou otimize a sequência de cálculo da folha de cálculo para evitar cálculos desnecessários.

Antes do início dos cálculos iterativos, o Excel tem de recalcular o livro para identificar todas as referências circulares e os respetivos dependentes. Este processo é igual a duas ou três iterações do cálculo.

Depois de as referências circulares e os respetivos dependentes serem identificados, cada iteração requer que o Excel calcule não só 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 células voláteis e respetivos dependentes. Se tiver um cálculo complexo que dependa das células na referência circular, pode ser mais rápido isolá-lo num livro fechado separado e abri-lo para recálculo após o cálculo circular convergir.

É importante reduzir o número de células no cálculo circular e o tempo de cálculo que estas células demoram.

Evite ligações entre livros quando for possível; podem ser lentas, facilmente quebradas e nem sempre fáceis de encontrar e corrigir.

Normalmente, utilizar menos livros maiores é, mas nem sempre, melhor do que utilizar muitos livros mais pequenos. Algumas exceções podem ser quando tem muitos cálculos de front-end que raramente são recalculados que faz sentido colocá-los num livro separado ou quando tem RAM insuficiente.

Tente utilizar referências de célula direta simples que funcionam em livros fechados. Ao fazê-lo, pode evitar recálculos de todos os livros ligados quando recalcular qualquer livro. Além disso, pode ver os valores que o Excel leu a partir do livro fechado, que é frequentemente importante para depurar e auditar o livro.

Se não conseguir evitar a utilização de livros ligados, tente que todos estejam abertos em vez de fechados e abra os livros que estão ligados antes de abrir os livros a partir dos quais estão ligados.

A utilização de muitas folhas de cálculo pode facilitar a utilização do seu livro, mas geralmente é mais lento calcular referências a outras folhas de cálculo do que referências em folhas de cálculo.

Minimizar o intervalo utilizado

Para guardar memória e reduzir o tamanho do ficheiro, o Excel tenta armazenar informações apenas sobre a área numa folha de cálculo utilizada. Isto chama-se intervalo utilizado. Por vezes, várias operações de edição e formatação expandem significativamente o intervalo utilizado para além do intervalo atualmente considerado utilizado. Isto pode causar obstruções de desempenho e obstruções ao tamanho do ficheiro.

Pode marcar o intervalo utilizado visível numa folha de cálculo com Ctrl+End. Quando isto for excessivo, deve considerar eliminar todas as linhas e colunas abaixo e à direita da última célula utilizada e, em seguida, guardar o livro. Crie primeiro uma cópia de segurança. Se tiver fórmulas com intervalos que se estendem ou se referem à área eliminada, estes intervalos serão reduzidos em tamanho ou alterados para #N/D.

Permitir dados adicionais

Quando adiciona frequentemente linhas ou colunas de dados às suas folhas de cálculo, tem de encontrar uma forma de fazer com que as suas fórmulas do Excel se refiram automaticamente à nova área de dados, em vez de tentar localizar e alterar sempre as suas fórmulas.

Pode fazê-lo ao utilizar um grande intervalo nas suas fórmulas que se estende muito além dos limites de dados atuais. No entanto, isto pode causar cálculos ineficientes em determinadas circunstâncias e é difícil mantê-lo porque a eliminação de linhas e colunas pode diminuir o intervalo sem que tenha em conta.

A partir do Excel 2007, pode utilizar referências de tabelas estruturadas, que 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 colunas inteiras e intervalos dinâmicos.

  • É fácil ter várias tabelas de dados numa única folha de cálculo.

  • As fórmulas incorporadas na tabela também expandem e contraem com os dados.

Em alternativa, utilize referências de colunas e linhas inteiras

Uma abordagem alternativa é utilizar uma referência de coluna inteira, por exemplo $A:$A. Esta referência devolve todas as linhas na Coluna A. Por conseguinte, pode adicionar o máximo de dados que quiser e a referência irá incluí-la sempre.

Esta solução tem vantagens e desvantagens:

  • Muitas funções incorporadas do Excel (SOMA, SOMA.SE) calculam as referências de coluna inteira de forma eficiente porque reconhecem automaticamente a última linha utilizada na coluna. No entanto, as funções de cálculo da matriz, como SUMPRODUCT , não conseguem processar referências de colunas inteiras ou calcular todas as células na coluna.

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

  • É difícil utilizar referências de colunas inteiras quando tem várias tabelas de dados numa única folha de cálculo.

  • No Excel 2007 e versões posteriores, as fórmulas de matriz podem processar referências de coluna inteira, mas isto força o cálculo de todas as células na coluna, incluindo células vazias. Isto pode ser lento para calcular, especialmente para 1 milhão de linhas.

Em alternativa, utilize intervalos dinâmicos

Ao utilizar as funções DESLOCAMENTO ou ÍNDICE e CONTAR.VAL na definição de um intervalo com nome, pode tornar a área à qual o intervalo com nome se refere dinamicamente expandir e contrair. Por exemplo, crie um nome definido com 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 utiliza o nome do intervalo dinâmico numa fórmula, este expande-se automaticamente para incluir novas entradas.

A utilização da fórmula ÍNDICE 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 CONTAR.VAL dentro da fórmula de intervalo dinâmico tem de examinar muitas linhas. Pode minimizar esta diminuição de desempenho ao armazenar a parte CONTAR.VAL da fórmula numa célula separada ou num nome definido e, em seguida, ao referir-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)

Também pode utilizar funções como INDIRETO para construir intervalos dinâmicos, mas INDIRETO é volátil e calcula sempre um 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 realizados por fórmulas de matriz.

  • A utilização de múltiplos intervalos dinâmicos numa única coluna requer funções de contagem para fins especiais.

  • A utilização de muitos intervalos dinâmicos pode diminuir o desempenho.

Melhorar o tempo de cálculo da 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 a ser obstruções de cálculo frequentemente significativas. Felizmente, existem muitas formas de melhorar o tempo de cálculo da pesquisa. Se utilizar a opção de correspondência exata, o tempo de cálculo da função é proporcional ao número de células analisadas antes de ser encontrada uma correspondência. Para pesquisas em intervalos grandes, desta vez pode ser significativo.

O tempo de pesquisa com as opções de correspondência aproximadas de PROCV, PROCH e CORRESP em dados ordenados é rápido e não é significativamente aumentado pelo comprimento do intervalo que está a procurar. As características são as mesmas que a pesquisa binária.

Compreender as opções de pesquisa

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

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

  MATCH(lookup value, lookup array, matchtype)

  • Matchtype=1 devolve a correspondência maior menor ou igual ao valor de pesquisa quando a matriz de pesquisa é ordenada ascendente (correspondência aproximada). Se a matriz de pesquisa não estiver ordenada como ascendente, CORRESP devolverá uma resposta incorreta. A opção predefinida é a correspondência aproximada ascendente.

  • Matchtype=0 pede uma correspondência exata e assume que os dados não estão ordenados.

  • Matchtype=-1 devolve a correspondência mais pequena maior ou igual ao valor de pesquisa se a matriz de pesquisa for ordenada como descendente (correspondência aproximada).

O seguinte exemplo de código mostra a sintaxe das funções PROCV e PROCH . Para obter mais informações, veja 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)

  • Range-lookup=TRUE devolve a correspondência maior menor ou igual ao valor de pesquisa (correspondência aproximada). Essa é a opção padrão. A matriz da tabela tem de ser ordenada ascendente.

  • Range-lookup=FALSE pede uma correspondência exata e assume que os dados não estão ordenados.

Evite efetuar pesquisas em dados não ordenados sempre que possível, porque é lento. Se os seus dados estiverem ordenados, mas quiser uma correspondência exata, veja Utilizar duas pesquisas para dados ordenados com valores em falta.

Utilizar ÍNDICE e CORRESP ou DESLOCAMENTO em vez de PROCV

Experimente utilizar as funções ÍNDICE e CORRESP em vez de PROCV. Embora a função PROCV seja ligeiramente mais rápida (aproximadamente 5 por cento mais rápida), mais simples e utilize menos memória do que uma combinação de CORRESP e ÍNDICE, ou DESLOCAMENTO, a flexibilidade adicional que a oferta CORRESP e ÍNDICE geralmente lhe permite poupar tempo significativamente. Por exemplo, pode armazenar o resultado de uma CORRESP exata numa célula e reutilizá-la em várias instruções INDEX .

A função ÍNDICE é 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, por vezes, aumenta significativamente o tempo necessário para processar a cadeia de cálculo.

É fácil converter a função PROCV em ÍNDICE e CORRESP. As duas instruções seguintes devolvem 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

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

  • Utilize uma folha de cálculo. É mais rápido manter as pesquisas e os dados na mesma folha.

  • Quando puder, ordene primeiro os dados (ORDENAR é rápido) e utilize a correspondência aproximada.

  • Quando tiver de utilizar uma pesquisa de correspondência exata, restrinja o intervalo de células a analisar no mínimo. Utilize 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. Por vezes, pode pré-calcular um limite de intervalo inferior e um limite de intervalo superior para a pesquisa.

Utilizar duas pesquisas para dados ordenados com valores em falta

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 breakeven tem cerca de 10 a 20 linhas.)

Se conseguir ordenar os seus dados, mas mesmo assim não conseguir utilizar uma correspondência aproximada porque não tem a certeza de que o valor que procura existe no intervalo de pesquisa, pode utilizar 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 ao fazer uma pesquisa aproximada na própria coluna de pesquisa.

  VLOOKUP(lookup_val ,lookup_array,1,True)

Pode marcar se a resposta da coluna de pesquisa for igual ao valor de pesquisa (nesse caso, tem uma correspondência exata) com a seguinte fórmula:

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

Se esta fórmula devolver Verdadeiro, encontrou uma correspondência exata, para que possa fazer a pesquisa aproximada novamente, mas, desta vez, devolva a resposta da coluna que pretende.

  VLOOKUP(lookup_val, lookup_array, column, True)

Se a resposta da coluna de pesquisa não corresponder ao valor de pesquisa, tem um valor em falta e a fórmula devolve "nãoexist".

Tenha em atenção que, se procurar um valor inferior ao menor valor na lista, receberá um erro. Pode lidar com este erro ao utilizar IFERROR ou ao adicionar um pequeno valor de teste à lista.

Utilizar a função SE.ERRO para dados não ordenados com valores em falta

Se tiver de utilizar a pesquisa de correspondência exata em dados não ordenados e não tiver a certeza se o valor de pesquisa existe, muitas vezes tem de processar o #N/D que é devolvido se não for encontrada nenhuma correspondência. A partir do Excel 2007, pode utilizar a função SE.ERRO , que é simples e rápida.

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

Em versões anteriores, uma forma simples, mas lenta, é utilizar uma função SE que contenha duas pesquisas.

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

Pode evitar a pesquisa exata dupla se utilizar a correspondência exata uma vez, armazenar o resultado numa célula e, em seguida, testar o resultado antes de efetuar um ÍNDICE.

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

Se não conseguir utilizar duas células, utilize CONTAR.SE. Geralmente, é mais rápido do que uma pesquisa de correspondência exata.

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

Utilizar CORRESP e ÍNDICE para pesquisas de correspondência exatas em múltiplas colunas

Muitas vezes, pode reutilizar um MATCH exato armazenado muitas vezes. Por exemplo, se estiver a fazer pesquisas exatas em múltiplas colunas de resultados, pode poupar tempo ao utilizar uma instrução CORRESP e muitas instruções INDEX em vez de muitas instruções PROCV .

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

  INDEX(Lookup_Range,stored_row,column_number)

Em alternativa, pode utilizar a função PROCV numa fórmula de matriz. (As fórmulas de matriz têm de ser introduzidas através de Ctrl+-Shift+Enter. O Excel adicionará { e } para lhe mostrar que se trata de uma fórmula de matriz).

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

Utilizar ÍNDICE para um conjunto de linhas ou colunas contíguas

Também pode devolver muitas células de uma operação de pesquisa. Para procurar várias colunas contíguas, pode utilizar a função ÍNDICE numa fórmula de matriz para devolver múltiplas colunas ao mesmo tempo (utilize 0 como o número da coluna). Também pode utilizar a função ÍNDICE para devolver múltiplas linhas de uma só vez.

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

Esta ação devolve a coluna A à coluna J da linha armazenada criada por uma instrução CORRESP anterior.

Utilizar CORRESP para devolver um bloco retangular de células

Utilize as funções CORRESP e DESLOCAMENTO para devolver um bloco retangular de células.

Utilizar CORRESP e ÍNDICE para pesquisa bidimensional

Pode fazer uma pesquisa de tabela bidimensional de forma eficiente ao utilizar pesquisas separadas nas linhas e colunas de uma tabela através de uma função ÍNDICE com duas funções CORRESP incorporadas, uma para a linha e outra para a coluna.

Utilizar um intervalo de subconjunto para pesquisa de múltiplos índices

Em folhas de cálculo grandes, poderá ter de procurar frequentemente através de vários índices, como procurar volumes de produtos num país/região. Para tal, pode concatenar os índices e efetuar a pesquisa com valores de pesquisa concatenados. No entanto, isto é ineficiente por duas razões:

  • A concatenação de cadeias é uma operação intensiva em termos de cálculo.

  • A pesquisa abrangerá um intervalo grande.

Muitas vezes, é mais eficiente calcular um intervalo de subconjunto para a pesquisa (por exemplo, ao encontrar a primeira e última linha do país/região e, em seguida, procurar o produto dentro desse intervalo de subconjunto).

Considerar opções para pesquisa tridimensional

Para procurar a tabela a utilizar para além da linha e da coluna, pode utilizar as seguintes técnicas, focando-se em como fazer o Excel procurar ou escolher a tabela.

Se cada tabela que pretende procurar (a terceira dimensão) for armazenada como um conjunto de tabelas estruturadas nomeadas, nomes de intervalo ou como uma tabela de cadeias de texto que representem intervalos, poderá conseguir utilizar as funções ESCOLHER ou INDIRETO .

  • A utilização de nomes CHOOSE e de intervalo pode ser um método eficiente. A função CHOOSE não é volátil, mas é mais adequada para um número relativamente pequeno de tabelas. Este exemplo utiliza TableLookup_Value dinamicamente para escolher o nome do intervalo (TableName1, TableName2, ...) a utilizar para a tabela de referência.

      INDEX(CHOOSE(TableLookup_Value,TableName1,TableName2,TableName3), _
  MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))

  • O exemplo seguinte utiliza a função INDIRETO e TableLookup_Value para criar dinamicamente o nome da folha a utilizar para a tabela de referência. Este método tem a vantagem de ser simples e capaz de lidar com um grande número de tabelas. Uma vez que INDIRETO é uma função volátil de thread único, a pesquisa é de thread único calculada em todos os cálculos, mesmo que não tenha sido alterado nenhum dado. A utilização deste método é lenta.

      INDEX(INDIRECT("Sheet" & TableLookup_Value & "!$B$2:$Z$1000"), _
  MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))

  • Também pode utilizar a função PROCV para localizar o nome da folha ou a cadeia de texto a utilizar para a tabela e, em seguida, utilizar a função INDIRETO para converter o texto resultante num 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 suas tabelas numa tabela gigante que tem uma coluna adicional que identifica as tabelas individuais. Em seguida, pode utilizar as técnicas para pesquisa de múltiplos índices apresentadas nos exemplos anteriores.

Utilizar a pesquisa de carateres universais

As funções CORRESP, PROCV e PROCH permitem-lhe utilizar os carateres universais ? (qualquer caráter individual) e * (nenhum caráter ou qualquer número de carateres) em correspondências exatas alfabéticas. Por vezes, pode utilizar este método para evitar múltiplas correspondências.

Otimizar fórmulas de matriz e SOMARPRODUTO

As fórmulas de matriz e a função SOMARPRODUTO são poderosas, mas tem de processá-las cuidadosamente. Uma única fórmula de matriz pode exigir muitos cálculos.

A chave para otimizar a velocidade de cálculo das fórmulas de matriz é garantir que o número de células e expressões avaliadas na fórmula de matriz é o menor possível. Lembre-se de que uma fórmula de matriz é um pouco semelhante a uma fórmula volátil: se uma das células que referencia tiver sido alterada, for volátil ou tiver sido recalculada, a fórmula de matriz calcula todas as células na fórmula e avalia todas as células virtuais de que precisa 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 de matriz em colunas e linhas auxiliares separadas. Isto faz uma utilização 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 utilizadas. Com 1 milhão de linhas disponíveis a partir do Excel 2007, uma fórmula de matriz que referencia uma coluna inteira é extremamente lenta a calcular.

  • A partir do Excel 2007, utilize referências estruturadas onde pode manter o número de células avaliadas pela fórmula de matriz no mínimo.

  • Em versões anteriores ao Excel 2007, utilize 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 as fórmulas de matriz que referenciam uma linha e uma coluna: isto força o cálculo de um intervalo retangular.

  • Utilize SUMPRODUCT , se possível; é ligeiramente mais rápido do que a fórmula de matriz equivalente.

Considerar opções para utilizar a SOMA para fórmulas de matriz de múltiplas condições

Deve sempre utilizar as funções SOMA.SE.S, CONTAR.SE.S e MÉDIA.SE.S em vez de fórmulas de matriz onde pode, porque 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 são frequentemente utilizadas para calcular uma soma com múltiplas condições. Isto é relativamente fácil de fazer, especialmente se utilizar o Assistente de Soma Condicional no Excel, mas muitas vezes é lento. Normalmente, existem formas muito mais rápidas de obter o mesmo resultado. Se tiver apenas alguns SUMs de múltiplas condições, poderá utilizar a função DSUM , que é muito mais rápida do que a fórmula de matriz equivalente.

Se tiver de utilizar fórmulas de matriz, alguns bons métodos de aceleração são os seguintes:

  • Utilize nomes de intervalo dinâmico ou referências de tabelas estruturadas para minimizar o número de células.

  • Divida as múltiplas condições numa coluna de fórmulas auxiliares que devolvem Verdadeiro ou Falso para cada linha e, em seguida, faça referência à coluna auxiliar numa fórmula de matriz ou SOMA.SE . Isto pode não parecer reduzir o número de cálculos de uma única fórmula de matriz; no entanto, na maioria das vezes, permite que o processo de recálculo inteligente recalcule apenas as fórmulas na coluna auxiliar que têm de ser recalculadas.

  • Considere concatenar todas as condições numa única condição e, em seguida, utilizar SOMA.SE.

  • Se os dados puderem ser ordenados, conte grupos de linhas e limite as fórmulas de matriz para ver os grupos de subconjunto.

Priorizar funções da família SOMA.SE.S, CONTAR.SE.S e outras funções da família SE.S de múltiplas condições

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

Considerar opções para utilizar SUMPRODUCT para fórmulas de matriz de múltiplas condições

A partir do Excel 2007, deve sempre utilizar as funções SOMA.SE.S, CONTAR.SE.S e MÉDIA.SE.S e, em Excel 2016 funções MÁXIMO.SE.S e MINIFS, em vez de fórmulas SUMPRODUCT sempre que possível.

Em versões anteriores, existem algumas vantagens em utilizar SUMPRODUCT em vez de fórmulas de matriz SOMA :

  • SUMPRODUCT não tem de ser introduzido na matriz utilizando Ctrl+Shift+Enter.

  • SUMPRODUCT é geralmente ligeiramente mais rápido (5 a 10 por cento).

Utilize SUMPRODUCT para fórmulas de matriz de múltiplas condições da seguinte forma:

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

Neste exemplo, Condition1 e Condition2 são expressões condicionais, como $A$1:$A$10000<=$Z4. Uma vez que as expressões condicionais devolvem Verdadeiro ou Falso em vez de números, têm de ser coagidas a números dentro da função SOMARPRODUTO . Pode fazê-lo utilizando dois sinais de subtração (--) ou adicionando 0 (+0) ou multiplicando por 1 (x1). A utilização -- é ligeiramente mais rápida do que +0 ou x1.

Tenha em atenção que o tamanho e a forma dos intervalos ou matrizes que são utilizados nas expressões condicionais e intervalo a somar têm de ser iguais e não podem conter colunas inteiras.

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

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

Normalmente, é um pouco mais lento do que utilizar a sintaxe de vírgula e apresenta um erro se o intervalo a somar contiver um valor de texto. No entanto, é ligeiramente mais flexível, uma vez que o intervalo a somar pode ter, por exemplo, múltiplas colunas quando as condições têm apenas uma coluna.

Utilizar SUMPRODUCT para multiplicar e adicionar intervalos e matrizes

Em casos como cálculos médios ponderados, em que tem de multiplicar um intervalo de números por outro intervalo de números e somar os resultados, a utilização da sintaxe de vírgula para SUMPRODUCT pode ser 20 a 25 por cento mais rápida do que uma SOMA introduzida 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)

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

Tenha em atenção potenciais obstruções ao cálculo da matriz e da função

O motor de cálculo no Excel está otimizado para explorar fórmulas de matriz e funções que referenciam intervalos. No entanto, algumas disposições invulgares destas fórmulas e funções podem, por vezes, causar um aumento significativo do tempo de cálculo.

Se encontrar uma obstrução de cálculo que envolva fórmulas de matriz e funções de intervalo, deve procurar o seguinte:

  • Referências parcialmente sobrepostas.

  • Fórmulas de matriz e funções de intervalo que referenciam parte de um bloco de células que são calculadas noutra fórmula de matriz ou função de intervalo. Esta situação pode ocorrer frequentemente na análise de série temporal.

  • Um conjunto de fórmulas referenciadas por linha e um segundo conjunto de fórmulas que referenciam 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 SOMA ao pé de cada coluna.

Utilizar funções de forma eficiente

As funções expandem significativamente o poder do Excel, mas a forma como as utiliza pode afetar frequentemente o tempo de cálculo.

Evitar funções de thread único

A maioria das funções nativas do Excel funcionam bem com cálculos com vários threads. No entanto, sempre que possível, evite utilizar as seguintes funções de thread único:

  • Funções definidas pelo utilizador (UDFs) do VBA e automatização, mas os UDFs baseados em XLL podem ter vários threads
  • PHONETIC
  • CÉL quando o argumento "formato" ou "endereço" é usado
  • INDIRETO
  • GETPIVOTDATA
  • MEMBROCUBO
  • VALORCUBO
  • PROPRIEDADEMEMBROCUBO
  • CONJUNTOCUBO
  • MEMBROCLASSIFICADOCUBO
  • MEMBROKPICUBO
  • CONTAGEMCONJUNTOCUBO
  • ENDEREÇO onde é fornecido o quinto parâmetro (o sheet_name)
  • 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

Utilizar tabelas para funções que processam intervalos

Para funções como SOMA, SOMA.SE e SOMA.SE.S que processam intervalos, o tempo de cálculo é proporcional ao número de células utilizadas que está a somar ou a contar. As células não utilizadas não são examinadas, pelo que as referências de colunas inteiras são relativamente eficientes, mas é melhor garantir que não inclui mais células utilizadas do que as necessárias. Utilize tabelas ou calcule intervalos de subconjunto ou intervalos dinâmicos.

Reduzir funções voláteis

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

Muitas vezes, pode reduzir o número de funções voláteis ao utilizar ÍNDICE em vez de DESLOCAMENTO e SELECIONAR em vez de INDIRETO. No entanto, OFFSET é uma função rápida e pode ser frequentemente utilizada de formas criativas que dão cálculos rápidos.

Utilizar funções definidas pelo utilizador C ou C++

As funções definidas pelo utilizador que são programadas em C ou C++ e que utilizam a API C (funções de suplemento XLL) geralmente têm um desempenho mais rápido do que as funções definidas pelo utilizador que são desenvolvidas através da utilização de VBA ou Automatização (suplementos XLA ou Automatização). Para obter mais informações, consulte Desenvolver XLLs do Excel 2010.

O desempenho das funções definidas pelo utilizador do VBA é sensível à forma como programa e as chama.

Utilizar funções definidas pelo utilizador do VBA mais rápidas

Normalmente, é mais rápido utilizar os cálculos de fórmulas do Excel e as funções da folha de cálculo do que utilizar funções definidas pelo utilizador do VBA. Isto acontece porque existe uma pequena sobrecarga para cada chamada de função definida pelo utilizador e uma sobrecarga significativa ao transferir informações do Excel para a função definida pelo utilizador. No entanto, as funções bem concebidas e denominadas definidas pelo utilizador podem ser muito mais rápidas do que as fórmulas de matriz complexas.

Certifique-se de que colocou todas as referências às células da folha de cálculo nos parâmetros de entrada da função definida pelo utilizador em vez de no corpo da função definida pelo utilizador, para que possa evitar adicionar Application.Volatile desnecessariamente.

Se tiver de ter muitas fórmulas que utilizem funções definidas pelo utilizador, certifique-se de que está no modo de cálculo manual e que o cálculo é iniciado a partir do VBA. As funções definidas pelo utilizador do VBA calculam muito mais lentamente se o cálculo não for chamado do VBA (por exemplo, no modo automático ou quando prime F9 no modo manual). Isto é particularmente verdade quando o Visual Basic Editor (Alt+F11) está aberto ou foi aberto na sessão atual do Excel.

Pode intercetar F9 e redirecioná-lo para um subroutine de cálculo VBA da seguinte forma. Adicione esta subrreplicação ao módulo Este livro de trabalho .

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

Adicione esta subrreplicação a um módulo padrão.

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

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

Se a função definida pelo utilizador processar cada célula num intervalo, declare a entrada como um intervalo, atribua-a a uma variante que contenha uma matriz e faça um ciclo sobre a mesma. Se quiser processar referências de colunas inteiras de forma eficiente, tem de criar um subconjunto do intervalo de entrada, dividindo-o na respetiva interseção com o intervalo utilizado, 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 utilizador estiver a utilizar funções de folha de cálculo ou métodos de modelo de objetos 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 utilizador.

  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 a função definida pelo utilizador for chamada no início da cadeia de cálculo, pode ser transmitida como argumentos não calculados. Dentro de uma função definida pelo utilizador, pode detetar células não calculadas com 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 utilizador e para cada transferência de dados do Excel para o VBA. Por vezes, uma função definida pelo utilizador de uma fórmula de matriz de várias células pode ajudá-lo a minimizar estas sobrecargas ao combinar múltiplas chamadas de função numa única função com um intervalo de entrada de várias células que devolve um intervalo de respostas.

Minimizar o intervalo de células que a referência SOMA e SOMA.SE

As funções SOMA e SOMA.SE do Excel são frequentemente utilizadas num grande número de células. O tempo de cálculo destas funções é proporcional ao número de células abrangidas, pelo que tente minimizar o intervalo de células que as funções estão a referenciar.

Utilize as funções SOMA.SE, CONTAR.SE, SOMA.SE, CONTAR.SE.S e outras funções SE.S universais

Utilize os carateres universais ? (qualquer caráter individual) e * (nenhum caráter ou qualquer número de carateres) nos critérios para intervalos alfabéticos como parte das funções SOMA.SE, CONTAR.SE, SOMA.SE.S, CONTAR.SE.S e outras funções SE.S .

Escolher o método para os SUMs do período até à data e cumulativos

Existem dois métodos para efetuar SUMs de ponto a ponto ou cumulativos. Suponha que os números que pretende somar cumulativamente estão na coluna A e pretende que a coluna B contenha a soma cumulativa; Pode efetuar um dos seguintes procedimentos:

  • 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 em A1, mas como a célula final tem uma referência de linha relativa, aumenta automaticamente para cada linha.

  • Pode criar uma fórmula como =$A1 na célula B1 e =$B1+$A2 na célula B2 e arrastá-la para baixo até onde precisar. Isto calcula a célula cumulativa ao adicionar o número desta linha à SOMA cumulativa anterior.

Para 1000 linhas, o primeiro método obriga o Excel a fazer cerca de 500 000 cálculos, mas o segundo método faz com que o Excel faça apenas cerca de 2000 cálculos.

Calcular somas de subconjunto

Quando tem vários índices ordenados para uma tabela (por exemplo, Site dentro da Área), muitas vezes pode poupar tempo de cálculo significativo ao calcular dinamicamente o endereço de um intervalo de linhas (ou colunas) subconjunto a utilizar na função SOMA ou SOMA.SE .

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

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

  2. Adicione as contagens cumulativamente para cada bloco para determinar a linha de início.

  3. Utilize DESLOCAMENTO com a linha inicial e a contagem para devolver um intervalo de subconjunto à SOMA ou SOMA.SE que abrange apenas o bloco de subconjunto de linhas.

Utilizar SUBTOTAL para listas filtradas

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

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

  • Outras funções SUBTOTAL .

Utilizar a função AGREGAR

A função AGREGAR é uma forma avançada e eficiente de calcular 19 métodos diferentes de agregação de dados (como SOMA, MEDIANA, PERCENTIL e GRANDE). AGREGAR tem opções para ignorar linhas ocultas ou filtradas, valores de erro e funções SUBTOTAL e AGREGAR aninhadas.

Evitar a utilização de DFunctions

As DFunctions DSUM, DCOUNT, DAVERAGE, etc., são significativamente mais rápidas do que as fórmulas de matriz equivalentes. A desvantagem dos DFunctions é que os critérios têm de estar num intervalo separado, o que os torna impraticáveis de utilizar e manter em muitas circunstâncias. A partir do Excel 2007, deve utilizar as funções SOMA.SE.S, CONTAR.SE.S e MÉDIA.SE.S em vez das funções DFunctions.

Criar macros VBA mais rápidas

Utilize as seguintes sugestões para criar macros VBA mais rápidas.

Utilize DoEvents para impedir que o Excel não responda

As macros do VBA são executadas no thread principal do Excel, que também é responsável por processar atualizações da interface de utilizador e outras operações críticas. As macros de execução prolongada que não produzem controlo podem fazer com que o Excel deixe de responder, afetando potencialmente outros processos que dependem do ciclo de mensagens do Excel.

Para impedir que o Excel seja suspenso durante operações longas, utilize a DoEvents função periodicamente no código VBA. DoEvents gera temporariamente controlo para o sistema operativo, permitindo que o Excel processe eventos pendentes e permaneça reativo. Isto é especialmente importante para macros que executam ciclos extensos, processamento de dados ou cálculos.

O exemplo seguinte mostra como incorporar DoEvents num ciclo:

  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 demasiada frequência pode abrandar a execução da macro. Encontre um equilíbrio ao chamar DoEvents em intervalos adequados com base nas operações da macro.

Desativar 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 enquanto o código é executado. Muitas vezes, um novo cálculo ou um novo cálculo 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 para o estado original.

Normalmente, a seguinte funcionalidade pode ser desativada enquanto a macro do VBA é executada:

  • Application.ScreenUpdating Desativar a atualização do ecrã. Se Application.ScreenUpdating estiver definido como Falso, o Excel não redesenhará o ecrã. Enquanto o código é executado, o ecrã é atualizado rapidamente e normalmente não é necessário que o utilizador veja cada atualização. Atualizar o ecrã 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 Falso, o Excel não apresenta a barra de status. A definição da barra de status é separada da definição de atualização do ecrã para que possa continuar a apresentar a status da operação atual, mesmo que o ecrã não esteja a atualizar. No entanto, se não precisar de apresentar a status de todas as operações, desativar a barra de status enquanto o código é executado também melhora o desempenho.

  • Application.Calculation Mude para cálculo manual. Se Application.Calculation estiver definido como xlCalculationManual, o Excel só calcula o livro quando o utilizador inicia 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 com uma fórmula for alterado, o Excel recalcula a fórmula. Se mudar o modo de cálculo para manual, pode aguardar até que todas as células associadas à fórmula sejam atualizadas antes de recálculo do livro. Ao recálculo do livro apenas quando necessário enquanto o código é executado, pode melhorar o desempenho.

  • Application.EnableEvents Desativar eventos. Se Application.EnableEvents estiver definido como Falso, o Excel não gerará eventos. Se existirem suplementos a escutar eventos do Excel, esses suplementos consomem recursos no computador à medida que registam os eventos. Se não for necessário que o suplemento registe os eventos que ocorrem enquanto o código é executado, desativar os eventos melhora o desempenho.

  • ActiveSheet.DisplayPageBreaks Desativar quebras de página. Se ActiveSheet.DisplayPageBreaks estiver definido como Falso, o Excel não apresenta 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 esta funcionalidade para o estado original após a execução do código.

O exemplo seguinte mostra a funcionalidade que pode desativar enquanto a macro do 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 escrever grandes blocos de dados numa única operação

Otimize o código ao reduzir explicitamente o número de vezes que os dados são transferidos entre o Excel e o 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 numa linha, utilizando uma variante que contém uma matriz bidimensional para armazenar os valores conforme necessário. Os exemplos de código seguintes comparam estes dois métodos.

O seguinte exemplo de código mostra 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. Estas 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 seguinte mostra o código otimizado que utiliza uma matriz para obter e definir os valores das células A1:C10000 ao mesmo tempo. Estas 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

Utilize . Valor2 em vez de . Valor ou . Texto ao ler dados de um intervalo do Excel

  • . O texto devolve o valor formatado de uma célula. Isto é lento, pode devolver ### se o utilizador ampliar e pode perder precisão.
  • . O valor devolve uma moeda VBA ou uma variável de data VBA se o intervalo tiver sido formatado como Data ou Conversor de Moedas. Isto é lento, pode perder precisão e pode causar erros ao chamar funções de folha de cálculo.
  • . O Valor2 é rápido e não altera os dados que estão a ser obtidos a partir do Excel.

Evite selecionar e ativar objetos

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

O seguinte exemplo de código mostra código não otimizado que seleciona cada Forma na folha 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 seguinte mostra o código otimizado que referencia cada Forma diretamente e altera o texto para "Olá".

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

Utilize estas otimizações de desempenho do VBA adicionais

Segue-se uma lista de otimizações de desempenho adicionais que pode utilizar no código VBA:

  • Devolver resultados ao atribuir 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 num ciclo, durante a execução do código.

  • Para funções simples que utiliza frequentemente no seu código, implemente as funções manualmente no VBA em vez de utilizar o objeto WorksheetFunction . Para obter mais informações, veja Utilizar funções definidas pelo utilizador do VBA mais rápidas.

  • Utilize o método Range.SpecialCells para definir o âmbito do número de células com as quais o código interage.

  • Considere os ganhos de desempenho se tiver implementado a funcionalidade com a API C no SDK XLL. Para obter mais informações, veja a Documentação do SDK XLL do Excel 2010.

Considerar o desempenho e o tamanho dos formatos de ficheiro do Excel

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

  • Formato XLS

    O formato XLS tem o mesmo formato que as versões anteriores. Quando utiliza este formato, está restrito a 256 colunas e 65 536 linhas. Quando guarda um livro do Excel 2007 ou Excel 2010 no formato XLS, o Excel executa uma marcar de compatibilidade. O tamanho do ficheiro é quase o mesmo que as versões anteriores (algumas informações adicionais podem ser armazenadas) e o desempenho é ligeiramente mais lento do que as versões anteriores. Qualquer otimização com vários threads que o Excel faça relativamente à ordem de cálculo de células não é guardada no formato XLS. Por conseguinte, o cálculo de um livro pode ser mais lento depois de guardar o livro no formato XLS, fechar e reabrir o livro.

  • Formato XLSB

    XLSB é o formato binário que começa no Excel 2007. É estruturado como uma pasta comprimida que contém muitos ficheiros binários. É muito mais compacto do que o formato XLS, mas a quantidade de compressão depende do conteúdo do livro. Por exemplo, dez livros mostram um fator de redução de tamanho que varia entre dois e oito com um fator de redução média de quatro. A partir do Excel 2007, abrir e guardar o desempenho é apenas ligeiramente mais lento do que o formato XLS.

  • Formato XLSX

    XLSX é o formato XML que começa no Excel 2007 e é o formato predefinido a partir do Excel 2007. O formato XLSX é uma pasta comprimida que contém muitos ficheiros XML (se alterar a extensão de nome de ficheiro para .zip, pode abrir a pasta comprimida e examinar os respetivos conteúdos). Normalmente, o formato XLSX cria ficheiros maiores do que o formato XLSB (1,5 vezes maior, em média), mas ainda são significativamente menores do que os ficheiros XLS. Deverá esperar que os tempos de abertura e poupança sejam ligeiramente mais longos do que os dos ficheiros XLSB.

Abrir, fechar e guardar livros

Poderá considerar que abrir, fechar e guardar livros é muito mais lento do que calculá-los. Por vezes, isto deve-se apenas ao facto de ter um livro grande, mas também pode haver outras razões.

Se um ou mais dos seus livros abrirem e fecharem mais lentamente do que o razoável, poderá ser causado por um dos seguintes problemas.

  • Arquivos temporários

    Os ficheiros temporários podem acumular-se no seu diretório \Windows\Temp (no Windows 95, Windows 98 e Windows ME) ou no seu diretório \Documents and Settings\User Name\Local Settings\Temp (no Windows 2000 e Windows XP). O Excel cria estes ficheiros para o livro e para controlos que são utilizados por livros abertos. Os programas de instalação de software também criam ficheiros temporários. Se o Excel deixar de responder por qualquer motivo, poderá ter de eliminar estes ficheiros.

    Demasiados ficheiros temporários podem causar problemas, pelo que, ocasionalmente, deve limpo-los. No entanto, se tiver instalado software que exija que reinicie o computador e ainda não o tiver feito, deve reiniciar antes de eliminar os ficheiros temporários.

 Uma forma fácil de abrir o diretório temporário é a partir do menu Iniciar do Windows: clique em Iniciar e, em seguida, clique em Executar. Na caixa de texto, escreva %temp%e, em seguida, clique em OK.

  • Controlar alterações num livro partilhado

    Registar alterações num livro partilhado faz com que o tamanho do ficheiro do livro aumente rapidamente.

  • Ficheiro de troca fragmentado

    Certifique-se de que o ficheiro de troca do Windows está localizado num disco com muito espaço e que desfragmenta o disco periodicamente.

  • Livro com estrutura protegida por palavra-passe

    Um livro com a estrutura protegida por uma palavra-passe (menu Ferramentas>Proteção de Proteção>de Livro> introduza a palavra-passe opcional) é aberto e fecha muito mais lentamente do que aquele que está protegido sem a palavra-passe opcional.

  • Problemas de intervalo utilizados

    Os intervalos utilizados de grandes dimensões podem causar uma abertura lenta e um aumento do tamanho do ficheiro, especialmente se forem causados por linhas ou colunas ocultas com altura ou largura não padrão. Para obter mais informações sobre os problemas de intervalo utilizados, veja Minimizar o intervalo utilizado.

  • Grande número de controlos em folhas de cálculo

    Um grande número de controlos (marcar caixas, hiperligações, etc.) em folhas de cálculo pode tornar a abertura de um livro mais lenta devido ao número de ficheiros temporários utilizados. Isto também pode causar problemas ao abrir ou guardar um livro numa WAN (ou mesmo numa LAN). Se tiver este problema, deve considerar redesenhar o seu livro.

  • Grande número de ligações para outros livros

    Se possível, abra os livros aos quais está a ligar antes de abrir o livro que contém as ligações. Muitas vezes, é mais rápido abrir um livro do que ler as ligações de um livro fechado.

  • Definições do detetor de vírus

    Algumas definições do detetor de vírus podem causar problemas ou lentidão ao abrir, fechar ou guardar, especialmente num servidor. Se pensa que este pode ser o problema, tente desativar temporariamente o detetor de vírus.

  • Cálculo lento que faz com que abra e guarde lentamente

    Em algumas circunstâncias, o Excel recalcula o seu livro quando é aberto ou guarda-o. Se o tempo de cálculo do livro for longo e estiver a causar um problema, certifique-se de que tem o cálculo definido como manual e considere desativar a opção calcular antes de guardar (FerramentasOpções>> cálculo).

  • Ficheiros da barra de ferramentas (.xlb)

    Verifique o tamanho do ficheiro da barra de ferramentas. Um ficheiro de barra de ferramentas típico está entre 10 KB e 20 KB. Pode encontrar os seus ficheiros XLB ao procurar *.xlb através da pesquisa do Windows. Cada utilizador tem um ficheiro XLB exclusivo. Adicionar, alterar ou personalizar barras de ferramentas aumenta o tamanho do ficheiro toolbar.xlb. Eliminar o ficheiro remove todas as personalizações da barra de ferramentas (ao mudar o nome de "barra de ferramentas". OLD" é mais seguro). É criado um novo ficheiro XLB da próxima vez que abrir o Excel.

Fazer otimizações de desempenho adicionais

Pode melhorar o desempenho nas seguintes áreas.

  • Tabelas Dinâmicas

    As tabelas dinâmicas fornecem uma forma eficiente de resumir grandes quantidades de dados.

    • Totais como resultados finais. Se precisar de produzir totais e subtotais como parte dos resultados finais do seu livro, experimente utilizar tabelas dinâmicas.

    • Totais como resultados intermédios. As tabelas dinâmicas são uma excelente forma de produzir relatórios de resumo, mas tente evitar criar fórmulas que utilizem resultados de tabela dinâmica como totais intermédios e subtotais na cadeia de cálculo, a menos que 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, pelo que as informações ainda estão visíveis.

    Se ainda quiser utilizar tabelas dinâmicas como resultados intermédios, utilize a função GETPIVOTDATA .

  • Formatos condicionais e validação de dados

    Os formatos condicionais e a validação de dados são ótimos, mas utilizar muitos deles pode abrandar significativamente o cálculo. Se a célula for apresentada, todas as fórmulas de formato condicional são avaliadas em cada cálculo e quando a apresentação da célula que contém o formato condicional é atualizada. O modelo de objetos do Excel tem uma propriedade Folha de Cálculo.EnableFormatConditionsCalculation para que possa ativar ou desativar o cálculo de formatos condicionais.

  • Nomes definidos

    Os nomes definidos são uma das funcionalidades mais avançadas do Excel, mas demoram mais tempo de cálculo. A utilização de nomes que se referem a outras folhas de cálculo adiciona um nível adicional de complexidade ao processo de cálculo. Além disso, deve tentar evitar nomes aninhados (nomes que se referem a outros nomes).

    Uma vez que os nomes são calculados sempre que uma fórmula que se refere aos mesmos é calculada, deve evitar colocar fórmulas ou funções com muitos cálculos em nomes definidos. Nestes casos, pode ser significativamente mais rápido colocar a fórmula ou função com utilização intensiva de cálculos numa célula sobressalente algures e, em alternativa, referir-se a essa célula, diretamente ou utilizando um nome.

  • Fórmulas que são utilizadas apenas ocasionalmente

    Muitos livros contêm um número significativo de fórmulas e pesquisas que se preocupam em colocar os dados de entrada na forma adequada para os cálculos ou estão a ser utilizados como medidas defensivas contra alterações no tamanho ou forma dos dados. Quando tem blocos de fórmulas que são utilizados apenas ocasionalmente, pode copiar e colar valores especiais para eliminar temporariamente as fórmulas ou pode colocá-las num livro separado e raramente aberto. Uma vez que os erros da folha de cálculo são frequentemente causados por não notar que as fórmulas foram convertidas em valores, o método do livro separado pode ser preferível.

  • Utilizar memória suficiente

    A versão de 32 bits do Excel pode utilizar até 2 GB de RAM ou até 4 GB de RAM para versões de 32 bits com Suporte para Endereços Grandes do Excel 2013 e 2016. No entanto, o computador que está a executar o Excel também requer recursos de memória. Por conseguinte, se tiver apenas 2 GB de RAM no computador, o Excel não pode tirar partido dos 2 GB completos porque uma parte da memória é alocada ao sistema operativo e a outros programas em execução. Para otimizar o desempenho do Excel num 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 secção "Conjuntos de dados grandes e a versão de 64 bits do Excel" em Desempenho do Excel: Melhorias de desempenho e limite.

Conclusão

Este artigo abordou formas de otimizar a funcionalidade do Excel, como ligações, 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