Desempenho do Excel: melhorar o desempenho de cálculo

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

A "Grande Grade" de 1 milhão linhas e 16.000 colunas no Excel 2016 do Office, em conjunto com muitos outros aumentos de limites, aumenta bastante o tamanho das planilhas que você pode criar com versões anteriores do Excel. Uma única planilha no Excel agora pode conter mais de 1000 vezes mais células do que versões anteriores.

Nas versões anteriores do Excel, muitas pessoas criavam planilhas de cálculo lento, e planilhas maiores geralmente calculam mais lentamente do que as menores. Com a introdução da "Grade Grande" no Excel 2007, o desempenho faz toda a diferença. Tarefas de redução de cálculo e manipulação de dados como filtragem e classificação dificultam a concentração dos usuários na tarefa, e a falta de concentração aumenta os erros.

As versões recentes do Excel introduziram vários recursos para ajudar a lidar com este aumento de capacidade, como a capacidade de usar mais de um processador de cada vez para cálculos e operações comuns de conjunto de dados como atualizar, classificar e abrir pastas de trabalho. Vários threads de cálculo podem reduzir substancialmente o tempo de cálculo da planilha. No entanto, o fator que mais influencia a velocidade de cálculo do Excel ainda é a maneira como a planilha é desenvolvida e criada.

Você pode modificar a maioria das planilhas de cálculo lento para calcular dezenas, centenas e até milhares de vezes mais rápido. Ao identificar, medir e melhorar as obstruções de cálculo nas suas planilhas, você poderá acelerar o cálculo.

A importância da velocidade de cálculo

O cálculo de velocidade incipiente afeta a produtividade e aumenta o erro de usuário. A produtividade do usuário e a capacidade de se concentrar se deterioram à medida que o tempo de resposta aumenta.

O Excel tem dois modos de cálculo principais que permitem controlar quando o cálculo ocorre:

• Cálculo automático - As fórmulas são recalculadas automaticamente quando você faz uma alteração.

• Cálculo manual - As fórmulas são recalculadas somente quando você solicitar (por exemplo, pressionando F9).

Para tempos de cálculo de menos de um décimo de segundo, os usuários sentem que o sistema está respondendo instantaneamente. Eles podem usar o cálculo automático mesmo quando inserem dados.

Entre um décimo de segundo e um segundo, os usuários com podem manter uma linha de raciocínio com êxito, embora notem o atraso do tempo de resposta.

Como o tempo de cálculo aumenta (normalmente entre 1 e 10 segundos), os usuários devem alternar para o cálculo manual ao inserir dados. Erros de usuário e níveis inconvenientes começam a aumentar, especialmente em tarefas repetitivas, e manter uma linha de raciocínio se torna difícil.

Com tempos de cálculos maiores do que 10 segundos, os usuários ficam impacientes e normalmente migram para outras tarefas enquanto eles esperam. Isso pode causar problemas quando o cálculo é uma sequência de tarefas e o usuário perde o controle.

Compreendendo os métodos de cálculo no Excel

Para melhorar o desempenho de cálculo no Excel, você deve compreender os métodos de cálculo disponíveis e a controlá-los.

Cálculos completos e dependências de recálculo

O mecanismo de recálculo inteligente no Excel tenta reduzir o tempo de cálculo, controla continuamente os precedentes e dependências para cada fórmula (as células referenciadas pela fórmula) e as alterações feitas desde o último cálculo. No próxima recálculo, o Excel recalcula somente o seguinte:

• Células, fórmulas, valores ou nomes que foram alterados ou estão sinalizados por precisarem de recálculo.

• Células dependentes em outras células, fórmulas, nomes ou valores que precisam de recálculo.

• Funções voláteis e formatos condicionais visíveis.

O Excel continua calculando células que dependem das células anteriormente calculadas mesmo se o valor da célula anteriormente calculada não for alterado quando for calculado.

Como você altera apenas parte dos dados de entrada ou algumas fórmulas entre os cálculos na maioria dos casos, esse recálculo inteligente geralmente leva apenas uma fração de tempo do que um cálculo completo de todas as fórmulas levaria.

No modo de cálculo manual, você pode acionar este recálculo inteligente pressionando F9. Você também pode forçar um cálculo completo de todas as fórmulas pressionando Ctrl + Alt + F9 ou você pode forçar a recriação completa das dependências e um cálculo completo pressionando Shift + Ctrl + Alt + F9.

Processo de cálculo

Fórmulas que fazem referência a outras células do Excel podem ser usadas antes ou depois das células referenciadas (referência para frente ou a referência para trás). Isso ocorre porque o Excel não calcula células em uma ordem determinada ou por linha ou coluna. Em vez disso, o Excel determina dinamicamente a sequência de cálculo com base em uma lista de todas as fórmulas para calcular (cadeia cálculo) e as informações de dependência sobre cada fórmula.

O Excel tem fases de cálculo distintas:

1. Criar a cadeia de cálculo inicial e determinar como começar a calcular. Esta fase ocorre quando a pasta de trabalho é carregada na memória.

2. Controlar dependências, sinalizar células como não calculadas e atualizar a cadeia de cálculo. Esta fase é executada em cada entrada ou alteração de mesmo no modo de cálculo manual. Normalmente, isso é executado tão rápido que você não percebe, mas em casos complexos, a resposta pode ser lenta.

3. Calcule todas as fórmulas. Como parte do processo de cálculo, o Excel reordena e reestrutura a cadeia de cálculo para otimizar novos recálculos futuros.

4. Atualize as partes visíveis das janelas do Excel.

A terceira fase é executada em cada cálculo ou recálculo. O Excel tenta calcular uma fórmula na cadeia de cálculo por vez, mas se uma fórmula depende de uma ou mais fórmulas que ainda não foram calculadas, a fórmula será enviada para a cadeia para ser calculada novamente mais tarde. Isso significa que uma fórmula pode ser calculada várias vezes por recálculo.

A segunda vez que você calcula uma pasta de trabalho costuma ser consideravelmente mais rápida do que a primeira vez. Isso ocorre por vários motivos:

• Normalmente, o Excel recalcula somente as células que foram alteradas e suas dependentes.

• O Excel armazena e reutiliza a sequência de cálculo mais recente para que ela possa economizar a maior parte do tempo usado para determinar a sequência de cálculos.

• Com vários computadores principais, o Excel tenta otimizar a maneira como os cálculos são distribuídos entre os núcleos com base nos resultados do cálculo anterior.

• Em uma sessão do Excel, e o Windows e do Excel armazenam programas e dados usados em cache para um acesso mais rápido.

Cálculo de pastas de trabalho, planilhas e intervalos

Você pode controlar o que é calculado usando os diferentes métodos de cálculo do Excel.

Calcular todas as pastas de trabalho abertas

Cada recálculo e cálculo completo calcula todas as pastas de trabalho que estão abertas atualmente, resolve qualquer dependências dentro e entre pastas de trabalho e planilhas e redefine todas as células (sujas) calculadas anteriormente conforme calculado.

Calcular as planilhas selecionadas

Você também pode recalcular apenas as planilhas selecionadas usando Shift+F9. Isso resolve as dependências da planilha e redefine todas as células (sujo) não calculadas anteriormente, conforme calculado.

Nas versões anteriores do Excel, o comportamento era diferente e sujo células não foram definidas como calculadas após a conclusão do cálculo. Se as funções definidas pelo usuário dependeram desse comportamento, essas funções deverão se tornar voláteis, conforme explicado na seção funções voláteis deste artigo.

Calcular um intervalo de células

O Excel também permite que o cálculo de um intervalo de células usando o Visual Basic for Applications (VBA)Range.CalculateRowMajorOrder e Range.Calculate:

• Range.CalculateRowMajorOrder calcula o intervalo da esquerda para a direita e de cima para baixo, ignorando todas as dependências.

• Range.Calculate calcula o intervalo de resolução de todas as dependências dentro do intervalo.

Como o CalculateRowMajorOrder não resolve dependências dentro do intervalo dentro do qual está sendo calculado, normalmente é consideravelmente mais rápido do que Range.Calculate. No entanto, ele deve ser usado com cuidado porque ele pode não retornar os mesmos resultados que Range.Calculate.

Range.Calculate é uma das ferramentas mais úteis do Excel para otimizar o desempenho, pois você pode usá-la para o tempo e para comparar a velocidade de cálculo de fórmulas diferentes.

Para saber mais, confira Desempenho do Excel: Melhorias de desempenho e limite.

Funções voláteis

Uma função volátil sempre é recalculada em cada recálculo, mesmo que não pareça ter qualquer precedente alterado. O uso de muitas funções voláteis diminui cada recálculo mas não faz diferença para um cálculo completo. Você pode fazer uma função volátil definida pelo usuário, incluindo Application.Volatile no código de função.

Algumas das funções internas do Excel são voláteis óbvios: RAND(), NOW(), TODAY(). Outros são menos óbvios voláteis: OFFSET(), CELL(), INDIRECT(), INFO().

Algumas funções documentadas anteriormente como voláteis não são de fato voláteis: INDEX(), ROWS(), COLUMNS(), AREAS().

Ações voláteis

Ações voláteis são ações que disparam um recálculo e incluem o seguinte:

