Blog

Postado em em 20 de março de 2019

Função Indireto – Como usar a fórmula indireto para buscar dados dinamicamente?

Caso prefira o conteúdo de Indireto no formato de vídeo-aula clique nesse link!

O que é a fórmula 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.

Como usar a fórmula 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órmulas 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 está 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 fórmula INDIRETO para substituir o texto de referência das abas dos meses. Para isso escrevemos a fórmula 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 fórmula INDIRETO, para quando mudarmos o mês o Excel consiga pegar a referência correta da aba que queremos.

Para saber mais sobre Indireto e outros assuntos de Excel e VBA, acompanhe que vamos enviar outros posts.