Fóruns
Microsoft Excel - 2...
2024 - VBA & Macros
FUNÇÃO AVERAGEIFS V...
 
Notifications
Clear all

FUNÇÃO AVERAGEIFS VBA

 
2024 - VBA & Macros
Post Recente por JAlmeida 6 anos atrás
11 Posts
2 Usuários
0 Reactions
2,065 Visualizações
RSS
 JAlmeida
(@jalmeida)
Posts: 43
Trusted Member
Topic starter
 

Pessoal,
Gostaria de pedir uma ajuda na função AverageIfs (MédiaSes Vba).
Desenvolvi uma rotina para calcular a media de valores maiores que 0 (Zero) em um intervalo de datas, entretanto, está dando um erro que não consigo resolver.
Estou anexando o relatório para auxílio, orientação e sugestões, se possível.
Abaixo, código desenvolvido.
Muito obrigado!
Josenildo

Sub Relat_Medicamentos()
    Dim uLin As Long
    Dim Lin As Integer
    Dim w As Worksheet
    
    Set w = Sheets("Relat_Media")
    w.Select
    
    uLin = Cells(Rows.Count, 2).End(xlUp).Row
    
    For Lin = 2 To uLin
        Cells(Lin, 3).Value = Application.WorksheetFunction.AverageIfs(Range("d_TotalSaidas"), _
        Range("d_TotalSaidas"), "<>" & 0, _
        Range("d_Medicamentos"), Cells(Lin, 1), _
        Range("d_DataSaidas"), ">=" & Format(Cells(Lin, 2)), "mm/dd/yyyy", _
        Range("d_DataSaidas"), "<=" & EoMonth(Cells(Lin, 2)), 0)
        Next Lin
    
End Sub
 
Postado : 30/05/2018 5:49 am
Wagner Morel
 Wagner Morel
(@wagner-morel-vidal-nobre)
Posts: 0
Illustrious Member
 

JosenildoA,

Bom dia!

Qual é o tipo de erro que está ocorrendo? Se for o apresentado na tela abaixo é porque está faltando a procedure EoMonth.

Desenvolvo pequenas soluções em VBA Excel a valores que variam entre R$ 50,00 a R$ 200,00. Se te interessar, entre no meu instagran (vba_excel_desenvolvimento)

Atenciosamente
Wagner Morel

 
Postado : 31/05/2018 8:30 am
 JAlmeida
(@jalmeida)
Posts: 43
Trusted Member
Topic starter
 

Wagner,
Grato pelo retorno.
Sim,é esse mesmo! Sou novo em VBA e não estou conseguindo resolver, pode me ajudar?
Muito grato!

 
Postado : 31/05/2018 10:55 am
Wagner Morel
 Wagner Morel
(@wagner-morel-vidal-nobre)
Posts: 0
Illustrious Member
 

JosenildoA,

Posso ajudar sim!

Todavia, não sei como é seu arquivo... fica difícil à distância dizer alguma coisa como o que está ocorrendo. Veja: Ao que parece, está faltando uma rotina no seu código chamada "EoMonth".

Onde foi que você achou esse código? Você precisa localizar onde foi que você pegou e verificar se existe lá uma outra rotina que tenha esse nome ou uma função que deve ser o mais comum.

É assim: podemos fazer uma rotina que chama outra rotina ou que chama uma função que executa alguma coisa e nos devolve um resultado para podermos prosseguir com a execução da rotina em que estamos. Entendeu???

Desenvolvo pequenas soluções em VBA Excel a valores que variam entre R$ 50,00 a R$ 200,00. Se te interessar, entre no meu instagran (vba_excel_desenvolvimento)

Atenciosamente
Wagner Morel

 
Postado : 31/05/2018 12:34 pm
Wagner Morel
 Wagner Morel
(@wagner-morel-vidal-nobre)
Posts: 0
Illustrious Member
 

JosenildoA,

Estava estudando um pouco mais aqui e vi que essa função EoMonth é também uma função do próprio Excel usada pelo objeto WorkSheetFunction.

Todavia, eu não costumo utilizar essas funções nativas do Excel dentro do VBA (inclusive essa AVERAGEIFS também que você está usando).

Podemos fazer algo totalmente diferente, bem mais simples que chegue ao mesmo objetivo desejado. Entretanto, para isso eu preciso entender o que você realmente quer. Você disse que quer "calcular a media de valores maiores que 0 (Zero) em um intervalo de datas". Mas... que datas, em que colunas estão essas datas? Qual a aba onde a média deve ser calculada? Qual coluna? Qual o critério para utilizar uma faixa de datas?

Desenvolvo pequenas soluções em VBA Excel a valores que variam entre R$ 50,00 a R$ 200,00. Se te interessar, entre no meu instagran (vba_excel_desenvolvimento)

Atenciosamente
Wagner Morel

 
Postado : 31/05/2018 2:56 pm
 JAlmeida
(@jalmeida)
Posts: 43
Trusted Member
Topic starter
 

Wagner,
Agradeço seu esforço e empenho em me ajudar ...
É o seguinte: O arquivo que enviei, é um relatório de Saídas de Medicamentos Por Periodo.
Na aba "Saída_Med" constam os medicamentos, datas e saídas e na aba "Relat_Media" constam o resumo desses medicamentos e período (Mês).
O que eu preciso é gerar a média de saída por medicamentos e por mês, de valores maiores do que 0 (zero). Contudo para calcular o mês, considerando que na aba "Saída_Med" as saías são diárias, tive que trabalhar com a data inicial e para o último dia, o Fimmês, assim consigo acha a saída por mês.

Como sou novo em VBA e sabendo que todas as funções do Excel tem também a equivalência em VBA, resolvi fazer as rotinas lá para otimizar o tempo e não pesar muito o arquivo, o que acaba ocorrendo quando uso as funções do excel (MédiaSes).

Resmindo:
Aba "Saída_Med" -- Arquivo base de dados
Aba "Relat_Media"
Coluna A constam os medicamentos relativos às médias

 
Postado : 31/05/2018 3:38 pm
 JAlmeida
(@jalmeida)
Posts: 43
Trusted Member
Topic starter
 

Continuando ...
Aba "Saída_Med" -- Arquivo base de dados
Aba "Relat_Media"
1. Coluna "A" constam os medicamentos relativos às médias
2. Coluna "B" constam os meses que devem ser informados as médias
3. Coluna "C" devem constar as médias desses meses
A função deve o localizar o medicamento, achar a media no intervalo de datas cujos valores são maiores do que 0 (Zero).
É exatamente igual a função MédiaSes, que está na aba "Relat_Media", coluna "E".
Grato pela colaboração.

Josenido

 
Postado : 31/05/2018 3:50 pm
Wagner Morel
 Wagner Morel
(@wagner-morel-vidal-nobre)
Posts: 0
Illustrious Member
 

JosenildoA,

Bom... eu acho que você não precisa de VBA para isso. Veja: com uma simples planilha dinâmica, fica bem fácil apurar a média mensal de cada medicamento. Tudo que fiz foi acrescentar uma coluna para que o Excel me dê o mês da saída e montar uma dinâmica que me dá a média das saídas mensais.

A grande vantagem da dinâmica é que trabalha com grandes volumes de dados e que é possível mexer à vontade e fazer relatórios diversos, além de permitir que você acrescente novos dados (caso que os meses forem se passando e a planilha de saída crescendo). Para isso, basta fazer atualização dos dados que a dinâmica mostrará sempre os dados atualizados.

Veja se assim lhe atende.

Relat_Medicam.zip

Desenvolvo pequenas soluções em VBA Excel a valores que variam entre R$ 50,00 a R$ 200,00. Se te interessar, entre no meu instagran (vba_excel_desenvolvimento)

Atenciosamente
Wagner Morel

 
Postado : 31/05/2018 4:17 pm
 JAlmeida
(@jalmeida)
Posts: 43
Trusted Member
Topic starter
 

Ótima sugestão e idéia, Wagner!
O problema é que o arquivo original contempla o período de 2014 à 2018, com mais de 15 mil linhas, tendo que apurar a média mensal por ano de cada medicamento.
Ao montar o relatório final com base no oficial, no recalculo ainda há delay.
Ainda tem mais um atenuante, esse relatório tenho que enviar à Secretaria do Estado e não é permitido outro formato a não ser uma planilha em excel no formato que enviei.
Por essas razões, pensei em algo como a própria fórmula do excel ou então uma rotina em VBA. Como a fórmula deixa o arquivo pesado, então optei pelo VBA.
Te agradeço muito pelo empenho e ajuda, mas infelizmente a dinâmica, nesta caso, não me atende.

Josenildo

 
Postado : 31/05/2018 5:47 pm
Wagner Morel
 Wagner Morel
(@wagner-morel-vidal-nobre)
Posts: 0
Illustrious Member
 

JosenildoA,

Boa tarde!

Problemas difíceis e complexos são meus incentivos de estudos. Dessa forma, não gosto muito de me dá por vencido diante de um desafio qualquer. Na verdade, o problema colocado por você exigia um pouco mais de gasto dos miolos e tempo tranquilo para isso (o que não estava tendo muito naquela ocasião).

Desse modo, segue uma nova versão somente com o cálculo das médias com VBA. Independente da quantidade de linhas que existam, o código calculará todas. A única alteração que fiz na sua aba Relat_Saida foi formatar a coluna B para que ela mostre o mês e o ano a que se refere a respectiva média, uma vez que você disse que tem muitos meses e muitos anos.

Para preservar a sua aba original Relat_Saida fiz uma cópia da mesma e deixei intacta lá, até para fins de conferência.

Na coluna G eu usei uma fórmula SE apenas para fazer uma comparação entre a média calculada via VBA (coluna C) com a média calculada pela fórmula MÉDIASES utilizada por você na coluna E. Você pode apagar. Foi apenas para eu saber se tá tudo certo.

Veja que a partir da linha 96 o VBA não trouxe nenhum outro produto. Isso é em razão dos produtos cuja média é zero. Esses não são copiados para a aba Relat_Saida. Se você fizer um filtro na aba Saida_Med para verificar os produtos cuja média é ZERO, vai ver que tem 357 linha com médias iguais a zero.

Veja se assim fica melhor.

Relat_Medicam.zip

Desenvolvo pequenas soluções em VBA Excel a valores que variam entre R$ 50,00 a R$ 200,00. Se te interessar, entre no meu instagran (vba_excel_desenvolvimento)

Atenciosamente
Wagner Morel

 
Postado : 02/06/2018 11:28 am
 JAlmeida
(@jalmeida)
Posts: 43
Trusted Member
Topic starter
 

Wagner,
Muito nobre da sua parte quando de seu empenho na busca de soluções aos desafios apresentados.
Dei uma olhada no algorítimo que descreveu e agora atende ao que preciso, embora existam alguns detalhes que não entendi muito bem, todavia, sou muito grato por isso.
De qualquer forma mais uma vez meu muito obrigado pelo esforço e parceria. Pessoas assim merecem destaque uma vez que representam um diferencial! Parabéns!
Grande abraço!
Josenildo

 
Postado : 04/06/2018 4:06 pm
Forum Jump:
  Tópico Anterior
Próximo Tópico