Baixe a apostila de exemplo
Este tutorial demonstra como usar o Função INDIRETA do Excel no Excel para criar uma referência de célula a partir do texto.
Visão geral da função INDIRETA
A função INDIRECT cria uma referência de célula a partir de uma string de texto.
(Observe como as entradas da fórmula aparecem)
Sintaxe e entradas da função INDIRETA:
| 1 | = INDIRETO (ref_text, C1) |
ref_text - Uma string que representa uma referência de célula ou referência de intervalo. A string pode estar no formato R1C1 ou A1, ou pode ser um intervalo nomeado.
a1 - OPCIONAL: indica se a referência está no formato R1C1 ou A1. FALSE para R1C1 ou TRUE / Ommitted para A1.
Qual é a função INDIRETA?
A função INDIRETO permite que você forneça uma string de texto e faça com que o computador interprete essa string como uma referência real. Isso pode ser usado para fazer referência a um intervalo na mesma planilha, em uma planilha diferente ou mesmo em uma pasta de trabalho diferente.
CUIDADO: A função INDIRETA é uma das funções voláteis. Na maioria das vezes, quando você está trabalhando em sua planilha, o computador só recalculará uma fórmula se as entradas tiverem seus valores alterados. Uma função volátil, no entanto, recalcula cada vez que você faz uma alteração em qualquer célula. Deve-se ter cuidado para garantir que você não cause um grande tempo de recálculo devido ao uso excessivo da função volátil ou por ter muitas células dependentes do resultado de uma função volátil.
Crie uma referência de célula
Digamos que você deseja buscar o valor de A2, mas deseja ter certeza de que sua fórmula fica em A2 independentemente de novas linhas sendo inseridas / removidas. Você poderia escrever uma fórmula de
| 1 | = INDIRETO ("A2") |
Observe que o argumento dentro de nossa função é a string de texto “A2”, e não uma referência de célula. Além disso, como esta é uma string de texto, não há necessidade de indicar uma referência absoluta como $ A $ 2. O texto nunca mudará e, portanto, esta fórmula sempre apontará para A2, não importa para onde seja movido.
Número da linha INDIRETO
Você pode concatenar strings de texto e valores de células. Em vez de escrever “A2” como fizemos anteriormente, podemos pegar um valor numérico da célula B2 e usá-lo em nossa fórmula. Escreveríamos fórmulas como
| 1 | = INDIRETO ("A" e B2) |
O símbolo “&” está sendo usado aqui para concatenar a string de texto “A” com o valor da célula B2. Então, se o valor de B2 fosse atualmente 10, então nossa fórmula seria lida como
| 123 | = INDIRETO ("A" & 10)= INDIRETO ("A10")= A10 |
Valor da coluna INDIRETO
Você também pode concatenar na referência da coluna. Desta vez, digamos que sabemos que queremos obter um valor da linha 10, mas queremos ser capazes de alterar de qual coluna extrair. Vamos colocar a letra da coluna que queremos na célula B2. Nossa fórmula pode parecer
| 1 | = INDIRETO (B2 & "10") |
Se o valor de B2 é "G", então nossa fórmula avalia assim
| 123 | = INDIRETO ("G" e 10)= INDIRETO ("G10")= G10 |
Estilo INDIRETO r1c1
Em nosso exemplo anterior, tivemos que usar uma letra para indicar a referência da coluna. Isso ocorre porque estávamos usando o que é conhecido como referência de estilo A1. No estilo A1, as colunas são fornecidas por uma letra e as linhas são fornecidas por números. As referências absolutas são indicadas com o “$” antes do item que queremos que permaneça absoluto.
Em r1c1, tanto as linhas quanto as colunas são iniciadas com o número. A referência absoluta a a1 seria escrita como
| 1 | = R1C1 |
Você pode ler isso como “Linha 1, Coluna 1”. As referências relativas são fornecidas usando colchetes, mas o número indica a posição em relação à célula com fórmula. Então, se estivéssemos escrevendo uma fórmula na célula A10 e precisássemos nos referir a A1, escreveríamos a fórmula
| 1 | = R [-9] C |
Você pode ler isso como “A célula 9 linhas acima, mas na mesma coluna.
A razão pela qual isso pode ser útil é que INDIRECT pode suportar o uso da notação r1c1. Considere o exemplo anterior em que buscamos um valor da linha 10, mas queríamos mudar a coluna. Em vez de dar uma carta, digamos que colocamos um número na célula B2. Nossa fórmula, então, pode parecer
| 1 | = INDIRETO ("R10C" & B2, FALSO) |
Temos omitido o 2WL argumento até agora. Se este argumento for omitido ou True, a função será avaliada usando o estilo A1. Como é falso, será avaliado em r1c1. Vamos supor que o valor de B2 seja 5. Nossa fórmula avaliará isso assim
| 12 | = INDIRETO ("R10C5", FALSO)= $ E $ 10 |
Diferenças INDIRETAS com A1 vs r1c1
Lembra que mostramos anteriormente que, como o conteúdo dessa fórmula era uma string de texto, ela nunca mudou?
| 1 | = INDIRETO ("A2") |
Esta fórmula estará sempre olhando para a célula A2, não importa para onde você mova a fórmula. Em r1c1, uma vez que você pode indicar a posição relativa usando colchetes, esta regra não permanece consistente. Se você colocar esta fórmula na célula B2
| 1 | = INDIRETO ("RC [-1]") |
Ele estará olhando para a célula A2 (uma vez que a coluna A está à esquerda da coluna B). Se você copiar esta fórmula para a célula B3, o texto interno permanecerá o mesmo, mas o INDIRETO agora estará olhando para a célula A3.
INDIRETO com o nome da folha
Você também pode combinar um nome de folha em suas referências INDIRETAS. Uma regra importante a lembrar é que você deve colocar os nomes entre aspas simples e separar o nome da planilha da referência da célula com um ponto de exclamação.
Digamos que tenhamos esta configuração, onde estamos informando o nome, linha e coluna de nossa planilha.
Nossa fórmula para combinar tudo isso em uma referência seria assim:
| 1 | = INDIRETO ("'" & A2 & "'!" & B2 & C2) |
Nossa fórmula será avaliada da seguinte forma:
| 123 | = INDIRETO ("'" & "Folha2" & "'!" & "B" e "5")= INDIRETO ("'" Folha2'! B5 ")= 'Folha2'! B5 |
Tecnicamente, uma vez que a palavra "Folha2" não contém espaços, não necessidade as aspas simples. É perfeitamente válido escrever algo como
| 1 | = Folha2! A2 |
No entanto, não faz mal colocar as aspas quando você não precisa delas. É uma prática recomendada incluí-los para que sua fórmula possa lidar com a instância onde eles podem ser necessários.
INDIRETO para outra pasta de trabalho
Mencionaremos também que INDIRETO pode criar uma referência para uma pasta de trabalho diferente. A limitação é que INDIRETO não busca valores de uma pasta de trabalho fechada, portanto, esse uso específico tem praticidade limitada. Se a pasta de trabalho para a qual INDIRETO está apontando não for aberta, a função lançará um “#REF!” erro.
A sintaxe ao escrever o nome da pasta de trabalho é que ela precisa estar entre colchetes. Vamos usar essa configuração e tentar buscar um valor da célula C7.
Nossa fórmula seria
| 1 | = INDIRETO ("'[" & A2 & "]" & B2 & "'! C7") |
Novamente, preste atenção ao posicionamento das aspas simples, colchetes e ponto de exclamação. Nossa fórmula será avaliada da seguinte forma:
| 123 | = INDIRETO ("'[" & "Sample.xlsx" & "]" & "Resumo" & "'! C7")= INDIRETO ("'[Sample.xslx] Resumo'! C7")= '[Sample.xlsx] Resumo'! C7 |
INDIRETO para construir faixa dinâmica
Quando você tem um grande conjunto de dados, é importante tentar otimizar as fórmulas para que não façam mais trabalho do que o necessário. Por exemplo, em vez de fazer referência a toda a coluna A, podemos querer apenas fazer referência ao número exato de células em nossa lista. Considere o seguinte layout:
Na célula B2, colocamos a fórmula
| 1 | = CONT.valores (A: A) |
A função CONT.valores é muito fácil para o computador calcular, pois ela simplesmente verifica quantas células na coluna A têm algum valor, ao invés de ter que fazer verificações lógicas ou operações matemáticas.
Agora, vamos construir nossa fórmula que somará os valores na coluna A, mas queremos ter certeza de que ela olha apenas para o intervalo exato com os valores (A2: A5). Vamos escrever nossa fórmula como
| 1 | = SOMA (INDIRETO ("A2: A" e B2)) |
Nosso INDIRETO pegará o número 5 da célula B2 e criará uma referência para o intervalo A2: A5. A SOMA pode então usar este intervalo para seu cálculo. Se adicionarmos outro valor à célula A6, o número em B2 será atualizado e nossa fórmula SUM também será atualizada automaticamente para incluir esse novo valor.
CUIDADO: com a introdução de tabelas no Office 2007, é muito mais eficiente armazenar seus dados em uma tabela e usar uma referência estrutural em vez de construir a fórmula que usamos neste exemplo devido à natureza volátil de INDIRETO. No entanto, podem ser casos em que você precisa criar uma lista de itens e não pode usar uma Tabela.
Charting Dinâmico com INDIRETO
Vamos pegar o exemplo anterior e dar mais um passo. Em vez de escrever uma fórmula para nos dar a soma dos valores, vamos criar um intervalo nomeado. Poderíamos chamar esse intervalo de "MyData" e fazer com que ele se refira a
| 1 | = INDIRETO ("A2: A" & CONTAR.VAL ($ A: $ A)) |
Observe que, como estamos colocando isso em um intervalo nomeado, trocamos a referência para B2 e, em vez disso, colocamos a função CONTAR.VAL diretamente.
Agora que temos esse intervalo nomeado, podemos usá-lo em um gráfico. Vamos criar um gráfico de linhas em branco e adicionar uma série de dados. Para os valores da série, você pode escrever algo como
| 1 | = Folha1! MyData |
O gráfico agora usará essa referência para plotar valores. À medida que mais valores são adicionados à coluna A, o INDIRETO se refere a um intervalo cada vez maior e nosso gráfico continuará a ser atualizado com todos os valores adicionados recentemente.
Validação Dinâmica de Dados com INDIRETO
Ao coletar informações dos usuários, às vezes é necessário fazer uma escolha entre as opções que dependem de uma escolha anterior. Considere este layout, onde nossa primeira coluna permite ao usuário escolher entre frutas, vegetais e carnes.
No 2WL coluna, não queremos ter uma lista grande mostrando todas as opções possíveis, pois já restringimos um pouco as coisas. Então, criamos três outras listas parecidas com esta:
A seguir, atribuiremos a cada um esses listas para um intervalo nomeado. Ou seja, todas as frutas estarão em uma faixa chamada “Frutas” e vegetais em “Vegetais”, etc.
De volta à nossa mesa, estamos prontos para configurar a validação de dados no 2WL coluna. Criaremos uma validação do tipo Lista, com uma entrada de:
| 1 | = INDIRETO (A2) |
O INDIRETO vai ler na escolha feita na coluna A e ver o nome de uma categoria. Definimos intervalos com esses nomes, então o INDIRETO pegará esse nome e criará uma referência para o intervalo desejado.
Notas Adicionais
Use a função INDIRETO para criar uma referência de célula a partir de texto.
Primeiro crie a string de texto que representa uma referência de célula. A string deve estar na letra da coluna estilo A1 usual e número da linha (M37) ou no estilo R1C1 (R37C13). Você pode digitar a referência diretamente, mas normalmente fará referência às células que definem as linhas e colunas. Por último, insira o formato de referência de célula que você escolher. VERDADEIRO ou Omitido para referência do estilo A1 ou FALSO para o estilo R1C1.
Ao trabalhar com fórmulas INDIRETAS, você pode querer usar o Função ROW para obter o número da linha de uma referência ou o Função COLUMN para obter o número da coluna (não a letra) de uma referência.
Voltar para a lista de todas as funções no Excel
INDIRETO no Google Sheets
A função INDIRETO funciona exatamente da mesma forma no Planilhas Google e no Excel:
