Ir ao conteúdo
  • Cadastre-se
Alexandre José Costa

Excel excel - somarproduto com várias condições

Recommended Posts

Olá amigos.  

 

Montei uma planilha para controle de saldos de diversas corretoras (ver plan anexa). 

Na última coluna (R) ela deve somar valores atendendo três condições:

 

(a) Somar o saldo por corretora, (b) por moeda, (c) até a data especificada na mesma linha.

 

Para atender estes três critérios usei a função SOMARPRODUTO. 

=SOMARPRODUTO(($D$5:$D719=D5)*(($C$5:$C719>=VALOR($C$5))-($C$5:$C719>VALOR(C5)))*($P$5:$Q719))

 

Esta função vai bem até o momento em que aparece uma linha com outra moeda. (não posso somar saldo em reais com saldo de outra moeda). Tudo funciona bem, o problema realmente está na última coluna, onde deve aparecer o saldo por corretora, por moeda e por data.

 

Estou há dois dias batendo a cabeça com isso. Alguém, por favor, poderia me ajudar?

OBS: A FÓRMULA NÃO PODE SER MATRICIAL (Ex: PROCV, CORRESP, ÍNDICE, ETC).

Desde já agradeço a ajuda.

 

Saldo por Conta e Moeda.xlsx

Editado por Alexandre José Costa

Compartilhar este post


Link para o post
Compartilhar em outros sites

Olá Casa do Hardware, 
Sim, a coluna descrevendo as moedas faz parte da planilha. É mais um quebra-cabeça com a fórmula mesmo.

Acha que pode ajudar?

adicionado 58 minutos depois

O melhor é ver a planilha anexa,mas para ajudar eu detalho o que fiz na fórmula que funcionaria bem se tivéssemos apenas uma moeda:

 

=SOMARPRODUTO(($D$5:$D719=D5)*(($C$5:$C719>=VALOR($C$5))-($C$5:$C719>VALOR(C5)))*($P$5:$Q719))

 

($D$5:$D719=D5) -------------------> indica a corretora
($C$5:$C719>=VALOR($C$5)) --> indica a coluda de datas (desde a primeira data até a linha atual)
($C$5:$C719>VALOR(C5)) -------> indica a coluna de datas (que são superiores a data da linha atual)
($P$5:$Q719) -------------------------> indica as duas colunas onde temos os saldos parciais de cada lançamento)

Editado por Alexandre José Costa

Compartilhar este post


Link para o post
Compartilhar em outros sites

@Alexandre José Costa , Bom Dia.

 

Confere se é desta forma que quer o resultado, se não for esse então coloca o resultado esperado.

 

Se foi útil clica no curtir é uma forma de agradecimento pela ajuda

Se você Visitante Também gostou e foi útil pode clicar no Curtir
 

Decio

 

Saldo por Conta e Moeda Decio.xlsx

  • Curtir 1

Compartilhar este post


Link para o post
Compartilhar em outros sites

Olá deciog,

Sua interação gerou avanços. Vamos lá.

 

O que preciso é do saldo acumulado por corretora, por moeda, a cada nova linha/lançamento. Ex:

Enquanto temos apenas uma moeda, os saldos acumulados para diferentes corretoras funcionam, mas quando entra uma nova moeda, para a mesma corretora e diferente de reais, ela continua somando esta nova moeda aos saldos acumulados em reais. Isso não pode acontecer. Cada corretora deveria ser o saldo acumulado em reais e também o saldo acumulado em outra moeda qualquer que fosse lançada. Os resultados esperados são:

 

Observe a linha 9 da planilha. Este é o primeiro caso onde aparece uma nova moeda diferente do real para a corretora MBT. Portanto, o resultado esperado para esta corretora, para esta nova moeda, até esta data deveria ser 0,17136436.

 

Na sequência, o resultado esperado para a linha 10 é 0,00026436, porque quase a totalidade da moeda comprada/adquirida pela corretora MBT foi transferida para a outra corretora, a ARN. Então, temos 0,17136436 - 0,17110000 = 0,00026436. 

 

Curiosidade: A linha 11 mostra a fórmula funcionando corretamente, porque é a primeira vez que a corretora ARN aparece, isto é, é o primeiro depósito para a ARN. Logo, o saldo acumulado para a ARN, nesta moeda, até esta data é de 0,17110000. O que está correto.

 

Em sua fórmula, vi que você mudou a célula que origina os cálculos, ou seja, era $D$5 você mudou para $D$4, era $C$5 você mudou para $C$4, era $P$5 você mudou para $P$4. Veja:

 

Antes era:

=SOMARPRODUTO(($D$5:$D719=D5)*(($C$5:$C719>=VALOR($C$5))-($C$5:$C719>VALOR(C5)))*($P$5:$Q719))


Você mudou para:

=SOMARPRODUTO(($D$4:$D5=D5)*(($C$4:$C5>=VALOR($C$5))-($C$4:$C5>VALOR(C5)))*($P$4:$Q5))

 

Eu não entendi o motivo, da alteração citada acima, já que as novas células de referência que colocou caem sobre os rótulos das colunas.

 

Outra alteração - muito boa - que você fez foi reduzir o range de $D$5:$D719 para $D$4:$D5, de $C$5:$C719 para $C$4:$C5, de $P$5:$Q719 para $P$4:$Q5. Isso me parece mais inteligente, uma vez que apenas a próxima linha entrará os cálculos, aumentando o range, automaticamente, linha a linha. Isso é um avanço considerável. 
 

Por hora, ainda falta a última coluna dar o saldo acumulado, por corretora, por moeda, a cada nova data lançada (em cada linha).

 

Editado por Alexandre José Costa

Compartilhar este post


Link para o post
Compartilhar em outros sites

Deciog,

 

Com sua alteração na fórmula, nós podemos reduzir um argumento simplificando a fórmula assim:

 

=SOMARPRODUTO(($D$4:$D5=D5)*(($C$4:$C5>=VALOR($C$5))-($C$4:$C5>VALOR(C5)))*($P$4:$Q5))

=SOMARPRODUTO(($D$4:$D5=D5)*(($C$4:$C5>=VALOR($C$5))*($P$4:$Q5)))

 

O argumento que exclui, antes existia porque eu delimitava o range para cerca de 700 linhas depois. Com sua alteração na fórmula, simplesmente aumentando o range linha a linha, este argumento perdeu o sentido. :-)

 

Antes era:

=SOMARPRODUTO(($D$5:$D719=D5)*(($C$5:$C719>=VALOR($C$5))-($C$5:$C719>VALOR(C5)))*($P$5:$Q719))


Você mudou para:

=SOMARPRODUTO(($D$4:$D5=D5)*(($C$4:$C5>=VALOR($C$5))-($C$4:$C5>VALOR(C5)))*($P$4:$Q5))

 

E agora com a exclusão do argumento, eu deixei assim:

=SOMARPRODUTO(($D$4:$D5=D5)*(($C$4:$C5>=VALOR($C$5))*($P$4:$Q5)))

 

Estamos progredindo. 
 

Cheguei no resultado desejado! Eis a fórmula:

=SOMARPRODUTO(($D$4:$D9=D9)*($H$4:$H9=H9)*(($C$4:$C9>=VALOR($C$5))*($P$4:$Q9)))

 

Com a diminuição de um argumento, a fórmula ficou mais simple e pude acrescentar o argumento correto. ;-)

 

OBS: estou chamando a sentença da fórmula de argumento, mas neste caso o correto é dizer "matriz".

 

Obrigado a todos pela colaboração, especialmente ao @deciog que com uma simples alteração na fórmula me permitiu desenvolvê-la melhor. :-)

 

Compartilhar este post


Link para o post
Compartilhar em outros sites

@Alexandre José Costa , Bom Dia.

 

Agora entendi o que você queria, confere o modelo se esta correto.

 

Se foi útil clica no curtir é uma forma de agradecimento pela ajuda

Se você Visitante Também gostou e foi útil pode clicar no Curtir
 

Decio

 

 

Saldo por Conta e Moeda Decio 2.xlsx

Editado por deciog
  • Curtir 1

Compartilhar este post


Link para o post
Compartilhar em outros sites

Caro @deciog,

 

Obrigado pelo retorno.

 

Vi que você simplificou ainda mais a fórmula. Eu imaginei que para termos um saldo acumulado por corretora, por moeda e até o último lançamento (até a última linha lançada), eu deveria incluir, na fórmula, uma matriz com as datas de cada lançamento. Mas pelo que vi em sua fórmula, isso não é necessário! 

 

Pelo que entendi, não adianta eu colocar, na fórmula, a matriz com as datas porque a fórmula simplesmente vai incluir no cálculo uma informação que não interfere no resultado, isto é, qualquer valor que seja >=a data lançada, é o mesmo que dizer "qualquer data acima do último lançamento que estou fazendo". Como as outras matrizes iniciam-se e terminam no mesmo ponto (mesma quantidade de linhas) ($D$4:$D11=D11)*($H$4:$H11=H11), a fórmula sempre fará a soma do produto dessas matrizes.

 

Pelo que me parece, uma boa analogia é dizer que cada matriz funciona como um filtro do Excel. Uma tabela qualquer com muitas colunas, vai se limitando (ficando cada vez menor) a cada filtro que aplicamos.

É isso mesmo?


 

Compartilhar este post


Link para o post
Compartilhar em outros sites

Crie uma conta ou entre para comentar

Você precisar ser um membro 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 publicações 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

×