Como fixar referências e bloquear células em fórmulas do Excel
Para fixar uma referência, use $ antes da coluna, da linha ou de ambas: $A1, A$1 ou $A$1. Isto controla o que muda quando copia uma fórmula. Impedir a edição de uma célula exige proteção da folha: é uma operação diferente.
Exemplo de referências mistas
Neste exemplo independente, coloque 100 em B2 e 200 em B3, 10% em C1 e 20% em D1. Em C2, introduza:
=$B2*(1-C$1)Copie para C2:D3. Os resultados esperados são C2 = 90, D2 = 80, C3 = 180 e D3 = 160. $B2 mantém a coluna do preço e deixa variar a linha; C$1 mantém a linha dos descontos e deixa variar a coluna. Este exemplo usa posições próprias e não depende das posições da imagem abaixo.
Referências relativas, absolutas e mistas
A1 permite variar coluna e linha; $A$1 fixa ambas; A$1 fixa só a linha; $A1 fixa só a coluna. Escolha a referência a partir da direção em que vai copiar a fórmula. Na secção seguinte encontra a tabela-resumo e outro exemplo ilustrado.
São os cifrões ($) que indicam o que está bloqueado. Um cifrão antes da letra indica que a coluna está bloqueada, antes do número indica que a linha está bloqueada.
Pode bloquear células no Excel inserindo manualmente os cifrões ($), ou pode usar a tecla F4, que permite alternar entre os diferentes tipos de referências:
- 1 vezpara tornar numa referência absoluta – $A$1;
- 2 vezes para tornar numa referência mista, trancando a linha – A$1;
- 3 vezes para tornar numa referência mista, trancando a coluna – $A1.
O que fica fixo em cada referência?
| Referência | Coluna | Linha |
|---|---|---|
A1 | Varia | Varia |
$A$1 | Fixa | Fixa |
A$1 | Varia | Fixa |
$A1 | Fixa | Varia |
Exemplo
Vamos a um exemplo.
Tem uma lista de produtos com o respetivo Preço de Venda (PVP) e quer calcular o valor de cada um caso sejam aplicados diferentes descontos:
O objetivo é escrever apenas uma fórmula na célula D6, que seja possível de arrastar para as restantes células:
Para calcular o preço com 10% de desconto do produto A, inseriu a seguinte fórmula:
Se copiar esta fórmula para as restantes células, os resultados vão estar incorretos:
Isto porque está a usar referências relativas. Não bloqueou nenhuma célula e, portanto, quando copia a fórmula as referências usadas vão alterar também.
Vamos por partes.
Quando arrasta a fórmula para baixo, o que precisa de ficar fixo? No exemplo dos 10% de desconto, independentemente do produto, quer que seja utilizado sempre os 10% (que estão na linha 5). Desta forma, tem de bloquear a linha, colocando o $ antes do número, para poder arrastar para baixo:
Nota: se o objetivo não fosse arrastar para a direita também, poderia usar uma referência absoluta ($D$5). No entanto, como vai querer arrastar para as colunas que contêm os outros descontos, tem de deixar a coluna “livre”.
Agora precisa de perceber o que tem de manter fixo para poder arrastar para a direita. Independentemente do desconto, o PVP a ser considerado é o mesmo, pelo que, para poder arrastar para a direita, terá de bloquear a coluna do PVP. Não pode fixar a linha, pois quer que esta varie consoante a linha em que está a colocar a fórmula:
Desta forma, conseguiu bloquear as células necessárias para escrever a fórmula apenas uma vez, e arrastar para todo o intervalo.
Imagine que queria transformar todos os valores apresentados em USD. Poderia colocar a taxa de câmbio numa célula, e aí sim, usar uma referência absoluta (coluna e linha bloqueada), porque independentemente da coluna ou linha em que estava a colocar a fórmula, a célula a ser usada é sempre a mesma.
Entender como bloquear corretamente as células é crucial para quem quer dominar fórmulas e funções do Excel e quem quer poupar tempo!
Partilhe comigo, já alguma vez tinha percebido a lógica por trás dos cifrões ($) nas fórmulas? Conte-me nos comentários!
