Conteúdo
À primeira vista (especialmente ao ler a ajuda), a função INDIRETO (INDIRETO) parece simples e até desnecessário. Sua essência é transformar o texto que parece um link em um link completo. Aqueles. se precisarmos nos referir à célula A1, podemos fazer um link direto habitualmente (insira um sinal de igual em D1, clique em A1 e pressione Enter), ou podemos usar INDIRETO para o mesmo fim:
Observe que o argumento da função – uma referência a A1 – é inserido entre aspas, de modo que, na verdade, é texto aqui.
"Bem, tudo bem", você diz. “E qual é o benefício?”
Mas não julgue pela primeira impressão – é enganoso. Esse recurso pode ajudá-lo em muitas situações.
Exemplo 1. Transpor
Um clássico do gênero: você precisa girar o diâmetro vertical
ranhura para horizontal (transposição). Claro, você pode usar uma inserção ou função especial TRANSP (TRANSPOR) em uma fórmula de matriz, mas você pode se virar com nosso INDIRETO:
A lógica é simples: para obter o endereço da próxima célula, colamos a letra “A” com o caractere especial “&” e o número da coluna da célula atual, que a função nos fornece COLUNA (COLUNA).
O procedimento inverso é melhor feito um pouco diferente. Como desta vez precisamos formar um link para as células B2, C2, D2, etc., é mais conveniente usar o modo de link R1C1 em vez do clássico “batalha marítima”. Neste modo, nossas células diferirão apenas no número da coluna: B2=R1C2, C2=R1C3, D2=R1C4 etc.
É aqui que entra o segundo argumento opcional da função. INDIRETO. Se for igual DEITADO (FALSO), então você pode definir o endereço do link no modo R1C1. Assim, podemos facilmente transpor o intervalo horizontal de volta para vertical:
Exemplo 2. Soma por intervalo
Já analisamos uma forma de somar em uma janela (intervalo) de um determinado tamanho em uma folha usando a função DISPOSIÇÃO (DESLOCAMENTO). Um problema semelhante também pode ser resolvido usando INDIRETO. Se precisarmos resumir dados apenas de um determinado período de intervalo, podemos colá-los a partir de pedaços e transformá-los em um link completo, que podemos inserir dentro da função SOMA (SOMA):
Exemplo 3. Lista suspensa de tabela inteligente
Às vezes, o Microsoft Excel não trata nomes e colunas de tabelas inteligentes como links completos. Assim, por exemplo, ao tentar criar uma lista suspensa (aba Dados - Validação de dados) com base na coluna Colaboradores da mesa inteligente Pessoas teremos um erro:
Se “empacotarmos” o link com nossa função INDIRETO, o Excel o aceitará facilmente e nossa lista suspensa será atualizada dinamicamente ao adicionar novos funcionários ao final da tabela inteligente:
Exemplo 4. Links inquebráveis
Como você sabe, o Excel corrige automaticamente os endereços de referência em fórmulas ao inserir ou excluir colunas de linha em uma planilha. Na maioria dos casos, isso é correto e conveniente, mas nem sempre. Digamos que precisamos transferir os nomes do diretório de funcionários para o relatório:
Se você colocar links regulares (digite =B2 na primeira célula verde e copie-o), quando você excluir, por exemplo, Dasha, obteremos o #LINK! erro na célula verde correspondente a ela. (#REF!). No caso de usar a função para criar links INDIRETO não haverá esse problema.
Exemplo 5: coletando dados de várias planilhas
Suponha que temos 5 folhas com relatórios do mesmo tipo de funcionários diferentes (Mikhail, Elena, Ivan, Sergey, Dmitry):
Vamos supor que a forma, tamanho, posição e sequência de mercadorias e meses em todas as tabelas sejam iguais – apenas os números diferem.
Você pode coletar dados de todas as planilhas (não resumir, mas colocá-los um embaixo do outro em uma “pilha”) com apenas uma fórmula:
Como você pode ver, a ideia é a mesma: colamos o link na célula desejada da planilha fornecida e INDIRETO transforma em um “vivo”. Por conveniência, acima da tabela, adicionei as letras das colunas (B,C,D), e à direita – os números das linhas que precisam ser retiradas de cada folha.
Armadilhas
Se você estiver usando INDIRETO (INDIRETO) você precisa se lembrar sobre suas fraquezas:
- Se você vincular a outro arquivo (colando o nome do arquivo entre colchetes, o nome da planilha e o endereço da célula), ele funcionará apenas enquanto o arquivo original estiver aberto. Se fecharmos, obtemos o erro #LINK!
- INDIRETO não pode se referir a um intervalo nomeado dinâmico. Na estática – não há problema.
- INDIRETA é uma função volátil ou “volátil”, ou seja, é recalculada para qualquer alteração em qualquer célula da planilha, e não apenas influenciando as células, como nas funções normais. Isso tem um efeito ruim no desempenho e é melhor não se empolgar com grandes tabelas INDIRETAS.
- Como criar um intervalo dinâmico com dimensionamento automático
- Somando sobre uma janela de intervalo em uma folha com a função OFFSET