none
Ajuda com desempenho de função RRS feed

  • Pergunta

  • Prezados,

    caso alguém possa ajudar, desejo saber se há como melhorar a performance de cáclulo da função abaixo, por favor. Conforme  quantidade de colunas e linhas aplicadas chega a quase 7 minutos para realizar o cálculo (CPU I7 Quatro Núcleos, 32GB Memória, 64Bits).

    Segue:

    ===========

    Function Regra1(Valor As Range, criteria As Range, criteria2 As Range) As Long
    Dim Celula As Range
    Dim Tempo As Date
    Dim Loc As String
    Dim Unico As New Collection
    Application.Volatile
    On Error Resume Next
    x = 3
    Tempo = criteria
    Loc = criteria2
    For Each Celula In Valor
    If Plan2.Cells(x, 5) = "Unidade Remota - " & Loc And Plan2.Cells(x, 10) = "Cadastramento" And Plan2.Cells(x, 11) = "Cadastrar dados do cliente" And Plan2.Cells(x, 12) = "Cadastro efetuado" And Plan2.Cells(x, 13) = Tempo Then
    a = "Unidade Remota - " & Loc & Plan2.Cells(x, 1) & Plan2.Cells(x, 10) & Plan2.Cells(x, 11) & Plan2.Cells(x, 12) & Tempo
    Unico.Add a, CStr(a)
    End If
    x = x + 1
    Next Celula
    On Error GoTo 0
    Regra1 = Unico.Count
    End Function

    ===================

    Grato,

    Raimundo

    terça-feira, 27 de agosto de 2013 13:20

