Excel Somar quantidade através dos dois primeiros dígitos de um código

Zamboni_du · 10 de junho de 2019

Boa tarde,

Preciso somar a quantidade de um determinado tipo de produto utilizando apenas os dois primeiros dígitos do código pois eles a qual tipo ele faz parte, segue exemplo anexo.

Pasta1.xlsx

Patropi · 10 de junho de 2019

Boa tarde @Zamboni_du

Confira na planilha e dê retorno.

Não se esqueça de clicar em curtir.

[]s

10_06_19_SOMAR com condição_Patropi.xlsx

Zamboni_du · 10 de junho de 2019

2 horas atrás, Patropi disse:

Boa tarde @Zamboni_du

Confira na planilha e dê retorno.

Não se esqueça de clicar em curtir.

[]s

10_06_19_SOMAR com condição_Patropi.xlsx 8 kB · 1 download

Deu certo, porém é possível incrementar e acrescentar um critério, por exemplo número da nota fiscal 100 quanto de cada tipo tem nessa NF?

10_06_19_SOMAR com condição_Patropi.xlsx

Patropi · 10 de junho de 2019

@Zamboni_du

Claro,é só acrescentar mais esse critério.

Confira na planilha e dê retorno.

[]s

10_06_19_SOMAR com condições_Patropi V.2.xlsx

Zamboni_du · 11 de junho de 2019

@Patropi Boa tarde, a pena uma dúvida, eu ajustei a forma conforme minha necessidade porém a formula está limitada as linhas 2 até a 19 e quando eu altero esses valores retorna erro, como eu faço por exemplo para calcular a coluna toda ao invés apenas da lina 2 a linha 19?

Patropi · 11 de junho de 2019

Boa tarde @Zamboni_du

A função SOMARPRODUTO não aceita a coluna inteira como intervalo.

Com a formula abaixo você poderá especificar um intervalo maior, mas tem que colocar a linha inicial e a final.

Copie e cole a formula abaixo na célula G3

=SOMARPRODUTO(--(ÉNÚM(LOCALIZAR("30";$B$2:$B$1000)));--($A$2:$A$1000=$F3);$C$2:$C$1000)

Dê retorno.

[]s

Zamboni_du · 11 de junho de 2019

@Patropi Muito obrigado pela ajuda, funcionou perfeitamente. Gostaria apenas de verificar se é possível você me explicar a formula para que em uma próxima necessidade eu possa aplicar essa solução.

Patropi · 11 de junho de 2019

@Zamboni_du

A função SOMARPRODUTO pode substituir as funções SOMASE; SOMASES; CONT.SE e CONT.SES.

Ela não trabalha com os dados digitados e sim com boleanos, ou seja, verifica se o resultado é VERDADEIRO ou FALSO.

Através da multiplicação de cada argumento, os argumentos VERDADEIROS são convertidos para o número 1 e os argumentos FALSOS para 0.

O -- é um modo de converter uma matriz booleana (VERDADEIRO ou FALSO) em 0 ou 1.

A função SOMARPRODUTO realiza a soma dos valores obtidos entre a multiplicação de "n" matrizes informadas, mas como não consegue multiplicar o elemento BOOLEANO, na forma literal dela, há a necessidade de converter VERDADEIRO para 1 e FALSO para 0, e é ai que entra o "--" que faz essa tarefa. O primeiro "-" converte o VERDADEIRO em -1 e FALSO em 0, e o segundo "-" trata de inverter o sinal do "-1" para "1". Alternativamente poderia utilizar a função N, ou então multiplicar por 1 (*1).

Acesse os links sobre as funções que usei junto:

http://www.cavalcanteassociados.com.br/article.php?id=304

https://fabiovianna.com.br/l-funcao-localizar/

Espero ter conseguido esclarecer mais um pouco.

Zamboni_du · 19 de junho de 2019

