Este fórum está sendo desativado

[rogerpenna]

A princípio me pareceu ser simples, mas a fórmula não está dando certo, então há alguma coisa errada.


Tenho uma Tabela Monitoramento. Nesta tabela, em cada linha se classifica uma entrega de um fornecedor.

Essa tabela tem um campo que é o nome do fornecedor [fornecedor], segundo campo é data (da entrega) [data], e há um terceiro campo importante aqui, que é uma nota negativa (como ela é calculada não vem ao caso para o problema aqui) [soma].

Cada entrega que o fornecedor faz se preenche esses campos, então pode ter várias linhas com o mesmo fornecedor, várias linhas com datas iguais, até mesmo linhas com o mesmo fornecedor e a mesma data (se o fornecedor fez duas entregas na mesma data)


O que eu quero é ter uma segunda tabela, onde cada fornecedor apareça uma única vez numa coluna, e então cada coluna a seguir tenha um mês diferente. E quero SOMAR as notas negativas de um mesmo fornecedor para cada mês.

Exemplo:

tabela Monitoramento
Areia do Juca : 20/05/2018 : -1,5
Metai Jorge : 21/05/2018 : -2
Areia do Juca : 22/05/2018 : -1
Areia do Juca : 05/06/2018 : -3

tabela resumo

Fornecedor : Abril : Maio : Junho:
Areia do Juca: 0 : -2,5 : -3
Metal Jorge : 0 : -2 : 0

Como tentei usar a fórmula

=SOMASES(TabMonitoramento[SOMA];TabMonitoramento[Fornecedor];"=Fornecedores!$E2";TabMonitoramento[Data];"=Fornecedores!F$1")

então, estou somando os valores em TabMonitoramento, campo SOMA (que contém as várias notas para vários fornecedores em várias datas)

QUANDO a TabMonitoramento campo Fornecedor tiver o mesmo nome de fornecedor da célula E2 (aba Fornecedores). Aqui já tem um problema... quando arrasto a formula pra copiar pra outras células, estranhamente o $E2 não muda. Talvez por estar entre aspas. Mas se não estiver entre aspas acho que não vai funcionar, correto?

e

QUANDO a TabMonitoramento campo DATA tiver o mesmo mês e mesmo ano da primeira linha da tabela que estou criando (jan/2019, fev/2019, mar/2019, etc...)

Bom, aqui temos um segundo problema, pois preciso comparar mês e ano, (que é como quero fazer a soma das notas), mas o dia não me importa o dia.