Respostas

  • Não use UDF. Sugiro utilizar funções nativas do Excel mesmo, são bem mais rápidas.

    Na célula I7, cole entre com a fórmula abaixo e pressione Ctrl+Shift+Enter:

    =SOMA(SE(FREQÜÊNCIA(SE((RelatorioEventosWorkflow!$E$3:$E$6978="Unidade Remota - "&$B7)*(RelatorioEventosWorkflow!$J$3:$J$6978="Cadastramento")*(RelatorioEventosWorkflow!$K$3:$K$6978="Cadastrar dados do cliente")*(RelatorioEventosWorkflow!$L$3:$L$6978="Cadastro efetuado")*(RelatorioEventosWorkflow!$M$3:$M$6978=I$3);CORRESP(RelatorioEventosWorkflow!$A$3:$A$6978;RelatorioEventosWorkflow!$A$3:$A$6978;0));LIN(RelatorioEventosWorkflow!$A$3:$A$6978)-LIN(RelatorioEventosWorkflow!$A$3)+1);1))

    O construto SOMA(SE(FREQUÊNCIA(SE(... é o que você busca: um SOMARPRODUTO que elimina duplicatas.

    Você não precisa entender plenamente a fórmula para reproduzir nas outras células, basta entender que as condições da fórmula se encontram na expressão:

    (RelatorioEventosWorkflow!$E$3:$E$6978="Unidade Remota - "&$B7)*
    (RelatorioEventosWorkflow!$J$3:$J$6978="Cadastramento")*
    (RelatorioEventosWorkflow!$K$3:$K$6978="Cadastrar dados do cliente")*
    (RelatorioEventosWorkflow!$L$3:$L$6978="Cadastro efetuado")*
    (RelatorioEventosWorkflow!$M$3:$M$6978=I$3)

    Note que a condição é um conjunto de 5 multiplicações. O resto da fórmula permanece a mesma.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    segunda-feira, 9 de setembro de 2013 21:25
    Moderador

Todas as Respostas

  • Boa noite Weivisson!

    Fica difícil, apenas olhando esse código, sem uma pasta de trabalho real para analisar, qual a causa na demora da execução.

    Para entender como melhorar a performance do Excel eu sugiro a leitura de 3 artigos muito bons:

    • http://msdn.microsoft.com/en-us/library/office/ff726673(v=office.14).aspx
    • http://msdn.microsoft.com/en-us/library/office/ff700515(v=office.14).aspx
    • http://msdn.microsoft.com/en-us/library/office/ff700514(v=office.14).aspx

    Mas, se fosse eu, eu começaria por reduzir a troca de dados entre a planilha e o VBA. Como vc poderá ler nos artigos acima, esse é um ponto que pode ser tornar bastante crítico quando é preciso robustez. Dessa forma, eu colocaria todas as condições dentro da própria planilha (numa coluna oculta por exemplo) e passaria como valor para sua função. Também trocaria  a sua coleção "Unico" por um contador simples, já que seu conteúdo não é utilizado para mais nada na rotina (caso seu código não precise dela em outra parte).

    Espero que as dicas possam te ajudar.

    Um abraço.


    Filipe Magno

    quarta-feira, 28 de agosto de 2013 01:28
  • Olá,

    É possível melhorar bastante sua função. No entanto, seria melhor se você disponibilizasse para download uma amostra de sua pasta de trabalho para eu poder testar minha sugestão, já que fica difícil entender a função sem um exemplo prático.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    quarta-feira, 28 de agosto de 2013 01:33
    Moderador
  • Prezados,  de que forma eu poderia enviar o anexo para vcs, pode ser por e-mail?

    Grato,

    Raimundo

    quarta-feira, 28 de agosto de 2013 18:18
  • http://www.ambienteoffice.com.br/outros/como_disponibilizar_um_arquivo_para_download/

    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    quinta-feira, 29 de agosto de 2013 00:54
    Moderador
  • Caríssimos, segue o link para o arquivo:

    https://skydrive.live.com/redir?resid=B9E687823781A0A3!1888&authkey=!ACssp1CbUXOIz5I

    Só resta salientar que a função consiste em contar os valores únicos, sem repetição, de acordo com os parâmetros descritos em cada função.Grato,

    Grato,

    Raimundo

    sexta-feira, 30 de agosto de 2013 13:40
  • Filipe, conseguiu avaliar algo sobre o desempenho da função?

    Valeu,

    Raimundo

    segunda-feira, 2 de setembro de 2013 12:46
  • Felipe, conseguiu avaliar algo sobre o desempenho da função?

    Valeu,

    Raimundo


    segunda-feira, 2 de setembro de 2013 12:47
  • Raimundo,

    Desculpe a demora.

    Não consegui entender o que sua função faz. Poderia explicar o seu objetivo? Pode ser que eu sugira uma forma diferente de realizar seu cálculo.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    terça-feira, 3 de setembro de 2013 00:17
    Moderador
  • A função consiste em contar os valores únicos, sem repetição, de acordo com os parâmetros descritos em cada função.

    Abs.,

    Raimundo

    terça-feira, 3 de setembro de 2013 00:40
  • Transforme sua tabela num objeto ListObject (menu Página Inicial >> controle Formatar como Tabela). Para tal, você deve ter apenas uma linha de cabeçalho.

    Em seguida, escreva a fórmula abaixo no intervalo E7 e cole-a até a célula I10. Utilize a mesma lógica para fazer as fórmulas dos outros blocos:

    =SOMARPRODUTO(--(Tabela1[Localidade]="Unidade Remota - "&'KPI Desligamento'!$B7);--(Tabela1[Data Inicial]='KPI Desligamento'!E$3);--(Tabela1[Evento]="Cadastrar dados do cliente"))


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    quinta-feira, 5 de setembro de 2013 00:57
    Moderador
  • Felipe,

    eu já tinha avaliado a utilização do "somarproduto", porém eu fiquei emperrado na questão para contar somente valores únicos.

    Depois de aplicadas as regras , tenho q contar quantas propostas, eliminando as repetições, se enquadram na regra , por isso usei a coleção "Unico".

    Filtre na tabela, no exemplo que disponibilizei, a seguinte regra: Etapa = Entrevista; Finalizador <> Vazia

    Teremos 581 linhas, excluindo as duplicidades pela coluna "Proposta" restarão apenas 314 linhas. Avalie na função Regra3 como eu trato essa regra para contar na coleção "Unico".

    Foi justamente por não saber como eliminar essa duplicidade na contagem das propostas dentro do "somarproduto" que eu criei as funções.

    Por ainda não fazer ideia se é possível tratar isso no "somarproduto" ou em outra função do Excel que eu busquei alguma solução no fórum para otimizar o desempenho do cálculo, que acabou por demorar demasiadamente para processar as contagens das regras.

    Valeu,

    Raimundo

    quinta-feira, 5 de setembro de 2013 18:26
  • Não use UDF. Sugiro utilizar funções nativas do Excel mesmo, são bem mais rápidas.

    Na célula I7, cole entre com a fórmula abaixo e pressione Ctrl+Shift+Enter:

    =SOMA(SE(FREQÜÊNCIA(SE((RelatorioEventosWorkflow!$E$3:$E$6978="Unidade Remota - "&$B7)*(RelatorioEventosWorkflow!$J$3:$J$6978="Cadastramento")*(RelatorioEventosWorkflow!$K$3:$K$6978="Cadastrar dados do cliente")*(RelatorioEventosWorkflow!$L$3:$L$6978="Cadastro efetuado")*(RelatorioEventosWorkflow!$M$3:$M$6978=I$3);CORRESP(RelatorioEventosWorkflow!$A$3:$A$6978;RelatorioEventosWorkflow!$A$3:$A$6978;0));LIN(RelatorioEventosWorkflow!$A$3:$A$6978)-LIN(RelatorioEventosWorkflow!$A$3)+1);1))

    O construto SOMA(SE(FREQUÊNCIA(SE(... é o que você busca: um SOMARPRODUTO que elimina duplicatas.

    Você não precisa entender plenamente a fórmula para reproduzir nas outras células, basta entender que as condições da fórmula se encontram na expressão:

    (RelatorioEventosWorkflow!$E$3:$E$6978="Unidade Remota - "&$B7)*
    (RelatorioEventosWorkflow!$J$3:$J$6978="Cadastramento")*
    (RelatorioEventosWorkflow!$K$3:$K$6978="Cadastrar dados do cliente")*
    (RelatorioEventosWorkflow!$L$3:$L$6978="Cadastro efetuado")*
    (RelatorioEventosWorkflow!$M$3:$M$6978=I$3)

    Note que a condição é um conjunto de 5 multiplicações. O resto da fórmula permanece a mesma.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    segunda-feira, 9 de setembro de 2013 21:25
    Moderador
  • Fechou! Havia tentado com "Frequência", mas não usei o "Corresp".

    Muito obrigado e bola pra frente.. :)

    terça-feira, 10 de setembro de 2013 14:42