• Clicar em um divisor de linha ou coluna no modo automático.
• Inserir ou excluir linhas, colunas ou células em uma planilha.
• Adicionar, alterar ou excluir nomes definidos.
• Renomear planilhas ou alterar a posição da planilha no modo automático.
• Filtrar, ocultar ou não ocultar linhas.
• Abrir uma pasta de trabalho no modo automático. Se a pasta de trabalho foi calculada pela última vez em uma versão diferente do Excel, abrir a pasta de trabalho geralmente resulta em um cálculo completo.
• Salvar uma pasta de trabalho no modo manual, se a opção Calcular antes de salvar for selecionada.

Fórmulas e circunstâncias da avaliação de nome.

Uma fórmula ou parte de uma fórmula é imediatamente avaliada (calculada), mesmo no modo de cálculo manual, ao fazer um destes procedimentos:

• Inserir ou editar a fórmula.
• Inserir ou editar a fórmula usando o Assistente de Função.
• Inserir a fórmula como um argumento no Assistente de Função.
• Selecione a fórmula na barra de fórmulas e pressione F9 (pressione Esc para desfazer e reverter para a fórmula) ou clique em Avaliar Fórmula.

Uma fórmula está sinalizada como não calculada quando se refere a (depende de) uma célula ou fórmula que tem uma destas condições:

• Foi inserida.
• Foi alterada.
• Está em uma lista de Filtro Automático e a lista suspensa de critérios foi habilitada.
• Está sinalizado como não calculado.

Uma fórmula que está sinalizada como não calculada é avaliada quando a planilha, pasta de trabalho ou instância do Excel que a contém é calculada ou recalculada.

As circunstâncias que fazem com que um nome definido seja avaliado diferem daquelas para uma fórmula em uma célula:

• Um nome definido é avaliado sempre que uma fórmula que se refere a ele é avaliada para que o uso de um nome em várias fórmulas possa fazer com que o nome seja avaliado várias vezes.
• Nomes que não são referenciados por todas as fórmulas não são calculados até mesmo por um cálculo completo.

Tabelas de dados

As tabelas de dados do Excel (grupo ferramentas de dados de guia > de dadosgrupo> What-If Analysis >Data Table) não devem ser confundidas com o recurso de tabela (Tabela Inicial>Estilos de grupo >Formatar como Tabela ou, Inserir tabela >Tabelasde grupo>). As tabelas de dados do Excel fazer vários novos cálculos da pasta de trabalho, cada uma delas orientadas pelos diferentes valores na tabela. O Excel calcula primeiro a pasta de trabalho normalmente. Para cada par de valores de linha e coluna, ele substitui os valores, faz um recálculo de encadeamento único e armazena os resultados na tabela de dados.

O recálculo de tabela de dados sempre usa apenas um processador.

As tabelas de dados oferecem uma maneira conveniente para calcular múltiplas variações e exibir e comparar os resultados das variações. Use a opção de cálculo Automático, exceto Tabelas para impedir que o Excel acione diversos cálculos em cada cálculo automaticamente, mas ainda calcule todas as fórmulas dependentes exceto tabelas.

Controlando opções de cálculo

O Excel tem uma variedade de opções que permitem controlar o modo de cálculo. Você pode alterar as opções usadas com mais frequência no Excel usando o grupo Cálculo na guia Fórmulas na faixa de opções.

Figura 1. Grupo de cálculo na guia Fórmulas

Para ver mais opções de cálculo do Excel, na guia Arquivo, clique em Opções. Na caixa de diálogo Opções do Excel, clique na guia Fórmulas.

Figura 2. Opções de cálculo na guia Fórmulas, nas Opções do Excel

Muitas opções de cálculo (Automática, Automático, exceto para tabelas de dados, Manual, Recalcular pasta de trabalho antes de salvar) e as configurações de iteração (Habilitar cálculo iterativo, Número máximo de iterações, Mudança máxima) operam no nível do aplicativo em vez do nível da pasta de trabalho (elas são as mesmas para todas os pastas de trabalho).

Para localizar opções de cálculo avançadas na guia Arquivo, clique em Opções. Na caixa de diálogo Opções do Excel, clique em Avançado. Na seção Fórmulas defina as opções de cálculo.

Figura 3. Opções avançadas de cálculo

Quando você inicia o Excel ou quando ele está em execução sem nenhuma pasta de trabalho aberta, as configurações de iteração e o modo de cálculo inicial são definidos a partir da primeira pasta de trabalho não-modelo, não-suplemento que você abre. Isso significa que as configurações de cálculo em pastas de trabalho abertas posterioremente são ignoradas, embora, obviamente, você possa alterar manualmente as configurações no Excel a qualquer momento. Quando você salva uma pasta de trabalho, as configurações atuais do cálculo são armazenadas na pasta de trabalho.

Cálculo automático

O modo de cálculo automático significa que o Excel recalcula automaticamente todas as pastas de trabalho em todas as alterações e quando você abre uma pasta de trabalho. Normalmente, quando você abre uma pasta de trabalho no modo automático e o Excel recalcula, não vê o recálculo porque nada mudou desde que a pasta de trabalho foi salva.

Você pode notar esse cálculo quando abre uma pasta de trabalho em uma versão mais recente do Excel do que você usou a última vez em que a pasta de trabalho foi calculada (por exemplo, o Excel 2016 ou o Excel 2013). Como os mecanismos de cálculo do Excel são diferentes, o Excel executa um cálculo completo ao abrir uma pasta de trabalho que foi salva em uma versão anterior do Excel.

Cálculo manual

O modo de cálculo manual significa que o Excel recalcula todas as pastas de trabalho abertas somente quando você solicitar pressionando F9 ou Ctrl + Alt + F9 ou, quando você salvar uma pasta de trabalho. Para pastas de trabalho que usam mais de uma fração de segundo para recalcular, você deve definir o cálculo para o modo manual para evitar um atraso quando você faz alterações.

O Excel tez diz quando uma pasta de trabalho no modo manual precisa de recálculo exibindo Calcular na barra de status. A barra de status também exibe Calcular se sua pasta de trabalho contiver referências circulares e a opção de iteração estiver selecionada.

Configurações de iteração

Se você tiver referências circulares intencionais na pasta de trabalho, as configurações de iteração permitem que você controle o número máximo de vezes que a pasta de trabalho é recalculada (iterações) e o critério de convergência (alteração máxima: quando interromper). Desmarque a caixa de iteração para que, se você tiver referências circulares acidentais, o Excel avise e não tente resolvê-las.

Propriedade ForceFullCalculation da pasta de trabalho

Quando você define essa propriedade de pasta de trabalho como Verdadeira, o recálculo inteligente do Excel é desativado e cada recálculo recalcula todas as fórmulas nas pastas de trabalho abertas. Para algumas pastas de trabalho complexas, o tempo necessário para criar e manter as árvores de dependência necessárias para o Recálculo Inteligente é maior do que tempo salvo por Recálculo Inteligente.

Se sua pasta de trabalho levar muito tempo para ser aberta, ou se pequenas alterações levarem muito tempo no modo de cálculo manual, pode valer a pena tentar o ForceFullCalculation.

Calcular será exibida na barra de status, se a propriedade da pasta de trabalho ForceFullCalculation for definida como Verdadeira.

Você pode controlar essa configuração usando VBE (Alt + F11), selecionando ThisWorkbook no Explorador de projeto (Ctrl + R) e mostrando a Janela de Propriedades (F4).

Figura 4. Definindo a propriedade Workbook.ForceFullCalculation

Faça as pastas de trabalho calcularem mais rápido

Use as seguintes etapas e métodos para fazer suas pastas de trabalho calcularem com maior rapidez.

Vários núcleos e velocidade de processador

Na maioria das versões do Excel, um processador mais rápido, obviamente, permitirá cálculos mais rápidos no Excel. O mecanismo de vários threads introduzido do Excel 2007 permite que o Excel faça excelente uso de sistemas multiprocessadores, e você pode esperar ganhos significativos de desempenho com a maioria das pastas de trabalho.

Para a maioria das pastas de trabalho grandes, o desempenho de cálculo de vários processadores aumentam quase que linearmente com o número de processadores físicos. No entanto, o hyper-threading de processadores físicos produz apenas um pequeno ganho de desempenho.

Para saber mais, confira Desempenho do Excel: Melhorias de desempenho e limite.

RAM

A paginação para um arquivo de paginação de memória virtual é lenta. Você deve ter RAM físico suficiente para o sistema operacional, Excel e suas pastas de trabalho. Se você tiver mais do que uma atividade ocasional no disco rígido durante o cálculo e não estiver executando funções definidas pelo usuário que acionam a atividade do disco, você precisará de mais RAM.

Conforme mencionado, as versões recentes do Excel podem fazer uso efetivo de grandes quantidades de memória e a versão de 32 bits do Excel 2007 e do Excel 2010 podem lidar com uma única pasta de trabalho ou uma combinação de pastas de trabalho usando até 2 GB de memória.

As versões de 32 bits do Excel 2013 e Excel 2016 que usam o recurso LAA (Reconhecimento de Endereço Grande) podem usar até 3 ou 4 GB de memória, dependendo da versão do Windows instalada. A versão de 64 bits do Excel pode lidar com pastas de trabalho maiores. Para saber mais, confira a seção "grandes conjuntos de dados, LAA e Excel de 64 bits" Desempenho do Excel: Melhorias de desempenho e limite.

