Referências Relativas e Absolutas: O Essencial em Qualquer Fórmula do Excel

Esse artigo foi publicado originalmente no meu antigo blog do Guru do Excel em 04/Nov/2013. Este é um trabalho que estou realizando de recuperação de publicações antigas minhas.

Laennder Alves

Índice

• Introdução
• O Conhecimento Básico
• Referências Relativas e Absolutas

Introdução

Para saber criar uma fórmula você não tem que dominar a teoria da relatividade de Einstein. Falaremos aqui sobre a outra relatividade, a das referências do Excel.

Este é o primeiro artigo da série 5 conhecimentos que devo ter sobre o Excel.

O Conhecimento Básico

Não dá pra acreditar que uma pessoa seja boa em Excel se ela não souber criar uma fórmula. E para criar fórmula é essencial conhecer as referências relativas e absolutas.

No Excel, quando falamos de referência, estamos falando da representação de uma célula ou intervalo, ou seja, o seu endereço. Quando vamos nos referir a uma célula, sempre usamos a coluna e linha em que a célula está. Veja o exemplo abaixo.

Na planilha a seguir vamos calcular o total das vendas dos meses de Janeiro a Março. A fórmula na célula E2 seria: =SOMA(B2:D2)

A fórmula =SOMA(B2:D2), calcula a somatória dos valores entre as células B2 e D2. Logo após inserir esta fórmula, é comum que você copie-a paras as células abaixo. Mas isso só é possível graças as tais referências.

REFERÊNCIAS RELATIVAS E ABSOLUTAS

Existem dois tipos de referências no Excel. As relativas e as absolutas. Uma referência é identificada de acordo com sua notação. As referências absolutas são precedidas por um $ (cifrão), enquanto as referências relativas não são precedidas por nenhum símbolo.

Referências Relativas

Quando você copia uma fórmula de uma célula para outra, você na realidade está copiando o cálculo, e não o resultado. Na planilha acima, ao copiar a fórmula =SOMA(B2:D2) da célula E2 para a célula E3, o Excel automaticamente refaz os cálculos baseando-se nas referências.

Primeiro, imagine o deslocamento da célula de origem, onde está a fórmula que copiou, para a célula de destino, que receberá a fórmula que irá colar.

E2 → E3. A referência incrementa uma linha (2 → 3) e não modifica na coluna (E → E). O mesmo acontecerá com todas as referências relativas da fórmula copiada. Veja o resultado:

=SOMA(B2:D2) → =SOMA(B3:D3) 

Isso irá ocorrer com qualquer que seja a célula de destino. Suponha que tenha copiado a fórmula da célula E2, e cole-a na célula F5.

E2 → F5. A referência incrementa 1 coluna (E → F) e incrementa 3 linhas (2 → 5). O resultado será então:

=SOMA(B2:D2) → =SOMA(C5:E5)

Sempre que copiar uma fórmula de uma célula para outra, as referências relativas irão mover na mesma quantidade de linhas e colunas que a célula de origem e destino estão distantes.

Obs: Você pode copiar uma fórmula usando a alça de preenchimento ou usando os recursos COPIAR (Ctrl + C) → COLAR (Ctrl + V).

Referências Absolutas

Ao usar referências absolutas, você fixa a linha e/ou coluna, usando um $ (cifrão) antes da referência, para que, ao copiar uma fórmula a referência não se altere. Veja o exemplo abaixo:

O objetivo da planilha é calcular o valor de cada um dos produtos, em reais (R$), conforme variação do dólar (US$). Para isso basta multiplicar o valor do produto (coluna B) pelo valor do dólar (linha 2).

Para calcular o valor do produto Smartphone, no dia 01-Nov-2013, a fórmula ficaria assim: =B6*C2.

Porém, perceba que, ao copiar a fórmula para a célula abaixo (C7), a mesma ficaria da seguinte forma: =B7*C3 (incrementando 1 linha nas referências anteriores). Isto seria um erro, já que o valor do dólar para todos os produtos da coluna C, encontra-se na célula C2. Desta maneira, é necessário fixar a linha 2, para que ela não se altere ao copiar as fórmulas. A célula C2 deve então ser anotada usando um cifrão antes da linha. C$2.

Obs: Repare que a coluna C não deve ser fixada, já que ao copiar a fórmula para as colunas D e E, o valor do dólar também se move para as colunas ao lado.

O mesmo ocorre com a referência da célula B6. Ao copiar a fórmula da célula C6 para a célula D6, há um incremento de 1 coluna. Sendo assim a célula B6 se alteraria para C6. Este também seria um erro, já que o valor do produto não altera de coluna, sendo o mesmo para todas as cotações do dólar. Para evitar isso, devemos fixar a coluna B, colocando um cifrão antes da mesma, na notação da referência. $B6.

A fórmula na célula C6 ficará assim: =$B6*C$2

Copie a fórmula da célula C6 para todas as células do intervalo C6:E10.

Para praticar, digite a planilha do exemplo acima, e proceda com o passo-a-passo do artigo.

Resumo

Tabela de resumo baseado na referência da célula A1.

Veja mais um exemplo prático da aplicação das referências.

SOMATÓRIA CUMULATIVA

Numa planilha onde são listadas as quantidades de defeitos mensalmente, deseja-se calcular o valor acumulado de defeitos ao longo do ano.

Solução: O valor da somatória de cada um dos meses será sempre da célula B2, até o valor do mês que deseja obter o valor acumulado. A fórmula na célula C2 será: =SOMA(B$2:B2)

Explicação: Ao copiar a fórmula para as linhas abaixo, a primeira referência B$2, não irá se alterar, já que a linha é uma referência absoluta. Já a segunda referência B2 (sem o cifrão), irá incrementar as linhas conforme é copiada para as linhas abaixo. O resultado, será então, as seguintes fórmulas:

Saber usas as referências relativas e absolutas é muito útil, pois além das fórmulas pode ser aplicado em outras ferramentas, como em validação e formatação condicional.

E pra você quais são os 5 conhecimentos que deve ter sobre o Excel? Deixe nos comentários.

Espero que tenham gostado. Se este artigo foi útil, ajude na divulgação do blog, compartilhando os artigos nas redes sociais utilizando os botões abaixo. Assim você ajuda o blog, e outras pessoas que querem aprender o Excel.