Função INDIRETA no Excel
A função indireta no Excel é uma função embutida que é usada para fazer referência e obter valores de células de uma string de texto, esta fórmula faz referência a partir da célula referida, ela tem dois argumentos, o primeiro argumento não é opcional enquanto o segundo argumento é opcional que se refere a o tipo de correspondência, esta função também pode ser usada com outra fórmula.
A função INDIRETA no Excel tem vários usos. Ele retorna a referência de célula especificada por uma string de texto. Por exemplo, se a célula B1 contém uma string, digamos que os intervalos c4 e c4 tenham o valor "Jessica". Agora, se usarmos a função INDIRETO e passarmos o texto de referência como B1, ele retornará o valor contido no intervalo de células c4 que é “Jessica”.
A função INDIRETO do Excel permite que você especifique um endereço de célula INDIRETAMENTE. Como você pode no exemplo INDIRETO do Excel acima, se a célula B1 contiver o texto C4, essa fórmula indireta do Excel retornará o conteúdo da célula C4 ( Jessica ). Esta função é extremamente útil. Você pode usar a função INDIRETO sempre que quiser alterar a referência a uma célula em uma fórmula indireta do Excel sem alterar a fórmula em si.
Fórmula INDIRETA do Excel
Explicação
Ref_text é uma referência a uma célula que contém uma referência de estilo A1, uma referência de estilo R1C1, um nome definido como uma referência ou uma referência a uma célula como uma sequência de texto.
o argumento a1 é opcional
| a1 | Falso ou 0, quando ref_text está no estilo R1C1 |
| a1 | Omitido ou Verdadeiro (1) quando ref_text está no estilo A1 |
Como usar a função INDIRETA no Excel?
Vamos dar um exemplo INDIRETO do Excel antes de usar a função de endereço na pasta de trabalho do Excel:
Suponha que, para um site www.xyz.com, temos os dados do Google Analytics para visitantes do site de diferentes canais de público para cinco países diferentes, conforme mostrado na tabela a seguir:
Podemos usar a função INDIRETO no Excel com o propósito de converter uma string de texto em uma referência, referindo-nos à célula H4 e usando esse nome como referência. O uso da função INDIRETO no Excel, neste caso, é para fazer referência a intervalos de nomes usando valores de uma célula, portanto, na célula I4, poderíamos simplesmente usar a função de soma para calcular o número total de visitantes de um país Canadá.
Agora, podemos calcular diretamente a soma de visitantes de cada país usando a função de soma, mas usando a função INDIRETO no Excel, podemos calcular a soma de visitantes de diferentes países alterando a referência do nome em vez de alterar a própria fórmula indireta do Excel.
Criamos o intervalo de nomes para cada visitante do país; isso pode ser feito facilmente digitando o intervalo de nomes no espaço logo abaixo da área de transferência, conforme mostrado abaixo.
Outro método é você pode ir para fórmulas-> gerenciador de nomes -> selecione o intervalo que deseja nomear -> clique no gerenciador de nomes -> digite o intervalo de nomes.
Da mesma forma, ele criou um intervalo nomeado no Excel para visitantes de outros países também.
Agora, em I4, calculando a soma do visitante do país Canadá usando a função INDIRETO e usando o intervalo de nomes em H4 (Canadá, um nome acima para F3: F7), obtemos a soma total de um visitante.
Se criarmos uma lista de dados usando a validação de dados para os países e aplicarmos essa lista a H4
E mudando os diferentes nomes de países de Canadá para Índia, EUA, Austrália ou Cingapura, obtemos a soma total do visitante em I4 usando a função INDIRETO no Excel.
Portanto, para cada caso, não estamos mudando a fórmula indireta do Excel, que está na célula I4; estamos mudando a referência do nome em H4 e calculando a soma dos visitantes de cada país.
Podemos dar um passo adiante e fazer referência a nomes de nível de planilha também usando a função INDIRETO no Excel. Por exemplo, agora usaremos a função INDIRETO em referência a diferentes planilhas na pasta de trabalho.
Criamos diferentes planilhas com os nomes dos países para o número total de vendas feitas com cada canal de pesquisa para diferentes países.
Para a Índia,
Para os EUA,
Da mesma forma, para outros países.
Novamente, estaremos calculando o total de vendas feitas em cada canal usando a função INDIRETO do Excel para diferentes planilhas com nomes de países como referência.
Neste caso, uma vez que temos dados de vendas em planilhas diferentes, precisaremos concatenar diferentes elementos que são o intervalo de dados, então o ref_text começará com o nome da planilha, que neste caso é Índia em B4, então iremos use o operador & denominado "e" comercial que é usado para concatenação.
Portanto, obtemos a soma total das vendas para a Índia; agora, se alterarmos o nome do país da folha em B4 da Índia para outros países como os EUA, Cingapura ou Canadá, obteremos o valor total de venda desses países.
Novamente, neste caso, simplesmente alteramos os valores ref_text na célula B4 sem alterar a principal fórmula indireta do Excel, que está em C4, para obter a soma das vendas para diferentes países que estão em planilhas diferentes usando a função INDIRETO.
Agora, vamos usar a função INDIRETO usando a referência R1C1 para obter o valor de venda do último mês. Temos vendas para meses diferentes nas colunas B, C e D para três meses de janeiro, fevereiro e março, que podem crescer para outros meses como abril, maio, junho e assim por diante. Portanto, a tabela de vendas sempre estará crescendo dependendo da adição da venda do próximo mês. Portanto, neste exemplo INDIRETO do Excel, queremos calcular a venda do último mês.
Portanto, aqui o último mês é março, e queremos calcular a venda total do mês de março, que é $ 249.344, usando a função INDIRETA.
Neste exemplo INDIRETO do Excel, encontraremos o uso do segundo argumento da função INDIRETA
INDIRETO (ref_text, a1)
a1: que é um argumento opcional, é um valor lógico (um valor booleano) que especifica que tipo de referência (ref_text) está contido na célula
se a1 for verdadeiro (1) ou omitido, ref_text é considerado uma referência do estilo A1
se a1 for falso (0), ref_text é interpretado como um estilo R1C1
Como o estilo A1 é diferente do estilo R1C1?
Normalmente, a função INDIRETA no Excel usa a notação A1. Cada endereço de célula consiste em uma letra de coluna e um número de linha. No entanto, a função INDIRETO no Excel também oferece suporte à notação R1C1. Neste sistema, a célula A1 é referida como célula R1C1, célula A2 como R2C1 e assim por diante.
Para alterar para a notação R1C1, escolha Arquivo-> opção para abrir a caixa de diálogo de opções do Excel, clique na guia fórmula e marque a opção de estilo de referência R1C1. Agora, você notará que todas as letras das colunas mudam para números. E todas as referências de células em suas fórmulas também se ajustam.
Portanto, quando usamos o estilo R1C1 na função INDIRETO no Excel, passamos o valor a1 como falso e, se usarmos o estilo A1, passamos o valor verdadeiro para a1.
Agora, neste exemplo INDIRETO do Excel, estaremos usando o estilo R1C1 devido ao requisito de que as colunas com mês estariam crescendo.
Usando o estilo R1C1, o valor total de venda está na linha 10 e o valor da última coluna, que está na coluna D, será calculado usando a função CONT.valores , que fornecerá o número da última coluna contendo um valor. Portanto, conte (10:10) neste caso dará 4, que é o número da coluna no estilo R1C1. Portanto, entramos em contato com esse valor para obter R10C4 que contém o valor desejado que é o valor total de venda do último mês ($ 249.344).
E, como estamos usando o estilo R1C1 aqui, passaremos um valor falso para o argumento a1.
Agora, vamos adicionar mais uma coluna com o mês de liquidação de abril.
Portanto, obtenha novos valores de vendas do último mês correspondentes aos do último mês de abril. Da mesma forma, se adicionarmos mais meses, sempre obteremos o novo valor de venda do mês anterior sem alterar a fórmula indireta do Excel para cada mês. Esta é a especialidade da função INDIRETA no excel.
Como personalizar a matriz da tabela na função INDIRETO VLOOKUP do Excel?
Temos cinco países e um canal de busca e venda que o site fez através desses diferentes canais de diferentes países. Agora, na célula B3, queremos obter a venda que foi por meio de pesquisas orgânicas do país Estados Unidos.
Para cada tabela para países diferentes, nomeamos cada tabela com um nome curto para o país, Ind para a Índia, Estados para os EUA, Can para o Canadá e assim por diante .
Se usarmos diretamente a função INDIRETO vlookup do Excel para obter o valor, como feito abaixo
Obtemos um erro porque a função INDIRETO vlookup do Excel não é capaz de reconhecer a matriz da tabela passada como o valor da célula B2, então usaremos a função INDIRETO no Excel para fazer com que o vlookup obtenha o table_array reconhecido como um argumento válido.
Se alterarmos os valores de B1 e B2, obteremos valores de venda diferentes em B3, conforme necessário.
Observação: os intervalos de nomes não diferenciam maiúsculas de minúsculas. Conseqüentemente - Ind, IND e InD são todos iguais para o Excel.
No próximo exemplo INDIRETO do Excel, usaremos a função INDIRETO no Excel para criar uma lista dependente. Temos uma lista da posição de um funcionário em uma empresa; depois, para cada cargo, temos os nomes dos funcionários.
Agora, na célula I2, estaremos criando uma lista para diferentes classificações de funcionários conforme suas posições, e em I3, mostraremos uma lista que será dependente do valor em I2. Se I2 for um diretor, então I3 deve refletir os nomes dos diretores que Andrew e Micheal; se I2 for Gerente, I3 deve refletir os nomes do gerente que é Camille, David, Davis e William e da mesma forma para o supervisor.
Nesse caso, novamente, usaremos a função INDIRETO no Excel para criar a lista dependente. Validação de dados, em configurações selecionando os critérios de validação como uma lista e no campo fonte, estaremos usando a função INDIRETO no Excel como segue.
= INDIRETO ($ I $ 2)
Quando estamos selecionando nomes de diretor em I3 reflete como uma lista com nomes Andrew e Micheal, quando I2 é Gerente, então I3 reflete os nomes de gerente Camille, David, Davis e William e da mesma forma para o supervisor, conforme mostrado abaixo na figura.
Coisas para lembrar
- O argumento ref_text deve ser uma referência de célula válida, ou INDIRECT retornará o #REF! Erro. O argumento ref_text pode se referir a outra pasta de trabalho.
- Se ref_text se refere a outra pasta de trabalho (uma referência externa), a outra pasta de trabalho deve ser aberta. Se a pasta de trabalho de origem não estiver aberta, INDIRETO retornará o #REF! Valor do erro.
- Se ref_text se refere a um intervalo de células fora do limite de linha de 1.048.576 ou do limite de coluna de 16.384 (XFD), INDIRECT retornará um #REF! Erro.
