Uma referência absoluta mantém uma célula fixa quando você copia uma fórmula. Em =B2*$E$1, os cifrões travam E1: arraste a fórmula para baixo e todas as linhas continuam multiplicando por E1, enquanto B2 passa para B3, B4 e assim por diante. Para colocar os cifrões, clique na referência dentro da fórmula e pressione F4 (no Mac, Cmd+T).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $425 | ||
| 4 | Chen | $15,200 | $760 | ||
| 5 | Dina | $9,800 | $490 | ||
| 6 | Eli | $11,000 | $550 |
=B2*$E$1C2 foi digitada uma vez e arrastada para baixo. Clique em C4: a fórmula é =B4*$E$1. A célula de vendas passou para a linha 4, a taxa ficou em E1. Mude a taxa em E1 para 8% e todas as comissões se atualizam.
Referência relativa ou absoluta
| Referência | Nome | Copiada uma linha para baixo e uma coluna para a direita |
|---|---|---|
A1 | relativa | B2 |
$A$1 | absoluta | $A$1 |
A$1 | mista: linha travada | B$1 |
$A1 | mista: coluna travada | $A2 |
Uma referência simples como B2 é relativa: o Excel guarda essa referência como "a célula nesta posição em relação a mim", então uma cópia uma linha abaixo aponta uma linha abaixo. É exatamente o que você quer para dados linha a linha, e é o padrão. O $ antes da letra da coluna ou do número da linha trava essa parte.
O erro clássico: arrastar para baixo sem $
Aqui está a tabela de comissões de novo, com =B2*E1 em C2 e sem cifrões. A primeira linha está certa. As outras dão 0.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $0 | ||
| 4 | Chen | $15,200 | $0 | ||
| 5 | Dina | $9,800 | $0 | ||
| 6 | Eli | $11,000 | $0 |
=B3*E2C3 tem =B3*E2: a referência da taxa desceu para E2, que está vazia, e uma célula vazia conta como 0. Corrija aqui mesmo: clique em C2, mude a fórmula para =B2*$E$1 e pressione Enter. A coluna inteira acompanha, porque C3:C6 são cópias de C2. Quando a célula fixa é um divisor, como em =B2/B7 para a participação em um total, o mesmo erro mostra #DIV/0! em vez de 0 (porcentagem do total é o caso mais comum).
Pressione F4 para colocar os cifrões
Enquanto digita ou edita uma fórmula, coloque o cursor em uma referência (ou logo depois dela) e pressione F4. Cada vez que você pressiona, ela passa para a forma seguinte:
E1 -> $E$1 -> E$1 -> $E1 -> E1
Em muitos notebooks a tecla F4 controla a tela ou o som, então pressione Fn+F4. No Mac, use Cmd+T, ou Fn+F4. Você também pode digitar os $ à mão.
Referência mista: travar só a linha ou só a coluna
Uma referência mista tem um cifrão só. $A2 sempre lê a coluna A, mas deixa a linha se mover; B$1 sempre lê a linha 1, mas deixa a coluna se mover. Com as duas em uma fórmula, uma única fórmula arrastada por uma grade monta uma tabuada:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | x | 1 | 2 | 3 | 4 | 5 |
| 2 | 1 | 1 | 2 | 3 | 4 | 5 |
| 3 | 2 | 2 | 4 | 6 | 8 | 10 |
| 4 | 3 | 3 | 6 | 9 | 12 | 15 |
| 5 | 4 | 4 | 8 | 12 | 16 | 20 |
| 6 | 5 | 5 | 10 | 15 | 20 | 25 |
=$A2*B$1B2 tem =$A2*B$1. Clique em F6: ela tem =$A6*F$1, o número da linha na coluna A vezes o número da coluna na linha 1, então mostra 25. Tire um dos cifrões em B2 e a tabela desmonta, porque as cópias passam a multiplicar células vizinhas em vez dos cabeçalhos.
O mesmo padrão calcula preços de uma lista com vários descontos: =$A2*(1-B$1), com os preços descendo pela coluna A e os descontos ao longo da linha 1.
Total acumulado com um intervalo meio travado
Um intervalo pode ser travado em uma ponta só. =SOMA($B$2:B2) começa sempre em B2, enquanto o fim desce conforme a fórmula é arrastada, então cada linha soma tudo até ela mesma. A tabela mostra a forma em inglês, =SUM($B$2:B2) (SOMA é SUM em inglês), e também aceita as fórmulas em português, com ponto e vírgula.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Total so far |
| 2 | Jan | 420 | 420 |
| 3 | Feb | 380 | 800 |
| 4 | Mar | 510 | 1310 |
| 5 | Apr | 450 | 1760 |
| 6 | May | 470 | 2230 |
=SOMA($B$2:B2)C6 tem =SOMA($B$2:B6) e mostra 2230, o total dos cinco meses. O mesmo intervalo meio travado faz =CONT.SE($A$2:A2;A2) (CONT.SE é COUNTIF em inglês) contar quantas vezes um valor já apareceu até ali, que é como se marcam as duplicatas depois da primeira.
Prática: uma fórmula para a tabela inteira
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Price | 10% | 20% | 30% |
| 2 | $40.00 | |||
| 3 | $25.00 | |||
| 4 | $60.00 | |||
| 5 | $18.00 |
Sua vez: Em B2, escreva o preço do primeiro produto com o desconto de B1. Use $ para que a mesma fórmula, arrastada para a direita e para baixo até D5, dê todos os preços da tabela.
A tabela copia a sua fórmula para todas as células de B2:D5, como faria a alça de preenchimento, e a verificação lê os doze resultados. Sem os cifrões certos, as cópias na linha 3 ou na coluna C leem o preço errado ou o desconto errado.
Referências absolutas para outra planilha ou uma tabela de busca
Os cifrões funcionam do mesmo jeito com o nome de uma planilha: =B2*Settings!$B$1. Eles importam mais nas buscas, em que a tabela precisa ficar no lugar enquanto o valor procurado se move: =PROCV(A2;$E$2:$F$10;2;FALSO) arrastada para baixo continua procurando em E2:F10, enquanto =PROCV(A2;E2:F10;2;FALSO) desliza a tabela uma linha para baixo a cada cópia e começa a perder as primeiras linhas (PROCV, ou VLOOKUP em inglês). Se uma célula fixa é usada em muitas fórmulas, você também pode dar um nome a ela em Fórmulas > Definir Nome e escrever =B2*Rate; um nome definido assim aponta para a mesma célula em todas as fórmulas, como $E$1.
Perguntas frequentes
O que significa o $ em uma fórmula do Excel?
Ele trava a parte da referência que vem logo depois. Em $E$1, tanto a coluna E quanto a linha 1 estão travadas, então a referência continua sendo E1 para onde quer que a fórmula seja copiada. E$1 trava só a linha e $E1 só a coluna.
Qual é o atalho de referência absoluta no Excel?
Clique dentro da referência enquanto edita a fórmula e pressione F4 (Fn+F4 em muitos notebooks). Cada vez que você pressiona, ela passa por $A$1, A$1, $A1 e A1. No Mac, pressione Cmd+T, ou Fn+F4.
Qual a diferença entre referência relativa e absoluta?
Uma referência relativa como B2 se move quando você copia a fórmula: uma linha abaixo, ela vira B3. Uma referência absoluta como $B$2 continua $B$2. Use referências absolutas para uma célula única de que todas as linhas precisam, como uma taxa ou um total.
O que é uma referência mista no Excel?
Uma referência com um cifrão só: $A2 mantém a coluna e deixa a linha se mover, B$1 mantém a linha e deixa a coluna se mover. =$A2*B$1 arrastada para a direita e para baixo em uma grade monta uma tabuada.
Por que minha fórmula mostra 0 ou #DIV/0! depois que arrasto para baixo?
Uma referência que deveria ficar fixa se moveu na cópia. Se a linha 2 tem =B2/B7, a linha 3 recebe =B3/B8, e B8 está vazia. Trave o total com =B2/$B$7 e arraste de novo.
