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órmula:
B1 = PesoB2 = Al…

Dr. Planilha: Como Colar Valores Horizontais na Vertical e Vice-Versa no #Excel

Olá pessoal!

Trouxe uma dica rápida para vocês que ajuda muito no dia-a-dia e sei que muitas pessoas a desconhecem: colar valores com transposição da horizontal para a vertical ou da vertical para a horizontal.

Para explicar, vamos a um exemplo comum do quotidiano de quem trabalha com planilhas:

O departamento financeiro fornece à área de marketing um relatório com o valor total das vendas por vendedor, distribuído da seguinte forma:

O problema é que no relatório da área de marketing essas informações não estão distribuídas em colunas, mas sim, em linhas. Como resolver isso com o menor trabalho possível? Veremos a seguir.