Considerada uma das funções mais peculiares do Excel, a função INDIRETO, como o próprio nome sugere, retorna uma referência indireta. Basicamente, seu papel é transformar seus argumentos em uma referência, seja ela uma única célula, um intervalo, um nome definido ou uma referência a outra planilha.
Neste artigo vamos aprender a sintaxe da função INDIRETO, como ela funciona e também veremos alguns exemplos práticos onde ela pode ser aplicada, buscando dados automaticamente de outras planilhas de uma pasta de trabalho. Além disso, veremos a como usar a função INDIRETO em conjunto com outras funções (como a CONT.SES, em nosso caso) para facilitar cálculos que dependam da análise de várias planilhas simultaneamente.
Entendendo a função INDIRETO
Sempre que referenciamos uma célula no Excel, o programa mostra seu conteúdo. Por exemplo, se a célula A1 contiver o termo “Cenoura”, em qualquer célula que digitarmos =A1 (uma referência ao conteúdo de A1), obteremos o termo “Cenoura”.
O papel da função INDIRETO é fazer um processo mais ou menos inverso. Usamos um texto (seja ele fixo ou contido em outra célula) que servirá de referência. Consequentemente, o resultado dessa referência é que será exibido na célula.
Vamos para um exemplo bem simples. Observe a sintaxe abaixo:
=INDIRETO(“D3”)
Ela foi inserida na célula D5 da planilha abaixo.
Que resultado você acha que essa fórmula retornará? Exatamente! O resultado contido na célula D3, que é a palavra SALDO.
Sob esse ângulo, parece ser uma função sem muitos propósitos. No entanto, note bem como usamos o argumento para a referência: dentro de aspas, o que configura, para o Excel, um texto. Ou seja, o simples texto D3 foi transformado em referência à célula D3.
Isso fica ainda mais interessante quando dividimos os “pedaços que podem formar uma referência”. Observe agora o exemplo a seguir.
Aqui não temos uma referência em forma de texto, mas a junção do conteúdo de duas células (B5 e B6) que formará um texto, texto esse que virará uma referência.
Quando usamos a função INDIRETO, de parâmetro obrigatório único, podemos concatenar (juntar) as informações que formarão uma referência usando o sinal do E comercial (&).
Note que juntaremos, nesse caso, os termos “D” e “3”, formando, assim, o texto “D3”, que será lido pelo Excel como uma referência: D3. Obteremos então o mesmo resultado, mas agora usando uma referência gerada a partir do conteúdo de duas células.
Agora sim é que entra a questão mais interessante, e que te fará abrir os olhos para as possibilidades dessa função. E se trocarmos a letra D pela B?
O resultado será alterado, porque a referência gerada é outra. Agora estamos buscando o conteúdo da célula B3. Isso nos permite, por exemplo, gerar resultados diferentes de acordo com uma referência diferente de forma automatizada, muito útil quando precisamos buscar dados de planilhas diferentes (porém similarmente estruturadas) sem que precisemos referenciar cada uma de modo manual.
Buscando dados em planilhas diferentes com a função INDIRETO
A planilha a seguir exibe um relatório de dados obtidos a partir dos resultados de cada mês. No entanto, o resultado de cada mês encontra-se em sua respectiva planilha no arquivo. Nesse caso, temos 4 planilhas para os quatro primeiros meses do ano (JAN, FEV, MAR e ABR).
Apenas uma única fórmula foi usada para buscar os dados de janeiro, sendo os dados dos demais meses obtidos através de autopreenchimento, sem que fossem realizadas referências individuais a cada uma das planilhas.
Como a função INDIRETO transforma um texto em referência, basicamente o que precisamos é fazer com que ela reconheça os termos inseridos na coluna “Mês” como referências às planilhas de mesmo nome, agregados às células nessas planilhas que contêm os dados que queremos retornar.
O primeiro passo é notar, na planilha que contém os dados que queremos buscar, onde está a informação a ser retornada. Em nosso exemplo, desejamos mostrar o total de ocorrências, informação presente na célula F1 da planilha JAN.
Com isso em mente, precisamos entender como funciona a referência entre planilhas diferentes em um mesmo arquivo. Dentro da minha planilha RELATÓRIO, caso eu queira buscar o conteúdo da célula F1 da planilha JAN, preciso usar a seguinte fórmula de referência:
=JAN!F1
Isso retornará o dado da célula F1 da planilha JAN. É muito importante notar a necessidade o uso do ponto de exclamação (!) como separador do nome da planilha e da célula referente a ela.
É isso que precisamos indicar à função INDIRETO. No entanto, a ideia aqui é fazer com que o nome da planilha seja modificado de acordo com a lista contida na coluna de meses, para que, aplicando o autopreenchimento, o Excel troque as referências: de JAN para FEV, depois para MAR, e assim por diante. É nisso que a função nos auxiliará.
=INDIRETO(A4&“!F1”)
A função colherá a informação contida em A4 (JAN) e a concatenará (pois usamos o sinal de concatenação) com o texto fixo “!F1″, que se refere à célula onde está contida nossa informação. Assim, o resultado dentro do argumento da função INDIRETO será o termo “JAN!F1”. Transformando isso em referência, o Excel buscará o valor contido nessa localização.
Aplicando o autopreenchimento, o Excel deslocará as próximas células de referência. Sendo assim, A5 se transformará em FEV, A6 se transformará em MAR, e assim por diante, que são justamente os nomes de nossas planilhas mensais.
É interessante observar que a célula F1 não precisa ser travada por referência absoluta, já que a inserimos em forma de texto. Sendo texto fixo na fórmula, ela sempre será fixa após ser concatenada com o primeiro item.
Os nomes contidos na coluna dos meses são exatamente os mesmos nomes das planilhas de nossa pasta de trabalho, por isso nossa referência funciona. É importante que esses nomes sejam idênticos.
Como não temos as demais planilhas (de maio em diante), o Excel retornará um erro #REF!, indicando que a referência não foi encontrada. Assim que criarmos novas planilhas com esses nomes, essas referências serão corrigidas e exibidas normalmente. Entretanto, se você preferir corrigir a exibição desses erros para planilhas que ainda não foram criadas, pode usar a função INDIRETO dentro da função SEERRO, que você aprende como usar conferindo este artigo.
Todas as planilhas dos meses, apesar de possuírem dados diferentes para cada mês, são estruturalmente similares, ou seja, os dados a que estamos buscando sempre se encontram na célula F1 de cada uma delas. Por isso a função INDIRETO é uma ótima alternativa no caso de planilhas de estruturalmente idênticas.
Agora não há muito segredo. O número de resoluções se encontra na célula H1 de cada planilha, portanto, apenas mudaremos essa referência de texto na fórmula que buscará nosso segundo dado.
Alternadamente, em vez de buscar todos os dados de todas as planilhas simultaneamente, você pode usar uma lista suspensa para selecionar uma planilha específica. A fórmula permanece a mesma, no entanto aplicada apenas a uma única célula, já que o conteúdo dela será atualizado conforme a escolha da opção da lista.
Para aprender a como criar uma lista suspensa no Excel, confira este artigo.
Usando a função INDIRETO com outras funções
Dada a utilidade da função INDIRETO, podemos usá-la para criar referências indiretas (e dinâmicas, naturalmente) dentro de outras funções.
Vamos continuar usando o mesmo exemplo anterior. Observe os dados que possuímos em cada planilha, de cada mês: uma coluna com todas as ocorrências de cada dia, uma coluna com todas as ocorrências que foram resolvidas e duas células com o resumo mensal: total de ocorrências e total de resoluções.
No entanto, imaginemos que nosso propósito seja contar a quantidade de dias de cada mês em que não houve nenhuma ocorrência. Esse dado não consta em nosso resumo, portanto não podemos referenciá-lo pela função INDIRETO como fizemos anteriormente. Mas podemos usá-la para gerar a referência de cada mês dentro de uma função que fará isso por nós: a função CONT.SES. Ela conta a quantidade de ocorrências de um determinado critério em um intervalo.
Observe como isso se aplica no nosso exemplo.
=CONT.SES(INDIRETO(A4&”!B:B”);”0″)
O primeiro critério da CONT.SES é o intervalo de análise (onde estão os dados a serem analisados). É justamente aí que entra a função INDIRETO, pois alteraremos esse intervalo de acordo com o mês escolhido. No entanto, como nosso intuito é analisar em quantos dias não houve nenhuma ocorrência, alteramos a referência de célula para uma referência de intervalo, que, nesse caso, é a coluna B inteira (“!B:B”). Trocando em miúdos, o Excel analisará toda a coluna B da planilha cujo nome está contido na célula A4 (JAN). Sendo assim, nossa referência será JAN!B:B. Esse é o intervalo de critérios da função CONT.SES.
O segundo argumento da função é o critério que deverá ser analisado nesse intervalo. Em nosso exemplo, procuraremos pelo número zero (“0”). O Excel contará quantas vezes o número zero aparece dentro da coluna B da planilha em questão. O resultado para a planilha JAN é 6, mas é alterado conforme usamos o autopreenchimento, visto que a planilha de referência também muda (mas não a coluna dela).
- É importante que os nomes a serem usados como referências a planilhas sejam idênticos aos das planilhas em si, respeitando, inclusive, acentos e espaçamentos, mas não há necessidade de se diferenciar maiúsculas de minúsculas.