Uma orientação aproximada para um cálculo eficiente é ter RAM suficiente para armazenar o maior conjunto de pastas de trabalho que você precisa abrir ao mesmo tempo, além de 1 a 2 GB para o Excel e sistema operacional, além de RAM adicional para outros aplicativos em execução.

Medir o tempo de cálculo

Para fazer as pastas de trabalho calcularem mais rápido, você deve conseguir medir precisamente o tempo de cálculo. Você precisa de um temporizador mais rápido e mais preciso do que a função VBA Tempo. A função MICROTIMER() mostrada no exemplo a seguir usa chamadas de API do Windows para o cronômetro de alta resolução do sistema. Ela poderá medir intervalos de tempo para números menores de microssegundos. Esteja ciente de que, como o Windows é um sistema operacional multitarefa e porque a segunda vez que você calcula algo, pode ser mais rápido do que a primeira vez, as vezes que você obtém geralmente não se repetem exatamente. Para obter maior precisão, meça tarefas de cálculo do tempo várias vezes e faça uma média dos resultados.

Para saber mais sobre como o Editor do Visual Basic pode afetar consideravelmente o desempenho da função definida pelo usuário do VBA, confira a seção "funções mais rápidas definidas pelo usuário do VBA" em Dicas para otimizar as obstruções de desempenho do Excel.

#If VBA7 Then
    Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias _
        "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias _
        "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#Else
    Private Declare Function getFrequency Lib "kernel32" Alias _
        "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
    Private Declare Function getTickCount Lib "kernel32" Alias _
        "QueryPerformanceCounter" (cyTickCount As Currency) As Long
#End If
Function MicroTimer() As Double
'

' Returns seconds.
    Dim cyTicks1 As Currency
    Static cyFrequency As Currency
    '
    MicroTimer = 0

' Get frequency.
    If cyFrequency = 0 Then getFrequency cyFrequency

' Get ticks.
    getTickCount cyTicks1                            

' Seconds
    If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency 
End Function

Para medir o tempo de cálculo, você deve ligar o método de cálculo apropriado. Essas sub-rotinas oferecem o cálculo de tempo para um intervalo, horas de recálculo para uma planilha ou pastas de trabalho ou todas as pastas de trabalho abertas, ou o tempo de cálculo para todas as pastas de trabalho abertas.

Copie todas essas funções e subrotinas em um módulo do VBA padrão. Para abrir o editor do VBA, pressione Alt + F11. No menu Inserir, selecione o Móduloe, em seguida, copie o código para o módulo.

Sub RangeTimer()
    DoCalcTimer 1
End Sub
Sub SheetTimer()
    DoCalcTimer 2
End Sub
Sub RecalcTimer()
    DoCalcTimer 3
End Sub
Sub FullcalcTimer()
    DoCalcTimer 4
End Sub

Sub DoCalcTimer(jMethod As Long)
    Dim dTime As Double
    Dim dOvhd As Double
    Dim oRng As Range
    Dim oCell As Range
    Dim oArrRange As Range
    Dim sCalcType As String
    Dim lCalcSave As Long
    Dim bIterSave As Boolean
    '
    On Error GoTo Errhandl

' Initialize
    dTime = MicroTimer              

    ' Save calculation settings.
    lCalcSave = Application.Calculation
    bIterSave = Application.Iteration
    If Application.Calculation <> xlCalculationManual Then
        Application.Calculation = xlCalculationManual
    End If
    Select Case jMethod
    Case 1

        ' Switch off iteration.

        If Application.Iteration <> False Then
            Application.Iteration = False
        End if
        
        ' Max is used range.

        If Selection.Count > 1000 Then
            Set oRng = Intersect(Selection, Selection.Parent.UsedRange)
        Else
            Set oRng = Selection
        End If

        ' Include array cells outside selection.

        For Each oCell In oRng
            If oCell.HasArray Then
                If oArrRange Is Nothing Then 
                    Set oArrRange = oCell.CurrentArray
                End If
                If Intersect(oCell, oArrRange) Is Nothing Then
                    Set oArrRange = oCell.CurrentArray
                    Set oRng = Union(oRng, oArrRange)
                End If
            End If
        Next oCell

        sCalcType = "Calculate " & CStr(oRng.Count) & _
            " Cell(s) in Selected Range: "
    Case 2
        sCalcType = "Recalculate Sheet " & ActiveSheet.Name & ": "
    Case 3
        sCalcType = "Recalculate open workbooks: "
    Case 4
        sCalcType = "Full Calculate open workbooks: "
    End Select

' Get start time.
    dTime = MicroTimer
    Select Case jMethod
    Case 1
        If Val(Application.Version) >= 12 Then
            oRng.CalculateRowMajorOrder
        Else
            oRng.Calculate
        End If
    Case 2
        ActiveSheet.Calculate
    Case 3
        Application.Calculate
    Case 4
        Application.CalculateFull
    End Select

