Notifications
Clear all

[Resolvido] Calcular limite inferior de intervalo para PROCV e SOMASES.

12 Posts
3 Usuários
5 Reactions
2,148 Visualizações
(@cesarm)
Posts: 8
Active Member
Topic starter
 

Tenho um intervalo A11 até B20 com valores por mês.

Uso as funções PROCV e SOMASES sobre o intervalo.
A célula A5 informa a qtde de meses do intervalo (no caso está com "10").
Preciso flexibilizar um pouco a operação.
O usuário pode alterar A5 para p. ex. 30.
E manualmente incluir 20 linhas embaixo do intervalo, que ficaria A11 até B40.
Pode em seguida alterar A5 para p. ex. 20 e excluir as últimas 10 linhas do intervalo.
Tenho que calcular o limite inferior do intervalo para a PROCV e a SOMASES.
Usei a função LIN para identificar a linha da célula A11 e a função ENDEREÇO para somar a célula A5 e subtrair 1.
ENDEREÇO((LIN($A$11)+$A$5-1);2) = B40
A PROCV não aceita a fórmula ENDEREÇO + LIN diretamente como final do intervalo.
Então usei a fórmula numa célula auxiliar, p. ex. A6.
E na PROCV referenciei a célula A6 como final do intervalo.
Não consegui na SOMASES, não aceita a fórmula nem a célula A6.
Agradeço qualquer ajuda.
 
Postado : 19/07/2022 12:20 am
EdsonBR
(@edsonbr)
Posts: 1057
Noble Member
 

Bom dia, @CesarM. Seja bem vindo à nossa comunidade!

Adianto a vc que tópicos sem um arquivo anexado ilustrando sua dúvida e o resultado esperado por experiência geralmente ficam sem respostas.

Ficará muito mais fácil analisar em algo já montado do que tentar abstrair mentalmente e criar do zero pra testar.

Ao anexar seu arquivo aqui no fórum, elimine ou distorça informações confidenciais como CPF's, nºs de telefone, endereços, etc.

 
Postado : 19/07/2022 7:47 am
(@cesarm)
Posts: 8
Active Member
Topic starter
 

Boa noite EdsonBR!
Eu não sabia, mas faz total sentido!
Inclusive em arquivo excel fica muito mais fácil apresentar o problema.

Em anexo arquivo excel com apresentação detalhado do problema.

Agradeço quem puder responder.

CesarM

 
Postado : 19/07/2022 11:46 pm
JSCOPA10
(@jscopa10)
Posts: 341
Reputable Member
 

@cesarm CesarM, faça um teste numa célula à parte, se for isto que você quer, é só colocar a fórmulas nas células desejadas ...

X40 ="L21"&":"&ENDEREÇO((LIN($K$21)+$K$17-1);12)

Z40 =SOMA(INDIRETO(X40))

únificando as fórmulas acima em uma só ... X42 =SOMA(INDIRETO("L21"&":"&ENDEREÇO((LIN($K$21)+$K$17-1);12)))

X62 =SOMA(INDIRETO(X40))

unificando a formula acima em uma só ... X64 =SOMA(INDIRETO("L21"&":"&ENDEREÇO((LIN($K$21)+$K$17-1);12)))

PS: tive que logar para no grupo, e depois para responder!!! AFFFF!!!

 
Postado : 20/07/2022 10:47 am
CesarM reacted
JSCOPA10
(@jscopa10)
Posts: 341
Reputable Member
 

Se não for isto que você deseja ... explique mais passo a passo ... tipo: na célula tal o resultado deveria ser tanto, por isto ....

 
Postado : 20/07/2022 11:12 am
EdsonBR
(@edsonbr)
Posts: 1057
Noble Member
 

@CesarM, entendi que sua dificuldade é retornar uma Referência (intervalo) que se dilata e contrai conforme a inserção/remoção de linhas feita pelo usuário, o que é bastante corriqueiro.

