Dúvidas sobre cálculos, funções simples e aninhadas, fórmulas matriciais, etc.
Por buzop 10 Set 2020 às 15:38
Membro Novato
Mensagens: 3
Reputação: 0
#58676
Boa tarde senhores.

Tenho a seguinte planilha na minha fábrica que uso para controlar reténs de produção, no meu estoque.

Para contexto:
→ Conforme minha produção entrega os lotes, eu separo algumas para retenção (dados da Tabela 01);
→ Eu deixo essas amostras em uma caixa numerada (coluna F: CAIXA);
→ Quando eu encho essa caixa, ela vai para um palete que estiver incompleto no estoque, conforme disponibilidade da Logística. Cada posição no estoque é referente à um palete (Tabela 02) ;

Meu fluxo de alimentação da planilha é:
→ Tabela 01: Linha à Linha
→ Tabela 02: Por coluna, de acordo com a disponibilidade.

Minha ideia, que eu não consegui executar, é executar alguma função que leia o valor da caixa na coluna F da tabela 01, procure essa referência na matriz da tabela 02 e me forneça a posição onde esta caixa está armazenada no estoque.

Por exemplo, eu estou procurando a caixa 674 (célula F5) na Tabela 01;
Na Tabela 02, a caixa 674 está na célula Q7;
O resultado que eu espero dessa fórmula seria o valor da célula Q3: "A-11-6"

Como eu devo estruturar a fórmula para ter esse tipo de resultado?


Desde já agradeço a colaboração.

Atenciosamente,
Apenas usuários registrados podem ver ou baixar anexos.
Por osvaldomp 10 Set 2020 às 17:24
Excel Expert
Mensagens: 1837
Reputação: 923
#58682
Experimente:

Código: Selecionar todos=SE(CONT.SE(J$4:R$12;F4)=0;"";ÍNDICE(J$3:R$3;SOMARPRODUTO(COL(A$4:I$12)*(J$4:R$12=F4))))
Por buzop 11 Set 2020 às 09:47
Membro Novato
Mensagens: 3
Reputação: 0
#58702
Obrigado pelo retorno Osvaldo.

Confesso que não compreendi com 100% de clareza a mecânica da função, mas parece-me que você utilizou produto de matrizes (A4:I12 x J4:R12) para extrair o resultado da posição, correto?

Corrija-me se eu estiver enganado, mas, para que a função funcione, da forma que está estruturada, é mandatório que as matrizes das tabelas 01 e 02 sejam do mesmo tamanho, certo? Se for o caso, a solução proposta não me atende...

A planilha que usei de exemplo aqui é apenas um pequeno resumo do meu banco de dados real. Minha "Tabela 01" tem quase 14k de linhas de entradas, e todos os dias eu alimento novas informações dos lotes produzidos diariamente na fábrica. Minha "Tabela 02" é mais controlada, mas mesmo assim é uma matriz de 20x50.

Diante disso, dificilmente eu conseguirei ajustar a fórmula para encaixar na minha realidade....

Tem alguma outra ideia de como resolver meu problema?


Desde já agradeço.
Por osvaldomp 11 Set 2020 às 10:17
Excel Expert
Mensagens: 1837
Reputação: 923
#58703
buzop escreveu:
Confesso que não compreendi com 100% de clareza ...
Pesquise sobre a função SOMARPRODUTO().
Para acompanhar cada etapa da execução da fórmula selecione a célula que contém a fórmula / menu Fórmulas / Avaliar Fórmula.


... é mandatório que as matrizes das tabelas 01 e 02 sejam do mesmo tamanho, ...
As tabelas do seu exemplo têm tamanhos diferentes e no entanto a fórmula funciona corretamente, então a sua afirmação não procede.

Diante disso, dificilmente eu conseguirei ajustar a fórmula para encaixar na minha realidade....
Tem alguma outra ideia de como resolver meu problema?
Não vejo problema.
1. ajuste os endereços na fórmula conforme os tamanhos das tabelas, ou
2. converta as tabelas (atuais intervalos) em Tabelas Excel


Avatar do usuário
Por Estevaoba 11 Set 2020 às 10:54
Membro 5 Estrelas
Mensagens: 784
Reputação: 371
#58708
Segue opção com fórmula matricial:
Código: Selecionar todos=SE(CONT.SE($J$4:$R$12;F4);ÍNDICE($J$3:$R$3;MÍNIMO(SE($J$4:$R$12=F4;COL($J$4:$R$4)-9)));"N/D")

Cole em G4, confirme com Ctrl+Shift+Enter e copie para baixo.
O 9 da parte COL($J$4:$R$4)-9) na fórmula refere-se ao nr de colunas antes do início da tabela 2. Ajuste conforme sua realidade.

Good luck!
Por buzop 12 Set 2020 às 09:12
Membro Novato
Mensagens: 3
Reputação: 0
#58760
Obrigado pelas respostas pessoal.

Consegui ajustar meus dados conforme a solução proposta pelo Osvaldo. Só um detalhe que para a função SOMARPRODUTO funcione, os argumentos das matrizes devem ser iguais sim.

Pode ser que a região que realmente utilizamos não seja a matriz inteira, mas os dois argumentos devem ser iguais.

Fiz os ajustes, testei algumas variantes, tá funcionando e a alimentação de novos dados não ficou comprometida.


Obrigado pelo suporte pessoal!