Referência Absoluta e Relativa no Excel, $ Guia Completo

O sinal do cifrão em fórmulas do Excel confunde muito os usuários quando apresentados em células com referência. Entenda de uma vez por todas a importância desse digito e prepare-se para não se confundir mais!

O propósito do cifrão ($) em uma fórmula com referência de células no Excel é o de informar ao Excel se a referência de célula ou intervalo deve ou não ser alterado quando a fórmula é copiada para outras células.

Este artigo fornecerá detalhes completo sobre este recurso, que garanto, tornará as suas tarefas do Excel bem mais simples de lidar.

Antes de mais nada, você deve entender a diferença entre referências absolutas, relativas e mistas que o Excel oferece.  Após isso você estará preparado para dominar o poder e a versatilidade das formulas e funções do Excel.

É provável que alguém já tenha visto o sinal do cifrão ($) no Excel e tenha se perguntado: “Mas o que é isso?”. Basicamente, eu digo que com o cifrão é possível fazer referências a mesma célula de 4 formas diferentes, como por exemplo: A1, $A$1, $A1 E A$1. E é isso que eu vou explicar mais adiante.

O sinal do cifrão em uma célula de referência afeta apenas uma coisa: instrui o Excel a trabalhar com determinada referência quando a formula é deslocada ou copiada para outras células. Resumidamente acontece isso: quando utilizamos o cifrão ($) antes de uma linha e coluna, as coordenadas farão uma referência absoluta da célula, e não haverá mudança quando copiada ou arrastada para as demais. Sem o sinal do cifrão ($) a referência é relativa e quando copiada ou arrastada o valor da célula muda.

Para quem está escrevendo uma fórmula isolada, não há problemas trabalhar com qualquer tipo de referência. O problema é trabalhar com várias células quando se tem uma única referência. No entanto, para quem pretende copiar uma determinada fórmula para outra célula, escolher o tipo de referência dessa célula é crucial!

O que é uma célula referenciada no Excel?

Uma célula de referência no Excel é uma célula endereçada, que informa ao Excel a necessidade de procurar pelo valor desejado que será inserido em uma determinada fórmula. Por exemplo, se for inserido uma fórmula simples como =A1 na célula C1, o Excel extrairá um valor da célula A1 para a célula C1. Observe:

Desde que se escreva uma fórmula para uma única célula, pode-se utilizar livremente qualquer tipo de referência, com ou sem o cifrão ($), que não haverá problemas.  O resultado será o mesmo:

Caso queira arrastar ou copiar a fórmula para as demais células, é importante escolher exatamente o tipo de referência que se deseja aplicar a ela. Isso porque, se for definida uma referência errada, os resultados apresentados também estarão errados. A dica é sempre confirmar se a fórmula está correta em uma outra célula envolvida, que não seja a inicial.

Os próximos tópicos apresentam informações mais detalhadas, como exemplos de fórmulas para cada referência de célula.

Referência de célula relativa

As referências de células relativas são células endereçadas sem o sinal do cifrão ($) nas coordenadas das linhas e colunas. Como por exemplo A1.

Quando uma fórmula em células de referências relativas é copiada para outra célula, a referência muda com base na posição das linhas e colunas. Todas as referências de células do Excel são de referências relativas, até se seja alterado pelo usuário. Adiante, veja alguns exemplos de como as referências relativas funcionam.

Suponha que na célula B1 tenhamos a seguinte fórmula:

=A1*10

Se a fórmula for copiada em outra linha da mesma coluna, como por exemplo para a célula B2, a fórmula será ajustada para a linha 2. Isso porque o Excel assumi que o usuário deseja multiplicar o valor de cada linha da coluna A por 10.

=A2*10

Observe que ambas as linhas e colunas referenciadas mudam:

Usar fórmulas com células relativas é conveniente quando se precisa trabalhar com apenas um cálculo na planilha. Para facilitar o entendimento, nos próximos tópicos darei um exemplo, voltado um pouco mais para o cotidiano de um usuário do Excel.

Referência de célula relativa: EXEMPLOS

Suponha que exista uma planilha que na coluna A consta os nomes de algumas pessoas que participaram de uma corrida. Na coluna B é apresentado a distância em metros que cada um deles percorreu durante a competição. No entanto é necessário converter estas distancias de metros (m) para quilômetros (km) afim de padronizar os dados e deixar a planilha mais completa e profissional.

Sabe-se que a distância em metros equivale a 0,001 quilômetros, e para converter de metros para quilômetros basta multiplicar o valor equivalente a metros por 0,001. O resultado é apresentado na coluna C. Observe:

Importante: por padrão, todas as células são de referência relativa no Excel. Então, ao digitar uma fórmula pode-se adicionar uma referência clicando na célula correspondente ao invés de digitar manualmente.

Para copiar a fórmula para demais linhas dessa mesma coluna basta arrastar o mouse até a última linha preenchida da tabela, ou apenas clique duas vezes sobre o sinal da “cruz preta” no canto inferior direito da célula inicial, que o Excel faz isso rapidamente para você!

Desta maneira a fórmula é copiada para as outras células com referências relativas que são propriamente ajustadas para cada célula individualmente.  Para ter certeza que o valor copiado para cada célula está calculado corretamente, selecione qualquer uma das células (que não seja a inicial) e confirme, se estiver certo em uma, estará certo nas outras também.

Neste próximo exemplo, a célula C4 está selecionada. Note que a referência da célula na fórmula é em relação a linha 4.

Referência de célula absoluta (com cifrão $)

Uma referência absoluta no Excel é um endereço de célula com o cifrão $ nas coordenadas de linha ou coluna, como por exemplo: $1$.

O sinal do cifrão trava a referência de uma célula de forma que ela permaneça inalterada, ou seja, não haverá alteração de referência quando a mesma for movida ou copiada para outra célula.

Se na célula A1 contém o valor 10 e neste caso for usado uma referência de célula absoluta como ($A$1), então a fórmula =$A$1+5 retornará sempre 15, independente das demais células para quais a fórmula for copiada. Por outro lado, se for digitado a mesma fórmula com uma referência de célula relativa como (A1), quando a mesma for copiada para uma outra célula na coluna A, um valor diferente será calculado para cada linha. A imagem abaixo demostra a diferença, observe:

Importante: Embora eu tenha explicado que uma referência absoluta no Excel nunca muda, na verdade ela muda quando se adiciona ou remove linhas ou colunas da planilha. No entanto se isso for feito será alterado o local da célula de referencia. No exemplo apresentado acima, se fosse inserido uma nova linha na parte superior da planilha, o Excel ajustararia a fórmula automaticamente. Veja um exemplo:

De fato, é raridade trabalhar apenas referências absolutas em fórmulas do Excel, porém existe algumas tarefas que exigem o uso delas. Para mais detalhes é necessário acompanhar o tópico a seguir.

Uso de células absolutas e relativas em uma fórmula

Muitas vezes é preciso usar uma fórmula em que algumas células possuem referências ajustadas a determinadas linhas e colunas de uma planilha, enquanto outras permanecem fixas em células específicas. Em outras palavras, isso quer dizer que é necessário trabalhar com referências de células relativas e absolutas em uma única fórmula.

Exemplo 1: Referência de células relativas e absolutas para calcular números

No exemplo em que foi necessário converter a distancia percorrida de metros para quilômetros de cada atleta que estava disputando uma corrida, a fórmula poderia ser exibida de maneira diferente: ao invés de digitar 0,001 na fórmula de conversão, uma alternativa seria fixar esse valor em uma única célula e usa-la para completar a fórmula. Para fazer o cifrão $ deve ser inserido na fórmula para travar o valor. Veja um exemplo:

Na fórmula (B4*$C$1), há dois tipos diferentes de referências:

• B4: referência de célula relativa que é ajustada para cada linha;
• $C$1: referência de célula absoluta que não se altera quando a fórmula é movida ou copiada.

Uma vantagem dessa abordagem é que o usuário pode calcular valores baseado em variáveis sem alterar a fórmula, assim como fizemos para converter de metros para quilômetros. O mesmo pode ser aplicado, por exemplo, para calcular a conversão de dólar para reais, no entanto bastará apenas alterar a referência da célula toda vez que o dólar alterar de valor.

Exemplo 2: Referência de células relativas e absolutas para calcular datas

Outro calculo muito comum que os usuários gostam de trabalhar é com usos de referencia relativas e absolutas em células que se aplicam fórmulas para calcular datas no Excel com base na data atual.

Suponha uma lista de tarefas para cumprir até o final do primeiro semestre de 2019 com as datas de entrega inseridas na coluna B, sendo que na célula C1 está apresenta a data atual criada pela função HOJE(). O objetivo é saber quantos dias faltam para o vencimento do prazo de cada uma dessas tarefas, usando a fórmula =B4-$C$1. Observe como é simples:

