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

[Alexandre José Costa]

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

[CasaDoHardware]

Não olhei a planilha, mas você pode inserir uma coluna informando a moeda que faz parte o saldo.

 

 

[Alexandre José Costa]

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)

[deciog]

@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

[Alexandre José Costa]

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).

 

[Alexandre José Costa]

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. :-)

 

[deciog]

@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

[Alexandre José Costa]

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?

 

[deciog]

@Alexandre José Costa , Bom Dia.

 

Obrigado pelo seu feedback

 

Fico contente por ter ajudado

 

Decio