E pelos testes que fiz, colocar mês(tabmonitoramento[data];"=mês(Fornecedores!F$1) tb não funcionou.



Então, espero que alguém consiga me dar uma luz aí...

[adefe]

Olá Roger me mande a tabela online e me explique melhor para eu poder ver se consigo te ajudar!

[FelipeMGarcia]

Tente assim, mas é mais fácil anexar a planilha com o resultado desejado para facilitar na ajuda.

=SOMASES(TabMonitoramento[SOMA];TabMonitoramento[Fornecedor];;Fornecedores!$E2";TabMonitoramento[Data];Fornecedores!F$1")

[rogerpenna]

em anexo planilha.

na aba Fornecedores, tem a tabela onde devem estar as fórmulas e abaixo a mesma tabela, escrito "Como Devia Aparecer), mostrando quais os valores que deviam aparecer na tabela, buscando automático claro, ao invés de manualmente selecionar os valores (se tiver 300 entregas de 20 fornecedores num mês, fica dificil selecionar na soma)

[FelipeMGarcia]

Amigo,

Veja se é isto o que precisa, caso queira agradecer, clique na mãozinha.

Abs

[rogerpenna]

Felipe Garcia, muito obrigado mesmo... mas eu gostaria de uma explicação... sabe como é... ensine o homem a pescar ao invés de somente dar o peixe

Porque usou SOMAPRODUTOS ao invés de SOMASES?

Na minha lógica rasa e aparentemente errônea, eu queria SOMAR os valores da coluna [soma]... SE a coluna fornecedor e a coluna data correspondessem ao da tabela nova.

tipo... em nenhum momento temos algum "produto" ali

[rogerpenna]

Felipe, por acidente (movendo a tabela com tuas fórmulas de lugar), notei que ela continuou funcionando mesmo com todos os =fornecedores!XX apontando pra células erradas... tanto faz se a célula que eles apontam estivesse vazia, com números ou algo escrito... a fórmula continuava funcionando...

------------------------

ok, é mais estranho do que eu pensava... somente copiando a fórmula, as referências ficaram em lugars com células em branco, com células com números nada a ver... mas tudo continuou funcionando.


daí quando CONSERTEI as referências, apontando pras células corretas, parou de funcionar!


Estou enviando novamente o arquivo... é que na verdade preciso daquelas fórmulas dentro da tabela, assim sempre que a pessoa acrescentar um novo fornecedor, já aparecem os valores dele.

Desculpe pedir novamente, mas é por isso que quero entender a fórmula, pra não depender da ajuda e do tempo de vocês. (obs: note que coloquei um +10 antes da fórmula, justamente pra transformar a nota negativa em uma avaliação de 0 a 10... no caso, todo mês o fornecedor começa com 10 pontos e problemas criam notas negativas que subtraem do 10)

[rogerpenna]

Olá, eu realmente estou tentando entender o método usado, pois preciso modificar algumas coisas, mas alterações que parecem óbvias não estão funcionando.

Estou enviando em anexo a planilha onde tem a versão final da Planilha, onde a fórmula deve estar dentro de uma tabela, pois a tabela precisa ser expandida facilmente pelo usuário, adicionando novos fornecedores e seu tipo.

[rogerpenna]

SOMARPRODUTO(TabMonitoramento[SOMA]*(TabMonitoramento[Fornecedor]=Fornecedores!$E2)*(TabMonitoramento[Data]>=Fornecedores!F$1)*(TabMonitoramento[Data]<=FIMMÊS(Fornecedores!F$1;0)))

ok, somaproduto faz a soma do produto de várias matrizes.

só temos UMA matriz nesta fórmula entretanto, já que não há ponto e virgula separando para a segundo matriz...

aqui já começa minha dúvida. Pq usar uma fórmula pra somar o produto de duas ou mais matrizes e então utilizar somente uma matriz???

Ok, vamos analisar a matriz então. Aqui tb não entendi... estamos multiplicando os valores da coluna SOMA com os valores da coline FORNECEDOR onde o Fornecedor seja igual ao Fornecedor X, e multiplicando TUDO isso pelos valores da coluna DATA onde os valores sejam maiores ou iguais a data X e multiplicando tudo isso pelos valores da coluna data que sejam menos ou iguais a data de fim de mês da data X.

Como exatamente funciona isso?

O Excel não está realmente MULTIPLICANDO esses valores, está?

[FelipeMGarcia]

Roger, a função somarproduto quando se usa o asterisco é como se fosse a função E do Excel, a cada intervalo de parênteses, ele verifica se dentro do intervalo atende ao critério, tudo que atende, ele considera como 1 e o que não atende como 0, assim ele multiplica o valor vezes tudo que for igual a 1.

Espero que ajude.

Abs

[rogerpenna]

obrigado.

talvez o artigo oficial da MS para o SOMARPRODUTOS seja o artigo mais incompleto que eu já tenha visto dentre os oficiais de funções do Excel.

estou lendo um artigo em inglês (não oficial) que tem bastante informação sobre a função, exemplos, como funciona
https://exceljet.net/excel-functions/ex ... t-function

[rogerpenna]

Roger, a função somarproduto quando se usa o asterisco é como se fosse a função E do Excel, a cada intervalo de parênteses, ele verifica se dentro do intervalo atende ao critério, tudo que atende, ele considera como 1 e o que não atende como 0, assim ele multiplica o valor vezes tudo que for igual a 1.

Espero que ajude.

Abs

Felipe, estou entendendo a função, mas ainda assim não estou entendendo algumas coisas que estão acontecendo na planilha.

Aqui está a tabela com as notas dos fornecedores. A função será usada em outra tabela para somar essas notas negativas se forem do fornecedor na primeira coluna e do mês na primeira linha da tabela.


aqui temos a primeira célula... onde editei a fórmula para ficar correta na tabela... me parece estar tudo correto, mas o resultado está errado. A "Areia do Juca" teve nota -1,3 em Fevereiro. Resultado devia ser 8,7


Já a coluna de Janeiro, que está com duas referencias erradas, está sendo calculada CORRETAMENTE para a Areia do Juca em FEVEREIRO, pois em janeiro devia ser 6,xx , assim como para a Explosão Combustíveis!! (obs: esses nomes de fornecedores são fictícios)



e no momento que TENTO CORRIGIR a fórmula da segunda coluna, apontando pra célula com a data, o resultado vira 0!!!!

[rogerpenna]

Ok, fazendo vários testes, parece que o problema é com as datas.

a fórmula que o amigo utilizou para o SOMARPRODUTO só está funcionando se a data for 01/mês/ano

já minha tabela pega o dia de HOJE, e cada coluna a direita ela retira um mês da data da esquerda. Mas se a data atual é 05/02/19, as próximas ficam como 05/01/19, 05/12/18, etc. E daí quebra a fórmula.

[FelipeMGarcia]

Veja se este vídeo ajuda a esclarecer as dúvidas sobre a função.

Abs

[rogerpenna]

ok pessoal. Fiz funcionar. Pra ficar mais explícito, coloquei a data inicial e a data final de cada mês acima da tabela (data inicial e final utilizando a função fimmês). Essas datas mudam é claro, conforme a data inicial inserida na tabela (a data inicial deve ser o dia atual, mas pra verificar o resultado, coloquei na imagem como junho /19).