Este fórum está sendo desativado

[vtenorio]

Acabei fechando o tópico MÉDIA PONDERADA COM PESOS DINÂMICOS ( http://gurudoexcel.com/forum/viewtopic. ... 380#p48380 ) sem saber que ao marcá-lo como resolvido, não poderia mais postar nele e fiquei sem poder agradecer o usuário Jimmy corretamente.

A fórmula funcionou perfeitamente, e eu adoraria entender o que ela faz passo a passo. Em todo caso, me ajudou muito.

Um grande abraço!

[Jimmy]

Olá Tenório,

Você agradeceu sim! Clicou no sinal de positivo, e isso é um agradecimento.

Vamos ver a fórmula.

Inicio colocando aqui o link do tópico inicial, para o caso de alguém resolver acompanhar.
http://gurudoexcel.com/forum/viewtopic.php?f=9&t=10525

Primeiro vamos entender que a fórmula é matricial, ou seja, retorna uma matriz (ou vetor) de valores, e não apenas 1 valor como as funções tradicionais (não matriciais).

As partes que geram matrizes são (E2:N2) e (E3:N3). Por exemplo, E2:N2, gera uma matriz cujo primeiro elemento é E2, o segundo F2, o terceiro G2, ..., e o décimo e último é N2. Essa é a matriz base. Fica: E2, F2, G2, H2, I2, J2, K2, L2, M2, N2

Vou tentar explicar isto: CONT.VALORES(E2:N2)-COL(E2:N2)+COL($E2)

Ao aplicar -COL (note que é negativo) na matriz base E2:N2, a matriz passa a refletir não mais o valor da célula, mas sim o número da coluna. Assim, E2 passa a ser -5, pois E é a quinda coluna, F passa a ser -6, ..., N passa a ser -14. Fica: -5, -6, -7, -8, -9, -10, -11, -12, -13, -14

Depois COL($E2) , que aponta para a primeira coluna de horários (o imediato), e que representa 5 (E é a quinta coluna), é somado à essa matriz. Então a matriz passa a ter: zero na primeira posição (era -5, mas após somar 5 passou a ser zero), -1 na segunda (-6 + 5), ... e -9 na última (-14+5). Fica: 0, -1, -2, -3, -4, -5, -6, -7, -8, -9 . O objetivo disso é obter sempre a mesma matriz, mesmo que todos os dados estejam em outras colunas da planilha, como por exemplo, BT. Ou seja, mesmo que você insira diversas colunas novas antes da coluna de média, o que deslocaria a média e seus dados para colunas bem mais a direita, a função continua a funcionar e esta matriz sempre será 0, -1, -2, -3, -4, -5, -6, -7, -8, -9.

Depois CONT.VALORES(E2:N2) , que representa 6 (é a quantidade de horários preenchidos entre E2 e N2), é somado à essa matriz. Então a matriz passa a ter: 6 na primeira posição (6 - zero), 5 na segunda (6 -1), 4 na terceira (6 -2), ... e -3 na última (6-9). Fica: 6, 5, 4, 3, 2, 1, 0, -1, -2, -3. Está formada a matriz dos pesos para as ponderações.

A ordem de somas e subtrações que mostrei acima não é bem essa, mas como na soma e subtração a propriedade comutativa vale, a ordem não altera o resultado. Na ordem que expliquei é mais fácil entender.

Agora essa matriz é multiplicada pela matriz gerada por E3:N3. Não é uma multiplicação de matrizes como reza a matemática. É uma multiplicação mais simples: o primeiro elemento de uma pelo primeiro da segunda, o quinto pelo quinto, etc, ou seja, o n-ésimo de uma pelo n-ésimo da segunda.
Fica: 6, 5, 4, 3, 2, 1, 0, -1, -2, -3
vezes: 10, 9, 8, 6, 4, 2, 0, 0, 0, 0
igual: 60, 45, 32, 18, 8, 2, 0, 0, 0, 0

A função Somarproduto, soma tudo isso e já temos o nominador da fração que resultará na média ponderada: 165.

No denominador montamos novamente a matriz de pesos, e somamos esses pesos. Na matriz de pesos que montamos há pouco (6, 5, 4, 3, 2, 1, 0, -1, -2, -3) os valores negativos não nos incomodaram, pois ao serem multiplicados pelos zeros da segunda matriz, eles sumiram e não interferiram na soma.

Agora é diferente. Se somarmos os valores da matriz de pesos, os negativos serão indesejáveis. Então, para resolver o problema, multiplicamos eles pela matriz (E2:N2<>""), que terá 10 elementos, sendo eles apenas zeros e uns. Para as células diferentes de vazio, resultará 1, e para as iguais a vazio, retornará 0. Como os zeros corresponderão às colunas de horários não preenchidas, e os valores negativos também, os negativos serão multiplicados por zero, e os demais não, resultando a matriz 6, 5, 4, 3, 2, 1, 0, 0, 0, 0. Somando isso (novamente a função Somarproduto) resulta no valor de 21.

A função Somarproduto multiplica seus parâmetros e os soma. Na fórmula que estamos analisando, ele não multiplica nada porque tudo o que está entre seus parêntesis é apenas 1 parâmetro. Estamos fazendo as multiplicações pelo sinal de multiplicação (asterisco), e não pela Somarproduto, logo, estamos apenas usando a característica de soma dela.
Porque não usamos então a função SOMA? Porque para a SOMA somar as matrizes que montamos, ela deve ser encerrada não com ENTER, mas com SHIFT+CTRL+ENTER, para informar ao SOMA que ele deve somar a matriz. Já para o Somarproduto, somar matrizes é natural, possibilitando trabalhar com matrizes sem encerrar com SHIFT+CTRL+ENTER.

Apenas para ilustrar, podemos alterar um pouco a fórmula, e não mais multiplicar as matrizes por asterisco. Vamos deixar o SOMARPRODUTO fazer essa multiplicação. Basta colocar as duas matrizes finais, cada uma como um parâmetro da função. Em vez de usar o asterisco para multiplicar, apenas coloca-se um ponto e vírgula, que é quem separa os parâmetros das funções.

=SOMARPRODUTO(E3:N3 ; (CONT.VALORES(E2:N2)-COL(E2:N2)+COL($E2)))/SOMARPRODUTO((CONT.VALORES(E2:N2)-COL(E2:N2)+COL($E2)) ;-- (E2:N2<>""))

Se tiver alguma dúvida sobre o funcionamento, pode perguntar.

Jimmy San Juan

[vtenorio]

Uau! Não podia esperar explicação melhor.

Eu vou estudar com mais cuidado mais tarde, de primeira batida de olho ainda não entendi muito bem.
Devo treinar um pouco o passo a passo para entrar melhor na cabeça, rs.

Muitíssimo obrigado!

[omor]

Olá pessoas, como eu somo a quantidade de produtos vendidos em diversas planilhas?
Exemplo: quero somar nas planilhas jan:dez a quantidade de um determinado produto, só que esse produto pode estar em outra celula da planilha fevereiro. Tem como somar?

[Jimmy]

Olá Omor,

Você criou uma resposta em um tópico já existente.

Para criar um tópico novo:
- Clique na opção PRINCIPAL no menu superior do site.
- Abaixo, aparecerão alguns blocos: TÓPICOS COM ATIVIDADES MAIS RECENTES / SUPORTE / FÓRUM DE EXCEL / GERAL, entre outros.
- No bloco FÓRUM DE EXCE escolha o título mais de acordo com o seu problema, e clique nele.
- Clique no botão "+ Novo Tópico"
- Coloque um título que resuma o seu problema, e o problema em si.
- Procure SEMPRE anexar uma planilha que exemplifique o que deseja.

Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo", acima e a direita.


Jimmy San Juan

[vtenorio]

Oi Jimmy,

Agora que eu fui aplicar a fórmula no meu arquivo, surgiu um probleminha.

Como eu havia explicado, minha intenção é evitar que o usuário final da tabela precise ficar inserindo ou excluindo colunas, sobrando a ele apenas preencher notas nos momentos que foram testados. Desta forma, pode ser que há situações em que alguns dos momentos não sejam preenchido, pois não foram testados. E eles podem não ser sequenciais.

Vejamos no arquivo:
http://s000.tinyupload.com/?file_id=017 ... 6199591370

No Cenário 1 está a fórmula como me passou. A média calculada bate com a referência.


No Cenário 2 incluí novas colunas, na quantidade que precisamos. A média calculada também bate com a referência.


No Cenário 3 tentei prever como o arquivo será usado por fim, com o usuário deixando de preencher o momento no qual não fez teste. Aí que os valores não bateram, pois a fórmula não foi capaz de remapear os multiplicadores.

Neste caso, os multiplicadores deveriam passar a ser 6 - 5 - __ - 4 - __ - 3 - 2 - 1, conforme os números em roxo, mas a fórmula ainda lê 8 - 7 - __ - 5 - __ - 3 - 2 - 1.

Tentei limpar as células que contribuem para a CONT.VALORES, mas mesmo assim, não deu certo.


A pergunta: Há uma forma de fazer a fórmula: 1) calcular apenas as médias preenchidas e 2) Não decrescer o multiplicador quando houver uma célula vazia?