Novamente é usado dois tipos diferentes de referências nesta fórmula:

• Relativa: para a célula que apresenta a primeira data de entrega (B4). Ela é relativa porque o valor contido nela varia dependendo da linha que a fórmula reside.
• Absoluta: para a célula travada com a data de hoje ($C$1). Ela é absoluta porque o valor contido nela permanece o mesmo quando a fórmula é movida ou copiada para as linhas de baixo.

Referência de célula mista no Excel.

Uma referência de célula mista no Excel é uma referência onde linhas ou colunas são travadas.  Por exemplo: $A1 e A$1 são referências mistas. Mas qual a diferença de cada uma delas?

Uma referência absoluta no Excel contém 2 cifrões ($) que travam colunas e linhas. Em uma referência de célula mista, apenas uma coordenada é absoluta e a outra é relativa. No caso o resultado contido na célula mudará com base em uma posição relativa da linha ou da coluna.

• Para células com colunas absolutas e linhas relativas o travamento é semelhante a: $A1. Quando uma fórmula com este tipo de referência é copiada para outra célula, o sinal do cifrão $ na frente da letra que endereça uma determinada coluna, trava a referência da coluna especificada para que o valor não seja alterado. A referência de linha relativa sem o cifrão, varia de acordo com a linha pela qual a fórmula é copiada.
• Para células com colunas relativas e linhas absolutas, como A$1, a referência da linha não é alterada, somente a da coluna.

Mais adiante é apresentado um exemplo explicando como usar fórmulas com as duas referências, facilitando a compreensão da importância do uso das células mistas.

Referência de célula mista no Excel: EXEMPLOS

Neste exemplo é apresentado uma tabela de conversão de moedas de nacionalidades distintas. Diferente do exemplo anterior, onde a distância percorrida em metros foi convertida para quilômetros, neste não é estabelecido um limite para o valor de referência. Nota-se que o valor da moeda em reais é convertido para outras três moedas, todas com uma única fórmula!

No exemplo, as taxas de conversão são usadas como referências e são inseridas na linha 2. No entanto, basta usar uma fórmula que envolve a célula superior esquerda, da célula onde será inserida a fórmula, multiplicado por suas respectivas taxas de conversão para calcular o preço da moeda desejada. Observe:

=$B5*C$2

• A referência $B5 refere-se ao valor em reais (BLR) que será convertido em outras moedas. A coluna B, neste caso, está travada enquanto os valores inseridos nas linhas, desta coluna, se alteram conforme a fórmula for copiada para as demais células.
• A referência C$2 refere-se a taxa de conversão para dólar (USD). A linha 2 encontra-se travada enquanto os valores inseridos nas demais colunas, desta linha, variam conforme a fórmula for copiada para outra célula.

Na sequência a fórmula inserida na célula C5 é arrastada e copiada para as linhas da coluna C.

Para que a fórmula seja copiada para as colunas D e E basta arrastar a alça para a direita.

Como resultado, aparecerá três colunas com preços diferentes calculados corretamente com base na taxa de conversão correspondente na linha 2.  No entanto é aconselhado sempre confirmar se os valores foram calculados corretamente, e para isso basta selecionar qualquer célula envolvida na tabela e checar se a fórmula está correta na barra de fórmulas.

Se selecionado a célula D7, responsável pela conversão do valor do produto 3 de reais para euros, a formula apresentada na barra de fórmulas é =$B7*D$2. Esta fórmula multiplica o preço contido na célula B7 pelo preço apresentado na célula D2, ou seja, é exatamente o cálculo que foi feito para a célula C5 quando a fórmula foi inserida, a diferença é que agora ela calcula um resultado com base na linha 7 e com a taxa de conversão apresentada na coluna D.

Apesar de todo esse contexto que foi apresentado acima, é importante saber como o Excel determina o preço exato que deve ser usado na multiplicação do exemplo acima. A chave mestre para o Excel executar corretamente os cálculos está nas referências de células mistas ($B5*C$2), elas fazem toda a diferença!

Como fazer referência de linhas e colunas inteiras no Excel

Quando se tem uma planilha que apresenta números variáveis de linhas, é natural trabalhar com referências de colunas inteiras em determinadas fórmulas. Costuma-se no caso, referenciar todo conteúdo das células dentro de uma coluna específica. No entanto, para referenciar uma coluna inteira, basta digitar a letra da coluna duas vezes separado por dois pontos (:), por exemplo: A:A.

Referência de coluna inteira 

