Uma planilha no Excel é composta de células. Essas células podem ser referenciadas especificando o valor da linha e o valor da coluna.
Por exemplo, A1 se refere à primeira linha (especificada como 1) e à primeira coluna (especificada como A). Da mesma forma, B3 seria a terceira linha e a segunda coluna.
O poder do Excel reside no fato de que você pode usar essas referências de células em outras células ao criar fórmulas.
Agora, existem três tipos de referências de células que você pode usar no Excel:
- Referências de células relativas
- Referências celulares absolutas
- Referências de células mistas
Compreender esses diferentes tipos de referências de células o ajudará a trabalhar com fórmulas e economizar tempo (especialmente ao copiar e colar fórmulas).
O que são referências de células relativas no Excel?
Deixe-me dar um exemplo simples para explicar o conceito de referências de células relativas no Excel.
Suponha que eu tenha um conjunto de dados mostrado abaixo:
Para calcular o total de cada item, precisamos multiplicar o preço de cada item pela quantidade desse item.
Para o primeiro item, a fórmula na célula D2 seria B2 * C2 (conforme mostrado abaixo):
Agora, em vez de inserir a fórmula para todas as células uma por uma, você pode simplesmente copiar a célula D2 e colá-la em todas as outras células (D3: D8). Ao fazer isso, você notará que a referência da célula se ajusta automaticamente para se referir à linha correspondente. Por exemplo, a fórmula na célula D3 se torna B3 * C3 e a fórmula em D4 se torna B4 * C4.
Essas referências de célula que se ajustam quando a célula é copiada são chamadas referências de células relativas no Excel.
Quando usar referências de células relativas no Excel?
As referências de células relativas são úteis quando você precisa criar uma fórmula para um intervalo de células e a fórmula precisa se referir a uma referência de célula relativa.
Nesses casos, você pode criar a fórmula para uma célula e copiá-la e colá-la em todas as células.
O que são referências de células absolutas no Excel?
Ao contrário das referências de células relativas, as referências de células absolutas não mudam quando você copia a fórmula para outras células.
Por exemplo, suponha que você tenha o conjunto de dados conforme mostrado abaixo, onde você deve calcular a comissão para as vendas totais de cada item.
A comissão é de 20% e está listada na célula G1.
Para obter o valor da comissão para cada venda de item, use a seguinte fórmula na célula E2 e copie para todas as células:
= D2 * $ G $ 1
Observe que há dois cifrões ($) na referência da célula que tem a comissão - $G$2.
O que o sinal de dólar ($) faz?
Um símbolo de cifrão, quando adicionado na frente do número da linha e da coluna, torna-o absoluto (ou seja, impede que o número da linha e da coluna mude quando copiado para outras células).
Por exemplo, no caso acima, quando copio a fórmula da célula E2 para E3, ela muda de = D2 * $ G $ 1 para = D3 * $ G $ 1.
Observe que, embora D2 mude para D3, $ G $ 1 não muda.
Uma vez que adicionamos um símbolo de dólar na frente de ‘G’ e ‘1’ em G1, não permitiria que a referência da célula mudasse quando fosse copiada.
Portanto, isso torna a referência da célula absoluta.
Quando usar referências de células absolutas no Excel?
As referências de célula absolutas são úteis quando você não deseja que a referência de célula mude conforme você copia as fórmulas. Esse pode ser o caso quando você tem um valor fixo que precisa ser usado na fórmula (como taxa de imposto, taxa de comissão, número de meses, etc.)
Embora você também possa codificar esse valor na fórmula (ou seja, usar 20% em vez de $ G $ 2), colocá-lo em uma célula e usar a referência da célula permite que você altere-o em uma data futura.
Por exemplo, se sua estrutura de comissão mudar e agora você está pagando 25% em vez de 20%, você pode simplesmente alterar o valor na célula G2 e todas as fórmulas serão atualizadas automaticamente.
O que são referências de células mistas no Excel?
As referências de células mistas são um pouco mais complicadas do que as referências de células absolutas e relativas.
Pode haver dois tipos de referências de células mistas:
- A linha é bloqueada enquanto a coluna muda quando a fórmula é copiada.
- A coluna é bloqueada enquanto a linha muda quando a fórmula é copiada.
Vamos ver como funciona usando um exemplo.
Abaixo está um conjunto de dados onde você precisa calcular os três níveis de comissão com base no valor percentual na célula E2, F2 e G2.
Agora você pode usar o poder da referência mista para calcular todas essas comissões com apenas uma fórmula.
Insira a fórmula abaixo na célula E4 e copie para todas as células.
= $ B4 * $ C4 * E $ 2
A fórmula acima usa os dois tipos de referências de células mistas (uma em que a linha está bloqueada e outra em que a coluna está bloqueada).
Vamos analisar cada referência de célula e entender como funciona:
- $ B4 (e $ C4) - Nesta referência, o cifrão está logo antes da notação da coluna, mas não antes do número da linha. Isso significa que quando você copia a fórmula para as células à direita, a referência permanecerá a mesma, pois a coluna é fixa. Por exemplo, se você copiar a fórmula de E4 para F4, essa referência não mudará. No entanto, ao copiá-lo, o número da linha mudaria, pois não está bloqueado.
- E $ 2 - Nesta referência, o cifrão está logo antes do número da linha e a notação da coluna não tem cifrão. Isso significa que quando você copia a fórmula para baixo nas células, a referência não muda porque o número da linha está bloqueado. No entanto, se você copiar a fórmula para a direita, o alfabeto da coluna mudará, pois não está bloqueado.
Como alterar a referência de relativa para absoluta (ou mista)?
Para alterar a referência de relativa para absoluta, você precisa adicionar o cifrão antes da notação da coluna e do número da linha.
Por exemplo, A1 é uma referência de célula relativa e se tornaria absoluta quando você a tornasse $ A $ 1.
Se você tiver apenas algumas referências para alterar, poderá achar fácil alterá-las manualmente. Portanto, você pode ir para a barra de fórmulas e editar a fórmula (ou selecionar a célula, pressionar F2 e alterá-la).
No entanto, uma maneira mais rápida de fazer isso é usando o atalho de teclado - F4.
Quando você seleciona uma referência de célula (na barra de fórmulas ou na célula no modo de edição) e pressiona F4, a referência é alterada.
Suponha que você tenha a referência = A1 em uma célula.
Aqui está o que acontece quando você seleciona a referência e pressiona a tecla F4.
- Pressione a tecla F4 uma vez: A referência da célula muda de A1 para $ A $ 1 (torna-se ‘absoluto’ de ‘relativo’).
- Pressione a tecla F4 duas vezes: A referência da célula muda de A1 para A $ 1 (muda para referência mista onde a linha está bloqueada).
- Pressione a tecla F4 três vezes: A referência da célula muda de A1 para $ A1 (muda para referência mista onde a coluna está bloqueada).
- Pressione a tecla F4 quatro vezes: A referência de célula torna-se A1 novamente.
