Notifications
Clear all

Ajuda com DESLOC/CORRESP

10 Posts
4 Usuários
0 Reactions
1,347 Visualizações
(@augustus88)
Posts: 5
Active Member
Topic starter
 

Pessoal, preciso calcular o intervalo de tempo entre dois tipos de itens distintos, neste caso me refiro à fórmula dá célula U44(que considera a família de itens 1):

=SEERRO(SE(E(SOMASES($I:$I,$R:$R,$AL$2,$B:$B,B44)>0,P44<>""),SOMASES($I:$I,$R:$R,$AL$2,$B:$B,B44)/SE(E(G44=DESLOC($A$1,CORRESP(MENOR(SE($J:$J=J45,$G:$G,""),1),$G$2:$G$82,0),6),R44=$AL$2),TEMPO(HORA(MENOR(IF($J:$J=J45,$G:$G,""),1)),MINUTO(MENOR(SE($J:$J=J45,$G:$G,""),1)),)-$AM$2,(MAIOR(SE($R:$R=$AL$2,SE($B:$B=B44,$O:$O,""),""),1)-DESLOC($A$1,(CORRESP(MENOR(SE($R:$R=$AL$2,SE($B:$B=B44,$O:$O,""),""),1),$O$2:$O$82,0)-1),14))*24),""),"")

Ou seja, se não de erro na fórmula, ele testa se o p44(controle de pedido) está preenchido e se a soma dos tempos correspondentes a familia 1 é maior do que 0. Caso sejam, então ele vai somar os tempos dessa família e dividir por critérios diferentes.

O critério que me interessa é:

(MAIOR(SE($R:$R=$AL$2,SE($B:$B=B44,$O:$O,""),""),1)-DESLOC($A$1,(CORRESP(MENOR(SE($R:$R=$AL$2,SE($B:$B=B44,$O:$O,""),""),1),$O$2:$O$82,0)-1),14))*24)

Que no caso ele vai pegar o maior tempo da familia da linha 44 que é família 1(1:34 h) e vai diminuir pelo valor equivalente a uma posição acima do menor tempo da mesma família 1(1:33). Pelo menos era isso que ele deveria fazer, mas acompanhando a fórmula ele não está fazendo isso.

Nesse caso, o cálculo deveria ser:

A soma do peso da família 1 para a nota 218457 (4,7 kg) / VALOR TRANSFORMADO EM HORA (maior tempo da familia 1 (1:34 - cel. O49) - valor correspondente a uma posição acima do menor tempo da familia 1(1:33 - cel. O47)

Resultado: deveria ser 282 kg/H, mas esta dando 141 kg/H

Gostaria de sugestões para resolver esse meu problema ou alternativas de fórmula, lembrando que precisa buscar o valor e não utilizar referências fixas do tipo O49-O47, a planilha tem q descobrir o menor valor e fazer o cálculo para que no momento que eu alterar a base de dados, os cálculos sejam refeitos.

Agradeço a ajuda e fico a disposição para esclarecimentos caso necessário.

EDIT: Simplificação do problema:
Preciso encontrar o menor valor de uma condicional e retornar o valor da coluna e linha imediatamente superior:
Exemplo na célula B44:

DESLOC($A$1,(CORRESP(MENOR(SE($R:$R=$AL$2,SE($B:$B=B44,$O:$O,""),""),1),$O$2:$O$82,0)-1),14))

Acompanhando a fórmula aqui, o Excel está buscando a célula incorreta (O44 ao invés de O47)
Gostaria de sugestões para resolver esse meu problema ou alternativas de fórmula, lembrando que precisa buscar o valor e não utilizar referências fixas do tipo O49-O47.
Agradeço a ajuda e fico a disposição para esclarecimentos caso necessário.

Muito obrigado!

 
Postado : 30/03/2014 9:35 pm
(@gtsalikis)
Posts: 2373
Noble Member
 

Então... vc colocou 3 fórmulas, que, creio eu, não façam o que vc quer, e não deu pra entender o que é pra fazer, nem onde... ou seja, não entendi :/

Lembre-se de AGRADECER aos que te ajudaram, e de marcar o tópico como [Resolvido]
Gente que cuida de gente.

Gilmar

 
Postado : 01/04/2014 5:47 am
(@edcronos)
Posts: 1006
Noble Member
 

tente colocar numa planilha, valores o mais real possível da sua, com posições exatas e explique oq vc quer que a planilha faça.
Tipo,
" quero que a formula me apresente a soma de bh4 e f200 se na celula a5 estiver com X e a data de gh for anterior a 2002"

Somente é impossíveis até que alguém faça
A logica está presa na irracionalidade humana, e morta nos que se consideram donos da verdade.

"ALGUM MODERADOR ME EXPULSE DO FÓRUM POR FAVOR"

 
Postado : 01/04/2014 6:24 am
(@augustus88)
Posts: 5
Active Member
Topic starter
 

Hehehe...

vamos tentar de novo:

Preciso encontrar o menor valor correspondente da coluna O seguindo a condicional: coluna Q tenha o valor de 1;

A única fórmula que encontrei foi de fazer uma matriz para achar o menor valor: MENOR(SE($Q:$Q=$AK$2;SE($B:$B=B44;$O:$O;"");"");1)

E retornar o valor correspondente na coluna O: CORRESP(MENOR(SE($Q:$Q=$AK$2;SE($B:$B=B44;$O:$O;"");"");1);$O$2:$O$82;0)

Sabendo a posição onde está esse valor, eu precisaria retornar o valor que está 1 linha acima dessa posição encontrada:

DESLOC($A$1;(CORRESP(MENOR(SE($Q:$Q=$AK$2;SE($B:$B=B44;$O:$O;"");"");1);$O$2:$O$82;0)-1);14))

O problema é que acompanhando a fórmula, o Excel está buscando a célula incorreta (O46 ao invés de O47), devido ao valor buscado (1:33) ser igual em duas linhas(Excel pega a linha que aparece primeiro).

Eu precisaria que ele pegasse o valor da coluna O correto, que nesse caso para mim seria o valor O47

=//

Muito obrigado!

 
Postado : 01/04/2014 6:26 am
(@gtsalikis)
Posts: 2373
Noble Member
 

Vc pode usar uma dessas 2 matriciais:

=MÍN(SE($Q$2:$Q$82=1;$O$2:$O$82))

=MÍN(SUBSTITUIR(--($Q$2:$Q$82=1);0;1000000)*($O$2:$O$82))

Lembre-se de AGRADECER aos que te ajudaram, e de marcar o tópico como [Resolvido]
Gente que cuida de gente.

Gilmar

 
Postado : 01/04/2014 7:05 am
(@edcronos)
Posts: 1006
Noble Member
 

bem,
mas qual o diferencial entre essas duas?
como vc falou os valores são iguais e a formula não sabe qual vc quer,
então logicamente ela vai pegar a primeira que achar

Somente é impossíveis até que alguém faça
A logica está presa na irracionalidade humana, e morta nos que se consideram donos da verdade.

"ALGUM MODERADOR ME EXPULSE DO FÓRUM POR FAVOR"

 
Postado : 01/04/2014 7:06 am
Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
 

Se é que entendi experimente
=INDIRETO("o"&CORRESP(MENOR(SE($Q2:$Q82=$AK$2;SE($B2:$B82=B44;$O2:$O82;"");"");1);$O2:$O82;1)) (matricial ctrl+alt+enter)

Existem mil maneiras de preparar Neston. Invente a sua!
http://www.youtube.com/ExpressoExcel

 
Postado : 01/04/2014 7:07 am
(@augustus88)
Posts: 5
Active Member
Topic starter
 

Antes de mais nada, obrigado por tentarem me ajudar!

Vamos lá...

bem,
mas qual o diferencial entre essas duas?
como vc falou os valores são iguais e a formula não sabe qual vc quer,
então logicamente ela vai pegar a primeira que achar

Cara, o diferencial é que um tempo é de uma família de produtos e eu preciso saber o menor tempo respeitando a condição, nesse caso, de família de produtos =1 (coluna R)
Eu convencionei que o tempo decorrido da família 1 é igual ao maior tempo da família 1 - tempo correspondente a uma coluna acima do menor tempo da família 1:

Assim:

Família 1: maior tempo: 1:34 min (O49)
menor tempo: 1:33 min (048) -> No desloc + corresp ele pega acima da célula O47 e isso resulta em 1:32, quando na verdade deveria ser a própria O47(célula O48 - 1 = O47)

O resultado de T44(exemplo do topico) deveria ser 282 kg/H, mas esta dando 141 kg/H justamente por estar pegando 1 célula acima.

Vc pode usar uma dessas 2 matriciais:

=MÍN(SE($Q$2:$Q$82=1;$O$2:$O$82))

=MÍN(SUBSTITUIR(--($Q$2:$Q$82=1);0;1000000)*($O$2:$O$82))

gtsalikis, não consegui fazer funcionar essa tua fórmula. Excel 2010 aqui.
Obrigado.

Se é que entendi experimente
=INDIRETO("o"&CORRESP(MENOR(SE($Q2:$Q82=$AK$2;SE($B2:$B82=B44;$O2:$O82;"");"");1);$O2:$O82;1)) (matricial ctrl+alt+enter)

Reinaldo, qual o critério utilizado pelo CORRESP na busca quando a mesma não é exata?

Eu precisaria que buscasse exatamente a célula referência seguindo os critérios da formula: MENOR(SE($Q:$Q=$AK$2;SE($B:$B=B43;$O:$O;"");"");1)

Ou seja, o resultado seria 1:33 e a referencia célula O48.

Só complementando:

Aí então, com a referência do valor por célula (O48), eu poderia utilizar a função CÉL("conteúdo",O48) e ter o valor pretendido.

 
Postado : 02/04/2014 11:41 am
Fernando Fernandes
(@fernandofernandes)
Posts: 43750
Illustrious Member
 

Augustus já conseguiu solução no seu problema?

Existem mil maneiras de preparar Neston. Invente a sua!
http://www.youtube.com/ExpressoExcel

 
Postado : 03/04/2014 6:44 am
(@augustus88)
Posts: 5
Active Member
Topic starter
 

Augustus já conseguiu solução no seu problema?

Opa vhdocavaco!

Quebrei a cabeça, estava a dias tentando e finalmente consegui! Porém, agora, estou com outro problema e gostaria de interceder à ajuda dos colegas.

A solução que achei foi fazer um desloc à partir daonde eu precisava:

DESLOC($A1,(SOMASES($B2:$B$29669,$B2,$R2:$R$29669,$R2)),14)-$O1

No caso, a formula me atendia ate o momento que percebi que há situações que fogem à regra. A fórmula vai pegar exatamente os valores que eu preciso, porém se os valores estiverem fora de sequencia, como por exemplo na planilha exemplo os valores sao:

Valores de Q1 a Q18: Controle 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8

Se for contar, há 17 valores para a pré-nota 218445, porém se tivessemos abaixo disso novos valores 8, como o intervalo é dinâmico e não fixo, ele iria contar e assim estragaria o resultado.

Eu precisaria que ele contasse somente se seguisse a sequência, se fosse como nesse caso: Controle 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 5 3 8, a formula contaria 18 valores, mas eu precisaria que contasse apenas 17(pois estao na sequencia).

Agradeço a ajuda!

 
Postado : 07/04/2014 8:55 pm