0217-Dica sobre fórmulas: “PROCV” trazendo colunas à esquerda

Bom dia, no artigo 0216-Dica sobre fórmulas: “PROCV” trazendo diferentes ocorrências vimos como é possível criar uma fórmula que permite trazer um resultado baseado num item a ser pesquisa e na ocorrência desejada.

Refletindo sobre o tema, avançamos um pouco mais e além da possibilidade de pesquisar um número de ocorrência específico, adicionamos a funcionalidade de estabelecer uma coluna qualquer para pesquisa e outra coluna qualquer para retorno. Com isso resolvemos uma outra limitação do PROCV que só consegue retornar os valores à direita da coluna pesquisada.

Partindo da fórmula anterior, definimos outra que necessita de 4 parâmetros para trazer um resultado:

  • O valor a ser pesquisado;
  • A coluna na qual será pesquisado;
  • O número da ocorrência desejada;
  • A coluna na qual está o valor a ser retornado.

0217_01

Passos executados:

  1. Pesquisar a posição da coluna a ser pesquisada no título da tabela;
  2. Determinar a posição em que ocorre a n-ésima ocorrência do valor pesquisado;
  3. Determinar a posição da coluna com o valor a ser retornado;
  4. Trazer o valor com base nas posições determinadas nos itens 2 e 3

Para poder tornar a fórmula mais facilmente interpretada e com maior flexibilidade de uso, recorremos a intervalos nomeados.

Pesquisar rngPesquisar =Exemplo!$H$1
Coluna Pesquisada rngColunaPesquisa =Exemplo!$H$2
Retornar ocorrência rngOcorrencia =Exemplo!$H$3
Coluna a retornar rngColunaRetorno =Exemplo!$H$4
Tabela com os dados tblBasePesquisa =Exemplo!$A$1:$E$13

A fórmula final, que assusta pelo tamanho, é essa aqui:

=DESLOC(tblBasePesquisa;
SOMARPRODUTO(MAIOR((DESLOC(tblBasePesquisa;0;
CORRESP(rngColunaPesquisa;DESLOC(tblBasePesquisa;0;0;1;
COLS(tblBasePesquisa));0)-1;LINS(tblBasePesquisa);1)=rngPesquisar)*
LIN(INDIRETO(“A1:A”&LINS(tblBasePesquisa)));
CONT.SE(DESLOC(tblBasePesquisa;0;
CORRESP(rngColunaPesquisa;
DESLOC(tblBasePesquisa;0;0;1;COLS(tblBasePesquisa));0)-1;
LINS(tblBasePesquisa);1);rngPesquisar)-rngOcorrencia+1))-1;
CORRESP(rngColunaRetorno;
DESLOC(tblBasePesquisa;0;0;1;COLS(tblBasePesquisa));0)-1;1;1)

Por isso vamos analisar seuss elementos.

DESLOC(tblBasePesquisa;0;0;1;COLS(tblBasePesquisa)) Referência à 1ª linha da tabela de dados
CORRESP(rngColunaPesquisa;
DESLOC(tblBasePesquisa;0;0;1;COLS(tblBasePesquisa));0)
Posição da coluna pesquisada
DESLOC(tblBasePesquisa;0;CORRESP(rngColunaPesquisa;
DESLOC(tblBasePesquisa;0;0;1;COLS(tblBasePesquisa));0)-1;
LINS(tblBasePesquisa);1)
Referência à coluna pesquisada
CONT.SE(DESLOC(tblBasePesquisa;0;
CORRESP(rngColunaPesquisa;
DESLOC(tblBasePesquisa;0;0;1;COLS(tblBasePesquisa));0)-1
;LINS(tblBasePesquisa);1);rngPesquisar)
Nº de ocorrências do valor pesquisado
SOMARPRODUTO(MAIOR((DESLOC(tblBasePesquisa;0;
CORRESP(rngColunaPesquisa;
DESLOC(tblBasePesquisa;0;0;1;COLS(tblBasePesquisa));0)-1;
LINS(tblBasePesquisa);1)=rngPesquisar)*
LIN(INDIRETO(“A1:A”&LINS(tblBasePesquisa)));
CONT.SE(DESLOC(tblBasePesquisa;0;
CORRESP(rngColunaPesquisa;
DESLOC(tblBasePesquisa;0;0;1;COLS(tblBasePesquisa));0)-1;
LINS(tblBasePesquisa);1);rngPesquisar)-rngOcorrencia+1))
Posição da n-ésima ocorrência
CORRESP(rngColunaRetorno;
DESLOC(tblBasePesquisa;0;0;1;COLS(tblBasePesquisa));0)
Posição da coluna de retorno

Mas a melhor forma de entender é utilizando, portanto baixe aqui o arquivo.

Anúncios

In Excel We Trust

Marcado com: , , , , ,
Publicado em Nível: Avançado

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Visitas dos usuários
  • 2,077,129

Clique para assinar este blog e receber notificações de novos artigos por email.

Junte-se a 1.537 outros seguidores

Aumente sua interação

Siga-nos do twitter (@usuariosdoexcel).

Acesse o nosso fórum e compartilhe as suas dúvidas.

Curta nossa página no Facebook.

Junte-se ao grupo do LinkedIn.

Visite-nos utilizando o seu smartphone ou tablet.

Recomende este Blog
Visualizar notícias
Site monitorado:
Real Time Web Analytics

Clicky

%d blogueiros gostam disto: