Excel Cálculo usando fórmula matricial

[mcoumiotis]

Boa noite a todos!

 

Alguém teria uma solução para realizar cálculos em diversas linhas utilizando uma única fórmula matricial?

 

Isso porque a planilha que utilizamos tem um padrão todo feito com fórmulas matriciais que capturam valores de outras abas e jogam para a aba desejada, porém na última coluna em que preciso calcular os saldos, não estou tendo ideia de como fazer com matricial.

 

Em anexo segue um exemplo simplificado!

 

Obrigado quem puder ajudar.

Modelo.xlsx

[Midori]

@mcoumiotis Cole na primeira linha do saldo. Não deixe nenhum valor a baixo da tabela (coluna valor) ou pode entrar no cálculo. O range da coluna vai aumentar na fórmula na quantidade das linhas da soma.

 

=SUBTOTAL(9;DESLOC(D4:D16;LIN(INDIRETO("1:"&CONT.VALORES(D4:D16)))-1;0))

 

[Midori]

Se tiver problema com a primeira fórmula por causa do range que comentei, fiz um a juste para evitar isso,

 

=SUBTOTAL(9;DESLOC(INDIRETO("D4:D"&MÁXIMO(LIN(D4:D16))-(LIN(INDIRETO("1:"&CONT.VALORES(D4:D16)))-1));(LIN(INDIRETO("1:"&CONT.VALORES(D4:D16)))-1);0))

 

[mcoumiotis]

Em 18/07/2023 às 09:53, Midori disse:

@mcoumiotis Cole na primeira linha do saldo. Não deixe nenhum valor a baixo da tabela (coluna valor) ou pode entrar no cálculo. O range da coluna vai aumentar na fórmula na quantidade das linhas da soma.

 

=SUBTOTAL(9;DESLOC(D4:D16;LIN(INDIRETO("1:"&CONT.VALORES(D4:D16)))-1;0))

 

 

Excelente solução @Midori ! Genial!!

 

Era exatamente o que eu estava buscando.

 

Sempre grato pela ajuda!

 

Abs

[Midori]

@mcoumiotis Não recomendo usar a primeira fórmula por causa da questão do range. Cheguei em outra mais compacta que a segunda,

 

=SUBTOTAL(9;INDIRETO(ENDEREÇO(LIN(D4:D16);COL(D:D))&":"&ENDEREÇO(MÁXIMO(LIN(D4:D16));COL(D:D))))

 

[mcoumiotis]

Em 19/07/2023 às 16:47, Midori disse:

@mcoumiotis Não recomendo usar a primeira fórmula por causa da questão do range. Cheguei em outra mais compacta que a segunda,

 

=SUBTOTAL(9;INDIRETO(ENDEREÇO(LIN(D4:D16);COL(D:D))&":"&ENDEREÇO(MÁXIMO(LIN(D4:D16));COL(D:D))))

 

 

@Midori de acordo com aquela primeira fórmula que você havia sugerido, fiz uma adaptação e estou usando assim:

 

=SUBTOTAL(9;DESLOC(D4:D9999;LIN(INDIRETO("1:"&CONT.VALORES(D4:D9999)))-1;0))

 

Funcionou perfeitamente e o range que ela pega é exatamente o mesmo range dos valores totais.

 

O risco dessa fórmula é só a possibilidade de ter valores soltos abaixo do último, na coluna de valor total, ou há algo mais?

[Midori]

Em 21/07/2023 às 18:00, mcoumiotis disse:

O risco dessa fórmula é só a possibilidade de ter valores soltos abaixo do último, na coluna de valor total, ou há algo mais?

Se o deslocamento passar do limite de linhas da planilha vai dar erro em algumas. Mas só se tiver outra edição que cause isso ou a tabela passe da metade de linhas da planilha.

 

[mcoumiotis]

11 horas atrás, Midori disse:

Se o deslocamento passar do limite de linhas da planilha vai dar erro em algumas. Mas só se tiver outra edição que cause isso ou a tabela passe da metade de linhas da planilha.

 

 

Sem problemas! Não chega nem perto disso. Excelente solução!