Há várias maneiras de resolver essa questão. Vejamos algumas:

  1. Usar a função DESLOC. Essa função foi feita justamente pra isso, pois ela retorna uma referência. E como vc já tem o número de linhas (mêses) na $K$17,  então a coluna de referência de mêses do exemplo ($K$21 : $K$35) pode ser reescrita como
    DESLOC($K$21; 0; 0; $K$17; 1) ou, alternativamente:
    $K$21:DESLOC($K$21; $K$17-1; 0; 1; 1)     ⇐ veja aqui que a função DESLOC está à direita dos dois pontos!
    Logicamente pra retornar a tabelinha inteira é só considerar o argumento "largura" como sendo 2 ou incluir a coluna L e continuar com largura 1;
    Usando essa metodologia, sua fórmula para PROCV ficaria:
    =PROCV($K$40; DESLOC($K$21;0; 0; $K$17; 2); 2; FALSO)

    E sua SOMASES:

    =SOMASES(DESLOC($K$21; 0; 1; $K$17; 1); DESLOC($K$21; 0; 0; $K$17; 1); ">=" & $K$56; DESLOC($K$21; 0; 0; $K$17; 1); "<=" & $L$56)
  2.  Nomear o(s) intervalo(s) de interesse e usar seu nome em vez de referências: os intervalos nomeados também são sensíveis à inserção/eliminação de células e portanto também contraem/expandem nesse sentido. Cuidar aqui apenas com o problema da inserção de linha no final do intervalo, problema antigo no Excel: por exemplo, no seu exercício, se vc selecionar a linha 36:36 e der um "Inserir", note que sua fórmula ficará aleijada, pois não considerará a soma até o final.
    Essa é minha forma predileta de uso, mas aqui costumo usar uma técnica pra evitar o problema descrito: sempre incluo no intervalo nomeado o cabeçalho e a linha de totais e uso o DESLOC para diminuir a altura em 2 pra compensar essas duas linhas. Dessa forma, como essas são linhas fixas, o problema não deve ocorrer.
  3. Da mesma forma que a função DESLOC, a função ÍNDICE também pode retornar Referências (e também pode ser usada à direita ou à esquerda do operador de intervalo, o ":"). Portanto, vc poderia adaptá-la para procurar, em combinação com CORRESP, por exemplo, a palavra "Total" e considerar o intervalo até uma linha acima.

Obs.: A determinação da quantidade de meses ($K$17) pode ser feita automaticamente conforme a inserção/eliminação de linhas tb., sem precisar ser inserida manualmente. Por exemplo (incluindo as células "Mês" e "Total"):

=LINS($K$20 : $K$36) - 2

 
Postado : 20/07/2022 2:10 pm
(@cesarm)
Posts: 8
Active Member
Topic starter
 

@jscopa10
JSCOPA10, antes de mais nada desculpe por não ter respondido antes.
Pensei que quando alguém postava resposta eu receberia e-mail avisando.
Por isso só ontem à noite abri o Fórum e vi sua resposta e a de EdsonBR.
Obrigado pelo "banho de loja" que vocês me deram sobre recursos do Excel.
Documentei as respostas para me facilitar a vida se precisar de novo desses recursos.
Tanto a alternativa de criar células auxiliares como a alternativa de unificar todas as fórmulas numa só.
Testei as 2 alternativas, tanto incluindo como excluindo linhas, funcionaram sem problema.
Estou respondendo também ao EdsonBR, as alternativas que ele apresentou, de DESLOC e de nomear os intervalos são muito interessantes, mas devo ter cometido algum erro, estou pedindo ajuda.
Na resposta para ele estou incluindo arquivo excel com a documentação que consegui montar até agora, inclusive suas 2 alternativas.
De novo muito obrigado pela atenção !!!

 
Postado : 24/07/2022 8:11 pm
(@cesarm)
Posts: 8
Active Member
Topic starter
 