' Calculate duration.
    dTime = MicroTimer - dTime
    On Error GoTo 0

    dTime = Round(dTime, 5)
    MsgBox sCalcType & " " & CStr(dTime) & " Seconds", _
        vbOKOnly + vbInformation, "CalcTimer"

Finish:

    ' Restore calculation settings.
    If Application.Calculation <> lCalcSave Then
         Application.Calculation = lCalcSave
    End If
    If Application.Iteration <> bIterSave Then
         Application.Iteration = bIterSave
    End If
    Exit Sub
Errhandl:
    On Error GoTo 0
    MsgBox "Unable to Calculate " & sCalcType, _
        vbOKOnly + vbCritical, "CalcTimer"
    GoTo Finish
End Sub

Para executar as sub-rotinas no Excel, pressione Alt + F8. Selecione a subrotina desejada e clique em Executar.

Figura 5. A janela Macro do Excel mostrando os temporizadores de cálculo

Localizar e priorizar as obstruções de cálculo

A maioria das pastas de trabalho de cálculo lento tem apenas algumas áreas de problemas ou obstruções que consomem a maior parte do tempo de cálculo. Se você ainda não sabe onde eles estão, use a abordagem de detalhamento descrita nesta seção para localizá-los. Se você souber onde elas, você deve medir o tempo de cálculo usado por cada obstrução para que você possa priorizar seu trabalho para removê-las.

Abordagem de aprofundamento para encontrar obstruções

A abordagem de aprofundamento inicia com o cronograma do cálculo da pasta de trabalho, o cálculo de cada planilha e os blocos de fórmulas nas planilhas de cálculo lento. Faça cada etapa na ordem e anote as horas de cálculo.

Para localizar as obstruções usando abordagem de aprofundamento

1. Verifique se você tem apenas uma pasta de trabalho aberta e nenhuma tarefa sendo executada.

2. Defina o cálculo como manual.

3. Fazer uma cópia de backup da pasta de trabalho.

4. Abra a pasta de trabalho que contém macros Medidores de Cálculo ou adicione-os na pasta de trabalho.

5. Verifique o intervalo usado pressionando Ctrl + End em cada planilha.

   Isso mostra onde está a última célula usada. Se isso estiver além de onde você espera que esteja, considere deletar as colunas e linhas e salvar a pasta de trabalho. Para saber mais, confira a seção "Minimizar o intervalo usado" em Dicas para otimizar as obstruções de desempenho do Excel.

6. Executar a macro FullCalcTimer.

   O tempo para calcular todas as fórmulas na pasta de trabalho é geralmente o pior momento.

7. Executar a macroRecalcTimer.

   O recálculo imediatamente depois de um cálculo completo geralmente é o melhor momento.

8. Calcule a volatilidade da pasta de trabalho como a razão entre a hora de recálculo e o tempo total de cálculo.

   Isso mede até que ponto as fórmulas e a avaliação da cadeia de cálculo são obstáculos.

9. Ative cada planilha e execute a macro SheetTimer.

   Como você recalculou apenas a pasta de trabalho, isso te dará o tempo de recálculo de cada planilha. Isso deve te habilitar a determinar quais são as planilhas problemáticas.

10. Executar a macroRangeTimer em blocos selecionados de fórmulas.

11. Para cada planilha com problema, divida as colunas ou linhas em um pequeno número de blocos.

12. Selecione cada bloco por sua vez e, em seguida, execute a macro RangeTimer no bloco.

13. Se necessário, detalhe ainda mais por subdividindo cada bloco em um número menor de blocos.

14. Priorize as obstruções.

Acelerando a cálculos e reduzindo as obstruções

Não é o número de fórmulas ou o tamanho de uma pasta de trabalho que consome o tempo de cálculo. É o número de referências de células e operações de cálculo e a eficiência das funções que estão sendo usadas.

Como a maioria das planilhas são elaboradas copiando as fórmulas que possuem uma combinação de referências absolutas e relativas, elas geralmente contêm um grande número de fórmulas que possuem referências e cálculos repetidos ou duplicados.

Evite mega fórmulas complexas e fórmulas de matriz. Em geral, é melhor ter mais linhas e colunas e menos cálculos complexos. Isso dá para o recálculo inteligente e para os vários threads de cálculo no Excel uma oportunidade melhor de otimizar os cálculos. Também é mais fácil de entender e depurar. A seguir estão algumas regras que ajudam a acelerar os cálculos da pasta de trabalho.

Primeira regra: remova cálculos desnecessários, duplicados e repetidos

Procure cálculos duplicados, repetidos e desnecessários, e descubra aproximadamente quantas referências e cálculos de células são necessários para o Excel calcular o resultado dessa obstrução. Pense como você pode obter o mesmo resultado com menos referências e cálculos.

Geralmente isso envolve uma ou mais das seguintes etapas:

• Reduza o número de referências em cada fórmula.

• Mova os cálculos repetidos para uma ou mais células de Ajuda e faça referência às células auxiliares das fórmulas originais.

• Use colunas e linhas adicionais para calcular e armazenar os resultados intermediários uma vez para que você possa reutilizá-las em outras fórmulas.

Segunda regra: use a função mais eficiente possível

Quando você encontrar uma obstrução que envolva as fórmulas de uma função ou de uma matriz, determine se há uma maneira mais eficiente de obter o mesmo resultado. Por exemplo:

• Pesquisas em dados classificados podem ser dezenas ou centenas de vezes mais eficientes do que pesquisas em dados não classificados.

• Funções definidas pelo usuário do VBA normalmente são mais lentas do que as funções internas do Excel (embora funções do VBA cuidadosamente escritas possam ser rápidas).

• Minimizar o número de células usadas nas funções como SOMAR e SOMASE. O cálculo de tempo é proporcional ao número de células usadas (células não utilizadas são ignoradas).

• Considere substituir as fórmulas de matriz lento com funções definidas pelo usuário.

Terceira regra: faça bom uso de vários threads de cálculo e do recálculo inteligente

Quanto melhor você usar os vários threads de cálculo e o recálculo inteligente no Excel, menos processamento precisará ser feito toda vez que o Excel recalcular, então:

• Evitar tais funções voláteis como INDIRECT e DESLOC onde você puder, a menos que elas sejam significativamente mais eficientes do que as alternativas. (O uso bem elaborado do deslocamento geralmente é mais rápido.)

• Diminua o tamanho dos intervalos que você está usando em funções e fórmulas de matriz.

• Divida fórmulas de matriz e mega-fórmulas em linhas e colunas auxiliares separadas.

• Evite funções com um único segmento:

  • PHONETIC
  • CÉL quando o argumento "formato" ou "endereço" é usado
  • INDIRETO
  • GETPIVOTDATA
  • MEMBROCUBO
  • VALORCUBO
  • PROPRIEDADEMEMBROCUBO
  • CONJUNTOCUBO
  • MEMBROCLASSIFICADOCUBO
  • MEMBROKPICUBO
  • CONTAGEMCONJUNTOCUBO
  • ENDEREÇO quando o quinto parâmetro (nome_da_planilha) é 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
  • Funções VBA e COM de suplementos definidas pelo usuário

• Evite o uso interativo de tabelas de dados e referências circulares: ambos sempre calculam encadeamentos únicos.

Quarta regra: programe e teste cada alteração

Algumas das alterações feitas podem ser surpreendentes, não dando a resposta que você pensou que elas dariam ou calculando mais lentamente do que você esperava. Portanto, você deve programar e testar a cada alteração, da seguinte maneira:

1. Programe a fórmula a ser alterada usando a macroRangeTimer.

2. Faça a alteração.

3. Programe a fórmula alterada usando a macro RangeTimer.

4. Verifique se a fórmula alterada ainda oferece a resposta correta.

Exemplos de regras

As seções a seguir fornecem exemplos de como usar as regras para acelerar o cálculo.

Somas de período até esta data

Por exemplo, você precisa calcular as somas atualizada no período de uma coluna que contém 2.000 números. Suponha que a coluna A contenha os números e que a coluna B e a coluna C contenham os totais do período até a data.

Você pode escrever a fórmula usando SOMA, que é uma função eficiente.

  B1=SUM($A$1:$A1)
  B2=SUM($A$1:$A2)

Figura 6. Exemplo de fórmulas de SOMA de período para data

Copie a fórmula até B2000.

Quantas referências de célula são adicionadas pela SOMA em total? B1 se refere a uma célula e B2000 se refere a 2.000 células. A média é 1.000 referências por célula, portanto o número total de referências é 2 milhões. Selecionar 2.000 fórmulas e usar a macro RangeTimer mostra que as 2.000 fórmulas na coluna B são calculadas em 80 milissegundos. A maioria desses cálculos são duplicados várias vezes: SOMA adiciona A1 para A2 em cada fórmula de B2:B2000.

Você poderá descartar dessa duplicação se escrever as fórmulas da seguinte maneira.

  C1=A1
  C2=C1+A1

Copie esta fórmula para até C2000.

Agora quantas referências de célula são adicionadas no total? Cada fórmula, exceto a primeira fórmula, usa duas referências de célula. Portanto, o total é 1999 * 2 + 1 = 3999. Este é um fator de 500 menos referências de célula.

