FÓrmula para procurar datas

Kadu Du · 29 de agosto de 2013

Tenho essa planilha http://www.sendspace.com/file/arfenz

Quero buscar na aba DADOS determinado produto pelo código e quero que ele retorne todas as datas de entrada na aba TESTE. Observe que na aba DADOS tenho uma coluna com data de recebimento e gostaria de ter o histórico por datas para a montagem dos gráficos.

O nosso amigo Vini deixou essa fórmula e não estou conseguindo utilizar a mesma:

{=SEERRO(ÍNDICE(TESTE!$L$1:$L$121;MENOR((TESTE!$D$1:$D$121=$A2)*LIN(TESTE!$D$1:$D$121);SOMARPRODUTO(N((TESTE!$D$1:$D$121=$A2)*LIN(TESTE!$D$1:$D$121)=0))+COL()-14));"")}

Quando coloco na célula e arrasto para as demais, o resultado sempre aparece 00/01/1900

Grato

vinibpeixoto · 30 de agosto de 2013

Olá, kyok,

Isto ocorre pois as células que deveriam conter a data do pedido, na planilha TESTE, estão vazias.

Copie a fórmula citada a partir da linha 2 da planilha DADOS, arraste-a para baixo e confira a linha 144 da planilha. Você perceberá que, nesta linha, a data será corretamente retornada; o CÓDIGO referenciado nesta linha é o único que contém a DATA DO PEDIDO definida na planilha TESTE.

Kadu Du · 30 de agosto de 2013

Vini bom dia!

Seria possível você dar uma pequena explicação da fórmula? Apenas troquei o valor de referência. Estou buscando a data e não a data do pedido.

Grato

vinibpeixoto · 31 de agosto de 2013

Olá, kyok,

Acredito que você percebeu que, ao alterar o intervalo TESTE!$L$1:$L$121, que correspondia às datas dos pedidos, para TESTE!$A$1:$A$121, as datas foram corretamente retornadas pela fórmula, não é?

---

Abaixo, explicação da fórmula.

{=SEERRO(ÍNDICE(TESTE!$A$1:$A$121;MENOR([COLOR="Red"](TESTE!$D$1:$D$121=$A20)[/COLOR]*LIN(TESTE!$D$1:$D$121);SOMARPRODUTO(N([COLOR="Red"](TESTE!$D$1:$D$121=$A20)[/COLOR]*LIN(TESTE!$D$1:$D$121)=0))+COL()-14));"")}

O trecho destacado acima cria um vetor com valores booleanos, a partir da comparação entre a coluna CÓDIGO DO FORNECEDOR, da planilha TESTE, e o CÓDIGO que está sendo procurado. Abaixo, exemplo de um vetor que poderia ser retornado.

- 18921/E4 = 28857/34 -> FALSO

- 28857/34 = 28857/34 -> VERDADEIRO

- 26136/42 = 28857/34 -> FALSO

- 39630/35 = 28857/34 -> FALSO

{=SEERRO(ÍNDICE(TESTE!$A$1:$A$121;MENOR((TESTE!$D$1:$D$121=$A20)*[COLOR="Red"]LIN(TESTE!$D$1:$D$121)[/COLOR];SOMARPRODUTO(N((TESTE!$D$1:$D$121=$A20)*[COLOR="Red"]LIN(TESTE!$D$1:$D$121)[/COLOR]=0))+COL()-14));"")}

O trecho destacado acima cria um vetor com o número das linhas dos códigos dos fornecedores.

- 18921/E4 -> 1

- 28857/34 -> 2

- 28857/34 -> 3

- 28857/34 -> 4

- 26136/42 -> 5

- 39630/35 -> 6

{=SEERRO(ÍNDICE(TESTE!$A$1:$A$121;MENOR([COLOR="Red"](TESTE!$D$1:$D$121=$A20)*LIN(TESTE!$D$1:$D$121)[/COLOR];SOMARPRODUTO(N((TESTE!$D$1:$D$121=$A20)*LIN(TESTE!$D$1:$D$121)=0))+COL()-14));"")}

A multiplicação entre ambos os vetores criados anteriormente cria um terceiro vetor, que corresponde aos números das linhas onde o código inicialmente pesquisado foi encontrado.

- 0 (FALSO) * 1 -> 0 (o código pesquisado não foi encontrado na primeira linha)

- 1 (VERDADEIRO) * 2 -> 2 (o código pesquisado foi encontrado na segunda linha)

- 1 * 3 -> 3

- 1 * 4 -> 4

- 0 * 5 -> 0

- 0 * 6 -> 0

A função menor retorna o k-ésimo menor número de uma determinada matriz. Portanto, a partir do terceiro vetor criado, a fórmula pode retornar qualquer k-ésimo elemento desejado.

MENOR([TERCEIRO VETOR CRIADO]; 1) = 0 (primeiro menor elemento do vetor)

MENOR([TERCEIRO VETOR CRIADO]; 2) = 0 (segundo menor elemento do vetor)

MENOR([TERCEIRO VETOR CRIADO]; 3) = 0

MENOR([TERCEIRO VETOR CRIADO]; 4) = 2

MENOR([TERCEIRO VETOR CRIADO]; 5) = 3

MENOR([TERCEIRO VETOR CRIADO]; 6) = 4

Conforme exemplos acima, observa-se que a função menor também retorna os valores zerados, o que não ajuda na resolução do problema.

Portanto, a função SOMARPRODUTO, destacada abaixo, soma a quantidade de zeros existentes no vetor criado.

{=SEERRO(ÍNDICE(TESTE!$A$1:$A$121;MENOR((TESTE!$D$1:$D$121=$A20)*LIN(TESTE!$D$1:$D$121);[COLOR="Red"]SOMARPRODUTO(N((TESTE!$D$1:$D$121=$A20)*LIN(TESTE!$D$1:$D$121)=0))[/COLOR]+COL()-14));"")}

Com a quantidade de zeros definida, ao invés da função MENOR retornar simplesmente o k-ésimo menor número do vetor, ela retorna o seu [k-ésimo + quantidade de zeros] menor número.

MENOR([TERCEIRO VETOR CRIADO]; 1 + QUANTIDADE DE ZEROS) = 2

MENOR([TERCEIRO VETOR CRIADO]; 2 + QUANTIDADE DE ZEROS) = 3

MENOR([TERCEIRO VETOR CRIADO]; 3 + QUANTIDADE DE ZEROS) = 4

O k-ésimo número, na fórmula utilizada, é definido pelo trecho destacado abaixo.

{=SEERRO(ÍNDICE(TESTE!$A$1:$A$121;MENOR((TESTE!$D$1:$D$121=$A20)*LIN(TESTE!$D$1:$D$121);SOMARPRODUTO(N((TESTE!$D$1:$D$121=$A20)*LIN(TESTE!$D$1:$D$121)=0))+[COLOR="Red"]COL()-14[/COLOR]));"")}

Devido à fórmula ser adicionada a partir da coluna O da planilha DADOS (décima quinta coluna), é utilizado [COL() - 14]. Como a função COL() retorna o número da coluna, o trecho destacado produziria, de acordo com o exemplo dado, o resultado abaixo.

COL() - 14 = 15 - 14 = 1

A função ÍNDICE simplesmente retorna a DATA da planilha TESTE, que encontra-se na linha retornada pela função MENOR. Abaixo, exemplo de resultados finais.

Célula O1 -> Primeira DATA que apresenta o CÓDIGO 28857/34 -> DATA da linha 2 -> 10/01/2013

Célula P1 -> Segunda DATA que apresenta o CÓDIGO 28857/34 -> DATA da linha 3 -> 11/01/2013

Célula Q1 -> Terceira DATA que apresenta o CÓDIGO 28857/34 -> DATA da linha 4 -> 12/01/2013