Atente também para o fato que as células com as Notas (médias) não estão vazias. Há uma fórmula de média com SEERRO (para trocar #DIV/0 por vazio quando as notas ainda não foram preenchidas), que aparentemente é lida como uma célula preenchida tanto por CONT.VALORES quanto por CONT.NÚM.

Obrigado novamente!

[Jimmy]

Olá Tenório,

Essa é a importância de sempre anexar uma planilha modelo, se não a própria. Sempre há particularidades que acabam por interferir na solução.

No seu caso, o problema é relativamente simples de solucionar, e há três formas:
- Sem o uso de uma linha auxiliar, mas complicando um pouco a fórmula;
- Com o uso de uma linha auxiliar, mas simplificando a fórmula;
- Sem o uso de uma linha auxiliar, com fórmula muito simples, com desenvolvimento VBA.

Se for útil pra você ter uma linha com os pesos (linha auxiliar citada na segunda solução), não há porque desenvolver a primeira solução.

Se preferir sem linha auxiliar, uma vez que com VBA é bem mais simples, não há porque desenvolver a primeira solução (novamente).

Abandonando a primeira solução, anexo a planilha para a segunda. A linha auxiliar com os pesos, permite células em branco no meio da faixa, além de simplificar a fórmula final.

Ainda hoje desenvolverei a terceira solução usando função desenvolvida em VBA. Já estou trabalhando nela.

Jimmy

[Jimmy]

Bom dia Tenório,

Não consegui postar a segunda planilha ontem. Segue agora.

A função é simples de usar. Basta marcar a faixa onde se encontram as notas, e nada mais. Por exemplo, =Ponderada(E7:N7).

A função varre o intervalo desconsiderando as células em branco ou não numéricas, e já atribuindo pesos, sendo o maior peso o primeiro valor da esquerda, e peso 1 no último valor da direita.

Caso o intervalo esteja na vertical (=Ponderada(E7:E50) por exemplo), o maior peso será o da célula de cima, e o menor o da de baixo.

Não há limite de tamanho para a faixa, mas deve ser de apenas 1 linha ou de apenas 1 coluna.

A função tem um segundo parâmetro, que especifica o tipo de resultado que se quer. Se for omitido, ou informado "M" (ex. =Ponderada(E7:E50,"M") ), como nos exemplos acima, ele retorna a média ponderada. Se for especificado "N" a função retorna o numerador da fração que gera a media ponderada, ou seja, a soma das notas multiplicadas pelos seus pesos. Se for informado "D" será retornado o denominador, ou seja, a soma dos pesos.

É possível implementar outras características, mas depende de você informar o que mais seria útil. Por exemplo, se você algumas vezes tiver necessidade do peso maior estar a direita, ou abaixo, em vez de a esquerda ou acima, é possível criar um parâmetro que controle isso.

Se algum parâmetro for informado errado, ou se não forem encontrados valores numéricos no intervalo, por exemplo, o retorno será uma mensagem de erro. Pode ser que você tenha que alargar temporariamente a coluna para poder ler toda a mensagem.

Se esta mensagem colabora para a solução do problema, peço que dê um Like, clicando no botão com o "positivo", acima e a direita.

Jimmy San Juan

[vtenorio]

Olá Jimmy,

Fiquei quase o dia todo trabalhando na planilha, por isso não respondi antes.
Acabei optando pela segunda opção, pois a terceira trouxe um nível de detalhamento e complexidade que já está muito além do que precisava.

De fato, a segunda solução, com apenas uma linha auxiliar, se provou muito simples (claro que depois que você mostrou o caminho, rs) e prática, então fui com ela. Está funcionando perfeitamente e não espero ter qualquer outro cenário inesperado.

Aprendi demais nesse processo e gostaria de agradecer por sua paciência e boa vontade.

Um grande abraço!

[Jimmy]

Olá Tenório,

Guarde a terceira opção num canto qualquer, porque um dia pode ter conhecimento e prática suficientes para aproveita-la.

Não precisa agradecer, ou melhor, aqui no fórum os agradecimentos se dão clicando no botão do polegar das mensagens.

Nas mensagens acima que você julgar que ajudaram de alguma forma com a solução do problema, peço que dê um Like, clicando no botão com o "positivo", acima e a direita. Obrigado.

Daqui há alguns dias, quando estiver confirmada a solução do problema, marque o tópico como RESOLVIDO.

Jimmy San Juan