Em 11/06/2019 às 15:09, Patropi disse:
Boa tarde @Zamboni_du

A função SOMARPRODUTO não aceita a coluna inteira como intervalo.

Com a formula abaixo você poderá especificar um intervalo maior, mas tem que colocar a linha inicial e a final.

Copie e cole a formula abaixo na célula G3
=SOMARPRODUTO(--(ÉNÚM(LOCALIZAR("30";$B$2:$B$1000)));--($A$2:$A$1000=$F3);$C$2:$C$1000)
Dê retorno.

[]s

Boa tarde @Patropi

Utilizando a função acima eu encontrei um problema, quando o código do produto contem o número de outra família (3050, 3070) ele acaba sendo somado nas para as outras famílias também, como eu posso resolver isso?

Patropi · 19 de junho de 2019

Boa noite @Zamboni_du

Na forma abaixo isso não vai ocorrer:

=SOMARPRODUTO((VALOR(ESQUERDA($B$2:$B$19;2))=30)*($A$2:$A$19=$F3)*($C$2:$C$19))

[]s

Zamboni_du · 19 de junho de 2019

29 minutos atrás, Patropi disse:
Boa noite @Zamboni_du

Na forma abaixo isso não vai ocorrer:
=SOMARPRODUTO((VALOR(ESQUERDA($B$2:$B$19;2))=30)*($A$2:$A$19=$F3)*($C$2:$C$19))
[]s

Entendi, mas no caso você limitou até a linha 19, isso vai influenciar caso eu altera para linha 100mil ou sem limitação?

Patropi · 19 de junho de 2019

@Zamboni_du

Consegui montar com uma nova configuração que aceita um intervalo maior que o número de linhas utilizado.

=SOMARPRODUTO(--($C$2:$C$10000)*($A$2:$A$10000=$F3)*(ÉNÚM(LOCALIZAR(G$2&"*";$B$2:$B$10000;1))))

Dê retorno.

[]s

OBS.: EDITADO

Zamboni_du · 24 de junho de 2019

Em 19/06/2019 às 20:25, Patropi disse:
@Zamboni_du

Consegui montar com uma nova configuração que aceita um intervalo maior que o número de linhas utilizado.
=SOMARPRODUTO(--($C$2:$C$10000)*($A$2:$A$10000=$F3)*(ÉNÚM(LOCALIZAR(G$2&"*";$B$2:$B$10000;1))))
Dê retorno.

[]s

OBS.: EDITADO

Boa tarde @Patropi,

Coloquei a formula na planilha e me retou "0".

Patropi · 24 de junho de 2019

@Zamboni_du

Anexe a planilha com a formula aplicada.

[]s

Zamboni_du · 24 de junho de 2019

1 hora atrás, Patropi disse:

@Zamboni_du

Anexe a planilha com a formula aplicada.

[]s

Boa tarde,

Segue. Eu alterei a posição porém mesmo na planilha original não funcionou, na ultima parte da formula tem o comando localizar que está fixado em G2 porém G2 contem "Familia 30".

10_06_19_SOMAR com condições_Patropi V.2 (1).xlsx

Patropi · 25 de junho de 2019

Bom dia @Zamboni_du

Confira na planilha e dê retorno.

Não se esqueça de clicar em curtir.

[]s

25_06_19_SOMAR com condições_Patropi V.3.xlsx

Zamboni_du · 25 de junho de 2019

5 horas atrás, Patropi disse:

Bom dia @Zamboni_du

Confira na planilha e dê retorno.

Não se esqueça de clicar em curtir.

[]s

25_06_19_SOMAR com condições_Patropi V.3.xlsx 10 kB · 0 downloads

Boa Tarde @Patropi

Ainda está somando duplicado, a quantidade do código 7050 está somando para a família 50 e 70 pois, a nota 10230 contem 05 peças da família 30, 03 peças para família 50 e 01 peça para a família 70, porém a formula traz 04 peças para a família 50.