Referência absoluta no Excel (como criar e utilizar)

Ao fazer referência a uma célula numa fórmula no Excel, o tipo de referência predefinido é relativo. Esse tipo de referência ajusta-se automaticamente quando a fórmula é copiada para outras células, conforme a posição relativa da linha e da coluna. Se pretender que a referência permaneça inalterada, independentemente do local onde a fórmula for copiada, deverá utilizar uma referência absoluta.

• O que é uma referência absoluta
• Como criar referências absolutas
• Utilize referência absoluta com exemplos
• Calcular percentagem do total
• Procurar um valor e devolver o valor correspondente
• 2 cliques para transformar em lote referências de células em absolutas com Kutools
• Referência relativa e referência mista
• Coisas a lembrar

Transfira gratuitamente o ficheiro de exemplo

Vídeo: Referência absoluta

O que é uma referência absoluta

Uma referência absoluta é um tipo de referência a células no Excel.

Em comparação com uma referência relativa, que se altera com base na sua posição relativa quando uma fórmula é copiada para outras células, uma referência absoluta permanece constante, independentemente do local para onde a fórmula for copiada ou movida.

Uma referência absoluta é criada ao adicionar um sinal de dólar ($) antes da letra da coluna e do número da linha na fórmula. Por exemplo, para criar uma referência absoluta à célula A1, represente-a como $A$1.

As referências absolutas são ideais quando precisa de fazer referência a uma célula ou intervalo fixo numa fórmula que será copiada para várias células, sem que a referência seja alterada.

Por exemplo, o intervalo A4:C7 contém os preços dos produtos, e pretende calcular o imposto a pagar para cada produto com base na taxa de imposto indicada na célula B2.

Se utilizar uma referência relativa na fórmula, como «=B5*B2», obterá resultados incorretos ao arrastar a alça de preenchimento automático para aplicar essa fórmula. Isto acontece porque a referência à célula B2 ajusta-se relativamente à posição das células onde a fórmula é copiada. Assim, a fórmula na célula C6 torna-se «=B6*B3» e a fórmula na célula C7 passa a ser «=B7*B4».

Contudo, ao utilizar uma referência absoluta à célula B2 na fórmula «=B5*$B$2», garante que a taxa de imposto permaneça inalterada em todas as células ao arrastar a fórmula para baixo com a alça de preenchimento automático, obtendo assim resultados corretos.

Utilização de referência relativa		Utilização de referência absoluta

Como criar referências absolutas

Para criar uma referência absoluta no Excel, basta adicionar o símbolo de dólar ($) antes das referências de coluna e linha numa fórmula. Existem duas formas de criar uma referência absoluta:

Adicionar manualmente sinais de dólar à referência da célula

Pode adicionar manualmente sinais de dólar ($) antes das referências de coluna e linha que deseja tornar absolutas enquanto digita a fórmula numa célula.

Por exemplo, se pretender somar os números nas células A1 e B1 e tornar ambas as referências absolutas, basta digitar a fórmula como «=$A$1+$B$1». Assim, as referências às células permanecem fixas sempre que a fórmula for copiada ou movida para outras células.

Alternativamente, se pretender converter as referências de uma fórmula existente em absolutas, basta selecionar a célula e, de seguida, aceder à Barra de Fórmulas para inserir os sinais de dólar ($).

Utilizar a tecla de atalho F4 para converter referência relativa em absoluta

1. Faça duplo clique na célula com a fórmula para entrar no modo de edição;
2. Coloque o cursor na referência de célula que pretende tornar absoluta;
3. Prima a tecla "F4" no teclado para alternar os tipos de referência até que os sinais de dólar sejam adicionados antes das referências da coluna e da linha;
4. Prima a tecla Enter para sair do modo de edição e aplicar as alterações.

A tecla F4 permite alternar entre referências relativas, absolutas e mistas.

A1 → $A$1 → A$1 → $A1 → A1

Para tornar todas as referências absolutas numa fórmula, selecione toda a fórmula na Barra de Fórmulas e prima a tecla "F4" até que os sinais de dólar apareçam antes das referências de coluna e linha.

A1+B1 → $A$1++$B$1 → A$1++B$1 → $A1+$B1 → A1+B1

Utilize referência absoluta com exemplos

Esta secção apresenta dois exemplos que mostram quando e como utilizar referências absolutas numa fórmula do Excel.

Exemplo 1 Calcular a percentagem do total

Imagine que tem um intervalo de dados (A3:B7) com as vendas de cada fruta e que a célula B8 contém o valor total das vendas dessas frutas. Pretende agora calcular a percentagem de vendas de cada fruta em relação ao total.

A fórmula genérica para calcular a percentagem do total:

Percentage = Sale/Amount

Utilize a referência relativa na fórmula para obter a percentagem da primeira fruta da seguinte forma:

=B4/B8

Ao arrastar a alça de preenchimento automático para baixo para calcular a percentagem das restantes frutas, surgem erros #DIV/0!.

Isto acontece porque, ao arrastar a alça de preenchimento automático para copiar a fórmula para as células abaixo, a referência relativa B8 é automaticamente ajustada para outras referências de célula — B9, B10 e B11 — de acordo com as respetivas posições relativas. Como as células B9, B10 e B11 estão vazias (ou seja, contêm zero), a fórmula devolve um erro quando o divisor é zero.

Para corrigir os erros, neste caso, deverá tornar a referência à célula B8 absoluta ($B$8) na fórmula, evitando assim que ela se altere ao mover ou copiar a fórmula para qualquer local. A fórmula é agora atualizada para:

=B4/$B$8