@edsonbr
Boa noite EdsonBR!
Como expliquei ao JSCOPA10, desculpe não ter respondido antes, pensei que eu receberia e-mail informando de respostas à questão que postei.
Só abri o Fórum ontem à noite.
Agradeço bastante pela qualidade das respostas que vocês deram.
Testei as 2 alternativas apresentadas pelo JSCOPA10, incluindo e excluindo linhas, funcionaram, resolve o problema que apresentei.
As alternativas que você apresentou, de DESLOC e de nomear os intervalos, achei também muito interessantes. Mas devo ter cometido algum erro, estou pedindo ajuda.
Em anexo com a documentação que fiz até agora de suas respostas e das do JSCOPA10.
Por favor veja os testes que fiz com suas respostas, nas abas Resposta B1-1, Resposta B1-2, Resposta B2-1 e Resposta B2-2.
Acredito que sejam erros fáceis de corrigir.
Mais uma vez muito obrigado pela atenção.

 
Postado : 24/07/2022 8:21 pm
EdsonBR
(@edsonbr)
Posts: 1057
Noble Member
 

Ok, @CesarM, vamos lá:

  • Resposta B1-1:

Para a primeira fórmula (T47), quando respondi imaginei que talvez vc pudesse se confundir nisso, por isso mesmo havia deixado sublinhado:

Postado por: @edsonbr

...então a coluna de referência de meses do exemplo ($K$21 : $K$35) pode ser reescrita como ...
...ou, alternativamente: 
$K$21:DESLOC($K$21; $K$17-1; 0; 1; 1) 

e mais adiante

Postado por: @edsonbr

... pra retornar a tabelinha inteira é só considerar o argumento "largura" como sendo 2 ou incluir a coluna L e continuar com largura 1...

Como na sua PROCV vc precisa tanto da coluna de meses como de valores, tem que incluir a coluna L, portanto sua fórmula em T47 fica:

=PROCV($K$40; $K$21:DESLOC($K$21; $K$17-1; 1; 1; 1); 2; FALSO)

Note que o argumento cols (colunas a deslocar) da função DESLOC deve ser 1 e não 0.

Já na segunda fórmula (T57), tá cheia de erros:

- o intervalo_de_soma da SOMASES está usando a coluna K ao invés da L;

- o intervalo_de_critérios1 e o intervalo_de_critérios2 estão considerando 2 colunas cada ao invés de uma (largura 2 na DESLOC);

- critérios_1 está apontando pra célula K50 que não tem nada a ver (é um texto), quando deveria ser a K52

- critérios_2, igualmente aponta para L50 ao invés de L52

Então a fórmula em T57 deveria estar assim:

=SOMASES($L$21:DESLOC($L$21; $K$17-1; 0; 1; 1);
                 $K$21:DESLOC($K$21; $K$17-1; 0; 1; 1); ">=" & $K$52;
                 $K$21:DESLOC($K$21; $K$17-1; 0; 1; 1); "<=" & $L$52)
  • Resposta B1-2:

 Mesmas orientações da Resposta B1-1.

  • Resposta B2-2:

Se as linhas forem inseridas em qualquer lugar no meio do intervalo, ele dilata também. Agora, se vc selecionou a linha do Total, que está fora do intervalo nomeado, aí não cresce junto - note a recomendação de cuidado a esse respeito na minha outra postagem:

Postado por: @edsonbr

...Cuidar aqui apenas com o problema da inserção de linha no final do intervalo, problema antigo no Excel: por exemplo, no seu exercício, se vc selecionar a linha 36:36 e der um "Inserir", note que sua fórmula ficará aleijada, pois não considerará a soma até o final.

Reveja também o truque pra contornar essa situação.

 

Caso tenha dificuldade pra reparar os apontamentos sugeridos, retorne com as novas dúvidas ou se precisar que anexe seu arquivo já com essas implementações.

 
Postado : 25/07/2022 2:35 am
CesarM reacted
(@cesarm)
Posts: 8
Active Member
Topic starter
 

Boa noite EdsonBR!
De novo obrigado pelo "banho de loja" sobre recursos do Excel.
As correções que você apresentou funcionaram, entendi a sintaxe da DESLOC.
Na T44 é uma forma de criar uma "tabela interna" com 2 colunas.
Na T47 é uma forma de criar uma "tabela interna" com apenas 1 coluna. Inclusive no exemplo que você apresentou nem há necessidade de informar os 2 últimos argumentos, que são opcionais.
E a dica de incluir a linha de Total quando se atribui nome a um intervalo (na prática uma "tabela interna") funcionou. Vai ser muito útil para mim.
De novo muito obrigado pela atenção e, principalmente, pela excelente qualidade das respostas.

 
Postado : 27/07/2022 12:04 am
EdsonBR reacted
EdsonBR
(@edsonbr)
Posts: 1057
Noble Member
 

Obrigado, @CesarM!

Apenas corrigindo um detalhe teórico/prático, já que vc está aproveitando esse tópico pra assimilar conhecimento:

Postado por: @cesarm

Na T47 é uma forma de criar uma "tabela interna" com apenas 1 coluna.

Não é bem isso. Vamos rever um dos fundamentos desse software:

O Excel trabalha com 3 tipos de operadores de referência: o dois-pontos (operador de intervalo), o ponto-e-vírgula (operador de união) e o espaço em branco (operador de intersecção). Concentremo-nos no operador de intervalo ( : )

Quando usamos uma referência tipo A1:C3 por exemplo, estamos dizendo pro Excel considerar todas as células entre essas duas referências, inclusive elas próprias. A1 sendo o canto superior esquerdo e C3 sendo o canto inferior direito. Não percamos de vista que ambas são células únicas.

Agora, nós poderíamos tanto pra A1 como pra C3, ao invés de usar uma referência direta a essas duas células dos extremos, usar uma fórmula que retorne uma, outra ou ambas.

Voltando ao problema então, no caso da fórmula da célula T47, o argumento matriz_tabela da PROCV é

$K$21:DESLOC($K$21; $K$17-1; 1; 1; 1)

E lá em cima eu já tinha feito o seguinte apontamento sobre essa referência:

Postado por: @edsonbr

 ⇐ veja aqui que a função DESLOC está à direita dos dois pontos!

Então, K21 é a célula do canto superior esquerdo enquanto que a célula do canto inferior direito do intervalo (à direita dos dois-pontos) será a K21 deslocada verticalmente da quantidade obtida subtraindo o conteúdo de K17-1, deslocada horizontalmente em 1 coluna, ou seja, coluna L, e terá altura e largura 1 - ou seja, uma única célula.

Portanto o resultado dessa bagaça é a referência K21:L35 quando o número de meses (K17) é 15, sendo uma referência de 2 colunas e não apenas uma.

 

 

 

 
Postado : 27/07/2022 2:15 am
CesarM reacted
(@cesarm)
Posts: 8
Active Member
Topic starter
 

OK EdsonBR, obrigado pela correção.
Eu entendi o conceito mas expressei de forma completamente errada.
E, nessas coisas, a gente tem que se expressar de forma conceitualmente correta.
Uma expressão correta seria:
Tanto o PROCV apresentado em T44 como o apresentado em T47 usam o mesmo intervalo ("tabela interna") K21:L35.
A função DESLOC tem 5 argumentos, sendo o primeiro uma referência, em seguida 2 pares de argumentos. O primeiro par define linha e coluna, o segundo par define altura e largura.
Na T44 a função DESLOC tem a célula K21 como referência. No segundo par de argumentos a altura é definida por K17 (ou seja, "desce" a qtde de linhas indicada por K17) e a largura é 2 (ou seja, é a segunda coluna, a que fica ao lado da coluna K da referência). Com isso a própria função DESLOC define o intervalo K21:L35.
Na T47 a célula inicial do intervalo, K21, é definida fora da função DESLOC. Também define K21 como o argumento de referência. Usa o primeiro par de argumentos. A linha é a de K21, somada de K17 - 1. E para definir a coluna soma 1 na coluna de K21. Assim, a DESLOC define apenas a segunda célula do intervalo, no caso L35. As células K21 e L35 precisam ser ligadas por ":", para se definir o intervalo.

Ufa .... acho que está bem mais perto de uma definição conceitualmente correta. Acabou me obrigando a estudar com mais cuidado a função DESLOC.
De novo, muito obrigado pelo cuidado com o rigor.

Ops ... tanto suas soluções como as apresentadas por JSCOPA10 já estou usando em outros casos, além do problema que gerou minha postagem original.
Valeu !!!

 
Postado : 28/07/2022 1:20 pm
EdsonBR reacted