Pular para o conteúdo principal

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!

Comentários

Postagens mais visitadas deste blog

Dr. Planilha: Como Remover Linhas de Grade no #Excel #dica

As linhas de grade do Excel não aparecerão em relatórios impressos, mas pode ser que você também deseje que elas fiquem ocultas quando estiver trabalhando com a planilha por uma questão de estética ou Antes de mais nada é importante ainda que se diga que "linha de grade" não é a mesma coisa que "borda".

Dr. Planilha: Como Fazer Cálculo do IMC no #Excel

Antes de mais nada, vamos entender melhor o que é IMC, emprestando a definição da wikipédia: O índice de massa corporal (IMC) é uma medida internacional usada para calcular se uma pessoa está no peso ideal. Ele foi desenvolvido pelo polímata Lambert Quételet no fim do século XIX. Trata-se de um método fácil e rápido para a avaliação do nível de gordura de cada pessoa, ou seja, é um preditor internacional de obesidade adotado pela Organização Mundial da Saúde (OMS). A fórmula para calcular o IMC é bastante simples, vamos precisar apenas de duas informações, seu Peso em quilogramas e sua Altura em metros. O cálculo consiste em dividir seu peso pela sua altura ao quadrado. Vejamos como ficaria: Ora, isso parece bastante fácil de se fazer no Excel, e de fato é. Vamos à prática! Para começar, faça uma planilha igual à do exemplo abaixo. Agora preencheremos a fórmula para o cálculo na célula B3, ela deve ficar da seguinte forma: =B1/B2^2 Para entender entender melhor a fórm

Dr. Planilha: Como tirar pontos e traços do CPF no #Excel

Um leitor do blog postou uma dúvida recentemente sobre formatação de números de CPF. Na verdade, ele desejava remover todos os caracteres diferentes de números, no caso pontos e hífen, mantendo apenas os números. Segue a pergunta: "A minha planilha tem uma coluna de números de CPF. Gostaria de tirar os pontos e o traço de todos os números da coluna. Como faço isso?" Costumeiramente eu solucionaria essa necessidade usando uma fórmula para substituir os caracteres "." e "-" por <vazio>, o que resulta em uma sequência de apenas números. A seguir descrevo como ficaria essa fórmula.