Depois, arraste a alça de preenchimento automático para baixo para calcular a percentagem das restantes frutas.

Exemplo 2 Procurar um valor e devolver o valor correspondente

Imagine que pretende procurar a lista de nomes em D4:D5 e devolver os respetivos salários com base nos nomes dos colaboradores e nos salários anuais correspondentes indicados no intervalo A4:B8.

A fórmula genérica para pesquisa é:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Se utilizar a referência relativa na fórmula para procurar um valor e devolver o valor correspondente da seguinte forma:

=VLOOKUP(D4,A4:B8,2,FALSE)

Ao arrastar a alça de preenchimento automático para baixo à procura do valor seguinte, é devolvido um erro.

Quando arrasta a alça de preenchimento para baixo para copiar a fórmula para a célula seguinte, as referências na fórmula ajustam-se automaticamente uma linha abaixo. Consequentemente, a referência ao intervalo da tabela, A4:B8, passa a ser A5:B9. Como Lisa não pode ser encontrada no intervalo A5:B9, a fórmula devolve um erro.

Para evitar os erros, utilize a referência absoluta $A$4:$B$8 em vez da referência relativa A4:B8 na fórmula:

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

Depois, arraste a alça de preenchimento automático para baixo para obter o salário da Lisa.

2 cliques para tornar em lote as referências das células absolutas com o Kutools

Quer opte por digitar manualmente ou utilizar o atalho F4, só pode alterar uma fórmula de cada vez no Excel. Se pretender tornar absolutas as referências das células em centenas de fórmulas, a ferramenta «Converter Referências de Células» do Kutools para Excel permite-lhe concluir esta tarefa em apenas dois cliques.

Para tornar absolutas as referências de células em várias fórmulas, selecione as células que contêm fórmulas e clique em **Kutools** > **Mais** > **Converter Referências de Células**. Em seguida, escolha a opção **Para absoluto** e clique em **OK** ou **Aplicar**. Assim, todas as referências de células nas fórmulas selecionadas serão convertidas em absolutas.

Referência relativa e referência mista

Além da referência absoluta, existem mais dois tipos de referência: a relativa e a mista.

Referência relativa é o tipo de referência predefinido no Excel, sem sinais de dólar ($) antes das referências de linha e coluna. Quando uma fórmula com referências relativas é copiada ou movida para outras células, estas ajustam-se automaticamente de acordo com a sua posição relativa.

Por exemplo, ao introduzir uma fórmula numa célula como «=A1+1» e arrastar a alça de preenchimento automático para baixo, a fórmula adapta-se automaticamente na célula seguinte para «=A2+1».

Referência mista combina uma referência absoluta e uma referência relativa. Por outras palavras, a referência mista utiliza o sinal de dólar ($) para fixar quer a linha quer a coluna quando se copia ou preenche uma fórmula.

Considere uma tabela de multiplicação como exemplo: as linhas e os dados da coluna contêm os números de 1 a 9, que serão multiplicados entre si.

Para começar, utilize a fórmula «=B3*C2» na célula C3 para multiplicar o número em B3 pelo número (1) da primeira coluna. Contudo, ao arrastar a alça de preenchimento automático para a direita e preencher as restantes células, verá que todos os resultados estão incorretos — exceto o primeiro.

Isto acontece porque, ao copiar a fórmula para a direita, a linha mantém-se inalterada, mas a coluna avança de B3 para C3, D3 e assim por diante. Assim, as fórmulas nas células à direita (D3, E3, etc.) tornam-se «=C3*D2», «=D3*E2», sucessivamente — quando o pretendido é, na verdade, «=B3*D2», «=B3*E2», etc.

Neste caso, terá de adicionar um sinal de dólar ($) para fixar a referência da coluna em “B3”. Utilize a fórmula indicada abaixo:

=$B3*C2

Agora, ao arrastar a fórmula para a direita, os resultados estão corretos.

Depois, terá de multiplicar o número 1 da célula C2 pelos números nas linhas seguintes.

Ao copiar a fórmula para baixo, a coluna da célula C2 permanece fixa, mas a linha ajusta-se automaticamente de C2 para C3, C4 e assim sucessivamente. Assim, as fórmulas nas células seguintes transformam-se em «=$B4*C3», «=$B5*C4», etc., gerando resultados incorretos.

Para resolver este problema, altere «C2» para «C$2», mantendo assim a referência de linha fixa ao arrastar a alça de preenchimento automático para baixo e preencher as fórmulas.

=$B3*C$2

Agora, basta arrastar a alça de preenchimento automático para a direita ou para baixo e obter todos os resultados.

Coisas a lembrar

• Resumo das referências de células

  Tipo	Exemplo	Resumo
  Referência Absoluta	$A$1	Nunca muda quando a fórmula é copiada para outras células
  Referência Relativa	A1	Tanto a referência da linha como a da coluna mudam com base na posição relativa quando a fórmula é copiada para outras células
  Referência Mista	$A1/A$1	A referência da linha muda quando a fórmula é copiada para outras células, mas a referência da coluna permanece fixa / A referência da coluna muda quando a fórmula é copiada para outras células, mas a referência da linha permanece fixa;

• Geralmente, as referências absolutas nunca mudam quando uma fórmula é movida. Contudo, as referências absolutas ajustam-se automaticamente quando uma linha ou coluna é adicionada ou removida no topo ou à esquerda da folha de cálculo. Por exemplo, numa fórmula «=$A$1+1», ao inserir uma linha no topo da folha, a fórmula será automaticamente alterada para «=$A$2+1».

  

• A tecla "F4" permite alternar facilmente entre referências relativas, absolutas e mistas.