RangeTimer indica que as fórmulas na coluna C 2.000 calculam em 3,7 milissegundos comparados aos 80 milissegundos coluna B. Essa alteração tem um fator de melhoria de desempenho de apenas 80/3,7 = 22 em vez de 500 porque há uma pequena sobrecarga por fórmula.

Tratamento de erros

Se você tiver uma fórmula de cálculos intensivo onde quer que o resultado seja exibido como zero se houver um erro (isso ocorre com frequência em pesquisas com correspondência exata), você pode escrever isso de várias maneiras.

• Você pode escrevê-la como uma única fórmula, isso é lento:

  B1=IF(ISERROR(time expensive formula),0,time expensive formula)

• Você pode escrevê-la como duas fórmulas, isso é rápido:

  A1=time expensive formula

  B1=IF(ISERROR(A1),0,A1)

• Ou você pode usar a função SEERRO, que foi desenvolvida para ser rápida e simples e é uma única fórmula:

  B1=IFERROR(time expensive formula,0)

Dinâmica de contagem exclusiva

Figura 7. Exemplo de lista de dados para contagem exclusiva

Se você tiver uma lista de 11.000 linhas de dados na coluna A, que altera com frequência, e você precisar de uma fórmula que calcule dinamicamente o número de itens exclusivos na lista, ignorando espaços em branco, a seguir estão várias soluções possíveis.

• Fórmulas de matriz (use Ctrl + Shift + Enter;) RangeTimer indica que isso leva 13,8 segundos.

  {=SUM(IF(LEN(A2:A11000)>0,1/COUNTIF(A2:A11000,A2:A11000)))}
  

• SOMARPRODUTO geralmente calcula mais rápido do que uma fórmula de matriz equivalente. Essa fórmula leva 10.0 segundos e oferece um fator de melhoria de 13.8/10.0=1.38, o que é melhor, mas não bom o suficiente.

  =SUMPRODUCT((A2:A11000<>"")/COUNTIF(A2:A11000,A2:A11000&amp;""))
  

• Funções Definidas pelo Usuário. O exemplo a seguir mostra uma função definida pelo usuário do VBA que usa o fato de que o índice de um conjunto deve ser exclusivo. Para obter explicação de algumas técnicas que são usadas, confira a seção sobre funções definidas pelo usuário na seção "Forma eficiente usando funções" em Dicas para otimizar as obstruções de desempenho do Excel. Essa fórmula, =COUNTU(A2:A11000), leva apenas 0,061 segundos. Isso dá um fator de melhoria de 13.8/0.061=226.

  Public Function COUNTU(theRange As Range) As Variant
      Dim colUniques As New Collection
      Dim vArr As Variant
      Dim vCell As Variant
      Dim vLcell As Variant
      Dim oRng As Range
  
      Set oRng = Intersect(theRange, theRange.Parent.UsedRange)
      vArr = oRng
      On Error Resume Next
      For Each vCell In vArr
      If vCell <> vLcell Then
          If Len(CStr(vCell)) > 0 Then
               colUniques.Add vCell, CStr(vCell)
          End If
      End If
      vLcell = vCell
      Next vCell
  
      COUNTU = colUniques.Count
  End Function
  

• Adicionar uma coluna de fórmulas. Se você examinar o exemplo anterior dos dados, você poderá ver que ela está classificada (o Excel leva 0,5 segundos para classificar as 11.000 linhas). Para explorar isso adicionando uma coluna de fórmulas que verifica se os dados nesta linha são os mesmos que dados da linha anterior. Se for diferente, a fórmula retornará 1. Caso contrário, retornará 0.

  Adicione esta fórmula para a célula B2.

    =IF(AND(A2<>"",A2<>A1),1,0)
  

  Copie a fórmula e adicione uma fórmula para adicionar a coluna B.

    =SUM(B2:B11000)
  

  Um cálculo completo de todas essas fórmulas leva 0,027 segundos. Isso dá um fator melhoria de 13.8/0.027=511.

Conclusão

O Excel permite que você gerencie com eficiência planilhas muito maiores e oferece melhorias significativas na velocidade de cálculo em comparação com as versões anteriores. Ao criar grandes planilhas, é fácil criá-las de uma maneira faça com que elas sejam calculadas lentamente. Calcular planilhas lentamente aumenta os erros porque os usuários acham difícil manter a concentração enquanto o cálculo está sendo feito.

Usando um conjunto de técnicas simples, você pode acelerar a maioria dos cálculos lentos das planilhas por um fator de 10 ou 100. Você também pode aplicar estas técnicas conforme você projeta e cria planilhas para garantir que elas calculem rapidamente.

Confira também

• Desempenho do Excel: Melhorias de desempenho e limite
• Dicas para otimizar as obstruções de desempenho do Excel
• Excel Developer Center

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.