domingo, 24 de março de 2013

Dr. Planilha: Como retornar o endereço da célula de um valor procurado no #Excel

Este artigo foi publicado com o objetivo de responder uma pergunta postada no Yahoo Respostas:
"Na planilha há valores que não se repetem, por exemplo 62, que está em D15. Que fórmula eu posso usar para que me diga em F1 que esse valor está na célula D15? Ou seja, sei que lá na "caixa de nome" do EXCEL já aparece essa informação, mas quero que apareça em F1 o valor "D15". É isso mesmo, "D15" em forma de texto mesmo. Alguém sabe? Pra quem tá curioso, pretendo usar o comando INDIRETO em uma outra fórmula que fiz. Grato."  
Fonte: Yahoo Respostas

Para resolver este problema usarei duas funções ainda não descritas no blog: ENDEREÇO() e CORRESP().

Antes de começar, vejamos uma definição rápida do objetivo de cada uma dessas funções.

CORRESP(): Procura valores em uma referência ou em uma matriz.
ENDEREÇO(): Retorna uma referência como texto para uma única célula em uma planilha.

Sem mais delongas, vamos ao passo-a-passo para resolver a questão:

PASSO 1: Preparar a planilha
1. Ao invés de usar uma lista de números, como é o caso proposto na pergunta, usarei uma lista de letras, para mostrar que a solução se aplica em ambas as situações. Para isso, criei uma lista de valores de A a H na coluna D da minha planilha.

2. Adicionaremos ainda uma célula para informar o valor que será pesquisado na coluna D e outro onde mostraremos o endereço. O resultado final será mais ou menos como mostrado na figura abaixo:

PASSO 2: Elaborar a fórmula de pesquisa
1. Na célula B2 iremos inserir a seguinte fórmula:

=ENDEREÇO(CORRESP(B1;D1:D1000;0);4;4)

Vamos dissecar essa fórmula para compreender melhor a solução.

 CORRESP(B1;D1:D1000;0)
  • O primeiro argumento da função (B1) é o valor que queremos pesquisar em nossa matriz. 
  • O segundo argumento (D1:D1000) é o intervalo onde a pesquisa será realizada. Observe que apesar de a lista verdadeiramente só começar a partir da linha 2, iniciei o intervalo na linha 1, pois caso contrário, a função retornaria a linha incorreta da planilha (a está na linha 2 da planilha, mas na linha 1 do intervalo, se ele começar na linha 2). Outro detalhe aqui é que o intervalo foi definido até 1000 linhas (D1000), isso porque estou prevendo que essa lista pode vir a ser incrementada, então usamos um intervalo maior para garantir que nossa função não vá ficar defasada quando novos valores forem inseridos.
  • O terceiro argumento (0) tem o objetivo de determinar que a função buscará valores exatos e não aproximados.

ENDEREÇO(CORRESP(B1;D1:D1000;0);4;4)
  • O primeiro argumento é resultado da função CORRESP() e ele indica em que linha da planilha está nosso valor.
  • O segundo argumento é o número 4, que na verdade representa a coluna D (4ª coluna da planilha). Se os valores estivessem em outra coluna, a C, por exemplo, usaríamos o valor 3 (3ª coluna da planilha).
  • O terceiro argumento é o número 4, determinando que o endereço devolvido será completamente relativo. As outras opções seriam as seguintes: 1 ou omitido (absoluta, ex: $A$1); 2 (linha absoluta e coluna relativa, ex: A$1); 3 (linha relativa e coluna absoluta, ex: $A1) e 4 (relativa, ex: A1).

Desta forma, se informarmos na célula B1 o valor D (ou d), obteremos o seguinte resultado:


Para facilitar a compreensão, compartilho abaixo a planilha usada neste artigo:

Excel 2003
Excel 2007/2010

Um grande abraço do Dr. Planilha!

Related Posts Plugin for WordPress, Blogger...