Veja neste artigo como fazer referência de células no Excel e como travar células no Excel.
1. INTRODUÇÃO
Ao construir fórmulas no Excel é fundamental entendermos como funcionam as referências de células e tabelas para que possamos replicar fórmulas de maneira produtiva sem necessidade de reescrevê-las. Saber travar as células corretamente permite obter resultados muitas vezes com apenas uma única fórmula, em vez de fazer fórmulas diferentes para cada coluna ou linha a ser preenchida, por exemplo. Este artigo objetiva esclarecer os tipos de referências no Excel e mostrar como travar células corretamente, incluindo as referências de tabela, na qual é usual a referência contendo o nome da tabela e do campo.
2. ENTENDENDO AS REFERÊNCIA DE CÉLULAS
As referências de células assumem 3 classificações: referências relativas, referências absolutas e referências mistas. Cada classificação implica ou não na mudança de endereço ao arrastarmos uma fórmula, ou ainda, em aspectos importantes ao construirmos formatação condicional, validação de dados e no uso do gerenciador de nomes. Compreendendo que arrastar fórmulas verticalmente (para cima ou para baixo) tende a mudar a linha da referência e que, arrastar as fórmulas horizontalmente (para a direita ou para a esquerda) tende a mudar a coluna da referência, vamos analisar os tipos de referências no Excel.
2.1. Referência relativa Excel
Referências relativas não possuem cifrão. Isso significa que a referência é passível de mudar sempre que aplicada em uma fórmula a ser copiada. Referências como B6, J5 e H10 são exemplos de referências relativas.
Quando estamos posicionados em uma célula de uma certa planilha e construímos uma fórmula que aponta para um endereço da mesma planilha ou de outra planilha dentro da mesma pasta de trabalho, a referência inserida será relativa por padrão. Note na imagem abaixo que desejamos calcular o dobro do número inserido em B6. Ao escrevermos a fórmula, após a multiplicação (asterisco), clicamos na referência B6 e ela é inserida em seu formato relativo.
Vamos tomar como base ainda a mesma fórmula da imagem. Se esta fórmula for copiada para baixo, B6 se tornará B7. Note que a coluna B permanece, mas a linha 6 será alterada para a linha 7. Ao continuar arrastando para baixo, teremos modificações para B8, B9, B10 e assim por diante. A referência é relativa, permitindo que a linha seja alterada. Não há cifrão antes da linha, o que seria suficiente para manter a linha 6 sempre ao ser copiada. Da mesma forma, se a fórmula inicial for arrastada para cima, B6 se tornará B5, B4, B3 e assim por diante.
Tomando como base a mesma fórmula original, = 2 * B6, ao arrastamos para a direita, e referência B6 será alterada para C6. Se prosseguirmos arrastando para a direita, teremos D6, E6, F6 e assim por diante. Note que B será sempre alterado para as letras que indicam as próximas colunas. Da mesma forma, se arrastarmos a fórmula original para a esquerda, B se tornará A (coluna anterior).
Note que as referências podem sempre mudar, a depender da direção, visto que são relativas, não possuindo “travas” que fixem a referência (linha e/ou coluna).
2.2. Referências absolutas
Enquanto as referências relativas são “livres” para mudar, as referências absolutas são o oposto. Referências absolutas possuem 2 cifrões e isso significa que a referência jamais mudará, independentemente da direção que copiemos uma fórmula que as contenha. Referências como $B$6, $J$5 e $H$10 são exemplos de referências absolutas. IMPORTANTE: o cifrão trava aquilo que o sucede.
Quando estamos posicionados em uma certa planilha e construímos uma fórmula que aponta para um endereço presente em outra pasta de trabalho, a referência inserida será absoluta por padrão. O mesmo ocorre quando criamos um nome/intervalo nomeado diretamente no gerenciador de nomes ou regras de formatação condicional usando fórmulas.
Vamos tomar como base a fórmula de acordo com a imagem a seguir. A fórmula inserida em D9 é = C9 * $B$9.
Se esta fórmula for copiada para baixo, ao passo que C9 sempre será alterado visto que sua referência é relativa (até se tornar C18, cujo valor é 10), B9 não se tornará B10, nem B11, nem B12 e assim por diante. Isso ocorre por conta dos cifrões colocados antes da coluna B e antes da linha 6. Com isso, sempre esta fórmula apontará para o número 15, inserido na célula B9. Desta forma, conseguimos fazer a multiplicação dos números de 1 a 10 pelo número 15 (que não muda). Veja nas imagens a continuação das fórmulas (a multiplicação por 6 e por 10).
Note que o C6 inicial se tornou C14 e também C18, ao passo que B9 manteve-se.
2.3. Referências mistas
As referências mistas são um caso especial. Elas possuem apenas um cifrão (na linha ou coluna). Isso significa que a referência poderá mudar ou não, a depender da direção em que ocorrerá a replicação da fórmula e também da própria referência. Referências como $B6, B$6, $J5, J$5, $H10 e H$10 são exemplos de referências mistas. Dentre todas as referências, estas são as que exigem mais atenção.
Analisemos o cenário da imagem a seguir. Devemos preencher a tabela para ratear as metas mensais entre os gestores proporcionalmente, de acordo com os pesos que estes representam.
A solução é uma multiplicação simples: o peso % do gestor e a meta do respectivo mês. Ao proceder com a fórmula contendo apenas referências relativas, a primeira célula é preenchida corretamente e temos a meta corretamente estabelecida em janeiro para o gerente Rivaldo Lopes.
O problema ocorre quando copiamos a fórmula para as demais células. O peso do primeiro gestor, inserido em C9, ao longo das fórmulas replicadas na coluna D, passa a ser C10, C11, C12 e C13. Isto é o desejável, visto que ao copiar a fórmula para baixo deveremos atualizar o cálculo para abranger os demais gerentes. No entanto, a meta do mês de janeiro, inserida em D6, também é deslocada para baixo e se torna D7, D8, D9 e D10, gerando erros. Perceba-os na imagem a seguir.
Note que a última fórmula copiada para a coluna D (em D13) apresenta a referência da coluna C correta (peso % do gestor Dênis França). Porém, a célula contendo a meta de janeiro (inserida em D6) foi alterada e para o último gestor passou a ser referenciada por D10. Isso é indesejado porque D10 não possui a meta mensal de janeiro. Fica claro que a célula da meta de janeiro não deveria ser deslocada para baixo. Com isso, a conclusão é que sua referência não deveria ser relativa.
A imagem a seguir apresenta uma tentativa de resolver este problema com a meta de janeiro. Vamos definir a referência da célula da meta de janeiro como absoluta e testar.
Note que a coluna para a meta de janeiro está perfeitamente calculada. Na última célula desta coluna percebemos que fazemos referências corretas: ao peso do gestor correto (Dênis França) e à meta do mês (janeiro). Porém, vários problemas ocorrem quando tentamos copiar a mesma fórmula para as demais células. Note o que ocorre com a meta do último gestor no último mês na imagem a seguir.
As referências estão completamente erradas. A fórmula continua tendo como base a meta de janeiro porque não deslocou ao longo da linha 6 para abranger os meses até junho. O peso dos gestores também está sendo referenciado de forma incorreta. Para o último gestor, em vez do peso, o cálculo está referenciando o próprio valor da meta de maio (que está inclusive errada).
Para resolver o nosso problema precisamos prever o que deverá ocorrer com as referências da nossa primeira fórmula, que é exibida a seguir.
Vamos iniciar com a tratativa em relação à célula contendo o peso do gestor. Podemos perceber que SEMPRE os pesos dos gestores estão ao longo de uma mesma coluna (C). Isto já garante que a referência C9, na primeira fórmula, deverá ter cifrão aplicado à coluna. Não deveremos ter cifrão aplicado à linha porque temos novos pesos nas linhas seguintes. Dado o exposto, a referência para C9 deverá ficar mista com cifrão apenas na coluna. Desta forma, ficará $C9.
Agora vejamos a meta mensal. Podemos notar claramente que SEMPRE todas as metas estão ao longo de uma mesma linha (6). Isto já garante que a referência D6, na primeira fórmula, deverá ter cifrão aplicado à linha. Não deveremos ter cifrão aplicado à coluna porque temos novas metas nas colunas seguintes (metas de FEV à JUN). Dado o exposto, a referência para D6 deverá ficar mista com cifrão apenas na linha. Desta forma, ficará D$6.
A imagem a seguir mostra o resultado com as referências devidamente ajustadas.
Perceba que a última fórmula faz referências corretas ao peso do último gestor e à meta do último mês. Note ainda que a linha 15 (de D15 a I15) mostra os totais das metas rateadas, que de forma esperada são iguais às metas mensais assinaladas na linha 6.
3. COMO TRAVAR CÉLULAS NO EXCEL
Após entender os tipos de referências e em que ocasiões travar as células, vamos finalizar nossa abordagem indicando as formas de se travar as células.
Como nossa necessidade é empregar cifrão corretamente, podemos muito bem digitar este símbolo. Para tanto, basta combinar a tecla SHIFT com o número 4 (este número 4 deverá ser o que não faz parte do teclado numérico e sim, o que está próximo das teclas de função e relativamente próximo do ESC).
Uma alternativa mais interessante é, no ato da construção das fórmulas, utilizar a tecla F4. Ela alterna entre todos os tipos de referências. Caso uma certa fórmula em edição esteja com o cursor posicionado na referência I2, pressionar F4 repetidamente modificará a referência:
4. COMO TRAVAR REFERÊNCIAS DE TABELAS NO EXCEL
As referências de tabelas são um caso à parte. Neste contexto, não usamos referências de células como discutidas anteriormente. Usamos a notação sempre fazendo referência ao nome da tabela e do respectivo campo. Assim, caso tenhamos uma tabela com o nome fVendas e o campo Vendedor, a referência da coluna será fVendas[Vendedor]. Note que a referência possui inicialmente o nome da tabela seguido do nome do campo entre colchetes.
Quando construímos fórmulas fazendo referência a um certo campo de uma tabela, podemos ter este campo sendo alterado ao arrastar a fórmula para outras colunas. Vejamos a fórmula abaixo. Trata-se de uma soma condicional que tem como critérios os nomes dos vendedores e dos produtos, somando os valores presentes no campo de nome Valor.
Note que a fórmula possui referência para 3 campos da tabela: Valor, Vendedor e Produto (nesta ordem). Ela está inserida apenas na célula H7 e resulta em R$ 5.600 (soma dos valores em amarelo que correspondem às vendas de arroz pelo vendedor João Falcão).
Caso esta fórmula seja copiada para as demais células utilizando CTRL + C e CTRL + V, CTRL + C e ENTER ou colagem especial de fórmulas, os valores serão preenchidos corretamente conforme imagem a seguir.
O problema é o hábito de arrastar as fórmulas usando a alça de preenchimento. Veja como ficaria o preenchimento dos dados caso a primeira fórmula seja arrastada para as demais células usando a alça de preenchimento.
Note que o campo com o nome do vendedor deixou de existir. Ele foi trocado para o campo de Data. O campo de Produto foi trocado pelo campo de Valor e o campo de Valor foi trocado pelo campo de Produto.
Para ajustar as referências, caso desejemos usar a alça de preenchimento, devemos citar duas vezes o nome do campo nas referências, usar o operador de intervalo (:) e colchetes duplamente. O campo de Valor, por exemplo, ficará assim referenciado: fVendas[[Valor]:[Valor]].
Veja a comparação entre as fórmulas:
Antes:
=SOMASES(fVendas[Valor];fVendas[Vendedor];$G7;fVendas[Produto];H$6)
Depois:
=SOMASES(fVendas[[Valor]:[Valor]];fVendas[[Vendedor]:[Vendedor]];$G7;fVendas[[Produto]:[Produto]];H$6)
Veja na imagem a seguir a fórmula aplicada.
Download Planilha de Referência de Células
Realize o download da planilha de exemplo de como referências células no Excel neste botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.
Curso Excel Completo – Do Básico ao VBA
Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:
