Ir ao conteúdo
  • Cadastre-se

Somar produto condicional


Ir à solução Resolvido por Visitante,

Posts recomendados

Boa noite pessoal,

 

 

Eu tenho uma planilha de vendas e gostaria de calcular o preço médio ponderado com o seguinte critério: 

 

Quando ocorrer a venda de um determinado produto em um estado por no mínimo 3 empresas.

 

   Col A        Col B         Col C          Col D            Col E                    Col, F

  Estado    Empresa    Quant          Preço        Quant*Preço          Produto

     PR           A               3                2,00             6,00                      BOLA

     SC           A               4                1,00             4,00                      BOLA

     SC           B               5                3,00           15,00                      BOLA

     SC           B               6                3,00           18,00                      BOLA

     SC           C               2                1,00             2,00                      BOLA

    AM           C               2                2,00              4,00                      BOLA

 

Então nessa tabela eu gostaria que só me retornasse o preço médio ponderado do produto BOLA vendido em SC, o que daria R$ 2,29, enquanto que nos estados do PR e AM deveria retornar "-".

 

Estou usando essa fórmula baixo, mas não consigo por uma condição que permita o que desejo. Se alguém puder me dar uma luz fico grato.

 

=SE(ÉERROS(SOMARPRODUTO(N(BASE!$A$2:$A$7=$A2);N(BASE!$F$2:$F$7=$B$1);(BASE!$C$2:$C$7)*BASE!$D$2:$D$7)/SOMARPRODUTO(N(BASE!$A$2:$A$7=$A2);N(BASE!$F$2:$F$7=$B$1);(BASE!$C$2:$C$7)));  

"-";SOMARPRODUTO(N(BASE!$A$2:$A$7=$A2);N(BASE!$F$2:$F$7=$B$1);(BASE!$C$2:$C$7)*BASE!$D$2:$D$7)/SOMARPRODUTO(N(BASE!$A$2:$A$7=$A2);N(BASE!$F$2:$F$7=$B$1);(BASE!$C$2:$C$7)))

 

 

Somar Produto Condicional.xlsx

Link para o comentário
Compartilhar em outros sites

Experimente

=SE(SOMARPRODUTO((BASE!$A$2:$A$7=A2)*(BASE!$F$2:$F$7=$B$1)*(1/CONT.SES(BASE!$A$2:$A$7;BASE!$A$2:$A$7;BASE!$B$2:$B$7;BASE!$B$2:$B$7&"")))>=3;SOMARPRODUTO((BASE!$A$2:$A$7=A2)*(BASE!$F$2:$F$7=$B$1)*(BASE!$E$2:$E$7))/SOMARPRODUTO((BASE!$A$2:$A$7=A2)*(BASE!$F$2:$F$7=$B$1)*(BASE!$C$2:$C$7));"-")
Link para o comentário
Compartilhar em outros sites

Coloquei agora a fórmula do post #2 em 'B2', no arquivo do post #1, e arrastei até 'B4'. Está retornando os seguintes resultados:

 

 B2 >>           -
 
B3 >>           -
 
B4 >>   2,294117647

 

Verifique pois algo está diferente aí.

 

 

Link para o comentário
Compartilhar em outros sites

beleza Osvaldo,
 
 
Novamente desculpa pela demora na resposta.
 
Realmente eu tinha digitado errado, sua fórmula está correta.
 
Depois eu incluí uma nova condição (em vermelho) na fórmula, pois tem situação que uma empresa vende mais de um produto no mesmo estado, então a fórmula ficou assim:
 
=SE(SOMARPRODUTO((BASE!$A$2:$A$7=A2)*(BASE!$F$2:$F$7=$B$1)*(1/CONT.SES(BASE!$A$2:$A$7;BASE!$A$2:$A$7;BASE!$B$2:$B$7;BASE!$B$2:$B$7;BASE!$F$2:$F$7;BASE!$F$2:$F$7&"")))>=3;SOMARPRODUTO((BASE!$A$2:$A$7=A2)*(BASE!$F$2:$F$7=$B$1)*(BASE!$E$2:$E$7))/SOMARPRODUTO((BASE!$A$2:$A$7=A2)*(BASE!$F$2:$F$7=$B$1)*(BASE!$C$2:$C$7));"-")

 

Porém, como o arquivo que eu trabalho tem muitas linhas (em torno de 8.000), tem um estado que a fórmula (com a condição em vermelho) não retorna o preço. Estranho que para os demais estados que atendem as condições o preço está sendo calculado corretamente.

 

Obrigado pela ajuda.

Link para o comentário
Compartilhar em outros sites

 

Realmente eu tinha digitado errado, sua fórmula está correta.

Você quis dizer que está digitando a fórmula? Se sim, não seria mais fácil copiar/colar, o que também evitaria erros de digitação?

 

Depois eu incluí uma nova condição (em vermelho) na fórmula, pois tem situação que uma empresa vende mais de um produto no mesmo estado, então a fórmula ficou assim:

Não entendi a necessidade de incluir nova condição pois a fórmula considera Estado e Produto, se você quer que o resultado da fórmula se refira a outro produto basta colocar o nome do outro Produto na célula correspondente.

 

Porém, como o arquivo que eu trabalho tem muitas linhas (em torno de 8.000), tem um estado que a fórmula (com a condição em vermelho) não retorna o preço. Estranho que para os demais estados que atendem as condições o preço está sendo calculado corretamente.

Veja se na célula que serve de base para a busca a grafia do nome do Estado está exatamente igual à grafia do nome do Estado nas células pesquisadas pela fórmula. A grafia inclui espaços extras antes e ou após o texto com o nome do Estado.

 

 

Se ainda restarem dúvidas sugiro que você disponibilize o seu arquivo atualizado com exemplos, com os resultados esperados e as necessárias explicações na própria planilha.

Link para o comentário
Compartilhar em outros sites

Boa noite Osvaldo,

 

Depois eu incluí uma nova condição (em vermelho) na fórmula, pois tem situação que uma empresa vende mais de um produto no mesmo estado, então a fórmula ficou assim:

Não entendi a necessidade de incluir nova condição pois a fórmula considera Estado e Produto, se você quer que o resultado da fórmula se refira a outro produto basta colocar o nome do outro Produto na célula correspondente.

Então, pus aquela condição porque tem vez que uma mesma empresa vende mais de um produto no mesmo estado e a fórmula anterior não estava retornando o preço nesse caso. Quando coloquei essa condição o preço foi calculado.

 

Porém, como o arquivo que eu trabalho tem muitas linhas (em torno de 8.000), tem um estado que a fórmula (com a condição em vermelho) não retorna o preço. Estranho que para os demais estados que atendem as condições o preço está sendo calculado corretamente.

Veja se na célula que serve de base para a busca a grafia do nome do Estado está exatamente igual à grafia do nome do Estado nas células pesquisadas pela fórmula. A grafia inclui espaços extras antes e ou após o texto com o nome do Estado.

Então, fiz essa verificação e a fórmula parece não reconhecer para o estado do AM quando o produto é “chumbo”, nesse caso o preço deveria ser 0,90292

 

Segue o arquivo anexo.

 

Grato pela atenção

Somar Prod. Condicional.xlsx.zip

Link para o comentário
Compartilhar em outros sites

  • Solução

Olá, Carioca.

 

Na fórmula que sugeri no post #2 altere esta parte >=3 para >=2,3

 

obs.

1. na célula C8450 você colocou a fórmula =SUBTOTAL(9;C2:C8449), esta fórmula retorna resultado igual a =SOMA(C2:C8449); se a sua intenção ao utilizar a função SUBTOTAL é somar somente as células filtradas então altere para =SUBTOTAL(109;C2:C8449); idem para a fórmula em E8450

2. o recálculo do seu arquivo está super lento, provavelmente em decorrência da quantidade de fórmulas; se realmente elas são necessárias talvez seja conveniente manter seu arquivo no recálculo manual e apertar F9 quando quiser atualizar todos os resultados de fórmulas

Link para o comentário
Compartilhar em outros sites

Bom dia Osvaldo,

 

Valeu pela dica da função subtotal.

 

Fiz a alteração de ">=3" para ">=2,3" na fórmula do post#2 e o que ocorreu foi que a fórmula não calculou os preços de alguns estados.

 

Então, inseri novamente esse critério "BASE!$F$2:$F$7;BASE!$F$2:$F$7" que junto com ">=2,3", a fórmula passou a calcular todos os preços corretamente.

 

Sobre o ">=2,3", seria porque a função (1/cont.ses(...)) conta em forma de fração cada vez que uma empresa é repetida?

 

Mais uma vez obrigado pela atenção. 

Link para o comentário
Compartilhar em outros sites

Olá, Carioca.

 


Fiz a alteração de ">=3" para ">=2,3" na fórmula do post#2 e o que ocorreu foi que a fórmula não calculou os preços de alguns estados.

Então, inseri novamente esse critério "BASE!$F$2:$F$7;BASE!$F$2:$F$7" que junto com ">=2,3", a fórmula passou a calcular todos os preços corretamente.

Testei aqui com o produto CHUMBO para todos os estados e os resultados foram corretos. Não testei com os demais produtos. Mas se resolveu introduzindo mais um critério, beleza! :aplausos: 

 

Sobre o ">=2,3", seria porque a função (1/cont.ses(...)) conta em forma de fração cada vez que uma empresa é repetida?

Exatamente. No caso do estado AM, por exemplo, satisfaz o critério de ao menos 3 empresas, no entanto essa parte da fórmula que filtra somente uma vez as empresas repetidas, retorna 2,99999... , então ">=3" não "pega" esse resultado. ;) Se no futuro apresentar ainda alguma inconsistência em casos específicos, experimente ">=2,25" 

 

 

Se o tópico foi solucionado, por favor marque como 'Resolvido'. :-BEER

Link para o comentário
Compartilhar em outros sites

  • 5 anos depois...
  • 4 semanas depois...

Boa noite, 

 

Preciso de uma ajuda.

Segue a planilha em anexo.

 

O que preciso é : na planilha tem a aba com nome de DRE e a aba com nome de Cálculo; preciso calcular com a função somarproduto do excel efetuando a divisão do Resultado Bruto pela Receita de Venda de Bens e/ou Serviços  ( que está destacado em vermelho ).

Esta divisão do somarproduto é para todos os códigos que estão na aba Cálculo e com os respectivos anos ( 2010 a 2020 ).

 

Obs :  já fiz a primeira parte da função somarproduto na aba Cálculo , e não consigo colocar a segunda parte, que é a divisão.

 

 

Obrigado .

somarproduto.xlsx

Link para o comentário
Compartilhar em outros sites

Crie uma conta ou entre para comentar

Você precisa ser um usuário para fazer um comentário

Criar uma conta

Crie uma nova conta em nossa comunidade. É fácil!

Crie uma nova conta

Entrar

Já tem uma conta? Faça o login.

Entrar agora

Sobre o Clube do Hardware

No ar desde 1996, o Clube do Hardware é uma das maiores, mais antigas e mais respeitadas comunidades sobre tecnologia do Brasil. Leia mais

Direitos autorais

Não permitimos a cópia ou reprodução do conteúdo do nosso site, fórum, newsletters e redes sociais, mesmo citando-se a fonte. Leia mais

×
×
  • Criar novo...