Função Indireto – Como usar a Fórmula INDIRETO para Buscar Dados Dinamicamente?
Nessa aula eu vou te mostrar como usar a função INDIRETO no Excel que poucas pessoas sabem utilizar com exemplo prático!
A função INDIRETO transforma um texto em referência de célula. Escrevendo =INDIRETO(C2&"!C:C"), o Excel lê o nome da aba que está em C2 e devolve a coluna C daquela aba: você troca a origem dos dados mudando uma célula, sem editar a fórmula. Vale lembrar que ela é uma função volátil, ou seja, recalcula a cada alteração na planilha.
Caso prefira o conteúdo de Indireto no formato de vídeo-aula, assista o vídeo abaixo!
O que você aprende neste vídeo
Resposta rápida: A função INDIRETO transforma texto em referência, o que permite trocar a aba consultada sem editar a fórmula. Em =SOMASES(INDIRETO(C2&"!C:C");INDIRETO(C2&"!B:B");B4), o mês digitado na célula C2 decide de onde vêm os dados somados. Por ser volátil, ela recalcula a cada alteração feita na planilha.
Neste vídeo (9 min):
- 0:45 - abre uma planilha financeira com abas de janeiro, fevereiro, março e resumo
- 1:35 - define o objetivo: somar na aba resumo cada tipo de gasto do mês
- 2:05 - monta o
SOMASEScom soma na coluna C e critério na coluna B - 3:25 - mostra o problema: trocar o mês na célula C2 não altera os valores
- 4:10 - apresenta o
INDIRETOcomo forma de trocar a aba automaticamente - 4:55 - revela a sintaxe
Janeiro!C:Cque o Excel monta por trás do intervalo - 5:30 - reescreve o
SOMASESapontando o intervalo para a célula C2 - 6:05 - concatena célula e texto com
&, formandoC2&"!C:C" - 6:40 - copia a estrutura para o critério, trocando
C:CporB:B - 7:20 - fecha os parênteses de cada
INDIRETOdentro doSOMASES - 7:45 - trava a célula C2 antes de arrastar a fórmula para baixo
- 8:10 - troca o mês e vê o resumo inteiro recalcular sozinho
Trechos do vídeo:
- O
INDIRETOolha o valor que está na célula C2 e escreve esse valor dentro doSOMASES. Janeiro!significa que estou na aba janeiro, eC:Csignifica que estou olhando para a coluna C.- Para concatenar informações no Excel eu uso o caractere
&, que junta as informações. - Textos dentro de uma fórmula devem estar entre aspas, por isso o
!C:Cfica entre aspas.
O que é a Função INDIRETO?
A fórmula INDIRETO é uma fórmula que retorna uma referência especificada por um texto. No caso que vamos ver iremos mudar a referência de uma aba utilizando esta fórmula, ou seja, para que não precisemos sempre alterar a fórmula quando quisermos uma referência diferente podemos utilizar a fórmula INDIRETO.
Quando Usar a Fórmula INDIRETO?
Vamos utilizar a fórmula INDIRETO quando quisermos alterar a referência de uma fórmula sem alterar a fórmula. No caso que vamos ver temos a referência de abas que tem nomes dos três primeiros meses do ano: Janeiro, Fevereiro e Março.
E na fórmula quando queremos referenciar a coluna C da aba Janeiro a referência é Janeiro!C:C, pois o nome da aba é Janeiro e queremos a coluna C, então se quisermos mudar a referência para Fevereiro teríamos que ir na fórmula e alterar o nome do mês.
Então para isso utilizamos a fórmula INDIRETO para que essa substituição seja mais fácil, então podemos referenciar uma célula que contenha os meses utilizando a fórmula indireto.
Por exemplo, temos o mês na célula C2, então se quisermos mudar de Fevereiro para Março, basta colocar Março na célula C2 e dentro na nossa fórmula original iremos trocar a referência da aba por INDIRETO(C2&”!C:C”), desta forma pegaremos o conteúdo da fórmula C2 e iremos juntar (utilizando o &) com o resto da referência que está especificando a coluna C.
Então, desta forma o Excel entende que estamos referenciando outra aba sem ter que alterar a fórmula, o que deixaria essa atividade mais trabalhosa. Desta forma, temos uma forma mais rápida e dinâmica de trocar a referência do mês alterando apenas em uma célula fora da fórmula.
Excel ImpressionadorTudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.
Começar agora
Como Usar a Função INDIRETO?
Primeiramente vamos construir nossa tabela de dados para que possamos seguir aos passos de como utilizar essa fórmula.
Para isso teremos 4 abas: Janeiro, Fevereiro, Março e Resumo. As três primeiras abas vão conter três colunas com data, tipo e valor de cada ação daquele respectivo mês, enquanto a aba de resumo vai conter um resumo dessas atividades para cada um dos meses.
Para o mês de Janeiro temos a seguinte tabela:
Para o mês de Fevereiro temos a seguinte tabela:
Para o mês de Março temos a seguinte tabela:
Por fim temos a tabela para a aba Resumo:
Agora vamos preencher a coluna C da aba de Resumo. Para isso iremos utilizar a fórmula SOMASES, que funciona igual a fórmula SOMASE, no entanto podemos colocar mais argumentos.
Temos que os argumentos são o intervalo_soma, intervalo_critérios1 e critérios1.
- Intervalo_soma – É o intervalo que será somado, ou seja, onde temos os valores, então iremos selecionar a coluna C da aba Janeiro.
- Intervalo_critérios1 – É o intervalo onde iremos procurar o nosso critério, neste caso iremos selecionar a coluna B também da nossa aba Janeiro.
- Critérios1 – Por fim é critério que será procurado no intervalo de critérios, neste caso como estamos preenchendo a tabela na aba Resumo iremos selecionar o primeiro item do mês que é Água, que está na célula B4.
Então a fórmula final ficará da seguinte forma:
Feito isso podemos selecionar a célula C4 e arrastar até a célula C11 para copiar a fórmula para todas as células da tabela.
Assim podemos observar que temos todos os gastos do mês de Janeiro somados, mesmo sabendo que neste mesmo mês temos duas ocorrências de Lazer, podemos observar que temos a soma dessas duas ocorrências.
No entanto, se quisermos saber o resumo de cada mês não basta apenas modificar o nome do mês, pois a fórmula irá continuar a mesma. Então, para resolver este problema que iremos utilizar a fórmula INDIRETO, para que possamos substituir o texto de dentro da fórmula, pelo texto do mês que queremos que é exatamente o nome de cada aba que temos na nossa planilha.
Antes de construirmos uma nova coluna utilizando essa fórmula precisamos saber qual o argumento desta fórmula. Ela tem apenas um que é o texto que queremos, ou seja, ela vai pegar uma célula que contém um texto e escrever ele.
Então vamos construir a nova coluna utilizando esta fórmula para pegar as referências das abas baseadas no texto que temos do mês selecionado.
A nossa nova fórmula vai ficar neste formato. Então iremos colocar a função INDIRETO para substituir o texto de referência das abas dos meses. Para isso escrevemos a função INDIRETO, colocamos a referência do mês que é a célula C2 e fazemos uma concatenação com o símbolo &, ou seja, iremos juntar esse texto com !C:C que é a referência que o Excel dá para a coluna C.
Então, essa primeira parte é para representar nosso intervalo de soma, que irá pegar a coluna C do mês selecionado na célula C2, faremos o mesmo para o intervalo de critérios trocando apenas o C por B, para que possamos pegar a coluna B de qualquer uma dessas abas, que possuem os tipos de gastos e, por fim, colocamos a célula B4 que é o nosso critério.
OBS: Temos que trancar as células C2 dentro da fórmula para quando arrastarmos a fórmula para baixo não perdermos a referência do mês que está na célula C2. Para isso, basta clicar em cima da referência no texto e pressionar F4 ou colocar $ antes da linha e da coluna, deixando desta forma: $C$2.
Então, ao arrastar temos os mesmos valores conforme nossa conta anterior, no entanto se modificarmos o mês para Fevereiro, por exemplo, podemos notar que os valores da coluna C não serão alterados.
Isso acontece porque a referência da fórmula na coluna C é apenas para a aba de Janeiro, então independente do mês que escolhermos a fórmula irá retornar o mesmo valor, por isso utilizamos a função INDIRETO, para quando mudarmos o mês o Excel consiga pegar a referência correta da aba que queremos.
Se você ainda tem dúvidas ou quer aprofundar um pouco mais seu conhecimento sobre a função INDIRETO vou deixar aqui mais dois vídeos com outros exemplos!
Função INDIRETO para Buscar Informações em Abas Diferentes
Saia do Zero Absoluto em Função INDIRETO
Conclusão da Função INDIRETO no Excel
Nessa aula eu te mostrei como você pode utilizar a função INDIRETO no Excel com alguns exemplos práticos para facilitar o entendimento.
Essa é uma função que muitas pessoas não entendem e muitos nem sabem que ela existe, mas depois dessa aula você já vai poder começar a utilizar e ter mais conhecimento para se tornar um diferencial!
E não se esqueça de assistir os outros dois vídeos para que você consiga de fato absorver tudo o que precisa sobre essa função!
Perguntas frequentes
1. Para que serve a função INDIRETO no Excel?
Ela converte um texto em referência. Serve para montar fórmulas que apontam para abas, colunas ou intervalos diferentes conforme o valor de uma célula, sem reescrever a fórmula.
2. Como usar o INDIRETO para buscar dados em várias abas?
Coloque o nome da aba em uma célula (C2, por exemplo) e monte a referência assim: INDIRETO(C2&”!C:C”). Trave a célula com F4, deixando =INDIRETO($C$2&”!C:C”), antes de arrastar a fórmula para baixo.
3. O INDIRETO funciona com outra pasta de trabalho fechada?
Não. A referência só é resolvida com o arquivo aberto; se a pasta de trabalho estiver fechada, o INDIRETO retorna #REF!.
4. Quando é melhor usar ÍNDICE e CORRESP no lugar do INDIRETO?
Quando a referência não precisa mudar de aba. Como o INDIRETO é volátil e recalcula o tempo todo, em bases grandes o ÍNDICE com CORRESP (ou o PROCX) deixa a planilha mais leve.
Hashtag Treinamentos
Para acessar outras publicações de Excel Avançado, clique aqui!
Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?
