Função INDIRETO

Quando utilizamos as fórmulas e funções do Excel, na grande maioria das vezes fazemos referências a células e intervalos de forma direta, ou seja, fazemos fórmulas que indicam diretamente quais são as células envolvidas no cálculo, como por exemplo =SOMA(A1:A10) ou =Planilha1!A1 onde no primeiro exemplo estamos fazendo uma soma das células de A1 a A10, e no segundo exemplo estamos buscando o dado presente da célula A1 de uma outra aba chamada Planilha1.

Mas existem inúmeros casos em que precisamos fazer uma referência indireta a um intervalo, e acredite em mim quando eu digo, essa necessidade é REAL, veja os seguintes exemplos onde usar a função INDIRETO é crucial.

• Buscar um dado de uma célula que pode não sempre ser a mesma;
• Criar uma lista de seleção que muda de tamanho.
• Montar uma fórmula que busque dados de uma aba ou outra aba, dependendo de dados em outras células. Ou seja buscar dados de outras abas dinamicamente;
• Montar fórmulas que sempre se referenciam a mesma célula, mesmo que a célula seja excluída, ou que haja inclusão de linhas ou colunas. (Não, congelar intervalo não resolve aqui);
• Converter letras em números e;
• Uma infinidade de outras possibilidades que aí… deixo pra você pensar 😛

Nesse artigo veremos algumas utilizações da função INDIRETO

Vídeo sobre a função INDIRETO

Todos os exemplos deste artigo são apresentados também em vídeo, caso prefira ver assim.

Coloque o vídeo em tela cheia para assistir normalmente

Entendendo a função INDIRETO

Aqui vão uma série de exemplos para explicar a diferença que faz utilizar essa função.

A imagem acima mostra uma tabela que exibe as fórmulas presente. Na primeira coluna são referências diretas (ou quase isso), para células da coluna D, enquanto que na segunda coluna, são referências indiretas, iguais as da primeira coluna.

Veja agora a diferença de resultado que cada uma delas apresenta.

Note que:

Fórmula	Referência Direta	Referência Indireta
1ª	Retorna o valor contido dentro de D3.	Retorna o valor contido dentro de E3, já que o que está contido em D3 é uma referência a célula E3.
2ª	Retorna apenas um texto escrito D4, não faz referência nenhuma.	Retorna o valor contido em D4 com a vantagem de que mesmo que D4 seja deletado, excluído ou arrastado, ele sempre se referencia-rá a D4.
3ª	Retorna a letra D junto com o resultado da função CONT.VALORES, finalizando com D3.	Retorna o valor contido dentro da célula D3, já que o resultado da função CONT.VALORES é 3.

Sendo assim, conseguimos ver a função INDIRETO apresenta vantagens e pode ser muito útil em determinadas situações, vejamos mais delas a seguir.

Caixa de seleção dinâmica

Já fizemos um outro post onde falamos sobre caixa de seleção com a função INDIRETA para os casos onde queremos retirar da caixa de seleção valores que já foram utilizados anteriormente em outras caixas de seleção, você pode ler sobre clicando aqui.

Neste exemplo queremos montar uma caixa de seleção que pode mudar de tamanho, conter mais ou menos dados de acordo com a lista de dados na quase se baseia.

A primeira coisa que faremos é uma fórmula que avalia qual é o tamanho da área conforme são incluídos/excluídos itens dela.

Levando em consideração que o nome da nossa aba atual seja Planilha1 faremos a seguinte fórmula é montada na célula D3, resultando conforme a imagem.

="'Planilha1'!B7:B"&CONT.VALORES(B:B)+1

Para entender, colocamos o nome da planilha entre aspas duplas, junto com aspas simples e o ponto de exclamação, isso porque é dessa forma que o Excel se referencia a outra abas do mesmo arquivo. As aspas duplas englobando tudo servem para considerar tudo como um texto e não como uma referência real (direta). E então combinamos este texto com a função CONT.VALORES que conta quantos itens existem na coluna B, sendo o +1 no final apanas para compensar que os dados começam na linha 2, pulando a primeira linha da tabela.

Em seguida, na célula F3, inserimos uma caixa de validação de dados, no formato lista onde a fórmula utilizada lá dentro é:

=INDIRETO(D3)

Caso você não saiba como criar uma caixa de validação de dados (caixa de seleção de dados) recomendo este link aqui.

Referência dinâmica a outras abas

Neste caso temos 3 abas Jan, Fev e Mar, com a seguinte aparência:

Cada um com números diferentes, obviamente, e queremos em uma tabela buscar estes 3 dados para estes 3 meses, mas com uma fórmula que sabe de qual aba deve buscar, automaticamente. Utilizaremos ainda um PROCV para isso.

Na tabela da imagem abaixo, na célula C3 inserimos a seguinte fórmula

=PROCV(C$2;INDIRETO($B3&"!$B$2:$C$4");2;FALSO)

O PROCV que montamos é um PROCV normal, buscando dado de uma outra aba, porem, o argumento matriz_tabela da função PROCV, a matriz onde os dados são procurados, substituímos por uma função indireto, que altera essa matriz de acordo com a célula em que a fórmula está inserida na tabela da imagem anterior.

De forma que na célula C3 a função INDIRETO funciona assim:

INDIRETO($B3&“!$B$2:$C$4”)

$B3 = Referência a célula que contém o nome da planilha onde deve buscar o dado

“!$B$2:$C$4” = Uma referência fixa, em forma de texto, já que em todas as 3 abas a área de busca é a mesma.

& = Apenas para concatenar (unir) as duas partes.

O resultado dessa função INDIRETO quando na célula C3 é:

Jan!$B$2:$C$4″

Assim podemos arrastar a células para as demais células e todos os dados serão buscados de acordo e da planilha (aba) correspondente.

Converter colunas (letras) em números

Não tão corriqueiro quanto os demais exemplos dados aqui, mas certamente interessante, é a possibilidade de converter o nome de colunas do Excel, ou seja, letras, e números.

Na imagem que segue, na segunda coluna inserimos a seguinte fórmula:

=COL(INDIRETO(B3&"1"))

Onde a função INDIRETO faz uma referência a primeira célula da coluna cujo nome está ao lado, ou seja, para a célula C3 ela se referencia a célula A1.

Já a função COL, retorna qual é o número da coluna da célula informada pela função INDIRETO. No caso da fórmula na célula C3, é a primeiro coluna, pois a coluna da célula A1 é a primeira coluna, assim o resultado da fórmula é 1.

Referência dinâmica a tabelas

Para este último exemplo, criamos 3 tabelas (Menu > Inserir > Tabela) e as nomeamos de Janeiro, Fevereiro e Março, para renomear uma tabela selecione-a e no menu principal do Excel vá para Design, e no topo à esquerda você verá o nome da tabela e poderá renomeá-la. Além de nomear as tabelas também nos certificamos de que todas elas contém uma coluna cujo cabeçalho é Valores.

Feito isso, criamos uma tabelinha com o nome dessas 3 tabelas e incluímos a seguinte função na célula L3:

=SOMA(INDIRETO(K3&"[Valores]"))

Onde [Valores] é uma referência a coluna valores de cada tabela.

Funciona assim:

INDIRETO(K3&”[Valores]“)

K3 = Nome da tabela, neste caso Janeiro

[Valores] = Nome da coluna

O resultado da função INDIRETO é: Janeiro[Valores]

E assim a função SOMA que envolve a função INDIRETO faz a soma de todos os itens da coluna Valores. Basta agora arrastar a fórmula para baixo para buscar o mesmo das demais tabelas.