Assim como referências de células, uma referência de coluna inteira pode ser classificada como absoluta ou relativa, por exemplo:

• Referência de coluna absoluta:

=$A:$A

• Referência de coluna relativa:

=A:A

O uso do cifrão $ em uma referência absoluta efetua o travamento de uma determinada coluna. Quando nos referimos a referências de colunas inteiras o mesmo acontece para que não haja interferência nos resultados finais da fórmula quando arrastada ou copiada para outras células.

É bom ressaltar que uma referência de coluna relativa será alterada quando a fórmula for copiada para outras colunas, porém se o mesmo for feito para células dentro da mesma coluna a fórmula permanecerá intacta.

Referência de linha inteira

Para se referir a uma linha inteira, a mesma abordagem é utilizada, exceto quando deseja-se digitar números de linhas de colunas:

Na teoria, pode-se criar colunas ou linhas inteiras de referências mistas, como $A:A ou $1:1.  O Exemplo mostra como as fórmulas com estas referências funcionam exatamente como deveriam funcionar.

Exemplo 1: Referência de coluna inteira (absoluta e relativa)

Suponha que alguns números são inseridos na coluna B e deseja-se calcular a média e a soma total destes valores. Contudo, novas linhas com novos valores são adicionadas na planilha toda semana. Sendo assim, escrever uma fórmula usual como SOMA() ou MÉDIA() para intervalos fixos não é o melhor caminho para efetuar a tarefa.

O melhor a ser feito neste caso, é referenciar a coluna B, por exemplo:

=SOMA($B:$B)

Use o cifrão para fazer uma referência absoluta da coluna inteira. A referência será travada na fórmula com o conteúdo da coluna B.

=SOMA(B:B)

Escreva a fórmula sem o cifrão para fazer uma referência relativa da coluna B, que será altera enquanto a fórmula for copiada para outras colunas.

DICA: Quando escrever a função, clique na letra da coluna responsável pelo endereço da coluna inteira, para adiciona-la a fórmula. Como é padrão do Excel, toda fórmula se inicia com referências relativas, ou seja, sem o sinal do cifrão. Para torna-la referência absoluta basta usar o cifrão.

Da mesma forma, uma nova fórmula é inserida para calcular o preço médio de toda a coluna B:

=MÉDIA(B:B)

Neste exemplo, é usado uma coluna inteira com referência relativa, no entanto a fórmula é usada corretamente quando copiada a e arrastada para as colunas vizinhas. Observe:

Importante: Ao usar uma referência de coluna inteira em fórmulas do Excel, nunca insira a fórmula em qualquer lugar desta mesma coluna. Por exemplo, eu poderia ter criado a célula “SOMA TOTAL” e “MÉDIA” na linha 9, trabalhando com as colunas B, C, D e E pois, desta forma ficaria mais cômodo a visualização dos resultados. Porém ao fazer isso o Excel cria uma referência circular e a fórmula retorna zero.

Exemplo 2: Referência linha inteira (absoluta e relativa)

Se os dados de uma planilha estiverem organizados em linhas ao invés de colunas, a linha pode ser referenciada na fórmula. Para calcular um preço médio com o conteúdo da linha 2 por exemplo, deve ser feito da seguinte maneira:

=MÉDIA($2:$2)

Use o cifrão para fazer uma referência absoluta da linha inteira. A referência será travada na fórmula com o conteúdo da linha 2.

=MÉDIA(2:2)

Use a fórmula sem o cifrão para fazer uma referência relativa da linha 2, que será alterada enquanto a fórmula for copiada para outras linhas.

Abaixo será apresentado um exemplo de travamento de referências relativas de linhas inteiras. Note que os dados estão contidos em quatro linhas diferentes.

Exemplo 3: Como se referir a uma coluna inteira excluindo informações explicativas que não devem ser incluídas nos cálculos

O Excel não permite usar referencias como B5:B que incluiria nos cálculos todas as linhas da coluna B a partir da linha 5. Caso seja adicionado essa referência, provavelmente surgirá um erro.

Ao invés disso, uma linha máxima pode ser especificada pelo usuário ao inserir o intervalo na fórmula. Para tanto é legal o usuário saber a quantidade de linhas que o Excel oferece para suas planilhas.

O Excel 2016, 2013, 2010 e 2007 oferece um número máximo de 1.048.576 linhas e 16.384 colunas. As versões mais anteriores do Excel oferecem uma quantidade menor como 65.536 linhas e 256 colunas.

Entretanto, para encontrar a média para cada coluna de preços na tabela abaixo, representada pelas colunas de B a E, deve-se inserir a seguinte fórmula na célula G2:

=MÉDIA(B5:B1048576)

Após inserido a fórmula, basta copia-la para as células H2, I2 e J2. Observe o resultado:

Sugestão: Para selecionar a intervalo na fórmula acima, o usuário ao invés de digitar B5:B1048576 pode utilizar um atalho. Neste caso, após digitar a função MÉDIA, entre os parênteses clique uma vez sobre a célula B5. Na sequência digite Ctrl + Shift + seta para baixo. Note que quando é pressionado a seta uma vez, o Excel seleciona todas as linhas da coluna até a última célula que contem dado, entretanto para selecionar as demais células que estão vazias, clique mais uma vez na seta e então todas as linhas serão selecionadas. O mesmo acontece quando se deseja selecionar um intervalo de colunas, o que difere um atalho do outro é a seta que indica o lado direito.

Caso o usuário esteja trabalhando com a função SOMA, é possível obter o resultado através da subtração de todas as linhas da coluna B por exemplo, com relação as linhas que não serão utilizadas no cálculo. Observe:

=SOMA(B:B)-SOMA(B1:B4)

Exemplo 4: Como usar travamento de colunas inteiras mistas no Excel

Como já mencionado em parágrafos anteriores, existe a opção de fazer referências de colunas inteiras ou linhas inteiras mistas no Excel.

• $A:A: é uma referência de coluna mista.

• $1:1: é uma referência de linha mista.

Note agora o que acontece quando uma fórmula é copiada para outra célula com essas referências. Supondo que trabalhemos com a fórmula =SOMA($B:B) na célula G2. Quando a fórmula é copiada para a direita (célula H2), a fórmula passa a ser =SOMA($B:C) porque o primeiro B é travado pelo sinal do cifrão $, enquanto o segundo não é. Veja abaixo um exemplo:

Como alterar referências absolutas, mistas e relativas através da tecla F4

Quando se escreve uma fórmula no Excel, o sinal do cifrão $ pode ser digitado manualmente pelo usuário para fazer travamentos e alteração de referências. A tecla F4 é um atalho que substitui essa ação.

Se uma referência de célula relativa como A1 for selecionada inicialmente, repetidamente pressionando a tecla F4 é possível alternar as referências da célula. Mas atenção! Para o atalho F4 funcionar, é necessário estar no modo de edição da fórmula.

Para entender melhor como isso funciona, basta seguir o passo a passo abaixo:

1. Selecione a célula com a fórmula.
2. Entre no modo editar pressionando a tecla F2 ou clicando duas vezes na célula.
3. Selecione a referência da célula que deseja efetuar a alteração.
4. Pressione F4 para alternar entre quatro tipos de referência de célula.

Caso o usuário tenha selecionado uma referência de célula relativa, sem o cifrão, como A1 por exemplo, pressionando repetidamente a tecla F4 a célula pode ser alterada entre:

• Uma referência absoluta, no caso $A$1 onde coluna e linha encontra-se travadas;
• Linha absoluta como A$1, travando apenas a linha;
• Coluna absoluta como $A1, travando apena a coluna.

Importante: Ao pressionar F4 sem selecionar uma referência de célula, a referência á esquerda do mouse será selecionada automaticamente e alterada para outro tipo de referência. É importante se atentar a isso!

Por fim, espero que você tenha entendido completamente o que são referências de células no Excel e a diferença entre elas.

Agradeço e leitura e espero você nos próximos artigos!

Conclusão

Neste artigo vimos mais uma das ferramentas que não pode faltar para o profissional que usa Excel no dia-a-dia. Entender como funciona as referências relativas e absolutas no Excel pode te tornar muito mais produtivo na ferramenta favorita do mercado de trabalho!

O site deste pessoal aqui foi referência para este artigo. O site é muitoooo bacana com várias dicas legais para Excel, mas infelizmente o conteúdo está em inglês, o que não deixa de ser uma boa referência para quem conseguir acompanhar. Eu recomendo.

Eu sei que não é comum ler um artigo deste, como tutorial, e repetir para aprender, mas recomendo fortemente que você tome coragem de repetir todo o passo-a-passo. Eu penso que praticar é a melhor maneira de aprender e, embora pareça simples, é preciso se acostumar a navegar entre as telas e fórmulas do Excel para que no futuro você possa se tornar um usuário avançado.

Portanto, continue lendo mais sobre Excel básico aqui.