Já alguma vez viu um cifrão ($) numa célula no Excel e se questionou o que significava? Ou já precisou de os usar para “trancar” células mas nunca percebeu a lógica da sua utilização? No Excel, existem referências relativas, absolutas e mistas. Escolher o tipo de referências certo para cada situação, por vezes é complicado para muitas pessoas. Neste artigo pretendo ensinar-lhe, de forma simples, a lógica que está por trás da escolha de cada tipo de referência.
Perceber a lógica de utilização das referências relativas, absolutas e mistas é essencial para tirar maior partido das fórmulas e funções e tornarmo-nos mais eficientes, tanto é que no meu curso completo de Excel – o EXCELerate Your Skills, é um dos temas que abordamos por serem um elemento tão importante na constituição das fórmulas.
Vou começar por lhe explicar o que é uma referência de célula, qual a diferença entre referências relativas, absolutas e mistas e em que situações usar cada uma.
O que é uma referência de célula?
Mas afinal o que é uma referência de célula? Uma referência de célula corresponde ao conjunto de coordenadas que indica a localização de uma célula. É constituída por uma letra, que indica a coluna e por um número que indica a linha.
As referências são um dos elementos que constituem as fórmulas. Utilizando o tipo de referência certo, é o que permite “arrastar” uma fórmula para outras células e obter o resultado pretendido, fixando as linhas ou colunas necessárias.
Referências Relativas, Absolutas e Mistas
No Excel existem referências Relativas, Absolutas e Mistas, e são identificadas da seguinte forma:
São os cifrões ($), ou cifrõezinhos, ou dólares (muitas pessoas chamam coisas diferentes a estes símbolos) que permitem alternar entre os diferentes tipos de referências. Para o fazer, tem de:
- Selecionar a célula que contém a fórmula que pretende modificar;
- Na barra de fórmulas, selecionar a referência a alterar;
- Carregar na tecla F4:
- 1 vez para 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.
Ou, em alternativa, também pode introduzir manualmente o cifrão ($) no sítio necessário.
Mas já vai entender melhor a utilidade deste símbolo tão poderoso!
Referências Relativas
Por defeito, uma referência de célula é uma referência relativa. Esta tem o aspeto A1 e é uma referência de célula sem o sinal do cifrão ($) nas coordenadas das linhas e colunas, ou seja, é uma referência “livre”.
Isto significa que se copiarmos uma fórmula que tenha uma referência relativa para outra célula, a referência vai-se alterar.
Exemplo 1:
Para perceber melhor o que é disto de referências relativas, vamos ver um exemplo. Temos uma lista de produtos, com o respetivo preço e quantidade vendida e precisamos de saber o Total de Vendas por Produto. Para tal, escrevemos a fórmula =C4*D4, que corresponde ao Preço do Produto A vezes a Quantidade vendida do Produto A:
Para sabermos o valor de vendas dos outros produtos, podemos copiar a fórmula para as restantes células, e vai reparar que as referências alteraram. Por exemplo, para o produto G, a fórmula passou de =C4*D4 para =C10*D10, ou seja, as referências iniciais foram movidas da linha 4 para a linha 10, isto por estamos perante células relativas, que são células “livres” e que alteram consoante o novo destino da célula:
Referências Absolutas
Uma referência absoluta, como $A$1, é uma referência de célula com o cifrão $ nas coordenadas de linha e coluna, estando tanto a linha como a coluna trancada. Se movermos uma fórmula que contenha uma referência deste tipo, esta vai ler sempre à mesma célula.
Exemplo 2:
Imagine que no exemplo anterior, queremos adicionar um desconto de 10% e saber qual o valor com esse desconto. Usaríamos a seguinte fórmula (na imagem seguinte estão apenas referências relativas!):
Acontece que se arrastarmos esta fórmula para as restantes células, não vamos obter o resultado pretendido, pois a percentagem do desconto, que está na célula F2, vai ser alterada.
Pode ver que para o produto G, o desconto que está a ser aplicado está a ser o valor que está na célula F8, o que não faz muito sentido. Isto acontece porque não fixámos que o desconto era sempre o valor da célula F2:
É para estas situações que as referências absolutas são extremamente úteis. Para quando precisamos de fixar sempre o valor de uma célula.
Para o Total com desconto fazer sentido, teríamos de trancar a célula F2, ou seja, torná-la numa referência absoluta, fixando tanto a linha como a coluna, através dos cifrões ($). Desta forma, teríamos o seguinte:
Assim, ao copiarmos a fórmula para as outras células, o resultado seria:
Assim, quando mover a fórmula, o desconto considerado vai ser sempre o da mesma célula. Pode experimentar copiar a fórmula para qualquer célula da folha e vai ver que vai ser usado sempre os 10% de desconto.
Pode ainda ver que na fórmula que colocámos, temos uma referência relativa (que corresponde ao valor total), porque queremos que o valor total varie consoante a linha do produto, e uma referência absoluta (que corresponde à percentagem de desconto), que é sempre a mesma célula.
Referências Mistas
Uma referência mista, como $A1 ou A$1, é uma referência de célula com o cifrão ($) nas coordenadas de linha ou coluna, ou seja, apenas estamos a fixar ou a linha ou a coluna.
Quando o $ está antes da letra significa que a coluna está trancada, quando está antes do número, é a linha que está trancada.
Dica: Para decorar em que posição deve estar o cifrão para fixar as Colunas ou as Linhas, pode lembrar-se de que no alfabeto o C vem antes do L. Da mesma forma que a coluna é primeiro que a linha, da esquerda para a direita.
Exemplo 3:
Agora além de precisarmos de ver o valor total usando um desconto de 10%, também precisamos de ver qual o resultado se o desconto for 20%.
Vamos pegar no exemplo anterior, e copiar a fórmula que criámos para os 10% e copiar para a coluna dos 20% e ver o que acontece:
Várias situações aqui:
- Estamos a usar o Valor Total para aplicar a taxa de desconto? Não…
- Estamos a aplicar o desconto correto? Não…
Vamos tentar resolver a primeira situação. Como a fórmula estava a usar uma referência relativa para ir buscar o valor total (célula E4 no exemplo anterior), quando copiamos para a coluna do lado, essa célula vai-se mover para a coluna do lado também, passando a ser F4, e não é o que queremos.
Queremos que a coluna considerada seja sempre a coluna E. Para isso acontecer, tínhamos de ter fixado essa coluna, criando então uma referência mista, com a coluna fixa e a linha livre:
Nota: não se esqueça que a maneira mais rápida de alternar entre os tipos de referências é usando a tecla F4.
Pode ver que agora na fórmula do desconto dos 10%, temos uma referência mista ($E4) e uma referência absoluta ($F$2).
Agora se copiarmos para a coluna do desconto de 20%, já vai estar resolvida a primeira questão. Já considerou a coluna do valor total, que era o que queríamos:
Pode ver que agora na fórmula do desconto dos 10%, temos uma referência mista ($E4) e uma referência absoluta ($F$2).
Agora se copiarmos para a coluna do desconto de 20%, já vai estar resolvida a primeira questão. Já considerou a coluna do valor total, que era o que queríamos:
A fórmula que vê agora é constituída por duas referências mistas: uma com a coluna trancada ($E4) e outra com a linha trancada (F$2).
Agora se copiarmos esta fórmula para a coluna dos 20% de desconto, já vamos ter o resultado pretendido:
Vamos analisar o produto G: estamos a usar a referência mista $E10, que significa que considerou sempre a coluna E, independentemente da coluna em que está, mas considerou a linha 10, tendo esta alterado, e a referência mista G$2, onde considerou sempre o valor da linha 2 (linha que contém o desconto), mas a coluna altera-se consoante a coluna em que está.
Conclusão sobre Referências Relativas, Absolutas e Mistas:
Nos exemplos anteriores, poderíamos ter alterado as células manualmente nas fórmulas, mas esta é a forma mais eficiente de o fazer: perceber o que se tem de manter fixo e o que pode ficar “livre”, para podermos copiar de uma só vez para todas as células!
E agora, já percebeu melhor como usar as referências relativas, absolutas e mistas? Gostava de ver mais exemplos destes aqui pelo Blog? Conte-me nos comentários!
Uma resposta
EXCELENTE CONTEÚDO.