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

Ao referenciar uma célula em uma fórmula no Excel, o tipo de referência padrão é a referência relativa. Essas referências mudarão quando a fórmula for copiada para outras células com base na coluna e linha relativas. Se você deseja manter uma referência constante, independentemente de onde a fórmula for copiada, precisa usar a referência absoluta.

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

Baixe gratuitamente o arquivo de exemplo

Vídeo: Referência absoluta

O que é uma referência absoluta

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

Comparada a uma referência relativa, que muda com base em sua posição relativa quando uma fórmula é copiada para outras células, uma referência absoluta permanecerá constante, independentemente de onde a fórmula seja copiada ou movida.

Uma referência absoluta é criada adicionando um cifrão ($) antes das referências de coluna e linha na fórmula. Por exemplo, para criar uma referência absoluta para a célula A1, você deve representá-la como $A$1.

As referências absolutas são úteis quando você deseja se referir a uma célula ou intervalo fixo em uma fórmula que será copiada para várias células, mas não quer que a referência mude.

Por exemplo, o intervalo A4:C7 contém os preços dos produtos, e você deseja obter o imposto a pagar de cada produto com base na taxa de imposto na célula B2.

Se você usar uma referência relativa na fórmula como "=B5*B2", resultados incorretos serão retornados ao arrastar a alça de preenchimento automático para aplicar essa fórmula. Isso ocorre porque a referência à célula B2 mudará em relação às células na fórmula. Agora, a fórmula na célula C6 é "=B6*B3" e a fórmula na célula C7 é "=B7*B4".

Mas se você usar uma referência absoluta para a célula B2 com a fórmula "=B5*$B$2", isso garantirá que a taxa de imposto permaneça a mesma para todas as células quando a fórmula for arrastada usando a alça de preenchimento automático, e os resultados estarão corretos.

Usando referência relativa		Usando referência absoluta

Como criar referências absolutas

Para criar uma referência absoluta no Excel, adicione cifrões ($) antes das referências de coluna e linha em uma fórmula. Existem duas maneiras de criar uma referência absoluta:

Adicionar manualmente cifrões à referência da célula

Você pode adicionar manualmente cifrões ($) antes das referências de coluna e linha que deseja tornar absolutas enquanto digita a fórmula em uma célula.

Por exemplo, se você deseja somar os números nas células A1 e B1 e torná-las absolutas, basta digitar a fórmula como "=$A$1+$B$1". Isso garantirá que as referências de células permaneçam constantes quando a fórmula for copiada ou movida para outras células.

Alternativamente, se você deseja alterar as referências em uma fórmula existente para absolutas, selecione a célula, vá para a barra de fórmulas e adicione os cifrões ($).

Usar o atalho F4 para converter referência relativa em absoluta

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

A tecla F4 alterna as referências entre relativa, absoluta e mista.

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

Se você deseja tornar todas as referências absolutas em uma fórmula, selecione toda a fórmula na barra de fórmulas, pressione a tecla "F4" para alternar os tipos de referência até que os cifrões sejam adicionados antes das referências de coluna e linha.

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

Usar referência absoluta com exemplos

Esta parte fornece 2 exemplos para mostrar quando e como usar referências absolutas em uma fórmula do Excel.

Exemplo 1: Calcular porcentagem do total

Suponha que você tenha um intervalo de dados (A3:B7) contendo as vendas de cada fruta, e a célula B8 contém o valor total das vendas dessas frutas. Agora, você deseja calcular a porcentagem de cada venda de fruta em relação ao total.

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

Percentage = Sale/Amount

Use a referência relativa na fórmula para obter a porcentagem da primeira fruta assim:

=B4/B8

Ao arrastar a alça de preenchimento automático para baixo para calcular a porcentagem das outras frutas, erros #DIV/0! serão retornados.

Isso ocorre 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 é ajustada automaticamente para outras referências de células (B9, B10, B11) com base em suas posições relativas. E as células B9, B10 e B11 estão vazias (zeros), quando o divisor é zero, a fórmula retorna um erro.

Para corrigir os erros, neste caso, você precisa tornar a referência da célula B8 absoluta ($B$8) na fórmula para mantê-la inalterada ao mover ou copiar a fórmula para qualquer lugar. Agora, a fórmula foi atualizada para:

=B4/$B$8

Em seguida, arraste a alça de preenchimento automático para baixo para calcular a porcentagem das outras frutas.

