Referência relativa é a que muda sozinha quando você copia a fórmula para outra célula (o padrão do Excel); referência absoluta é a que fica travada, usando o símbolo $ antes da coluna e/ou da linha, como em $B$7.
Na aula passada criamos a nossa primeira planilha no Excel
Este artigo faz parte do nosso curso grátis de Excel para iniciantes. Nesta aula iremos aprender como realizar cálculos no Excel, como trabalhar com as fórmulas, os operadores aritméticos e suas prioridades, e as referências absoluta e relativa.
CÁLCULOS
O Excel é um programa criado para realizar cálculos. Como você viu quando criou a primeira planilha, tudo acontece nas células.
Por exemplo, vamos criar uma planilha que vai armazenar dois valores em duas células e calcular a sua soma em outra célula.
Veja que inserimos o valor 12 na célula A2, o valor 10 na célula B2 e estamos inserindo a fórmula para calcular a soma dos dois valores na célula C2. Como o valor dessa célula mostrará o resultado de um cálculo, antes da expressão de soma digitamos o sinal de igual (=), para que o Excel reconheça o conteúdo da célula como sendo um cálculo.
Após pressionar a tecla Enter, teremos o seguinte resultado:
O resultado está correto, a soma foi efetuada. Mas, se por qualquer motivo, um dos valores que estão sendo somados for modificado, o resultado apresentado não será o correto, pois na célula C2 a soma é 12 + 10, independentemente dos valores que estão armazenados nas células A2 e B2. Como fazer para que o Excel atualize o resultado da soma de acordo com os valores armazenados nas células?
FÓRMULAS
Para que o Excel entenda que os valores de um cálculo podem ser variáveis, vamos trabalhar com fórmulas, que são expressões que produzem um resultado.
No nosso exemplo, vamos modificar o conteúdo da célula C2 indicando os endereços das células que compõem a fórmula, ao invés de seus valores.
Assim que a tecla Enter for pressionada, o valor da soma é calculado. Agora, quaisquer que sejam os valores digitados nas células A2 e B2, na célula C2 sempre teremos o valor da soma calculado corretamente.
Lembre-se que a digitação de uma fórmula sempre deve iniciar com o sinal de igual (=), para que o Excel reconheça que é uma fórmula, e não um conteúdo do tipo texto.
OPERADORES ARITMÉTICOS E SUAS PRIORIDADES
Ao efetuar cálculos aritméticos, devemos sempre prestar atenção à forma como indicamos as operações que devem ser efetuadas. Por exemplo:
Ao efetuar o cálculo 2 + 20 / 5, o resultado que iremos obter é 6, e não 4,4 como poderíamos esperar. Isso acontece por causa das prioridades dos operadores aritméticos em uma expressão. No cálculo 2 + 20 / 5, a divisão (20/5) é feita em primeiro lugar, em seguida seu resultado é somado ao valor 2.
Os operadores aritméticos e suas prioridades são mostrados na tabela a seguir.
Operadores com a mesma prioridade são executados na ordem em que se encontram na expressão.
Para modificar a ordem em que os cálculos são efetuados em uma expressão aritmética, a operação que deve ser efetuada antes deve ser envolvida em parênteses.
No caso do exemplo, para que o resultado seja 4,4, devemos indicar a expressão como:
(2 + 20) / 5
TRABALHANDO COM FÓRMULAS
Vamos criar uma planilha para calcular o valor das vendas de uma loja.
A fórmula para calcular o Valor Total é: Quantidade * Valor Unitário.
- Na célula D2 digite: =B2*C2
- Na célula D3 digite: =B3*C3
- Na célula D4 digite: =B4*C4
- Na célula D5 digite: =B5*C5
Caso apareça uma sequência de caracteres # como resultado da fórmula, não se assuste! Isso significa que o valor na célula é muito grande para a largura da coluna. Basta aumentar a largura da coluna que o problema é resolvido.
Após a inserção das fórmulas na coluna Valor Total, você terá o seguinte resultado:
Observe que as fórmulas para o cálculo do Valor Total são iguais em relação à lógica, ou seja, o resultado é sempre o cálculo da coluna Quantidade multiplicado pela coluna Valor Unitário. O que muda, de fórmula para fórmula, é o Produto, ou seja, a posição que este elemento ocupa na planilha.
Um dos recursos mais poderosos que o Excel oferece é a cópia de fórmulas em uma planilha, evitando a digitação e, consequentemente, minimizando possíveis erros, como também facilitando o preenchimento da planilha. Imagine se tivéssemos 300 produtos como seria trabalhoso o processo de digitação das fórmulas!
Referências relativas
Analisando as fórmulas da planilha, podemos verificar que o que muda são as identificações das linhas nos endereços das células.
A fórmula utiliza referências relativas, ou seja, é modificada relativamente quanto à posição em que ocupa na planilha.
- Apague as fórmulas que você digitou na coluna Valor Total.
- A seguir, digite a fórmula na célula D2: =B2*C2
- Selecione a célula D2.
- Clique no quadradinho que se encontra no canto inferior direito da célula. A esse elemento damos o nome de Alça de Preenchimento.
- O mouse assume o formato de uma cruz, indicando que está em processo de cópia.
- Arraste o mouse até a célula D5. Pronto! As fórmulas estão copiadas.
EXERCÍCIO
Modifique a planilha inserindo duas novas colunas: Desconto e Valor a Pagar.
- A fórmula para a coluna Desconto é: Valor Total * 10%
- A fórmula para a coluna Valor a Pagar é: Valor Total – Desconto
Utilize o recurso de cópia de fórmula para preencher as novas colunas.
Referências absolutas
Vamos modificar a planilha para deixar o percentual de desconto como sendo um valor variável, informado pelo usuário e armazenado em uma célula. Dessa forma a planilha vai calcular o valor a ser pago de acordo com o percentual de desconto informado.
Veja que agora o percentual de desconto está armazenado em uma célula (B7), não sendo mais o valor fixo de 10%, permitindo que seja alterado e que a planilha seja automaticamente calculada.
A fórmula que deve ser colocada na coluna Desconto é a seguinte: Valor Total * Desconto.
- Digite na célula E2 a seguinte fórmula: =D2*B7
- Copie a fórmula para as demais células.
- Nessa situação não temos mais os valores calculados corretamente. Vamos analisar o motivo.
Veja que, na cópia, o Excel considerou os endereços de forma relativa, ou seja, a célula B7 que contém o percentual de desconto só está correta na fórmula original, nas demais foi substituída por B8, B9 e B10. Isso aconteceu porque o Excel considerou que, como o Valor Total é relativo à posição que se encontra na planilha, então o restante da fórmula também deveria ser, modificando o valor da linha. Só que, nesse caso, o cálculo não está correto. Na fórmula, a célula que contém o percentual de desconto deve ser fixa, não pode ser alterada de forma relativa.
Para informar ao Excel que linhas e/ou colunas devem ser fixadas em uma fórmula, fazendo com que o endereço da célula seja fixo, vamos utilizar o caractere $ para indicar se uma linha, ou uma coluna, ou ambas devem ser fixas como endereço de célula.
Dessa forma, a fórmula correta que deve ser colocada na célula é:
=D2*$B$7
O caractere $ colocado antes da identificação da coluna(B), faz com que ela não mude no processo de cópia da fórmula. Da mesma fora, o caractere $ colocado antes da identificação da linha (7) faz com que ela não mude no processo de cópia. Nesse caso, o endereço B7 se torna absoluto.
- Modifique a fórmula colocada na célula E2 para: =D2*$B$7. A seguir, copie a fórmula para as demais células.
Agora o cálculo está correto. Independentemente da posição que o produto ocupa na planilha, a fórmula está considerando o valor do percentual de desconto que está armazenado na célula B7.
- Para calcular o Valor a Pagar, digite na célula F2 a fórmula: =D2-E2.
- Copie a fórmula para as demais células. Você terá a planilha calculada de forma correta (Figura 55).
- Modifique os valores, como Quantidade, Valor Unitário e Percentual de Desconto, e veja que a planilha é automaticamente recalculada.
- Grave a pasta de trabalho como Vendas.
DICA: quando precisar fixar um endereço de célula, digite o endereço e pressione a tecla F4. Esse procedimento colocará, automaticamente, o caractere $ antes da identificação da coluna e, também, da linha.
EXERCÍCIO
Crie a seguinte planilha:
O valor do dólar deve ser variável. A coluna VALOR EM REAL deve ser calculada. Formate a planilha como na figura. Grave a pasta de trabalho como Preços.
Perguntas frequentes
Qual a diferença entre referência relativa e referência absoluta no Excel?
A referência relativa muda automaticamente de acordo com a posição da célula quando a fórmula é copiada para outras células — é o comportamento padrão do Excel. A referência absoluta, indicada pelo símbolo $ antes da coluna e/ou da linha (como em $B$7), permanece fixa independentemente de para onde a fórmula é copiada.
Qual atalho de teclado transforma uma referência relativa em absoluta rapidamente?
Pressione F4 logo depois de digitar ou selecionar o endereço da célula na fórmula. O Excel adiciona automaticamente o símbolo $ antes da coluna e da linha, tornando a referência absoluta. Pressionar F4 várias vezes alterna entre os diferentes tipos de referência: absoluta, mista e relativa.
Por que minha fórmula copiada para outras células mostra um resultado errado?
O motivo mais comum é o uso de uma referência relativa em uma célula que deveria ser fixa, como o percentual de desconto ou uma taxa usada em todos os cálculos. Quando a fórmula é copiada, essa referência ‘anda junto’ com a célula de destino em vez de continuar apontando para o valor fixo original — a solução é transformar essa referência em absoluta com o símbolo $.