0153-Localizando e priorizando as restrições de cálculos (Bottlenecks)

Tradução do trecho do artigo original da Microsoft: Improving performance in Excel 2007

Muitos arquivos do Excel que realizam cálculos de forma demorada, possuem poucas áreas problemáticas que provocam o maior consumo de recursos e prejudicam a performance. Se você não sabe como localizá-las, utilize as dicas a seguir para aprender como fazê-lo. Após localizá-las, é necessário mensurar o tempo despendido em cada restrição, para que possa priorizar o trabalho de redução do tempo de processamento.

Abordagem Drill-Down para localizar as restrições

Esta abordagem se inicia pela cronometragem do tempo total despendido na pasta de trabalho e, incrementando o detalhamento, avaliar as planilhas e os intervalos que apresentam maior lentidão no processamento.

Localizando as restrições

  1. Assegure-se de manter apenas uma pasta de trabalho aberta;
  2. Coloque o Excel em modo de cálculo manual;
  3. Crie uma cópia de backup da pasta de trabalho;
  4. Transfira as macros para cronometragem para o arquivo a ser avaliado;
  5. Selecione o intervalo de células que está sendo utilizado em cada planilha (CTRL+SHFT+END). Desta forma irá selecionar o conjunto de todas as células utilizadas na planilha. Se o resultado estiver além das suas expectativas, considere a possibilidade de eliminar linhas e colunas que não contribuem para os resultados desejados.
  6. Execute a macro FullCalcTimer.

A primeira vez em que os cálculos são executados, normalmente, representa a situação mais conservativa..

  1. Execute a macro RecalcTimer.

Neste recálculo, temos uma avaliação do tempo demandado mais próxima da realidade de uso da planilha.

  1. Determine o parâmetro de volatilidade como a razão entre os tempos de recálculo e de cálculo inicial.

Esta medição avalia o quanto as formulas avaliadas contribuem para o aumento do tempo demandado para os cálculos.

  1. Execute a macro SheetTimer para cada uma das planilhas.

Como você já recalculou a pasta de trabalho, este resultado fornece o tempo de recálculo para cada planilha. Este procedimento permite identificar quais as planilhas problemáticas.

  1. Execute a macro RangeTimer nos blocos selecionados de fórmulas.
    • Para cada planilha identificada como problemática, seccione as linhas e/ou colunas em blocos menores.
    • Selecione cada um dos blocos menores e execute RangeTimer.
    • Se for necessário, continue fracionando em blocos menores.
  2. Com base nos resultados obtidos, defina a lista de priorização das restrições.

Acelerando os cálculos e reduzindo as restrições.

Não é o número de formulas, ou tamanho do intervalo que ocupam que determinam o tempo de cálculo necessário. Os fatores determinantes são:

  • Quantidade de células referenciadas;
  • Número de operações realizadas;
  • Eficiência das funções utilizadas.

Como muitas planilhas são construídas a partir da cópia de fórmulas que contém uma mistura de referências relativas e absolutas, é comum que haja uma grande quantidade de fórmulas que contém referências repetidas ou duplicadas.

Evite mega-fórmulas complexas e fórmulas matriciais. Normalmente é melhor possuir mais linhas e colunas do que poucas fórmulas de elevada complexidade. Isto permite que o Excel 2007 possa utilizar os recursos de otimização para acelerar os cálculos. Isto também facilita o processo de compreensão e compilação. (Nota do blog: esta é uma questão que deve ser analisada criteriosamente. Temos obtido melhores resultados ao trabalharmos com fórmulas de maior complexidade do que aqueles obtidos com a utilização de colunas auxiliares. Obviamente, não podemos abusar do seu uso, pois as mega-fórmulas podem atuar como freios para os cálculos).

A seguir, algumas regras para acelerar a velocidade dos cálculos.

1ª regra: Elimine cálculos redundantes e desnecessários.

Audite as suas formulas e identifique quantas referências e cálculos são necessários para obtenção dos resultados. Em seguida, tente obter o mesmo resultado reduzindo o esforço necessário.

Para tanto, normalmente lançamos mão de uma ou mais das etapas a seguir:

  • Reduza o número de referências em cada fórmula;
  • Crie células auxiliaries para fracionar os cálculos repetidos e referencie as células auxiliares a partir das fórmulas originais;
  • Utilize linhas e colunas adicionais para calcular e armazenar os resultados intermediários que podem ser, então, referenciados por outras fórmulas.

2ª regra: Use a função mais eficiente possível

Quando se confronter com uma restrição que envolve uma função específica, verifique a possibilidade de obtenção do resultado utilizando outras abordagens:

  • Pesquisar dados ordenados é muito mais rápido do que pesquisá-los de forma não ordenada.
  • Funções criadas em VBA são, normalmente, mais lentas do que as funções nativas do Excel (porém funções otimizadas fornecem resultados de forma muito rápida).
  • Minimize o número de células utilizadas em funções como SOMA e SOMASE. O tempo de cálculo é proporcional ao número de células utilizadas.
  • Verifique se é possível substituir as formulas matriciais mais lentas por versões em VBA.

3ª regra: Faça bom uso do Smart Recalculation

  • Evite o uso de funções voláteis como INDIRETO e DESLOC quando possível, a menos que sejam mais eficientes do que as outras alternativas;
  • Minimize o uso de intervalos em fórmulas matriciais e funções;
  • Fracione as suas formulas matriciais e as mega-formulas em linhas e colunas auxiliaries.

4ª regra: Teste e mensure cada mudança

Algumas munaças realizadas podem ser surpreendentes: tanto na redução, quanto no aumento do tempo de processamento. Por isso é necessário testá-las

  1. Teste a fórmula original utilizando a macro RangeTimer;
  2. Realize as mudanças.
  3. Teste a nova versão da fórmula utilizando RangeTimer;
  4. Verifique se os resultados fornecidos se mantém inalterados.

Listagem das macros referenciadas:

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

' Calc 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.Calculation = bIterSave

End If

Exit Sub

Errhandl:

On Error GoTo 0

MsgBox "Unable to Calculate " & sCalcType, _

vbOKOnly + vbCritical, "CalcTimer"

GoTo Finish

End Sub

In Excel We Trust

Marcado com: , , , , , ,
Publicado em Nível: Avançado
Um comentário em “0153-Localizando e priorizando as restrições de cálculos (Bottlenecks)
  1. Victor Gamboa disse:

    Muito bom este artigo, embora haja muito mais a dizer neste capitulo, muitos usuários de Excel mal formados criam verdadeiros monstros.

    Quando tiver um pouco de tempo irei elaborar posts sobre esta matéria que poderemos trocar e referenciar entre os nosso bogs, considero este tema uma das bases de formação em Excel que na maioria das vezes é ignorado.

    VICTOR GAMBOA

    VG-EXCEL-GEST
    vg-excel-gest.blogspot.com/
    http://www.youtube.com/user/VGEXCELGEST2?feature=mhum
    http://www.facebook.com/vgexcelgest

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Visitas dos usuários
  • 1,969,548

Clique para assinar este blog e receber notificações de novos artigos por email.

Junte-se a 1.477 outros seguidores

Aumente sua interação

Siga-nos do twitter (@usuariosdoexcel).

Acesse o nosso fórum e compartilhe as suas dúvidas.

Curta nossa página no Facebook.

Junte-se ao grupo do LinkedIn.

Visite-nos utilizando o seu smartphone ou tablet.

Recomende este Blog
Visualizar notícias
Site monitorado:
Real Time Web Analytics

Clicky

%d blogueiros gostam disto: