Conteúdo
Parabéns! Você chegou ao último dia da maratona 30 funções do Excel em 30 dias. Foi uma jornada longa e interessante durante a qual você aprendeu muitas coisas úteis sobre as funções do Excel.
No 30º dia da maratona, vamos dedicar o estudo da função INDIRETO (INDIRETO), que retorna o link especificado pela string de texto. Com esta função, você pode criar listas suspensas dependentes. Por exemplo, ao selecionar um país em uma lista suspensa, determina quais opções aparecerão na lista suspensa de cidades.
Então, vamos dar uma olhada mais de perto na parte teórica da função INDIRETO (INDIRETO) e explorar exemplos práticos de sua aplicação. Se você tiver informações ou exemplos adicionais, compartilhe-os nos comentários.
Função 30: INDIRETO
função INDIRETO (INDIRETO) retorna o link especificado pela string de texto.
Como você pode usar a função INDIRETO?
Desde a função INDIRETO (INDIRETO) retorna um link fornecido por uma string de texto, você pode usá-lo para:
- Crie um link inicial sem deslocamento.
- Crie uma referência a um intervalo nomeado estático.
- Crie um link usando informações de planilha, linha e coluna.
- Crie uma matriz de números sem deslocamento.
Sintaxe INDIRETO (INDIRETO)
função INDIRETO (INDIRETO) tem a seguinte sintaxe:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- texto_ref (link_to_cell) é o texto do link.
- a1 – se igual a TRUE (TRUE) ou não especificado, então o estilo do link será usado A1; e se FALSE (FALSE), então o estilo R1C1.
Armadilhas INDIRETAS (INDIRETAS)
- função INDIRETO (INDIRETO) é recalculado sempre que os valores na planilha do Excel mudam. Isso pode tornar sua pasta de trabalho muito mais lenta se a função for usada em muitas fórmulas.
- Se a função INDIRETO (INDIRETO) cria um link para outra pasta de trabalho do Excel, essa pasta de trabalho deve estar aberta ou a fórmula relatará um erro # Ref! (#LINK!).
- Se a função INDIRETO (INDIRETO) referencia um intervalo que excede o limite de linha e coluna, a fórmula relatará um erro # Ref! (#LINK!).
- função INDIRETO (INDIRETO) não pode fazer referência a um intervalo nomeado dinâmico.
Exemplo 1: criar um link inicial sem deslocamento
No primeiro exemplo, as colunas C e E contêm os mesmos números, suas somas calculadas usando a função SOMA (SOMA) também são os mesmos. No entanto, as fórmulas são ligeiramente diferentes. Na célula C8, a fórmula é:
=SUM(C2:C7)
=СУММ(C2:C7)
Na célula E8, a função INDIRETO (INDIRETO) cria um link para a célula inicial E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Se você inserir uma linha na parte superior da planilha e adicionar o valor de janeiro (jan), o valor na coluna C não será alterado. A fórmula mudará, reagindo à adição de uma linha:
=SUM(C3:C8)
=СУММ(C3:C8)
No entanto, a função INDIRETO (INDIRETO) fixa E2 como a célula inicial, então janeiro é automaticamente incluído no cálculo dos totais da coluna E. A célula final foi alterada, mas a célula inicial não foi afetada.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Exemplo 2: link para um intervalo nomeado estático
função INDIRETO (INDIRETO) pode criar uma referência a um intervalo nomeado. Neste exemplo, as células azuis compõem o intervalo ListaNum. Além disso, um intervalo dinâmico também é criado a partir dos valores na coluna B NumListDyn, dependendo do número de números nesta coluna.
A soma para ambos os intervalos pode ser calculada simplesmente dando seu nome como um argumento para a função SOMA (SOMA), como você pode ver nas células E3 e E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Em vez de digitar um nome de intervalo em uma função SOMA (SOMA), você pode consultar o nome escrito em uma das células da planilha. Por exemplo, se o nome ListaNum está escrito na célula D7, então a fórmula na célula E7 será assim:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Infelizmente a função INDIRETO (INDIRETO) não pode criar uma referência de intervalo dinâmico, portanto, ao copiar esta fórmula para a célula E8, você receberá um erro # Ref! (#LINK!).
Exemplo 3: criar um link usando informações de planilha, linha e coluna
Você pode criar facilmente um link com base nos números de linha e coluna, bem como usar o valor FALSE (FALSE) para o segundo argumento da função INDIRETO (INDIRETO). É assim que o link de estilo é criado R1C1. Neste exemplo, adicionamos adicionalmente o nome da planilha ao link – 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Exemplo 4: Crie uma matriz de números sem deslocamento
Às vezes, você precisa usar uma matriz de números em fórmulas do Excel. No exemplo a seguir, queremos calcular a média dos 3 maiores números na coluna B. Os números podem ser inseridos em uma fórmula, como é feito na célula D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Se você precisar de uma matriz maior, provavelmente não desejará inserir todos os números na fórmula. A segunda opção é usar a função LINHA (ROW), conforme feito na fórmula de matriz inserida na célula D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
A terceira opção é usar a função LINHA (STRING) junto com INDIRETO (INDIRETO), como feito com a fórmula de matriz na célula D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
O resultado para todas as 3 fórmulas será o mesmo:
No entanto, se as linhas forem inseridas na parte superior da planilha, a segunda fórmula retornará um resultado incorreto devido ao fato de que as referências na fórmula serão alteradas junto com o deslocamento da linha. Agora, em vez da média dos três maiores números, a fórmula retorna a média dos 3º, 4º e 5º maiores números.
Usando funções INDIRETO (INDIRETO), a terceira fórmula mantém as referências de linha corretas e continua a mostrar o resultado correto.