Ajuda com desempenho de função

Question

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

Answer 1

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

Answer 2

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

Answer 3

Prezados,  de que forma eu poderia enviar o anexo para vcs, pode ser por e-mail?

Grato,

Raimundo

Answer 4

http://www.ambienteoffice.com.br/outros/como_disponibilizar_um_arquivo_para_download/

---

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

Answer 5

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

Answer 6

Filipe, conseguiu avaliar algo sobre o desempenho da função?

Valeu,

Raimundo

Answer 7

Felipe, conseguiu avaliar algo sobre o desempenho da função?

Valeu,

Raimundo

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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

Answer 13

Fechou! Havia tentado com "Frequência", mas não usei o "Corresp".

Muito obrigado e bola pra frente.. :)