Exemplo 2: Procurar um valor e retornar o valor correspondente

Supondo que você deseje procurar a lista de nomes em D4:D5 e retornar seus salários correspondentes com base nos nomes dos funcionários e salários anuais fornecidos no intervalo (A4:B8).

A fórmula genérica para pesquisa é:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Se você usar a referência relativa na fórmula para pesquisar um valor e retornar o valor correspondente assim:

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

Depois, arraste a alça de preenchimento automático para baixo para pesquisar o valor abaixo, e um erro será retornado.

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

Para evitar os erros, use 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 de Lisa.

2 cliques para tornar referências de células absolutas em lote com Kutools

Seja digitando manualmente ou usando o atalho F4, você só pode alterar uma fórmula por vez no Excel. Se você deseja tornar as referências de células em centenas de fórmulas absolutas no Excel, a ferramenta "Converter Referências" do "Kutools para Excel" pode ajudá-lo a lidar com a tarefa com 2 cliques.

Para tornar as referências de células absolutas em várias fórmulas, selecione as células das fórmulas e clique em "Kutools" > "Mais" > "Converter Referências". Em seguida, escolha a opção "Para Absoluta" e clique em "Ok" ou "Aplicar". Agora, todas as referências de células das fórmulas selecionadas foram convertidas para absolutas.

Referência relativa e referência mista

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

A referência relativa é o tipo de referência padrão no Excel, que não possui cifrões ($) antes das referências de linha e coluna. E quando uma fórmula com referências relativas é copiada ou movida para outras células, as referências mudarão automaticamente com base em sua posição relativa.

Por exemplo, ao digitar uma fórmula em uma célula como "=A1+1", depois arrastar a alça de preenchimento automático para baixo para preencher essa fórmula na próxima célula, a fórmula mudará automaticamente para "=A2+1".

A referência mista é composta tanto por uma referência absoluta quanto por uma referência relativa. Em outras palavras, a referência mista usa o cifrão ($) para fixar a linha ou a coluna quando uma fórmula é copiada ou preenchida.

Tomemos uma tabela de multiplicação como exemplo, as linhas e colunas listam os números de 1 a 9, que você irá multiplicar uns pelos outros.

Para começar, você pode usar a fórmula "=B3*C2" na célula C3 para multiplicar 1 na célula B3 pelo número (1) na primeira coluna. No entanto, ao arrastar a alça de preenchimento automático para a direita para preencher as outras células, você notará que todos os resultados estão incorretos, exceto o primeiro.

Isso ocorre porque, ao copiar a fórmula para a direita, a posição da linha não muda, mas a posição da coluna muda de B3 para C3, D3, etc. Como resultado, as fórmulas nas células à direita (D3, E3, etc.) mudam para "=C3*D2", "=D3*E2", e assim por diante, quando na verdade você deseja que sejam "=B3*D2", "=B3*E2", e assim por diante.

Neste caso, você precisa adicionar um cifrão ($) para bloquear a referência da coluna de “B3”. Use a fórmula abaixo:

=$B3*C2

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

Então, você precisa multiplicar o número 1 na célula C2 pelos números nas linhas abaixo.

Ao copiar a fórmula para baixo, a posição da coluna da célula C2 não mudará, mas a posição da linha muda de C2 para C3, C4, etc. Como resultado, as fórmulas nas células abaixo mudam para "=$B4C3", "=$B5C4", etc., o que produzirá resultados incorretos.

Para resolver esse problema, altere “C2” para “C$2” para manter a referência da linha constante ao arrastar a alça de preenchimento automático para baixo para preencher as fórmulas.

=$B3*C$2

Agora você pode arrastar a alça de preenchimento automático para a direita ou para baixo para obter todos os resultados.

Coisas para 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	Ambas as referências de linha e 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 de linha muda quando a fórmula é copiada para outras células, mas a referência de coluna é fixa / A referência de coluna muda quando a fórmula é copiada para outras células, mas a referência de linha é fixa;

• Geralmente, as referências absolutas nunca mudam quando uma fórmula é movida. No entanto, as referências absolutas ajustarão automaticamente quando uma linha ou coluna for adicionada ou removida do topo ou da esquerda na planilha. Por exemplo, em uma fórmula "=$A$1+1", ao inserir uma linha no topo da planilha, a fórmula mudará automaticamente para "=$A$2+1".

  

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